110: -- Declare cursors and local variables
111: --
112: l_proc varchar2(72);
113: --
114: CURSOR c_noa_family_code(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
115: SELECT noa_family_code
116: FROM ghr_pa_requests
117: WHERE pa_request_id = c_pa_request_id;
118: l_noa_family_code ghr_pa_requests.noa_family_code%type;
112: l_proc varchar2(72);
113: --
114: CURSOR c_noa_family_code(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
115: SELECT noa_family_code
116: FROM ghr_pa_requests
117: WHERE pa_request_id = c_pa_request_id;
118: l_noa_family_code ghr_pa_requests.noa_family_code%type;
119:
120: begin
114: CURSOR c_noa_family_code(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
115: SELECT noa_family_code
116: FROM ghr_pa_requests
117: WHERE pa_request_id = c_pa_request_id;
118: l_noa_family_code ghr_pa_requests.noa_family_code%type;
119:
120: begin
121: l_proc := g_package||'create_pa_request_extra_info';
122: hr_utility.set_location('Entering:'|| l_proc, 5);
476: l_proc varchar2(72) ;
477: l_object_version_number ghr_pa_request_extra_info.object_version_number%TYPE;
478: l_obj_version_number ghr_pa_request_extra_info.object_version_number%TYPE; -- NOCOPY changes
479: --
480: CURSOR c_noa_family_code(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
481: SELECT noa_family_code
482: FROM ghr_pa_requests
483: WHERE pa_request_id = c_pa_request_id;
484: l_noa_family_code ghr_pa_requests.noa_family_code%type;
478: l_obj_version_number ghr_pa_request_extra_info.object_version_number%TYPE; -- NOCOPY changes
479: --
480: CURSOR c_noa_family_code(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
481: SELECT noa_family_code
482: FROM ghr_pa_requests
483: WHERE pa_request_id = c_pa_request_id;
484: l_noa_family_code ghr_pa_requests.noa_family_code%type;
485: l_pa_req_id ghr_pa_requests.pa_request_id%type;
486: l_information_type ghr_pa_request_extra_info.information_type%type;
480: CURSOR c_noa_family_code(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
481: SELECT noa_family_code
482: FROM ghr_pa_requests
483: WHERE pa_request_id = c_pa_request_id;
484: l_noa_family_code ghr_pa_requests.noa_family_code%type;
485: l_pa_req_id ghr_pa_requests.pa_request_id%type;
486: l_information_type ghr_pa_request_extra_info.information_type%type;
487:
488: CURSOR c_pa_req_id (c_pa_req_extra_info_id ghr_pa_request_extra_info.pa_request_extra_info_id%type) IS
481: SELECT noa_family_code
482: FROM ghr_pa_requests
483: WHERE pa_request_id = c_pa_request_id;
484: l_noa_family_code ghr_pa_requests.noa_family_code%type;
485: l_pa_req_id ghr_pa_requests.pa_request_id%type;
486: l_information_type ghr_pa_request_extra_info.information_type%type;
487:
488: CURSOR c_pa_req_id (c_pa_req_extra_info_id ghr_pa_request_extra_info.pa_request_extra_info_id%type) IS
489: SELECT pa_request_id, information_type
917: ,p_rei_information29 VARCHAR2
918: ,p_rei_information30 VARCHAR2
919: ,p_ben_ei_validate varchar2 default 'FALSE') IS
920:
921: CURSOR c_get_pa_req(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
922: SELECT *
923: FROM ghr_pa_requests
924: WHERE pa_request_id = c_pa_request_id;
925:
919: ,p_ben_ei_validate varchar2 default 'FALSE') IS
920:
921: CURSOR c_get_pa_req(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
922: SELECT *
923: FROM ghr_pa_requests
924: WHERE pa_request_id = c_pa_request_id;
925:
926: CURSOR c_payroll_id(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
927: SELECT rei_information3 payroll_id
922: SELECT *
923: FROM ghr_pa_requests
924: WHERE pa_request_id = c_pa_request_id;
925:
926: CURSOR c_payroll_id(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
927: SELECT rei_information3 payroll_id
928: FROM ghr_pa_request_extra_info
929: WHERE pa_request_id = c_pa_request_id
930: AND information_type = 'GHR_US_PAR_PAYROLL_TYPE';
936: AND TO_CHAR(start_date,'YYYY') = c_year
937: AND TO_CHAR(start_date,'MM') = c_month;
938:
939:
940: l_retirement_plan ghr_pa_requests.retirement_plan%type;
941: l_pa_request_id ghr_pa_requests.pa_request_id%type;
942: l_start_date per_time_periods.start_date%type;
943: l_effective_date ghr_pa_requests.effective_date%type;
944: l_first_noa_code ghr_pa_requests.first_noa_code%type;
937: AND TO_CHAR(start_date,'MM') = c_month;
938:
939:
940: l_retirement_plan ghr_pa_requests.retirement_plan%type;
941: l_pa_request_id ghr_pa_requests.pa_request_id%type;
942: l_start_date per_time_periods.start_date%type;
943: l_effective_date ghr_pa_requests.effective_date%type;
944: l_first_noa_code ghr_pa_requests.first_noa_code%type;
945: l_tenure ghr_pa_requests.tenure%type;
939:
940: l_retirement_plan ghr_pa_requests.retirement_plan%type;
941: l_pa_request_id ghr_pa_requests.pa_request_id%type;
942: l_start_date per_time_periods.start_date%type;
943: l_effective_date ghr_pa_requests.effective_date%type;
944: l_first_noa_code ghr_pa_requests.first_noa_code%type;
945: l_tenure ghr_pa_requests.tenure%type;
946: l_annuitant_indicator ghr_pa_requests.annuitant_indicator%type;
947: l_assignment_id ghr_pa_requests.employee_assignment_id%type;
940: l_retirement_plan ghr_pa_requests.retirement_plan%type;
941: l_pa_request_id ghr_pa_requests.pa_request_id%type;
942: l_start_date per_time_periods.start_date%type;
943: l_effective_date ghr_pa_requests.effective_date%type;
944: l_first_noa_code ghr_pa_requests.first_noa_code%type;
945: l_tenure ghr_pa_requests.tenure%type;
946: l_annuitant_indicator ghr_pa_requests.annuitant_indicator%type;
947: l_assignment_id ghr_pa_requests.employee_assignment_id%type;
948:
941: l_pa_request_id ghr_pa_requests.pa_request_id%type;
942: l_start_date per_time_periods.start_date%type;
943: l_effective_date ghr_pa_requests.effective_date%type;
944: l_first_noa_code ghr_pa_requests.first_noa_code%type;
945: l_tenure ghr_pa_requests.tenure%type;
946: l_annuitant_indicator ghr_pa_requests.annuitant_indicator%type;
947: l_assignment_id ghr_pa_requests.employee_assignment_id%type;
948:
949: l_health_plan ghr_pa_request_extra_info.rei_information5%type;
942: l_start_date per_time_periods.start_date%type;
943: l_effective_date ghr_pa_requests.effective_date%type;
944: l_first_noa_code ghr_pa_requests.first_noa_code%type;
945: l_tenure ghr_pa_requests.tenure%type;
946: l_annuitant_indicator ghr_pa_requests.annuitant_indicator%type;
947: l_assignment_id ghr_pa_requests.employee_assignment_id%type;
948:
949: l_health_plan ghr_pa_request_extra_info.rei_information5%type;
950: l_enrollment_option ghr_pa_request_extra_info.rei_information6%type;
943: l_effective_date ghr_pa_requests.effective_date%type;
944: l_first_noa_code ghr_pa_requests.first_noa_code%type;
945: l_tenure ghr_pa_requests.tenure%type;
946: l_annuitant_indicator ghr_pa_requests.annuitant_indicator%type;
947: l_assignment_id ghr_pa_requests.employee_assignment_id%type;
948:
949: l_health_plan ghr_pa_request_extra_info.rei_information5%type;
950: l_enrollment_option ghr_pa_request_extra_info.rei_information6%type;
951: l_date_temp_elig date;
948:
949: l_health_plan ghr_pa_request_extra_info.rei_information5%type;
950: l_enrollment_option ghr_pa_request_extra_info.rei_information6%type;
951: l_date_temp_elig date;
952: l_fegli ghr_pa_requests.fegli%type;
953: l_tsp_status ghr_pa_request_extra_info.rei_information15%type;
954: l_payroll_id ghr_pa_request_extra_info.rei_information3%type;
955: l_noa_family_code ghr_pa_requests.noa_family_code%type;
956: l_st_month VARCHAR2(20);
951: l_date_temp_elig date;
952: l_fegli ghr_pa_requests.fegli%type;
953: l_tsp_status ghr_pa_request_extra_info.rei_information15%type;
954: l_payroll_id ghr_pa_request_extra_info.rei_information3%type;
955: l_noa_family_code ghr_pa_requests.noa_family_code%type;
956: l_st_month VARCHAR2(20);
957: l_end_month VARCHAR2(20);
958: l_pay_month VARCHAR2(20);
959: