[Home] [Help]
246: l_second_noa_code := noa_code.code;
247: exit;
248: END LOOP;
249:
250: hr_utility.set_location('first noa code '||l_first_noa_code , 1);
251: hr_utility.set_location('second noa code '||l_second_noa_code, 2);
252: IF l_first_noa_code = '002' THEN
253: l_noa_code := l_second_noa_code;
254: ELSE
247: exit;
248: END LOOP;
249:
250: hr_utility.set_location('first noa code '||l_first_noa_code , 1);
251: hr_utility.set_location('second noa code '||l_second_noa_code, 2);
252: IF l_first_noa_code = '002' THEN
253: l_noa_code := l_second_noa_code;
254: ELSE
255: l_noa_code := l_first_noa_code;
253: l_noa_code := l_second_noa_code;
254: ELSE
255: l_noa_code := l_first_noa_code;
256: END IF;
257: hr_utility.set_location('l_noa_code is '||l_noa_code, 2);
258: -- Delete the RPA EI if there is a change in Effective Date or
259: -- Person
260: IF p_refresh_flag = 'N' THEN
261: IF (nvl(p_effective_date,hr_api.g_date)
261: IF (nvl(p_effective_date,hr_api.g_date)
262: <> nvl(ghr_par_shd.g_old_rec.effective_date,hr_api.g_date)) or
263: (nvl(p_person_id,hr_api.g_number)
264: <> nvl(ghr_par_shd.g_old_rec.person_id,hr_api.g_number)) then
265: hr_utility.set_location('Inside the Delete IF '||l_noa_code, 2);
266: -- Delete from ghr_pa_request_extra_info
267: DELETE ghr_pa_request_extra_info
268: WHERE INFORMATION_TYPE in ( 'GHR_US_PAR_TERM_RET_GRADE',
269: 'GHR_US_PAR_TERM_RG_PROMO',
278: AND PA_REQUEST_ID = p_pa_request_id;
279: END IF;
280: END IF;
281: IF l_noa_code IN ( '866' ,'890') THEN
282: hr_utility.set_location('Inside the c_866 loop'||l_noa_code, 3);
283: -- Delete from ghr_pa_request_extra_info
284: DELETE ghr_pa_request_extra_info
285: WHERE INFORMATION_TYPE = 'GHR_US_PAR_TERM_RET_GRADE'
286: AND PA_REQUEST_ID = p_pa_request_id;
291: AND PA_REQUEST_ID = p_pa_request_id;
292: --8288066 Added the below or condition for dual actions
293: ELSIF l_noa_code = '702' or (l_noa_code not in ('001','002') and l_second_noa_code = '702') THEN
294: FOR pei_id IN c_702_rei LOOP
295: hr_utility.set_location('Inside the c_702_rei loop'||l_noa_code, 3);
296: -- Delete from ghr_pa_request_extra_info
297: DELETE ghr_pa_request_extra_info
298: WHERE INFORMATION_TYPE = 'GHR_US_PAR_TERM_RG_PROMO'
299: AND PA_REQUEST_ID = p_pa_request_id;
305: EXIT;
306: END LOOP;
307: --8288066 Added the below or condition for dual actions
308: ELSIF l_noa_code = '740' or (l_noa_code not in ('001','002') and l_second_noa_code = '740') THEN
309: hr_utility.set_location('Inside the 740 processing'||l_noa_code, 3);
310: l_existed := FALSE;
311: for pei_rec in c_740_pei loop
312: hr_utility.set_location('PEI rec id'||pei_rec.person_extra_info_id, 4);
313: for rei_rec in c_740_rei loop
308: ELSIF l_noa_code = '740' or (l_noa_code not in ('001','002') and l_second_noa_code = '740') THEN
309: hr_utility.set_location('Inside the 740 processing'||l_noa_code, 3);
310: l_existed := FALSE;
311: for pei_rec in c_740_pei loop
312: hr_utility.set_location('PEI rec id'||pei_rec.person_extra_info_id, 4);
313: for rei_rec in c_740_rei loop
314: hr_utility.set_location(' REI rec id'||rei_rec.rei_information3, 5);
315: IF pei_rec.person_extra_info_id = rei_rec.rei_information3 THEN
316: l_existed := TRUE;
310: l_existed := FALSE;
311: for pei_rec in c_740_pei loop
312: hr_utility.set_location('PEI rec id'||pei_rec.person_extra_info_id, 4);
313: for rei_rec in c_740_rei loop
314: hr_utility.set_location(' REI rec id'||rei_rec.rei_information3, 5);
315: IF pei_rec.person_extra_info_id = rei_rec.rei_information3 THEN
316: l_existed := TRUE;
317: EXIT;
318: ELSE
403: l_existed := FALSE;
404: END IF;
405: end loop;
406: END IF;
407: hr_utility.set_location('Before creating RPA EI'||l_noa_code, 4);
408: -- 8288066 Added the below or condition for dual actions
409: IF l_noa_code = '702' or (l_noa_code not in ('001','002') and l_second_noa_code = '702') THEN
410: FOR pei_id IN c_702 LOOP
411: hr_utility.set_location('In side creation of RPA EI'||l_noa_code, 4);
407: hr_utility.set_location('Before creating RPA EI'||l_noa_code, 4);
408: -- 8288066 Added the below or condition for dual actions
409: IF l_noa_code = '702' or (l_noa_code not in ('001','002') and l_second_noa_code = '702') THEN
410: FOR pei_id IN c_702 LOOP
411: hr_utility.set_location('In side creation of RPA EI'||l_noa_code, 4);
412: l_rei_rec.rei_information3 := pei_id.person_extra_info_id;
413: --l_rei_rec.rei_information4 := pei_id.ret_grade;
414: l_rei_rec.rei_information5 := null;
415: l_rei_rec.information_type := 'GHR_US_PAR_TERM_RG_PROMO';
432: END LOOP;
433: ELSIF l_noa_code IN ('866', '890') THEN
434: IF l_altered_pa_request_id is not null THEN
435: FOR pei_id IN c_866_rei_correct LOOP
436: hr_utility.set_location('866 rei correct ' , 1);
437: l_ret_grade1 := pei_id.pei_information1 || '..'|| pei_id.pei_information2 ||
438: '..' || pei_id.pei_information3 || '..' || pei_id.pei_information4 ||
439: '..' || pei_id.pei_information5 || '..' || pei_id.pei_information6 ||
440: '..' || pei_id.pei_information7 || '..' || pei_id.pei_information8
472: NULL;
473: END;
474: ---------- Bug# 2569180 To avoid PL/SQL Error
475:
476: hr_utility.set_location('866 non correct ' , 2);
477: -- FOR pei_id IN c_866(l_retained_grade_rec.person_extra_info_id) LOOP
478: FOR pei_id IN c_866 LOOP
479: l_ret_grade1 := pei_id.pei_information1 || '..'|| pei_id.pei_information2 ||
480: '..' || pei_id.pei_information3 || '..' || pei_id.pei_information4 ||
570: ghr_pa_request_extra_info b
571: where b.pa_request_id = p_pa_request_id );
572:
573: Begin
574: hr_utility.set_location('Entering ' || l_proc,5);
575: FOR noa_code IN c_noa_code(p_first_noa_id) LOOP
576: l_first_noa_code := noa_code.code;
577: exit;
578: END LOOP;
579: FOR noa_code IN c_noa_code(p_second_noa_id) LOOP
580: l_second_noa_code := noa_code.code;
581: exit;
582: END LOOP;
583: hr_utility.set_location('first noa code '||l_first_noa_code , 1);
584: hr_utility.set_location('second noa code '||l_second_noa_code, 2);
585: IF l_first_noa_code = '002' THEN
586: l_noa_code := l_second_noa_code;
587: ELSE
580: l_second_noa_code := noa_code.code;
581: exit;
582: END LOOP;
583: hr_utility.set_location('first noa code '||l_first_noa_code , 1);
584: hr_utility.set_location('second noa code '||l_second_noa_code, 2);
585: IF l_first_noa_code = '002' THEN
586: l_noa_code := l_second_noa_code;
587: ELSE
588: l_noa_code := l_first_noa_code;
586: l_noa_code := l_second_noa_code;
587: ELSE
588: l_noa_code := l_first_noa_code;
589: END IF;
590: hr_utility.set_location('l_noa_code is '||l_noa_code, 2);
591: --Added the OR condition for dual actions
592: IF (l_second_noa_code in ('702','866','890','740') and l_first_noa_code = '002' ) OR
593: l_first_noa_code in ('702','866','890','740') OR (l_first_noa_code not in ('001','002')
594: and l_second_noa_code in ('702','866','890','740'))
603: p_refresh_flag => p_refresh_flag );
604: -- End
605: END IF;
606: If p_first_noa_id is not null then
607: hr_utility.set_location(l_proc,30);
608: hr_utility.set_location('PER ID'||p_person_id,30);
609: --
610: fetch_noa_spec_extra_info
611: (p_pa_request_id => p_pa_request_id,
604: -- End
605: END IF;
606: If p_first_noa_id is not null then
607: hr_utility.set_location(l_proc,30);
608: hr_utility.set_location('PER ID'||p_person_id,30);
609: --
610: fetch_noa_spec_extra_info
611: (p_pa_request_id => p_pa_request_id,
612: p_noa_id => p_first_noa_id,
615: p_position_id => p_position_id,
616: p_effective_date => trunc(nvl(p_effective_date,sysdate)),
617: p_refresh_flag => p_refresh_flag
618: );
619: hr_utility.set_location(l_proc,65);
620: End if;
621:
622: hr_utility.set_location('PER ID' || p_person_id,65);
623: hr_utility.set_location('Second NOa ID ' || p_second_noa_id,65);
618: );
619: hr_utility.set_location(l_proc,65);
620: End if;
621:
622: hr_utility.set_location('PER ID' || p_person_id,65);
623: hr_utility.set_location('Second NOa ID ' || p_second_noa_id,65);
624: If p_second_noa_id is not null then
625: --Bug#4089400 commented second-noa-code not in 702,866,740 clause
626: IF l_first_noa_code = '002' then --(l_second_noa_code not in ('702','866','740')) and
619: hr_utility.set_location(l_proc,65);
620: End if;
621:
622: hr_utility.set_location('PER ID' || p_person_id,65);
623: hr_utility.set_location('Second NOa ID ' || p_second_noa_id,65);
624: If p_second_noa_id is not null then
625: --Bug#4089400 commented second-noa-code not in 702,866,740 clause
626: IF l_first_noa_code = '002' then --(l_second_noa_code not in ('702','866','740')) and
627: for altered_par_rec in c_altered_par_rec loop
626: IF l_first_noa_code = '002' then --(l_second_noa_code not in ('702','866','740')) and
627: for altered_par_rec in c_altered_par_rec loop
628: l_altered_pa_request_id := altered_par_rec.altered_pa_request_id;
629: end loop;
630: hr_utility.set_location('Creation of new RPA EI Records' ,67);
631: -- Fetch the original rpa extra information and create new RPA Extra info records
632: -- with current PA request id
633: FOR orig_rei_rec IN c_orig_rei_rec LOOP
634: l_new_rei_rec := orig_rei_rec;
633: FOR orig_rei_rec IN c_orig_rei_rec LOOP
634: l_new_rei_rec := orig_rei_rec;
635: l_new_rei_rec.pa_request_id := p_pa_request_id;
636: l_new_rei_rec.pa_request_extra_info_id := NULL;
637: hr_utility.set_location('Creation of new RPA EI Records'||l_new_rei_rec.information_type ,65);
638: -- Begin bug# 12572750
639: -- Removed Detail start date and end date so passing null values to error free for previous records
640: IF l_new_rei_rec.information_type = 'GHR_US_PAR_DET_INFO' AND
641: (l_new_rei_rec.rei_information1 IS NOT NULL OR l_new_rei_rec.rei_information2 IS NOT NULL) THEN
638: -- Begin bug# 12572750
639: -- Removed Detail start date and end date so passing null values to error free for previous records
640: IF l_new_rei_rec.information_type = 'GHR_US_PAR_DET_INFO' AND
641: (l_new_rei_rec.rei_information1 IS NOT NULL OR l_new_rei_rec.rei_information2 IS NOT NULL) THEN
642: hr_utility.set_location('Nullify rei_information1 and rei_information2 '|| l_proc ,166);
643: l_new_rei_rec.rei_information1 := NULL;
644: l_new_rei_rec.rei_information2 := NULL;
645: END IF;
646: -- End bug# 12572750
660: p_position_id => p_position_id,
661: p_effective_date => trunc(nvl(p_effective_date,sysdate)),
662: p_refresh_flag => p_refresh_flag
663: );
664: hr_utility.set_location(l_proc,150);
665: End if;
666: --
667: -- delete all the extra info records which are not required for the new noa code
668: --
669: for rei_rec in c_rei_rec loop
670: l_rei_rec.pa_request_extra_info_id := rei_rec.pa_request_extra_info_id ;
671: l_rei_rec.object_version_number := rei_rec.object_version_number ;
672: l_flag := 'D';
673: hr_utility.set_location(l_proc,160);
674: generic_populate_extra_info
675: (p_rei_rec => l_rei_rec,
676: p_org_rec => l_org_rec,
677: p_flag => l_flag
678: );
679: end loop;
680:
681: --
682: hr_utility.set_location('Leaving ' || l_proc,135);
683: --
684: End populate_noa_spec_extra_info;
685:
686:
960: Procedure appt_info is
961: Begin
962:
963: -- Read from history if person id is not null
964: hr_utility.set_location('Person inside appt_info is ' || to_char(l_person_id),1);
965: If l_person_id is not null then
966: hr_utility.set_location('Person inside appt_info is ' || to_char(l_person_id),1);
967: -- if the person_id is not the same as the old person_id then do the foll.
968: -- If l_person_id <> nvl(ghr_par_shd.g_old_rec.person_id,hr_api.g_number) then
962:
963: -- Read from history if person id is not null
964: hr_utility.set_location('Person inside appt_info is ' || to_char(l_person_id),1);
965: If l_person_id is not null then
966: hr_utility.set_location('Person inside appt_info is ' || to_char(l_person_id),1);
967: -- if the person_id is not the same as the old person_id then do the foll.
968: -- If l_person_id <> nvl(ghr_par_shd.g_old_rec.person_id,hr_api.g_number) then
969: -- a) Get PER_GROUP1 Information
970: l_refresh_flag := l_per_refresh_flag;
1017: p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
1018: p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1019: p_per_ei_data => l_per_ei_data
1020: );
1021: hr_utility.set_location('per_sep_retire' ,1);
1022: --Begin Bug# 12557463, commented following and added in proc ghr_scd_info
1023: /*set_ei(l_org_rec.rei_information7,l_per_ei_data.pei_information5,l_rei_rec.rei_information7,l_refresh_flag);
1024: hr_utility.set_location('per_sep_retire' || l_rei_rec.rei_information7 ,2);
1025: l_org_rec.rei_information7 := l_per_ei_data.pei_information5;
1020: );
1021: hr_utility.set_location('per_sep_retire' ,1);
1022: --Begin Bug# 12557463, commented following and added in proc ghr_scd_info
1023: /*set_ei(l_org_rec.rei_information7,l_per_ei_data.pei_information5,l_rei_rec.rei_information7,l_refresh_flag);
1024: hr_utility.set_location('per_sep_retire' || l_rei_rec.rei_information7 ,2);
1025: l_org_rec.rei_information7 := l_per_ei_data.pei_information5;
1026: */
1027: --End Bug# 12557463
1028: set_ei(l_org_rec.rei_information14,l_per_ei_data.pei_information4,l_rei_rec.rei_information14,l_refresh_flag);
1039:
1040:
1041:
1042:
1043: IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
1044: -- Get PER_LEAVE Information
1045: hr_utility.set_location('per_leave_info' ,1);
1046: ghr_history_fetch.fetch_peopleei
1047: (p_person_id => l_person_id,
1041:
1042:
1043: IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
1044: -- Get PER_LEAVE Information
1045: hr_utility.set_location('per_leave_info' ,1);
1046: ghr_history_fetch.fetch_peopleei
1047: (p_person_id => l_person_id,
1048: p_information_type => 'GHR_US_PER_LEAVE_INFO',
1049: p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1105: p_pos_ei_data => l_pos_ei_data
1106: );
1107: set_ei(l_org_rec.rei_information13,l_pos_ei_data.poei_information12,l_rei_rec.rei_information13,l_refresh_flag);
1108: l_org_rec.rei_information13 := l_pos_ei_data.poei_information12;
1109: hr_utility.set_location('posei in test ei - ' || l_pos_ei_data.poei_information12,2);
1110: l_pos_ei_data := null;
1111: --Begin Bug# 14514445
1112: hr_utility.set_location('promotion_eligibility_date Position Id in appt_info '|| l_position_id,350);
1113: ghr_history_fetch.fetch_positionei
1108: l_org_rec.rei_information13 := l_pos_ei_data.poei_information12;
1109: hr_utility.set_location('posei in test ei - ' || l_pos_ei_data.poei_information12,2);
1110: l_pos_ei_data := null;
1111: --Begin Bug# 14514445
1112: hr_utility.set_location('promotion_eligibility_date Position Id in appt_info '|| l_position_id,350);
1113: ghr_history_fetch.fetch_positionei
1114: (p_position_id => l_position_id,
1115: p_information_type => 'GHR_US_POS_VALID_GRADE',
1116: p_date_effective => trunc(nvl(p_effective_date,sysdate)),
1123: FOR grade_name in grade_id(l_pos_ei_data.poei_information4) LOOP
1124: l_target_grade_name := grade_name.name;
1125: exit;
1126: END LOOP;
1127: hr_utility.set_location('promotion_eligibility_date Valid Grade in appt_info '|| l_valid_grade_name,351);
1128: hr_utility.set_location('promotion_eligibility_date Target Grade in appt_info '|| l_target_grade_name,352);
1129:
1130: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
1131: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
1124: l_target_grade_name := grade_name.name;
1125: exit;
1126: END LOOP;
1127: hr_utility.set_location('promotion_eligibility_date Valid Grade in appt_info '|| l_valid_grade_name,351);
1128: hr_utility.set_location('promotion_eligibility_date Target Grade in appt_info '|| l_target_grade_name,352);
1129:
1130: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
1131: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
1132: hr_utility.set_location('promotion_eligibility_date default in appt_info '|| l_valid_grade_name,353);
1128: hr_utility.set_location('promotion_eligibility_date Target Grade in appt_info '|| l_target_grade_name,352);
1129:
1130: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
1131: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
1132: hr_utility.set_location('promotion_eligibility_date default in appt_info '|| l_valid_grade_name,353);
1133: set_ei(l_org_rec.rei_information28,fnd_date.date_to_canonical(p_effective_date+365),l_rei_rec.rei_information28,l_refresh_flag);
1134: END IF;
1135: l_pos_ei_data := null;
1136: --End Bug# 14514445
1134: END IF;
1135: l_pos_ei_data := null;
1136: --End Bug# 14514445
1137: End if;
1138: hr_utility.set_location('Appt_info Family Code: '||l_noa_family_code,10);
1139: IF NVL(l_noa_family_code,'C') <> 'CORRECT' THEN
1140: OPEN c_noac_1xx;
1141: FETCH c_noac_1xx INTO l_dlei_date;
1142: IF l_dlei_date is not null THEN
1510: -- Check if FEHB exists
1511: l_new_element_name := pqp_fedhr_uspay_int_utils.return_new_element_name('Health Benefits',l_bus_group_id,l_effective_date,null);
1512: FOR l_element_entry_cur IN c_element_entry(p_assignment_id , l_new_element_name, l_effective_date) LOOP
1513: l_fehb_exists := TRUE;
1514: hr_utility.set_location('Health benefits present',111);
1515: EXIT;
1516: END LOOP;
1517: -- Check if FEHB Pre tax exists
1518: IF l_fehb_exists = FALSE THEN
1518: IF l_fehb_exists = FALSE THEN
1519: 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);
1520: FOR l_element_entry_cur IN c_element_entry(p_assignment_id , l_new_element_name, l_effective_date) LOOP
1521: l_fehb_exists := TRUE;
1522: hr_utility.set_location('Health benefits pre tax present',111);
1523: EXIT;
1524: END LOOP;
1525: END IF;
1526:
1542: l_prev_fegli:= l_cur_element.screen_entry_value;
1543: END IF;
1544: END LOOP;
1545:
1546: hr_utility.set_location('Option' || l_enrollment_option,111);
1547: hr_utility.set_location('Health Plan' || l_health_plan,111);
1548: hr_utility.set_location('Prev. FEGLI ' || l_prev_fegli,111);
1549: hr_utility.set_location('l_person_id ' || l_person_id,111);
1550: hr_utility.set_location('p_effective_date ' || p_effective_date,111);
1543: END IF;
1544: END LOOP;
1545:
1546: hr_utility.set_location('Option' || l_enrollment_option,111);
1547: hr_utility.set_location('Health Plan' || l_health_plan,111);
1548: hr_utility.set_location('Prev. FEGLI ' || l_prev_fegli,111);
1549: hr_utility.set_location('l_person_id ' || l_person_id,111);
1550: hr_utility.set_location('p_effective_date ' || p_effective_date,111);
1551:
1544: END LOOP;
1545:
1546: hr_utility.set_location('Option' || l_enrollment_option,111);
1547: hr_utility.set_location('Health Plan' || l_health_plan,111);
1548: hr_utility.set_location('Prev. FEGLI ' || l_prev_fegli,111);
1549: hr_utility.set_location('l_person_id ' || l_person_id,111);
1550: hr_utility.set_location('p_effective_date ' || p_effective_date,111);
1551:
1552: ghr_history_fetch.fetch_peopleei
1545:
1546: hr_utility.set_location('Option' || l_enrollment_option,111);
1547: hr_utility.set_location('Health Plan' || l_health_plan,111);
1548: hr_utility.set_location('Prev. FEGLI ' || l_prev_fegli,111);
1549: hr_utility.set_location('l_person_id ' || l_person_id,111);
1550: hr_utility.set_location('p_effective_date ' || p_effective_date,111);
1551:
1552: ghr_history_fetch.fetch_peopleei
1553: (p_person_id => l_person_id,
1546: hr_utility.set_location('Option' || l_enrollment_option,111);
1547: hr_utility.set_location('Health Plan' || l_health_plan,111);
1548: hr_utility.set_location('Prev. FEGLI ' || l_prev_fegli,111);
1549: hr_utility.set_location('l_person_id ' || l_person_id,111);
1550: hr_utility.set_location('p_effective_date ' || p_effective_date,111);
1551:
1552: ghr_history_fetch.fetch_peopleei
1553: (p_person_id => l_person_id,
1554: p_information_type => 'GHR_US_PER_BENEFIT_INFO',
1558:
1559: l_date_temp_elig := fnd_date.canonical_to_date(l_per_ei_data.pei_information5);
1560: l_agency_contrib_date := fnd_date.canonical_to_date(l_per_ei_data.pei_information14);
1561:
1562: hr_utility.set_location('l_date_temp_elig' || l_per_ei_data.pei_information5,111);
1563: --8793163 For Dual Actions Benefits need to defaulted even if second action is conversion to appointment
1564: IF l_family_code = 'CONV_APP' OR nvl(l_second_noa_family_code,hr_api.g_varchar2) = 'CONV_APP' THEN
1565: IF l_fehb_exists = FALSE OR (l_fehb_exists = TRUE AND NVL(l_enrollment_option,'Z') = 'Z') THEN
1566: -- FEHB Eligibility expires
1567:
1568: --8793163 Added for second noa code
1569: IF l_noa_code IN ('500', '501', '507', '508', '520', '524', '540', '541', '542', '543', '546', '548', '570', '590') OR
1570: l_second_noa_code IN ('500', '501', '507', '508', '520', '524', '540', '541', '542', '543', '546', '548', '570', '590') THEN
1571: hr_utility.set_location('inside 1st',121);
1572: set_ei(l_org_rec.rei_information3, fnd_date.date_to_canonical(nvl(p_effective_date,sysdate)+60),l_rei_rec.rei_information3,'Y');
1573: END IF;
1574:
1575: --8793163 Added for second noa code
1612: l_new_element_name := NULL;
1613: l_new_element_name := pqp_fedhr_uspay_int_utils.return_new_element_name('TSP',l_bus_group_id,l_effective_date,null);
1614: FOR l_element_entry_cur IN c_element_entry(p_assignment_id , l_new_element_name, l_effective_date) LOOP
1615: l_tsp_exists := TRUE;
1616: hr_utility.set_location('TSP present',111);
1617: EXIT;
1618: END LOOP;
1619:
1620: IF l_tsp_exists = TRUE THEN
1650: END IF; -- IF nvl(l_retirement_plan,hr_api.
1651: END IF; -- IF l_effective_date > to_date('31/07/2010','DD/MM/RRRR')
1652:
1653: -- TSP Status
1654: hr_utility.set_location('TSP status' || l_tsp_status,111);
1655: --Bug# 15941036 added Retirement codes DR,KR,LR,MR,NR,PR effective from 01-Jan-2013
1656: IF l_effective_date >= fnd_date.canonical_to_date('2013/01/01') THEN
1657: IF nvl(l_retirement_plan,hr_api.g_varchar2) IN ('D','K','L','M','N','P','1','3','6','C','E','F','G','R','T','H','W',
1658: 'DR','KR','LR','MR','NR','PR') AND l_tsp_status IS NULL THEN
1715: END IF; -- IF NVL(l_retirement_plan,hr_a
1716:
1717: ELSIF l_family_code = 'EXT_NTE' THEN
1718: IF l_noa_code IN ('760','762') THEN
1719: hr_utility.set_location('l_enrollment_option ' || l_enrollment_option,121);
1720: hr_utility.set_location('l_date_temp_elig ' || to_char(l_date_temp_elig,'dd/mm/yyyy'),121);
1721: IF l_fehb_exists = TRUE AND NVL(l_enrollment_option,'Z') = 'Z' THEN
1722: -- FEHB Eligibility date,Enrollment
1723: --bug# 12761010 moved rei_information3 to below
1716:
1717: ELSIF l_family_code = 'EXT_NTE' THEN
1718: IF l_noa_code IN ('760','762') THEN
1719: hr_utility.set_location('l_enrollment_option ' || l_enrollment_option,121);
1720: hr_utility.set_location('l_date_temp_elig ' || to_char(l_date_temp_elig,'dd/mm/yyyy'),121);
1721: IF l_fehb_exists = TRUE AND NVL(l_enrollment_option,'Z') = 'Z' THEN
1722: -- FEHB Eligibility date,Enrollment
1723: --bug# 12761010 moved rei_information3 to below
1724: IF l_date_temp_elig <= l_effective_date THEN
1744: Procedure mddds_pay is
1745: Begin
1746:
1747: -- Read from history if person id is not null
1748: hr_utility.set_location('Person inside mddds_pay is ' || to_char(l_person_id),1);
1749: If l_assignment_id is not null then
1750: hr_utility.set_location('Person inside mddds_pay is ' || to_char(l_person_id),1);
1751:
1752: l_refresh_flag := l_per_refresh_flag;
1746:
1747: -- Read from history if person id is not null
1748: hr_utility.set_location('Person inside mddds_pay is ' || to_char(l_person_id),1);
1749: If l_assignment_id is not null then
1750: hr_utility.set_location('Person inside mddds_pay is ' || to_char(l_person_id),1);
1751:
1752: l_refresh_flag := l_per_refresh_flag;
1753:
1754: ghr_api.retrieve_element_entry_value
1875:
1876: BEGIN
1877:
1878: If l_assignment_id is not null then
1879: hr_utility.set_location('Person inside premium_pay_ind is ' || to_char(l_person_id),1);
1880: l_refresh_flag := l_per_refresh_flag;
1881:
1882: ghr_api.retrieve_element_entry_value
1883: (p_element_name => 'Premium Pay',
1895:
1896: Procedure appt_transfer is -- set / reset l_update_rei
1897: Begin
1898: -- Read from history if person id is not null
1899: hr_utility.set_location('appt_transfer - person id ' || l_person_id,1);
1900: --Bug 3128526. Changed p_person_id -> l_person_id
1901: If l_person_id is not null then
1902: l_refresh_flag := l_per_refresh_flag;
1903: ghr_history_fetch.fetch_peopleei
1933:
1934: set_ei(l_org_rec.rei_information18,l_per_ei_data.pei_information5,l_rei_rec.rei_information18,l_refresh_flag);
1935: l_org_rec.rei_information18 := l_per_ei_data.pei_information5;
1936:
1937: hr_utility.set_location('RINO ' || l_rei_rec.rei_information17,1);
1938: hr_utility.set_location('RINO _CORE ' || l_per_ei_data.pei_information5,2);
1939:
1940: set_ei(l_org_rec.rei_information19,l_per_ei_data.pei_information4,l_rei_rec.rei_information19,l_refresh_flag);
1941: l_org_rec.rei_information19 := l_per_ei_data.pei_information4;
1934: set_ei(l_org_rec.rei_information18,l_per_ei_data.pei_information5,l_rei_rec.rei_information18,l_refresh_flag);
1935: l_org_rec.rei_information18 := l_per_ei_data.pei_information5;
1936:
1937: hr_utility.set_location('RINO ' || l_rei_rec.rei_information17,1);
1938: hr_utility.set_location('RINO _CORE ' || l_per_ei_data.pei_information5,2);
1939:
1940: set_ei(l_org_rec.rei_information19,l_per_ei_data.pei_information4,l_rei_rec.rei_information19,l_refresh_flag);
1941: l_org_rec.rei_information19 := l_per_ei_data.pei_information4;
1942: --Begin Bug# 12557463, commented following and added in proc ghr_scd_info
1958: p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
1959: p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1960: p_per_ei_data => l_per_ei_data
1961: );
1962: hr_utility.set_location('original ' || l_org_rec.rei_information3,1);
1963: hr_utility.set_location(' person' || l_per_ei_data.pei_information7,1);
1964: hr_utility.set_location('rei ' || l_rei_rec.rei_information3,1);
1965:
1966: -- This seems wrong ???? set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information7,l_rei_rec.rei_information3,l_refresh_flag);
1959: p_date_effective => nvl(p_effective_date,trunc(sysdate)),
1960: p_per_ei_data => l_per_ei_data
1961: );
1962: hr_utility.set_location('original ' || l_org_rec.rei_information3,1);
1963: hr_utility.set_location(' person' || l_per_ei_data.pei_information7,1);
1964: hr_utility.set_location('rei ' || l_rei_rec.rei_information3,1);
1965:
1966: -- This seems wrong ???? set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information7,l_rei_rec.rei_information3,l_refresh_flag);
1967: -- l_org_rec.rei_information3 := l_per_ei_data.pei_information7;
1960: p_per_ei_data => l_per_ei_data
1961: );
1962: hr_utility.set_location('original ' || l_org_rec.rei_information3,1);
1963: hr_utility.set_location(' person' || l_per_ei_data.pei_information7,1);
1964: hr_utility.set_location('rei ' || l_rei_rec.rei_information3,1);
1965:
1966: -- This seems wrong ???? set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information7,l_rei_rec.rei_information3,l_refresh_flag);
1967: -- l_org_rec.rei_information3 := l_per_ei_data.pei_information7;
1968:
1965:
1966: -- This seems wrong ???? set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information7,l_rei_rec.rei_information3,l_refresh_flag);
1967: -- l_org_rec.rei_information3 := l_per_ei_data.pei_information7;
1968:
1969: hr_utility.set_location('original ' || l_org_rec.rei_information3,1);
1970: hr_utility.set_location(' person' || l_per_ei_data.pei_information7,1);
1971: hr_utility.set_location('rei ' || l_rei_rec.rei_information3,1);
1972: --Begin Bug# 12557463, commented following and added in proc ghr_scd_info
1973: /*set_ei(l_org_rec.rei_information9,l_per_ei_data.pei_information5,l_rei_rec.rei_information9,l_refresh_flag);
1966: -- This seems wrong ???? set_ei(l_org_rec.rei_information3,l_per_ei_data.pei_information7,l_rei_rec.rei_information3,l_refresh_flag);
1967: -- l_org_rec.rei_information3 := l_per_ei_data.pei_information7;
1968:
1969: hr_utility.set_location('original ' || l_org_rec.rei_information3,1);
1970: hr_utility.set_location(' person' || l_per_ei_data.pei_information7,1);
1971: hr_utility.set_location('rei ' || l_rei_rec.rei_information3,1);
1972: --Begin Bug# 12557463, commented following and added in proc ghr_scd_info
1973: /*set_ei(l_org_rec.rei_information9,l_per_ei_data.pei_information5,l_rei_rec.rei_information9,l_refresh_flag);
1974: l_org_rec.rei_information9 := l_per_ei_data.pei_information5;*/
1967: -- l_org_rec.rei_information3 := l_per_ei_data.pei_information7;
1968:
1969: hr_utility.set_location('original ' || l_org_rec.rei_information3,1);
1970: hr_utility.set_location(' person' || l_per_ei_data.pei_information7,1);
1971: hr_utility.set_location('rei ' || l_rei_rec.rei_information3,1);
1972: --Begin Bug# 12557463, commented following and added in proc ghr_scd_info
1973: /*set_ei(l_org_rec.rei_information9,l_per_ei_data.pei_information5,l_rei_rec.rei_information9,l_refresh_flag);
1974: l_org_rec.rei_information9 := l_per_ei_data.pei_information5;*/
1975: --End Bug# 12557463
1994:
1995: l_per_ei_data := null;*/
1996: --End Bug# 12557463
1997:
1998: IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
1999: -- Get PER_LEAVE Information
2000: hr_utility.set_location('per_leave_info' ,1);
2001: ghr_history_fetch.fetch_peopleei
2002: (p_person_id => l_person_id,
1996: --End Bug# 12557463
1997:
1998: IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
1999: -- Get PER_LEAVE Information
2000: hr_utility.set_location('per_leave_info' ,1);
2001: ghr_history_fetch.fetch_peopleei
2002: (p_person_id => l_person_id,
2003: p_information_type => 'GHR_US_PER_LEAVE_INFO',
2004: p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2012: --END IF;
2013: l_per_ei_data := null;
2014: --Gain Or Lose
2015: set_ei(l_org_rec.rei_information23,'1B',l_rei_rec.rei_information23,l_refresh_flag);
2016: END IF; -- IF ghr_utility.is_ghr_nfc = 'TRUE'
2017: End if;
2018: If l_assignment_id is not null then
2019: l_refresh_flag := l_asg_refresh_flag;
2020: ghr_history_fetch.fetch_asgei
2073: l_org_rec.rei_information15 := l_pos_ei_data.poei_information12;
2074: End if;
2075: l_pos_ei_data := null;
2076: --Begin Bug# 14514445
2077: hr_utility.set_location('promotion_eligibility_date Position Id in appt_transfer '|| l_position_id,350);
2078: ghr_history_fetch.fetch_positionei
2079: (p_position_id => l_position_id,
2080: p_information_type => 'GHR_US_POS_VALID_GRADE',
2081: p_date_effective => trunc(nvl(p_effective_date,sysdate)),
2088: FOR grade_name in grade_id(l_pos_ei_data.poei_information4) LOOP
2089: l_target_grade_name := grade_name.name;
2090: exit;
2091: END LOOP;
2092: hr_utility.set_location('promotion_eligibility_date Valid Grade in appt_transfer '|| l_valid_grade_name,351);
2093: hr_utility.set_location('promotion_eligibility_date Target Grade in appt_transfer '|| l_target_grade_name,352);
2094:
2095: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
2096: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
2089: l_target_grade_name := grade_name.name;
2090: exit;
2091: END LOOP;
2092: hr_utility.set_location('promotion_eligibility_date Valid Grade in appt_transfer '|| l_valid_grade_name,351);
2093: hr_utility.set_location('promotion_eligibility_date Target Grade in appt_transfer '|| l_target_grade_name,352);
2094:
2095: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
2096: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
2097: hr_utility.set_location('promotion_eligibility_date default '|| l_valid_grade_name,353);
2093: hr_utility.set_location('promotion_eligibility_date Target Grade in appt_transfer '|| l_target_grade_name,352);
2094:
2095: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
2096: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
2097: hr_utility.set_location('promotion_eligibility_date default '|| l_valid_grade_name,353);
2098: set_ei(l_org_rec.rei_information27,fnd_date.date_to_canonical(p_effective_date+365),l_rei_rec.rei_information27,l_refresh_flag);
2099: END IF;
2100: l_pos_ei_data := null;
2101: --End Bug# 14514445
2099: END IF;
2100: l_pos_ei_data := null;
2101: --End Bug# 14514445
2102: End if;
2103: hr_utility.set_location('Appt_transfer Family Code: '||l_noa_family_code,10);
2104: IF NVL(l_noa_family_code,'C') <> 'CORRECT' THEN
2105: OPEN c_noac_130;
2106: FETCH c_noac_130 INTO l_dlei_date;
2107: IF l_dlei_date is not null THEN
2215: -- set_ei(l_org_rec.rei_information18,l_per_ei_data.pei_information6,l_rei_rec.rei_information18,l_refresh_flag);
2216: -- l_org_rec.rei_information18 := l_per_ei_data.pei_information6;
2217: l_per_ei_data := null;
2218: -- Get PER_LEAVE Information
2219: hr_utility.set_location('per_leave_info' ,1);
2220: IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
2221: ghr_history_fetch.fetch_peopleei
2222: (p_person_id => l_person_id,
2223: p_information_type => 'GHR_US_PER_LEAVE_INFO',
2216: -- l_org_rec.rei_information18 := l_per_ei_data.pei_information6;
2217: l_per_ei_data := null;
2218: -- Get PER_LEAVE Information
2219: hr_utility.set_location('per_leave_info' ,1);
2220: IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
2221: ghr_history_fetch.fetch_peopleei
2222: (p_person_id => l_person_id,
2223: p_information_type => 'GHR_US_PER_LEAVE_INFO',
2224: p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2230: --Bug # 13581524 modified to default 'N' in set ei itself
2231: set_ei(l_org_rec.rei_information22,NVL(l_per_ei_data.pei_information4,'N'),l_rei_rec.rei_information22,l_refresh_flag);
2232: --END IF;
2233: l_per_ei_data := null;
2234: END IF; -- IF ghr_utility.is_ghr_nfc = 'TR
2235: End if;
2236: If NVL(l_assignment_id,p_assignment_id) is not null then
2237: l_refresh_flag := l_asg_refresh_flag;
2238: ghr_history_fetch.fetch_asgei
2274: );
2275: set_ei(l_org_rec.rei_information9,l_pos_ei_data.poei_information12,l_rei_rec.rei_information9,l_refresh_flag);
2276: l_pos_ei_data := null;
2277: --Begin Bug# 14514445
2278: hr_utility.set_location('promotion_eligibility_date Position Id in conv_appt '|| l_position_id,350);
2279: ghr_history_fetch.fetch_positionei
2280: (p_position_id => l_position_id,
2281: p_information_type => 'GHR_US_POS_VALID_GRADE',
2282: p_date_effective => trunc(nvl(p_effective_date,sysdate)),
2289: FOR grade_name in grade_id(l_pos_ei_data.poei_information4) LOOP
2290: l_target_grade_name := grade_name.name;
2291: exit;
2292: END LOOP;
2293: hr_utility.set_location('promotion_eligibility_date Valid Grade in conv_appt '|| l_valid_grade_name,351);
2294: hr_utility.set_location('promotion_eligibility_date Target Grade in conv_appt '|| l_target_grade_name,352);
2295:
2296: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
2297: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
2290: l_target_grade_name := grade_name.name;
2291: exit;
2292: END LOOP;
2293: hr_utility.set_location('promotion_eligibility_date Valid Grade in conv_appt '|| l_valid_grade_name,351);
2294: hr_utility.set_location('promotion_eligibility_date Target Grade in conv_appt '|| l_target_grade_name,352);
2295:
2296: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
2297: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
2298: hr_utility.set_location('promotion_eligibility_date default in conv_appt '|| l_valid_grade_name,353);
2294: hr_utility.set_location('promotion_eligibility_date Target Grade in conv_appt '|| l_target_grade_name,352);
2295:
2296: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
2297: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
2298: hr_utility.set_location('promotion_eligibility_date default in conv_appt '|| l_valid_grade_name,353);
2299: set_ei(l_org_rec.rei_information26,fnd_date.date_to_canonical(p_effective_date+365),l_rei_rec.rei_information26,l_refresh_flag);
2300: END IF;
2301: l_pos_ei_data := null;
2302: --End Bug# 14514445
2367: exit;
2368: END LOOP;
2369: END IF;
2370: --Bug#4126188 End
2371: hr_utility.set_location('conv_Appt Family Code: '||l_noa_family_code,10);
2372: IF NVL(l_noa_family_code,'C') <> 'CORRECT' THEN
2373: OPEN c_noac_5xx;
2374: FETCH c_noac_5xx INTO l_dlei_date;
2375: IF l_dlei_date is not null THEN
2497:
2498: l_pos_ei_data := null;
2499: */
2500: --Begin Bug# 14514445
2501: hr_utility.set_location('promotion_eligibility_date Position Id in reassignment '|| l_position_id,350);
2502: ghr_history_fetch.fetch_positionei
2503: (p_position_id => l_position_id,
2504: p_information_type => 'GHR_US_POS_VALID_GRADE',
2505: p_date_effective => trunc(nvl(p_effective_date,sysdate)),
2512: FOR grade_name in grade_id(l_pos_ei_data.poei_information4) LOOP
2513: l_target_grade_name := grade_name.name;
2514: exit;
2515: END LOOP;
2516: hr_utility.set_location('promotion_eligibility_date Valid Grade in reassignment '|| l_valid_grade_name,351);
2517: hr_utility.set_location('promotion_eligibility_date Target Grade in reassignment '|| l_target_grade_name,352);
2518:
2519: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
2520: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
2513: l_target_grade_name := grade_name.name;
2514: exit;
2515: END LOOP;
2516: hr_utility.set_location('promotion_eligibility_date Valid Grade in reassignment '|| l_valid_grade_name,351);
2517: hr_utility.set_location('promotion_eligibility_date Target Grade in reassignment '|| l_target_grade_name,352);
2518:
2519: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
2520: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
2521: hr_utility.set_location('promotion_eligibility_date default in reassignment '|| l_valid_grade_name,353);
2517: hr_utility.set_location('promotion_eligibility_date Target Grade in reassignment '|| l_target_grade_name,352);
2518:
2519: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
2520: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
2521: hr_utility.set_location('promotion_eligibility_date default in reassignment '|| l_valid_grade_name,353);
2522: ghr_history_fetch.fetch_peopleei
2523: (p_person_id => l_person_id,
2524: p_information_type => 'GHR_US_PER_GROUP1',
2525: p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2600: Procedure realign is
2601: Begin
2602: -- Bug#3593584 The following variable is used in procedure realign
2603: -- to populate the Extra info only when noa_family_code = 'CORRECT'
2604: hr_utility.set_location('l_noa_family_code '|| l_noa_family_code,10);
2605: -- Bug#4089400 Moved the cursor to the begining of the main procedure.
2606: IF NVL(l_noa_family_code,'C') = 'CORRECT' THEN
2607: If l_assignment_id is not null then
2608: l_refresh_flag := l_asg_refresh_flag;
2772: BEGIN
2773: IF l_person_id IS NOT NULL THEN
2774: l_refresh_flag := l_per_refresh_flag;
2775:
2776: hr_utility.set_location ('l_refresh_flag in prv_ret_info is '
2777: || l_refresh_flag, 4);
2778:
2779: ghr_history_fetch.fetch_peopleei
2780: (p_person_id => l_person_id
2797: begin
2798:
2799: If l_person_id is not null then
2800: l_refresh_flag := l_per_refresh_flag;
2801: hr_utility.set_location('l_refresh_flag is ' || l_refresh_flag,1);
2802: ghr_history_fetch.fetch_peopleei
2803: (p_person_id => l_person_id,
2804: p_information_type => 'GHR_US_PER_SCD_INFORMATION',
2805: p_date_effective => nvl(p_effective_date,trunc(sysdate)),
2818: begin
2819:
2820: If l_person_id is not null then
2821: l_refresh_flag := l_per_refresh_flag;
2822: hr_utility.set_location('l_refresh_flag is ' || l_refresh_flag,4);
2823: IF NVL(l_noa_family_code,'C') = 'CORRECT' THEN
2824: ghr_history_fetch.fetch_peopleei
2825: (p_person_id => l_person_id,
2826: p_information_type => 'GHR_US_PER_GROUP1',
2834: End if;
2835:
2836:
2837: --Bug 12652438
2838: hr_utility.set_location('l_noa_family_code'||l_noa_family_code,1000);
2839: IF NVL(l_noa_family_code,'C') <> 'CORRECT' THEN
2840:
2841: IF l_first_noa_code = '430' THEN
2842: set_ei(l_org_rec.rei_information15,'O',l_rei_rec.rei_information15,'Y');
2850: set_ei(l_org_rec.rei_information15,'P',l_rei_rec.rei_information15,'Y');
2851: END IF;
2852:
2853: ELSE
2854: hr_utility.set_location('l_rei_rec.rei_information15 '||l_rei_rec.rei_information15 ,1000);
2855: hr_utility.set_location('l_first_noa_code '||l_first_noa_code ,1000);
2856: IF l_rei_rec.rei_information15 IS NULL THEN
2857: IF l_second_noa_code = '430' THEN
2858: l_rei_rec.rei_information15 := 'O';
2851: END IF;
2852:
2853: ELSE
2854: hr_utility.set_location('l_rei_rec.rei_information15 '||l_rei_rec.rei_information15 ,1000);
2855: hr_utility.set_location('l_first_noa_code '||l_first_noa_code ,1000);
2856: IF l_rei_rec.rei_information15 IS NULL THEN
2857: IF l_second_noa_code = '430' THEN
2858: l_rei_rec.rei_information15 := 'O';
2859:
2858: l_rei_rec.rei_information15 := 'O';
2859:
2860: ELSIF l_second_noa_code = '450' THEN
2861: l_rei_rec.rei_information15 := 'S';
2862: hr_utility.set_location('l_rei_rec.rei_information15 '||l_rei_rec.rei_information15 ,1000);
2863: ELSIF l_second_noa_code = '452' THEN
2864: l_rei_rec.rei_information15 := 'T';
2865: ELSIF l_second_noa_code = '460' THEN
2866: l_rei_rec.rei_information15 := 'L';
2881: begin
2882:
2883: If l_person_id is not null then
2884: l_refresh_flag := l_per_refresh_flag;
2885: hr_utility.set_location('l_refresh_flag is ' || l_refresh_flag,5);
2886: IF NVL(l_noa_family_code,'C') = 'CORRECT' THEN
2887: ghr_history_fetch.fetch_peopleei
2888: (p_person_id => l_person_id,
2889: p_information_type => 'GHR_US_PER_GROUP1',
2948: -- populate the date award earned field with the effective_date
2949: --
2950: IF NVL(l_noa_family_code,'C') <> 'CORRECT' THEN -- Bug 2836175
2951: l_refresh_flag := l_per_refresh_flag;
2952: hr_utility.set_location('eff. date is ' || fnd_date.date_to_canonical(p_effective_date),1);
2953:
2954: --set_ei(l_org_rec.rei_information9, fnd_date.date_to_canonical(p_effective_date), l_rei_rec.rei_information9, l_refresh_flag);
2955: --Bug 2833942
2956: 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);
2954: --set_ei(l_org_rec.rei_information9, fnd_date.date_to_canonical(p_effective_date), l_rei_rec.rei_information9, l_refresh_flag);
2955: --Bug 2833942
2956: 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);
2957:
2958: hr_utility.set_location('Eff date ' || p_effective_date,1);
2959: hr_utility.set_location('Info9 for Gov Awards ' || l_org_rec.rei_information9,1);
2960: -- Start Bug 1379280
2961: FOR c_noa_code1 in c_noa_code LOOP
2962: IF p_position_id is NOT NULL then
2955: --Bug 2833942
2956: 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);
2957:
2958: hr_utility.set_location('Eff date ' || p_effective_date,1);
2959: hr_utility.set_location('Info9 for Gov Awards ' || l_org_rec.rei_information9,1);
2960: -- Start Bug 1379280
2961: FOR c_noa_code1 in c_noa_code LOOP
2962: IF p_position_id is NOT NULL then
2963: FOR c_bg_id in c_bg1 LOOP
2990: l_serv_oblig_stdt DATE;
2991: l_serv_oblig_enddt DATE;
2992: BEGIN
2993: --
2994: hr_utility.set_location('Entering Service Obligation. l_person_id '||l_person_id,0);
2995: hr_utility.set_location('shadow type code '||l_org_rec.rei_information3,1);
2996: hr_utility.set_location('shadow start date '||l_org_rec.rei_information5,1);
2997: hr_utility.set_location('shadow end date '||l_org_rec.rei_information4,1);
2998: hr_utility.set_location('RPAEIT type code '||l_rei_rec.rei_information3,1);
2991: l_serv_oblig_enddt DATE;
2992: BEGIN
2993: --
2994: hr_utility.set_location('Entering Service Obligation. l_person_id '||l_person_id,0);
2995: hr_utility.set_location('shadow type code '||l_org_rec.rei_information3,1);
2996: hr_utility.set_location('shadow start date '||l_org_rec.rei_information5,1);
2997: hr_utility.set_location('shadow end date '||l_org_rec.rei_information4,1);
2998: hr_utility.set_location('RPAEIT type code '||l_rei_rec.rei_information3,1);
2999: hr_utility.set_location('RPAEIT start date '||l_rei_rec.rei_information5,1);
2992: BEGIN
2993: --
2994: hr_utility.set_location('Entering Service Obligation. l_person_id '||l_person_id,0);
2995: hr_utility.set_location('shadow type code '||l_org_rec.rei_information3,1);
2996: hr_utility.set_location('shadow start date '||l_org_rec.rei_information5,1);
2997: hr_utility.set_location('shadow end date '||l_org_rec.rei_information4,1);
2998: hr_utility.set_location('RPAEIT type code '||l_rei_rec.rei_information3,1);
2999: hr_utility.set_location('RPAEIT start date '||l_rei_rec.rei_information5,1);
3000: hr_utility.set_location('RPAEIT end date '||l_rei_rec.rei_information4,1);
2993: --
2994: hr_utility.set_location('Entering Service Obligation. l_person_id '||l_person_id,0);
2995: hr_utility.set_location('shadow type code '||l_org_rec.rei_information3,1);
2996: hr_utility.set_location('shadow start date '||l_org_rec.rei_information5,1);
2997: hr_utility.set_location('shadow end date '||l_org_rec.rei_information4,1);
2998: hr_utility.set_location('RPAEIT type code '||l_rei_rec.rei_information3,1);
2999: hr_utility.set_location('RPAEIT start date '||l_rei_rec.rei_information5,1);
3000: hr_utility.set_location('RPAEIT end date '||l_rei_rec.rei_information4,1);
3001:
2994: hr_utility.set_location('Entering Service Obligation. l_person_id '||l_person_id,0);
2995: hr_utility.set_location('shadow type code '||l_org_rec.rei_information3,1);
2996: hr_utility.set_location('shadow start date '||l_org_rec.rei_information5,1);
2997: hr_utility.set_location('shadow end date '||l_org_rec.rei_information4,1);
2998: hr_utility.set_location('RPAEIT type code '||l_rei_rec.rei_information3,1);
2999: hr_utility.set_location('RPAEIT start date '||l_rei_rec.rei_information5,1);
3000: hr_utility.set_location('RPAEIT end date '||l_rei_rec.rei_information4,1);
3001:
3002: l_serv_oblig_code := NULL;
2995: hr_utility.set_location('shadow type code '||l_org_rec.rei_information3,1);
2996: hr_utility.set_location('shadow start date '||l_org_rec.rei_information5,1);
2997: hr_utility.set_location('shadow end date '||l_org_rec.rei_information4,1);
2998: hr_utility.set_location('RPAEIT type code '||l_rei_rec.rei_information3,1);
2999: hr_utility.set_location('RPAEIT start date '||l_rei_rec.rei_information5,1);
3000: hr_utility.set_location('RPAEIT end date '||l_rei_rec.rei_information4,1);
3001:
3002: l_serv_oblig_code := NULL;
3003: l_serv_oblig_stdt := NULL;
2996: hr_utility.set_location('shadow start date '||l_org_rec.rei_information5,1);
2997: hr_utility.set_location('shadow end date '||l_org_rec.rei_information4,1);
2998: hr_utility.set_location('RPAEIT type code '||l_rei_rec.rei_information3,1);
2999: hr_utility.set_location('RPAEIT start date '||l_rei_rec.rei_information5,1);
3000: hr_utility.set_location('RPAEIT end date '||l_rei_rec.rei_information4,1);
3001:
3002: l_serv_oblig_code := NULL;
3003: l_serv_oblig_stdt := NULL;
3004: l_serv_oblig_enddt := NULL;
3121: --
3122: Procedure nfc_separation is
3123: --
3124: begin
3125: IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
3126: set_ei(l_org_rec.rei_information12,'1B',l_rei_rec.rei_information12,l_refresh_flag);
3127: END IF;
3128: end nfc_separation;
3129:
3241: Procedure key_emergency_essntl
3242: is
3243: Begin
3244:
3245: hr_utility.set_location('l_noa_family_code'||l_noa_family_code,1000);
3246: If l_noa_family_code = 'APP' then
3247: set_ei(l_org_rec.rei_information3,nvl(l_rei_rec.rei_information3,'0'),
3248: l_rei_rec.rei_information3,'Y');
3249: Else
3266: l_asg_ei_data := null;
3267: End If;
3268: Else
3269:
3270: hr_utility.set_location('l_noa_family_code'||l_noa_family_code,1000);
3271: set_ei(l_org_rec.rei_information3,nvl(l_rei_rec.rei_information3,'0'),
3272: l_rei_rec.rei_information3,'Y');
3273: End If;
3274: End Loop;
3355: --END IF;
3356: set_ei(l_org_rec.rei_information7,l_per_ei_data.pei_information7,
3357: l_rei_rec.rei_information7,l_refresh_flag);
3358:
3359: hr_utility.set_location('rei_ei_data:'||l_rei_rec.rei_information7,0);
3360: l_per_ei_data := null;
3361: End If;
3362: End ghr_conv_dates;
3363:
3420: l_second_noa_id ghr_pa_requests.second_noa_id%type;
3421: l_second_noa_family_code ghr_pa_requests.noa_family_code%type;
3422: --End Bug# 14001082
3423: Begin
3424: hr_utility.set_location('Entering ddf ghr_scd_info '|| l_noa_family_code,1100);
3425: l_per_ei_data := null;
3426: If l_person_id is not null then
3427: l_refresh_flag := l_per_refresh_flag;
3428: FOR pa_rec in c_pa_req_details LOOP
3518: l_pos_ei_data := null;
3519: END IF;
3520: end if;
3521:
3522: hr_utility.set_location('Leaving ddf ghr_scd_info '|| l_noa_family_code,1100);
3523: End ghr_scd_info;
3524: --End Bug# 12557463
3525:
3526: Procedure set_refresh_flags is
3535: (p_person_id = nvl(ghr_par_shd.g_old_rec.person_id,hr_api.g_number) and
3536: p_pa_request_id <> nvl(ghr_par_shd.g_old_rec.pa_request_id ,hr_api.g_number) and
3537: trunc(nvl(p_effective_date,sysdate)) = trunc(nvl(ghr_par_shd.g_old_rec.effective_date,sysdate)))
3538: then
3539: hr_utility.set_location('change in eff. date only',2);
3540: l_person_id := p_person_id;
3541: l_per_refresh_flag := 'Y';
3542: Elsif p_person_id <> nvl(ghr_par_shd.g_old_rec.person_id,hr_api.g_number) then
3543: hr_utility.set_location('person id has changed',3);
3539: hr_utility.set_location('change in eff. date only',2);
3540: l_person_id := p_person_id;
3541: l_per_refresh_flag := 'Y';
3542: Elsif p_person_id <> nvl(ghr_par_shd.g_old_rec.person_id,hr_api.g_number) then
3543: hr_utility.set_location('person id has changed',3);
3544: l_person_id := p_person_id;
3545: l_per_refresh_flag := 'N';
3546: Else
3547: l_person_id := null;
3545: l_per_refresh_flag := 'N';
3546: Else
3547: l_person_id := null;
3548: End if;
3549: hr_utility.set_location('person id in the condition - refresh flag N ' || to_char(l_person_id),1);
3550: End if;
3551: If p_assignment_id is not null then
3552: if (p_assignment_id = nvl(ghr_par_shd.g_old_rec.employee_assignment_id,hr_api.g_number) and
3553: (l_rei_rec_exists = 'N' or
3726: l_sec_la_code1 := c_noa_fam_code_rec.sec_la_code1;
3727: l_sec_la_code2 := c_noa_fam_code_rec.sec_la_code2;
3728: l_payment_option := c_noa_fam_code_rec.payment_option;
3729: END LOOP;
3730: hr_utility.set_location('l_noa_family_code '|| l_noa_family_code,10);
3731:
3732: l_resp_id := fnd_profile.value('RESP_ID');
3733: l_application_id := fnd_profile.value('RESP_APPL_ID');
3734: hr_utility.set_location('Resp id '|| l_resp_id,10);
3730: hr_utility.set_location('l_noa_family_code '|| l_noa_family_code,10);
3731:
3732: l_resp_id := fnd_profile.value('RESP_ID');
3733: l_application_id := fnd_profile.value('RESP_APPL_ID');
3734: hr_utility.set_location('Resp id '|| l_resp_id,10);
3735: hr_utility.set_location('appl id '|| l_application_id,10);
3736: for c_info_type_rec in c_info_types(l_application_id,l_resp_id) loop
3737: l_information_type := c_info_type_rec.information_type;
3738: l_rei_rec := NULL;
3731:
3732: l_resp_id := fnd_profile.value('RESP_ID');
3733: l_application_id := fnd_profile.value('RESP_APPL_ID');
3734: hr_utility.set_location('Resp id '|| l_resp_id,10);
3735: hr_utility.set_location('appl id '|| l_application_id,10);
3736: for c_info_type_rec in c_info_types(l_application_id,l_resp_id) loop
3737: l_information_type := c_info_type_rec.information_type;
3738: l_rei_rec := NULL;
3739: l_org_rec := NULL; -- Bug 4691293
3813: -- call the procedure to set the refresh flags
3814: If p_refresh_flag = 'N' then
3815: set_refresh_flags;
3816: Else
3817: hr_utility.set_location('p_refresh_flag is Y',3);
3818: l_per_refresh_flag := 'Y';
3819: l_asg_refresh_flag := 'Y';
3820: l_pos_refresh_flag := 'Y';
3821:
3824: l_assignment_id := p_assignment_id;
3825: l_position_id := p_position_id;
3826: End if;
3827: --
3828: hr_utility.set_location('INFO_TYPE_IS'|| l_information_type,0);
3829: hr_utility.set_location('PER ID is'|| l_person_id,0);
3830: if l_information_type = 'GHR_US_PAR_APPT_INFO' then
3831: hr_utility.set_location(l_proc,5);
3832: hr_utility.set_location('info type ' || l_information_type,1);
3825: l_position_id := p_position_id;
3826: End if;
3827: --
3828: hr_utility.set_location('INFO_TYPE_IS'|| l_information_type,0);
3829: hr_utility.set_location('PER ID is'|| l_person_id,0);
3830: if l_information_type = 'GHR_US_PAR_APPT_INFO' then
3831: hr_utility.set_location(l_proc,5);
3832: hr_utility.set_location('info type ' || l_information_type,1);
3833: appt_info;
3827: --
3828: hr_utility.set_location('INFO_TYPE_IS'|| l_information_type,0);
3829: hr_utility.set_location('PER ID is'|| l_person_id,0);
3830: if l_information_type = 'GHR_US_PAR_APPT_INFO' then
3831: hr_utility.set_location(l_proc,5);
3832: hr_utility.set_location('info type ' || l_information_type,1);
3833: appt_info;
3834: elsif l_information_type = 'GHR_US_PAR_MD_DDS_PAY' then
3835: hr_utility.set_location(l_proc,15);
3828: hr_utility.set_location('INFO_TYPE_IS'|| l_information_type,0);
3829: hr_utility.set_location('PER ID is'|| l_person_id,0);
3830: if l_information_type = 'GHR_US_PAR_APPT_INFO' then
3831: hr_utility.set_location(l_proc,5);
3832: hr_utility.set_location('info type ' || l_information_type,1);
3833: appt_info;
3834: elsif l_information_type = 'GHR_US_PAR_MD_DDS_PAY' then
3835: hr_utility.set_location(l_proc,15);
3836: hr_utility.set_location('info type ' || l_information_type,3);
3831: hr_utility.set_location(l_proc,5);
3832: hr_utility.set_location('info type ' || l_information_type,1);
3833: appt_info;
3834: elsif l_information_type = 'GHR_US_PAR_MD_DDS_PAY' then
3835: hr_utility.set_location(l_proc,15);
3836: hr_utility.set_location('info type ' || l_information_type,3);
3837: mddds_pay;
3838: elsif l_information_type = 'GHR_US_PAR_PREMIUM_PAY_IND' then
3839: hr_utility.set_location(l_proc,15);
3832: hr_utility.set_location('info type ' || l_information_type,1);
3833: appt_info;
3834: elsif l_information_type = 'GHR_US_PAR_MD_DDS_PAY' then
3835: hr_utility.set_location(l_proc,15);
3836: hr_utility.set_location('info type ' || l_information_type,3);
3837: mddds_pay;
3838: elsif l_information_type = 'GHR_US_PAR_PREMIUM_PAY_IND' then
3839: hr_utility.set_location(l_proc,15);
3840: hr_utility.set_location('info type ' || l_information_type,3);
3835: hr_utility.set_location(l_proc,15);
3836: hr_utility.set_location('info type ' || l_information_type,3);
3837: mddds_pay;
3838: elsif l_information_type = 'GHR_US_PAR_PREMIUM_PAY_IND' then
3839: hr_utility.set_location(l_proc,15);
3840: hr_utility.set_location('info type ' || l_information_type,3);
3841: premium_pay_ind;
3842:
3843: elsif l_information_type = 'GHR_US_PAR_APPT_TRANSFER' then
3836: hr_utility.set_location('info type ' || l_information_type,3);
3837: mddds_pay;
3838: elsif l_information_type = 'GHR_US_PAR_PREMIUM_PAY_IND' then
3839: hr_utility.set_location(l_proc,15);
3840: hr_utility.set_location('info type ' || l_information_type,3);
3841: premium_pay_ind;
3842:
3843: elsif l_information_type = 'GHR_US_PAR_APPT_TRANSFER' then
3844: hr_utility.set_location(l_proc,10);
3840: hr_utility.set_location('info type ' || l_information_type,3);
3841: premium_pay_ind;
3842:
3843: elsif l_information_type = 'GHR_US_PAR_APPT_TRANSFER' then
3844: hr_utility.set_location(l_proc,10);
3845: hr_utility.set_location('info type ' || l_information_type,2);
3846: for c_req_num_rec in c_req_num loop
3847: --Bug 3128526. Added 'NVL' to handle NULL values
3848: if nvl(c_req_num_rec.request_number,hr_api.g_number) <> 'MTI'||to_char(p_pa_request_id) then
3841: premium_pay_ind;
3842:
3843: elsif l_information_type = 'GHR_US_PAR_APPT_TRANSFER' then
3844: hr_utility.set_location(l_proc,10);
3845: hr_utility.set_location('info type ' || l_information_type,2);
3846: for c_req_num_rec in c_req_num loop
3847: --Bug 3128526. Added 'NVL' to handle NULL values
3848: if nvl(c_req_num_rec.request_number,hr_api.g_number) <> 'MTI'||to_char(p_pa_request_id) then
3849: appt_transfer;
3849: appt_transfer;
3850: end if;
3851: end loop;
3852: elsif l_information_type = 'GHR_US_PAR_CONV_APP' then
3853: hr_utility.set_location(l_proc,15);
3854: hr_utility.set_location('info type ' || l_information_type,3);
3855: conv_appt;
3856: elsif l_information_type = 'GHR_US_PAR_RETURN_TO_DUTY' then
3857: hr_utility.set_location(l_proc,20);
3850: end if;
3851: end loop;
3852: elsif l_information_type = 'GHR_US_PAR_CONV_APP' then
3853: hr_utility.set_location(l_proc,15);
3854: hr_utility.set_location('info type ' || l_information_type,3);
3855: conv_appt;
3856: elsif l_information_type = 'GHR_US_PAR_RETURN_TO_DUTY' then
3857: hr_utility.set_location(l_proc,20);
3858: hr_utility.set_location('info type ' || l_information_type,4);
3853: hr_utility.set_location(l_proc,15);
3854: hr_utility.set_location('info type ' || l_information_type,3);
3855: conv_appt;
3856: elsif l_information_type = 'GHR_US_PAR_RETURN_TO_DUTY' then
3857: hr_utility.set_location(l_proc,20);
3858: hr_utility.set_location('info type ' || l_information_type,4);
3859: return_to_duty;
3860: elsif l_information_type = 'GHR_US_PAR_REASSIGNMENT' then
3861: hr_utility.set_location(l_proc,25);
3854: hr_utility.set_location('info type ' || l_information_type,3);
3855: conv_appt;
3856: elsif l_information_type = 'GHR_US_PAR_RETURN_TO_DUTY' then
3857: hr_utility.set_location(l_proc,20);
3858: hr_utility.set_location('info type ' || l_information_type,4);
3859: return_to_duty;
3860: elsif l_information_type = 'GHR_US_PAR_REASSIGNMENT' then
3861: hr_utility.set_location(l_proc,25);
3862: hr_utility.set_location('info type ' || l_information_type,4);
3857: hr_utility.set_location(l_proc,20);
3858: hr_utility.set_location('info type ' || l_information_type,4);
3859: return_to_duty;
3860: elsif l_information_type = 'GHR_US_PAR_REASSIGNMENT' then
3861: hr_utility.set_location(l_proc,25);
3862: hr_utility.set_location('info type ' || l_information_type,4);
3863: reassignment;
3864: elsif l_information_type = 'GHR_US_PAR_REALIGNMENT' then
3865: hr_utility.set_location(l_proc,30);
3858: hr_utility.set_location('info type ' || l_information_type,4);
3859: return_to_duty;
3860: elsif l_information_type = 'GHR_US_PAR_REASSIGNMENT' then
3861: hr_utility.set_location(l_proc,25);
3862: hr_utility.set_location('info type ' || l_information_type,4);
3863: reassignment;
3864: elsif l_information_type = 'GHR_US_PAR_REALIGNMENT' then
3865: hr_utility.set_location(l_proc,30);
3866: hr_utility.set_location('info type ' || l_information_type,4);
3861: hr_utility.set_location(l_proc,25);
3862: hr_utility.set_location('info type ' || l_information_type,4);
3863: reassignment;
3864: elsif l_information_type = 'GHR_US_PAR_REALIGNMENT' then
3865: hr_utility.set_location(l_proc,30);
3866: hr_utility.set_location('info type ' || l_information_type,4);
3867: realign;
3868: elsif l_information_type = 'GHR_US_PAR_CHG_DATA_ELEMENT' then
3869: hr_utility.set_location(l_proc,45);
3862: hr_utility.set_location('info type ' || l_information_type,4);
3863: reassignment;
3864: elsif l_information_type = 'GHR_US_PAR_REALIGNMENT' then
3865: hr_utility.set_location(l_proc,30);
3866: hr_utility.set_location('info type ' || l_information_type,4);
3867: realign;
3868: elsif l_information_type = 'GHR_US_PAR_CHG_DATA_ELEMENT' then
3869: hr_utility.set_location(l_proc,45);
3870: hr_utility.set_location('info type ' || l_information_type,4);
3865: hr_utility.set_location(l_proc,30);
3866: hr_utility.set_location('info type ' || l_information_type,4);
3867: realign;
3868: elsif l_information_type = 'GHR_US_PAR_CHG_DATA_ELEMENT' then
3869: hr_utility.set_location(l_proc,45);
3870: hr_utility.set_location('info type ' || l_information_type,4);
3871: chg_data_element;
3872: elsif l_information_type = 'GHR_US_PAR_CHG_RETIRE_PLAN' then
3873: hr_utility.set_location(l_proc,50);
3866: hr_utility.set_location('info type ' || l_information_type,4);
3867: realign;
3868: elsif l_information_type = 'GHR_US_PAR_CHG_DATA_ELEMENT' then
3869: hr_utility.set_location(l_proc,45);
3870: hr_utility.set_location('info type ' || l_information_type,4);
3871: chg_data_element;
3872: elsif l_information_type = 'GHR_US_PAR_CHG_RETIRE_PLAN' then
3873: hr_utility.set_location(l_proc,50);
3874: hr_utility.set_location('info type ' || l_information_type,4);
3869: hr_utility.set_location(l_proc,45);
3870: hr_utility.set_location('info type ' || l_information_type,4);
3871: chg_data_element;
3872: elsif l_information_type = 'GHR_US_PAR_CHG_RETIRE_PLAN' then
3873: hr_utility.set_location(l_proc,50);
3874: hr_utility.set_location('info type ' || l_information_type,4);
3875: chg_retire_plan;
3876: --bug# 14030562, 12557463 commneted below as GHR_US_PAR_CHG_SCD disabled
3877: /*elsif l_information_type = 'GHR_US_PAR_CHG_SCD' then
3870: hr_utility.set_location('info type ' || l_information_type,4);
3871: chg_data_element;
3872: elsif l_information_type = 'GHR_US_PAR_CHG_RETIRE_PLAN' then
3873: hr_utility.set_location(l_proc,50);
3874: hr_utility.set_location('info type ' || l_information_type,4);
3875: chg_retire_plan;
3876: --bug# 14030562, 12557463 commneted below as GHR_US_PAR_CHG_SCD disabled
3877: /*elsif l_information_type = 'GHR_US_PAR_CHG_SCD' then
3878: hr_utility.set_location(l_proc,55);
3874: hr_utility.set_location('info type ' || l_information_type,4);
3875: chg_retire_plan;
3876: --bug# 14030562, 12557463 commneted below as GHR_US_PAR_CHG_SCD disabled
3877: /*elsif l_information_type = 'GHR_US_PAR_CHG_SCD' then
3878: hr_utility.set_location(l_proc,55);
3879: hr_utility.set_location('info type ' || l_information_type,4);
3880: chg_scd; */
3881: --Begin bug# 14751665,15882322,15882330
3882: elsif l_information_type = 'GHR_US_PAR_PRV_RET_INFO' then
3875: chg_retire_plan;
3876: --bug# 14030562, 12557463 commneted below as GHR_US_PAR_CHG_SCD disabled
3877: /*elsif l_information_type = 'GHR_US_PAR_CHG_SCD' then
3878: hr_utility.set_location(l_proc,55);
3879: hr_utility.set_location('info type ' || l_information_type,4);
3880: chg_scd; */
3881: --Begin bug# 14751665,15882322,15882330
3882: elsif l_information_type = 'GHR_US_PAR_PRV_RET_INFO' then
3883: hr_utility.set_location(l_proc ,55);
3879: hr_utility.set_location('info type ' || l_information_type,4);
3880: chg_scd; */
3881: --Begin bug# 14751665,15882322,15882330
3882: elsif l_information_type = 'GHR_US_PAR_PRV_RET_INFO' then
3883: hr_utility.set_location(l_proc ,55);
3884: hr_utility.set_location('info type ' || l_information_type,14);
3885: prv_ret_info;
3886: --End Bug# 14751665,15882322,15882330
3887: --Bug#2146912 Added condition for GHR_US_PAR_TSP
3880: chg_scd; */
3881: --Begin bug# 14751665,15882322,15882330
3882: elsif l_information_type = 'GHR_US_PAR_PRV_RET_INFO' then
3883: hr_utility.set_location(l_proc ,55);
3884: hr_utility.set_location('info type ' || l_information_type,14);
3885: prv_ret_info;
3886: --End Bug# 14751665,15882322,15882330
3887: --Bug#2146912 Added condition for GHR_US_PAR_TSP
3888: elsif l_information_type = 'GHR_US_PAR_TSP' then
3885: prv_ret_info;
3886: --End Bug# 14751665,15882322,15882330
3887: --Bug#2146912 Added condition for GHR_US_PAR_TSP
3888: elsif l_information_type = 'GHR_US_PAR_TSP' then
3889: hr_utility.set_location(l_proc,60);
3890: hr_utility.set_location('info type ' || l_information_type,4);
3891: scd_tsp;
3892: elsif l_information_type = 'GHR_US_PAR_NON_PAY_DUTY_STATUS' then
3893: hr_utility.set_location(l_proc,80);
3886: --End Bug# 14751665,15882322,15882330
3887: --Bug#2146912 Added condition for GHR_US_PAR_TSP
3888: elsif l_information_type = 'GHR_US_PAR_TSP' then
3889: hr_utility.set_location(l_proc,60);
3890: hr_utility.set_location('info type ' || l_information_type,4);
3891: scd_tsp;
3892: elsif l_information_type = 'GHR_US_PAR_NON_PAY_DUTY_STATUS' then
3893: hr_utility.set_location(l_proc,80);
3894: hr_utility.set_location('info type ' || l_information_type,4);
3889: hr_utility.set_location(l_proc,60);
3890: hr_utility.set_location('info type ' || l_information_type,4);
3891: scd_tsp;
3892: elsif l_information_type = 'GHR_US_PAR_NON_PAY_DUTY_STATUS' then
3893: hr_utility.set_location(l_proc,80);
3894: hr_utility.set_location('info type ' || l_information_type,4);
3895: non_pay_duty;
3896: elsif l_information_type = 'GHR_US_PAR_LWOP_INFO' then
3897: hr_utility.set_location(l_proc,90);
3890: hr_utility.set_location('info type ' || l_information_type,4);
3891: scd_tsp;
3892: elsif l_information_type = 'GHR_US_PAR_NON_PAY_DUTY_STATUS' then
3893: hr_utility.set_location(l_proc,80);
3894: hr_utility.set_location('info type ' || l_information_type,4);
3895: non_pay_duty;
3896: elsif l_information_type = 'GHR_US_PAR_LWOP_INFO' then
3897: hr_utility.set_location(l_proc,90);
3898: lwop_info;
3893: hr_utility.set_location(l_proc,80);
3894: hr_utility.set_location('info type ' || l_information_type,4);
3895: non_pay_duty;
3896: elsif l_information_type = 'GHR_US_PAR_LWOP_INFO' then
3897: hr_utility.set_location(l_proc,90);
3898: lwop_info;
3899: elsif l_information_type = 'GHR_US_PAR_AWARDS_BONUS' then
3900: hr_utility.set_location(l_proc,95);
3901: gov_awards;
3896: elsif l_information_type = 'GHR_US_PAR_LWOP_INFO' then
3897: hr_utility.set_location(l_proc,90);
3898: lwop_info;
3899: elsif l_information_type = 'GHR_US_PAR_AWARDS_BONUS' then
3900: hr_utility.set_location(l_proc,95);
3901: gov_awards;
3902: elsif l_information_type = 'GHR_US_PAR_CHG_HOURS' then
3903: hr_utility.set_location(l_proc,100);
3904: chg_sched_hours;
3899: elsif l_information_type = 'GHR_US_PAR_AWARDS_BONUS' then
3900: hr_utility.set_location(l_proc,95);
3901: gov_awards;
3902: elsif l_information_type = 'GHR_US_PAR_CHG_HOURS' then
3903: hr_utility.set_location(l_proc,100);
3904: chg_sched_hours;
3905: elsif l_information_type = 'GHR_US_PAR_CHG_TEN' then
3906: hr_utility.set_location(l_proc,110);
3907: chg_in_tenure;
3902: elsif l_information_type = 'GHR_US_PAR_CHG_HOURS' then
3903: hr_utility.set_location(l_proc,100);
3904: chg_sched_hours;
3905: elsif l_information_type = 'GHR_US_PAR_CHG_TEN' then
3906: hr_utility.set_location(l_proc,110);
3907: chg_in_tenure;
3908: -- Bug#2759379 Added condition for GHR_US_PAR_FEGLI
3909: elsif l_information_type = 'GHR_US_PAR_FEGLI' then
3910: hr_utility.set_location(l_proc,120);
3906: hr_utility.set_location(l_proc,110);
3907: chg_in_tenure;
3908: -- Bug#2759379 Added condition for GHR_US_PAR_FEGLI
3909: elsif l_information_type = 'GHR_US_PAR_FEGLI' then
3910: hr_utility.set_location(l_proc,120);
3911: chg_in_fegli;
3912: -- Bug#3385386 Added the following if condition.
3913: elsif l_information_type = 'GHR_US_PAR_FOR_TRANSER_ALLOW' then
3914: hr_utility.set_location(l_proc,125);
3910: hr_utility.set_location(l_proc,120);
3911: chg_in_fegli;
3912: -- Bug#3385386 Added the following if condition.
3913: elsif l_information_type = 'GHR_US_PAR_FOR_TRANSER_ALLOW' then
3914: hr_utility.set_location(l_proc,125);
3915: fta;
3916: elsif l_information_type = 'GHR_US_PAR_NFC_SEPARATION_INFO' then
3917: hr_utility.set_location(l_proc,130);
3918: nfc_separation;
3913: elsif l_information_type = 'GHR_US_PAR_FOR_TRANSER_ALLOW' then
3914: hr_utility.set_location(l_proc,125);
3915: fta;
3916: elsif l_information_type = 'GHR_US_PAR_NFC_SEPARATION_INFO' then
3917: hr_utility.set_location(l_proc,130);
3918: nfc_separation;
3919: elsif l_information_type = 'GHR_US_PAR_ETHNICITY_RACE' then
3920: hr_utility.set_location(l_proc,120);
3921: hr_utility.set_location('info type ' || l_information_type,1);
3916: elsif l_information_type = 'GHR_US_PAR_NFC_SEPARATION_INFO' then
3917: hr_utility.set_location(l_proc,130);
3918: nfc_separation;
3919: elsif l_information_type = 'GHR_US_PAR_ETHNICITY_RACE' then
3920: hr_utility.set_location(l_proc,120);
3921: hr_utility.set_location('info type ' || l_information_type,1);
3922: ethnic_race_info;
3923: elsif l_information_type = 'GHR_US_PAR_BENEFITS' then
3924: hr_utility.set_location(l_proc,125);
3917: hr_utility.set_location(l_proc,130);
3918: nfc_separation;
3919: elsif l_information_type = 'GHR_US_PAR_ETHNICITY_RACE' then
3920: hr_utility.set_location(l_proc,120);
3921: hr_utility.set_location('info type ' || l_information_type,1);
3922: ethnic_race_info;
3923: elsif l_information_type = 'GHR_US_PAR_BENEFITS' then
3924: hr_utility.set_location(l_proc,125);
3925: hr_utility.set_location('info type ' || l_information_type,1);
3920: hr_utility.set_location(l_proc,120);
3921: hr_utility.set_location('info type ' || l_information_type,1);
3922: ethnic_race_info;
3923: elsif l_information_type = 'GHR_US_PAR_BENEFITS' then
3924: hr_utility.set_location(l_proc,125);
3925: hr_utility.set_location('info type ' || l_information_type,1);
3926: --Bug # 10420280 Modified to not to call benefits for correction
3927: IF NVL(l_noa_family_code,'C') <> 'CORRECT' THEN
3928: appt_benefits;
3921: hr_utility.set_location('info type ' || l_information_type,1);
3922: ethnic_race_info;
3923: elsif l_information_type = 'GHR_US_PAR_BENEFITS' then
3924: hr_utility.set_location(l_proc,125);
3925: hr_utility.set_location('info type ' || l_information_type,1);
3926: --Bug # 10420280 Modified to not to call benefits for correction
3927: IF NVL(l_noa_family_code,'C') <> 'CORRECT' THEN
3928: appt_benefits;
3929: END IF;
3928: appt_benefits;
3929: END IF;
3930: --Bug# 5039072 Added procedure service_obligation.
3931: elsif l_information_type = 'GHR_US_PAR_SERVICE_OBLIGATION' then
3932: hr_utility.set_location(l_proc,130);
3933: hr_utility.set_location('info type ' || l_information_type,1);
3934: service_obligation;
3935: -- Bug 4280026
3936: elsif l_information_type = 'GHR_US_PAR_EMERG_ESSNTL_ASG' then
3929: END IF;
3930: --Bug# 5039072 Added procedure service_obligation.
3931: elsif l_information_type = 'GHR_US_PAR_SERVICE_OBLIGATION' then
3932: hr_utility.set_location(l_proc,130);
3933: hr_utility.set_location('info type ' || l_information_type,1);
3934: service_obligation;
3935: -- Bug 4280026
3936: elsif l_information_type = 'GHR_US_PAR_EMERG_ESSNTL_ASG' then
3937: hr_utility.set_location(l_proc,135);
3933: hr_utility.set_location('info type ' || l_information_type,1);
3934: service_obligation;
3935: -- Bug 4280026
3936: elsif l_information_type = 'GHR_US_PAR_EMERG_ESSNTL_ASG' then
3937: hr_utility.set_location(l_proc,135);
3938: hr_utility.set_location('info type ' || l_information_type,1);
3939: key_emergency_essntl;
3940: -- Bug 5482191
3941: elsif l_information_type = 'GHR_US_PAR_CONVERSION_DATES' then
3934: service_obligation;
3935: -- Bug 4280026
3936: elsif l_information_type = 'GHR_US_PAR_EMERG_ESSNTL_ASG' then
3937: hr_utility.set_location(l_proc,135);
3938: hr_utility.set_location('info type ' || l_information_type,1);
3939: key_emergency_essntl;
3940: -- Bug 5482191
3941: elsif l_information_type = 'GHR_US_PAR_CONVERSION_DATES' then
3942: hr_utility.set_location(l_proc,140);
3938: hr_utility.set_location('info type ' || l_information_type,1);
3939: key_emergency_essntl;
3940: -- Bug 5482191
3941: elsif l_information_type = 'GHR_US_PAR_CONVERSION_DATES' then
3942: hr_utility.set_location(l_proc,140);
3943: hr_utility.set_location('info type ' || l_information_type,1);
3944: ghr_conv_dates;
3945: /* --Begin Bug# 4588575
3946: elsif l_information_type = 'GHR_US_PAR_PROBATION_INFO' then
3939: key_emergency_essntl;
3940: -- Bug 5482191
3941: elsif l_information_type = 'GHR_US_PAR_CONVERSION_DATES' then
3942: hr_utility.set_location(l_proc,140);
3943: hr_utility.set_location('info type ' || l_information_type,1);
3944: ghr_conv_dates;
3945: /* --Begin Bug# 4588575
3946: elsif l_information_type = 'GHR_US_PAR_PROBATION_INFO' then
3947: hr_utility.set_location(l_proc,270);
3943: hr_utility.set_location('info type ' || l_information_type,1);
3944: ghr_conv_dates;
3945: /* --Begin Bug# 4588575
3946: elsif l_information_type = 'GHR_US_PAR_PROBATION_INFO' then
3947: hr_utility.set_location(l_proc,270);
3948: hr_utility.set_location('info type ' || l_information_type,270);
3949: ghr_prob_info;
3950: --end Bug# 4588575
3951: */ --Backout the changes done for Bug# 4588575
3944: ghr_conv_dates;
3945: /* --Begin Bug# 4588575
3946: elsif l_information_type = 'GHR_US_PAR_PROBATION_INFO' then
3947: hr_utility.set_location(l_proc,270);
3948: hr_utility.set_location('info type ' || l_information_type,270);
3949: ghr_prob_info;
3950: --end Bug# 4588575
3951: */ --Backout the changes done for Bug# 4588575
3952: --Begin Bug# 12557463
3950: --end Bug# 4588575
3951: */ --Backout the changes done for Bug# 4588575
3952: --Begin Bug# 12557463
3953: elsif l_information_type = 'GHR_US_PAR_SCD_INFO' then
3954: hr_utility.set_location(l_proc,280);
3955: hr_utility.set_location('info type ' || l_information_type,280);
3956: ghr_scd_info;
3957: --End Bug# 12557463
3958: -- Modifications related to Bug#6312144 -- New RPA EIT Benefits
3951: */ --Backout the changes done for Bug# 4588575
3952: --Begin Bug# 12557463
3953: elsif l_information_type = 'GHR_US_PAR_SCD_INFO' then
3954: hr_utility.set_location(l_proc,280);
3955: hr_utility.set_location('info type ' || l_information_type,280);
3956: ghr_scd_info;
3957: --End Bug# 12557463
3958: -- Modifications related to Bug#6312144 -- New RPA EIT Benefits
3959: -- Bug# 8653508 added INFO GHR_US_PAR_DET_BENEFIT_CONT
3957: --End Bug# 12557463
3958: -- Modifications related to Bug#6312144 -- New RPA EIT Benefits
3959: -- Bug# 8653508 added INFO GHR_US_PAR_DET_BENEFIT_CONT
3960: elsif l_information_type IN ('GHR_US_PAR_BENEFITS_CONT','GHR_US_PAR_DET_BENEFIT_CONT') then
3961: hr_utility.set_location(l_proc,5);
3962: hr_utility.set_location('info type ' || l_information_type,1);
3963: ipa_benefits_cont;
3964: -- Begin Bug# 8653508
3965: elsif l_information_type = ('GHR_US_PAR_DET_INFO') then
3958: -- Modifications related to Bug#6312144 -- New RPA EIT Benefits
3959: -- Bug# 8653508 added INFO GHR_US_PAR_DET_BENEFIT_CONT
3960: elsif l_information_type IN ('GHR_US_PAR_BENEFITS_CONT','GHR_US_PAR_DET_BENEFIT_CONT') then
3961: hr_utility.set_location(l_proc,5);
3962: hr_utility.set_location('info type ' || l_information_type,1);
3963: ipa_benefits_cont;
3964: -- Begin Bug# 8653508
3965: elsif l_information_type = ('GHR_US_PAR_DET_INFO') then
3966: hr_utility.set_location(l_proc,5);
3962: hr_utility.set_location('info type ' || l_information_type,1);
3963: ipa_benefits_cont;
3964: -- Begin Bug# 8653508
3965: elsif l_information_type = ('GHR_US_PAR_DET_INFO') then
3966: hr_utility.set_location(l_proc,5);
3967: hr_utility.set_location('info type ' || l_information_type,1);
3968: detail_info;
3969: --End Bug# 8653508
3970: elsif l_information_type = 'GHR_US_PAR_RETIRMENT_SYS_INFO' then
3963: ipa_benefits_cont;
3964: -- Begin Bug# 8653508
3965: elsif l_information_type = ('GHR_US_PAR_DET_INFO') then
3966: hr_utility.set_location(l_proc,5);
3967: hr_utility.set_location('info type ' || l_information_type,1);
3968: detail_info;
3969: --End Bug# 8653508
3970: elsif l_information_type = 'GHR_US_PAR_RETIRMENT_SYS_INFO' then
3971: hr_utility.set_location(l_proc,5);
3967: hr_utility.set_location('info type ' || l_information_type,1);
3968: detail_info;
3969: --End Bug# 8653508
3970: elsif l_information_type = 'GHR_US_PAR_RETIRMENT_SYS_INFO' then
3971: hr_utility.set_location(l_proc,5);
3972: hr_utility.set_location('info type ' || l_information_type,1);
3973: retirement_system_info;
3974: elsif l_information_type = 'GHR_US_PAR_BENEFIT_INFO' then
3975: hr_utility.set_location(l_proc,5);
3968: detail_info;
3969: --End Bug# 8653508
3970: elsif l_information_type = 'GHR_US_PAR_RETIRMENT_SYS_INFO' then
3971: hr_utility.set_location(l_proc,5);
3972: hr_utility.set_location('info type ' || l_information_type,1);
3973: retirement_system_info;
3974: elsif l_information_type = 'GHR_US_PAR_BENEFIT_INFO' then
3975: hr_utility.set_location(l_proc,5);
3976: hr_utility.set_location('info type ' || l_information_type,1);
3971: hr_utility.set_location(l_proc,5);
3972: hr_utility.set_location('info type ' || l_information_type,1);
3973: retirement_system_info;
3974: elsif l_information_type = 'GHR_US_PAR_BENEFIT_INFO' then
3975: hr_utility.set_location(l_proc,5);
3976: hr_utility.set_location('info type ' || l_information_type,1);
3977: par_benefits;
3978: -- Modifications related to Bug#6312144 -- New RPA EIT Benefits
3979: end if;
3972: hr_utility.set_location('info type ' || l_information_type,1);
3973: retirement_system_info;
3974: elsif l_information_type = 'GHR_US_PAR_BENEFIT_INFO' then
3975: hr_utility.set_location(l_proc,5);
3976: hr_utility.set_location('info type ' || l_information_type,1);
3977: par_benefits;
3978: -- Modifications related to Bug#6312144 -- New RPA EIT Benefits
3979: end if;
3980:
4057: /*Start - Bug 6129752*/
4058: IF p_assignment_id IS NULL THEN
4059: FOR C_GET_ASSIGNMENNT_ID IN GET_ASSIGNEMNT_ID LOOP
4060: l_assignment_id := C_GET_ASSIGNMENNT_ID.assignment_id;
4061: IF ( hr_utility.debug_enabled()) THEN
4062: hr_utility.set_location(' P_Assignment id is null and l_assignment is'||l_assignment_id,1733);
4063: END IF;
4064: END LOOP;
4065: END IF;
4058: IF p_assignment_id IS NULL THEN
4059: FOR C_GET_ASSIGNMENNT_ID IN GET_ASSIGNEMNT_ID LOOP
4060: l_assignment_id := C_GET_ASSIGNMENNT_ID.assignment_id;
4061: IF ( hr_utility.debug_enabled()) THEN
4062: hr_utility.set_location(' P_Assignment id is null and l_assignment is'||l_assignment_id,1733);
4063: END IF;
4064: END LOOP;
4065: END IF;
4066: /*End - Bug 6129752*/
4105: exit;
4106: END LOOP;
4107: END IF;
4108: --Begin Bug# 14514445
4109: hr_utility.set_location('promotion_eligibility_date Position Id in Salary change '|| l_position_id,350);
4110: l_pos_ei_data := null;
4111: ghr_history_fetch.fetch_positionei
4112: (p_position_id => l_position_id,
4113: p_information_type => 'GHR_US_POS_VALID_GRADE',
4121: FOR grade_name in grade_id(l_pos_ei_data.poei_information4) LOOP
4122: l_target_grade_name := grade_name.name;
4123: exit;
4124: END LOOP;
4125: hr_utility.set_location('promotion_eligibility_date Valid Grade in Salary change '|| l_valid_grade_name,351);
4126: hr_utility.set_location('promotion_eligibility_date Target Grade in Salary change '|| l_target_grade_name,352);
4127:
4128: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
4129: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
4122: l_target_grade_name := grade_name.name;
4123: exit;
4124: END LOOP;
4125: hr_utility.set_location('promotion_eligibility_date Valid Grade in Salary change '|| l_valid_grade_name,351);
4126: hr_utility.set_location('promotion_eligibility_date Target Grade in Salary change '|| l_target_grade_name,352);
4127:
4128: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
4129: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
4130: hr_utility.set_location('promotion_eligibility_date default in Salary change '|| l_valid_grade_name,353);
4126: hr_utility.set_location('promotion_eligibility_date Target Grade in Salary change '|| l_target_grade_name,352);
4127:
4128: IF SUBSTR(l_valid_grade_name,1,2) = SUBSTR(l_target_grade_name,1,2) AND
4129: TO_NUMBER(SUBSTR(l_valid_grade_name,4,5)) < TO_NUMBER(SUBSTR(l_target_grade_name,4,5)) THEN
4130: hr_utility.set_location('promotion_eligibility_date default in Salary change '|| l_valid_grade_name,353);
4131: IF l_first_noa_code IN ('702') THEN
4132: set_ei(l_org_rec.rei_information7,fnd_date.date_to_canonical(p_effective_date+365),l_rei_rec.rei_information7,l_refresh_flag);
4133: ELSE
4134: ghr_history_fetch.fetch_peopleei
4150:
4151: l_rei_rec.information_type := l_information_type;
4152: l_rei_rec.pa_request_id := p_pa_request_id;
4153:
4154: hr_utility.set_location('Test Null EI',110);
4155: If l_rei_rec.rei_information1 is not null or l_rei_rec.rei_information2 is not null or
4156: l_rei_rec.rei_information3 is not null or l_rei_rec.rei_information4 is not null or
4157: l_rei_rec.rei_information5 is not null or l_rei_rec.rei_information6 is not null or
4158: l_rei_rec.rei_information7 is not null or l_rei_rec.rei_information8 is not null or
4166: l_rei_rec.rei_information23 is not null or l_rei_rec.rei_information24 is not null or
4167: l_rei_rec.rei_information25 is not null or l_rei_rec.rei_information26 is not null or
4168: l_rei_rec.rei_information27 is not null or l_rei_rec.rei_information28 is not null or
4169: l_rei_rec.rei_information29 is not null or l_rei_rec.rei_information30 is not null then
4170: hr_utility.set_location('l_update_rei is Y ',1);
4171: l_update_rei := 'Y';
4172: Else
4173: l_update_rei := 'N';
4174: hr_utility.set_location('l_update_rei is N ',1);
4170: hr_utility.set_location('l_update_rei is Y ',1);
4171: l_update_rei := 'Y';
4172: Else
4173: l_update_rei := 'N';
4174: hr_utility.set_location('l_update_rei is N ',1);
4175: End if;
4176:
4177: hr_utility.set_location('Test Null EI',110);
4178: determine_operation
4173: l_update_rei := 'N';
4174: hr_utility.set_location('l_update_rei is N ',1);
4175: End if;
4176:
4177: hr_utility.set_location('Test Null EI',110);
4178: determine_operation
4179: (p_pa_request_id => p_pa_request_id,
4180: p_information_type => l_information_type,
4181: p_update_rei => l_update_rei,
4184: p_pa_request_extra_info_id => l_rei_rec.pa_request_extra_info_id,
4185: p_object_version_number => l_rei_rec.object_version_number
4186: );
4187:
4188: hr_utility.set_location('PAR ' || to_char(l_rei_rec.pa_request_id),1);
4189: hr_utility.set_location('Info type ' || l_rei_rec.information_type,2);
4190: hr_utility.set_location('Flag ' || l_flag ,3);
4191:
4192: generic_populate_extra_info
4185: p_object_version_number => l_rei_rec.object_version_number
4186: );
4187:
4188: hr_utility.set_location('PAR ' || to_char(l_rei_rec.pa_request_id),1);
4189: hr_utility.set_location('Info type ' || l_rei_rec.information_type,2);
4190: hr_utility.set_location('Flag ' || l_flag ,3);
4191:
4192: generic_populate_extra_info
4193: (p_rei_rec => l_rei_rec,
4186: );
4187:
4188: hr_utility.set_location('PAR ' || to_char(l_rei_rec.pa_request_id),1);
4189: hr_utility.set_location('Info type ' || l_rei_rec.information_type,2);
4190: hr_utility.set_location('Flag ' || l_flag ,3);
4191:
4192: generic_populate_extra_info
4193: (p_rei_rec => l_rei_rec,
4194: p_org_rec => l_org_rec,
4339:
4340:
4341:
4342: begin
4343: hr_utility.set_location('Entering fetch generic ',1);
4344: hr_utility.set_location('Entering ' || l_proc,5);
4345: l_update_rei := 'N';
4346: l_flag := null;
4347: l_person_id := p_person_id;
4340:
4341:
4342: begin
4343: hr_utility.set_location('Entering fetch generic ',1);
4344: hr_utility.set_location('Entering ' || l_proc,5);
4345: l_update_rei := 'N';
4346: l_flag := null;
4347: l_person_id := p_person_id;
4348: l_assignment_id := p_assignment_id;
4508: l_org_rec.rei_information30 := rei_rec.rei_information30;
4509: end loop;
4510:
4511:
4512: hr_utility.set_location(l_proc,10);
4513: l_exists := FALSE;
4514:
4515: If nvl(l_noa_family_code,hr_api.g_varchar2) = 'APP' then
4516: hr_utility.set_location(l_proc,11);
4512: hr_utility.set_location(l_proc,10);
4513: l_exists := FALSE;
4514:
4515: If nvl(l_noa_family_code,hr_api.g_varchar2) = 'APP' then
4516: hr_utility.set_location(l_proc,11);
4517: l_asg_refresh_flag := 'Y';
4518: If p_person_id is not null then
4519: for bus_gp in c_bus_gp loop
4520: hr_utility.set_location('bus gp is ' || bus_gp.business_group_id,12);
4516: hr_utility.set_location(l_proc,11);
4517: l_asg_refresh_flag := 'Y';
4518: If p_person_id is not null then
4519: for bus_gp in c_bus_gp loop
4520: hr_utility.set_location('bus gp is ' || bus_gp.business_group_id,12);
4521: l_business_group_id := bus_gp.business_group_id;
4522: end loop;
4523: Else
4524: fnd_profile.get('PER_BUSINESS_GROUP_ID',l_business_group_id);
4521: l_business_group_id := bus_gp.business_group_id;
4522: end loop;
4523: Else
4524: fnd_profile.get('PER_BUSINESS_GROUP_ID',l_business_group_id);
4525: hr_utility.set_location('bus gp is ' || l_business_group_id,13);
4526: End if;
4527: for def_payroll in c_def_payroll loop
4528: hr_utility.set_location(l_proc,14);
4529: l_exists := TRUE;
4524: fnd_profile.get('PER_BUSINESS_GROUP_ID',l_business_group_id);
4525: hr_utility.set_location('bus gp is ' || l_business_group_id,13);
4526: End if;
4527: for def_payroll in c_def_payroll loop
4528: hr_utility.set_location(l_proc,14);
4529: l_exists := TRUE;
4530: set_ei(l_org_rec.rei_information3,to_char(def_payroll.payroll_id),l_rei_rec.rei_information3,l_asg_refresh_flag);
4531: l_rei_rec.information_type := 'GHR_US_PAR_PAYROLL_TYPE';
4532: exit;
4531: l_rei_rec.information_type := 'GHR_US_PAR_PAYROLL_TYPE';
4532: exit;
4533: end loop;
4534: ELSE -- For conversion of exemployee defaults to 'Biweekly' -- Bug # 1236354
4535: hr_utility.set_location(l_proc,15);
4536: FOR person_type_rec in c_person_type LOOP
4537: l_person_type := person_type_rec.system_person_type;
4538: hr_utility.set_location(' Person Type is ' || l_person_type,16);
4539: EXIT;
4534: ELSE -- For conversion of exemployee defaults to 'Biweekly' -- Bug # 1236354
4535: hr_utility.set_location(l_proc,15);
4536: FOR person_type_rec in c_person_type LOOP
4537: l_person_type := person_type_rec.system_person_type;
4538: hr_utility.set_location(' Person Type is ' || l_person_type,16);
4539: EXIT;
4540: END LOOP;
4541: IF l_noa_family_code = 'CONV_APP' and l_person_type = 'EX_EMP' THEN
4542: l_asg_refresh_flag := 'Y';
4541: IF l_noa_family_code = 'CONV_APP' and l_person_type = 'EX_EMP' THEN
4542: l_asg_refresh_flag := 'Y';
4543: IF p_person_id is not null THEN
4544: FOR bus_gp in c_bus_gp LOOP
4545: hr_utility.set_location('bus gp is ' || bus_gp.business_group_id,17);
4546: l_business_group_id := bus_gp.business_group_id;
4547: END LOOP;
4548: END IF;
4549: FOR def_payroll in c_def_payroll LOOP
4546: l_business_group_id := bus_gp.business_group_id;
4547: END LOOP;
4548: END IF;
4549: FOR def_payroll in c_def_payroll LOOP
4550: hr_utility.set_location(l_proc,18);
4551: l_exists := TRUE;
4552: set_ei(l_org_rec.rei_information3,to_char(def_payroll.payroll_id),l_rei_rec.rei_information3,l_asg_refresh_flag);
4553: l_rei_rec.information_type := 'GHR_US_PAR_PAYROLL_TYPE';
4554: exit;
4554: exit;
4555: END LOOP;
4556: ELSE -- fetch payroll from employee's assignment record
4557: FOR payroll in C_payroll LOOP
4558: hr_utility.set_location(l_proc,19);
4559: l_exists := TRUE;
4560: set_ei(l_org_rec.rei_information3,to_char(payroll.payroll_id),l_rei_rec.rei_information3,l_asg_refresh_flag);
4561: l_rei_rec.information_type := 'GHR_US_PAR_PAYROLL_TYPE';
4562: END LOOP;
4563: END IF;
4564: END IF;
4565:
4566: If l_rei_rec.rei_information3 is not null then
4567: hr_utility.set_location(l_proc,20);
4568: l_update_rei := 'Y';
4569: End if;
4570:
4571: l_rei_rec.pa_request_id := p_pa_request_id;
4568: l_update_rei := 'Y';
4569: End if;
4570:
4571: l_rei_rec.pa_request_id := p_pa_request_id;
4572: hr_utility.set_location(l_proc,25);
4573: determine_operation
4574: (p_pa_request_id => p_pa_request_id,
4575: p_information_type => 'GHR_US_PAR_PAYROLL_TYPE',
4576: p_update_rei => l_update_rei,
4579: p_pa_request_extra_info_id => l_rei_rec.pa_request_extra_info_id,
4580: p_object_version_number => l_rei_rec.object_version_number
4581: );
4582:
4583: hr_utility.set_location(l_proc,30);
4584: generic_populate_extra_info
4585: (p_rei_rec => l_rei_rec,
4586: p_org_rec => l_org_rec,
4587: p_flag => l_flag
4668: l_org_rec.rei_information30 := rei_rec.rei_information30;
4669: end loop;
4670:
4671:
4672: hr_utility.set_location(l_proc,40);
4673: ghr_api.return_special_information
4674: (p_person_id => l_person_id,
4675: p_structure_name => 'US Fed Perf Appraisal',
4676: p_effective_date => nvl(p_effective_date,trunc(sysdate)),
4675: p_structure_name => 'US Fed Perf Appraisal',
4676: p_effective_date => nvl(p_effective_date,trunc(sysdate)),
4677: p_special_info => l_perf_appraisal
4678: );
4679: hr_utility.set_location(l_proc,45);
4680: -- use set_ei
4681: set_ei(l_org_rec.rei_information3,l_perf_appraisal.segment2,l_rei_rec.rei_information3,l_per_refresh_flag);
4682: set_ei(l_org_rec.rei_information4,l_perf_appraisal.segment4,l_rei_rec.rei_information4,l_per_refresh_flag);
4683:
4722: l_rei_rec.rei_information13 is not null or l_rei_rec.rei_information14 is not null or
4723: l_rei_rec.rei_information15 is not null or l_rei_rec.rei_information16 is not null or
4724: l_rei_rec.rei_information17 is not null or l_rei_rec.rei_information18 is not null then
4725:
4726: hr_utility.set_location(l_proc,50);
4727: l_update_rei := 'Y';
4728: Else
4729: hr_utility.set_location(l_proc,55);
4730: l_update_rei := 'N';
4725:
4726: hr_utility.set_location(l_proc,50);
4727: l_update_rei := 'Y';
4728: Else
4729: hr_utility.set_location(l_proc,55);
4730: l_update_rei := 'N';
4731: End if;*/
4732: --Commented the above IF condition since 'US Fed Perf Appraisal' is a mandatory SIT
4733: -- and we will never have to delete this record from the Extra info table.!!
4736: ---
4737: l_rei_rec.pa_request_id := p_pa_request_id;
4738: l_rei_rec.information_type := 'GHR_US_PAR_PERF_APPRAISAL';
4739:
4740: hr_utility.set_location(l_proc,60);
4741:
4742: determine_operation
4743: (p_pa_request_id => p_pa_request_id,
4744: p_information_type => 'GHR_US_PAR_PERF_APPRAISAL',
4748: p_pa_request_extra_info_id => l_rei_rec.pa_request_extra_info_id,
4749: p_object_version_number => l_rei_rec.object_version_number
4750: );
4751:
4752: hr_utility.set_location(l_proc,65);
4753: generic_populate_extra_info
4754: (p_rei_rec => l_rei_rec,
4755: p_org_rec => l_org_rec,
4756: p_flag => l_flag
4755: p_org_rec => l_org_rec,
4756: p_flag => l_flag
4757: );
4758: End if;
4759: hr_utility.set_location('Leaving ' || l_proc,70);
4760: End fetch_generic_extra_info;
4761:
4762:
4763: -- Get Information Type
4845: where rei.pa_request_id = p_pa_request_id
4846: and rei.information_type = p_information_type;
4847:
4848: Begin
4849: hr_utility.set_location('Entering '|| l_proc,10);
4850: l_rei_rec := p_rei_rec;
4851:
4852: for rei_rec in c_rei_rec loop
4853: p_object_version_number := rei_rec.object_version_number;
4881: l_dummy.rei_information27 := rei_rec.rei_information27;
4882: l_dummy.rei_information28 := rei_rec.rei_information28;
4883: l_dummy.rei_information29 := rei_rec.rei_information29;
4884: l_dummy.rei_information30 := rei_rec.rei_information30;
4885: hr_utility.set_location(l_proc,20);
4886: l_exists := true;
4887: end loop;
4888:
4889: -- If non-sf52 data already exists, then as a result of the new changes, there might be need to update/ delete it.
4892: -- Else if non-sf52 does not exists, then if update_rei = 'Y', then Create. else do nothing.
4893:
4894:
4895: If l_exists then
4896: hr_utility.set_location(' rei exists ',1);
4897: If p_update_rei = 'Y' then
4898: hr_utility.set_location(' Update ',1);
4899: If nvl(l_dummy.rei_information3,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information3,hr_api.g_varchar2) or
4900: nvl(l_dummy.rei_information4,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information4,hr_api.g_varchar2) or
4894:
4895: If l_exists then
4896: hr_utility.set_location(' rei exists ',1);
4897: If p_update_rei = 'Y' then
4898: hr_utility.set_location(' Update ',1);
4899: If nvl(l_dummy.rei_information3,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information3,hr_api.g_varchar2) or
4900: nvl(l_dummy.rei_information4,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information4,hr_api.g_varchar2) or
4901: nvl(l_dummy.rei_information5,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information5,hr_api.g_varchar2) or
4902: nvl(l_dummy.rei_information6,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information6,hr_api.g_varchar2) or
4923: nvl(l_dummy.rei_information27,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information27,hr_api.g_varchar2) or
4924: nvl(l_dummy.rei_information28,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information28,hr_api.g_varchar2) or
4925: nvl(l_dummy.rei_information29,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information29,hr_api.g_varchar2) or
4926: nvl(l_dummy.rei_information30,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information30,hr_api.g_varchar2) then
4927: hr_utility.set_location('operation is Update',1);
4928: p_operation_flag := 'U';
4929: hr_utility.set_location(l_proc,25);
4930: End if;
4931: Else
4925: nvl(l_dummy.rei_information29,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information29,hr_api.g_varchar2) or
4926: nvl(l_dummy.rei_information30,hr_api.g_varchar2) <> nvl(l_rei_rec.rei_information30,hr_api.g_varchar2) then
4927: hr_utility.set_location('operation is Update',1);
4928: p_operation_flag := 'U';
4929: hr_utility.set_location(l_proc,25);
4930: End if;
4931: Else
4932: hr_utility.set_location(' Delete ',1);
4933: hr_utility.set_location(l_proc,30);
4928: p_operation_flag := 'U';
4929: hr_utility.set_location(l_proc,25);
4930: End if;
4931: Else
4932: hr_utility.set_location(' Delete ',1);
4933: hr_utility.set_location(l_proc,30);
4934: p_operation_flag := 'D';
4935: End if;
4936: Else
4929: hr_utility.set_location(l_proc,25);
4930: End if;
4931: Else
4932: hr_utility.set_location(' Delete ',1);
4933: hr_utility.set_location(l_proc,30);
4934: p_operation_flag := 'D';
4935: End if;
4936: Else
4937: If p_update_rei = 'Y' then
4934: p_operation_flag := 'D';
4935: End if;
4936: Else
4937: If p_update_rei = 'Y' then
4938: hr_utility.set_location(l_proc,35);
4939: hr_utility.set_location(' Create ',1);
4940: p_operation_flag := 'C';
4941: Else
4942: hr_utility.set_location(' Null ',1);
4935: End if;
4936: Else
4937: If p_update_rei = 'Y' then
4938: hr_utility.set_location(l_proc,35);
4939: hr_utility.set_location(' Create ',1);
4940: p_operation_flag := 'C';
4941: Else
4942: hr_utility.set_location(' Null ',1);
4943: hr_utility.set_location(l_proc,40);
4938: hr_utility.set_location(l_proc,35);
4939: hr_utility.set_location(' Create ',1);
4940: p_operation_flag := 'C';
4941: Else
4942: hr_utility.set_location(' Null ',1);
4943: hr_utility.set_location(l_proc,40);
4944: p_operation_flag := null;
4945: End if;
4946: End if;
4939: hr_utility.set_location(' Create ',1);
4940: p_operation_flag := 'C';
4941: Else
4942: hr_utility.set_location(' Null ',1);
4943: hr_utility.set_location(l_proc,40);
4944: p_operation_flag := null;
4945: End if;
4946: End if;
4947: End determine_operation;
4958: l_org_rec ghr_pa_request_ei_shadow%rowtype;
4959: l_proc varchar2(72) := 'generic_populate_extra_info';
4960:
4961: Begin
4962: hr_utility.set_location('Entering ' || l_proc,10);
4963: hr_utility.set_location('Flag := ' || l_flag,1);
4964:
4965: -- l_flag :
4966: -- C - Create
4959: l_proc varchar2(72) := 'generic_populate_extra_info';
4960:
4961: Begin
4962: hr_utility.set_location('Entering ' || l_proc,10);
4963: hr_utility.set_location('Flag := ' || l_flag,1);
4964:
4965: -- l_flag :
4966: -- C - Create
4967: -- U - Update
4968: -- D - Delete
4969: -- Null - do nothing
4970:
4971: If l_flag is not null then
4972: hr_utility.set_location(l_proc,15);
4973:
4974: If l_flag = 'C' then
4975: hr_utility.set_location(l_proc,20);
4976: ghr_par_extra_info_api.create_pa_request_extra_info
4971: If l_flag is not null then
4972: hr_utility.set_location(l_proc,15);
4973:
4974: If l_flag = 'C' then
4975: hr_utility.set_location(l_proc,20);
4976: ghr_par_extra_info_api.create_pa_request_extra_info
4977: (p_pa_request_id => l_rei_rec.pa_request_id,
4978: p_information_type => l_rei_rec.information_type, -- to be replaced by the info_type returned by the get_infor._type
4979: p_rei_information_category => l_rei_rec.information_type,
5082: l_rei_rec.rei_information30
5083: );
5084:
5085: Elsif l_flag = 'U' then
5086: hr_utility.set_location(l_proc,25);
5087: hr_utility.set_location('update extra info',1);
5088: ghr_par_extra_info_api.update_pa_request_extra_info
5089: (P_PA_REQUEST_EXTRA_INFO_ID => l_rei_rec.pa_request_extra_info_id,
5090: P_OBJECT_VERSION_NUMBER => l_rei_rec.object_version_number ,
5083: );
5084:
5085: Elsif l_flag = 'U' then
5086: hr_utility.set_location(l_proc,25);
5087: hr_utility.set_location('update extra info',1);
5088: ghr_par_extra_info_api.update_pa_request_extra_info
5089: (P_PA_REQUEST_EXTRA_INFO_ID => l_rei_rec.pa_request_extra_info_id,
5090: P_OBJECT_VERSION_NUMBER => l_rei_rec.object_version_number ,
5091: p_rei_information1 => l_rei_rec.rei_information1,
5118: p_rei_information28 => l_rei_rec.rei_information28,
5119: p_rei_information29 => l_rei_rec.rei_information29,
5120: p_rei_information30 => l_rei_rec.rei_information30
5121: );
5122: hr_utility.set_location('bef upd of shad ' || 'ovn ' || l_rei_rec.object_version_number,2);
5123:
5124: update ghr_pa_request_ei_shadow set
5125: rei_information1 = p_org_rec.rei_information1,
5126: rei_information2 = p_org_rec.rei_information2,
5155: where pa_request_extra_info_id = l_rei_rec.pa_request_extra_info_id;
5156:
5157:
5158: Elsif l_flag = 'D' then
5159: hr_utility.set_location(l_proc,30);
5160: hr_utility.set_location('delete extra info',1);
5161:
5162: ghr_par_extra_info_api.delete_pa_request_extra_info
5163: (p_pa_request_extra_info_id => l_rei_rec.pa_request_extra_info_id,
5156:
5157:
5158: Elsif l_flag = 'D' then
5159: hr_utility.set_location(l_proc,30);
5160: hr_utility.set_location('delete extra info',1);
5161:
5162: ghr_par_extra_info_api.delete_pa_request_extra_info
5163: (p_pa_request_extra_info_id => l_rei_rec.pa_request_extra_info_id,
5164: p_object_version_number => l_rei_rec.object_version_number
5166: delete from ghr_pa_request_ei_shadow
5167: where pa_request_extra_info_id = l_rei_rec.pa_request_extra_info_id;
5168: End if;
5169: End if;
5170: hr_utility.set_location('Leaving ' || l_proc,40);
5171: end generic_populate_extra_info ;
5172:
5173: Procedure set_ei
5174: (p_original in out NOCOPY varchar2,
5179:
5180: begin
5181:
5182: If p_refresh_flag = 'Y' then
5183: hr_utility.set_location('in set ei - Y ',5);
5184: If nvl(p_as_in_ddf,hr_api.g_varchar2) <> nvl(p_as_in_core,hr_api.g_varchar2) and
5185: nvl(p_as_in_ddf,hr_api.g_varchar2) = nvl(p_original,hr_api.g_varchar2) then
5186: p_as_in_ddf := p_as_in_core;
5187: End if;
5185: nvl(p_as_in_ddf,hr_api.g_varchar2) = nvl(p_original,hr_api.g_varchar2) then
5186: p_as_in_ddf := p_as_in_core;
5187: End if;
5188: Else
5189: hr_utility.set_location('in set ei - N ',6);
5190: p_as_in_ddf := p_as_in_core;
5191: End if;
5192: p_original := p_as_in_core;
5193: End set_ei;