[Home] [Help]
PACKAGE BODY: APPS.GHR_NON_SF52_EXTRA_INFO
Source
1 PACKAGE BODY GHR_NON_SF52_EXTRA_INFO AS
2 /* $Header: ghddfdef.pkb 120.45.12010000.3 2008/08/21 13:46:42 managarw ship $ */
3
4 g_package varchar2(33) := ' GHR_SF52_DDF_INFO.';
5
6
7 Procedure term_ret_grade
8 ( p_pa_request_id in ghr_pa_requests.pa_request_id%type,
9 p_person_id in per_people_f.person_id%type,
10 p_first_noa_id in ghr_pa_requests.first_noa_id%type,
11 p_second_noa_id in ghr_pa_requests.second_noa_id%type,
12 p_effective_date in ghr_pa_requests.effective_date%type,
13 p_refresh_flag in varchar2 )
14 is
15
16 --Begin Bug 5923620
17 CURSOR GET_ASSIGNEMNT_ID IS
18 SELECT assignment_id
19 FROM per_all_assignments_f
20 WHERE person_id = p_person_id
21 AND p_effective_date between effective_start_date AND effective_end_date;
22
23 l_assignment_id per_assignments_f.assignment_id%type;
24 l_asg_ei_data per_assignment_extra_info%rowtype;
25 l_session ghr_history_api.g_session_var_type;
26 l_session1 ghr_history_api.g_session_var_type;
27 --End Bug 5923620
28
29 l_pei_id per_people_extra_info.person_extra_info_id%type;
30 l_rei_rec ghr_pa_request_extra_info%rowtype ;
31 l_org_rec ghr_pa_request_ei_shadow%rowtype;
32 l_ret_grade1 ghr_pa_request_extra_info.rei_information4%type;
33 l_noa_code ghr_nature_of_actions.code%type;
34 l_first_noa_code ghr_nature_of_actions.code%type;
35 l_second_noa_code ghr_nature_of_actions.code%type;
36 l_existed BOOLEAN := FALSE;
37 l_altered_pa_request_id ghr_pa_requests.altered_pa_request_id%type;
38 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
39
40 cursor c_noa_code(p_noa_id in ghr_pa_requests.first_noa_id%type) is
41 select code from ghr_nature_of_actions
42 where nature_of_action_id = p_noa_id;
43
44 Cursor c_702 is
45 SELECT pei_information1 From_Date,
46 pei_information2 To_date,
47 pei_information3,
48 pei_information4,
49 pei_information5,
50 pei_information6,
51 pei_information7,
52 pei_information8,
53 pei_information9,
54 person_extra_info_id
55 FROM per_people_extra_info pei,
56 pay_user_tables put
57 WHERE pei.person_id = p_person_id
58 AND pei.information_type = 'GHR_US_RETAINED_GRADE'
59 AND put.user_table_id = ghr_general.return_number(pei.pei_information6)
60 AND NVL(fnd_date.canonical_to_date(pei.pei_information1) ,p_effective_date)
61 <= p_effective_date
62 AND nvl(fnd_date.canonical_to_date(pei.pei_information2),p_effective_date)
63 >= p_effective_date
64 UNION
65 SELECT pei_information1 From_Date,
66 pei_information2 To_date,
67 pei_information3,
68 pei_information4,
69 pei_information5,
70 pei_information6,
71 pei_information7,
72 pei_information8,
73 pei_information9,
74 person_extra_info_id
75 FROM per_people_extra_info pei2, ghr_pa_request_extra_info rei,
76 pay_user_tables put
77 WHERE pei2.information_type = 'GHR_US_RETAINED_GRADE'
78 AND rei.information_type = 'GHR_US_PAR_TERM_RG_PROMO'
79 AND pei2.person_extra_info_id
80 = ghr_general.return_number(rei.rei_information3)
81 AND (rei.rei_information5 = 'Y' or rei.rei_information5 is NULL )
82 AND put.user_table_id
83 = ghr_general.return_number(pei2.pei_information6)
84 AND rei.pa_request_id = l_altered_pa_request_id
85 order by 1;
86
87 Cursor c_866(p_person_extra_info_id in
88 per_people_extra_info.person_extra_info_id%type) is
89 SELECT pei_information1,
90 pei_information2,
91 pei_information3,
92 pei_information4,
93 pei_information5,
94 pei_information6,
95 pei_information7,
96 pei_information8,
97 pei_information9,
98 person_extra_info_id
99 FROM per_people_extra_info pei
100 WHERE pei.person_extra_info_id = p_person_extra_info_id;
101
102 cursor c_866_rei_correct is
103 SELECT pei_information1,
104 pei_information2,
105 pei_information3,
106 pei_information4,
107 pei_information5,
108 pei_information6,
109 pei_information7,
110 pei_information8,
111 pei_information9,
112 person_extra_info_id
113 FROM per_people_extra_info pei
114 WHERE pei.person_extra_info_id in (
115 SELECT rei_information3
116 FROM ghr_pa_request_extra_info
117 WHERE pa_request_id = l_altered_pa_request_id
118 AND information_type = 'GHR_US_PAR_TERM_RET_GRADE');
119
120
121 Cursor c_740_pei is
122 SELECT pei_information1 From_Date,
123 pei_information2 To_date,
124 pei_information3 ,
125 pei_information4,
126 pei_information5,
127 pei_information6,
128 pei_information7,
129 pei_information8,
130 pei_information9,
131 person_extra_info_id,
132 null terminate_flag,
133 null original_rpa
134 FROM per_people_extra_info pei,pay_user_tables put
135 WHERE pei.person_id = p_person_id
136 AND pei.information_type = 'GHR_US_RETAINED_GRADE'
137 AND put.user_table_id = pei.pei_information6
138 AND p_effective_date
139 BETWEEN NVL(fnd_date.canonical_to_date(pei.pei_information1) ,p_effective_date)
140 AND NVL(fnd_date.canonical_to_date(pei.pei_information2),p_effective_date)
141 UNION
142 SELECT pei_information1 From_Date,
143 pei_information2 To_date,
144 pei_information3,
145 pei_information4,
146 pei_information5,
147 pei_information6,
148 pei_information7,
149 pei_information8,
150 pei_information9,
151 person_extra_info_id,
152 rei_information5 terminate_flag,
153 'Original RPA' origianl_rpa
154 FROM per_people_extra_info pei2, ghr_pa_request_extra_info rei,
155 pay_user_tables put
156 WHERE pei2.information_type = 'GHR_US_RETAINED_GRADE'
157 AND rei.information_type = 'GHR_US_PAR_TERM_RG_POSN_CHG'
158 AND pei2.person_extra_info_id
159 = ghr_general.return_number(rei.rei_information3)
160 AND put.user_table_id
161 = ghr_general.return_number(pei2.pei_information6)
162 AND rei.rei_information5 = 'Y'
163 AND rei.pa_request_id = l_altered_pa_request_id
164 order by 1;
165
166 cursor c_740_rei is
167 select rei_information3,rei_information4
168 from ghr_pa_request_extra_info
169 where pa_request_id = p_pa_request_id
170 and information_type = 'GHR_US_PAR_TERM_RG_POSN_CHG';
171
172 cursor c_702_rei is
173 select rei_information3,rei_information4
174 from ghr_pa_request_extra_info
175 where pa_request_id = p_pa_request_id
176 and information_type = 'GHR_US_PAR_TERM_RG_PROMO';
177
178 cursor c_altered_par_rec is
179 select altered_pa_request_id from ghr_pa_requests
180 where pa_request_id = p_pa_request_id;
181
182 --Bug#4126188 Begin
183 cursor c_position is
184 SELECT from_position_id,to_position_id
185 FROM ghr_pa_requests
186 WHERE pa_request_id = p_pa_request_id;
187
188 l_pos_ei_data per_position_extra_info%rowtype;
189 l_from_position_id ghr_pa_requests.from_position_id%type;
190 l_to_position_id ghr_pa_requests.to_position_id%type;
191 l_from_poid ghr_pa_requests.personnel_office_id%type;
192 l_to_poid ghr_pa_requests.personnel_office_id%type;
193 --Bug#4126188 End
194
195
196 BEGIN
197 IF p_person_id IS NOT NULL THEN
198 --Begin Bug 5923620
199 FOR C_GET_ASSIGNMENNT_ID IN GET_ASSIGNEMNT_ID LOOP
200 l_assignment_id := C_GET_ASSIGNMENNT_ID.assignment_id;
201 end loop;
202 --end Bug 5923620
203 -- Get Altered PA Request ID to find out Retain Grade records
204 -- terminated in Original Action
205 for altered_par_rec in c_altered_par_rec loop
206 l_altered_pa_request_id := altered_par_rec.altered_pa_request_id;
207 end loop;
208 FOR noa_code IN c_noa_code(p_first_noa_id) LOOP
209 l_first_noa_code := noa_code.code;
210 exit;
211 END LOOP;
212 FOR noa_code IN c_noa_code(p_second_noa_id) LOOP
213 l_second_noa_code := noa_code.code;
214 exit;
215 END LOOP;
216 hr_utility.set_location('first noa code '||l_first_noa_code , 1);
217 hr_utility.set_location('second noa code '||l_second_noa_code, 2);
218 IF l_first_noa_code = '002' THEN
219 l_noa_code := l_second_noa_code;
220 ELSE
221 l_noa_code := l_first_noa_code;
222 END IF;
223 hr_utility.set_location('l_noa_code is '||l_noa_code, 2);
224 -- Delete the RPA EI if there is a change in Effective Date or
225 -- Person
226 IF p_refresh_flag = 'N' THEN
227 IF (nvl(p_effective_date,hr_api.g_date)
228 <> nvl(ghr_par_shd.g_old_rec.effective_date,hr_api.g_date)) or
229 (nvl(p_person_id,hr_api.g_number)
230 <> nvl(ghr_par_shd.g_old_rec.person_id,hr_api.g_number)) then
231 hr_utility.set_location('Inside the Delete IF '||l_noa_code, 2);
232 -- Delete from ghr_pa_request_extra_info
233 DELETE ghr_pa_request_extra_info
234 WHERE INFORMATION_TYPE in ( 'GHR_US_PAR_TERM_RET_GRADE',
235 'GHR_US_PAR_TERM_RG_PROMO',
236 'GHR_US_PAR_TERM_RG_POSN_CHG')
237 AND PA_REQUEST_ID = p_pa_request_id;
238
239 -- Delete from ghr_pa_request_ei_shadow
240 DELETE ghr_pa_request_ei_shadow
241 WHERE INFORMATION_TYPE in ( 'GHR_US_PAR_TERM_RET_GRADE',
242 'GHR_US_PAR_TERM_RG_PROMO',
243 'GHR_US_PAR_TERM_RG_POSN_CHG')
244 AND PA_REQUEST_ID = p_pa_request_id;
245 END IF;
246 END IF;
247 IF l_noa_code IN ( '866' ,'890') THEN
248 hr_utility.set_location('Inside the c_866 loop'||l_noa_code, 3);
249 -- Delete from ghr_pa_request_extra_info
250 DELETE ghr_pa_request_extra_info
251 WHERE INFORMATION_TYPE = 'GHR_US_PAR_TERM_RET_GRADE'
252 AND PA_REQUEST_ID = p_pa_request_id;
253
254 -- Delete from ghr_pa_request_ei_shadow
255 DELETE ghr_pa_request_ei_shadow
256 WHERE INFORMATION_TYPE = 'GHR_US_PAR_TERM_RET_GRADE'
257 AND PA_REQUEST_ID = p_pa_request_id;
258 ELSIF l_noa_code = '702' THEN
259 FOR pei_id IN c_702_rei LOOP
260 hr_utility.set_location('Inside the c_702_rei loop'||l_noa_code, 3);
261 -- Delete from ghr_pa_request_extra_info
262 DELETE ghr_pa_request_extra_info
263 WHERE INFORMATION_TYPE = 'GHR_US_PAR_TERM_RG_PROMO'
264 AND PA_REQUEST_ID = p_pa_request_id;
265
266 -- Delete from ghr_pa_request_ei_shadow
267 DELETE ghr_pa_request_ei_shadow
268 WHERE INFORMATION_TYPE = 'GHR_US_PAR_TERM_RG_PROMO'
269 AND PA_REQUEST_ID = p_pa_request_id;
270 EXIT;
271 END LOOP;
272 ELSIF l_noa_code = '740' THEN
273 hr_utility.set_location('Inside the 740 processing'||l_noa_code, 3);
274 l_existed := FALSE;
275 for pei_rec in c_740_pei loop
276 hr_utility.set_location('PEI rec id'||pei_rec.person_extra_info_id, 4);
277 for rei_rec in c_740_rei loop
278 hr_utility.set_location(' REI rec id'||rei_rec.rei_information3, 5);
279 IF pei_rec.person_extra_info_id = rei_rec.rei_information3 THEN
280 l_existed := TRUE;
281 EXIT;
282 ELSE
283 l_existed := FALSE;
284 END IF;
285 end loop;
286 IF not l_existed THEN
287 -- Insert into RPA Extra Info
288 l_rei_rec.rei_information3 := pei_rec.person_extra_info_id;
289 --l_rei_rec.rei_information4 := pei_rec.ret_grade;
290 l_rei_rec.rei_information5 := nvl(pei_rec.terminate_flag,'N'); -- Terminate Record
291 l_rei_rec.rei_information30 := pei_rec.original_rpa;
292 l_rei_rec.information_type := 'GHR_US_PAR_TERM_RG_POSN_CHG';
293 l_rei_rec.pa_request_id := p_pa_request_id;
294 l_rei_rec.rei_information7 := pei_rec.From_Date;
295 l_rei_rec.rei_information8 := pei_rec.To_Date;
296 l_rei_rec.rei_information9 := pei_rec.pei_information3;
297 l_rei_rec.rei_information10 := pei_rec.pei_information4;
298 l_rei_rec.rei_information11 := pei_rec.pei_information5;
299 l_rei_rec.rei_information12 := pei_rec.pei_information6;
300 l_rei_rec.rei_information13 := pei_rec.pei_information8;
301 l_rei_rec.rei_information14 := pei_rec.pei_information9;
302
303 --Bug#4126188 Begin
304 FOR c_posn_to_frm IN c_position LOOP
305 l_from_position_id :=c_posn_to_frm.from_position_id;
306 l_to_position_id := c_posn_to_frm.to_position_id;
307 END LOOP;
308 ghr_history_fetch.fetch_positionei
309 (p_position_id => l_from_position_id,
310 p_information_type => 'GHR_US_POS_GRP1',
311 p_date_effective => trunc(nvl(p_effective_date,sysdate)),
312 p_pos_ei_data => l_pos_ei_data
313 );
314 l_from_poid := l_pos_ei_data.poei_information3;
315
316 ghr_history_fetch.fetch_positionei
317 (p_position_id => l_to_position_id,
318 p_information_type => 'GHR_US_POS_GRP1',
319 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
320 p_pos_ei_data => l_pos_ei_data
321 );
322 l_to_poid := l_pos_ei_data.poei_information3;
323 --Begin Bug 5923620
324 IF l_from_poid = l_to_poid THEN
325 IF l_first_noa_code = '002' THEN
326 ghr_history_api.get_g_session_var(l_session);
327 l_session1 := l_session;
328 l_session.noa_id_correct := NULL;
329 ghr_history_api.reinit_g_session_var;
330 ghr_history_api.set_g_session_var(l_session);
331 ghr_history_fetch.fetch_asgei
332 (p_assignment_id => l_assignment_id,
333 p_information_type => 'GHR_US_ASG_NON_SF52',
334 p_date_effective => p_effective_date-1,
335 p_asg_ei_data => l_asg_ei_data );
336 ghr_history_api.reinit_g_session_var;
337 ghr_history_api.set_g_session_var(l_session1);
338 ELSE
339 ghr_history_fetch.fetch_asgei
340 (p_assignment_id => l_assignment_id,
341 p_information_type => 'GHR_US_ASG_NON_SF52',
342 p_date_effective => p_effective_date,
343 p_asg_ei_data => l_asg_ei_data);
344 END IF;
345 set_ei(l_org_rec.rei_information15,l_asg_ei_data.aei_information3,l_rei_rec.rei_information15,'Y');
346 --end Bug 5923620
347 ELSIF l_from_poid <> l_to_poid THEN
348 set_ei(l_org_rec.rei_information15,fnd_date.date_to_canonical(p_effective_date),
349 l_rei_rec.rei_information15,'Y');
350 END IF;
351 --Bug#4126188 End
352
353 generic_populate_extra_info
354 (p_rei_rec => l_rei_rec,
355 p_org_rec => l_org_rec,
356 p_flag => 'C'
357 );
358 l_existed := FALSE;
359 END IF;
360 end loop;
361 END IF;
362 hr_utility.set_location('Before creating RPA EI'||l_noa_code, 4);
363 IF l_noa_code = '702' THEN
364 FOR pei_id IN c_702 LOOP
365 hr_utility.set_location('In side creation of RPA EI'||l_noa_code, 4);
366 l_rei_rec.rei_information3 := pei_id.person_extra_info_id;
367 --l_rei_rec.rei_information4 := pei_id.ret_grade;
368 l_rei_rec.rei_information5 := null;
369 l_rei_rec.information_type := 'GHR_US_PAR_TERM_RG_PROMO';
370 l_rei_rec.pa_request_id := p_pa_request_id;
371 l_rei_rec.rei_information8 := pei_id.From_Date;
372 l_rei_rec.rei_information9 := pei_id.To_Date;
373 l_rei_rec.rei_information10 := pei_id.pei_information3;
374 l_rei_rec.rei_information11 := pei_id.pei_information4;
375 l_rei_rec.rei_information12 := pei_id.pei_information5;
376 l_rei_rec.rei_information13 := pei_id.pei_information6;
377 l_rei_rec.rei_information14 := pei_id.pei_information8;
378 l_rei_rec.rei_information15 := pei_id.pei_information9;
379
380
381 generic_populate_extra_info
382 (p_rei_rec => l_rei_rec,
383 p_org_rec => l_org_rec,
384 p_flag => 'C'
385 );
386 END LOOP;
387 ELSIF l_noa_code IN ('866', '890') THEN
388 IF l_altered_pa_request_id is not null THEN
389 FOR pei_id IN c_866_rei_correct LOOP
390 hr_utility.set_location('866 rei correct ' , 1);
391 l_ret_grade1 := pei_id.pei_information1 || '..'|| pei_id.pei_information2 ||
392 '..' || pei_id.pei_information3 || '..' || pei_id.pei_information4 ||
393 '..' || pei_id.pei_information5 || '..' || pei_id.pei_information6 ||
394 '..' || pei_id.pei_information7 || '..' || pei_id.pei_information8
395 || '..' || pei_id.pei_information9;
396 l_rei_rec.rei_information3 := pei_id.person_extra_info_id;
397 l_rei_rec.rei_information4 := l_ret_grade1;
398 l_rei_rec.rei_information5 := null;
399 l_rei_rec.rei_information6 := pei_id.pei_information1;
400 l_rei_rec.rei_information7 := pei_id.pei_information2;
401 l_rei_rec.rei_information8 := pei_id.pei_information3;
402 l_rei_rec.rei_information9 := pei_id.pei_information4;
403 l_rei_rec.rei_information10 := pei_id.pei_information5;
404 l_rei_rec.rei_information11 := pei_id.pei_information6;
405 l_rei_rec.rei_information12 := pei_id.pei_information7;
406 l_rei_rec.rei_information13 := pei_id.pei_information8;
407 l_rei_rec.rei_information14 := pei_id.pei_information9;
408 l_rei_rec.information_type := 'GHR_US_PAR_TERM_RET_GRADE';
409 l_rei_rec.pa_request_id := p_pa_request_id;
410 generic_populate_extra_info
411 (p_rei_rec => l_rei_rec,
412 p_org_rec => l_org_rec,
413 p_flag => 'C'
414 );
415 END LOOP;
416 ELSE
417 ---------- Bug# 2569180 To avoid PL/SQL Error
418 BEGIN
419 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details
420 (p_person_id => p_person_id,
421 p_effective_date => p_effective_date,
422 p_pa_request_id => p_pa_request_id
423 );
424 EXCEPTION
425 WHEN ghr_pay_calc.pay_calc_message THEN
426 NULL;
427 END;
428 ---------- Bug# 2569180 To avoid PL/SQL Error
429
430 hr_utility.set_location('866 non correct ' , 2);
431 FOR pei_id IN c_866(l_retained_grade_rec.person_extra_info_id) LOOP
432 l_ret_grade1 := pei_id.pei_information1 || '..'|| pei_id.pei_information2 ||
433 '..' || pei_id.pei_information3 || '..' || pei_id.pei_information4 ||
434 '..' || pei_id.pei_information5 || '..' || pei_id.pei_information6 ||
435 '..' || pei_id.pei_information7 || '..' || pei_id.pei_information8
436 || '..' || pei_id.pei_information9;
437
438 l_rei_rec.rei_information3 := pei_id.person_extra_info_id;
439 l_rei_rec.rei_information4 := l_ret_grade1;
440 l_rei_rec.rei_information5 := null;
441 l_rei_rec.rei_information6 := pei_id.pei_information1;
442 l_rei_rec.rei_information7 := pei_id.pei_information2;
443 l_rei_rec.rei_information8 := pei_id.pei_information3;
444 l_rei_rec.rei_information9 := pei_id.pei_information4;
445 l_rei_rec.rei_information10 := pei_id.pei_information5;
446 l_rei_rec.rei_information11 := pei_id.pei_information6;
447 l_rei_rec.rei_information12 := pei_id.pei_information7;
448 l_rei_rec.rei_information13 := pei_id.pei_information8;
449 l_rei_rec.rei_information14 := pei_id.pei_information9;
450 l_rei_rec.information_type := 'GHR_US_PAR_TERM_RET_GRADE';
451 l_rei_rec.pa_request_id := p_pa_request_id;
452 generic_populate_extra_info
453 (p_rei_rec => l_rei_rec,
454 p_org_rec => l_org_rec,
455 p_flag => 'C'
456 );
457 END LOOP;
458 END IF;
459 END IF;
460 END IF;
461 END term_ret_grade;
462
463 Procedure populate_noa_spec_extra_info
464 (p_pa_request_id in number ,
465 p_first_noa_id in number ,
466 p_second_noa_id in number ,
467 p_person_id in per_people_f.person_id%type ,
468 p_assignment_id in per_assignments_f.assignment_id%type ,
469 p_position_id in per_positions.position_id%type ,
470 p_effective_date in ghr_pa_requests.effective_date%type ,
471 p_refresh_flag in varchar2 default 'Y'
472 )
473 is
474 l_proc varchar2(72) := g_package || 'populate_noa_spec_extra_info';
475 l_information_type ghr_pa_request_extra_info.information_type%type;
476 l_dum_information_type ghr_pa_request_extra_info.information_type%type;
477 l_flag varchar2(1);
478 l_rei_rec ghr_pa_request_extra_info%rowtype;
479 l_org_rec ghr_pa_request_ei_shadow%rowtype;
480 l_new_rei_rec ghr_pa_request_extra_info%rowtype;
481 l_refresh_flag varchar2(1);
482
483 cursor c_rei_rec is
484 select rei.pa_request_extra_info_id,
485 rei.object_version_number
486 from ghr_pa_request_extra_info rei
487 where rei.pa_request_id = p_pa_request_id
488 and rei.information_type NOT IN ('GHR_US_PAR_PAYROLL_TYPE',
489 'GHR_US_PAR_PERF_APPRAISAL',
490 'GHR_US_PAR_GEN_AGENCY_DATA',
491 'GHR_US_PD_GEN_EMP')
492 and rei.information_type not in
493 (Select pit.information_type
494 from ghr_pa_request_info_types pit,
495 ghr_noa_families nfa,
496 ghr_families fam
497 where nfa.nature_of_action_id in (p_first_noa_id, p_second_noa_id)
498 and nfa.noa_family_code = fam.noa_family_code
499 and fam.pa_info_type_flag = 'Y'
500 and pit.noa_family_code = fam.noa_family_code);
501 ---- and pit.information_type LIKE 'GHR_US%');
502
503 cursor c_noa_code(p_noa_id in ghr_pa_requests.first_noa_id%type) is
504 select code from ghr_nature_of_actions
505 where nature_of_action_id = p_noa_id;
506
507 cursor c_altered_par_rec is
508 select altered_pa_request_id from ghr_pa_requests
509 where pa_request_id = p_pa_request_id;
510
511 l_noa_code ghr_nature_of_actions.code%type;
512 l_first_noa_code ghr_nature_of_actions.code%type;
513 l_second_noa_code ghr_nature_of_actions.code%type;
514 l_altered_pa_request_id ghr_pa_requests.pa_request_id%type;
515
516 cursor c_orig_rei_rec is
517 select *
518 from ghr_pa_request_extra_info a
519 where a.pa_request_id = l_altered_pa_request_id
520 and a.information_type like 'GHR_US%'
521 and a.information_type not in (
522 select information_type from
523 ghr_pa_request_extra_info b
524 where b.pa_request_id = p_pa_request_id );
525
526 Begin
527 hr_utility.set_location('Entering ' || l_proc,5);
528 FOR noa_code IN c_noa_code(p_first_noa_id) LOOP
529 l_first_noa_code := noa_code.code;
530 exit;
531 END LOOP;
532 FOR noa_code IN c_noa_code(p_second_noa_id) LOOP
533 l_second_noa_code := noa_code.code;
534 exit;
535 END LOOP;
536 hr_utility.set_location('first noa code '||l_first_noa_code , 1);
537 hr_utility.set_location('second noa code '||l_second_noa_code, 2);
538 IF l_first_noa_code = '002' THEN
539 l_noa_code := l_second_noa_code;
540 ELSE
541 l_noa_code := l_first_noa_code;
542 END IF;
543 hr_utility.set_location('l_noa_code is '||l_noa_code, 2);
544 IF (l_second_noa_code in ('702','866','890','740') and l_first_noa_code = '002' ) OR
545 l_first_noa_code in ('702','866','890','740')
546 THEN
547 -- New Processing for Termination of Retain Grade
548 -- Start
549 term_ret_grade(p_pa_request_id => p_pa_request_id,
550 p_person_id => p_person_id,
551 p_first_noa_id => p_first_noa_id,
552 p_second_noa_id => p_second_noa_id,
553 p_effective_date => p_effective_date,
554 p_refresh_flag => p_refresh_flag );
555 -- End
556 END IF;
557 If p_first_noa_id is not null then
558 hr_utility.set_location(l_proc,30);
559 hr_utility.set_location('PER ID'||p_person_id,30);
560 --
561 fetch_noa_spec_extra_info
562 (p_pa_request_id => p_pa_request_id,
563 p_noa_id => p_first_noa_id,
564 p_person_id => p_person_id,
565 p_assignment_id => p_assignment_id,
566 p_position_id => p_position_id,
567 p_effective_date => trunc(nvl(p_effective_date,sysdate)),
568 p_refresh_flag => p_refresh_flag
569 );
570 hr_utility.set_location(l_proc,65);
571 End if;
572
573 hr_utility.set_location('PER ID' || p_person_id,65);
574 hr_utility.set_location('Second NOa ID ' || p_second_noa_id,65);
575 If p_second_noa_id is not null then
576 --Bug#4089400 commented second-noa-code not in 702,866,740 clause
577 IF l_first_noa_code = '002' then --(l_second_noa_code not in ('702','866','740')) and
578 for altered_par_rec in c_altered_par_rec loop
579 l_altered_pa_request_id := altered_par_rec.altered_pa_request_id;
580 end loop;
581 hr_utility.set_location('Creation of new RPA EI Records' ,67);
582 -- Fetch the original rpa extra information and create new RPA Extra info records
583 -- with current PA request id
584 FOR orig_rei_rec IN c_orig_rei_rec LOOP
585 l_new_rei_rec := orig_rei_rec;
586 l_new_rei_rec.pa_request_id := p_pa_request_id;
587 l_new_rei_rec.pa_request_extra_info_id := NULL;
588 hr_utility.set_location('Creation of new RPA EI Records'||l_new_rei_rec.information_type ,65);
589 generic_populate_extra_info
590 (p_rei_rec => l_new_rei_rec,
591 p_org_rec => l_org_rec,
592 p_flag => 'C'
593 );
594 END LOOP;
595
596 end if;
597 fetch_noa_spec_extra_info
598 (p_pa_request_id => p_pa_request_id,
599 p_noa_id => p_second_noa_id,
600 p_person_id => p_person_id,
601 p_assignment_id => p_assignment_id,
602 p_position_id => p_position_id,
603 p_effective_date => trunc(nvl(p_effective_date,sysdate)),
604 p_refresh_flag => p_refresh_flag
605 );
606 hr_utility.set_location(l_proc,150);
607 End if;
608 --
609 -- delete all the extra info records which are not required for the new noa code
610 --
611 for rei_rec in c_rei_rec loop
612 l_rei_rec.pa_request_extra_info_id := rei_rec.pa_request_extra_info_id ;
613 l_rei_rec.object_version_number := rei_rec.object_version_number ;
614 l_flag := 'D';
615 hr_utility.set_location(l_proc,160);
616 generic_populate_extra_info
617 (p_rei_rec => l_rei_rec,
618 p_org_rec => l_org_rec,
619 p_flag => l_flag
620 );
621 end loop;
622 --
623 hr_utility.set_location('Leaving ' || l_proc,135);
624 --
625 End populate_noa_spec_extra_info;
626
627
628 Procedure fetch_noa_spec_extra_info
629 (p_pa_request_id in number,
630 p_noa_id in number,
631 p_person_id in per_people_f.person_id%type,
632 p_assignment_id in per_assignments_f.assignment_id%type,
633 p_position_id in per_positions.position_id%type,
634 p_effective_date in ghr_pa_requests.effective_date%type,
635 p_refresh_flag in varchar2 default 'Y'
636 )
637 is
638
639 l_per_ei_data per_people_extra_info%rowtype;
640 l_asg_ei_data per_assignment_extra_info%rowtype;
641 l_pos_ei_data per_position_extra_info%rowtype;
642 l_multiple_error_flag boolean;
643 l_noa_id ghr_nature_of_actions.nature_of_action_id%type;
644 l_proc varchar2(72):= g_package || 'noa_spec_extra_info';
645 l_information_type ghr_pa_request_info_types.information_type%type;
646 l_update_rei varchar2(1) := 'N';
647 l_exists boolean := FALSE;
648 l_value varchar2(30);
649 l_rei_rec ghr_pa_request_extra_info%rowtype; -- as in the ddf and then subsequently overwrittwn with data to be updated
650 l_org_rec ghr_pa_request_ei_shadow%rowtype; -- original from the duplicate table
651 l_old_rei_rec ghr_pa_request_extra_info%rowtype;
652 l_flag varchar2(1);
653 l_refresh_flag varchar2(1);
654 l_rei_rec_exists varchar2(1);
655 l_person_id per_people_f.person_id%type;
656 l_assignment_id per_assignments_f.assignment_id%type;
657 l_ret_review_date varchar2(30);
658 l_eff_date date;
659 l_position_id per_positions.position_id%type;
660 l_per_refresh_flag varchar2(1);
661 l_asg_refresh_flag varchar2(1);
662 l_pos_refresh_flag varchar2(1);
663 -- Bug#4089400
664 l_noa_family_code ghr_noa_families.noa_family_code%type;
665 -- Bug#5039072 Added the following two parameters.
666 l_first_noa_code ghr_pa_requests.first_noa_code%type;
667 l_la_code1 ghr_pa_requests.first_action_la_code1%type;
668 l_payment_option ghr_pa_requests.pa_incentive_payment_option%type;
669 l_application_id fnd_application.application_id%type;
670 l_resp_id fnd_responsibility.responsibility_id%type;
671
672 -- Begin Bug#4126188
673 l_from_position_id ghr_pa_requests.from_position_id%type;
674 l_to_position_id ghr_pa_requests.to_position_id%type;
675 l_second_noa_code ghr_pa_requests.second_noa_code%type;
676 l_from_poid ghr_pa_requests.personnel_office_id%type;
677 l_to_poid ghr_pa_requests.personnel_office_id%type;
678 l_session ghr_history_api.g_session_var_type;
679 l_session1 ghr_history_api.g_session_var_type;
680
681 CURSOR c_pa_req_2noa_dtls is
682 SELECT second_noa_code
683 FROM ghr_pa_requests
684 WHERE pa_request_id = p_pa_request_id;
685 -- End Bug#4126188
686
687 cursor c_rei_rec is
688 select pa_request_extra_info_id,
689 rei_information1,
690 rei_information2,
691 rei_information3,
692 rei_information4,
693 rei_information5,
694 rei_information6,
695 rei_information7,
696 rei_information8,
697 rei_information9,
698 rei_information10,
699 rei_information11,
700 rei_information12,
701 rei_information13,
702 rei_information14,
703 rei_information15,
704 rei_information16,
705 rei_information17,
706 rei_information18,
707 rei_information19,
708 rei_information20,
709 rei_information21,
710 rei_information22,
711 rei_information23,
712 rei_information24,
713 rei_information25,
714 rei_information26,
715 rei_information27,
716 rei_information28,
717 rei_information29,
718 rei_information30,
719 object_version_number
720 from ghr_pa_request_extra_info
721 where pa_request_id = p_pa_request_id
722 and information_type = l_information_type;
723
724 cursor c_org_rei_rec is
725 select pa_request_extra_info_id,
726 rei_information1,
727 rei_information2,
728 rei_information3,
729 rei_information4,
730 rei_information5,
731 rei_information6,
732 rei_information7,
733 rei_information8,
734 rei_information9,
735 rei_information10,
736 rei_information11,
737 rei_information12,
738 rei_information13,
739 rei_information14,
740 rei_information15,
741 rei_information16,
742 rei_information17,
743 rei_information18,
744 rei_information19,
745 rei_information20,
746 rei_information21,
747 rei_information22,
748 rei_information23,
749 rei_information24,
750 rei_information25,
751 rei_information26,
752 rei_information27,
753 rei_information28,
754 rei_information29,
755 rei_information30
756 from ghr_pa_request_ei_shadow
757 where pa_request_extra_info_id = l_rei_rec.pa_request_extra_info_id;
758
759 -- Bug#3941541 Added effective date condition to the cursor.
760 Cursor c_info_types(c_application_id fnd_application.application_id%type,c_resp_id fnd_responsibility.responsibility_id%type) is
761 Select pit.information_type
762 from ghr_pa_request_info_types pit,
763 ghr_noa_families nfa,
764 ghr_families fam
765 where nfa.nature_of_action_id = p_noa_id
766 and nfa.noa_family_code = fam.noa_family_code
767 and p_effective_date between NVL(nfa.start_date_active,p_effective_date)
768 and NVL(nfa.end_date_active,p_effective_date)
769 and fam.pa_info_type_flag = 'Y'
770 and pit.noa_family_code = fam.noa_family_code
771 and pit.information_type like 'GHR_US%'
772 --Bug#3942126 Added the following clause.
773 and pit.active_inactive_flag = 'Y'
774 and pit.information_type IN
775 (SELECT information_type
776 from per_info_type_security
777 where application_id = c_application_id
778 and responsibility_id = c_resp_id);
779
780
781 cursor c_get_effective_date is
782 Select effective_date
783 from ghr_pa_requests
784 where pa_request_id = p_pa_request_id;
785
786 -- Bug#4089400
787 cursor c_noa_fam_code is
788 select par.noa_family_code noa_family_code,
789 par.first_noa_code first_noa_code,
790 par.first_action_la_code1 la_code1,
791 par.pa_incentive_payment_option payment_option
792 from ghr_pa_requests par
793 where pa_request_id = p_pa_request_id;
794
795 cursor c_noac_1xx is
796 SELECT par.effective_date
797 FROM ghr_nature_of_actions noa,
798 ghr_pa_requests par,
799 ghr_pay_plans pp
800 WHERE noa.nature_of_action_id = par.first_noa_id
801 and par.pa_request_id = p_pa_request_id
802 and pp.pay_plan = par.to_pay_plan
803 and pp.equivalent_pay_plan in ('GS','FW')
804 and substr(noa.code,1,1) = '1'
805 AND noa.code <> '130';
806
807 cursor c_noac_5xx is
808 SELECT par.effective_date
809 FROM ghr_nature_of_actions noa,
810 ghr_pa_requests par,
811 ghr_pay_plans pp
812 WHERE noa.nature_of_action_id = par.first_noa_id
813 and par.pa_request_id = p_pa_request_id
814 and pp.pay_plan = par.to_pay_plan
815 and pp.equivalent_pay_plan in ('GS','FW')
816 and substr(noa.code,1,1) = '5'
817 AND noa.code not in ('542','543','546','548','549');
818
819 cursor c_noac_130 is
820 SELECT par.effective_date
821 FROM ghr_nature_of_actions noa,
822 ghr_pa_requests par,
823 ghr_pay_plans pp
824 WHERE noa.nature_of_action_id = par.first_noa_id
825 and par.pa_request_id = p_pa_request_id
826 and pp.pay_plan = par.to_pay_plan
827 and pp.equivalent_pay_plan in ('GS','FW')
828 AND noa.code = '130'
829 AND par.first_action_la_code1 = 'KVM';
830
831 cursor c_noac_sal_chg is
832 SELECT par.effective_date
833 FROM ghr_nature_of_actions noa,
834 ghr_pa_requests par,
835 ghr_pay_plans pp
836 WHERE noa.nature_of_action_id = par.first_noa_id
837 AND par.pa_request_id = p_pa_request_id
838 AND pp.pay_plan = par.to_pay_plan
839 AND pp.equivalent_pay_plan in ('GS','FW')
840 AND code in ('702','703','891','893');
841
842 -- Bug#5657744 Created the cursor
843 CURSOR c_afhr_noac_sal_chg IS
844 SELECT par.effective_date
845 FROM ghr_pa_requests par
846 WHERE par.pa_request_id = p_pa_request_id
847 AND par.first_noa_code IN ('890','891','892','893');
848
849
850 cursor c_pp_and_grade is
851 SELECT to_pay_plan,to_grade_or_level,
852 from_pay_plan,from_grade_or_level
853 FROM ghr_pa_requests
854 WHERE pa_request_id = p_pa_request_id;
855
856 -- Bug 4280026
857 cursor c_position is
858 SELECT from_position_id,to_position_id
859 FROM ghr_pa_requests
860 WHERE pa_request_id = p_pa_request_id;
861
862
863 l_dummy varchar2(1);
864 l_dlei_date date := null;
865 -- Bug#5657744
866 l_psi VARCHAR2(10);
867
868 cursor c_req_num is
869 select request_number from ghr_pa_requests
870 where pa_request_id = p_pa_request_id;
871 /*Start - BUG 6129752-DATE ARRIVED PERSONNEL OFFICE CHANGING TO WRONG DATE ON CORRECTION TO PROMOTION
872 Following cursor has been added to fetch assignment id when p_assignment_id is null. */
873
874 CURSOR GET_ASSIGNEMNT_ID IS
875 SELECT assignment_id
876 FROM per_all_assignments_f
877 WHERE person_id = p_person_id
878 AND p_effective_date between effective_start_date AND effective_end_date;
879 /*End Bug -6129752 */
880 -- populate rei_information with appt_info
881
882 Procedure appt_info is
883 Begin
884
885 -- Read from history if person id is not null
886 hr_utility.set_location('Person inside appt_info is ' || to_char(l_person_id),1);
887 If l_person_id is not null then
888 hr_utility.set_location('Person inside appt_info is ' || to_char(l_person_id),1);
889 -- if the person_id is not the same as the old person_id then do the foll.
890 -- If l_person_id <> nvl(ghr_par_shd.g_old_rec.person_id,hr_api.g_number) then
891 -- a) Get PER_GROUP1 Information
892 l_refresh_flag := l_per_refresh_flag;
893 ghr_history_fetch.fetch_peopleei
894 (p_person_id => l_person_id,
895 p_information_type => 'GHR_US_PER_GROUP1',
896 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
897 p_per_ei_data => l_per_ei_data
898 );
899 -- set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information3,l_rei_rec.rei_information3,l_refresh_flag);
900 -- l_org_rec.rei_information3 := l_per_ei_data.pei_information3;
901 -- Bug 1371770
902 IF l_per_ei_data.pei_information11 is null THEN
903 l_rei_rec.rei_information8 := '05';
904 ELSE
905 set_ei(l_org_rec.rei_information8,l_per_ei_data.pei_information11,l_rei_rec.rei_information8,l_refresh_flag);
906 l_org_rec.rei_information8 := l_per_ei_data.pei_information11;
907 END IF;
908 /*
909 set_ei(l_org_rec.rei_information10,l_per_ei_data.pei_information8,l_rei_rec.rei_information10,l_refresh_flag);
910 l_org_rec.rei_information10 := l_per_ei_data.pei_information8;
911 set_ei(l_org_rec.rei_information11,l_per_ei_data.pei_information9,l_rei_rec.rei_information11,l_refresh_flag);
912 l_org_rec.rei_information11 := l_per_ei_data.pei_information9;
913 */
914 set_ei(l_org_rec.rei_information16,l_per_ei_data.pei_information5,l_rei_rec.rei_information16,l_refresh_flag);
915 l_org_rec.rei_information16 := l_per_ei_data.pei_information5;
916 set_ei(l_org_rec.rei_information17,l_per_ei_data.pei_information4,l_rei_rec.rei_information17,l_refresh_flag);
917 l_org_rec.rei_information17 := l_per_ei_data.pei_information4;
918
919 l_per_ei_data := null;
920
921 -- b) Get PER_UNIFORMED_SERVICES Information
922 ghr_history_fetch.fetch_peopleei
923 (p_person_id => l_person_id,
924 p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES',
925 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
926 p_per_ei_data => l_per_ei_data
927 );
928 set_ei(l_org_rec.rei_information4,l_per_ei_data.pei_information5,l_rei_rec.rei_information4,l_refresh_flag);
929 l_org_rec.rei_information4 := l_per_ei_data.pei_information5;
930 l_per_ei_data := null;
931
932 -- c) Get PER_SEPARATE_RETIRE Information
933 ghr_history_fetch.fetch_peopleei
934 (p_person_id => l_person_id,
935 p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
936 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
937 p_per_ei_data => l_per_ei_data
938 );
939 hr_utility.set_location('per_sep_retire' ,1);
940 set_ei(l_org_rec.rei_information7,l_per_ei_data.pei_information5,l_rei_rec.rei_information7,l_refresh_flag);
941 hr_utility.set_location('per_sep_retire' || l_rei_rec.rei_information7 ,2);
942 l_org_rec.rei_information7 := l_per_ei_data.pei_information5;
943 set_ei(l_org_rec.rei_information14,l_per_ei_data.pei_information4,l_rei_rec.rei_information14,l_refresh_flag);
944 l_org_rec.rei_information14 := l_per_ei_data.pei_information4;
945 set_ei(l_org_rec.rei_information19,l_per_ei_data.pei_information3,l_rei_rec.rei_information19,l_refresh_flag);
946 l_org_rec.rei_information19 := l_per_ei_data.pei_information3;
947
948 l_per_ei_data := null;
949
950
951
952
953
954
955
956
957
958 IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
959 -- Get PER_LEAVE Information
960 hr_utility.set_location('per_leave_info' ,1);
961 ghr_history_fetch.fetch_peopleei
962 (p_person_id => l_person_id,
963 p_information_type => 'GHR_US_PER_LEAVE_INFO',
964 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
965 p_per_ei_data => l_per_ei_data
966 );
967 IF l_per_ei_data.pei_information4 is NULL THEN
968 l_rei_rec.rei_information20 := 'N';
969 ELSE
970 set_ei(l_org_rec.rei_information20,l_per_ei_data.pei_information4,l_rei_rec.rei_information20,l_refresh_flag);
971 END IF;
972 l_per_ei_data := null;
973
974 --Gain Or Lose
975 set_ei(l_org_rec.rei_information21,'1B',l_rei_rec.rei_information21,l_refresh_flag);
976 END IF;
977 End if;
978
979 If l_assignment_id is not null then
980
981 -- Get ASG_NON_SF52 data
982 l_refresh_flag := l_asg_refresh_flag;
983 ghr_history_fetch.fetch_asgei
984 (p_assignment_id => l_assignment_id,
985 p_information_type => 'GHR_US_ASG_NON_SF52',
986 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
987 p_asg_ei_data => l_asg_ei_data
988 );
989
990 -- added on 02-oct-98 by skutteti to autopopulate with the effective date
991 -- set_ei(l_org_rec.rei_information5,fnd_date.date_to_canonical(p_effective_date),
992 -- l_rei_rec.rei_information5,l_refresh_flag);
993 -- set_ei(l_org_rec.rei_information5,l_asg_ei_data.aei_information3,
994 -- l_rei_rec.rei_information5,l_refresh_flag);
995 -- l_org_rec.rei_information5 := l_asg_ei_data.aei_information3;
996
997 -- added on 3-dec-99 -- Refer bug 963634
998 for c_get_eff_rec in c_get_effective_date loop
999 set_ei(l_org_rec.rei_information5,fnd_date.date_to_canonical(c_get_eff_rec.effective_date),
1000 l_rei_rec.rei_information5,l_refresh_flag);
1001 exit;
1002 end loop;
1003
1004 set_ei(l_org_rec.rei_information9,l_asg_ei_data.aei_information6,l_rei_rec.rei_information9,l_refresh_flag);
1005 l_org_rec.rei_information9 := l_asg_ei_data.aei_information6;
1006 set_ei(l_org_rec.rei_information12,l_asg_ei_data.aei_information8,l_rei_rec.rei_information12,l_refresh_flag);
1007 l_org_rec.rei_information12 := l_asg_ei_data.aei_information8;
1008 set_ei(l_org_rec.rei_information15,l_asg_ei_data.aei_information9,l_rei_rec.rei_information15,l_refresh_flag);
1009 l_org_rec.rei_information15 := l_asg_ei_data.aei_information9;
1010 l_asg_ei_data := null;
1011 End if;
1012
1013 If l_position_id is not null then
1014 l_refresh_flag := l_pos_refresh_flag;
1015 ghr_history_fetch.fetch_positionei
1016 (p_position_id => l_position_id,
1017 p_information_type => 'GHR_US_POS_GRP1',
1018 p_date_effective => trunc(nvl(p_effective_date,sysdate)),
1019 p_pos_ei_data => l_pos_ei_data
1020 );
1021 set_ei(l_org_rec.rei_information13,l_pos_ei_data.poei_information12,l_rei_rec.rei_information13,l_refresh_flag);
1022 l_org_rec.rei_information13 := l_pos_ei_data.poei_information12;
1023 hr_utility.set_location('posei in test ei - ' || l_pos_ei_data.poei_information12,2);
1024 l_pos_ei_data := null;
1025 End if;
1026 hr_utility.set_location('Appt_info Family Code: '||l_noa_family_code,10);
1027 IF NVL(l_noa_family_code,'C') <> 'CORRECT' THEN
1028 OPEN c_noac_1xx;
1029 FETCH c_noac_1xx INTO l_dlei_date;
1030 IF l_dlei_date is not null THEN
1031 set_ei(l_org_rec.rei_information18,
1032 fnd_date.date_to_canonical(l_dlei_date),
1033 l_rei_rec.rei_information18,'Y');
1034 ELSE
1035 set_ei(l_org_rec.rei_information18,
1036 null,l_rei_rec.rei_information18,'Y');
1037 END IF;
1038 CLOSE c_noac_1xx;
1039 END IF;
1040 End appt_info;
1041
1042 Procedure appt_benefits is
1043 l_tenure ghr_pa_requests.tenure%type;
1044 l_retirement_plan ghr_pa_requests.retirement_plan%type;
1045 l_noa_code ghr_pa_requests.first_noa_code%type;
1046 l_fegli ghr_pa_requests.fegli%type;
1047 l_prev_fegli ghr_pa_requests.fegli%type;
1048 l_scd ghr_pa_requests.SERVICE_COMP_DATE%type;
1049 l_payroll_id pay_payrolls_f.payroll_id%type;
1050 l_pa_request_id ghr_pa_requests.pa_request_id%type;
1051 l_effective_date ghr_pa_requests.effective_date%type;
1052 l_start_date per_time_periods.start_date%type;
1053 l_family_code ghr_pa_requests.noa_family_code%type;
1054 l_new_element_name pay_element_types_f.element_name%type;
1055 l_bus_group_id per_all_people_f.business_group_id%type;
1056 l_fehb_exists BOOLEAN;
1057 l_tsp_exists BOOLEAN;
1058 l_health_plan pay_element_entry_values_f.screen_entry_value%type;
1059 l_enrollment_option pay_element_entry_values_f.screen_entry_value%type;
1060 l_pre_tax_waiver pay_element_entry_values_f.screen_entry_value%type;
1061 l_date_temp_elig date;
1062 l_tsp_status pay_element_entry_values_f.screen_entry_value%type;
1063 l_tsp_status_date date;
1064 l_agency_contrib_date date;
1065 l_second_noa_code ghr_pa_requests.second_noa_code%type;
1066
1067 CURSOR c_pa_req_details is
1068 SELECT *
1069 FROM ghr_pa_requests
1070 WHERE pa_request_id = p_pa_request_id;
1071
1072 CURSOR c_payroll_id(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
1073 SELECT rei_information3 payroll_id
1074 FROM ghr_pa_request_extra_info
1075 WHERE pa_request_id = c_pa_request_id
1076 AND information_type = 'GHR_US_PAR_PAYROLL_TYPE';
1077
1078 CURSOR c_start_date(c_payroll_id pay_payrolls_f.payroll_id%type, c_year varchar2, c_month varchar2) IS
1079 SELECT min(start_date) start_date
1080 FROM per_time_periods
1081 WHERE payroll_id = c_payroll_id
1082 AND TO_CHAR(start_date,'YYYY') = c_year
1083 AND TO_CHAR(start_date,'MM') = c_month;
1084
1085 CURSOR c_element_entry(c_assignment_id pay_element_entries_f.assignment_id%type,
1086 c_element_name pay_element_types_f.element_name%type,
1087 c_effective_date pay_element_entries_f.effective_start_date%type) IS
1088 SELECT 1
1089 FROM pay_element_entries_f ele
1090 WHERE ele.assignment_id = c_assignment_id
1091 AND c_effective_date BETWEEN ele.effective_start_date AND effective_end_date
1092 AND ele.element_type_id = (SELECT elt.element_type_id
1093 FROM pay_element_types_f elt
1094 WHERE element_name = c_element_name
1095 AND c_effective_date BETWEEN elt.effective_start_date AND elt.effective_end_date);
1096
1097 CURSOR c_element_value (c_element_name pay_element_types_f.element_name%type,
1098 c_assignment_id per_all_assignments_f.assignment_id%type,
1099 c_effective_date pay_element_entries_f.effective_start_date%type
1100 ) is
1101 SELECT a.element_name element_name,
1102 b.NAME ipv_name,
1103 f.input_value_id input_value_id,
1104 e.effective_start_date effective_start_date,
1105 e.effective_end_date effective_end_date,
1106 e.element_entry_id element_entry_id,
1107 e.assignment_id assignment_id,
1108 e.object_version_number object_version_number,
1109 f.element_entry_value_id element_entry_value_id,
1110 f.screen_entry_value screen_entry_value
1111 FROM pay_element_types_f a,
1112 pay_input_values_f b,
1113 pay_element_entries_f e,
1114 pay_element_entry_values_f f
1115 WHERE a.element_type_id = b.element_type_id AND
1116 e.element_type_id = a.element_type_id AND
1117 f.element_entry_id = e.element_entry_id AND
1118 f.input_value_id = b.input_value_id AND
1119 e.assignment_id = c_assignment_id AND a.element_name = c_element_name AND
1120 c_effective_date BETWEEN e.effective_start_date AND e.effective_end_date AND
1121 /*Start Bug#6082557 Added three more condition in the where clause.*/
1122 c_effective_date BETWEEN b.effective_start_date AND b.effective_end_date AND
1123 c_effective_date BETWEEN a.effective_start_date AND a.effective_end_date AND
1124 c_effective_date BETWEEN f.effective_start_date AND f.effective_end_date;
1125 /*End Bug#6082557 */
1126
1127 BEGIN
1128 l_noa_code := NULL;
1129 FOR pa_rec in c_pa_req_details LOOP
1130 l_noa_code := pa_rec.first_noa_code;
1131 l_family_code := pa_rec.noa_family_code;
1132 l_tenure := pa_rec.tenure;
1133 l_retirement_plan := pa_rec.retirement_plan;
1134 l_fegli := pa_rec.fegli;
1135 l_scd := pa_rec.service_comp_date;
1136 l_pa_request_id := pa_rec.pa_request_id;
1137 l_effective_date := NVL(pa_rec.effective_date,SYSDATE);
1138 l_person_id := pa_rec.person_id;
1139 l_second_noa_code := pa_rec.second_noa_code;
1140 END LOOP;
1141
1142 l_fehb_exists := FALSE;
1143 l_tsp_exists := FALSE;
1144 IF l_second_noa_code IS NULL THEN
1145 IF l_family_code = 'APP' THEN
1146
1147 -- Date FEHB Eligibility Expires
1148 -- Enrollment
1149 -- Pre Tax Waiver
1150 IF l_noa_code in
1151 ( '100', '101', '107', '108', '120',
1152 '124', '140', '141',
1153 '142', '143', '146',
1154 '148','170', '190' ) THEN
1155 set_ei(l_org_rec.rei_information3,
1156 fnd_date.date_to_canonical(nvl(p_effective_date,sysdate)+60),
1157 l_rei_rec.rei_information3,'Y');
1158 set_ei(l_org_rec.rei_information6, 'X',
1159 l_rei_rec.rei_information6,'Y');
1160 ELSE
1161 set_ei(l_org_rec.rei_information3,
1162 NULL,l_rei_rec.rei_information3,'Y');
1163 set_ei(l_org_rec.rei_information6, 'Z',
1164 l_rei_rec.rei_information6,'Y');
1165 END IF; -- IF l_noa_code in
1166
1167 -- Pre tax waiver -- Bug 4653096
1168 IF l_noa_code in
1169 ( '100', '101', '107', '108', '120',
1170 '124', '130','132', '140', '141',
1171 '142', '143', '145','146', '147',
1172 '148','170', '190' ) THEN
1173 set_ei(l_org_rec.rei_information9, 'N',
1174 l_rei_rec.rei_information9,'Y');
1175 ELSE
1176 set_ei(l_org_rec.rei_information9,NULL,
1177 l_rei_rec.rei_information9,'Y');
1178 END IF;
1179
1180
1181 -- Health Plan
1182 set_ei(l_org_rec.rei_information5, 'ZZ',
1183 l_rei_rec.rei_information5,'Y');
1184 -- Date Temp Eligibility FEHB
1185 IF l_noa_code in
1186 ( '115', '122', '149', '171') and
1187 nvl(l_tenure,hr_api.g_varchar2) = '0' THEN
1188 set_ei(l_org_rec.rei_information4,
1189 fnd_date.date_to_canonical(add_months(nvl(p_effective_date,TRUNC(sysdate)),12)+1),
1190 l_rei_rec.rei_information4,'Y');
1191 ELSE
1192 set_ei(l_org_rec.rei_information4,
1193 NULL,l_rei_rec.rei_information4,'Y');
1194 END IF; -- IF l_noa_code in Date Temp Eligibility FEHB
1195
1196 -- FEGLI Eligibility Expiration
1197 IF nvl(l_fegli,hr_api.g_varchar2) <> 'A0' AND
1198 l_noa_code NOT IN ('130','132','145','147','115', '122', '149', '171') THEN -- Bug 4669419
1199 set_ei(l_org_rec.rei_information10,
1200 fnd_date.date_to_canonical(nvl(p_effective_date,TRUNC(sysdate))+31),
1201 l_rei_rec.rei_information10,'Y');
1202 ELSE
1203 set_ei(l_org_rec.rei_information10,NULL,
1204 l_rei_rec.rei_information10,'Y');
1205 END IF;
1206 -- TSP SCD
1207 IF nvl(l_retirement_plan,hr_api.g_varchar2)
1208 in ('D','K','L','M','N','P') THEN
1209 set_ei(l_org_rec.rei_information12,
1210 fnd_date.date_to_canonical(nvl(l_scd,TRUNC(sysdate))),
1211 l_rei_rec.rei_information12,'Y');
1212 ELSE
1213 set_ei(l_org_rec.rei_information12,NULL,
1214 l_rei_rec.rei_information12,'Y');
1215 END IF; -- IF nvl(l_retirement_pl
1216 -- TSP Status
1217 IF nvl(l_retirement_plan,hr_api.g_varchar2)
1218 in ('D','K','L','M','N','P') THEN
1219 set_ei(l_org_rec.rei_information15,'I',
1220 l_rei_rec.rei_information15,'Y');
1221 ELSIF nvl(l_retirement_plan,hr_api.g_varchar2)
1222 in ('2','4','5') THEN
1223 set_ei(l_org_rec.rei_information15,NULL,
1224 l_rei_rec.rei_information15,'Y');
1225 ELSIF nvl(l_retirement_plan,hr_api.g_varchar2)
1226 in ('1','3','8','9','C','E','F','G','H','R','T',
1227 'W' ) THEN
1228 set_ei(l_org_rec.rei_information15,'E',
1229 l_rei_rec.rei_information15,'Y');
1230 ELSE
1231 set_ei(l_org_rec.rei_information15,NULL,
1232 l_rei_rec.rei_information15,'Y');
1233 END IF; -- IF nvl(l_retirement_plan,hr_api.
1234 -- TSP Status Date
1235 IF NVL(l_retirement_plan,hr_api.g_varchar2)
1236 NOT IN ('2','4','5') THEN
1237 set_ei(l_org_rec.rei_information16,fnd_date.date_to_canonical(l_effective_date),
1238 l_rei_rec.rei_information16,'Y');
1239 set_ei(l_org_rec.rei_information18,NULL,l_rei_rec.rei_information18,'Y'); -- TSP Emp Contrib Elig Date
1240 ELSE
1241 set_ei(l_org_rec.rei_information16,NULL, l_rei_rec.rei_information16,'Y');
1242 set_ei(l_org_rec.rei_information18,NULL,l_rei_rec.rei_information18,'Y'); -- TSP Emp Contrib Elig Date
1243 END IF; -- -- TSP Status Date
1244
1245 -- TSP Agency Contrib Elig Date
1246 IF NVL(l_retirement_plan,hr_api.g_varchar2) IN ('D', 'K', 'L', 'M', 'N', 'P') THEN
1247 -- Get Payroll ID
1248 FOR l_cur_payroll_id IN c_payroll_id(l_pa_request_id) LOOP
1249 l_payroll_id := l_cur_payroll_id.payroll_id;
1250 END LOOP;
1251 /*Bug#6312182 Changed the logic to determine TSP Agency Contribution Eligibilty date.*/
1252 IF to_number(to_char(l_effective_date,'MM')) BETWEEN 6 AND 11 THEN
1253 FOR l_cur_start_date IN c_start_date(l_payroll_id,to_char(l_effective_date+365,'YYYY'), '06') LOOP
1254 l_start_date := l_cur_start_date.start_date;
1255 END LOOP;
1256 ELSE
1257 FOR l_cur_start_date IN c_start_date(l_payroll_id,to_char(l_effective_date+31,'YYYY'), '12') LOOP
1258 l_start_date := l_cur_start_date.start_date;
1259 END LOOP;
1260 END IF; -- IF to_number(to_char(l_effective_date,'MM'))
1261 /*Bug#6312182*/
1262 set_ei(l_org_rec.rei_information17,fnd_date.date_to_canonical(l_start_date), l_rei_rec.rei_information17,'Y');
1263 ELSE
1264 set_ei(l_org_rec.rei_information17,NULL,l_rei_rec.rei_information17,'Y');
1265 END IF; -- IF NVL(l_retirement_plan,hr_a
1266 -- TSP Emp Contrib Elig Date
1267 END IF; -- If Family code is Appointment
1268
1269 /*
1270 ############ For Conversion to Appointment and Extension action #################
1271 */
1272
1273 IF l_family_code = 'CONV_APP' OR l_family_code = 'EXT_NTE' THEN
1274 fnd_profile.get('PER_BUSINESS_GROUP_ID',l_bus_group_id);
1275 -- Check if FEHB exists
1276 l_new_element_name := pqp_fedhr_uspay_int_utils.return_new_element_name('Health Benefits',l_bus_group_id,l_effective_date,null);
1277 FOR l_element_entry_cur IN c_element_entry(p_assignment_id , l_new_element_name, l_effective_date) LOOP
1278 l_fehb_exists := TRUE;
1279 hr_utility.set_location('Health benefits present',111);
1280 EXIT;
1281 END LOOP;
1282 -- Check if FEHB Pre tax exists
1283 IF l_fehb_exists = FALSE THEN
1284 l_new_element_name := pqp_fedhr_uspay_int_utils.return_new_element_name('Health Benefits Pre tax',l_bus_group_id,l_effective_date,null);
1285 FOR l_element_entry_cur IN c_element_entry(p_assignment_id , l_new_element_name, l_effective_date) LOOP
1286 l_fehb_exists := TRUE;
1287 hr_utility.set_location('Health benefits pre tax present',111);
1288 EXIT;
1289 END LOOP;
1290 END IF;
1291
1292 IF l_fehb_exists = TRUE THEN
1293 FOR l_cur_element IN c_element_value(l_new_element_name,p_assignment_id, l_effective_date) LOOP
1294 IF l_cur_element.ipv_name = 'Enrollment' THEN
1295 l_enrollment_option := l_cur_element.screen_entry_value;
1296 ELSIF l_cur_element.ipv_name = 'Health Plan' THEN
1297 l_health_plan := l_cur_element.screen_entry_value;
1298 ELSIF l_cur_element.ipv_name = 'Pre tax Waiver' THEN
1299 l_pre_tax_waiver := l_cur_element.screen_entry_value;
1300 END IF;
1301 END LOOP;
1302 END IF;
1303 -- Bug 4702325
1304 l_new_element_name := pqp_fedhr_uspay_int_utils.return_new_element_name('FEGLI',l_bus_group_id,l_effective_date,null);
1305 FOR l_cur_element IN c_element_value(l_new_element_name,p_assignment_id, l_effective_date) LOOP
1306 IF l_cur_element.ipv_name = 'FEGLI' THEN
1307 l_prev_fegli:= l_cur_element.screen_entry_value;
1308 END IF;
1309 END LOOP;
1310
1311 hr_utility.set_location('Option' || l_enrollment_option,111);
1312 hr_utility.set_location('Health Plan' || l_health_plan,111);
1313 hr_utility.set_location('Prev. FEGLI ' || l_prev_fegli,111);
1314 hr_utility.set_location('l_person_id ' || l_person_id,111);
1315 hr_utility.set_location('p_effective_date ' || p_effective_date,111);
1316
1317 ghr_history_fetch.fetch_peopleei
1318 (p_person_id => l_person_id,
1319 p_information_type => 'GHR_US_PER_BENEFIT_INFO',
1320 p_date_effective => nvl(l_effective_date,trunc(sysdate)),
1321 p_per_ei_data => l_per_ei_data
1322 );
1323
1324 l_date_temp_elig := fnd_date.canonical_to_date(l_per_ei_data.pei_information5);
1325 l_agency_contrib_date := fnd_date.canonical_to_date(l_per_ei_data.pei_information14);
1326
1327 hr_utility.set_location('l_date_temp_elig' || l_per_ei_data.pei_information5,111);
1328
1329 IF l_family_code = 'CONV_APP' THEN
1330 IF l_fehb_exists = FALSE OR (l_fehb_exists = TRUE AND NVL(l_enrollment_option,'Z') = 'Z') THEN
1331 -- FEHB Eligibility expires
1332 IF l_noa_code IN ('500', '501', '507', '508', '520', '524', '540', '541', '542', '543', '546', '548', '570', '590') THEN
1333 hr_utility.set_location('inside 1st',121);
1334 set_ei(l_org_rec.rei_information3, fnd_date.date_to_canonical(nvl(p_effective_date,sysdate)+60),l_rei_rec.rei_information3,'Y');
1335 END IF;
1336 -- FEHB Temp eligibility expires
1337 IF l_noa_code IN ('515','522','549','571') THEN
1338 set_ei(l_org_rec.rei_information4,
1339 fnd_date.date_to_canonical(add_months(nvl(l_effective_date,TRUNC(sysdate)),12)+1),l_rei_rec.rei_information4,'Y');
1340 END IF;
1341 -- Health Plan
1342 IF NVL(l_health_plan,'ZZ') = 'ZZ' THEN
1343 set_ei(l_org_rec.rei_information5, 'ZZ', l_rei_rec.rei_information5,'Y');
1344 END IF; -- Health Plan IF l_fehb_exists = FALSE OR (l_fehb_
1345 -- Enrollment
1346 set_ei(l_org_rec.rei_information6, 'X', l_rei_rec.rei_information6,'Y');
1347 -- Pre Tax waiver
1348 IF l_noa_code IN ('500','501','507','508','520','524','540','541','542','543','546','548','570','590') THEN
1349 set_ei(l_org_rec.rei_information9, 'N', l_rei_rec.rei_information9,'Y');
1350 END IF;
1351 END IF; -- IF l_fehb_exists = FALSE OR
1352
1353 -- FEGLI Eligibility expires
1354 IF NVL(l_fegli,hr_api.g_varchar2) <> 'A0' AND
1355 NVL(l_prev_fegli,hr_api.g_varchar2) = 'A0' THEN
1356 set_ei(l_org_rec.rei_information10, fnd_date.date_to_canonical(nvl(l_effective_date,TRUNC(sysdate))+31), l_rei_rec.rei_information10,'Y');
1357 END IF;
1358
1359 -- Check if TSP exists
1360 l_new_element_name := NULL;
1361 l_new_element_name := pqp_fedhr_uspay_int_utils.return_new_element_name('TSP',l_bus_group_id,l_effective_date,null);
1362 FOR l_element_entry_cur IN c_element_entry(p_assignment_id , l_new_element_name, l_effective_date) LOOP
1363 l_tsp_exists := TRUE;
1364 hr_utility.set_location('TSP present',111);
1365 EXIT;
1366 END LOOP;
1367
1368 IF l_tsp_exists = TRUE THEN
1369 FOR l_cur_element IN c_element_value(l_new_element_name,p_assignment_id, l_effective_date) LOOP
1370 IF l_cur_element.ipv_name = 'Status' then
1371 l_tsp_status := substr(l_cur_element.screen_entry_value,1,1);
1372 ELSIF l_cur_element.ipv_name = 'Status Date' then
1373 l_tsp_status_date := fnd_date.canonical_to_date(l_cur_element.screen_entry_value);
1374 END IF;
1375 END LOOP;
1376 END IF; -- IF l_tsp_exists = TRUE TH
1377
1378 -- TSP SCD
1379 IF nvl(l_retirement_plan,hr_api.g_varchar2) IN ('D','K','L','M','N','P') AND l_tsp_status IS NULL THEN
1380 set_ei(l_org_rec.rei_information12, fnd_date.date_to_canonical(nvl(l_scd,TRUNC(sysdate))), l_rei_rec.rei_information12,'Y');
1381 ELSE
1382 set_ei(l_org_rec.rei_information12,NULL, l_rei_rec.rei_information12,'Y');
1383 END IF; -- IF nvl(l_retirement_pl
1384
1385 -- TSP Status
1386 hr_utility.set_location('TSP status' || l_tsp_status,111);
1387 IF nvl(l_retirement_plan,hr_api.g_varchar2) IN ('D','K','L','M','N','P') AND l_tsp_status IS NULL THEN
1388 set_ei(l_org_rec.rei_information15,'I', l_rei_rec.rei_information15,'Y');
1389 ELSIF nvl(l_retirement_plan,hr_api.g_varchar2) IN ('2','4','5') THEN
1390 set_ei(l_org_rec.rei_information15,NULL, l_rei_rec.rei_information15,'Y');
1391 ELSIF nvl(l_retirement_plan,hr_api.g_varchar2) IN ('1','3','8','9','C','E','F','G','H','R','T','W' ) and l_tsp_status IS NULL THEN
1392 set_ei(l_org_rec.rei_information15,'E', l_rei_rec.rei_information15,'Y');
1393 ELSE
1394 set_ei(l_org_rec.rei_information15,NULL, l_rei_rec.rei_information15,'Y');
1395 END IF; -- IF nvl(l_retirement_plan,hr_api.
1396
1397 -- TSP Status Date
1398 IF NVL(l_retirement_plan,hr_api.g_varchar2) NOT IN ('2','4','5') AND l_tsp_status_date IS NULL THEN
1399 set_ei(l_org_rec.rei_information16,fnd_date.date_to_canonical(l_effective_date), l_rei_rec.rei_information16,'Y');
1400 ELSE
1401 set_ei(l_org_rec.rei_information16,NULL, l_rei_rec.rei_information16,'Y');
1402 END IF; -- -- TSP Status Date
1403
1404 -- TSP Agency Contrib Elig Date
1405 IF NVL(l_retirement_plan,hr_api.g_varchar2) IN ('D', 'K', 'L', 'M', 'N', 'P') AND l_agency_contrib_date IS NULL THEN
1406 -- Get Payroll ID
1407 FOR l_cur_payroll_id IN c_payroll_id(l_pa_request_id) LOOP
1408 l_payroll_id := l_cur_payroll_id.payroll_id;
1409 END LOOP;
1410 /*Bug#6312182 Changed the logic to determine TSP Agency Contribution Eligibilty date.*/
1411 IF to_number(to_char(l_effective_date,'MM')) BETWEEN 6 AND 11 THEN
1412 FOR l_cur_start_date IN c_start_date(l_payroll_id,to_char(l_effective_date+365,'YYYY'), '06') LOOP
1413 l_start_date := l_cur_start_date.start_date;
1414 END LOOP;
1415 ELSE
1416 FOR l_cur_start_date IN c_start_date(l_payroll_id,to_char(l_effective_date+31,'YYYY'), '12') LOOP
1417 l_start_date := l_cur_start_date.start_date;
1418 END LOOP;
1419
1420 END IF; -- IF to_number(to_char(l_effective_date,'MM'))
1421 /*Bug#6312182*/
1422 set_ei(l_org_rec.rei_information17,fnd_date.date_to_canonical(l_start_date), l_rei_rec.rei_information17,'Y');
1423 ELSE
1424 set_ei(l_org_rec.rei_information17,NULL,l_rei_rec.rei_information17,'Y');
1425 END IF; -- IF NVL(l_retirement_plan,hr_a
1426
1427 ELSIF l_family_code = 'EXT_NTE' THEN
1428 IF l_noa_code IN ('760','762') THEN
1429 hr_utility.set_location('l_enrollment_option ' || l_enrollment_option,121);
1430 hr_utility.set_location('l_date_temp_elig ' || to_char(l_date_temp_elig,'dd/mm/yyyy'),121);
1431 IF l_fehb_exists = TRUE AND NVL(l_enrollment_option,'Z') = 'Z' THEN
1432 -- FEHB Eligibility date
1433 hr_utility.set_location('Inside l_enrollment_option ' || l_enrollment_option,121);
1434 set_ei(l_org_rec.rei_information3,fnd_date.date_to_canonical(l_date_temp_elig+1),l_rei_rec.rei_information3,'Y');
1435 -- Enrollment
1436 IF l_date_temp_elig <= l_effective_date THEN
1437 set_ei(l_org_rec.rei_information6, 'X', l_rei_rec.rei_information6,'Y');
1438 END IF; -- IF l_date_temp_elig
1439
1440 END IF; -- IF l_fehb_exists = TRUE
1441 END IF; -- IF l_noa_code IN ('760','762') THEN
1442
1443 -- Health Plan
1444 IF l_fehb_exists = FALSE THEN
1445 set_ei(l_org_rec.rei_information5, 'ZZ', l_rei_rec.rei_information5,'Y');
1446 END IF; -- IF l_fehb_exists = FALSE THEN
1447 END IF; -- IF l_family_code = 'CONV_APP' THEN
1448 END IF; -- IF l_family_code = 'CONV_APP' OR l_family_code = 'EXT_NTE'
1449 END IF; -- If l_second_noa_code is not null
1450 END appt_benefits;
1451 ---Title38 Requirement...
1452 -- populate rei_information with mddds_pay
1453 -- GHR_US_PAR_MD_DDS_PAY
1454 --
1455
1456 Procedure mddds_pay is
1457 Begin
1458
1459 -- Read from history if person id is not null
1460 hr_utility.set_location('Person inside mddds_pay is ' || to_char(l_person_id),1);
1461 If l_assignment_id is not null then
1462 hr_utility.set_location('Person inside mddds_pay is ' || to_char(l_person_id),1);
1463
1464 l_refresh_flag := l_per_refresh_flag;
1465
1466 ghr_api.retrieve_element_entry_value
1467 (p_element_name => 'MDDDS Special Pay',
1468 p_input_value_name => 'Full Time Status',
1469 p_assignment_id => l_assignment_id,
1470 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1471 p_value => l_value,
1472 p_multiple_error_flag => l_multiple_error_flag
1473 );
1474 set_ei(l_org_rec.rei_information9, l_value, l_rei_rec.rei_information9, l_refresh_flag);
1475
1476 ghr_api.retrieve_element_entry_value
1477 (p_element_name => 'MDDDS Special Pay',
1478 p_input_value_name => 'Length of Service',
1479 p_assignment_id => l_assignment_id,
1480 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1481 p_value => l_value,
1482 p_multiple_error_flag => l_multiple_error_flag
1483 );
1484 set_ei(l_org_rec.rei_information10, l_value, l_rei_rec.rei_information10, l_refresh_flag);
1485
1486 ghr_api.retrieve_element_entry_value
1487 (p_element_name => 'MDDDS Special Pay',
1488 p_input_value_name => 'Scarce Specialty',
1489 p_assignment_id => l_assignment_id,
1490 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1491 p_value => l_value,
1492 p_multiple_error_flag => l_multiple_error_flag
1493 );
1494 set_ei(l_org_rec.rei_information3, l_value, l_rei_rec.rei_information3, l_refresh_flag);
1495
1496 ghr_api.retrieve_element_entry_value
1497 (p_element_name => 'MDDDS Special Pay',
1498 p_input_value_name => 'Specialty or Board Certification',
1499 p_assignment_id => l_assignment_id,
1500 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1501 p_value => l_value,
1502 p_multiple_error_flag => l_multiple_error_flag
1503 );
1504 set_ei(l_org_rec.rei_information4, l_value, l_rei_rec.rei_information4, l_refresh_flag);
1505
1506 ghr_api.retrieve_element_entry_value
1507 (p_element_name => 'MDDDS Special Pay',
1508 p_input_value_name => 'Geographic Location',
1509 p_assignment_id => l_assignment_id,
1510 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1511 p_value => l_value,
1512 p_multiple_error_flag => l_multiple_error_flag
1513 );
1514 set_ei(l_org_rec.rei_information5, l_value, l_rei_rec.rei_information5, l_refresh_flag);
1515
1516 ghr_api.retrieve_element_entry_value
1517 (p_element_name => 'MDDDS Special Pay',
1518 p_input_value_name => 'Exceptional Qualifications',
1519 p_assignment_id => l_assignment_id,
1520 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1521 p_value => l_value,
1522 p_multiple_error_flag => l_multiple_error_flag
1523 );
1524 set_ei(l_org_rec.rei_information6, l_value, l_rei_rec.rei_information6, l_refresh_flag);
1525
1526 ghr_api.retrieve_element_entry_value
1527 (p_element_name => 'MDDDS Special Pay',
1528 p_input_value_name => 'Executive Position',
1529 p_assignment_id => l_assignment_id,
1530 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1531 p_value => l_value,
1532 p_multiple_error_flag => l_multiple_error_flag
1533 );
1534 set_ei(l_org_rec.rei_information7, l_value, l_rei_rec.rei_information7, l_refresh_flag);
1535
1536 ghr_api.retrieve_element_entry_value
1537 (p_element_name => 'MDDDS Special Pay',
1538 p_input_value_name => 'Dentist Post Graduate Training',
1539 p_assignment_id => l_assignment_id,
1540 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1541 p_value => l_value,
1542 p_multiple_error_flag => l_multiple_error_flag
1543 );
1544 set_ei(l_org_rec.rei_information8, l_value, l_rei_rec.rei_information8, l_refresh_flag);
1545
1546 ghr_api.retrieve_element_entry_value
1547 (p_element_name => 'MDDDS Special Pay',
1548 p_input_value_name => 'Amount',
1549 p_assignment_id => l_assignment_id,
1550 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1551 p_value => l_value,
1552 p_multiple_error_flag => l_multiple_error_flag
1553 );
1554 set_ei(l_org_rec.rei_information11, l_value, l_rei_rec.rei_information11, l_refresh_flag);
1555
1556 if l_rei_rec.rei_information11 is null then
1557 l_rei_rec.rei_information11 := 0;
1558 end if;
1559
1560 ghr_api.retrieve_element_entry_value
1561 (p_element_name => 'MDDDS Special Pay',
1562 p_input_value_name => 'MDDDS Special Pay NTE Date',
1563 p_assignment_id => l_assignment_id,
1564 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1565 p_value => l_value,
1566 p_multiple_error_flag => l_multiple_error_flag
1567 );
1568 set_ei(l_org_rec.rei_information12, l_value, l_rei_rec.rei_information12, l_refresh_flag);
1569
1570 ghr_api.retrieve_element_entry_value
1571 (p_element_name => 'Premium Pay',
1572 p_input_value_name => 'Premium Pay Ind',
1573 p_assignment_id => l_assignment_id,
1574 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1575 p_value => l_value,
1576 p_multiple_error_flag => l_multiple_error_flag
1577 );
1578 set_ei(l_org_rec.rei_information13, l_value, l_rei_rec.rei_information13, l_refresh_flag);
1579
1580 End if;
1581 End mddds_pay;
1582
1583
1584 --Title 38 requirement
1585 -- Populate Premium_pay_ind for 855.
1586 Procedure premium_pay_ind IS
1587
1588 BEGIN
1589
1590 If l_assignment_id is not null then
1591 hr_utility.set_location('Person inside premium_pay_ind is ' || to_char(l_person_id),1);
1592 l_refresh_flag := l_per_refresh_flag;
1593
1594 ghr_api.retrieve_element_entry_value
1595 (p_element_name => 'Premium Pay',
1596 p_input_value_name => 'Premium Pay Ind',
1597 p_assignment_id => l_assignment_id,
1598 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1599 p_value => l_value,
1600 p_multiple_error_flag => l_multiple_error_flag
1601 );
1602 set_ei(l_org_rec.rei_information3, l_value, l_rei_rec.rei_information3, l_refresh_flag);
1603
1604 End If;
1605
1606 END premium_pay_ind;
1607
1608 Procedure appt_transfer is -- set / reset l_update_rei
1609 Begin
1610 -- Read from history if person id is not null
1611 hr_utility.set_location('appt_transfer - person id ' || l_person_id,1);
1612 --Bug 3128526. Changed p_person_id -> l_person_id
1613 If l_person_id is not null then
1614 l_refresh_flag := l_per_refresh_flag;
1615 ghr_history_fetch.fetch_peopleei
1616 (p_person_id => l_person_id,
1617 p_information_type => 'GHR_US_PER_GROUP1',
1618 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1619 p_per_ei_data => l_per_ei_data
1620 );
1621 --
1622 set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information7,l_rei_rec.rei_information3,l_refresh_flag);
1623 l_org_rec.rei_information3 := l_per_ei_data.pei_information7;
1624 --
1625 --set_ei(l_org_rec.rei_information4,l_per_ei_data.pei_information3,l_rei_rec.rei_information4,l_refresh_flag);
1626 --l_org_rec.rei_information4 := l_per_ei_data.pei_information3;
1627
1628 --Bug 3128526. Added IF..ELSE to default the handicap code
1629
1630 IF l_per_ei_data.pei_information11 is null THEN
1631 l_rei_rec.rei_information10 := '05';
1632 ELSE
1633 set_ei(l_org_rec.rei_information10,l_per_ei_data.pei_information11,l_rei_rec.rei_information10,l_refresh_flag);
1634 l_org_rec.rei_information10 := l_per_ei_data.pei_information11;
1635 END IF;
1636 /*
1637
1638 set_ei(l_org_rec.rei_information12,l_per_ei_data.pei_information8,l_rei_rec.rei_information12,l_refresh_flag);
1639 l_org_rec.rei_information12 := l_per_ei_data.pei_information8;
1640
1641 set_ei(l_org_rec.rei_information13,l_per_ei_data.pei_information9,l_rei_rec.rei_information13,l_refresh_flag);
1642 l_org_rec.rei_information13 := l_per_ei_data.pei_information9;
1643 */
1644
1645 set_ei(l_org_rec.rei_information18,l_per_ei_data.pei_information5,l_rei_rec.rei_information18,l_refresh_flag);
1646 l_org_rec.rei_information18 := l_per_ei_data.pei_information5;
1647
1648 hr_utility.set_location('RINO ' || l_rei_rec.rei_information17,1);
1649 hr_utility.set_location('RINO _CORE ' || l_per_ei_data.pei_information5,2);
1650
1651 set_ei(l_org_rec.rei_information19,l_per_ei_data.pei_information4,l_rei_rec.rei_information19,l_refresh_flag);
1652 l_org_rec.rei_information19 := l_per_ei_data.pei_information4;
1653
1654 l_per_ei_data := null;
1655
1656 ghr_history_fetch.fetch_peopleei
1657 (p_person_id => l_person_id,
1658 p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES',
1659 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1660 p_per_ei_data => l_per_ei_data
1661 );
1662 set_ei(l_org_rec.rei_information6,l_per_ei_data.pei_information5,l_rei_rec.rei_information6,l_refresh_flag);
1663 l_org_rec.rei_information6 := l_per_ei_data.pei_information5;
1664
1665 l_per_ei_data := null;
1666
1667 ghr_history_fetch.fetch_peopleei
1668 (p_person_id => l_person_id,
1669 p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
1670 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1671 p_per_ei_data => l_per_ei_data
1672 );
1673 hr_utility.set_location('original ' || l_org_rec.rei_information3,1);
1674 hr_utility.set_location(' person' || l_per_ei_data.pei_information7,1);
1675 hr_utility.set_location('rei ' || l_rei_rec.rei_information3,1);
1676
1677 -- This seems wrong ???? set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information7,l_rei_rec.rei_information3,l_refresh_flag);
1678 -- l_org_rec.rei_information3 := l_per_ei_data.pei_information7;
1679
1680 hr_utility.set_location('original ' || l_org_rec.rei_information3,1);
1681 hr_utility.set_location(' person' || l_per_ei_data.pei_information7,1);
1682 hr_utility.set_location('rei ' || l_rei_rec.rei_information3,1);
1683
1684 set_ei(l_org_rec.rei_information9,l_per_ei_data.pei_information5,l_rei_rec.rei_information9,l_refresh_flag);
1685 l_org_rec.rei_information9 := l_per_ei_data.pei_information5;
1686
1687 set_ei(l_org_rec.rei_information16,l_per_ei_data.pei_information4,l_rei_rec.rei_information16,l_refresh_flag);
1688 l_org_rec.rei_information16 := l_per_ei_data.pei_information4;
1689 set_ei(l_org_rec.rei_information21,l_per_ei_data.pei_information3,l_rei_rec.rei_information21,l_refresh_flag);
1690 l_org_rec.rei_information21 := l_per_ei_data.pei_information3;
1691
1692 l_per_ei_data := null;
1693
1694 --bug 4443968
1695 ghr_history_fetch.fetch_peopleei
1696 (p_person_id => l_person_id,
1697 p_information_type => 'GHR_US_PER_SCD_INFORMATION',
1698 p_date_effective => nvl (p_effective_date, trunc(sysdate)),
1699 p_per_ei_data => l_per_ei_data
1700 );
1701
1702 set_ei(l_org_rec.rei_information25,l_per_ei_data.pei_information12,l_rei_rec.rei_information25,l_refresh_flag);
1703 l_org_rec.rei_information25 := l_per_ei_data.pei_information12;
1704
1705 l_per_ei_data := null;
1706
1707
1708 IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
1709 -- Get PER_LEAVE Information
1710 hr_utility.set_location('per_leave_info' ,1);
1711 ghr_history_fetch.fetch_peopleei
1712 (p_person_id => l_person_id,
1713 p_information_type => 'GHR_US_PER_LEAVE_INFO',
1714 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1715 p_per_ei_data => l_per_ei_data
1716 );
1717 IF l_per_ei_data.pei_information4 is NULL THEN
1718 l_rei_rec.rei_information22 := 'N';
1719 ELSE
1720 set_ei(l_org_rec.rei_information22,l_per_ei_data.pei_information4,l_rei_rec.rei_information22,l_refresh_flag);
1721 END IF;
1722 l_per_ei_data := null;
1723 --Gain Or Lose
1724 set_ei(l_org_rec.rei_information23,'1B',l_rei_rec.rei_information23,l_refresh_flag);
1725 END IF; -- IF ghr_utility.is_ghr_nfc = 'TRUE'
1726 End if;
1727 If l_assignment_id is not null then
1728 l_refresh_flag := l_asg_refresh_flag;
1729 ghr_history_fetch.fetch_asgei
1730 (p_assignment_id => l_assignment_id,
1731 p_information_type => 'GHR_US_ASG_NON_SF52',
1732 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1733 p_asg_ei_data => l_asg_ei_data
1734 );
1735
1736 -- added on 2-oct-98 to autopopulate date arrived personnel office by the
1737 -- effective date
1738 -- set_ei(l_org_rec.rei_information7,fnd_date.date_to_canonical(p_effective_date),
1739 -- l_rei_rec.rei_information7,l_refresh_flag);
1740 -- set_ei(l_org_rec.rei_information7,l_asg_ei_data.aei_information3,
1741 -- l_rei_rec.rei_information7,l_refresh_flag);
1742 -- l_org_rec.rei_information7 := l_asg_ei_data.aei_information3;
1743
1744 -- added on 3-dec-99 -- Refer bug 963634
1745 for c_get_eff_rec in c_get_effective_date loop
1746 set_ei(l_org_rec.rei_information7,fnd_date.date_to_canonical(c_get_eff_rec.effective_date),
1747 l_rei_rec.rei_information7,l_refresh_flag);
1748 exit;
1749 end loop;
1750 set_ei(l_org_rec.rei_information11,l_asg_ei_data.aei_information6,l_rei_rec.rei_information11,l_refresh_flag);
1751 l_org_rec.rei_information11 := l_asg_ei_data.aei_information6;
1752 -- 3 to 8
1753 set_ei(l_org_rec.rei_information14,l_asg_ei_data.aei_information8,l_rei_rec.rei_information14,l_refresh_flag);
1754 l_org_rec.rei_information14 := l_asg_ei_data.aei_information8;
1755
1756 set_ei(l_org_rec.rei_information17,l_asg_ei_data.aei_information9,l_rei_rec.rei_information17,l_refresh_flag);
1757 l_org_rec.rei_information17 := l_asg_ei_data.aei_information9;
1758
1759 l_asg_ei_data := null;
1760 /*ghr_api.retrieve_element_entry_value
1761 (p_element_name => 'Within Grade Increase',
1762 p_input_value_name => 'Date Due',
1763 p_assignment_id => l_assignment_id,
1764 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1765 p_value => l_value,
1766 p_multiple_error_flag => l_multiple_error_flag
1767 );
1768 set_ei(l_org_rec.rei_information7,l_value,l_rei_rec.rei_information6,l_refresh_flag);
1769 l_org_rec.rei_information7 := l_value; */
1770 End if;
1771 --Check whether the follwing assignment would work correct in all cases
1772 If l_position_id is not null then
1773 l_refresh_flag := l_pos_refresh_flag;
1774 ghr_history_fetch.fetch_positionei
1775 (p_position_id => l_position_id,
1776 p_information_type => 'GHR_US_POS_GRP1',
1777 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1778 p_pos_ei_data => l_pos_ei_data
1779 );
1780 If l_pos_ei_data.position_extra_info_id is not null then
1781 set_ei(l_org_rec.rei_information15,l_pos_ei_data.poei_information12,l_rei_rec.rei_information15,l_refresh_flag);
1782 l_org_rec.rei_information15 := l_pos_ei_data.poei_information12;
1783 End if;
1784 l_pos_ei_data := null;
1785 End if;
1786 hr_utility.set_location('Appt_transfer Family Code: '||l_noa_family_code,10);
1787 IF NVL(l_noa_family_code,'C') <> 'CORRECT' THEN
1788 OPEN c_noac_130;
1789 FETCH c_noac_130 INTO l_dlei_date;
1790 IF l_dlei_date is not null THEN
1791 set_ei(l_org_rec.rei_information20,
1792 fnd_date.date_to_canonical(l_dlei_date),
1793 l_rei_rec.rei_information20,'Y');
1794 ELSE
1795 set_ei(l_org_rec.rei_information20,
1796 null,l_rei_rec.rei_information20,'Y');
1797 END IF;
1798 CLOSE c_noac_130;
1799 END IF;
1800 End appt_transfer;
1801
1802
1803 Procedure conv_appt is
1804 Begin
1805 -- Read from history if person id is not null
1806 If l_person_id is not null then
1807 l_refresh_flag := l_per_refresh_flag;
1808 ghr_history_fetch.fetch_peopleei
1809 (p_person_id => l_person_id,
1810 p_information_type => 'GHR_US_PER_GROUP1',
1811 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1812 p_per_ei_data => l_per_ei_data
1813 );
1814 set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information3,l_rei_rec.rei_information3,l_refresh_flag);
1815 l_org_rec.rei_information3 := l_per_ei_data.pei_information3;
1816 set_ei(l_org_rec.rei_information7,l_per_ei_data.pei_information11,l_rei_rec.rei_information7,l_refresh_flag);
1817 l_org_rec.rei_information7 := l_per_ei_data.pei_information11;
1818 set_ei(l_org_rec.rei_information12,l_per_ei_data.pei_information5,l_rei_rec.rei_information12,l_refresh_flag);
1819 l_org_rec.rei_information12 := l_per_ei_data.pei_information5;
1820 set_ei(l_org_rec.rei_information13,l_per_ei_data.pei_information4,l_rei_rec.rei_information13,l_refresh_flag);
1821 l_org_rec.rei_information13 := l_per_ei_data.pei_information4;
1822 l_per_ei_data := null;
1823
1824 ghr_history_fetch.fetch_peopleei
1825 (p_person_id => l_person_id,
1826 p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES',
1827 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1828 p_per_ei_data => l_per_ei_data
1829 );
1830 set_ei(l_org_rec.rei_information4,l_per_ei_data.pei_information5,l_rei_rec.rei_information4,l_refresh_flag);
1831 l_org_rec.rei_information4 := l_per_ei_data.pei_information5;
1832 l_per_ei_data := null;
1833 ghr_history_fetch.fetch_peopleei
1834 (p_person_id => l_person_id,
1835 p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
1836 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1837 p_per_ei_data => l_per_ei_data
1838 );
1839 set_ei(l_org_rec.rei_information6,l_per_ei_data.pei_information5,l_rei_rec.rei_information6,l_refresh_flag);
1840 l_org_rec.rei_information6 := l_per_ei_data.pei_information5;
1841 set_ei(l_org_rec.rei_information10,l_per_ei_data.pei_information4,l_rei_rec.rei_information10,l_refresh_flag);
1842 l_org_rec.rei_information10 := l_per_ei_data.pei_information4;
1843 set_ei(l_org_rec.rei_information21,l_per_ei_data.pei_information3,l_rei_rec.rei_information21,l_refresh_flag);
1844 l_org_rec.rei_information21 := l_per_ei_data.pei_information3;
1845 l_per_ei_data := null;
1846 ghr_history_fetch.fetch_peopleei
1847 (p_person_id => l_person_id,
1848 p_information_type => 'GHR_US_PER_CONVERSIONS',
1849 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1850 p_per_ei_data => l_per_ei_data
1851 );
1852 --
1853 -- commented out the following as autopopulating is not required for these fields
1854 -- 13-oct-98 by skutteti for the conversion rpa ddf update requirement
1855 -- set_ei(l_org_rec.rei_information14,l_per_ei_data.pei_information3,l_rei_rec.rei_information14,l_refresh_flag);
1856 -- l_org_rec.rei_information14 := l_per_ei_data.pei_information3;
1857 -- set_ei(l_org_rec.rei_information15,l_per_ei_data.pei_information4,l_rei_rec.rei_information15,l_refresh_flag);
1858 -- l_org_rec.rei_information15 := l_per_ei_data.pei_information4;
1859 -- set_ei(l_org_rec.rei_information16,l_per_ei_data.pei_information5,l_rei_rec.rei_information16,l_refresh_flag);
1860 -- l_org_rec.rei_information16 := l_per_ei_data.pei_information5;
1861 -- set_ei(l_org_rec.rei_information17,l_per_ei_data.pei_information7,l_rei_rec.rei_information17,l_refresh_flag);
1862 -- l_org_rec.rei_information17 := l_per_ei_data.pei_information7;
1863 -- set_ei(l_org_rec.rei_information18,l_per_ei_data.pei_information6,l_rei_rec.rei_information18,l_refresh_flag);
1864 -- l_org_rec.rei_information18 := l_per_ei_data.pei_information6;
1865 l_per_ei_data := null;
1866 -- Get PER_LEAVE Information
1867 hr_utility.set_location('per_leave_info' ,1);
1868 IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
1869 ghr_history_fetch.fetch_peopleei
1870 (p_person_id => l_person_id,
1871 p_information_type => 'GHR_US_PER_LEAVE_INFO',
1872 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1873 p_per_ei_data => l_per_ei_data
1874 );
1875 IF l_per_ei_data.pei_information4 is NULL THEN
1876 l_rei_rec.rei_information22 := 'N';
1877 ELSE
1878 set_ei(l_org_rec.rei_information22,l_per_ei_data.pei_information4,l_rei_rec.rei_information22,l_refresh_flag);
1879 END IF;
1880 l_per_ei_data := null;
1881 END IF; -- IF ghr_utility.is_ghr_nfc = 'TR
1882 End if;
1883 If NVL(l_assignment_id,p_assignment_id) is not null then
1884 l_refresh_flag := l_asg_refresh_flag;
1885 ghr_history_fetch.fetch_asgei
1886 (p_assignment_id => l_assignment_id,
1887 p_information_type => 'GHR_US_ASG_NON_SF52',
1888 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1889 p_asg_ei_data => l_asg_ei_data
1890 );
1891 --Begin Bug#4126188
1892 --set_ei(l_org_rec.rei_information5,l_asg_ei_data.aei_information3,l_rei_rec.rei_information5,l_refresh_flag);
1893 --End Bug#4126188
1894 set_ei(l_org_rec.rei_information8,l_asg_ei_data.aei_information8,l_rei_rec.rei_information8,l_refresh_flag);
1895 set_ei(l_org_rec.rei_information11,l_asg_ei_data.aei_information9,l_rei_rec.rei_information11,l_refresh_flag);
1896 -- Start Bug 1318341
1897
1898 --Bug#5527363 Modified the l_assignment_id parameter passed.
1899 ghr_api.retrieve_element_entry_value
1900 (p_element_name => 'Within Grade Increase',
1901 p_input_value_name => 'Date Due',
1902 p_assignment_id => NVL(l_assignment_id,p_assignment_id),
1903 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
1904 p_value => l_value,
1905 p_multiple_error_flag => l_multiple_error_flag
1906 );
1907
1908 set_ei(l_org_rec.rei_information19,l_value,l_rei_rec.rei_information19,l_refresh_flag);
1909 -- End Bug 1318341
1910 l_asg_ei_data := null;
1911 End if;
1912 --Check whether the follwing assignment would work correct in all cases
1913 If l_position_id is not null then
1914 l_refresh_flag := l_pos_refresh_flag;
1915 -- Make sure that the foll. check is correct
1916 ghr_history_fetch.fetch_positionei
1917 (p_position_id => l_position_id,
1918 p_information_type => 'GHR_US_POS_GRP1',
1919 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1920 p_pos_ei_data => l_pos_ei_data
1921 );
1922 set_ei(l_org_rec.rei_information9,l_pos_ei_data.poei_information12,l_rei_rec.rei_information9,l_refresh_flag);
1923 l_pos_ei_data := null;
1924 End if;
1925 --Bug#4126188 Begin
1926 FOR c_posn_to_frm IN c_position LOOP
1927 l_from_position_id :=c_posn_to_frm.from_position_id;
1928 l_to_position_id := c_posn_to_frm.to_position_id;
1929 END LOOP;
1930 ghr_history_fetch.fetch_positionei
1931 (p_position_id => l_from_position_id,
1932 p_information_type => 'GHR_US_POS_GRP1',
1933 p_date_effective => trunc(nvl(p_effective_date,sysdate)),
1934 p_pos_ei_data => l_pos_ei_data
1935 );
1936 l_from_poid := l_pos_ei_data.poei_information3;
1937 l_pos_ei_data := NULL;
1938
1939 ghr_history_fetch.fetch_positionei
1940 (p_position_id => l_to_position_id,
1941 p_information_type => 'GHR_US_POS_GRP1',
1942 p_date_effective => trunc(nvl(p_effective_date,sysdate)),
1943 p_pos_ei_data => l_pos_ei_data
1944 );
1945 l_to_poid := l_pos_ei_data.poei_information3;
1946 l_pos_ei_data := NULL;
1947 IF l_from_poid = l_to_poid THEN
1948 IF l_noa_family_code = 'CORRECT' THEN
1949 ghr_history_api.get_g_session_var(l_session);
1950 l_session1 := l_session;
1951 l_session.noa_id_correct := NULL;
1952 ghr_history_api.reinit_g_session_var;
1953 ghr_history_api.set_g_session_var(l_session);
1954 ghr_history_fetch.fetch_asgei
1955 (p_assignment_id => NVL(l_assignment_id,p_assignment_id),
1956 p_information_type => 'GHR_US_ASG_NON_SF52',
1957 p_date_effective => p_effective_date-1,
1958 p_asg_ei_data => l_asg_ei_data );
1959 ghr_history_api.reinit_g_session_var;
1960 ghr_history_api.set_g_session_var(l_session1);
1961 ELSE
1962 ghr_history_fetch.fetch_asgei
1963 (p_assignment_id => NVL(l_assignment_id,p_assignment_id),
1964 p_information_type => 'GHR_US_ASG_NON_SF52',
1965 p_date_effective => p_effective_date,
1966 p_asg_ei_data => l_asg_ei_data);
1967 END IF;
1968 set_ei(l_org_rec.rei_information5,l_asg_ei_data.aei_information3,l_rei_rec.rei_information5,l_refresh_flag);
1969 /*Start - Bug 7295154*/
1970 ELSIF l_to_poid IS NULL AND l_noa_family_code = 'CORRECT' THEN
1971 ghr_history_fetch.fetch_asgei
1972 (p_assignment_id => NVL(l_assignment_id,p_assignment_id),
1973 p_information_type => 'GHR_US_ASG_NON_SF52',
1974 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1975 p_asg_ei_data => l_asg_ei_data
1976 );
1977 set_ei(l_org_rec.rei_information5,l_asg_ei_data.aei_information3,l_rei_rec.rei_information5,'Y');
1978 /*End - Bug 7295154*/
1979 ELSE
1980 FOR c_get_eff_rec in c_get_effective_date
1981 LOOP
1982 IF (l_rei_rec.rei_information5 IS NULL) OR
1983 (l_noa_family_code = 'CORRECT' AND l_rei_rec.rei_information5 IS NOT NULL) THEN
1984 set_ei(l_org_rec.rei_information5,fnd_date.date_to_canonical(c_get_eff_rec.effective_date),
1985 l_rei_rec.rei_information5,l_refresh_flag);
1986 END IF;
1987 exit;
1988 END LOOP;
1989 END IF;
1990 --Bug#4126188 End
1991 hr_utility.set_location('conv_Appt Family Code: '||l_noa_family_code,10);
1992 IF NVL(l_noa_family_code,'C') <> 'CORRECT' THEN
1993 OPEN c_noac_5xx;
1994 FETCH c_noac_5xx INTO l_dlei_date;
1995 IF l_dlei_date is not null THEN
1996 FOR get_pay_grd in c_pp_and_grade LOOP
1997 IF nvl(get_pay_grd.to_grade_or_level,0)
1998 > nvl(get_pay_grd.from_grade_or_level,1)
1999 and get_pay_grd.to_pay_plan = get_pay_grd.from_pay_plan
2000 THEN
2001 set_ei(l_org_rec.rei_information20,
2002 fnd_date.date_to_canonical(l_dlei_date),
2003 l_rei_rec.rei_information20,'Y');
2004 END IF;
2005 END LOOP;
2006 ELSE
2007 set_ei(l_org_rec.rei_information20,
2008 null,l_rei_rec.rei_information20,'Y');
2009 END IF;
2010 CLOSE c_noac_5xx;
2011 END IF;
2012 End conv_appt;
2013
2014 Procedure return_to_duty
2015 is
2016 Begin
2017
2018 If l_person_id is not null then
2019 l_refresh_flag := l_per_refresh_flag;
2020 ghr_history_fetch.fetch_peopleei
2021 (p_person_id => l_person_id,
2022 p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES',
2023 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2024 p_per_ei_data => l_per_ei_data
2025 );
2026 set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information5,l_rei_rec.rei_information3,l_refresh_flag);
2027 l_per_ei_data := null;
2028
2029 ghr_history_fetch.fetch_peopleei
2030 (p_person_id => l_person_id,
2031 p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
2032 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2033 p_per_ei_data => l_per_ei_data
2034 );
2035 set_ei(l_org_rec.rei_information5,l_per_ei_data.pei_information5,l_rei_rec.rei_information5,l_refresh_flag);
2036
2037 -- Bug 3966783 changes
2038 IF NVL(l_noa_family_code,'C') = 'CORRECT' THEN
2039 ghr_history_fetch.fetch_peopleei
2040 (p_person_id => l_person_id,
2041 p_information_type => 'GHR_US_PER_GROUP1',
2042 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2043 p_per_ei_data => l_per_ei_data
2044 );
2045 set_ei(l_org_rec.rei_information8,l_per_ei_data.pei_information4,l_rei_rec.rei_information8,l_refresh_flag);
2046 END IF;
2047 l_per_ei_data := null;
2048 End if;
2049
2050 If l_assignment_id is not null then
2051 l_refresh_flag := l_asg_refresh_flag;
2052 ghr_history_fetch.fetch_asgei
2053 (p_assignment_id => l_assignment_id,
2054 p_information_type => 'GHR_US_ASG_NON_SF52',
2055 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2056 p_asg_ei_data => l_asg_ei_data
2057 );
2058 set_ei(l_org_rec.rei_information6,l_asg_ei_data.aei_information6,l_rei_rec.rei_information6,l_refresh_flag);
2059 set_ei(l_org_rec.rei_information7,l_asg_ei_data.aei_information8,l_rei_rec.rei_information7,l_refresh_flag);
2060
2061 l_asg_ei_data := null;
2062 -- do not populate within grade increase.
2063
2064 End if;
2065 End return_to_duty;
2066
2067 Procedure reassignment is
2068 Begin
2069 If l_assignment_id is not null then
2070 l_refresh_flag := l_asg_refresh_flag;
2071 ghr_history_fetch.fetch_asgei
2072 (p_assignment_id => l_assignment_id,
2073 p_information_type => 'GHR_US_ASG_NON_SF52',
2074 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2075 p_asg_ei_data => l_asg_ei_data
2076 );
2077 set_ei(l_org_rec.rei_information4,l_asg_ei_data.aei_information8,l_rei_rec.rei_information4,l_refresh_flag);
2078 set_ei(l_org_rec.rei_information6,l_asg_ei_data.aei_information9,l_rei_rec.rei_information6,l_refresh_flag);
2079
2080 l_asg_ei_data := null;
2081 End if;
2082 If l_position_id is not null then
2083 l_refresh_flag := l_pos_refresh_flag;
2084 ghr_history_fetch.fetch_positionei
2085 (p_position_id => l_position_id,
2086 p_information_type => 'GHR_US_POS_GRP1',
2087 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2088 p_pos_ei_data => l_pos_ei_data
2089 );
2090 set_ei(l_org_rec.rei_information5,l_pos_ei_data.poei_information12,l_rei_rec.rei_information5,l_refresh_flag);
2091 l_pos_ei_data := null;
2092
2093 /* Bug # 1794090
2094
2095 ghr_history_fetch.fetch_positionei
2096 (p_position_id => l_position_id,
2097 p_information_type => 'GHR_US_POS_GRP2',
2098 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2099 p_pos_ei_data => l_pos_ei_data
2100 );
2101 set_ei(l_org_rec.rei_information3,l_pos_ei_data.poei_information12,l_rei_rec.rei_information3,l_refresh_flag);
2102
2103 l_pos_ei_data := null;
2104 */
2105 End if;
2106 --Bug#4126188 Begin
2107 FOR c_posn_to_frm IN c_position LOOP
2108 l_from_position_id :=c_posn_to_frm.from_position_id;
2109 l_to_position_id := c_posn_to_frm.to_position_id;
2110 END LOOP;
2111 ghr_history_fetch.fetch_positionei
2112 (p_position_id => l_from_position_id,
2113 p_information_type => 'GHR_US_POS_GRP1',
2114 p_date_effective => trunc(nvl(p_effective_date,sysdate)),
2115 p_pos_ei_data => l_pos_ei_data
2116 );
2117 l_from_poid := l_pos_ei_data.poei_information3;
2118 l_pos_ei_data := NULL;
2119
2120 ghr_history_fetch.fetch_positionei
2121 (p_position_id => l_to_position_id,
2122 p_information_type => 'GHR_US_POS_GRP1',
2123 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2124 p_pos_ei_data => l_pos_ei_data
2125 );
2126 l_to_poid := l_pos_ei_data.poei_information3;
2127 l_pos_ei_data := NULL;
2128 IF l_from_poid = l_to_poid THEN
2129 IF nvl(l_noa_family_code,'C') = 'CORRECT' THEN
2130
2131 ghr_history_api.get_g_session_var(l_session);
2132 l_session1 := l_session;
2133 l_session.noa_id_correct := NULL;
2134 ghr_history_api.reinit_g_session_var;
2135 ghr_history_api.set_g_session_var(l_session);
2136 ghr_history_fetch.fetch_asgei
2137 (p_assignment_id => NVL(l_assignment_id,p_assignment_id),
2138 p_information_type => 'GHR_US_ASG_NON_SF52',
2139 p_date_effective => p_effective_date-1,
2140 p_asg_ei_data => l_asg_ei_data
2141 );
2142 ghr_history_api.reinit_g_session_var;
2143 ghr_history_api.set_g_session_var(l_session1);
2144 ELSE
2145 ghr_history_fetch.fetch_asgei
2146 (p_assignment_id => NVL(l_assignment_id,p_assignment_id),
2147 p_information_type => 'GHR_US_ASG_NON_SF52',
2148 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2149 p_asg_ei_data => l_asg_ei_data
2150 );
2151 END IF;
2152 set_ei(l_org_rec.rei_information7,l_asg_ei_data.aei_information3,l_rei_rec.rei_information7,l_refresh_flag);
2153 ELSE
2154 FOR c_get_eff_rec in c_get_effective_date
2155 LOOP
2156 IF l_org_rec.rei_information7 IS NULL THEN
2157 set_ei(l_org_rec.rei_information7,fnd_date.date_to_canonical(c_get_eff_rec.effective_date),
2158 l_rei_rec.rei_information7,l_refresh_flag);
2159 ELSIF (nvl(l_noa_family_code,'C') = 'CORRECT' AND l_org_rec.rei_information7 IS NOT NULL) THEN
2160 set_ei(l_org_rec.rei_information7,fnd_date.date_to_canonical(c_get_eff_rec.effective_date),
2161 l_rei_rec.rei_information7,l_refresh_flag);
2162 END IF;
2163 exit;
2164 END LOOP;
2165 END IF;
2166 --Bug#4126188 End
2167 End reassignment;
2168
2169 -- Added realign procedure to fix bug 3593584
2170 Procedure realign is
2171 Begin
2172 -- Bug#3593584 The following variable is used in procedure realign
2173 -- to populate the Extra info only when noa_family_code = 'CORRECT'
2174 hr_utility.set_location('l_noa_family_code '|| l_noa_family_code,10);
2175 -- Bug#4089400 Moved the cursor to the begining of the main procedure.
2176 IF NVL(l_noa_family_code,'C') = 'CORRECT' THEN
2177
2178 If l_assignment_id is not null then
2179 l_refresh_flag := l_asg_refresh_flag;
2180 ghr_history_fetch.fetch_asgei
2181 (p_assignment_id => l_assignment_id,
2182 p_information_type => 'GHR_US_ASG_NON_SF52',
2183 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2184 p_asg_ei_data => l_asg_ei_data
2185 );
2186 set_ei(l_org_rec.rei_information3,l_asg_ei_data.aei_information3,l_rei_rec.rei_information3,l_refresh_flag);
2187 l_asg_ei_data := null;
2188 End if;
2189 If l_position_id is not null then
2190 l_refresh_flag := l_pos_refresh_flag;
2191 ghr_history_fetch.fetch_positionei
2192 (p_position_id => l_position_id,
2193 p_information_type => 'GHR_US_POS_GRP1',
2194 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2195 p_pos_ei_data => l_pos_ei_data
2196 );
2197 set_ei(l_org_rec.rei_information4,l_pos_ei_data.poei_information18,l_rei_rec.rei_information4,l_refresh_flag);
2198 set_ei(l_org_rec.rei_information5,l_pos_ei_data.poei_information3,l_rei_rec.rei_information5,l_refresh_flag);
2199 set_ei(l_org_rec.rei_information6,l_pos_ei_data.poei_information4,l_rei_rec.rei_information6,l_refresh_flag);
2200 --Bug 3593584 Populate positions' organization,
2201 set_ei(l_org_rec.rei_information8,l_pos_ei_data.poei_information21,l_rei_rec.rei_information8,l_refresh_flag);
2202 set_ei(l_org_rec.rei_information11,l_pos_ei_data.poei_information5,l_rei_rec.rei_information11,l_refresh_flag);
2203
2204 l_pos_ei_data := null;
2205
2206 ghr_history_fetch.fetch_positionei
2207 (p_position_id => l_position_id,
2208 p_information_type => 'GHR_US_POS_GRP2',
2209 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2210 p_pos_ei_data => l_pos_ei_data
2211 );
2212 set_ei(l_org_rec.rei_information7,l_pos_ei_data.poei_information4,l_rei_rec.rei_information7,l_refresh_flag);
2213 l_pos_ei_data := null;
2214
2215 End if;
2216 END IF;
2217 End realign;
2218 -- Added realign procedure to fix bug 3593584
2219
2220 Procedure chg_data_element is
2221 cursor c_bg is
2222 SELECT business_group_id
2223 FROM per_positions
2224 WHERE position_id = l_position_id;
2225 l_agency_code ghr_pa_requests.agency_code%type;
2226 l_bg_id per_positions.business_group_id%type;
2227 Begin
2228 If l_position_id is not null then
2229 l_refresh_flag := l_pos_refresh_flag;
2230 ghr_history_fetch.fetch_positionei
2231 (p_position_id => l_position_id,
2232 p_information_type => 'GHR_US_POS_GRP1',
2233 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2234 p_pos_ei_data => l_pos_ei_data
2235 );
2236 set_ei(l_org_rec.rei_information3,l_pos_ei_data.poei_information3,l_rei_rec.rei_information3,l_refresh_flag);
2237 l_pos_ei_data := null;
2238 FOR c_bg_id in c_bg LOOP
2239 l_bg_id := c_bg_id.business_group_id;
2240 END LOOP;
2241 l_agency_code := ghr_api.get_position_agency_code_pos
2242 (p_position_id => l_position_id
2243 ,p_business_group_id => l_bg_id
2244 ,p_effective_date => nvl(p_effective_date,trunc(sysdate)));
2245 set_ei(l_org_rec.rei_information4,l_agency_code,l_rei_rec.rei_information4,l_refresh_flag);
2246 -- Begin Bug# 4126188
2247 set_ei(l_org_rec.rei_information5,fnd_date.date_to_canonical(p_effective_date),l_rei_rec.rei_information5,l_refresh_flag);
2248 -- End Bug# 4126188
2249 End if;
2250
2251 End chg_data_element;
2252
2253 Procedure chg_retire_plan is
2254 Begin
2255 If l_person_id is not null then
2256 l_refresh_flag := l_per_refresh_flag;
2257 ghr_history_fetch.fetch_peopleei
2258 (p_person_id => l_person_id,
2259 p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES',
2260 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2261 p_per_ei_data => l_per_ei_data
2262 );
2263 set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information5,l_rei_rec.rei_information3,l_refresh_flag);
2264 l_per_ei_data := null;
2265
2266 ghr_history_fetch.fetch_peopleei
2267 (p_person_id => l_person_id,
2268 p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
2269 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2270 p_per_ei_data => l_per_ei_data
2271 );
2272 set_ei(l_org_rec.rei_information4,l_per_ei_data.pei_information3,l_rei_rec.rei_information4,l_refresh_flag);
2273 set_ei(l_org_rec.rei_information5,l_per_ei_data.pei_information5,l_rei_rec.rei_information5,l_refresh_flag);
2274 set_ei(l_org_rec.rei_information6,l_per_ei_data.pei_information4,l_rei_rec.rei_information6,l_refresh_flag);
2275 l_per_ei_data := null;
2276 End if;
2277 End chg_retire_plan;
2278 --
2279 --
2280 Procedure chg_scd is
2281 Begin
2282 If l_person_id is not null then
2283 l_refresh_flag := l_per_refresh_flag;
2284 ghr_history_fetch.fetch_peopleei
2285 (p_person_id => l_person_id,
2286 p_information_type => 'GHR_US_PER_SCD_INFORMATION',
2287 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2288 p_per_ei_data => l_per_ei_data
2289 );
2290 --Modified for EHRI reports 3675673
2291 set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information5,l_rei_rec.rei_information3,l_refresh_flag);
2292 set_ei(l_org_rec.rei_information4,l_per_ei_data.pei_information4,l_rei_rec.rei_information4,l_refresh_flag);
2293 set_ei(l_org_rec.rei_information8,l_per_ei_data.pei_information7,l_rei_rec.rei_information8,l_refresh_flag);
2294 set_ei(l_org_rec.rei_information9,l_per_ei_data.pei_information6,l_rei_rec.rei_information9,l_refresh_flag);
2295 -- SCD Retirement
2296
2297 -- Bug 4164083 eHRI New Attribution Changes
2298 set_ei(l_org_rec.rei_information10,l_per_ei_data.pei_information8,l_rei_rec.rei_information10,l_refresh_flag);
2299 set_ei(l_org_rec.rei_information11,l_per_ei_data.pei_information9,l_rei_rec.rei_information11,l_refresh_flag);
2300
2301 -- End eHRI New Attribution Changes
2302
2303 --bug 4443968
2304 set_ei(l_org_rec.rei_information12,l_per_ei_data.pei_information12,l_rei_rec.rei_information12,l_refresh_flag);
2305
2306 l_per_ei_data := null;
2307
2308 ghr_history_fetch.fetch_peopleei
2309 (p_person_id => l_person_id,
2310 p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES',
2311 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2312 p_per_ei_data => l_per_ei_data
2313 );
2314 set_ei(l_org_rec.rei_information5,l_per_ei_data.pei_information5,l_rei_rec.rei_information5,l_refresh_flag);
2315 l_per_ei_data := null;
2316
2317 ghr_history_fetch.fetch_peopleei
2318 (p_person_id => l_person_id,
2319 p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
2320 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2321 p_per_ei_data => l_per_ei_data
2322 );
2323 set_ei(l_org_rec.rei_information6,l_per_ei_data.pei_information5,l_rei_rec.rei_information6,l_refresh_flag);
2324 set_ei(l_org_rec.rei_information7,l_per_ei_data.pei_information4,l_rei_rec.rei_information7,l_refresh_flag);
2325 l_per_ei_data := null;
2326
2327 End if;
2328 End chg_scd;
2329 --
2330 --Bug#2146912
2331 --
2332 Procedure scd_tsp is
2333 begin
2334
2335 If l_person_id is not null then
2336 l_refresh_flag := l_per_refresh_flag;
2337 hr_utility.set_location('l_refresh_flag is ' || l_refresh_flag,1);
2338 ghr_history_fetch.fetch_peopleei
2339 (p_person_id => l_person_id,
2340 p_information_type => 'GHR_US_PER_SCD_INFORMATION',
2341 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2342 p_per_ei_data => l_per_ei_data
2343 );
2344 set_ei(l_org_rec.rei_information12,l_per_ei_data.pei_information6,
2345 l_rei_rec.rei_information12,l_refresh_flag);
2346 l_per_ei_data := null;
2347
2348 End if;
2349 End scd_tsp;
2350 --
2351 --
2352 Procedure non_pay_duty is
2353 l_special_info ghr_api.special_information_type;
2354 begin
2355
2356 If l_person_id is not null then
2357 l_refresh_flag := l_per_refresh_flag;
2358 hr_utility.set_location('l_refresh_flag is ' || l_refresh_flag,4);
2359 IF NVL(l_noa_family_code,'C') = 'CORRECT' THEN
2360 ghr_history_fetch.fetch_peopleei
2361 (p_person_id => l_person_id,
2362 p_information_type => 'GHR_US_PER_GROUP1',
2363 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2364 p_per_ei_data => l_per_ei_data
2365 );
2366 set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information4,l_rei_rec.rei_information3,l_refresh_flag);
2367 END IF;
2368 l_per_ei_data := null;
2369
2370 End if;
2371 End non_pay_duty;
2372 --
2373 --
2374 Procedure lwop_info is
2375 l_special_info ghr_api.special_information_type;
2376 begin
2377
2378 If l_person_id is not null then
2379 l_refresh_flag := l_per_refresh_flag;
2380 hr_utility.set_location('l_refresh_flag is ' || l_refresh_flag,5);
2381 IF NVL(l_noa_family_code,'C') = 'CORRECT' THEN
2382 ghr_history_fetch.fetch_peopleei
2383 (p_person_id => l_person_id,
2384 p_information_type => 'GHR_US_PER_GROUP1',
2385 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2386 p_per_ei_data => l_per_ei_data
2387 );
2388 set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information4,l_rei_rec.rei_information3,l_refresh_flag);
2389 END IF;
2390 l_per_ei_data := null;
2391
2392 End if;
2393 End lwop_info;
2394 --
2395 --
2396 Procedure gov_awards is
2397 cursor c_bg1 is
2398 SELECT business_group_id
2399 FROM per_positions
2400 WHERE position_id = p_position_id;
2401 cursor c_noa_code is
2402 SELECT 'X'
2403 FROM ghr_nature_of_actions
2404 WHERE nature_of_action_id = p_noa_id
2405 and code in ('825','840','841','842','843','844','845','846',
2406 '847','848','849','878','879' ); -- Bug 3266198 Added 848 and 849 to the NOA codes.
2407 l_agency_code ghr_pa_requests.agency_code%type;
2408 l_bg_id per_positions.business_group_id%type;
2409
2410 --
2411 begin
2412 --
2413 -- populate the date award earned field with the effective_date
2414 --
2415 IF NVL(l_noa_family_code,'C') <> 'CORRECT' THEN -- Bug 2836175
2416 l_refresh_flag := l_per_refresh_flag;
2417 hr_utility.set_location('eff. date is ' || fnd_date.date_to_canonical(p_effective_date),1);
2418
2419 --set_ei(l_org_rec.rei_information9, fnd_date.date_to_canonical(p_effective_date), l_rei_rec.rei_information9, l_refresh_flag);
2420 --Bug 2833942
2421 set_ei(l_org_rec.rei_information9, NVL(l_rei_rec.rei_information9,fnd_date.date_to_canonical(p_effective_date)), l_rei_rec.rei_information9, l_refresh_flag);
2422
2423 hr_utility.set_location('Eff date ' || p_effective_date,1);
2424 hr_utility.set_location('Info9 for Gov Awards ' || l_org_rec.rei_information9,1);
2425 -- Start Bug 1379280
2426 FOR c_noa_code1 in c_noa_code LOOP
2427 IF p_position_id is NOT NULL then
2428 FOR c_bg_id in c_bg1 LOOP
2429 l_bg_id := c_bg_id.business_group_id;
2430 END LOOP;
2431 l_agency_code := ghr_api.get_position_agency_code_pos
2432 (p_position_id => p_position_id
2433 ,p_business_group_id => l_bg_id);
2434 set_ei(l_org_rec.rei_information3, substr(l_agency_code,1,2), l_rei_rec.rei_information3, l_refresh_flag);
2435 END IF;
2436 END LOOP;
2437 IF l_position_id is not null then
2438 l_refresh_flag := l_pos_refresh_flag;
2439 ghr_history_fetch.fetch_positionei
2440 (p_position_id => l_position_id,
2441 p_information_type => 'GHR_US_POS_GRP2',
2442 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2443 p_pos_ei_data => l_pos_ei_data);
2444 set_ei(l_org_rec.rei_information10,l_pos_ei_data.poei_information13,l_rei_rec.rei_information10,l_refresh_flag);
2445 END IF;
2446 END IF;
2447
2448 -- End Bug 1379280
2449 --
2450 End gov_awards;
2451 --
2452 -- Bug#5039072 Added procedure service_obligation.
2453 Procedure service_obligation IS
2454 l_serv_oblig_code VARCHAR2(50);
2455 l_serv_oblig_stdt DATE;
2456 l_serv_oblig_enddt DATE;
2457 BEGIN
2458 --
2459 hr_utility.set_location('Entering Service Obligation. l_person_id '||l_person_id,0);
2460 hr_utility.set_location('shadow type code '||l_org_rec.rei_information3,1);
2461 hr_utility.set_location('shadow start date '||l_org_rec.rei_information5,1);
2462 hr_utility.set_location('shadow end date '||l_org_rec.rei_information4,1);
2463 hr_utility.set_location('RPAEIT type code '||l_rei_rec.rei_information3,1);
2464 hr_utility.set_location('RPAEIT start date '||l_rei_rec.rei_information5,1);
2465 hr_utility.set_location('RPAEIT end date '||l_rei_rec.rei_information4,1);
2466
2467 l_serv_oblig_code := NULL;
2468 l_serv_oblig_stdt := NULL;
2469 l_serv_oblig_enddt := NULL;
2470
2471 If p_person_id is not null then
2472 l_refresh_flag := l_per_refresh_flag;
2473
2474 IF l_first_noa_code IN ('815','816') THEN
2475 IF l_la_code1 IN ('V8V') THEN
2476 l_serv_oblig_code := 'A4';
2477 ELSE
2478 l_serv_oblig_code := '04';
2479 END IF;
2480 ELSE
2481 IF l_la_code1 IN ('VPR','VPS') AND
2482 NVL(l_payment_option,'B') <> 'B' THEN
2483 l_serv_oblig_code := 'A1';
2484 END IF;
2485 END IF;
2486
2487 -- Bug#5132121 Service Obligation for Student Loan and MD/DDS
2488 IF l_first_noa_code IN ('817') THEN
2489 l_serv_oblig_code := '02';
2490 END IF;
2491 IF l_first_noa_code IN ('850') THEN
2492 l_serv_oblig_code := 'A3';
2493 END IF;
2494 IF l_first_noa_code IN ('480') THEN
2495 l_serv_oblig_code := 'A5';
2496 END IF;
2497 -- Bug#5132121 Service Obligation for Student Loan and MD/DDS
2498
2499 set_ei(l_org_rec.rei_information3,l_serv_oblig_code,l_rei_rec.rei_information3,l_refresh_flag);
2500 -- Bug#5039072 If the Service Oblig Type is NULL THEN Don't default the Start Date.
2501 IF l_serv_oblig_code IS NULL THEN
2502 set_ei(l_org_rec.rei_information4,l_serv_oblig_stdt,l_rei_rec.rei_information4,l_refresh_flag);
2503 set_ei(l_org_rec.rei_information5,l_serv_oblig_enddt,l_rei_rec.rei_information5,l_refresh_flag);
2504 ELSE
2505 set_ei(l_org_rec.rei_information4,fnd_date.date_to_canonical(p_effective_date),l_rei_rec.rei_information4,l_refresh_flag);
2506 set_ei(l_org_rec.rei_information5,l_serv_oblig_enddt,l_rei_rec.rei_information5,l_refresh_flag);
2507 END IF;
2508 l_per_ei_data := null;
2509 End if;
2510 END service_obligation;
2511 --
2512 --
2513 Procedure chg_sched_hours is
2514 -- Bug#2468297
2515 Cursor c_work_sch is
2516 select work_schedule
2517 from ghr_pa_requests
2518 where pa_request_id = p_pa_request_id;
2519
2520 l_work_schedule ghr_pa_requests.work_schedule%type;
2521 Begin
2522 If l_assignment_id is not null then
2523 l_refresh_flag := l_asg_refresh_flag;
2524 ghr_history_fetch.fetch_asgei
2525 (p_assignment_id => l_assignment_id,
2526 p_information_type => 'GHR_US_ASG_NON_SF52',
2527 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2528 p_asg_ei_data => l_asg_ei_data
2529 );
2530 --Bug#2468297
2531 FOR work_sch in c_work_sch
2532 LOOP
2533 l_work_schedule := work_sch.work_schedule;
2534 END LOOP;
2535
2536 If l_work_schedule in ('B','F','G','I','J') then
2537 l_asg_ei_data.aei_information8:=NULL;
2538 End If;
2539
2540 set_ei(l_org_rec.rei_information7,l_asg_ei_data.aei_information8,
2541 l_rei_rec.rei_information7,l_refresh_flag);
2542 l_asg_ei_data := null;
2543 End if;
2544 End chg_sched_hours;
2545 --
2546 Procedure chg_in_tenure is
2547 --
2548
2549 begin
2550 --
2551 -- Read from history if person id is not null
2552 IF p_person_id is not null THEN
2553 l_refresh_flag := l_per_refresh_flag;
2554 ghr_history_fetch.fetch_peopleei
2555 (p_person_id => p_person_id,
2556 p_information_type => 'GHR_US_PER_GROUP1',
2557 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2558 p_per_ei_data => l_per_ei_data
2559 );
2560 set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information3,l_rei_rec.rei_information3,l_refresh_flag);
2561 l_org_rec.rei_information3 := l_per_ei_data.pei_information3;
2562 END IF;
2563 l_per_ei_data := NULL;
2564 --
2565 End chg_in_tenure;
2566 -- Bug#2759379 Added procedure chg_in_fegli
2567 Procedure chg_in_fegli is
2568 --
2569 begin
2570 --
2571 -- Fetch the element entry value
2572 IF l_assignment_id is not null THEN
2573 l_refresh_flag := l_per_refresh_flag;
2574 ghr_api.retrieve_element_entry_value
2575 (p_element_name => 'FEGLI',
2576 p_input_value_name => 'Eligibility Expiration',
2577 p_assignment_id => l_assignment_id,
2578 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
2579 p_value => l_value,
2580 p_multiple_error_flag => l_multiple_error_flag
2581 );
2582 set_ei(l_org_rec.rei_information1,l_value,l_rei_rec.rei_information1,l_refresh_flag);
2583 END IF;
2584 --
2585 End chg_in_fegli;
2586 --
2587 Procedure nfc_separation is
2588 --
2589 begin
2590 IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
2591 set_ei(l_org_rec.rei_information12,'1B',l_rei_rec.rei_information12,l_refresh_flag);
2592 END IF;
2593 end nfc_separation;
2594
2595 -- Bug 4724337 Race or National Origin changes
2596 procedure ethnic_race_info is
2597 begin
2598 l_per_ei_data := null;
2599 -- Read from history if person id is not null
2600 IF l_person_id IS NOT NULL THEN
2601 l_refresh_flag := l_per_refresh_flag;
2602 ghr_history_fetch.fetch_peopleei
2603 (p_person_id => p_person_id,
2604 p_information_type => 'GHR_US_PER_ETHNICITY_RACE',
2605 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2606 p_per_ei_data => l_per_ei_data
2607 );
2608 set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information3,l_rei_rec.rei_information3,l_refresh_flag);
2609 set_ei(l_org_rec.rei_information4,l_per_ei_data.pei_information4,l_rei_rec.rei_information4,l_refresh_flag);
2610 set_ei(l_org_rec.rei_information5,l_per_ei_data.pei_information5,l_rei_rec.rei_information5,l_refresh_flag);
2611 set_ei(l_org_rec.rei_information6,l_per_ei_data.pei_information6,l_rei_rec.rei_information6,l_refresh_flag);
2612 set_ei(l_org_rec.rei_information7,l_per_ei_data.pei_information7,l_rei_rec.rei_information7,l_refresh_flag);
2613 set_ei(l_org_rec.rei_information8,l_per_ei_data.pei_information8,l_rei_rec.rei_information8,l_refresh_flag);
2614 END IF;
2615 end ethnic_race_info;
2616
2617 -- End Bug 4724337 Race or National Origin changes
2618
2619
2620 -- Bug#3385386 Added procedure foreign_transfer_allowance
2621 Procedure fta is
2622 --
2623 begin
2624 --
2625 -- Fetch the element entry value
2626 IF l_assignment_id is not null THEN
2627 l_refresh_flag := l_per_refresh_flag;
2628 ghr_api.retrieve_element_entry_value
2629 (p_element_name => 'Foreign Transfer Allowance',
2630 p_input_value_name => 'Last Action Code',
2631 p_assignment_id => l_assignment_id,
2632 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
2633 p_value => l_value,
2634 p_multiple_error_flag => l_multiple_error_flag
2635 );
2636 set_ei(l_org_rec.rei_information3, l_value, l_rei_rec.rei_information3, l_refresh_flag);
2637 l_value := NULL;
2638 ghr_api.retrieve_element_entry_value
2639 (p_element_name => 'Foreign Transfer Allowance',
2640 p_input_value_name => 'Number Family Members',
2641 p_assignment_id => l_assignment_id,
2642 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
2643 p_value => l_value,
2644 p_multiple_error_flag => l_multiple_error_flag
2645 );
2646 set_ei(l_org_rec.rei_information4, l_value, l_rei_rec.rei_information4, l_refresh_flag);
2647 l_value := NULL;
2648 ghr_api.retrieve_element_entry_value
2649 (p_element_name => 'Foreign Transfer Allowance',
2650 p_input_value_name => 'Miscellaneous Expense',
2651 p_assignment_id => l_assignment_id,
2652 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
2653 p_value => l_value,
2654 p_multiple_error_flag => l_multiple_error_flag
2655 );
2656 set_ei(l_org_rec.rei_information5, l_value, l_rei_rec.rei_information5, l_refresh_flag);
2657 l_value := NULL;
2658 ghr_api.retrieve_element_entry_value
2659 (p_element_name => 'Foreign Transfer Allowance',
2660 p_input_value_name => 'Wardrobe Expense',
2661 p_assignment_id => l_assignment_id,
2662 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
2663 p_value => l_value,
2664 p_multiple_error_flag => l_multiple_error_flag
2665 );
2666 set_ei(l_org_rec.rei_information6, l_value, l_rei_rec.rei_information6, l_refresh_flag);
2667 l_value := NULL;
2668 ghr_api.retrieve_element_entry_value
2669 (p_element_name => 'Foreign Transfer Allowance',
2670 p_input_value_name => 'Pre Departure Sub Expense',
2671 p_assignment_id => l_assignment_id,
2672 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
2673 p_value => l_value,
2674 p_multiple_error_flag => l_multiple_error_flag
2675 );
2676 set_ei(l_org_rec.rei_information7, l_value, l_rei_rec.rei_information7, l_refresh_flag);
2677 l_value := NULL;
2678 ghr_api.retrieve_element_entry_value
2679 (p_element_name => 'Foreign Transfer Allowance',
2680 p_input_value_name => 'Lease Penalty Expense',
2681 p_assignment_id => l_assignment_id,
2682 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
2683 p_value => l_value,
2684 p_multiple_error_flag => l_multiple_error_flag
2685 );
2686 set_ei(l_org_rec.rei_information8, l_value, l_rei_rec.rei_information8, l_refresh_flag);
2687 l_value := NULL;
2688 ghr_api.retrieve_element_entry_value
2689 (p_element_name => 'Foreign Transfer Allowance',
2690 p_input_value_name => 'Amount',
2691 p_assignment_id => l_assignment_id,
2692 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
2693 p_value => l_value,
2694 p_multiple_error_flag => l_multiple_error_flag
2695 );
2696 -- Bug#3754136 Added the format mask to the field l_value.
2697 set_ei(l_org_rec.rei_information9, to_char(to_number(l_value),'FM999990.90'),
2698 l_rei_rec.rei_information9, l_refresh_flag);
2699 l_value := NULL;
2700 END IF;
2701 --
2702 End fta;
2703 --
2704
2705 -- Bug 4280026
2706 Procedure key_emergency_essntl
2707 is
2708 Begin
2709 If l_noa_family_code = 'APP' then
2710 set_ei(l_org_rec.rei_information3,nvl(l_rei_rec.rei_information3,'0'),
2711 l_rei_rec.rei_information3,'Y');
2712 Else
2713 For get_pos in c_position
2714 Loop
2715 If nvl(get_pos.from_position_id,0) = nvl(get_pos.to_position_id,0) then
2716 If l_assignment_id is not null then
2717 -- Get ASG_NON_SF52 data
2718 l_refresh_flag := l_asg_refresh_flag;
2719 ghr_history_fetch.fetch_asgei
2720 (p_assignment_id => l_assignment_id,
2721 p_information_type => 'GHR_US_ASG_NON_SF52',
2722 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2723 p_asg_ei_data => l_asg_ei_data
2724 );
2725 set_ei(l_org_rec.rei_information3,l_asg_ei_data.aei_information5,
2726 l_rei_rec.rei_information3,l_refresh_flag);
2727 l_asg_ei_data := null;
2728 End If;
2729 Else
2730 set_ei(l_org_rec.rei_information3,nvl(l_rei_rec.rei_information3,'0'),
2731 l_rei_rec.rei_information3,'Y');
2732 End If;
2733 End Loop;
2734 End if;
2735 End key_emergency_essntl;
2736
2737
2738 -- Bug 5482191
2739 Procedure ghr_conv_dates is
2740 /* --Begin Bug# 4588575
2741 l_tenure ghr_pa_requests.tenure%type;
2742 l_pos_occpied ghr_pa_requests.position_occupied%type;
2743 l_pos_intel_pos VARCHAR(30);
2744 CURSOR c_pa_req_details is
2745 SELECT tenure,position_occupied
2746 FROM ghr_pa_requests
2747 WHERE pa_request_id = p_pa_request_id;
2748 */ --Backout the changes done for Bug# 4588575
2749
2750 Begin
2751 /* l_per_ei_data := null;
2752 If l_position_id is not null then
2753 ghr_history_fetch.fetch_positionei
2754 (p_position_id => l_position_id,
2755 p_information_type => 'GHR_US_POS_GRP2',
2756 p_date_effective => trunc(nvl(p_effective_date,sysdate)),
2757 p_pos_ei_data => l_pos_ei_data
2758 );
2759 l_pos_intel_pos := l_pos_ei_data.poei_information15;
2760 l_pos_ei_data := null;
2761 end if;
2762 FOR pa_rec in c_pa_req_details LOOP
2763 l_tenure := pa_rec.tenure;
2764 l_pos_occpied := pa_rec.position_occupied;
2765 END LOOP;
2766 --end Bug# 4588575
2767 */ --Backout the changes done for Bug# 4588575
2768 If l_person_id is not null then
2769 ghr_history_fetch.fetch_peopleei
2770 (p_person_id => l_person_id,
2771 p_information_type => 'GHR_US_PER_CONVERSIONS',
2772 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2773 p_per_ei_data => l_per_ei_data
2774 );
2775 l_refresh_flag := l_per_refresh_flag;
2776 /* --Begin Bug# 4588575
2777 IF nvl(l_tenure,hr_api.g_varchar2) = '2' and
2778 nvl(l_pos_occpied,hr_api.g_varchar2) = '1' and
2779 nvl(l_pos_intel_pos,hr_api.g_varchar2) <> '2' THEN
2780 set_ei(l_org_rec.rei_information3,fnd_date.date_to_canonical(p_effective_date),
2781 l_rei_rec.rei_information3,l_refresh_flag);
2782 ELSE
2783 --end Bug# 4588575
2784 */ --Backout the changes done for Bug# 4588575
2785 set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information3,
2786 l_rei_rec.rei_information3,l_refresh_flag);
2787 /* END IF;
2788 --Begin Bug# 4588575
2789 IF nvl(l_tenure,hr_api.g_varchar2) = '2' and
2790 nvl(l_pos_occpied,hr_api.g_varchar2) = '1' and
2791 l_rei_rec.rei_information3 IS NOT NULL THEN
2792 set_ei(l_org_rec.rei_information4,
2793 fnd_date.date_to_canonical(add_months(p_effective_date,36)),
2794 l_rei_rec.rei_information4,l_refresh_flag);
2795 ELSE
2796 --End Bug# 4588575
2797 */ --Backout the changes done for Bug# 4588575
2798 set_ei(l_org_rec.rei_information4,l_per_ei_data.pei_information4,
2799 l_rei_rec.rei_information4,l_refresh_flag);
2800 -- END IF;
2801
2802 set_ei(l_org_rec.rei_information5,l_per_ei_data.pei_information5,
2803 l_rei_rec.rei_information5,l_refresh_flag);
2804 /*--Begin Bug# 4588575
2805 IF nvl(l_la_code1,hr_api.g_varchar2) = 'J8M' THEN
2806 set_ei( l_org_rec.rei_information6,
2807 fnd_date.date_to_canonical(add_months(p_effective_date,24)),
2808 l_rei_rec.rei_information6,l_refresh_flag);
2809 ELSE
2810 --End Bug# 4588575
2811 */ --Backout the changes done for Bug# 4588575
2812 set_ei(l_org_rec.rei_information6,l_per_ei_data.pei_information6,
2813 l_rei_rec.rei_information6,l_refresh_flag);
2814 --END IF;
2815 set_ei(l_org_rec.rei_information7,l_per_ei_data.pei_information7,
2816 l_rei_rec.rei_information7,l_refresh_flag);
2817
2818 hr_utility.set_location('rei_ei_data:'||l_rei_rec.rei_information7,0);
2819 l_per_ei_data := null;
2820 End If;
2821 End ghr_conv_dates;
2822
2823 /* --Begin Bug# 4588575
2824 Procedure ghr_prob_info is
2825 l_pos_occpied ghr_pa_requests.position_occupied%type;
2826 CURSOR c_pa_req_details is
2827 SELECT position_occupied
2828 FROM ghr_pa_requests
2829 WHERE pa_request_id = p_pa_request_id;
2830 Begin
2831 l_per_ei_data := null;
2832 If l_person_id is not null then
2833 ghr_history_fetch.fetch_peopleei
2834 (p_person_id => l_person_id,
2835 p_information_type => 'GHR_US_PER_PROBATIONS',
2836 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2837 p_per_ei_data => l_per_ei_data
2838 );
2839 l_refresh_flag := l_per_refresh_flag;
2840 IF l_first_noa_code not in('130','132','145','147') THEN
2841 set_ei(l_org_rec.rei_information10,fnd_date.date_to_canonical(p_effective_date),
2842 l_rei_rec.rei_information10,l_refresh_flag);
2843 ELSE
2844 set_ei(l_org_rec.rei_information10,l_per_ei_data.pei_information3,
2845 l_rei_rec.rei_information10,l_refresh_flag);
2846 END IF;
2847 FOR pa_rec in c_pa_req_details LOOP
2848 l_pos_occpied := pa_rec.position_occupied;
2849 END LOOP;
2850 IF l_first_noa_code not in('130','132','145','147') and l_pos_occpied = '1' THEN
2851 set_ei(l_org_rec.rei_information11,
2852 fnd_date.date_to_canonical(add_months(p_effective_date,12)),
2853 l_rei_rec.rei_information11,l_refresh_flag);
2854 ELSIF l_first_noa_code not in('130','132','145','147') and l_pos_occpied = '2' THEN
2855 set_ei(l_org_rec.rei_information11,
2856 fnd_date.date_to_canonical(add_months(p_effective_date,24)),
2857 l_rei_rec.rei_information11,l_refresh_flag);
2858 ELSE
2859 set_ei(l_org_rec.rei_information11,
2860 l_per_ei_data.pei_information4,
2861 l_rei_rec.rei_information11,l_refresh_flag);
2862 END IF;
2863 l_pos_ei_data := null;
2864 end if;
2865 End ghr_prob_info;
2866
2867 Procedure ghr_scd_info is
2868 l_retirement_plan ghr_pa_requests.retirement_plan%type;
2869 l_service_comp_date ghr_pa_requests.service_comp_date%type;
2870 CURSOR c_pa_req_details is
2871 SELECT retirement_plan,service_comp_date
2872 FROM ghr_pa_requests
2873 WHERE pa_request_id = p_pa_request_id;
2874 Begin
2875 l_per_ei_data := null;
2876 If l_person_id is not null then
2877 ghr_history_fetch.fetch_peopleei
2878 (p_person_id => l_person_id,
2879 p_information_type => 'GHR_US_PER_SCD_INFORMATION',
2880 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2881 p_per_ei_data => l_per_ei_data
2882 );
2883 l_refresh_flag := l_per_refresh_flag;
2884 FOR pa_rec in c_pa_req_details LOOP
2885 l_retirement_plan := pa_rec.retirement_plan;
2886 l_service_comp_date := pa_rec.service_comp_date;
2887 END LOOP;
2888 IF l_first_noa_code not in('130','132','145','147') THEN
2889 set_ei(l_org_rec.rei_information10,fnd_date.date_to_canonical(l_service_comp_date),
2890 l_rei_rec.rei_information10,l_refresh_flag);
2891 set_ei(l_org_rec.rei_information11,fnd_date.date_to_canonical(l_service_comp_date),
2892 l_rei_rec.rei_information11,l_refresh_flag);
2893 ELSE
2894 set_ei(l_org_rec.rei_information10,l_per_ei_data.pei_information4,
2895 l_rei_rec.rei_information10,l_refresh_flag);
2896 set_ei(l_org_rec.rei_information11,l_per_ei_data.pei_information5,
2897 l_rei_rec.rei_information11,l_refresh_flag);
2898 END IF;
2899 IF l_retirement_plan in('2','4','5') AND l_first_noa_code not in('130','132','145','147') THEN
2900 set_ei(l_org_rec.rei_information12,fnd_date.date_to_canonical(l_service_comp_date),
2901 l_rei_rec.rei_information12,l_refresh_flag);
2902 ELSE
2903 set_ei(l_org_rec.rei_information12,l_per_ei_data.pei_information7,
2904 l_rei_rec.rei_information12,l_refresh_flag);
2905 END IF;
2906 l_pos_ei_data := null;
2907 end if;
2908 End ghr_scd_info;
2909
2910 --end Bug# 4588575
2911 */ --Backout the changes done for Bug# 4588575
2912 Procedure set_refresh_flags is
2913 Begin
2914 If p_person_id is not null then
2915 if p_person_id = nvl(ghr_par_shd.g_old_rec.person_id,hr_api.g_number) and
2916 (l_rei_rec_exists = 'N' or
2917 trunc(nvl(p_effective_date,sysdate)) <> trunc(nvl(ghr_par_shd.g_old_rec.effective_date,sysdate)))
2918 then
2919 hr_utility.set_location('change in eff. date only',2);
2920 l_person_id := p_person_id;
2921 l_per_refresh_flag := 'Y';
2922 Elsif p_person_id <> nvl(ghr_par_shd.g_old_rec.person_id,hr_api.g_number) then
2923 hr_utility.set_location('person id has changed',3);
2924 l_person_id := p_person_id;
2925 l_per_refresh_flag := 'N';
2926 Else
2927 l_person_id := null;
2928 End if;
2929 hr_utility.set_location('person id in the condition - refresh flag N ' || to_char(l_person_id),1);
2930 End if;
2931 If p_assignment_id is not null then
2932 if p_assignment_id = nvl(ghr_par_shd.g_old_rec.employee_assignment_id,hr_api.g_number) and
2933 (l_rei_rec_exists = 'N' or
2934 trunc(nvl(p_effective_date,sysdate)) <> trunc(nvl(ghr_par_shd.g_old_rec.effective_date,sysdate)))
2935 then
2936 l_assignment_id := p_assignment_id;
2937 l_asg_refresh_flag := 'Y';
2938 Elsif p_assignment_id <> nvl(ghr_par_shd.g_old_rec.employee_assignment_id,hr_api.g_number) then
2939 l_assignment_id := p_assignment_id;
2940 l_asg_refresh_flag := 'N';
2941 Else
2942 l_assignment_id := null;
2943 End if;
2944 End if;
2945 If p_position_id is not null then
2946 if p_position_id = nvl(ghr_par_shd.g_old_rec.to_position_id,hr_api.g_number) and
2947 (l_rei_rec_exists = 'N' or
2948 trunc(nvl(p_effective_date,sysdate)) <> trunc(nvl(ghr_par_shd.g_old_rec.effective_date,sysdate)))
2949 then
2950 l_position_id := p_position_id;
2951 l_pos_refresh_flag := 'Y';
2952 Elsif p_position_id <> nvl(ghr_par_shd.g_old_rec.to_position_id,hr_api.g_number) then
2953 l_position_id := p_position_id;
2954 l_pos_refresh_flag := 'Y';
2955 Else
2956 l_position_id := null;
2957 End if;
2958 End if;
2959 End set_refresh_flags;
2960 --
2961 --
2962 --Start of Bug# 6312144
2963 procedure ipa_benefits_cont is
2964 begin
2965 l_per_ei_data := null;
2966 -- Read from history if person id is not null
2967 IF l_person_id IS NOT NULL THEN
2968 l_refresh_flag := l_per_refresh_flag;
2969 ghr_history_fetch.fetch_peopleei
2970 (p_person_id => p_person_id,
2971 p_information_type => 'GHR_US_PER_BENEFITS_CONT',
2972 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2973 p_per_ei_data => l_per_ei_data
2974 );
2975 set_ei(l_org_rec.rei_information1,l_per_ei_data.pei_information1,l_rei_rec.rei_information1,l_refresh_flag);
2976 set_ei(l_org_rec.rei_information2,l_per_ei_data.pei_information2,l_rei_rec.rei_information2,l_refresh_flag);
2977 set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information3,l_rei_rec.rei_information3,l_refresh_flag);
2978 set_ei(l_org_rec.rei_information4,l_per_ei_data.pei_information4,l_rei_rec.rei_information4,l_refresh_flag);
2979 set_ei(l_org_rec.rei_information5,l_per_ei_data.pei_information5,l_rei_rec.rei_information5,l_refresh_flag);
2980 set_ei(l_org_rec.rei_information6,l_per_ei_data.pei_information6,l_rei_rec.rei_information6,l_refresh_flag);
2981 set_ei(l_org_rec.rei_information7,l_per_ei_data.pei_information7,l_rei_rec.rei_information7,l_refresh_flag);
2982 set_ei(l_org_rec.rei_information12,l_per_ei_data.pei_information12,l_rei_rec.rei_information12,l_refresh_flag);
2983 set_ei(l_org_rec.rei_information8,l_per_ei_data.pei_information8,l_rei_rec.rei_information8,l_refresh_flag);
2984 set_ei(l_org_rec.rei_information9,l_per_ei_data.pei_information9,l_rei_rec.rei_information9,l_refresh_flag);
2985 set_ei(l_org_rec.rei_information10,l_per_ei_data.pei_information10,l_rei_rec.rei_information10,l_refresh_flag);
2986 set_ei(l_org_rec.rei_information11,l_per_ei_data.pei_information11,l_rei_rec.rei_information11,l_refresh_flag);
2987 END IF;
2988 end ipa_benefits_cont;
2989
2990 procedure retirement_system_info is
2991 begin
2992 l_per_ei_data := null;
2993 -- Read from history if person id is not null
2994 IF l_person_id IS NOT NULL THEN
2995 l_refresh_flag := l_per_refresh_flag;
2996 ghr_history_fetch.fetch_peopleei
2997 (p_person_id => p_person_id,
2998 p_information_type => 'GHR_US_PER_RETIRMENT_SYS_INFO',
2999 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
3000 p_per_ei_data => l_per_ei_data
3001 );
3002 set_ei(l_org_rec.rei_information1,l_per_ei_data.pei_information1,l_rei_rec.rei_information1,l_refresh_flag);
3003 set_ei(l_org_rec.rei_information2,l_per_ei_data.pei_information2,l_rei_rec.rei_information2,l_refresh_flag);
3004 set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information3,l_rei_rec.rei_information3,l_refresh_flag);
3005 set_ei(l_org_rec.rei_information4,l_per_ei_data.pei_information4,l_rei_rec.rei_information4,l_refresh_flag);
3006 set_ei(l_org_rec.rei_information5,l_per_ei_data.pei_information5,l_rei_rec.rei_information5,l_refresh_flag);
3007 END IF;
3008 end retirement_system_info;
3009
3010 procedure par_benefits is
3011 begin
3012 -- Read from history if person id is not null
3013 IF l_person_id IS NOT NULL THEN
3014 l_refresh_flag := l_per_refresh_flag;
3015 ghr_history_fetch.fetch_peopleei
3016 (p_person_id => p_person_id,
3017 p_information_type => 'GHR_US_PER_BENEFIT_INFO',
3018 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
3019 p_per_ei_data => l_per_ei_data
3020 );
3021 set_ei(l_org_rec.rei_information1,l_per_ei_data.pei_information16,l_rei_rec.rei_information1,l_refresh_flag);
3022 set_ei(l_org_rec.rei_information2,l_per_ei_data.pei_information17,l_rei_rec.rei_information2,l_refresh_flag);
3023 set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information18,l_rei_rec.rei_information3,l_refresh_flag);
3024 set_ei(l_org_rec.rei_information4,l_per_ei_data.pei_information19,l_rei_rec.rei_information4,l_refresh_flag);
3025 set_ei(l_org_rec.rei_information5,l_per_ei_data.pei_information20,l_rei_rec.rei_information5,l_refresh_flag);
3026 END IF;
3027 end par_benefits;
3028 --End of Bug# 6312144
3029
3030 Begin
3031
3032 -- Bug#4089400 Getting the Family code to restrict DLEI calculation for Correction Actions.
3033 FOR c_noa_fam_code_rec IN c_noa_fam_code
3034 LOOP
3035 l_noa_family_code := c_noa_fam_code_rec.noa_family_code;
3036 -- Bug#5039072 RRR Changes.
3037 l_first_noa_code := c_noa_fam_code_rec.first_noa_code;
3038 l_la_code1 := c_noa_fam_code_rec.la_code1;
3039 l_payment_option := c_noa_fam_code_rec.payment_option;
3040 END LOOP;
3041 hr_utility.set_location('l_noa_family_code '|| l_noa_family_code,10);
3042 l_resp_id := fnd_profile.value('RESP_ID');
3043 l_application_id := fnd_profile.value('RESP_APPL_ID');
3044 hr_utility.set_location('Resp id '|| l_resp_id,10);
3045 hr_utility.set_location('appl id '|| l_application_id,10);
3046 for c_info_type_rec in c_info_types(l_application_id,l_resp_id) loop
3047 l_information_type := c_info_type_rec.information_type;
3048 l_rei_rec := NULL;
3049 l_org_rec := NULL; -- Bug 4691293
3050 l_rei_rec_exists := 'N';
3051
3052 -- first populate l_rei_rec with existing values on pa_request_extra_info, if any
3053
3054 for rei_rec in c_rei_rec loop
3055 l_rei_rec.pa_request_extra_info_id := rei_rec.pa_request_extra_info_id;
3056 l_rei_rec.rei_information1 := rei_rec.rei_information1;
3057 l_rei_rec.rei_information2 := rei_rec.rei_information2;
3058 l_rei_rec.rei_information3 := rei_rec.rei_information3;
3059 l_rei_rec.rei_information4 := rei_rec.rei_information4;
3060 l_rei_rec.rei_information5 := rei_rec.rei_information5;
3061 l_rei_rec.rei_information6 := rei_rec.rei_information6;
3062 l_rei_rec.rei_information7 := rei_rec.rei_information7;
3063 l_rei_rec.rei_information8 := rei_rec.rei_information8;
3064 l_rei_rec.rei_information9 := rei_rec.rei_information9;
3065 l_rei_rec.rei_information10 := rei_rec.rei_information10;
3066 l_rei_rec.rei_information11 := rei_rec.rei_information11;
3067 l_rei_rec.rei_information12 := rei_rec.rei_information12;
3068 l_rei_rec.rei_information13 := rei_rec.rei_information13;
3069 l_rei_rec.rei_information14 := rei_rec.rei_information14;
3070 l_rei_rec.rei_information15 := rei_rec.rei_information15;
3071 l_rei_rec.rei_information16 := rei_rec.rei_information16;
3072 l_rei_rec.rei_information17 := rei_rec.rei_information17;
3073 l_rei_rec.rei_information18 := rei_rec.rei_information18;
3074 l_rei_rec.rei_information19 := rei_rec.rei_information19;
3075 l_rei_rec.rei_information20 := rei_rec.rei_information20;
3076 l_rei_rec.rei_information21 := rei_rec.rei_information21;
3077 l_rei_rec.rei_information22 := rei_rec.rei_information22;
3078 l_rei_rec.rei_information23 := rei_rec.rei_information23;
3079 l_rei_rec.rei_information24 := rei_rec.rei_information24;
3080 l_rei_rec.rei_information25 := rei_rec.rei_information25;
3081 l_rei_rec.rei_information26 := rei_rec.rei_information26;
3082 l_rei_rec.rei_information27 := rei_rec.rei_information27;
3083 l_rei_rec.rei_information28 := rei_rec.rei_information28;
3084 l_rei_rec.rei_information29 := rei_rec.rei_information29;
3085 l_rei_rec.rei_information30 := rei_rec.rei_information30;
3086 l_rei_rec_exists := 'Y'; -- will be used in set_refresh_flags
3087 End loop;
3088
3089 for rei_rec in c_org_rei_rec loop
3090 l_org_rec.rei_information1 := rei_rec.rei_information1;
3091 l_org_rec.rei_information2 := rei_rec.rei_information2;
3092 l_org_rec.rei_information3 := rei_rec.rei_information3;
3093 l_org_rec.rei_information4 := rei_rec.rei_information4;
3094 l_org_rec.rei_information5 := rei_rec.rei_information5;
3095 l_org_rec.rei_information6 := rei_rec.rei_information6;
3096 l_org_rec.rei_information7 := rei_rec.rei_information7;
3097 l_org_rec.rei_information8 := rei_rec.rei_information8;
3098 l_org_rec.rei_information9 := rei_rec.rei_information9;
3099 l_org_rec.rei_information10 := rei_rec.rei_information10;
3100 l_org_rec.rei_information11 := rei_rec.rei_information11;
3101 l_org_rec.rei_information12 := rei_rec.rei_information12;
3102 l_org_rec.rei_information13 := rei_rec.rei_information13;
3103 l_org_rec.rei_information14 := rei_rec.rei_information14;
3104 l_org_rec.rei_information15 := rei_rec.rei_information15;
3105 l_org_rec.rei_information16 := rei_rec.rei_information16;
3106 l_org_rec.rei_information17 := rei_rec.rei_information17;
3107 l_org_rec.rei_information18 := rei_rec.rei_information18;
3108 l_org_rec.rei_information19 := rei_rec.rei_information19;
3109 l_org_rec.rei_information20 := rei_rec.rei_information20;
3110 l_org_rec.rei_information21 := rei_rec.rei_information21;
3111 l_org_rec.rei_information22 := rei_rec.rei_information22;
3112 l_org_rec.rei_information23 := rei_rec.rei_information23;
3113 l_org_rec.rei_information24 := rei_rec.rei_information24;
3114 l_org_rec.rei_information25 := rei_rec.rei_information25;
3115 l_org_rec.rei_information26 := rei_rec.rei_information26;
3116 l_org_rec.rei_information27 := rei_rec.rei_information27;
3117 l_org_rec.rei_information28 := rei_rec.rei_information28;
3118 l_org_rec.rei_information29 := rei_rec.rei_information29;
3119 l_org_rec.rei_information30 := rei_rec.rei_information30;
3120 End loop;
3121
3122 -- call the procedure to set the refresh flags
3123 If p_refresh_flag = 'N' then
3124 set_refresh_flags;
3125 Else
3126 hr_utility.set_location('p_refresh_flag is Y',3);
3127 l_per_refresh_flag := 'Y';
3128 l_asg_refresh_flag := 'Y';
3129 l_pos_refresh_flag := 'Y';
3130 End if;
3131 --
3132 hr_utility.set_location('INFO_TYPE_IS'|| l_information_type,0);
3133 hr_utility.set_location('PER ID is'|| l_person_id,0);
3134 if l_information_type = 'GHR_US_PAR_APPT_INFO' then
3135 hr_utility.set_location(l_proc,5);
3136 hr_utility.set_location('info type ' || l_information_type,1);
3137 appt_info;
3138 elsif l_information_type = 'GHR_US_PAR_MD_DDS_PAY' then
3139 hr_utility.set_location(l_proc,15);
3140 hr_utility.set_location('info type ' || l_information_type,3);
3141 mddds_pay;
3142 elsif l_information_type = 'GHR_US_PAR_PREMIUM_PAY_IND' then
3143 hr_utility.set_location(l_proc,15);
3144 hr_utility.set_location('info type ' || l_information_type,3);
3145 premium_pay_ind;
3146
3147 elsif l_information_type = 'GHR_US_PAR_APPT_TRANSFER' then
3148 hr_utility.set_location(l_proc,10);
3149 hr_utility.set_location('info type ' || l_information_type,2);
3150 for c_req_num_rec in c_req_num loop
3151 --Bug 3128526. Added 'NVL' to handle NULL values
3152 if nvl(c_req_num_rec.request_number,hr_api.g_number) <> 'MTI'||to_char(p_pa_request_id) then
3153 appt_transfer;
3154 end if;
3155 end loop;
3156 elsif l_information_type = 'GHR_US_PAR_CONV_APP' then
3157 hr_utility.set_location(l_proc,15);
3158 hr_utility.set_location('info type ' || l_information_type,3);
3159 conv_appt;
3160 elsif l_information_type = 'GHR_US_PAR_RETURN_TO_DUTY' then
3161 hr_utility.set_location(l_proc,20);
3162 hr_utility.set_location('info type ' || l_information_type,4);
3163 return_to_duty;
3164 elsif l_information_type = 'GHR_US_PAR_REASSIGNMENT' then
3165 hr_utility.set_location(l_proc,25);
3166 hr_utility.set_location('info type ' || l_information_type,4);
3167 reassignment;
3168 elsif l_information_type = 'GHR_US_PAR_REALIGNMENT' then
3169 hr_utility.set_location(l_proc,30);
3170 hr_utility.set_location('info type ' || l_information_type,4);
3171 realign;
3172 elsif l_information_type = 'GHR_US_PAR_CHG_DATA_ELEMENT' then
3173 hr_utility.set_location(l_proc,45);
3174 hr_utility.set_location('info type ' || l_information_type,4);
3175 chg_data_element;
3176 elsif l_information_type = 'GHR_US_PAR_CHG_RETIRE_PLAN' then
3177 hr_utility.set_location(l_proc,50);
3178 hr_utility.set_location('info type ' || l_information_type,4);
3179 chg_retire_plan;
3180 elsif l_information_type = 'GHR_US_PAR_CHG_SCD' then
3181 hr_utility.set_location(l_proc,55);
3182 hr_utility.set_location('info type ' || l_information_type,4);
3183 chg_scd;
3184 --Bug#2146912 Added condition for GHR_US_PAR_TSP
3185 elsif l_information_type = 'GHR_US_PAR_TSP' then
3186 hr_utility.set_location(l_proc,60);
3187 hr_utility.set_location('info type ' || l_information_type,4);
3188 scd_tsp;
3189 elsif l_information_type = 'GHR_US_PAR_NON_PAY_DUTY_STATUS' then
3190 hr_utility.set_location(l_proc,80);
3191 hr_utility.set_location('info type ' || l_information_type,4);
3192 non_pay_duty;
3193 elsif l_information_type = 'GHR_US_PAR_LWOP_INFO' then
3194 hr_utility.set_location(l_proc,90);
3195 lwop_info;
3196 elsif l_information_type = 'GHR_US_PAR_AWARDS_BONUS' then
3197 hr_utility.set_location(l_proc,95);
3198 gov_awards;
3199 elsif l_information_type = 'GHR_US_PAR_CHG_HOURS' then
3200 hr_utility.set_location(l_proc,100);
3201 chg_sched_hours;
3202 elsif l_information_type = 'GHR_US_PAR_CHG_TEN' then
3203 hr_utility.set_location(l_proc,110);
3204 chg_in_tenure;
3205 -- Bug#2759379 Added condition for GHR_US_PAR_FEGLI
3206 elsif l_information_type = 'GHR_US_PAR_FEGLI' then
3207 hr_utility.set_location(l_proc,120);
3208 chg_in_fegli;
3209 -- Bug#3385386 Added the following if condition.
3210 elsif l_information_type = 'GHR_US_PAR_FOR_TRANSER_ALLOW' then
3211 hr_utility.set_location(l_proc,125);
3212 fta;
3213 elsif l_information_type = 'GHR_US_PAR_NFC_SEPARATION_INFO' then
3214 hr_utility.set_location(l_proc,130);
3215 nfc_separation;
3216 elsif l_information_type = 'GHR_US_PAR_ETHNICITY_RACE' then
3217 hr_utility.set_location(l_proc,120);
3218 hr_utility.set_location('info type ' || l_information_type,1);
3219 ethnic_race_info;
3220 elsif l_information_type = 'GHR_US_PAR_BENEFITS' then
3221 hr_utility.set_location(l_proc,125);
3222 hr_utility.set_location('info type ' || l_information_type,1);
3223 appt_benefits;
3224 --Bug# 5039072 Added procedure service_obligation.
3225 elsif l_information_type = 'GHR_US_PAR_SERVICE_OBLIGATION' then
3226 hr_utility.set_location(l_proc,130);
3227 hr_utility.set_location('info type ' || l_information_type,1);
3228 service_obligation;
3229 -- Bug 4280026
3230 elsif l_information_type = 'GHR_US_PAR_EMERG_ESSNTL_ASG' then
3231 hr_utility.set_location(l_proc,135);
3232 hr_utility.set_location('info type ' || l_information_type,1);
3233 key_emergency_essntl;
3234 -- Bug 5482191
3235 elsif l_information_type = 'GHR_US_PAR_CONVERSION_DATES' then
3236 hr_utility.set_location(l_proc,140);
3237 hr_utility.set_location('info type ' || l_information_type,1);
3238 ghr_conv_dates;
3239 /* --Begin Bug# 4588575
3240 elsif l_information_type = 'GHR_US_PAR_PROBATION_INFO' then
3241 hr_utility.set_location(l_proc,270);
3242 hr_utility.set_location('info type ' || l_information_type,270);
3243 ghr_prob_info;
3244 elsif l_information_type = 'GHR_US_PAR_SCD_INFO' then
3245 hr_utility.set_location(l_proc,280);
3246 hr_utility.set_location('info type ' || l_information_type,280);
3247 ghr_scd_info;
3248 --end Bug# 4588575
3249 */ --Backout the changes done for Bug# 4588575
3250 -- Modifications related to Bug#6312144 -- New RPA EIT Benefits
3251 elsif l_information_type = 'GHR_US_PAR_BENEFITS_CONT' then
3252 hr_utility.set_location(l_proc,5);
3253 hr_utility.set_location('info type ' || l_information_type,1);
3254 ipa_benefits_cont;
3255 elsif l_information_type = 'GHR_US_PAR_RETIRMENT_SYS_INFO' then
3256 hr_utility.set_location(l_proc,5);
3257 hr_utility.set_location('info type ' || l_information_type,1);
3258 retirement_system_info;
3259 elsif l_information_type = 'GHR_US_PAR_BENEFIT_INFO' then
3260 hr_utility.set_location(l_proc,5);
3261 hr_utility.set_location('info type ' || l_information_type,1);
3262 par_benefits;
3263 -- Modifications related to Bug#6312144 -- New RPA EIT Benefits
3264 end if;
3265
3266 -- No defaulting reqd. for the families (Recruitment Bonus, Relocation Bonus,
3267 -- Gov. Awards,salary_chg, denial_wgi,realignment,chg_hours,posn_chg
3268 -- because for these it makes no sense to default existing details
3269 -- as the user will have to enter only the current data and
3270 -- would not want to see the existing data.
3271 -- Bug#4089400 Added condition to skip the DLEI Processing for Correction Actions.
3272 IF l_information_type = 'GHR_US_PAR_SALARY_CHG' THEN
3273 IF NVL(l_noa_family_code,'C') <> 'CORRECT' THEN
3274 l_psi := ghr_pa_requests_pkg.get_personnel_system_indicator
3275 (p_position_id,
3276 p_effective_date);
3277
3278 IF l_psi <> '00' THEN
3279 OPEN c_afhr_noac_sal_chg;
3280 FETCH c_afhr_noac_sal_chg INTO l_dlei_date;
3281 IF l_dlei_date is not null THEN
3282 set_ei(l_org_rec.rei_information5,
3283 fnd_date.date_to_canonical(l_dlei_date),
3284 l_rei_rec.rei_information5,'Y');
3285 ELSE
3286 -- Bug 3263140
3287 --Setting the value to NULL only if the original value is NULL
3288 IF l_org_rec.rei_information5 IS NULL THEN
3289 set_ei(l_org_rec.rei_information5,
3290 null,l_rei_rec.rei_information5,'Y');
3291 END IF;
3292 END IF;
3293 CLOSE c_afhr_noac_sal_chg;
3294
3295 ELSE
3296
3297 OPEN c_noac_sal_chg;
3298 FETCH c_noac_sal_chg INTO l_dlei_date;
3299 IF l_dlei_date is not null THEN
3300 set_ei(l_org_rec.rei_information5,
3301 fnd_date.date_to_canonical(l_dlei_date),
3302 l_rei_rec.rei_information5,'Y');
3303 ELSE
3304 -- Bug 3263140
3305 --Setting the value to NULL only if the original value is NULL
3306 IF l_org_rec.rei_information5 IS NULL THEN
3307 set_ei(l_org_rec.rei_information5,
3308 null,l_rei_rec.rei_information5,'Y');
3309 END IF;
3310 END IF;
3311 CLOSE c_noac_sal_chg;
3312 END IF;
3313 END IF;
3314 -- Bug#4126188 Populating Date Arrived Personnel office
3315 FOR c_posn_to_frm IN c_position LOOP
3316 l_from_position_id :=c_posn_to_frm.from_position_id;
3317 l_to_position_id := c_posn_to_frm.to_position_id;
3318 END LOOP;
3319 FOR pa_rec in c_pa_req_2noa_dtls LOOP
3320 l_second_noa_code := pa_rec.second_noa_code;
3321 END LOOP;
3322 IF l_first_noa_code IN ('702','703','713') OR
3323 (l_noa_family_code = 'CORRECT' AND l_second_noa_code IN ('702','703','713')) THEN
3324 ghr_history_fetch.fetch_positionei
3325 (p_position_id => l_from_position_id,
3326 p_information_type => 'GHR_US_POS_GRP1',
3327 p_date_effective => trunc(nvl(p_effective_date,sysdate)),
3328 p_pos_ei_data => l_pos_ei_data
3329 );
3330 l_from_poid := l_pos_ei_data.poei_information3;
3331 l_pos_ei_data := NULL;
3332 ghr_history_fetch.fetch_positionei
3333 (p_position_id => l_to_position_id,
3334 p_information_type => 'GHR_US_POS_GRP1',
3335 p_date_effective => trunc(nvl(p_effective_date,sysdate)),
3336 p_pos_ei_data => l_pos_ei_data
3337 );
3338 l_to_poid := l_pos_ei_data.poei_information3;
3339 /*Start - Bug 6129752*/
3340 IF p_assignment_id IS NULL THEN
3341 FOR C_GET_ASSIGNMENNT_ID IN GET_ASSIGNEMNT_ID LOOP
3342 l_assignment_id := C_GET_ASSIGNMENNT_ID.assignment_id;
3343 IF ( hr_utility.debug_enabled()) THEN
3344 hr_utility.set_location(' P_Assignment id is null and l_assignment is'||l_assignment_id,1733);
3345 END IF;
3346 END LOOP;
3347 END IF;
3348 /*End - Bug 6129752*/
3349 IF l_from_poid = l_to_poid THEN
3350 IF l_noa_family_code = 'CORRECT' THEN
3351 ghr_history_api.get_g_session_var(l_session);
3352 l_session1 := l_session;
3353 l_session.noa_id_correct := NULL;
3354 ghr_history_api.reinit_g_session_var;
3355 ghr_history_api.set_g_session_var(l_session);
3356 ghr_history_fetch.fetch_asgei
3357 (p_assignment_id => NVL(l_assignment_id,p_assignment_id),
3358 p_information_type => 'GHR_US_ASG_NON_SF52',
3359 p_date_effective => p_effective_date-1,
3360 p_asg_ei_data => l_asg_ei_data
3361 );
3362 ghr_history_api.reinit_g_session_var;
3363 ghr_history_api.set_g_session_var(l_session1);
3364 ELSE
3365 ghr_history_fetch.fetch_asgei
3366 (p_assignment_id => NVL(l_assignment_id,p_assignment_id),
3367 p_information_type => 'GHR_US_ASG_NON_SF52',
3368 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
3369 p_asg_ei_data => l_asg_ei_data
3370 );
3371 END IF;
3372 set_ei(l_org_rec.rei_information6,l_asg_ei_data.aei_information3,l_rei_rec.rei_information6,'Y');
3373 /*Start - Bug 6129752*/
3374 ELSIF l_to_poid IS NULL AND l_noa_family_code = 'CORRECT' THEN
3375 ghr_history_fetch.fetch_asgei
3376 (p_assignment_id => NVL(l_assignment_id,p_assignment_id),
3377 p_information_type => 'GHR_US_ASG_NON_SF52',
3378 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
3379 p_asg_ei_data => l_asg_ei_data
3380 );
3381 set_ei(l_org_rec.rei_information6,l_asg_ei_data.aei_information3,l_rei_rec.rei_information6,'Y');
3382 /*End - Bug 6129752*/
3383 ELSE
3384 FOR c_get_eff_rec in c_get_effective_date
3385 LOOP
3386 set_ei(l_org_rec.rei_information6,fnd_date.date_to_canonical(c_get_eff_rec.effective_date), l_rei_rec.rei_information6,'Y');
3387 exit;
3388 END LOOP;
3389 END IF;
3390 END IF;
3391 --Bug#4126188 End
3392 END IF;
3393
3394 l_rei_rec.information_type := l_information_type;
3395 l_rei_rec.pa_request_id := p_pa_request_id;
3396
3397 hr_utility.set_location('Test Null EI',110);
3398 If l_rei_rec.rei_information1 is not null or l_rei_rec.rei_information2 is not null or
3399 l_rei_rec.rei_information3 is not null or l_rei_rec.rei_information4 is not null or
3400 l_rei_rec.rei_information5 is not null or l_rei_rec.rei_information6 is not null or
3401 l_rei_rec.rei_information7 is not null or l_rei_rec.rei_information8 is not null or
3402 l_rei_rec.rei_information9 is not null or l_rei_rec.rei_information10 is not null or
3403 l_rei_rec.rei_information11 is not null or l_rei_rec.rei_information12 is not null or
3404 l_rei_rec.rei_information13 is not null or l_rei_rec.rei_information14 is not null or
3405 l_rei_rec.rei_information15 is not null or l_rei_rec.rei_information16 is not null or
3406 l_rei_rec.rei_information17 is not null or l_rei_rec.rei_information18 is not null or
3407 l_rei_rec.rei_information19 is not null or l_rei_rec.rei_information20 is not null or
3408 l_rei_rec.rei_information21 is not null or l_rei_rec.rei_information22 is not null or
3409 l_rei_rec.rei_information23 is not null or l_rei_rec.rei_information24 is not null or
3410 l_rei_rec.rei_information25 is not null or l_rei_rec.rei_information26 is not null or
3411 l_rei_rec.rei_information27 is not null or l_rei_rec.rei_information28 is not null or
3412 l_rei_rec.rei_information29 is not null or l_rei_rec.rei_information30 is not null then
3413 hr_utility.set_location('l_update_rei is Y ',1);
3414 l_update_rei := 'Y';
3415 Else
3416 l_update_rei := 'N';
3417 hr_utility.set_location('l_update_rei is N ',1);
3418 End if;
3419
3420 hr_utility.set_location('Test Null EI',110);
3421 determine_operation
3422 (p_pa_request_id => p_pa_request_id,
3423 p_information_type => l_information_type,
3424 p_update_rei => l_update_rei,
3425 p_rei_rec => l_rei_rec,
3426 p_operation_flag => l_flag,
3427 p_pa_request_extra_info_id => l_rei_rec.pa_request_extra_info_id,
3428 p_object_version_number => l_rei_rec.object_version_number
3429 );
3430
3431 hr_utility.set_location('PAR ' || to_char(l_rei_rec.pa_request_id),1);
3432 hr_utility.set_location('Info type ' || l_rei_rec.information_type,2);
3433 hr_utility.set_location('Flag ' || l_flag ,3);
3434
3435 generic_populate_extra_info
3436 (p_rei_rec => l_rei_rec,
3437 p_org_rec => l_org_rec,
3438 p_flag => l_flag
3439 );
3440 --
3441 end loop;
3442 --
3443 end fetch_noa_spec_extra_info;
3444 --
3445 --
3446 --
3447 Procedure fetch_generic_extra_info
3448 (p_pa_request_id in number,
3449 p_person_id in number,
3450 p_assignment_id in number,
3451 p_effective_date in date ,
3452 p_refresh_flag in varchar2 default 'Y'
3453 )
3454 is
3455 l_perf_appraisal ghr_api.special_information_type;
3456 l_update_rei varchar2(1) := 'N';
3457 l_exists boolean := FALSE;
3458 l_flag varchar2(1) := null;
3459 l_rei_rec ghr_pa_request_extra_info%rowtype;
3460 l_org_rec ghr_pa_request_ei_shadow%rowtype;
3461 l_person_id per_people_f.person_id%type;
3462 l_assignment_id per_assignments_f.assignment_id%type;
3463 l_per_refresh_flag varchar2(1);
3464 l_asg_refresh_flag varchar2(1);
3465 l_business_group_id per_assignments_f.business_group_id%type;
3466 l_noa_family_code ghr_noa_families.noa_family_code%type;
3467
3468 l_proc varchar2(72) := g_package || 'fetch_gneric_extra_info';
3469 l_information_type ghr_pa_request_extra_info.information_type%type;
3470 l_person_type per_person_types.system_person_type%type := hr_api.g_varchar2;
3471
3472 cursor c_bus_gp is
3473 select business_group_id
3474 from per_people_f
3475 where person_id = p_person_id
3476 and nvl(p_effective_date,sysdate) between
3477 effective_start_date and effective_end_date;
3478
3479 cursor c_rei_rec is
3480 select pa_request_extra_info_id,
3481 rei_information1,
3482 rei_information2,
3483 rei_information3,
3484 rei_information4,
3485 rei_information5,
3486 rei_information6,
3487 rei_information7,
3488 rei_information8,
3489 rei_information9,
3490 rei_information10,
3491 rei_information11,
3492 rei_information12,
3493 rei_information13,
3494 rei_information14,
3495 rei_information15,
3496 rei_information16,
3497 rei_information17,
3498 rei_information18,
3499 rei_information19,
3500 rei_information20,
3501 rei_information21,
3502 rei_information22,
3503 rei_information23,
3504 rei_information24,
3505 rei_information25,
3506 rei_information26,
3507 rei_information27,
3508 rei_information28,
3509 rei_information29,
3510 rei_information30,
3511 object_version_number
3512 from ghr_pa_request_extra_info
3513 where pa_request_id = p_pa_request_id
3514 and information_type = l_information_type;
3515
3516 cursor c_org_rei_rec is
3517 select pa_request_extra_info_id,
3518 rei_information1,
3519 rei_information2,
3520 rei_information3,
3521 rei_information4,
3522 rei_information5,
3523 rei_information6,
3524 rei_information7,
3525 rei_information8,
3526 rei_information9,
3527 rei_information10,
3528 rei_information11,
3529 rei_information12,
3530 rei_information13,
3531 rei_information14,
3532 rei_information15,
3533 rei_information16,
3534 rei_information17,
3535 rei_information18,
3536 rei_information19,
3537 rei_information20,
3538 rei_information21,
3539 rei_information22,
3540 rei_information23,
3541 rei_information24,
3542 rei_information25,
3543 rei_information26,
3544 rei_information27,
3545 rei_information28,
3546 rei_information29,
3547 rei_information30
3548 from ghr_pa_request_ei_shadow
3549 where pa_request_extra_info_id = l_rei_rec.pa_request_extra_info_id;
3550
3551 cursor c_payroll is
3552 select payroll_id
3553 from per_assignments_f asg
3554 where asg.assignment_id = l_assignment_id
3555 and trunc(nvl(p_effective_date,sysdate))
3556 between asg.effective_start_date
3557 and asg.effective_end_date;
3558
3559 cursor c_noa_fam is
3560 select par.noa_family_code
3561 from ghr_pa_requests par
3562 where pa_request_id = p_pa_request_id;
3563
3564 Cursor c_def_payroll is
3565 select pay.payroll_id
3566 from pay_payrolls_f pay
3567 where payroll_name = 'Biweekly'
3568 and nvl(p_effective_date,sysdate) between
3569 pay.effective_start_date and pay.effective_end_date
3570 and business_group_id = l_business_group_id;
3571
3572 -- Added by Venkat -- Bug # 1236354
3573 cursor c_person_type is
3574 select ppt.system_person_type
3575 from per_person_types ppt,
3576 per_people_f ppf
3577 where ppf.person_id = p_person_id
3578 and ppt.person_type_id = ppf.person_type_id
3579 and p_effective_date
3580 between ppf.effective_start_date
3581 and ppf.effective_end_date;
3582
3583
3584
3585 begin
3586 hr_utility.set_location('Entering fetch generic ',1);
3587 hr_utility.set_location('Entering ' || l_proc,5);
3588 l_update_rei := 'N';
3589 l_flag := null;
3590 l_person_id := p_person_id;
3591 l_assignment_id := p_assignment_id;
3592 l_per_refresh_flag := p_refresh_flag;
3593 l_asg_refresh_flag := p_refresh_flag;
3594
3595 l_flag := p_refresh_flag;
3596
3597 for fam_code in c_noa_fam loop
3598 l_noa_family_code := fam_code.noa_family_code;
3599 end loop;
3600
3601 If l_noa_family_code = 'APP' then
3602 l_flag := 'Y';
3603 l_asg_refresh_flag := 'Y';
3604 End if;
3605
3606 If p_refresh_flag = 'N' then
3607 l_person_id := p_person_id;
3608 If p_person_id is null then
3609 If l_flag = 'N' then
3610 -- check if earlier had information and delete it
3611 l_information_type := 'GHR_US_PAR_PAYROLL_TYPE' ;
3612 l_rei_rec := Null;
3613 l_org_rec := Null;
3614 for rei_rec in c_rei_rec loop
3615 l_rei_rec.pa_request_extra_info_id := rei_rec.pa_request_extra_info_id;
3616 l_rei_rec.object_version_number := rei_rec.object_version_number;
3617 end loop;
3618 If l_rei_rec.pa_request_extra_info_id is not null then
3619 generic_populate_extra_info
3620 (p_rei_rec => l_rei_rec,
3621 p_org_rec => l_org_rec,
3622 p_flag => 'D'
3623 );
3624 End if;
3625 End if;
3626
3627
3628 l_information_type := 'GHR_US_PAR_PERF_APPRAISAL' ;
3629 l_rei_rec := Null;
3630 l_org_rec := Null;
3631
3632 for rei_rec in c_rei_rec loop
3633 l_rei_rec.pa_request_extra_info_id := rei_rec.pa_request_extra_info_id;
3634 l_rei_rec.object_version_number := rei_rec.object_version_number;
3635 end loop;
3636 If l_rei_rec.pa_request_extra_info_id is not null then
3637 generic_populate_extra_info
3638 (p_rei_rec => l_rei_rec,
3639 p_org_rec => l_org_rec,
3640 p_flag => 'D'
3641 );
3642 End if;
3643
3644 l_information_type := Null;
3645 l_rei_rec := Null;
3646 l_org_rec := Null;
3647 End if;
3648
3649 If p_person_id is not null then
3650 if p_person_id = nvl(ghr_par_shd.g_old_rec.person_id,hr_api.g_number) and
3651 trunc(nvl(p_effective_date,sysdate)) <> trunc(nvl(ghr_par_shd.g_old_rec.effective_date,sysdate))
3652 then
3653 l_person_id := p_person_id;
3654 l_per_refresh_flag := 'Y';
3655 Elsif p_person_id <> nvl(ghr_par_shd.g_old_rec.person_id,hr_api.g_number) then
3656 l_person_id := p_person_id;
3657 l_per_refresh_flag := 'N';
3658 Else
3659 l_person_id := null;
3660 End if;
3661 End if;
3662
3663
3664 If p_assignment_id is not null and l_flag = 'N' then
3665 if p_assignment_id = nvl(ghr_par_shd.g_old_rec.employee_assignment_id,hr_api.g_number) and
3666 trunc(nvl(p_effective_date,sysdate)) <> trunc(nvl(ghr_par_shd.g_old_rec.effective_date,sysdate))
3667 then
3668 l_assignment_id := p_assignment_id;
3669 l_asg_refresh_flag := 'Y';
3670 Elsif p_assignment_id <> nvl(ghr_par_shd.g_old_rec.employee_assignment_id,hr_api.g_number) then
3671 --and l_noa_family_code <> 'APP'
3672 l_assignment_id := p_assignment_id;
3673 l_asg_refresh_flag := 'N';
3674 Else
3675 l_assignment_id := null;
3676 End if;
3677 End if;
3678
3679 End if;
3680
3681 -- 1. For 'GHR_US_PAR_PAYROLL_TYPE'
3682
3683 if l_assignment_id is not null
3684 or l_noa_family_code = 'APP' then
3685 l_information_type := 'GHR_US_PAR_PAYROLL_TYPE';
3686 for rei_rec in c_rei_rec loop
3687 l_rei_rec.pa_request_extra_info_id := rei_rec.pa_request_extra_info_id;
3688 l_rei_rec.rei_information1 := rei_rec.rei_information1;
3689 l_rei_rec.rei_information2 := rei_rec.rei_information2;
3690 l_rei_rec.rei_information3 := rei_rec.rei_information3;
3691 l_rei_rec.rei_information4 := rei_rec.rei_information4;
3692 l_rei_rec.rei_information5 := rei_rec.rei_information5;
3693 l_rei_rec.rei_information6 := rei_rec.rei_information6;
3694 l_rei_rec.rei_information7 := rei_rec.rei_information7;
3695 l_rei_rec.rei_information8 := rei_rec.rei_information8;
3696 l_rei_rec.rei_information9 := rei_rec.rei_information9;
3697 l_rei_rec.rei_information10 := rei_rec.rei_information10;
3698 l_rei_rec.rei_information11 := rei_rec.rei_information11;
3699 l_rei_rec.rei_information12 := rei_rec.rei_information12;
3700 l_rei_rec.rei_information13 := rei_rec.rei_information13;
3701 l_rei_rec.rei_information14 := rei_rec.rei_information14;
3702 l_rei_rec.rei_information15 := rei_rec.rei_information15;
3703 l_rei_rec.rei_information16 := rei_rec.rei_information16;
3704 l_rei_rec.rei_information17 := rei_rec.rei_information17;
3705 l_rei_rec.rei_information18 := rei_rec.rei_information18;
3706 l_rei_rec.rei_information19 := rei_rec.rei_information19;
3707 l_rei_rec.rei_information20 := rei_rec.rei_information20;
3708 l_rei_rec.rei_information21 := rei_rec.rei_information21;
3709 l_rei_rec.rei_information22 := rei_rec.rei_information22;
3710 l_rei_rec.rei_information23 := rei_rec.rei_information23;
3711 l_rei_rec.rei_information24 := rei_rec.rei_information24;
3712 l_rei_rec.rei_information25 := rei_rec.rei_information25;
3713 l_rei_rec.rei_information26 := rei_rec.rei_information26;
3714 l_rei_rec.rei_information27 := rei_rec.rei_information27;
3715 l_rei_rec.rei_information28 := rei_rec.rei_information28;
3716 l_rei_rec.rei_information29 := rei_rec.rei_information29;
3717 l_rei_rec.rei_information30 := rei_rec.rei_information30;
3718 End loop;
3719
3720
3721 for rei_rec in c_org_rei_rec loop
3722 l_org_rec.rei_information1 := rei_rec.rei_information1;
3723 l_org_rec.rei_information2 := rei_rec.rei_information2;
3724 l_org_rec.rei_information3 := rei_rec.rei_information3;
3725 l_org_rec.rei_information4 := rei_rec.rei_information4;
3726 l_org_rec.rei_information5 := rei_rec.rei_information5;
3727 l_org_rec.rei_information6 := rei_rec.rei_information6;
3728 l_org_rec.rei_information7 := rei_rec.rei_information7;
3729 l_org_rec.rei_information8 := rei_rec.rei_information8;
3730 l_org_rec.rei_information9 := rei_rec.rei_information9;
3731 l_org_rec.rei_information10 := rei_rec.rei_information10;
3732 l_org_rec.rei_information11 := rei_rec.rei_information11;
3733 l_org_rec.rei_information12 := rei_rec.rei_information12;
3734 l_org_rec.rei_information13 := rei_rec.rei_information13;
3735 l_org_rec.rei_information14 := rei_rec.rei_information14;
3736 l_org_rec.rei_information15 := rei_rec.rei_information15;
3737 l_org_rec.rei_information16 := rei_rec.rei_information16;
3738 l_org_rec.rei_information17 := rei_rec.rei_information17;
3739 l_org_rec.rei_information18 := rei_rec.rei_information18;
3740 l_org_rec.rei_information19 := rei_rec.rei_information19;
3741 l_org_rec.rei_information20 := rei_rec.rei_information20;
3742 l_org_rec.rei_information21 := rei_rec.rei_information21;
3743 l_org_rec.rei_information22 := rei_rec.rei_information22;
3744 l_org_rec.rei_information23 := rei_rec.rei_information23;
3745 l_org_rec.rei_information24 := rei_rec.rei_information24;
3746 l_org_rec.rei_information25 := rei_rec.rei_information25;
3747 l_org_rec.rei_information26 := rei_rec.rei_information26;
3748 l_org_rec.rei_information27 := rei_rec.rei_information27;
3749 l_org_rec.rei_information28 := rei_rec.rei_information28;
3750 l_org_rec.rei_information29 := rei_rec.rei_information29;
3751 l_org_rec.rei_information30 := rei_rec.rei_information30;
3752 end loop;
3753
3754
3755 hr_utility.set_location(l_proc,10);
3756 l_exists := FALSE;
3757
3758 If nvl(l_noa_family_code,hr_api.g_varchar2) = 'APP' then
3759 hr_utility.set_location(l_proc,11);
3760 l_asg_refresh_flag := 'Y';
3761 If p_person_id is not null then
3762 for bus_gp in c_bus_gp loop
3763 hr_utility.set_location('bus gp is ' || bus_gp.business_group_id,12);
3764 l_business_group_id := bus_gp.business_group_id;
3765 end loop;
3766 Else
3767 fnd_profile.get('PER_BUSINESS_GROUP_ID',l_business_group_id);
3768 hr_utility.set_location('bus gp is ' || l_business_group_id,13);
3769 End if;
3770 for def_payroll in c_def_payroll loop
3771 hr_utility.set_location(l_proc,14);
3772 l_exists := TRUE;
3773 set_ei(l_org_rec.rei_information3,to_char(def_payroll.payroll_id),l_rei_rec.rei_information3,l_asg_refresh_flag);
3774 l_rei_rec.information_type := 'GHR_US_PAR_PAYROLL_TYPE';
3775 exit;
3776 end loop;
3777 ELSE -- For conversion of exemployee defaults to 'Biweekly' -- Bug # 1236354
3778 hr_utility.set_location(l_proc,15);
3779 FOR person_type_rec in c_person_type LOOP
3780 l_person_type := person_type_rec.system_person_type;
3781 hr_utility.set_location(' Person Type is ' || l_person_type,16);
3782 EXIT;
3783 END LOOP;
3784 IF l_noa_family_code = 'CONV_APP' and l_person_type = 'EX_EMP' THEN
3785 l_asg_refresh_flag := 'Y';
3786 IF p_person_id is not null THEN
3787 FOR bus_gp in c_bus_gp LOOP
3788 hr_utility.set_location('bus gp is ' || bus_gp.business_group_id,17);
3789 l_business_group_id := bus_gp.business_group_id;
3790 END LOOP;
3791 END IF;
3792 FOR def_payroll in c_def_payroll LOOP
3793 hr_utility.set_location(l_proc,18);
3794 l_exists := TRUE;
3795 set_ei(l_org_rec.rei_information3,to_char(def_payroll.payroll_id),l_rei_rec.rei_information3,l_asg_refresh_flag);
3796 l_rei_rec.information_type := 'GHR_US_PAR_PAYROLL_TYPE';
3797 exit;
3798 END LOOP;
3799 ELSE -- fetch payroll from employee's assignment record
3800 FOR payroll in C_payroll LOOP
3801 hr_utility.set_location(l_proc,19);
3802 l_exists := TRUE;
3803 set_ei(l_org_rec.rei_information3,to_char(payroll.payroll_id),l_rei_rec.rei_information3,l_asg_refresh_flag);
3804 l_rei_rec.information_type := 'GHR_US_PAR_PAYROLL_TYPE';
3805 END LOOP;
3806 END IF;
3807 END IF;
3808
3809 If l_rei_rec.rei_information3 is not null then
3810 hr_utility.set_location(l_proc,20);
3811 l_update_rei := 'Y';
3812 End if;
3813
3814 l_rei_rec.pa_request_id := p_pa_request_id;
3815 hr_utility.set_location(l_proc,25);
3816 determine_operation
3817 (p_pa_request_id => p_pa_request_id,
3818 p_information_type => 'GHR_US_PAR_PAYROLL_TYPE',
3819 p_update_rei => l_update_rei,
3820 p_rei_rec => l_rei_rec,
3821 p_operation_flag => l_flag,
3822 p_pa_request_extra_info_id => l_rei_rec.pa_request_extra_info_id,
3823 p_object_version_number => l_rei_rec.object_version_number
3824 );
3825
3826 hr_utility.set_location(l_proc,30);
3827 generic_populate_extra_info
3828 (p_rei_rec => l_rei_rec,
3829 p_org_rec => l_org_rec,
3830 p_flag => l_flag
3831 );
3832 End if;
3833
3834 l_rei_rec := null;
3835 l_org_rec := null;
3836
3837 --2. p_information_type = 'GHR_US_PAR_PERF_APPRAISAL' then
3838
3839 l_update_rei := 'N';
3840 l_flag := null;
3841 l_exists := FALSE;
3842
3843
3844 If l_person_id is not null then
3845 l_information_type := 'GHR_US_PAR_PERF_APPRAISAL' ;
3846 for rei_rec in c_rei_rec loop
3847 l_exists := TRUE;
3848 l_rei_rec.pa_request_extra_info_id := rei_rec.pa_request_extra_info_id;
3849 l_rei_rec.rei_information1 := rei_rec.rei_information1;
3850 l_rei_rec.rei_information2 := rei_rec.rei_information2;
3851 l_rei_rec.rei_information3 := rei_rec.rei_information3;
3852 l_rei_rec.rei_information4 := rei_rec.rei_information4;
3853 l_rei_rec.rei_information5 := rei_rec.rei_information5;
3854 l_rei_rec.rei_information6 := rei_rec.rei_information6;
3855 l_rei_rec.rei_information7 := rei_rec.rei_information7;
3856 l_rei_rec.rei_information8 := rei_rec.rei_information8;
3857 l_rei_rec.rei_information9 := rei_rec.rei_information9;
3858 l_rei_rec.rei_information10 := rei_rec.rei_information10;
3859 l_rei_rec.rei_information11 := rei_rec.rei_information11;
3860 l_rei_rec.rei_information12 := rei_rec.rei_information12;
3861 l_rei_rec.rei_information13 := rei_rec.rei_information13;
3862 l_rei_rec.rei_information14 := rei_rec.rei_information14;
3863 l_rei_rec.rei_information15 := rei_rec.rei_information15;
3864 l_rei_rec.rei_information16 := rei_rec.rei_information16;
3865 l_rei_rec.rei_information17 := rei_rec.rei_information17;
3866 l_rei_rec.rei_information18 := rei_rec.rei_information18;
3867 l_rei_rec.rei_information19 := rei_rec.rei_information19;
3868 l_rei_rec.rei_information20 := rei_rec.rei_information20;
3869 l_rei_rec.rei_information21 := rei_rec.rei_information21;
3870 l_rei_rec.rei_information22 := rei_rec.rei_information22;
3871 l_rei_rec.rei_information23 := rei_rec.rei_information23;
3872 l_rei_rec.rei_information24 := rei_rec.rei_information24;
3873 l_rei_rec.rei_information25 := rei_rec.rei_information25;
3874 l_rei_rec.rei_information26 := rei_rec.rei_information26;
3875 l_rei_rec.rei_information27 := rei_rec.rei_information27;
3876 l_rei_rec.rei_information28 := rei_rec.rei_information28;
3877 l_rei_rec.rei_information29 := rei_rec.rei_information29;
3878 l_rei_rec.rei_information30 := rei_rec.rei_information30;
3879 End loop;
3880
3881 for rei_rec in c_org_rei_rec loop
3882 l_org_rec.rei_information1 := rei_rec.rei_information1;
3883 l_org_rec.rei_information2 := rei_rec.rei_information2;
3884 l_org_rec.rei_information3 := rei_rec.rei_information3;
3885 l_org_rec.rei_information4 := rei_rec.rei_information4;
3886 l_org_rec.rei_information5 := rei_rec.rei_information5;
3887 l_org_rec.rei_information6 := rei_rec.rei_information6;
3888 l_org_rec.rei_information7 := rei_rec.rei_information7;
3889 l_org_rec.rei_information8 := rei_rec.rei_information8;
3890 l_org_rec.rei_information9 := rei_rec.rei_information9;
3891 l_org_rec.rei_information10 := rei_rec.rei_information10;
3892 l_org_rec.rei_information11 := rei_rec.rei_information11;
3893 l_org_rec.rei_information12 := rei_rec.rei_information12;
3894 l_org_rec.rei_information13 := rei_rec.rei_information13;
3895 l_org_rec.rei_information14 := rei_rec.rei_information14;
3896 l_org_rec.rei_information15 := rei_rec.rei_information15;
3897 l_org_rec.rei_information16 := rei_rec.rei_information16;
3898 l_org_rec.rei_information17 := rei_rec.rei_information17;
3899 l_org_rec.rei_information18 := rei_rec.rei_information18;
3900 l_org_rec.rei_information19 := rei_rec.rei_information19;
3901 l_org_rec.rei_information20 := rei_rec.rei_information20;
3902 l_org_rec.rei_information21 := rei_rec.rei_information21;
3903 l_org_rec.rei_information22 := rei_rec.rei_information22;
3904 l_org_rec.rei_information23 := rei_rec.rei_information23;
3905 l_org_rec.rei_information24 := rei_rec.rei_information24;
3906 l_org_rec.rei_information25 := rei_rec.rei_information25;
3907 l_org_rec.rei_information26 := rei_rec.rei_information26;
3908 l_org_rec.rei_information27 := rei_rec.rei_information27;
3909 l_org_rec.rei_information28 := rei_rec.rei_information28;
3910 l_org_rec.rei_information29 := rei_rec.rei_information29;
3911 l_org_rec.rei_information30 := rei_rec.rei_information30;
3912 end loop;
3913
3914
3915 hr_utility.set_location(l_proc,40);
3916 ghr_api.return_special_information
3917 (p_person_id => l_person_id,
3918 p_structure_name => 'US Fed Perf Appraisal',
3919 p_effective_date => nvl(p_effective_date,trunc(sysdate)),
3920 p_special_info => l_perf_appraisal
3921 );
3922 hr_utility.set_location(l_proc,45);
3923 -- use set_ei
3924 set_ei(l_org_rec.rei_information3,l_perf_appraisal.segment2,l_rei_rec.rei_information3,l_per_refresh_flag);
3925 set_ei(l_org_rec.rei_information4,l_perf_appraisal.segment4,l_rei_rec.rei_information4,l_per_refresh_flag);
3926
3927 --Bug 3209817
3928 --For autopopulating 'Rating of Record Level' field
3929 IF ( l_noa_family_code IN ('APP','APPT_TRANS') ) THEN
3930 set_ei(l_org_rec.rei_information5,nvl(l_perf_appraisal.segment5,'X'),l_rei_rec.rei_information5,l_per_refresh_flag);
3931 ELSE
3932 set_ei(l_org_rec.rei_information5,l_perf_appraisal.segment5,l_rei_rec.rei_information5,l_per_refresh_flag);
3933 END IF;
3934 set_ei(l_org_rec.rei_information6,l_perf_appraisal.segment6,l_rei_rec.rei_information6,l_per_refresh_flag);
3935 -- added by skutteti on 6/10/98
3936 set_ei(l_org_rec.rei_information7,l_perf_appraisal.segment1,l_rei_rec.rei_information7,l_per_refresh_flag);
3937 set_ei(l_org_rec.rei_information8,l_perf_appraisal.segment14,l_rei_rec.rei_information8,l_per_refresh_flag);
3938 set_ei(l_org_rec.rei_information9,l_perf_appraisal.segment3,l_rei_rec.rei_information9,l_per_refresh_flag);
3939 set_ei(l_org_rec.rei_information10,l_perf_appraisal.segment7,l_rei_rec.rei_information10,l_per_refresh_flag);
3940 set_ei(l_org_rec.rei_information11,l_perf_appraisal.segment8,l_rei_rec.rei_information11,l_per_refresh_flag);
3941 set_ei(l_org_rec.rei_information12,l_perf_appraisal.segment9,l_rei_rec.rei_information12,l_per_refresh_flag);
3942 set_ei(l_org_rec.rei_information13,l_perf_appraisal.segment10,l_rei_rec.rei_information13,l_per_refresh_flag);
3943 set_ei(l_org_rec.rei_information14,l_perf_appraisal.segment11,l_rei_rec.rei_information14,l_per_refresh_flag);
3944 set_ei(l_org_rec.rei_information15,l_perf_appraisal.segment12,l_rei_rec.rei_information15,l_per_refresh_flag);
3945 set_ei(l_org_rec.rei_information16,l_perf_appraisal.segment13,l_rei_rec.rei_information16,l_per_refresh_flag);
3946 set_ei(l_org_rec.rei_information17,l_perf_appraisal.segment15,l_rei_rec.rei_information17,l_per_refresh_flag);
3947 set_ei(l_org_rec.rei_information18,l_perf_appraisal.segment16,l_rei_rec.rei_information18,l_per_refresh_flag);
3948 -- added by vmididho (4753117) on 05/mar/2007
3949 set_ei(l_org_rec.rei_information19,l_perf_appraisal.segment17,l_rei_rec.rei_information19,l_per_refresh_flag);
3950
3951
3952
3953 -- l_rei_rec.rei_information3 := l_perf_appraisal.segment2;
3954 -- l_rei_rec.rei_information4 := l_perf_appraisal.segment4;
3955 -- l_rei_rec.rei_information5 := l_perf_appraisal.segment5;
3956 -- l_rei_rec.rei_information6 := l_perf_appraisal.segment6;
3957 -- l_rei_rec.information_type := 'GHR_US_PAR_PERF_APPRAISAL' ;
3958
3959 /* If
3960 l_rei_rec.rei_information3 is not null or l_rei_rec.rei_information4 is not null or
3961 l_rei_rec.rei_information5 is not null or l_rei_rec.rei_information6 is not null or
3962 l_rei_rec.rei_information7 is not null or l_rei_rec.rei_information8 is not null or
3963 l_rei_rec.rei_information9 is not null or l_rei_rec.rei_information10 is not null or
3964 l_rei_rec.rei_information11 is not null or l_rei_rec.rei_information12 is not null or
3965 l_rei_rec.rei_information13 is not null or l_rei_rec.rei_information14 is not null or
3966 l_rei_rec.rei_information15 is not null or l_rei_rec.rei_information16 is not null or
3967 l_rei_rec.rei_information17 is not null or l_rei_rec.rei_information18 is not null then
3968
3969 hr_utility.set_location(l_proc,50);
3970 l_update_rei := 'Y';
3971 Else
3972 hr_utility.set_location(l_proc,55);
3973 l_update_rei := 'N';
3974 End if;*/
3975 --Commented the above IF condition since 'US Fed Perf Appraisal' is a mandatory SIT
3976 -- and we will never have to delete this record from the Extra info table.!!
3977 ---Added for bug 3187894
3978 l_update_rei := 'Y';
3979 ---
3980 l_rei_rec.pa_request_id := p_pa_request_id;
3981 l_rei_rec.information_type := 'GHR_US_PAR_PERF_APPRAISAL';
3982
3983 hr_utility.set_location(l_proc,60);
3984
3985 determine_operation
3986 (p_pa_request_id => p_pa_request_id,
3987 p_information_type => 'GHR_US_PAR_PERF_APPRAISAL',
3988 p_update_rei => l_update_rei,
3989 p_rei_rec => l_rei_rec,
3990 p_operation_flag => l_flag,
3991 p_pa_request_extra_info_id => l_rei_rec.pa_request_extra_info_id,
3992 p_object_version_number => l_rei_rec.object_version_number
3993 );
3994
3995 hr_utility.set_location(l_proc,65);
3996 generic_populate_extra_info
3997 (p_rei_rec => l_rei_rec,
3998 p_org_rec => l_org_rec,
3999 p_flag => l_flag
4000 );
4001 End if;
4002 hr_utility.set_location('Leaving ' || l_proc,70);
4003 End fetch_generic_extra_info;
4004
4005
4006 -- Get Information Type
4007 Procedure get_information_type
4008 (p_noa_id in ghr_nature_of_actions.nature_of_action_id%type,
4009 p_information_type out NOCOPY ghr_pa_request_info_types.information_type%type
4010 )
4011 is
4012
4013 l_proc varchar2(72) := g_package || 'get_information_type';
4014
4015 Cursor c_info_type is
4016 Select pit.information_type
4017 from ghr_pa_request_info_types pit,
4018 ghr_noa_families nfa,
4019 ghr_families fam
4020 where nfa.nature_of_action_id = p_noa_id
4021 and nfa.noa_family_code = fam.noa_family_code
4022 and fam.pa_info_type_flag = 'Y'
4023 and pit.noa_family_code = fam.noa_family_code
4024 and pit.information_type like 'GHR_US%';
4025
4026
4027 Begin
4028 If p_noa_id is not null then
4029 for info_type in c_info_type loop
4030 p_information_type := info_type.information_type;
4031 end loop;
4032 Else
4033 p_information_type := null;
4034 End if;
4035
4036 End get_information_type;
4037
4038 Procedure determine_operation
4039 (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
4040 p_information_type in ghr_pa_request_info_types.information_type%type,
4041 p_update_rei in varchar2,
4042 p_rei_rec in ghr_pa_request_extra_info%rowtype,
4043 p_operation_flag out NOCOPY varchar2,
4044 p_pa_request_extra_info_id out NOCOPY ghr_pa_request_extra_info.pa_request_extra_info_id%type,
4045 p_object_version_number out NOCOPY ghr_pa_requests.object_version_number%type
4046 ) is
4047
4048 l_proc varchar2(72) := 'determine_operation';
4049 l_object_version_number ghr_pa_requests.object_version_number%type;
4050 l_pa_request_extra_info_id ghr_pa_request_extra_info.pa_request_extra_info_id%type;
4051 l_exists boolean := FALSE;
4052 l_dummy ghr_pa_request_extra_info%rowtype;
4053 l_rei_rec ghr_pa_request_extra_info%rowtype;
4054 cursor c_rei_rec is
4055 select rei.pa_request_extra_info_id,
4056 rei.object_version_number,
4057 rei.rei_information1,
4058 rei.rei_information2,
4059 rei.rei_information3,
4060 rei.rei_information4,
4061 rei.rei_information5,
4062 rei.rei_information6,
4063 rei.rei_information7,
4064 rei.rei_information8,
4065 rei.rei_information9,
4066 rei.rei_information10,
4067 rei.rei_information11,
4068 rei.rei_information12,
4069 rei.rei_information13,
4070 rei.rei_information14,
4071 rei.rei_information15,
4072 rei.rei_information16,
4073 rei.rei_information17,
4074 rei.rei_information18,
4075 rei.rei_information19,
4076 rei.rei_information20,
4077 rei.rei_information21,
4078 rei.rei_information22,
4079 rei.rei_information23,
4080 rei.rei_information24,
4081 rei.rei_information25,
4082 rei.rei_information26,
4083 rei.rei_information27,
4084 rei.rei_information28,
4085 rei.rei_information29,
4086 rei.rei_information30
4087 from ghr_pa_request_extra_info rei
4088 where rei.pa_request_id = p_pa_request_id
4089 and rei.information_type = p_information_type;
4090
4091 Begin
4092 hr_utility.set_location('Entering '|| l_proc,10);
4093 l_rei_rec := p_rei_rec;
4094
4095 for rei_rec in c_rei_rec loop
4096 p_object_version_number := rei_rec.object_version_number;
4097 p_pa_request_extra_info_id := rei_rec.pa_request_extra_info_id;
4098 -- l_dummy.rei_information1 := rei_rec.rei_information1;
4099 -- l_dummy.rei_information2 := rei_rec.rei_information2;
4100 l_dummy.rei_information3 := rei_rec.rei_information3;
4101 l_dummy.rei_information4 := rei_rec.rei_information4;
4102 l_dummy.rei_information5 := rei_rec.rei_information5;
4103 l_dummy.rei_information6 := rei_rec.rei_information6;
4104 l_dummy.rei_information7 := rei_rec.rei_information7;
4105 l_dummy.rei_information8 := rei_rec.rei_information8;
4106 l_dummy.rei_information9 := rei_rec.rei_information9;
4107 l_dummy.rei_information10 := rei_rec.rei_information10;
4108 l_dummy.rei_information11 := rei_rec.rei_information11;
4109 l_dummy.rei_information12 := rei_rec.rei_information12;
4110 l_dummy.rei_information13 := rei_rec.rei_information13;
4111 l_dummy.rei_information14 := rei_rec.rei_information14;
4112 l_dummy.rei_information15 := rei_rec.rei_information15;
4113 l_dummy.rei_information16 := rei_rec.rei_information16;
4114 l_dummy.rei_information17 := rei_rec.rei_information17;
4115 l_dummy.rei_information18 := rei_rec.rei_information18;
4116 l_dummy.rei_information19 := rei_rec.rei_information19;
4117 l_dummy.rei_information20 := rei_rec.rei_information20;
4118 l_dummy.rei_information21 := rei_rec.rei_information21;
4119 l_dummy.rei_information22 := rei_rec.rei_information22;
4120 l_dummy.rei_information23 := rei_rec.rei_information23;
4121 l_dummy.rei_information24 := rei_rec.rei_information24;
4122 l_dummy.rei_information25 := rei_rec.rei_information25;
4123 l_dummy.rei_information26 := rei_rec.rei_information26;
4124 l_dummy.rei_information27 := rei_rec.rei_information27;
4125 l_dummy.rei_information28 := rei_rec.rei_information28;
4126 l_dummy.rei_information29 := rei_rec.rei_information29;
4127 l_dummy.rei_information30 := rei_rec.rei_information30;
4128 hr_utility.set_location(l_proc,20);
4129 l_exists := true;
4130 end loop;
4131
4132 -- If non-sf52 data already exists, then as a result of the new changes, there might be need to update/ delete it.
4133 -- If there are changes, then update else do nothing.
4134 -- If the update_flag (update_rei) is 'N' , then delete the existing data.
4135 -- Else if non-sf52 does not exists, then if update_rei = 'Y', then Create. else do nothing.
4136
4137
4138 If l_exists then
4139 hr_utility.set_location(' rei exists ',1);
4140 If p_update_rei = 'Y' then
4141 hr_utility.set_location(' Update ',1);
4142 If nvl(l_dummy.rei_information3,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information3,hr_api.g_varchar2) or
4143 nvl(l_dummy.rei_information4,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information4,hr_api.g_varchar2) or
4144 nvl(l_dummy.rei_information5,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information5,hr_api.g_varchar2) or
4145 nvl(l_dummy.rei_information6,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information6,hr_api.g_varchar2) or
4146 nvl(l_dummy.rei_information7,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information7,hr_api.g_varchar2) or
4147 nvl(l_dummy.rei_information8,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information8,hr_api.g_varchar2) or
4148 nvl(l_dummy.rei_information9,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information9,hr_api.g_varchar2) or
4149 nvl(l_dummy.rei_information10,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information10,hr_api.g_varchar2) or
4150 nvl(l_dummy.rei_information11,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information11,hr_api.g_varchar2) or
4151 nvl(l_dummy.rei_information12,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information12,hr_api.g_varchar2) or
4152 nvl(l_dummy.rei_information13,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information13,hr_api.g_varchar2) or
4153 nvl(l_dummy.rei_information14,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information14,hr_api.g_varchar2) or
4154 nvl(l_dummy.rei_information15,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information15,hr_api.g_varchar2) or
4155 nvl(l_dummy.rei_information16,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information16,hr_api.g_varchar2) or
4156 nvl(l_dummy.rei_information17,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information17,hr_api.g_varchar2) or
4157 nvl(l_dummy.rei_information18,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information18,hr_api.g_varchar2) or
4158 nvl(l_dummy.rei_information19,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information19,hr_api.g_varchar2) or
4159 nvl(l_dummy.rei_information20,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information20,hr_api.g_varchar2) or
4160 nvl(l_dummy.rei_information21,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information21,hr_api.g_varchar2) or
4161 nvl(l_dummy.rei_information22,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information22,hr_api.g_varchar2) or
4162 nvl(l_dummy.rei_information23,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information23,hr_api.g_varchar2) or
4163 nvl(l_dummy.rei_information24,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information24,hr_api.g_varchar2) or
4164 nvl(l_dummy.rei_information25,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information25,hr_api.g_varchar2) or
4165 nvl(l_dummy.rei_information26,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information26,hr_api.g_varchar2) or
4166 nvl(l_dummy.rei_information27,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information27,hr_api.g_varchar2) or
4167 nvl(l_dummy.rei_information28,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information28,hr_api.g_varchar2) or
4168 nvl(l_dummy.rei_information29,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information29,hr_api.g_varchar2) or
4169 nvl(l_dummy.rei_information30,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information30,hr_api.g_varchar2) then
4170 hr_utility.set_location('operation is Update',1);
4171 p_operation_flag := 'U';
4172 hr_utility.set_location(l_proc,25);
4173 End if;
4174 Else
4175 hr_utility.set_location(' Delete ',1);
4176 hr_utility.set_location(l_proc,30);
4177 p_operation_flag := 'D';
4178 End if;
4179 Else
4180 If p_update_rei = 'Y' then
4181 hr_utility.set_location(l_proc,35);
4182 hr_utility.set_location(' Create ',1);
4183 p_operation_flag := 'C';
4184 Else
4185 hr_utility.set_location(' Null ',1);
4186 hr_utility.set_location(l_proc,40);
4187 p_operation_flag := null;
4188 End if;
4189 End if;
4190 End determine_operation;
4191
4192 Procedure generic_populate_extra_info
4193 (p_rei_rec in ghr_pa_request_extra_info%rowtype,
4194 p_org_rec in ghr_pa_request_ei_shadow%rowtype,
4195 p_flag in varchar2
4196 )
4197 is
4198
4199 l_flag varchar2(1) := p_flag;
4200 l_rei_rec ghr_pa_request_extra_info%rowtype := p_rei_rec;
4201 l_org_rec ghr_pa_request_ei_shadow%rowtype;
4202 l_proc varchar2(72) := 'generic_populate_extra_info';
4203
4204 Begin
4205 hr_utility.set_location('Entering ' || l_proc,10);
4206 hr_utility.set_location('Flag := ' || l_flag,1);
4207
4208 -- l_flag :
4209 -- C - Create
4210 -- U - Update
4211 -- D - Delete
4212 -- Null - do nothing
4213
4214 If l_flag is not null then
4215 hr_utility.set_location(l_proc,15);
4216
4217 If l_flag = 'C' then
4218 hr_utility.set_location(l_proc,20);
4219 ghr_par_extra_info_api.create_pa_request_extra_info
4220 (p_pa_request_id => l_rei_rec.pa_request_id,
4221 p_information_type => l_rei_rec.information_type, -- to be replaced by the info_type returned by the get_infor._type
4222 p_rei_information_category => l_rei_rec.information_type,
4223 p_rei_information1 => l_rei_rec.rei_information1,
4224 p_rei_information2 => l_rei_rec.rei_information2,
4225 p_rei_information3 => l_rei_rec.rei_information3,
4226 p_rei_information4 => l_rei_rec.rei_information4,
4227 p_rei_information5 => l_rei_rec.rei_information5,
4228 p_rei_information6 => l_rei_rec.rei_information6,
4229 p_rei_information7 => l_rei_rec.rei_information7,
4230 p_rei_information8 => l_rei_rec.rei_information8,
4231 p_rei_information9 => l_rei_rec.rei_information9,
4232 p_rei_information10 => l_rei_rec.rei_information10,
4233 p_rei_information11 => l_rei_rec.rei_information11,
4234 p_rei_information12 => l_rei_rec.rei_information12,
4235 p_rei_information13 => l_rei_rec.rei_information13,
4236 p_rei_information14 => l_rei_rec.rei_information14,
4237 p_rei_information15 => l_rei_rec.rei_information15,
4238 p_rei_information16 => l_rei_rec.rei_information16,
4239 p_rei_information17 => l_rei_rec.rei_information17,
4240 p_rei_information18 => l_rei_rec.rei_information18,
4241 p_rei_information19 => l_rei_rec.rei_information19,
4242 p_rei_information20 => l_rei_rec.rei_information20,
4243 p_rei_information21 => l_rei_rec.rei_information21,
4244 p_rei_information22 => l_rei_rec.rei_information22,
4245 p_rei_information23 => l_rei_rec.rei_information23,
4246 p_rei_information24 => l_rei_rec.rei_information24,
4247 p_rei_information25 => l_rei_rec.rei_information25,
4248 p_rei_information26 => l_rei_rec.rei_information26,
4249 p_rei_information27 => l_rei_rec.rei_information27,
4250 p_rei_information28 => l_rei_rec.rei_information28,
4251 p_rei_information29 => l_rei_rec.rei_information29,
4252 p_rei_information30 => l_rei_rec.rei_information30,
4253 P_PA_REQUEST_EXTRA_INFO_ID => l_rei_rec.pa_request_extra_info_id,
4254 P_OBJECT_VERSION_NUMBER => l_rei_rec.object_version_number
4255 );
4256
4257 insert into ghr_pa_request_ei_shadow
4258 (
4259 pa_request_extra_info_id ,
4260 pa_request_id ,
4261 information_type,
4262 rei_information1 ,
4263 rei_information2 ,
4264 rei_information3 ,
4265 rei_information4 ,
4266 rei_information5 ,
4267 rei_information6 ,
4268 rei_information7 ,
4269 rei_information8 ,
4270 rei_information9 ,
4271 rei_information10 ,
4272 rei_information11 ,
4273 rei_information12 ,
4274 rei_information13 ,
4275 rei_information14 ,
4276 rei_information15 ,
4277 rei_information16 ,
4278 rei_information17 ,
4279 rei_information18 ,
4280 rei_information19 ,
4281 rei_information20 ,
4282 rei_information21 ,
4283 rei_information22 ,
4284 rei_information23 ,
4285 rei_information24 ,
4286 rei_information25 ,
4287 rei_information26 ,
4288 rei_information27 ,
4289 rei_information28 ,
4290 rei_information29 ,
4291 rei_information30
4292 ) values
4293 (l_rei_rec.pa_request_extra_info_id,
4294 l_rei_rec.pa_request_id,
4295 l_rei_rec.information_type,
4296 l_rei_rec.rei_information1,
4297 l_rei_rec.rei_information2,
4298 l_rei_rec.rei_information3,
4299 l_rei_rec.rei_information4,
4300 l_rei_rec.rei_information5,
4301 l_rei_rec.rei_information6,
4302 l_rei_rec.rei_information7,
4303 l_rei_rec.rei_information8,
4304 l_rei_rec.rei_information9,
4305 l_rei_rec.rei_information10,
4306 l_rei_rec.rei_information11,
4307 l_rei_rec.rei_information12,
4308 l_rei_rec.rei_information13,
4309 l_rei_rec.rei_information14,
4310 l_rei_rec.rei_information15,
4311 l_rei_rec.rei_information16,
4312 l_rei_rec.rei_information17,
4313 l_rei_rec.rei_information18,
4314 l_rei_rec.rei_information19,
4315 l_rei_rec.rei_information20,
4316 l_rei_rec.rei_information21,
4317 l_rei_rec.rei_information22,
4318 l_rei_rec.rei_information23,
4319 l_rei_rec.rei_information24,
4320 l_rei_rec.rei_information25,
4321 l_rei_rec.rei_information26,
4322 l_rei_rec.rei_information27,
4323 l_rei_rec.rei_information28,
4324 l_rei_rec.rei_information29,
4325 l_rei_rec.rei_information30
4326 );
4327
4328 Elsif l_flag = 'U' then
4329 hr_utility.set_location(l_proc,25);
4330 hr_utility.set_location('update extra info',1);
4331 ghr_par_extra_info_api.update_pa_request_extra_info
4332 (P_PA_REQUEST_EXTRA_INFO_ID => l_rei_rec.pa_request_extra_info_id,
4333 P_OBJECT_VERSION_NUMBER => l_rei_rec.object_version_number ,
4334 p_rei_information1 => l_rei_rec.rei_information1,
4335 p_rei_information2 => l_rei_rec.rei_information2,
4336 p_rei_information3 => l_rei_rec.rei_information3,
4337 p_rei_information4 => l_rei_rec.rei_information4,
4338 p_rei_information5 => l_rei_rec.rei_information5,
4339 p_rei_information6 => l_rei_rec.rei_information6,
4340 p_rei_information7 => l_rei_rec.rei_information7,
4341 p_rei_information8 => l_rei_rec.rei_information8,
4342 p_rei_information9 => l_rei_rec.rei_information9,
4343 p_rei_information10 => l_rei_rec.rei_information10,
4344 p_rei_information11 => l_rei_rec.rei_information11,
4345 p_rei_information12 => l_rei_rec.rei_information12,
4346 p_rei_information13 => l_rei_rec.rei_information13,
4347 p_rei_information14 => l_rei_rec.rei_information14,
4348 p_rei_information15 => l_rei_rec.rei_information15,
4349 p_rei_information16 => l_rei_rec.rei_information16,
4350 p_rei_information17 => l_rei_rec.rei_information17,
4351 p_rei_information18 => l_rei_rec.rei_information18,
4352 p_rei_information19 => l_rei_rec.rei_information19,
4353 p_rei_information20 => l_rei_rec.rei_information20,
4354 p_rei_information21 => l_rei_rec.rei_information21,
4355 p_rei_information22 => l_rei_rec.rei_information22,
4356 p_rei_information23 => l_rei_rec.rei_information23,
4357 p_rei_information24 => l_rei_rec.rei_information24,
4358 p_rei_information25 => l_rei_rec.rei_information25,
4359 p_rei_information26 => l_rei_rec.rei_information26,
4360 p_rei_information27 => l_rei_rec.rei_information27,
4361 p_rei_information28 => l_rei_rec.rei_information28,
4362 p_rei_information29 => l_rei_rec.rei_information29,
4363 p_rei_information30 => l_rei_rec.rei_information30
4364 );
4365 hr_utility.set_location('bef upd of shad ' || 'ovn ' || l_rei_rec.object_version_number,2);
4366
4367 update ghr_pa_request_ei_shadow set
4368 rei_information1 = p_org_rec.rei_information1,
4369 rei_information2 = p_org_rec.rei_information2,
4370 rei_information3 = p_org_rec.rei_information3,
4371 rei_information4 = p_org_rec.rei_information4,
4372 rei_information5 = p_org_rec.rei_information5,
4373 rei_information6 = p_org_rec.rei_information6,
4374 rei_information7 = p_org_rec.rei_information7,
4375 rei_information8 = p_org_rec.rei_information8,
4376 rei_information9 = p_org_rec.rei_information9,
4377 rei_information10 = p_org_rec.rei_information10,
4378 rei_information11 = p_org_rec.rei_information11,
4379 rei_information12 = p_org_rec.rei_information12,
4380 rei_information13 = p_org_rec.rei_information13,
4381 rei_information14 = p_org_rec.rei_information14,
4382 rei_information15 = p_org_rec.rei_information15,
4383 rei_information16 = p_org_rec.rei_information16,
4384 rei_information17 = p_org_rec.rei_information17,
4385 rei_information18 = p_org_rec.rei_information18,
4386 rei_information19 = p_org_rec.rei_information19,
4387 rei_information20 = p_org_rec.rei_information20,
4388 rei_information21 = p_org_rec.rei_information21,
4389 rei_information22 = p_org_rec.rei_information22,
4390 rei_information23 = p_org_rec.rei_information23,
4391 rei_information24 = p_org_rec.rei_information24,
4392 rei_information25 = p_org_rec.rei_information25,
4393 rei_information26 = p_org_rec.rei_information26,
4394 rei_information27 = p_org_rec.rei_information27,
4395 rei_information28 = p_org_rec.rei_information28,
4396 rei_information29 = p_org_rec.rei_information29,
4397 rei_information30 = p_org_rec.rei_information30
4398 where pa_request_extra_info_id = l_rei_rec.pa_request_extra_info_id;
4399
4400
4401 Elsif l_flag = 'D' then
4402 hr_utility.set_location(l_proc,30);
4403 hr_utility.set_location('delete extra info',1);
4404
4405 ghr_par_extra_info_api.delete_pa_request_extra_info
4406 (p_pa_request_extra_info_id => l_rei_rec.pa_request_extra_info_id,
4407 p_object_version_number => l_rei_rec.object_version_number
4408 );
4409 delete from ghr_pa_request_ei_shadow
4410 where pa_request_extra_info_id = l_rei_rec.pa_request_extra_info_id;
4411 End if;
4412 End if;
4413 hr_utility.set_location('Leaving ' || l_proc,40);
4414 end generic_populate_extra_info ;
4415
4416 Procedure set_ei
4417 (p_original in out NOCOPY varchar2,
4418 p_as_in_core in varchar2,
4419 p_as_in_ddf in out NOCOPY varchar2,
4420 p_refresh_flag in varchar2 default 'Y')
4421 is
4422
4423 begin
4424
4425 If p_refresh_flag = 'Y' then
4426 hr_utility.set_location('in set ei - Y ',5);
4427 If nvl(p_as_in_ddf,hr_api.g_varchar2) <> nvl(p_as_in_core,hr_api.g_varchar2) and
4428 nvl(p_as_in_ddf,hr_api.g_varchar2) = nvl(p_original,hr_api.g_varchar2) then
4429 p_as_in_ddf := p_as_in_core;
4430 End if;
4431 Else
4432 hr_utility.set_location('in set ei - N ',6);
4433 p_as_in_ddf := p_as_in_core;
4434 End if;
4435 p_original := p_as_in_core;
4436 End set_ei;
4437
4438 end GHR_NON_SF52_EXTRA_INFO;