[Home] [Help]
220: ,p_information_type IN VARCHAR2
221: ,p_rei_rec OUT NOCOPY ghr_pa_request_extra_info%ROWTYPE) IS
222: CURSOR c_par IS
223: SELECT par.pa_request_id
224: FROM ghr_pa_requests par
225: CONNECT BY par.pa_request_id = prior par.altered_pa_request_id
226: START WITH par.pa_request_id = p_pa_request_id;
227: --
228: CURSOR cur_rei (cp_pa_request_id IN NUMBER) IS
803: CURSOR get_asg_id(p_request_id NUMBER,
804: p_person_id NUMBER)
805: IS
806: SELECT employee_assignment_id
807: FROM ghr_pa_requests
808: WHERE pa_request_id=p_request_id
809: AND person_id=p_person_id;
810: --
811: l_asg_id per_assignments_f.assignment_id%TYPE;
899: ,p_noa_id_corrected => l_noa_id
900: ,p_date_effective => p_effective_date
901: ,p_assignment_data => l_asgn_data);
902: --
903: ghr_pa_requests_pkg.get_SF52_loc_ddf_details (l_asgn_data.location_id
904: ,l_duty_station_id);
905: --
906: ghr_pa_requests_pkg.get_duty_station_details (l_duty_station_id
907: ,p_effective_date
902: --
903: ghr_pa_requests_pkg.get_SF52_loc_ddf_details (l_asgn_data.location_id
904: ,l_duty_station_id);
905: --
906: ghr_pa_requests_pkg.get_duty_station_details (l_duty_station_id
907: ,p_effective_date
908: ,l_duty_station_code
909: ,l_dummy_varchar);
910: p_from_duty_station_code := l_duty_station_code;
959: ,p_duty_station_location_id => l_location_id
960: ,p_pay_rate_determinant => p_from_pay_rate_determinant
961: ,p_work_schedule => p_from_work_schedule);
962: --
963: ghr_pa_requests_pkg.get_SF52_loc_ddf_details (l_location_id
964: ,l_duty_station_id);
965:
966: ghr_pa_requests_pkg.get_duty_station_details (l_duty_station_id
967: ,p_effective_date
962: --
963: ghr_pa_requests_pkg.get_SF52_loc_ddf_details (l_location_id
964: ,l_duty_station_id);
965:
966: ghr_pa_requests_pkg.get_duty_station_details (l_duty_station_id
967: ,p_effective_date
968: ,l_duty_station_code
969: ,l_dummy_varchar);
970: p_from_duty_station_code := l_duty_station_code;
988: ,p_from_national_identifier OUT NOCOPY VARCHAR2) IS
989: --
990: CURSOR cur_prev_ssn IS
991: SELECT par.employee_national_identifier prev_ssn
992: FROM ghr_pa_requests par
993: WHERE par.pa_request_id = p_altered_pa_request_id;
994: --
995: BEGIN
996: IF p_noa_family_code = 'CORRECT' THEN
2292: --
2293: l_info_type VARCHAR2(200) := NULL;
2294: l_api_assignment_id per_assignments.assignment_id%TYPE;
2295: --
2296: l_first_noa_id ghr_pa_requests.first_noa_id%TYPE;
2297: l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
2298: l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
2299: l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
2300: l_noa_family_code ghr_pa_requests.noa_family_code%TYPE;
2293: l_info_type VARCHAR2(200) := NULL;
2294: l_api_assignment_id per_assignments.assignment_id%TYPE;
2295: --
2296: l_first_noa_id ghr_pa_requests.first_noa_id%TYPE;
2297: l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
2298: l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
2299: l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
2300: l_noa_family_code ghr_pa_requests.noa_family_code%TYPE;
2301: l_multi_error_flag boolean;
2294: l_api_assignment_id per_assignments.assignment_id%TYPE;
2295: --
2296: l_first_noa_id ghr_pa_requests.first_noa_id%TYPE;
2297: l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
2298: l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
2299: l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
2300: l_noa_family_code ghr_pa_requests.noa_family_code%TYPE;
2301: l_multi_error_flag boolean;
2302: --
2295: --
2296: l_first_noa_id ghr_pa_requests.first_noa_id%TYPE;
2297: l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
2298: l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
2299: l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
2300: l_noa_family_code ghr_pa_requests.noa_family_code%TYPE;
2301: l_multi_error_flag boolean;
2302: --
2303: -- This cursor drives of the PAR table to first see which PA Requests had 'Update HR'
2296: l_first_noa_id ghr_pa_requests.first_noa_id%TYPE;
2297: l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
2298: l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
2299: l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
2300: l_noa_family_code ghr_pa_requests.noa_family_code%TYPE;
2301: l_multi_error_flag boolean;
2302: --
2303: -- This cursor drives of the PAR table to first see which PA Requests had 'Update HR'
2304: -- selected by a user in the given period
2303: -- This cursor drives of the PAR table to first see which PA Requests had 'Update HR'
2304: -- selected by a user in the given period
2305: -- may as well select everyting from the PA Request table (saves going back!)
2306:
2307: -- 3/13/02 -- Joined the ghr_pa_requests table with per_people_f table to view the
2308: -- records based on business group id/security group id
2309: -- Not added outer join for per_people_f because we are expecting existence of person_id
2310: -- in ghr_pa_requests for the actions with status in ('UPDATE_HR_COMPLETE','FUTURE_ACTION')
2311:
2306:
2307: -- 3/13/02 -- Joined the ghr_pa_requests table with per_people_f table to view the
2308: -- records based on business group id/security group id
2309: -- Not added outer join for per_people_f because we are expecting existence of person_id
2310: -- in ghr_pa_requests for the actions with status in ('UPDATE_HR_COMPLETE','FUTURE_ACTION')
2311:
2312: -- 24-OCT-2002 JH truncated sf50_approval_date because it does contain time on the db.
2313: -- which causes some rows to not be included on last day.
2314:
2315: -- Bug 4349372 changed per_people_f to per_all_people_f.
2316:
2317: CURSOR cur_get_pars IS
2318: SELECT par.*
2319: FROM ghr_pa_requests par,
2320: per_all_people_f per
2321: WHERE NVL(par.agency_code,par.from_agency_code) LIKE p_agency
2322: AND par.person_id = per.person_id
2323: AND trunc(par.sf50_approval_date) BETWEEN per.effective_start_date
2347: l_ghr_cpdf_temp_rec ghr_cpdf_temp%ROWTYPE;
2348: l_ghr_empty_cpdf_temp ghr_cpdf_temp%ROWTYPE;
2349: -- l_ghr_cpdf_cancel_rec ghr_cpdf_temp%ROWTYPE;
2350:
2351: l_ghr_pa_requests_rec ghr_pa_requests%ROWTYPE;
2352: l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2353: l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
2354: l_retained_grade_or_level ghr_pa_requests.to_grade_or_level%type;
2355: l_retained_step_or_rate ghr_pa_requests.to_step_or_rate%type;
2349: -- l_ghr_cpdf_cancel_rec ghr_cpdf_temp%ROWTYPE;
2350:
2351: l_ghr_pa_requests_rec ghr_pa_requests%ROWTYPE;
2352: l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2353: l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
2354: l_retained_grade_or_level ghr_pa_requests.to_grade_or_level%type;
2355: l_retained_step_or_rate ghr_pa_requests.to_step_or_rate%type;
2356: l_sf52_rec1 ghr_pa_requests%ROWTYPE;
2357: l_sf52_rec2 ghr_pa_requests%ROWTYPE;
2350:
2351: l_ghr_pa_requests_rec ghr_pa_requests%ROWTYPE;
2352: l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2353: l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
2354: l_retained_grade_or_level ghr_pa_requests.to_grade_or_level%type;
2355: l_retained_step_or_rate ghr_pa_requests.to_step_or_rate%type;
2356: l_sf52_rec1 ghr_pa_requests%ROWTYPE;
2357: l_sf52_rec2 ghr_pa_requests%ROWTYPE;
2358: l_dual_flg BOOLEAN:=FALSE;
2351: l_ghr_pa_requests_rec ghr_pa_requests%ROWTYPE;
2352: l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2353: l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
2354: l_retained_grade_or_level ghr_pa_requests.to_grade_or_level%type;
2355: l_retained_step_or_rate ghr_pa_requests.to_step_or_rate%type;
2356: l_sf52_rec1 ghr_pa_requests%ROWTYPE;
2357: l_sf52_rec2 ghr_pa_requests%ROWTYPE;
2358: l_dual_flg BOOLEAN:=FALSE;
2359: l_single_flg BOOLEAN:=TRUE;
2352: l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2353: l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
2354: l_retained_grade_or_level ghr_pa_requests.to_grade_or_level%type;
2355: l_retained_step_or_rate ghr_pa_requests.to_step_or_rate%type;
2356: l_sf52_rec1 ghr_pa_requests%ROWTYPE;
2357: l_sf52_rec2 ghr_pa_requests%ROWTYPE;
2358: l_dual_flg BOOLEAN:=FALSE;
2359: l_single_flg BOOLEAN:=TRUE;
2360: l_loop NUMBER :=1;
2353: l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
2354: l_retained_grade_or_level ghr_pa_requests.to_grade_or_level%type;
2355: l_retained_step_or_rate ghr_pa_requests.to_step_or_rate%type;
2356: l_sf52_rec1 ghr_pa_requests%ROWTYPE;
2357: l_sf52_rec2 ghr_pa_requests%ROWTYPE;
2358: l_dual_flg BOOLEAN:=FALSE;
2359: l_single_flg BOOLEAN:=TRUE;
2360: l_loop NUMBER :=1;
2361: l_index NUMBER :=1;
2371: -- the following cursor can be used for dual purposes
2372: -- can pass the exact eff date or the eff_date-1 to pick the prior details
2373: --
2374: CURSOR cur_per_details( p_person_id per_all_people_f.person_id%type,
2375: p_eff_date ghr_pa_requests.effective_date%TYPE)
2376: IS
2377: SELECT last_name, middle_names, first_name,title,business_group_id
2378: FROM per_all_people_f
2379: WHERE person_id = p_person_id
2380: AND p_eff_date between effective_start_date and effective_end_date;
2381:
2382: -- Bug 5010844
2383: -- Cursor for getting authorization person details
2384: CURSOR cur_approver_id(c_pa_request_id ghr_pa_requests.pa_request_id%type)
2385: IS
2386: SELECT user_name_employee_id approver_id
2387: FROM ghr_pa_routing_history
2388: WHERE pa_request_id = c_pa_request_id
2402: --
2403: -- This cursor can be used to pick the prior num, org details of that pos
2404: --
2405: CURSOR cur_prior_pos_org(p_position_id hr_positions_f.position_id%TYPE,
2406: p_eff_date ghr_pa_requests.effective_date%TYPE)
2407: IS
2408: SELECT name
2409: FROM hr_organization_units
2410: WHERE organization_id = ( SELECT organization_id
2412: WHERE position_id=p_position_id
2413: AND p_eff_date between effective_start_date and effective_end_date);
2414: --
2415: CURSOR cur_pos_org(p_org_id hr_organization_units.organization_id%TYPE,
2416: p_eff_date ghr_pa_requests.effective_date%TYPE)
2417: IS
2418: SELECT name
2419: FROM hr_organization_units
2420: WHERE organization_id = p_org_id
2421: AND p_eff_date between date_from and NVL(date_to,to_Date('31/12/4712','DD/MM/YYYY'));
2422: --
2423: -- added date cond and other fields for ENW EHRI changes
2424: --
2425: CURSOR cur_scd_dates(p_pa_request_id ghr_pa_requests.pa_request_id%type)
2426: IS
2427: SELECT REI_INFORMATION3 rif ,REI_INFORMATION8 ret
2428: FROM ghr_pa_request_extra_info parei
2429: WHERE parei.pa_request_id=p_pa_request_id
2429: WHERE parei.pa_request_id=p_pa_request_id
2430: AND parei.information_type='GHR_US_PAR_CHG_SCD';
2431:
2432: --BUG# 6458070 -- ssn corrected issue
2433: cursor cur_ssn_corr(p_altered_pa_request_id ghr_pa_requests.altered_pa_request_id%type,
2434: p_to_national_identifier ghr_pa_requests.employee_national_identifier%type)
2435: is
2436: SELECT employee_national_identifier
2437: FROM ghr_pa_requests
2430: AND parei.information_type='GHR_US_PAR_CHG_SCD';
2431:
2432: --BUG# 6458070 -- ssn corrected issue
2433: cursor cur_ssn_corr(p_altered_pa_request_id ghr_pa_requests.altered_pa_request_id%type,
2434: p_to_national_identifier ghr_pa_requests.employee_national_identifier%type)
2435: is
2436: SELECT employee_national_identifier
2437: FROM ghr_pa_requests
2438: WHERE pa_request_id = p_altered_pa_request_id
2433: cursor cur_ssn_corr(p_altered_pa_request_id ghr_pa_requests.altered_pa_request_id%type,
2434: p_to_national_identifier ghr_pa_requests.employee_national_identifier%type)
2435: is
2436: SELECT employee_national_identifier
2437: FROM ghr_pa_requests
2438: WHERE pa_request_id = p_altered_pa_request_id
2439: AND employee_national_identifier <> p_to_national_identifier;
2440:
2441: l_records_found BOOLEAN;
2449: l_last_name per_all_people.last_name%type;
2450: l_suffix ghr_cpdf_temp.prior_name_suffix%type;
2451:
2452: -- For Dual Actions PRD is becoming null so preserving it using a local variable.
2453: l_pay_rate_determinant ghr_pa_requests.pay_rate_determinant%TYPE;
2454: --
2455: CURSOR cur_rem(p_pa_request_id ghr_pa_requests.pa_request_id%TYPE)
2456: IS
2457: SELECT Description
2451:
2452: -- For Dual Actions PRD is becoming null so preserving it using a local variable.
2453: l_pay_rate_determinant ghr_pa_requests.pay_rate_determinant%TYPE;
2454: --
2455: CURSOR cur_rem(p_pa_request_id ghr_pa_requests.pa_request_id%TYPE)
2456: IS
2457: SELECT Description
2458: FROM ghr_pa_remarks
2459: WHERE pa_request_id=p_pa_request_id;
2479:
2480: FOR cur_get_pars_rec IN cur_get_pars
2481: LOOP
2482: -- 1) Get PA Request data
2483: l_ghr_pa_requests_rec := cur_get_pars_rec;
2484:
2485:
2486: l_sf52_rec1 := l_ghr_pa_requests_rec;
2487: l_sf52_rec2 := l_ghr_pa_requests_rec;
2482: -- 1) Get PA Request data
2483: l_ghr_pa_requests_rec := cur_get_pars_rec;
2484:
2485:
2486: l_sf52_rec1 := l_ghr_pa_requests_rec;
2487: l_sf52_rec2 := l_ghr_pa_requests_rec;
2488:
2489: IF l_ghr_pa_requests_rec.second_noa_code IS NOT NULL
2490: AND l_ghr_pa_requests_rec.first_noa_code NOT IN ('001','002') THEN
2483: l_ghr_pa_requests_rec := cur_get_pars_rec;
2484:
2485:
2486: l_sf52_rec1 := l_ghr_pa_requests_rec;
2487: l_sf52_rec2 := l_ghr_pa_requests_rec;
2488:
2489: IF l_ghr_pa_requests_rec.second_noa_code IS NOT NULL
2490: AND l_ghr_pa_requests_rec.first_noa_code NOT IN ('001','002') THEN
2491: l_loop := 2;
2485:
2486: l_sf52_rec1 := l_ghr_pa_requests_rec;
2487: l_sf52_rec2 := l_ghr_pa_requests_rec;
2488:
2489: IF l_ghr_pa_requests_rec.second_noa_code IS NOT NULL
2490: AND l_ghr_pa_requests_rec.first_noa_code NOT IN ('001','002') THEN
2491: l_loop := 2;
2492: l_dual_flg := TRUE;
2493: l_single_flg := FALSE;
2486: l_sf52_rec1 := l_ghr_pa_requests_rec;
2487: l_sf52_rec2 := l_ghr_pa_requests_rec;
2488:
2489: IF l_ghr_pa_requests_rec.second_noa_code IS NOT NULL
2490: AND l_ghr_pa_requests_rec.first_noa_code NOT IN ('001','002') THEN
2491: l_loop := 2;
2492: l_dual_flg := TRUE;
2493: l_single_flg := FALSE;
2494: ELSE
2496: l_single_flg := TRUE;
2497: l_dual_flg := FALSE;
2498: END IF;
2499:
2500: /* If ( l_ghr_pa_requests_rec.first_noa_code like '3%'and
2501: l_ghr_pa_requests_rec.second_noa_code ='825' ) THEN
2502: l_loop := 2;
2503: l_dual_flg := TRUE;
2504: l_single_flg := FALSE;
2497: l_dual_flg := FALSE;
2498: END IF;
2499:
2500: /* If ( l_ghr_pa_requests_rec.first_noa_code like '3%'and
2501: l_ghr_pa_requests_rec.second_noa_code ='825' ) THEN
2502: l_loop := 2;
2503: l_dual_flg := TRUE;
2504: l_single_flg := FALSE;
2505: ELSE
2518: IF ( l_dual_flg = TRUE and l_index = 1 ) then
2519:
2520: ghr_process_sf52.assign_new_rg( p_action_num => 1,
2521: p_pa_req => l_sf52_rec1);
2522: l_ghr_pa_requests_rec := l_sf52_rec1;
2523: ELSIF ( l_dual_flg = TRUE and l_index = 2 ) then
2524: -- In case of Dual Actin assign_new_rg is nulling out the PRD.
2525: l_pay_rate_determinant := l_ghr_pa_requests_rec.pay_rate_determinant;
2526: ghr_process_sf52.assign_new_rg( p_action_num => 2,
2521: p_pa_req => l_sf52_rec1);
2522: l_ghr_pa_requests_rec := l_sf52_rec1;
2523: ELSIF ( l_dual_flg = TRUE and l_index = 2 ) then
2524: -- In case of Dual Actin assign_new_rg is nulling out the PRD.
2525: l_pay_rate_determinant := l_ghr_pa_requests_rec.pay_rate_determinant;
2526: ghr_process_sf52.assign_new_rg( p_action_num => 2,
2527: p_pa_req => l_sf52_rec2);
2528:
2529: l_ghr_pa_requests_rec := l_sf52_rec2;
2525: l_pay_rate_determinant := l_ghr_pa_requests_rec.pay_rate_determinant;
2526: ghr_process_sf52.assign_new_rg( p_action_num => 2,
2527: p_pa_req => l_sf52_rec2);
2528:
2529: l_ghr_pa_requests_rec := l_sf52_rec2;
2530: l_ghr_pa_requests_rec.pay_rate_determinant := l_pay_rate_determinant;
2531: l_dual_flg := FALSE;
2532: ELSIF (l_single_flg = TRUE and l_dual_flg <> TRUE ) THEN
2533: l_ghr_pa_requests_rec := cur_get_pars_rec;
2526: ghr_process_sf52.assign_new_rg( p_action_num => 2,
2527: p_pa_req => l_sf52_rec2);
2528:
2529: l_ghr_pa_requests_rec := l_sf52_rec2;
2530: l_ghr_pa_requests_rec.pay_rate_determinant := l_pay_rate_determinant;
2531: l_dual_flg := FALSE;
2532: ELSIF (l_single_flg = TRUE and l_dual_flg <> TRUE ) THEN
2533: l_ghr_pa_requests_rec := cur_get_pars_rec;
2534: END IF;
2529: l_ghr_pa_requests_rec := l_sf52_rec2;
2530: l_ghr_pa_requests_rec.pay_rate_determinant := l_pay_rate_determinant;
2531: l_dual_flg := FALSE;
2532: ELSIF (l_single_flg = TRUE and l_dual_flg <> TRUE ) THEN
2533: l_ghr_pa_requests_rec := cur_get_pars_rec;
2534: END IF;
2535:
2536: -- Bug# 4648811 getting the suffix from the lastname and also removing suffix from lastname
2537: get_suffix_lname(l_ghr_pa_requests_rec.employee_last_name,
2533: l_ghr_pa_requests_rec := cur_get_pars_rec;
2534: END IF;
2535:
2536: -- Bug# 4648811 getting the suffix from the lastname and also removing suffix from lastname
2537: get_suffix_lname(l_ghr_pa_requests_rec.employee_last_name,
2538: l_ghr_pa_requests_rec.effective_date,
2539: l_suffix,
2540: l_last_name);
2541:
2534: END IF;
2535:
2536: -- Bug# 4648811 getting the suffix from the lastname and also removing suffix from lastname
2537: get_suffix_lname(l_ghr_pa_requests_rec.employee_last_name,
2538: l_ghr_pa_requests_rec.effective_date,
2539: l_suffix,
2540: l_last_name);
2541:
2542: l_ghr_pa_requests_rec.employee_last_name := l_last_name;
2538: l_ghr_pa_requests_rec.effective_date,
2539: l_suffix,
2540: l_last_name);
2541:
2542: l_ghr_pa_requests_rec.employee_last_name := l_last_name;
2543: --End Bug# 4648811
2544:
2545: hr_utility.set_location(l_proc||' Check non_us_citizen_and_foreign_ds' ,30);
2546: --
2544:
2545: hr_utility.set_location(l_proc||' Check non_us_citizen_and_foreign_ds' ,30);
2546: --
2547: -- 2.1) Do not include PAR's for a non US Citizen in a foreign country
2548: IF non_us_citizen_and_foreign_ds (p_citizenship => l_ghr_pa_requests_rec.citizenship
2549: ,p_duty_station_code => l_ghr_pa_requests_rec.duty_station_code) THEN
2550: GOTO end_par_loop; -- loop for the next one!
2551: END IF;
2552: --
2545: hr_utility.set_location(l_proc||' Check non_us_citizen_and_foreign_ds' ,30);
2546: --
2547: -- 2.1) Do not include PAR's for a non US Citizen in a foreign country
2548: IF non_us_citizen_and_foreign_ds (p_citizenship => l_ghr_pa_requests_rec.citizenship
2549: ,p_duty_station_code => l_ghr_pa_requests_rec.duty_station_code) THEN
2550: GOTO end_par_loop; -- loop for the next one!
2551: END IF;
2552: --
2553: hr_utility.set_location(l_proc||' Customer exclusion hook' ,40);
2552: --
2553: hr_utility.set_location(l_proc||' Customer exclusion hook' ,40);
2554: --
2555: -- Bug 714944 -- Added exclusion of NAF:
2556: IF exclude_position (p_position_id => NVL(l_ghr_pa_requests_rec.to_position_id
2557: ,l_ghr_pa_requests_rec.from_position_id)
2558: ,p_effective_date => l_ghr_pa_requests_rec.effective_date) THEN
2559: GOTO end_par_loop; -- loop for the next one!
2560: END IF;
2553: hr_utility.set_location(l_proc||' Customer exclusion hook' ,40);
2554: --
2555: -- Bug 714944 -- Added exclusion of NAF:
2556: IF exclude_position (p_position_id => NVL(l_ghr_pa_requests_rec.to_position_id
2557: ,l_ghr_pa_requests_rec.from_position_id)
2558: ,p_effective_date => l_ghr_pa_requests_rec.effective_date) THEN
2559: GOTO end_par_loop; -- loop for the next one!
2560: END IF;
2561:
2554: --
2555: -- Bug 714944 -- Added exclusion of NAF:
2556: IF exclude_position (p_position_id => NVL(l_ghr_pa_requests_rec.to_position_id
2557: ,l_ghr_pa_requests_rec.from_position_id)
2558: ,p_effective_date => l_ghr_pa_requests_rec.effective_date) THEN
2559: GOTO end_par_loop; -- loop for the next one!
2560: END IF;
2561:
2562: -- Obtain Retained Grade information
2565: l_retained_pay_plan := NULL;
2566: l_retained_grade_or_level := NULL;
2567: l_retained_step_or_rate := NULL;
2568: l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details (
2569: p_person_id => l_ghr_pa_requests_rec.person_id,
2570: p_effective_date => l_ghr_pa_requests_rec.effective_date
2571: );
2572:
2573: l_retained_pay_plan := l_retained_grade_rec.pay_plan;
2566: l_retained_grade_or_level := NULL;
2567: l_retained_step_or_rate := NULL;
2568: l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details (
2569: p_person_id => l_ghr_pa_requests_rec.person_id,
2570: p_effective_date => l_ghr_pa_requests_rec.effective_date
2571: );
2572:
2573: l_retained_pay_plan := l_retained_grade_rec.pay_plan;
2574: l_retained_grade_or_level := l_retained_grade_rec.grade_or_level;
2595: -- 3.1) Get all info from PAR table itself
2596: --
2597: hr_utility.set_location(l_proc||' populate cpdf temp from par' ,50);
2598: --
2599: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
2600: -- Agency Subelement Code
2601: l_ghr_cpdf_temp_rec.to_national_identifier := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
2602: -- SSN
2603: -- SSN corrected
2597: hr_utility.set_location(l_proc||' populate cpdf temp from par' ,50);
2598: --
2599: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
2600: -- Agency Subelement Code
2601: l_ghr_cpdf_temp_rec.to_national_identifier := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
2602: -- SSN
2603: -- SSN corrected
2604: IF (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2605: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '1%') THEN
2600: -- Agency Subelement Code
2601: l_ghr_cpdf_temp_rec.to_national_identifier := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
2602: -- SSN
2603: -- SSN corrected
2604: IF (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2605: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '1%') THEN
2606: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2607: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2608: for cur_ssn_corr_rec in cur_ssn_corr(l_ghr_pa_requests_rec.altered_pa_request_id,
2601: l_ghr_cpdf_temp_rec.to_national_identifier := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
2602: -- SSN
2603: -- SSN corrected
2604: IF (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2605: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '1%') THEN
2606: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2607: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2608: for cur_ssn_corr_rec in cur_ssn_corr(l_ghr_pa_requests_rec.altered_pa_request_id,
2609: l_ghr_pa_requests_rec.employee_national_identifier)
2604: IF (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2605: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '1%') THEN
2606: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2607: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2608: for cur_ssn_corr_rec in cur_ssn_corr(l_ghr_pa_requests_rec.altered_pa_request_id,
2609: l_ghr_pa_requests_rec.employee_national_identifier)
2610: loop
2611: l_ghr_cpdf_temp_rec.SSN_CORRECTED := format_ni(cur_ssn_corr_rec.employee_national_identifier);
2612: end loop;
2605: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '1%') THEN
2606: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2607: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2608: for cur_ssn_corr_rec in cur_ssn_corr(l_ghr_pa_requests_rec.altered_pa_request_id,
2609: l_ghr_pa_requests_rec.employee_national_identifier)
2610: loop
2611: l_ghr_cpdf_temp_rec.SSN_CORRECTED := format_ni(cur_ssn_corr_rec.employee_national_identifier);
2612: end loop;
2613: END IF;
2612: end loop;
2613: END IF;
2614: --
2615:
2616: IF (l_ghr_pa_requests_rec.first_noa_code = '001' AND
2617: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '350') OR
2618: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2619: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2620: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2613: END IF;
2614: --
2615:
2616: IF (l_ghr_pa_requests_rec.first_noa_code = '001' AND
2617: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '350') OR
2618: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2619: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2620: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2621: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2614: --
2615:
2616: IF (l_ghr_pa_requests_rec.first_noa_code = '001' AND
2617: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '350') OR
2618: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2619: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2620: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2621: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2622: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2615:
2616: IF (l_ghr_pa_requests_rec.first_noa_code = '001' AND
2617: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '350') OR
2618: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2619: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2620: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2621: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2622: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2623: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2616: IF (l_ghr_pa_requests_rec.first_noa_code = '001' AND
2617: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '350') OR
2618: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2619: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2620: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2621: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2622: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2623: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2624: ELSE
2617: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '350') OR
2618: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2619: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2620: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2621: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2622: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2623: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2624: ELSE
2625: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2618: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2619: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2620: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2621: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2622: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2623: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2624: ELSE
2625: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2626: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2619: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2620: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2621: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2622: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2623: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2624: ELSE
2625: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2626: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2627: END IF;
2621: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2622: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2623: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2624: ELSE
2625: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2626: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2627: END IF;
2628: END IF;
2629:
2622: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2623: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2624: ELSE
2625: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2626: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2627: END IF;
2628: END IF;
2629:
2630: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date; -- format in report
2626: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2627: END IF;
2628: END IF;
2629:
2630: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date; -- format in report
2631: l_ghr_cpdf_temp_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
2632: l_ghr_cpdf_temp_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);-- Moved here for bug# 1399854
2633:
2634: -- All NOACS need to report details regarding the approval date, authorizer details etc.
2627: END IF;
2628: END IF;
2629:
2630: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date; -- format in report
2631: l_ghr_cpdf_temp_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
2632: l_ghr_cpdf_temp_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);-- Moved here for bug# 1399854
2633:
2634: -- All NOACS need to report details regarding the approval date, authorizer details etc.
2635: l_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE := l_ghr_pa_requests_rec.APPROVAL_DATE;
2628: END IF;
2629:
2630: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date; -- format in report
2631: l_ghr_cpdf_temp_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
2632: l_ghr_cpdf_temp_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);-- Moved here for bug# 1399854
2633:
2634: -- All NOACS need to report details regarding the approval date, authorizer details etc.
2635: l_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE := l_ghr_pa_requests_rec.APPROVAL_DATE;
2636:
2631: l_ghr_cpdf_temp_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
2632: l_ghr_cpdf_temp_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);-- Moved here for bug# 1399854
2633:
2634: -- All NOACS need to report details regarding the approval date, authorizer details etc.
2635: l_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE := l_ghr_pa_requests_rec.APPROVAL_DATE;
2636:
2637: -- 5010844
2638: -- Setting l_approver_id to NULL to find the proper approver's name.
2639: l_approver_id := NULL;
2636:
2637: -- 5010844
2638: -- Setting l_approver_id to NULL to find the proper approver's name.
2639: l_approver_id := NULL;
2640: FOR l_cur_auth_id IN cur_approver_id(l_ghr_pa_requests_rec.pa_request_id) LOOP
2641: l_approver_id := l_cur_auth_id.approver_id;
2642: END LOOP;
2643: -- Bug#5209089 If Approver's ID is NULL in the pa_routing, the action may be a mass action.
2644: -- For mass actions pick the approver's ID from the approver name attached to the Personnel Office.
2642: END LOOP;
2643: -- Bug#5209089 If Approver's ID is NULL in the pa_routing, the action may be a mass action.
2644: -- For mass actions pick the approver's ID from the approver name attached to the Personnel Office.
2645: IF l_approver_id IS NULL THEN
2646: FOR l_approver_rec IN cur_poid_approver(l_ghr_pa_requests_rec.personnel_office_id)
2647: LOOP
2648: l_approver_id := l_approver_rec.person_id;
2649: END LOOP;
2650: END IF;
2649: END LOOP;
2650: END IF;
2651:
2652: FOR auth_det IN cur_per_details(l_approver_id,
2653: l_ghr_pa_requests_rec.effective_date)
2654: LOOP
2655: -- End Bug 5010844
2656: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
2657: get_suffix_lname(auth_det.last_name,
2654: LOOP
2655: -- End Bug 5010844
2656: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
2657: get_suffix_lname(auth_det.last_name,
2658: l_ghr_pa_requests_rec.effective_date,
2659: l_suffix,
2660: l_last_name);
2661: l_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME := l_last_name;
2662: l_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME := auth_det.first_name;
2666:
2667:
2668: END LOOP;
2669:
2670: l_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE := l_ghr_pa_requests_rec.sf50_approving_ofcl_work_title;
2671:
2672: -- Restricting Remarks to 2000 characters.
2673: FOR rem_rec IN cur_rem(l_ghr_pa_requests_rec.pa_request_id) LOOP
2674: l_ghr_cpdf_temp_rec.remarks_text := SUBSTR(l_ghr_cpdf_temp_rec.remarks_text||rem_rec.description,1,2000);
2669:
2670: l_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE := l_ghr_pa_requests_rec.sf50_approving_ofcl_work_title;
2671:
2672: -- Restricting Remarks to 2000 characters.
2673: FOR rem_rec IN cur_rem(l_ghr_pa_requests_rec.pa_request_id) LOOP
2674: l_ghr_cpdf_temp_rec.remarks_text := SUBSTR(l_ghr_cpdf_temp_rec.remarks_text||rem_rec.description,1,2000);
2675: IF LENGTH(l_ghr_cpdf_temp_rec.remarks_text) = 2000 THEN
2676: EXIT;
2677: END IF;
2688: -- Insert_row in GHR_CPDF_TEMP, and continue in the LOOP for the next PAR row.
2689: --- EHRI_EMPLOYEE_ID IS TO BE REPORTED FOR ALL THE EMPLOYEES AND FOR ALL RPA's
2690: --- esp for 001 action
2691: BEGIN
2692: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
2693: ,l_ghr_pa_requests_rec.first_noa_id
2694: ,l_ghr_pa_requests_rec.second_noa_id
2695: ,l_ghr_pa_requests_rec.noa_family_code
2696: ,l_ghr_pa_requests_rec.person_id
2689: --- EHRI_EMPLOYEE_ID IS TO BE REPORTED FOR ALL THE EMPLOYEES AND FOR ALL RPA's
2690: --- esp for 001 action
2691: BEGIN
2692: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
2693: ,l_ghr_pa_requests_rec.first_noa_id
2694: ,l_ghr_pa_requests_rec.second_noa_id
2695: ,l_ghr_pa_requests_rec.noa_family_code
2696: ,l_ghr_pa_requests_rec.person_id
2697: ,l_ghr_pa_requests_rec.effective_date
2690: --- esp for 001 action
2691: BEGIN
2692: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
2693: ,l_ghr_pa_requests_rec.first_noa_id
2694: ,l_ghr_pa_requests_rec.second_noa_id
2695: ,l_ghr_pa_requests_rec.noa_family_code
2696: ,l_ghr_pa_requests_rec.person_id
2697: ,l_ghr_pa_requests_rec.effective_date
2698: ,l_dummy
2691: BEGIN
2692: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
2693: ,l_ghr_pa_requests_rec.first_noa_id
2694: ,l_ghr_pa_requests_rec.second_noa_id
2695: ,l_ghr_pa_requests_rec.noa_family_code
2696: ,l_ghr_pa_requests_rec.person_id
2697: ,l_ghr_pa_requests_rec.effective_date
2698: ,l_dummy
2699: ,l_dummy
2692: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
2693: ,l_ghr_pa_requests_rec.first_noa_id
2694: ,l_ghr_pa_requests_rec.second_noa_id
2695: ,l_ghr_pa_requests_rec.noa_family_code
2696: ,l_ghr_pa_requests_rec.person_id
2697: ,l_ghr_pa_requests_rec.effective_date
2698: ,l_dummy
2699: ,l_dummy
2700: ,l_dummy
2693: ,l_ghr_pa_requests_rec.first_noa_id
2694: ,l_ghr_pa_requests_rec.second_noa_id
2695: ,l_ghr_pa_requests_rec.noa_family_code
2696: ,l_ghr_pa_requests_rec.person_id
2697: ,l_ghr_pa_requests_rec.effective_date
2698: ,l_dummy
2699: ,l_dummy
2700: ,l_dummy
2701: ,l_dummy
2733: EXCEPTION
2734: WHEN OTHERS THEN
2735: l_message_name := 'get_par_ei_noac';
2736: l_log_text := 'Error in fetching EHRI Employee id for pa_request_id: '||
2737: l_ghr_pa_requests_rec.pa_request_id ||
2738: ' ; SSN/employee last name' ||
2739: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2740: l_ghr_pa_Requests_rec.employee_last_name ||
2741: ' ; first NOAC/Second NOAC: '||
2735: l_message_name := 'get_par_ei_noac';
2736: l_log_text := 'Error in fetching EHRI Employee id for pa_request_id: '||
2737: l_ghr_pa_requests_rec.pa_request_id ||
2738: ' ; SSN/employee last name' ||
2739: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2740: l_ghr_pa_Requests_rec.employee_last_name ||
2741: ' ; first NOAC/Second NOAC: '||
2742: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2743: l_ghr_pa_requests_rec.second_noa_code ||
2736: l_log_text := 'Error in fetching EHRI Employee id for pa_request_id: '||
2737: l_ghr_pa_requests_rec.pa_request_id ||
2738: ' ; SSN/employee last name' ||
2739: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2740: l_ghr_pa_Requests_rec.employee_last_name ||
2741: ' ; first NOAC/Second NOAC: '||
2742: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2743: l_ghr_pa_requests_rec.second_noa_code ||
2744: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2738: ' ; SSN/employee last name' ||
2739: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2740: l_ghr_pa_Requests_rec.employee_last_name ||
2741: ' ; first NOAC/Second NOAC: '||
2742: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2743: l_ghr_pa_requests_rec.second_noa_code ||
2744: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2745:
2746: Raise EHRI_DYNRPT_ERROR;
2739: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2740: l_ghr_pa_Requests_rec.employee_last_name ||
2741: ' ; first NOAC/Second NOAC: '||
2742: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2743: l_ghr_pa_requests_rec.second_noa_code ||
2744: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2745:
2746: Raise EHRI_DYNRPT_ERROR;
2747: END;
2747: END;
2748:
2749: -- Bug 5063301
2750: --Birth Date
2751: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
2752: -- End Bug 5063301
2753:
2754: IF l_ghr_pa_requests_rec.first_noa_code = '001' THEN
2755: insert_row(l_ghr_cpdf_temp_rec);
2750: --Birth Date
2751: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
2752: -- End Bug 5063301
2753:
2754: IF l_ghr_pa_requests_rec.first_noa_code = '001' THEN
2755: insert_row(l_ghr_cpdf_temp_rec);
2756: l_records_found:=TRUE;
2757: GOTO end_par_loop; -- loop for the next one!
2758: END IF;
2757: GOTO end_par_loop; -- loop for the next one!
2758: END IF;
2759:
2760: -- Obtain Family Code
2761: l_noa_family_code := l_ghr_pa_requests_rec.noa_family_code;
2762: IF l_noa_family_code = 'CORRECT' THEN
2763: -- Bug#2789704 Added Exception Handling
2764: -- Bug#5172710 Modified the function to determine the noa family code.
2765: BEGIN
2762: IF l_noa_family_code = 'CORRECT' THEN
2763: -- Bug#2789704 Added Exception Handling
2764: -- Bug#5172710 Modified the function to determine the noa family code.
2765: BEGIN
2766: l_noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family
2767: (l_ghr_pa_requests_rec.second_noa_id,
2768: l_ghr_pa_requests_rec.effective_date);
2769: EXCEPTION
2770: WHEN OTHERS THEN
2763: -- Bug#2789704 Added Exception Handling
2764: -- Bug#5172710 Modified the function to determine the noa family code.
2765: BEGIN
2766: l_noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family
2767: (l_ghr_pa_requests_rec.second_noa_id,
2768: l_ghr_pa_requests_rec.effective_date);
2769: EXCEPTION
2770: WHEN OTHERS THEN
2771: l_message_name := 'get_family_code';
2764: -- Bug#5172710 Modified the function to determine the noa family code.
2765: BEGIN
2766: l_noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family
2767: (l_ghr_pa_requests_rec.second_noa_id,
2768: l_ghr_pa_requests_rec.effective_date);
2769: EXCEPTION
2770: WHEN OTHERS THEN
2771: l_message_name := 'get_family_code';
2772: l_log_text := 'Error in getting family code for pa_request_id: '||
2769: EXCEPTION
2770: WHEN OTHERS THEN
2771: l_message_name := 'get_family_code';
2772: l_log_text := 'Error in getting family code for pa_request_id: '||
2773: l_ghr_pa_requests_rec.pa_request_id ||
2774: ' ; SSN/employee last name' ||
2775: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2776: l_ghr_pa_Requests_rec.employee_last_name ||
2777: ' ; first NOAC/Second NOAC: '||
2771: l_message_name := 'get_family_code';
2772: l_log_text := 'Error in getting family code for pa_request_id: '||
2773: l_ghr_pa_requests_rec.pa_request_id ||
2774: ' ; SSN/employee last name' ||
2775: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2776: l_ghr_pa_Requests_rec.employee_last_name ||
2777: ' ; first NOAC/Second NOAC: '||
2778: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2779: l_ghr_pa_requests_rec.second_noa_code ||
2772: l_log_text := 'Error in getting family code for pa_request_id: '||
2773: l_ghr_pa_requests_rec.pa_request_id ||
2774: ' ; SSN/employee last name' ||
2775: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2776: l_ghr_pa_Requests_rec.employee_last_name ||
2777: ' ; first NOAC/Second NOAC: '||
2778: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2779: l_ghr_pa_requests_rec.second_noa_code ||
2780: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2774: ' ; SSN/employee last name' ||
2775: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2776: l_ghr_pa_Requests_rec.employee_last_name ||
2777: ' ; first NOAC/Second NOAC: '||
2778: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2779: l_ghr_pa_requests_rec.second_noa_code ||
2780: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2781:
2782: Raise EHRI_DYNRPT_ERROR;
2775: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2776: l_ghr_pa_Requests_rec.employee_last_name ||
2777: ' ; first NOAC/Second NOAC: '||
2778: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2779: l_ghr_pa_requests_rec.second_noa_code ||
2780: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2781:
2782: Raise EHRI_DYNRPT_ERROR;
2783: END;
2784: -- Bug#2789704 Added Exception Handling
2785: END IF;
2786:
2787: -- Moved POI to this place for bug# 1402287 to not print for Cancellations.
2788: l_ghr_cpdf_temp_rec.personnel_office_id := l_ghr_pa_requests_rec.personnel_office_id;
2789: -- Personnel Office Identifier Code
2790: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
2791: --Birth Date
2792: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
2786:
2787: -- Moved POI to this place for bug# 1402287 to not print for Cancellations.
2788: l_ghr_cpdf_temp_rec.personnel_office_id := l_ghr_pa_requests_rec.personnel_office_id;
2789: -- Personnel Office Identifier Code
2790: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
2791: --Birth Date
2792: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
2793: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
2794: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
2788: l_ghr_cpdf_temp_rec.personnel_office_id := l_ghr_pa_requests_rec.personnel_office_id;
2789: -- Personnel Office Identifier Code
2790: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
2791: --Birth Date
2792: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
2793: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
2794: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
2795: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
2796: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
2789: -- Personnel Office Identifier Code
2790: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
2791: --Birth Date
2792: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
2793: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
2794: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
2795: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
2796: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
2797: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
2790: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
2791: --Birth Date
2792: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
2793: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
2794: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
2795: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
2796: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
2797: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
2798: -- Retirement System Type Code (retierment plan)
2791: --Birth Date
2792: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
2793: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
2794: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
2795: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
2796: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
2797: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
2798: -- Retirement System Type Code (retierment plan)
2799:
2792: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
2793: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
2794: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
2795: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
2796: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
2797: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
2798: -- Retirement System Type Code (retierment plan)
2799:
2800:
2793: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
2794: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
2795: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
2796: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
2797: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
2798: -- Retirement System Type Code (retierment plan)
2799:
2800:
2801: --Start of Bug #6158983
2800:
2801: --Start of Bug #6158983
2802: --start of Bug #6522440 adding one more validation of showing the hyp_full_reg_duty_part_emp
2803: -- only for part time employees
2804: if l_ghr_pa_requests_rec.part_time_hours is not null then
2805: if l_ghr_cpdf_temp_rec.retirement_plan in ('E','M','T') then
2806: l_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp := 72.00;
2807: else
2808: l_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp := 80.00;
2813:
2814:
2815:
2816:
2817: l_ghr_cpdf_temp_rec.veterans_status := l_ghr_pa_requests_rec.veterans_status;
2818: l_ghr_cpdf_temp_rec.FLSA_category := l_ghr_pa_requests_rec.FLSA_category;
2819:
2820: IF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF in ('P','R') ) THEN
2821: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'Y';
2814:
2815:
2816:
2817: l_ghr_cpdf_temp_rec.veterans_status := l_ghr_pa_requests_rec.veterans_status;
2818: l_ghr_cpdf_temp_rec.FLSA_category := l_ghr_pa_requests_rec.FLSA_category;
2819:
2820: IF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF in ('P','R') ) THEN
2821: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'Y';
2822: ELSIF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF = 'N') THEN
2816:
2817: l_ghr_cpdf_temp_rec.veterans_status := l_ghr_pa_requests_rec.veterans_status;
2818: l_ghr_cpdf_temp_rec.FLSA_category := l_ghr_pa_requests_rec.FLSA_category;
2819:
2820: IF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF in ('P','R') ) THEN
2821: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'Y';
2822: ELSIF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF = 'N') THEN
2823: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'N';
2824: ELSE
2818: l_ghr_cpdf_temp_rec.FLSA_category := l_ghr_pa_requests_rec.FLSA_category;
2819:
2820: IF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF in ('P','R') ) THEN
2821: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'Y';
2822: ELSIF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF = 'N') THEN
2823: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'N';
2824: ELSE
2825: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'NA';
2826: END IF;
2827:
2828: -- APPOINTMENT_NTE_DATE
2829: BEGIN
2830:
2831: get_asg_details( l_ghr_pa_requests_rec.pa_request_id,
2832: l_ghr_pa_requests_rec.person_id,
2833: l_ghr_pa_requests_rec.effective_date,
2834: l_ghr_cpdf_temp_rec.appointment_nte_date);
2835:
2828: -- APPOINTMENT_NTE_DATE
2829: BEGIN
2830:
2831: get_asg_details( l_ghr_pa_requests_rec.pa_request_id,
2832: l_ghr_pa_requests_rec.person_id,
2833: l_ghr_pa_requests_rec.effective_date,
2834: l_ghr_cpdf_temp_rec.appointment_nte_date);
2835:
2836: EXCEPTION
2829: BEGIN
2830:
2831: get_asg_details( l_ghr_pa_requests_rec.pa_request_id,
2832: l_ghr_pa_requests_rec.person_id,
2833: l_ghr_pa_requests_rec.effective_date,
2834: l_ghr_cpdf_temp_rec.appointment_nte_date);
2835:
2836: EXCEPTION
2837: WHEN OTHERS THEN
2836: EXCEPTION
2837: WHEN OTHERS THEN
2838: l_message_name := 'get_asg_details';
2839: l_log_text := 'Error in getting appointment_nte_date for pa_request_id: '||
2840: l_ghr_pa_requests_rec.pa_request_id ||
2841: ' ; SSN/employee last name' ||
2842: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2843: l_ghr_pa_Requests_rec.employee_last_name ||
2844: ' ; first NOAC/Second NOAC: '||
2838: l_message_name := 'get_asg_details';
2839: l_log_text := 'Error in getting appointment_nte_date for pa_request_id: '||
2840: l_ghr_pa_requests_rec.pa_request_id ||
2841: ' ; SSN/employee last name' ||
2842: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2843: l_ghr_pa_Requests_rec.employee_last_name ||
2844: ' ; first NOAC/Second NOAC: '||
2845: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2846: l_ghr_pa_requests_rec.second_noa_code ||
2839: l_log_text := 'Error in getting appointment_nte_date for pa_request_id: '||
2840: l_ghr_pa_requests_rec.pa_request_id ||
2841: ' ; SSN/employee last name' ||
2842: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2843: l_ghr_pa_Requests_rec.employee_last_name ||
2844: ' ; first NOAC/Second NOAC: '||
2845: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2846: l_ghr_pa_requests_rec.second_noa_code ||
2847: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2841: ' ; SSN/employee last name' ||
2842: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2843: l_ghr_pa_Requests_rec.employee_last_name ||
2844: ' ; first NOAC/Second NOAC: '||
2845: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2846: l_ghr_pa_requests_rec.second_noa_code ||
2847: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2848:
2849: Raise EHRI_DYNRPT_ERROR;
2842: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2843: l_ghr_pa_Requests_rec.employee_last_name ||
2844: ' ; first NOAC/Second NOAC: '||
2845: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2846: l_ghr_pa_requests_rec.second_noa_code ||
2847: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2848:
2849: Raise EHRI_DYNRPT_ERROR;
2850: END;
2851:
2852: --
2853: --Bug#5328177 Added the following conditions to report the incentive percentage, amount
2854: -- in the Award percentage(for noac 827) and award amount(for noacs 815,816,825) fields.
2855: IF l_ghr_pa_requests_rec.first_noa_code = '827' OR
2856: l_ghr_pa_requests_rec.second_noa_code = '827' THEN
2857: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
2858: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
2859: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
2852: --
2853: --Bug#5328177 Added the following conditions to report the incentive percentage, amount
2854: -- in the Award percentage(for noac 827) and award amount(for noacs 815,816,825) fields.
2855: IF l_ghr_pa_requests_rec.first_noa_code = '827' OR
2856: l_ghr_pa_requests_rec.second_noa_code = '827' THEN
2857: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
2858: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
2859: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
2860: l_ghr_pa_requests_rec.award_amount IS NULL THEN
2853: --Bug#5328177 Added the following conditions to report the incentive percentage, amount
2854: -- in the Award percentage(for noac 827) and award amount(for noacs 815,816,825) fields.
2855: IF l_ghr_pa_requests_rec.first_noa_code = '827' OR
2856: l_ghr_pa_requests_rec.second_noa_code = '827' THEN
2857: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
2858: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
2859: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
2860: l_ghr_pa_requests_rec.award_amount IS NULL THEN
2861: l_ghr_pa_requests_rec.award_amount := l_ghr_pa_requests_rec.to_total_salary;
2854: -- in the Award percentage(for noac 827) and award amount(for noacs 815,816,825) fields.
2855: IF l_ghr_pa_requests_rec.first_noa_code = '827' OR
2856: l_ghr_pa_requests_rec.second_noa_code = '827' THEN
2857: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
2858: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
2859: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
2860: l_ghr_pa_requests_rec.award_amount IS NULL THEN
2861: l_ghr_pa_requests_rec.award_amount := l_ghr_pa_requests_rec.to_total_salary;
2862: END IF;
2855: IF l_ghr_pa_requests_rec.first_noa_code = '827' OR
2856: l_ghr_pa_requests_rec.second_noa_code = '827' THEN
2857: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
2858: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
2859: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
2860: l_ghr_pa_requests_rec.award_amount IS NULL THEN
2861: l_ghr_pa_requests_rec.award_amount := l_ghr_pa_requests_rec.to_total_salary;
2862: END IF;
2863:
2856: l_ghr_pa_requests_rec.second_noa_code = '827' THEN
2857: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
2858: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
2859: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
2860: l_ghr_pa_requests_rec.award_amount IS NULL THEN
2861: l_ghr_pa_requests_rec.award_amount := l_ghr_pa_requests_rec.to_total_salary;
2862: END IF;
2863:
2864:
2857: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
2858: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
2859: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
2860: l_ghr_pa_requests_rec.award_amount IS NULL THEN
2861: l_ghr_pa_requests_rec.award_amount := l_ghr_pa_requests_rec.to_total_salary;
2862: END IF;
2863:
2864:
2865: -- IF l_noa_family_code = 'AWARD' THEN --BUG#5328177
2864:
2865: -- IF l_noa_family_code = 'AWARD' THEN --BUG#5328177
2866: IF l_noa_family_code IN ('AWARD','GHR_INCENTIVE') THEN
2867:
2868: l_ghr_pa_requests_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
2869: l_ghr_pa_requests_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2870: l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2871: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2872: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
2865: -- IF l_noa_family_code = 'AWARD' THEN --BUG#5328177
2866: IF l_noa_family_code IN ('AWARD','GHR_INCENTIVE') THEN
2867:
2868: l_ghr_pa_requests_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
2869: l_ghr_pa_requests_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2870: l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2871: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2872: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
2873: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2866: IF l_noa_family_code IN ('AWARD','GHR_INCENTIVE') THEN
2867:
2868: l_ghr_pa_requests_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
2869: l_ghr_pa_requests_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2870: l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2871: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2872: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
2873: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2874: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
2867:
2868: l_ghr_pa_requests_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
2869: l_ghr_pa_requests_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2870: l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2871: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2872: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
2873: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2874: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
2875: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
2868: l_ghr_pa_requests_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
2869: l_ghr_pa_requests_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2870: l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2871: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2872: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
2873: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2874: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
2875: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
2876: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
2869: l_ghr_pa_requests_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2870: l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2871: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2872: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
2873: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2874: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
2875: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
2876: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
2877:
2870: l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2871: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2872: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
2873: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2874: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
2875: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
2876: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
2877:
2878: END IF;
2871: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2872: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
2873: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2874: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
2875: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
2876: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
2877:
2878: END IF;
2879:
2872: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
2873: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2874: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
2875: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
2876: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
2877:
2878: END IF;
2879:
2880: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
2876: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
2877:
2878: END IF;
2879:
2880: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
2881: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
2882: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
2883: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
2884: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
2877:
2878: END IF;
2879:
2880: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
2881: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
2882: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
2883: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
2884: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
2885: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
2878: END IF;
2879:
2880: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
2881: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
2882: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
2883: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
2884: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
2885: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
2886: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
2879:
2880: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
2881: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
2882: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
2883: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
2884: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
2885: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
2886: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
2887: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
2880: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
2881: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
2882: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
2883: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
2884: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
2885: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
2886: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
2887: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
2888: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
2881: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
2882: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
2883: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
2884: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
2885: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
2886: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
2887: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
2888: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
2889:
2882: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
2883: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
2884: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
2885: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
2886: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
2887: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
2888: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
2889:
2890:
2883: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
2884: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
2885: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
2886: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
2887: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
2888: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
2889:
2890:
2891: IF l_noa_family_code <> 'AWARD' THEN
2884: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
2885: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
2886: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
2887: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
2888: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
2889:
2890:
2891: IF l_noa_family_code <> 'AWARD' THEN
2892: -- Added following 'IF' according to bug# 1375333
2889:
2890:
2891: IF l_noa_family_code <> 'AWARD' THEN
2892: -- Added following 'IF' according to bug# 1375333
2893: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
2894: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
2895: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2896: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
2897: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
2890:
2891: IF l_noa_family_code <> 'AWARD' THEN
2892: -- Added following 'IF' according to bug# 1375333
2893: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
2894: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
2895: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2896: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
2897: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
2898: )
2891: IF l_noa_family_code <> 'AWARD' THEN
2892: -- Added following 'IF' according to bug# 1375333
2893: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
2894: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
2895: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2896: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
2897: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
2898: )
2899: ) THEN
2892: -- Added following 'IF' according to bug# 1375333
2893: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
2894: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
2895: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2896: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
2897: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
2898: )
2899: ) THEN
2900: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
2893: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
2894: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
2895: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2896: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
2897: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
2898: )
2899: ) THEN
2900: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
2901: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
2896: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
2897: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
2898: )
2899: ) THEN
2900: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
2901: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
2902: ELSE
2903: l_ghr_cpdf_temp_rec.to_pay_rate_determinant := NULL;
2904: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
2897: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
2898: )
2899: ) THEN
2900: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
2901: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
2902: ELSE
2903: l_ghr_cpdf_temp_rec.to_pay_rate_determinant := NULL;
2904: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
2905: --
2915: END IF;
2916: END IF;
2917:
2918: -- Added following 'IF' according to bug# 1375333
2919: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
2920: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
2921: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2922: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
2923: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
2916: END IF;
2917:
2918: -- Added following 'IF' according to bug# 1375333
2919: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
2920: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
2921: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2922: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
2923: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
2924: )
2917:
2918: -- Added following 'IF' according to bug# 1375333
2919: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
2920: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
2921: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2922: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
2923: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
2924: )
2925: ) THEN
2918: -- Added following 'IF' according to bug# 1375333
2919: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
2920: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
2921: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2922: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
2923: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
2924: )
2925: ) THEN
2926:
2919: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
2920: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
2921: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2922: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
2923: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
2924: )
2925: ) THEN
2926:
2927:
2924: )
2925: ) THEN
2926:
2927:
2928: l_ghr_cpdf_temp_rec.to_work_schedule := l_ghr_pa_requests_rec.work_schedule;
2929: l_ghr_cpdf_temp_rec.part_time_hours := l_ghr_pa_requests_rec.part_time_hours;
2930: --- commented for bug# 2257630 as duty station code required for all NOA codes except for cancellation action
2931: -- l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
2932:
2925: ) THEN
2926:
2927:
2928: l_ghr_cpdf_temp_rec.to_work_schedule := l_ghr_pa_requests_rec.work_schedule;
2929: l_ghr_cpdf_temp_rec.part_time_hours := l_ghr_pa_requests_rec.part_time_hours;
2930: --- commented for bug# 2257630 as duty station code required for all NOA codes except for cancellation action
2931: -- l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
2932:
2933: ELSE
2927:
2928: l_ghr_cpdf_temp_rec.to_work_schedule := l_ghr_pa_requests_rec.work_schedule;
2929: l_ghr_cpdf_temp_rec.part_time_hours := l_ghr_pa_requests_rec.part_time_hours;
2930: --- commented for bug# 2257630 as duty station code required for all NOA codes except for cancellation action
2931: -- l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
2932:
2933: ELSE
2934: l_ghr_cpdf_temp_rec.to_work_schedule := NULL;
2935: l_ghr_cpdf_temp_rec.part_time_hours := NULL;
2947: END IF;
2948: END IF;
2949: --End of BUG# 6631879
2950:
2951: l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
2952: l_ghr_cpdf_temp_rec.position_occupied := l_ghr_pa_requests_rec.position_occupied;
2953: l_ghr_cpdf_temp_rec.supervisory_status := l_ghr_pa_requests_rec.supervisory_status;
2954: -- Supervisory Status
2955: l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
2948: END IF;
2949: --End of BUG# 6631879
2950:
2951: l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
2952: l_ghr_cpdf_temp_rec.position_occupied := l_ghr_pa_requests_rec.position_occupied;
2953: l_ghr_cpdf_temp_rec.supervisory_status := l_ghr_pa_requests_rec.supervisory_status;
2954: -- Supervisory Status
2955: l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
2956:
2949: --End of BUG# 6631879
2950:
2951: l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
2952: l_ghr_cpdf_temp_rec.position_occupied := l_ghr_pa_requests_rec.position_occupied;
2953: l_ghr_cpdf_temp_rec.supervisory_status := l_ghr_pa_requests_rec.supervisory_status;
2954: -- Supervisory Status
2955: l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
2956:
2957: -- Added IF for bug# 1375342
2951: l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
2952: l_ghr_cpdf_temp_rec.position_occupied := l_ghr_pa_requests_rec.position_occupied;
2953: l_ghr_cpdf_temp_rec.supervisory_status := l_ghr_pa_requests_rec.supervisory_status;
2954: -- Supervisory Status
2955: l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
2956:
2957: -- Added IF for bug# 1375342
2958: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
2959: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','825','826','827',
2954: -- Supervisory Status
2955: l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
2956:
2957: -- Added IF for bug# 1375342
2958: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
2959: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','825','826','827',
2960: '840','841','842','843','844','845','846','847','848','849','878','879')
2961: )
2962: OR
2955: l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
2956:
2957: -- Added IF for bug# 1375342
2958: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
2959: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','825','826','827',
2960: '840','841','842','843','844','845','846','847','848','849','878','879')
2961: )
2962: OR
2963: l_ghr_pa_requests_rec.first_noa_code in ('815','816','817','825','826','827',
2959: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','825','826','827',
2960: '840','841','842','843','844','845','846','847','848','849','878','879')
2961: )
2962: OR
2963: l_ghr_pa_requests_rec.first_noa_code in ('815','816','817','825','826','827',
2964: '840','841','842','843','844','845','846','847','848','849','878','879')
2965: )
2966: AND
2967: NOT ( (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' ) OR
2963: l_ghr_pa_requests_rec.first_noa_code in ('815','816','817','825','826','827',
2964: '840','841','842','843','844','845','846','847','848','849','878','879')
2965: )
2966: AND
2967: NOT ( (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' ) OR
2968: (l_ghr_pa_requests_rec.first_noa_code ='002' and
2969: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%'
2970: )
2971: )
2964: '840','841','842','843','844','845','846','847','848','849','878','879')
2965: )
2966: AND
2967: NOT ( (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' ) OR
2968: (l_ghr_pa_requests_rec.first_noa_code ='002' and
2969: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%'
2970: )
2971: )
2972: THEN
2965: )
2966: AND
2967: NOT ( (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' ) OR
2968: (l_ghr_pa_requests_rec.first_noa_code ='002' and
2969: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%'
2970: )
2971: )
2972: THEN
2973:
2970: )
2971: )
2972: THEN
2973:
2974: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
2975: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2976: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2977: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2978: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
2971: )
2972: THEN
2973:
2974: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
2975: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2976: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2977: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2978: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
2979: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2972: THEN
2973:
2974: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
2975: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2976: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2977: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2978: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
2979: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2980: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
2973:
2974: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
2975: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2976: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2977: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2978: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
2979: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2980: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
2981: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
2974: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
2975: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2976: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2977: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2978: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
2979: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2980: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
2981: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
2982: END IF;
2975: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2976: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2977: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2978: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
2979: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2980: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
2981: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
2982: END IF;
2983:
2976: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2977: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2978: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
2979: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2980: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
2981: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
2982: END IF;
2983:
2984: -- NEW EHRI changes need these prior details
2977: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2978: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
2979: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2980: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
2981: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
2982: END IF;
2983:
2984: -- NEW EHRI changes need these prior details
2985: -- Madhuri
2983:
2984: -- NEW EHRI changes need these prior details
2985: -- Madhuri
2986:
2987: IF get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
2988: p_effective_date => l_ghr_pa_requests_rec.effective_date) <> '99' THEN
2989:
2990: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
2991: l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
2984: -- NEW EHRI changes need these prior details
2985: -- Madhuri
2986:
2987: IF get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
2988: p_effective_date => l_ghr_pa_requests_rec.effective_date) <> '99' THEN
2989:
2990: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
2991: l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
2992: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
2986:
2987: IF get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
2988: p_effective_date => l_ghr_pa_requests_rec.effective_date) <> '99' THEN
2989:
2990: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
2991: l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
2992: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
2993: (NVL(l_ghr_pa_requests_rec.first_noa_code,'@#') <> '002' OR
2994: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') NOT LIKE '2%'
2987: IF get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
2988: p_effective_date => l_ghr_pa_requests_rec.effective_date) <> '99' THEN
2989:
2990: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
2991: l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
2992: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
2993: (NVL(l_ghr_pa_requests_rec.first_noa_code,'@#') <> '002' OR
2994: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') NOT LIKE '2%'
2995: )
2988: p_effective_date => l_ghr_pa_requests_rec.effective_date) <> '99' THEN
2989:
2990: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
2991: l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
2992: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
2993: (NVL(l_ghr_pa_requests_rec.first_noa_code,'@#') <> '002' OR
2994: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') NOT LIKE '2%'
2995: )
2996: ) THEN
2989:
2990: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
2991: l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
2992: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
2993: (NVL(l_ghr_pa_requests_rec.first_noa_code,'@#') <> '002' OR
2994: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') NOT LIKE '2%'
2995: )
2996: ) THEN
2997:
2990: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
2991: l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
2992: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
2993: (NVL(l_ghr_pa_requests_rec.first_noa_code,'@#') <> '002' OR
2994: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') NOT LIKE '2%'
2995: )
2996: ) THEN
2997:
2998: IF ( l_noa_family_code <> 'AWARD' or
2995: )
2996: ) THEN
2997:
2998: IF ( l_noa_family_code <> 'AWARD' or
2999: (l_ghr_pa_requests_rec.first_noa_code ='885' or
3000: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')='885') )
3001: THEN
3002: --
3003: IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan)) <> 'FW' THEN
2996: ) THEN
2997:
2998: IF ( l_noa_family_code <> 'AWARD' or
2999: (l_ghr_pa_requests_rec.first_noa_code ='885' or
3000: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')='885') )
3001: THEN
3002: --
3003: IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan)) <> 'FW' THEN
3004: l_ghr_cpdf_temp_rec.from_locality_adj := NVL(l_ghr_pa_requests_rec.from_locality_adj, 0);
2999: (l_ghr_pa_requests_rec.first_noa_code ='885' or
3000: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')='885') )
3001: THEN
3002: --
3003: IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan)) <> 'FW' THEN
3004: l_ghr_cpdf_temp_rec.from_locality_adj := NVL(l_ghr_pa_requests_rec.from_locality_adj, 0);
3005: ELSE
3006: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3007: END IF;
3000: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')='885') )
3001: THEN
3002: --
3003: IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan)) <> 'FW' THEN
3004: l_ghr_cpdf_temp_rec.from_locality_adj := NVL(l_ghr_pa_requests_rec.from_locality_adj, 0);
3005: ELSE
3006: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3007: END IF;
3008: --
3010: ELSE
3011: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3012: END IF;
3013:
3014: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3015: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '3%' AND
3016: (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
3017: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
3018: )
3011: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3012: END IF;
3013:
3014: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3015: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '3%' AND
3016: (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
3017: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
3018: )
3019: ) AND
3012: END IF;
3013:
3014: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3015: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '3%' AND
3016: (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
3017: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
3018: )
3019: ) AND
3020: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '4%' AND
3013:
3014: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3015: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '3%' AND
3016: (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
3017: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
3018: )
3019: ) AND
3020: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '4%' AND
3021: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3016: (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
3017: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
3018: )
3019: ) AND
3020: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '4%' AND
3021: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3022: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '4%'
3023: )
3024: ) THEN
3017: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
3018: )
3019: ) AND
3020: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '4%' AND
3021: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3022: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '4%'
3023: )
3024: ) THEN
3025: IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan)) <> 'FW' THEN
3018: )
3019: ) AND
3020: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '4%' AND
3021: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3022: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '4%'
3023: )
3024: ) THEN
3025: IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan)) <> 'FW' THEN
3026: l_ghr_cpdf_temp_rec.to_locality_adj := NVL(l_ghr_pa_requests_rec.to_locality_adj, 0);
3021: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3022: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '4%'
3023: )
3024: ) THEN
3025: IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan)) <> 'FW' THEN
3026: l_ghr_cpdf_temp_rec.to_locality_adj := NVL(l_ghr_pa_requests_rec.to_locality_adj, 0);
3027: END IF;
3028: ELSE
3029: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3022: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '4%'
3023: )
3024: ) THEN
3025: IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan)) <> 'FW' THEN
3026: l_ghr_cpdf_temp_rec.to_locality_adj := NVL(l_ghr_pa_requests_rec.to_locality_adj, 0);
3027: END IF;
3028: ELSE
3029: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3030: END IF;
3034: -- l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3035: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3036: END IF;
3037:
3038: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3039: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3040: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3041: )
3042: ) AND
3035: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3036: END IF;
3037:
3038: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3039: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3040: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3041: )
3042: ) AND
3043: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3036: END IF;
3037:
3038: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3039: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3040: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3041: )
3042: ) AND
3043: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3044: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3039: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3040: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3041: )
3042: ) AND
3043: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3044: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3045: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3046: )
3047: )
3040: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3041: )
3042: ) AND
3043: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3044: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3045: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3046: )
3047: )
3048: THEN -- Issue 20 4257213
3041: )
3042: ) AND
3043: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3044: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3045: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3046: )
3047: )
3048: THEN -- Issue 20 4257213
3049: l_ghr_cpdf_temp_rec.to_staffing_differential := l_ghr_pa_requests_rec.to_staffing_differential; -- format in report
3045: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3046: )
3047: )
3048: THEN -- Issue 20 4257213
3049: l_ghr_cpdf_temp_rec.to_staffing_differential := l_ghr_pa_requests_rec.to_staffing_differential; -- format in report
3050: l_ghr_cpdf_temp_rec.to_supervisory_differential := l_ghr_pa_requests_rec.to_supervisory_differential;-- format in report
3051: l_ghr_cpdf_temp_rec.to_retention_allowance := l_ghr_pa_requests_rec.to_retention_allowance; -- format in report
3052: ELSE
3053: l_ghr_cpdf_temp_rec.to_staffing_differential := NULL;
3046: )
3047: )
3048: THEN -- Issue 20 4257213
3049: l_ghr_cpdf_temp_rec.to_staffing_differential := l_ghr_pa_requests_rec.to_staffing_differential; -- format in report
3050: l_ghr_cpdf_temp_rec.to_supervisory_differential := l_ghr_pa_requests_rec.to_supervisory_differential;-- format in report
3051: l_ghr_cpdf_temp_rec.to_retention_allowance := l_ghr_pa_requests_rec.to_retention_allowance; -- format in report
3052: ELSE
3053: l_ghr_cpdf_temp_rec.to_staffing_differential := NULL;
3054: l_ghr_cpdf_temp_rec.to_supervisory_differential := NULL;
3047: )
3048: THEN -- Issue 20 4257213
3049: l_ghr_cpdf_temp_rec.to_staffing_differential := l_ghr_pa_requests_rec.to_staffing_differential; -- format in report
3050: l_ghr_cpdf_temp_rec.to_supervisory_differential := l_ghr_pa_requests_rec.to_supervisory_differential;-- format in report
3051: l_ghr_cpdf_temp_rec.to_retention_allowance := l_ghr_pa_requests_rec.to_retention_allowance; -- format in report
3052: ELSE
3053: l_ghr_cpdf_temp_rec.to_staffing_differential := NULL;
3054: l_ghr_cpdf_temp_rec.to_supervisory_differential := NULL;
3055: l_ghr_cpdf_temp_rec.to_retention_allowance := NULL;
3055: l_ghr_cpdf_temp_rec.to_retention_allowance := NULL;
3056: END IF;
3057:
3058: IF l_noa_family_code IN ('AWARD', 'OTHER_PAY','GHR_INCENTIVE') THEN -- Bug# 1400486 --GHR_INCENTIVE added for bug # 5328177
3059: IF l_ghr_pa_requests_rec.first_noa_code IN ('818', '819') OR
3060: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3061: l_ghr_pa_requests_rec.second_noa_code IN ('818', '819')) THEN
3062: IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
3063: -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
3056: END IF;
3057:
3058: IF l_noa_family_code IN ('AWARD', 'OTHER_PAY','GHR_INCENTIVE') THEN -- Bug# 1400486 --GHR_INCENTIVE added for bug # 5328177
3059: IF l_ghr_pa_requests_rec.first_noa_code IN ('818', '819') OR
3060: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3061: l_ghr_pa_requests_rec.second_noa_code IN ('818', '819')) THEN
3062: IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
3063: -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
3064: IF l_ghr_pa_requests_rec.first_noa_code = '818' OR
3057:
3058: IF l_noa_family_code IN ('AWARD', 'OTHER_PAY','GHR_INCENTIVE') THEN -- Bug# 1400486 --GHR_INCENTIVE added for bug # 5328177
3059: IF l_ghr_pa_requests_rec.first_noa_code IN ('818', '819') OR
3060: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3061: l_ghr_pa_requests_rec.second_noa_code IN ('818', '819')) THEN
3062: IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
3063: -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
3064: IF l_ghr_pa_requests_rec.first_noa_code = '818' OR
3065: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3060: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3061: l_ghr_pa_requests_rec.second_noa_code IN ('818', '819')) THEN
3062: IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
3063: -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
3064: IF l_ghr_pa_requests_rec.first_noa_code = '818' OR
3065: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3066: l_ghr_pa_requests_rec.second_noa_code = '818') THEN
3067: -- Bug#2789704 Added Exception Handling
3068: BEGIN
3061: l_ghr_pa_requests_rec.second_noa_code IN ('818', '819')) THEN
3062: IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
3063: -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
3064: IF l_ghr_pa_requests_rec.first_noa_code = '818' OR
3065: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3066: l_ghr_pa_requests_rec.second_noa_code = '818') THEN
3067: -- Bug#2789704 Added Exception Handling
3068: BEGIN
3069: ghr_api.retrieve_element_entry_value (p_element_name => 'AUO'
3062: IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
3063: -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
3064: IF l_ghr_pa_requests_rec.first_noa_code = '818' OR
3065: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3066: l_ghr_pa_requests_rec.second_noa_code = '818') THEN
3067: -- Bug#2789704 Added Exception Handling
3068: BEGIN
3069: ghr_api.retrieve_element_entry_value (p_element_name => 'AUO'
3070: ,p_input_value_name => 'Amount'
3067: -- Bug#2789704 Added Exception Handling
3068: BEGIN
3069: ghr_api.retrieve_element_entry_value (p_element_name => 'AUO'
3070: ,p_input_value_name => 'Amount'
3071: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3072: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3073: ,p_value => l_ghr_cpdf_temp_rec.award_amount
3074: ,p_multiple_error_flag => l_multi_error_flag);
3075: EXCEPTION
3068: BEGIN
3069: ghr_api.retrieve_element_entry_value (p_element_name => 'AUO'
3070: ,p_input_value_name => 'Amount'
3071: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3072: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3073: ,p_value => l_ghr_cpdf_temp_rec.award_amount
3074: ,p_multiple_error_flag => l_multi_error_flag);
3075: EXCEPTION
3076: WHEN OTHERS THEN
3075: EXCEPTION
3076: WHEN OTHERS THEN
3077: l_message_name := 'retrieve_element_entry_value';
3078: l_log_text := 'Error in fetching AUO Amount for pa_request_id: '||
3079: l_ghr_pa_requests_rec.pa_request_id ||
3080: ' ; SSN/employee last name' ||
3081: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3082: l_ghr_pa_Requests_rec.employee_last_name ||
3083: ' ; first NOAC/Second NOAC: '||
3077: l_message_name := 'retrieve_element_entry_value';
3078: l_log_text := 'Error in fetching AUO Amount for pa_request_id: '||
3079: l_ghr_pa_requests_rec.pa_request_id ||
3080: ' ; SSN/employee last name' ||
3081: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3082: l_ghr_pa_Requests_rec.employee_last_name ||
3083: ' ; first NOAC/Second NOAC: '||
3084: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3085: l_ghr_pa_requests_rec.second_noa_code ||
3078: l_log_text := 'Error in fetching AUO Amount for pa_request_id: '||
3079: l_ghr_pa_requests_rec.pa_request_id ||
3080: ' ; SSN/employee last name' ||
3081: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3082: l_ghr_pa_Requests_rec.employee_last_name ||
3083: ' ; first NOAC/Second NOAC: '||
3084: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3085: l_ghr_pa_requests_rec.second_noa_code ||
3086: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3080: ' ; SSN/employee last name' ||
3081: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3082: l_ghr_pa_Requests_rec.employee_last_name ||
3083: ' ; first NOAC/Second NOAC: '||
3084: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3085: l_ghr_pa_requests_rec.second_noa_code ||
3086: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3087:
3088: Raise EHRI_DYNRPT_ERROR;
3081: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3082: l_ghr_pa_Requests_rec.employee_last_name ||
3083: ' ; first NOAC/Second NOAC: '||
3084: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3085: l_ghr_pa_requests_rec.second_noa_code ||
3086: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3087:
3088: Raise EHRI_DYNRPT_ERROR;
3089: END;
3092: -- Bug#2789704 Added Exception Handling
3093: BEGIN
3094: ghr_api.retrieve_element_entry_value (p_element_name => 'Availability Pay'
3095: ,p_input_value_name => 'Amount'
3096: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3097: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3098: ,p_value => l_ghr_cpdf_temp_rec.award_amount
3099: ,p_multiple_error_flag => l_multi_error_flag);
3100: EXCEPTION
3093: BEGIN
3094: ghr_api.retrieve_element_entry_value (p_element_name => 'Availability Pay'
3095: ,p_input_value_name => 'Amount'
3096: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3097: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3098: ,p_value => l_ghr_cpdf_temp_rec.award_amount
3099: ,p_multiple_error_flag => l_multi_error_flag);
3100: EXCEPTION
3101: WHEN OTHERS THEN
3100: EXCEPTION
3101: WHEN OTHERS THEN
3102: l_message_name := 'retrieve_element_entry_value';
3103: l_log_text := 'Error in fetching Availability Pay Amount for pa_request_id: '||
3104: l_ghr_pa_requests_rec.pa_request_id ||
3105: ' ; SSN/employee last name' ||
3106: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3107: l_ghr_pa_Requests_rec.employee_last_name ||
3108: ' ; first NOAC/Second NOAC: '||
3102: l_message_name := 'retrieve_element_entry_value';
3103: l_log_text := 'Error in fetching Availability Pay Amount for pa_request_id: '||
3104: l_ghr_pa_requests_rec.pa_request_id ||
3105: ' ; SSN/employee last name' ||
3106: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3107: l_ghr_pa_Requests_rec.employee_last_name ||
3108: ' ; first NOAC/Second NOAC: '||
3109: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3110: l_ghr_pa_requests_rec.second_noa_code ||
3103: l_log_text := 'Error in fetching Availability Pay Amount for pa_request_id: '||
3104: l_ghr_pa_requests_rec.pa_request_id ||
3105: ' ; SSN/employee last name' ||
3106: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3107: l_ghr_pa_Requests_rec.employee_last_name ||
3108: ' ; first NOAC/Second NOAC: '||
3109: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3110: l_ghr_pa_requests_rec.second_noa_code ||
3111: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3105: ' ; SSN/employee last name' ||
3106: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3107: l_ghr_pa_Requests_rec.employee_last_name ||
3108: ' ; first NOAC/Second NOAC: '||
3109: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3110: l_ghr_pa_requests_rec.second_noa_code ||
3111: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3112:
3113: Raise EHRI_DYNRPT_ERROR;
3106: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3107: l_ghr_pa_Requests_rec.employee_last_name ||
3108: ' ; first NOAC/Second NOAC: '||
3109: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3110: l_ghr_pa_requests_rec.second_noa_code ||
3111: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3112:
3113: Raise EHRI_DYNRPT_ERROR;
3114: END;
3120: -- Bug#2789704 Added Exception Handling
3121: BEGIN
3122: ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
3123: ,p_input_value_name => 'Amount'
3124: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3125: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3126: ,p_value => l_ghr_cpdf_temp_rec.to_supervisory_differential
3127: ,p_multiple_error_flag => l_multi_error_flag);
3128: EXCEPTION
3121: BEGIN
3122: ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
3123: ,p_input_value_name => 'Amount'
3124: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3125: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3126: ,p_value => l_ghr_cpdf_temp_rec.to_supervisory_differential
3127: ,p_multiple_error_flag => l_multi_error_flag);
3128: EXCEPTION
3129: WHEN OTHERS THEN
3128: EXCEPTION
3129: WHEN OTHERS THEN
3130: l_message_name := 'retrieve_element_entry_value';
3131: l_log_text := 'Error in fetching Supervisory Differential Amount for pa_request_id: '||
3132: l_ghr_pa_requests_rec.pa_request_id ||
3133: ' ; SSN/employee last name' ||
3134: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3135: l_ghr_pa_Requests_rec.employee_last_name ||
3136: ' ; first NOAC/Second NOAC: '||
3130: l_message_name := 'retrieve_element_entry_value';
3131: l_log_text := 'Error in fetching Supervisory Differential Amount for pa_request_id: '||
3132: l_ghr_pa_requests_rec.pa_request_id ||
3133: ' ; SSN/employee last name' ||
3134: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3135: l_ghr_pa_Requests_rec.employee_last_name ||
3136: ' ; first NOAC/Second NOAC: '||
3137: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3138: l_ghr_pa_requests_rec.second_noa_code ||
3131: l_log_text := 'Error in fetching Supervisory Differential Amount for pa_request_id: '||
3132: l_ghr_pa_requests_rec.pa_request_id ||
3133: ' ; SSN/employee last name' ||
3134: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3135: l_ghr_pa_Requests_rec.employee_last_name ||
3136: ' ; first NOAC/Second NOAC: '||
3137: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3138: l_ghr_pa_requests_rec.second_noa_code ||
3139: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3133: ' ; SSN/employee last name' ||
3134: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3135: l_ghr_pa_Requests_rec.employee_last_name ||
3136: ' ; first NOAC/Second NOAC: '||
3137: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3138: l_ghr_pa_requests_rec.second_noa_code ||
3139: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3140:
3141: Raise EHRI_DYNRPT_ERROR;
3134: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3135: l_ghr_pa_Requests_rec.employee_last_name ||
3136: ' ; first NOAC/Second NOAC: '||
3137: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3138: l_ghr_pa_requests_rec.second_noa_code ||
3139: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3140:
3141: Raise EHRI_DYNRPT_ERROR;
3142: END;
3147: -- Bug#2789704 Added Exception Handling
3148: BEGIN
3149: ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
3150: ,p_input_value_name => 'Amount'
3151: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3152: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3153: ,p_value => l_ghr_cpdf_temp_rec.to_retention_allowance
3154: ,p_multiple_error_flag => l_multi_error_flag);
3155: EXCEPTION
3148: BEGIN
3149: ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
3150: ,p_input_value_name => 'Amount'
3151: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3152: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3153: ,p_value => l_ghr_cpdf_temp_rec.to_retention_allowance
3154: ,p_multiple_error_flag => l_multi_error_flag);
3155: EXCEPTION
3156: WHEN OTHERS THEN
3155: EXCEPTION
3156: WHEN OTHERS THEN
3157: l_message_name := 'retrieve_element_entry_value';
3158: l_log_text := 'Error in fetching Retention Allowance Amount for pa_request_id: '||
3159: l_ghr_pa_requests_rec.pa_request_id ||
3160: ' ; SSN/employee last name' ||
3161: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3162: l_ghr_pa_Requests_rec.employee_last_name ||
3163: ' ; first NOAC/Second NOAC: '||
3157: l_message_name := 'retrieve_element_entry_value';
3158: l_log_text := 'Error in fetching Retention Allowance Amount for pa_request_id: '||
3159: l_ghr_pa_requests_rec.pa_request_id ||
3160: ' ; SSN/employee last name' ||
3161: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3162: l_ghr_pa_Requests_rec.employee_last_name ||
3163: ' ; first NOAC/Second NOAC: '||
3164: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3165: l_ghr_pa_requests_rec.second_noa_code ||
3158: l_log_text := 'Error in fetching Retention Allowance Amount for pa_request_id: '||
3159: l_ghr_pa_requests_rec.pa_request_id ||
3160: ' ; SSN/employee last name' ||
3161: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3162: l_ghr_pa_Requests_rec.employee_last_name ||
3163: ' ; first NOAC/Second NOAC: '||
3164: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3165: l_ghr_pa_requests_rec.second_noa_code ||
3166: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3160: ' ; SSN/employee last name' ||
3161: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3162: l_ghr_pa_Requests_rec.employee_last_name ||
3163: ' ; first NOAC/Second NOAC: '||
3164: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3165: l_ghr_pa_requests_rec.second_noa_code ||
3166: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3167:
3168: Raise EHRI_DYNRPT_ERROR;
3161: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3162: l_ghr_pa_Requests_rec.employee_last_name ||
3163: ' ; first NOAC/Second NOAC: '||
3164: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3165: l_ghr_pa_requests_rec.second_noa_code ||
3166: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3167:
3168: Raise EHRI_DYNRPT_ERROR;
3169: END;
3172: END IF;
3173: END IF;
3174:
3175: -- Sundar Changes for education
3176: ghr_api.return_education_details(p_person_id => l_ghr_pa_requests_rec.person_id,
3177: p_effective_date => l_ghr_pa_requests_rec.effective_date,
3178: p_education_level => l_ghr_cpdf_temp_rec.education_level,
3179: p_academic_discipline => l_ghr_cpdf_temp_rec.academic_discipline,
3180: p_year_degree_attained => l_ghr_cpdf_temp_rec.year_degree_attained);
3173: END IF;
3174:
3175: -- Sundar Changes for education
3176: ghr_api.return_education_details(p_person_id => l_ghr_pa_requests_rec.person_id,
3177: p_effective_date => l_ghr_pa_requests_rec.effective_date,
3178: p_education_level => l_ghr_cpdf_temp_rec.education_level,
3179: p_academic_discipline => l_ghr_cpdf_temp_rec.academic_discipline,
3180: p_year_degree_attained => l_ghr_cpdf_temp_rec.year_degree_attained);
3181: -- End Sundar changes for education Commented below code
3188: -- it must come from to_position if there, if not from the from_position
3189: --
3190: -- Bug#2789704 Added Exception Handling
3191: BEGIN
3192: get_org_comp (NVL(l_ghr_pa_requests_rec.to_position_id
3193: ,l_ghr_pa_requests_rec.from_position_id)
3194: ,l_ghr_pa_requests_rec.effective_date
3195: ,l_ghr_cpdf_temp_rec.organizational_component);
3196: -- Organizational Component Code
3189: --
3190: -- Bug#2789704 Added Exception Handling
3191: BEGIN
3192: get_org_comp (NVL(l_ghr_pa_requests_rec.to_position_id
3193: ,l_ghr_pa_requests_rec.from_position_id)
3194: ,l_ghr_pa_requests_rec.effective_date
3195: ,l_ghr_cpdf_temp_rec.organizational_component);
3196: -- Organizational Component Code
3197: EXCEPTION
3190: -- Bug#2789704 Added Exception Handling
3191: BEGIN
3192: get_org_comp (NVL(l_ghr_pa_requests_rec.to_position_id
3193: ,l_ghr_pa_requests_rec.from_position_id)
3194: ,l_ghr_pa_requests_rec.effective_date
3195: ,l_ghr_cpdf_temp_rec.organizational_component);
3196: -- Organizational Component Code
3197: EXCEPTION
3198: WHEN OTHERS THEN
3197: EXCEPTION
3198: WHEN OTHERS THEN
3199: l_message_name := 'get_org_comp';
3200: l_log_text := 'Error in fetching OPM Organizational Component for pa_request_id: '||
3201: l_ghr_pa_requests_rec.pa_request_id ||
3202: ' ; SSN/employee last name' ||
3203: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3204: l_ghr_pa_Requests_rec.employee_last_name ||
3205: ' ; first NOAC/Second NOAC: '||
3199: l_message_name := 'get_org_comp';
3200: l_log_text := 'Error in fetching OPM Organizational Component for pa_request_id: '||
3201: l_ghr_pa_requests_rec.pa_request_id ||
3202: ' ; SSN/employee last name' ||
3203: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3204: l_ghr_pa_Requests_rec.employee_last_name ||
3205: ' ; first NOAC/Second NOAC: '||
3206: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3207: l_ghr_pa_requests_rec.second_noa_code ||
3200: l_log_text := 'Error in fetching OPM Organizational Component for pa_request_id: '||
3201: l_ghr_pa_requests_rec.pa_request_id ||
3202: ' ; SSN/employee last name' ||
3203: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3204: l_ghr_pa_Requests_rec.employee_last_name ||
3205: ' ; first NOAC/Second NOAC: '||
3206: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3207: l_ghr_pa_requests_rec.second_noa_code ||
3208: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3202: ' ; SSN/employee last name' ||
3203: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3204: l_ghr_pa_Requests_rec.employee_last_name ||
3205: ' ; first NOAC/Second NOAC: '||
3206: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3207: l_ghr_pa_requests_rec.second_noa_code ||
3208: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3209:
3210: Raise EHRI_DYNRPT_ERROR;
3203: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3204: l_ghr_pa_Requests_rec.employee_last_name ||
3205: ' ; first NOAC/Second NOAC: '||
3206: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3207: l_ghr_pa_requests_rec.second_noa_code ||
3208: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3209:
3210: Raise EHRI_DYNRPT_ERROR;
3211: END;
3210: Raise EHRI_DYNRPT_ERROR;
3211: END;
3212: -- Getting appropriation code new EHRI CHanges.
3213: BEGIN
3214: get_appr_code(NVL(l_ghr_pa_requests_rec.to_position_id
3215: ,l_ghr_pa_requests_rec.from_position_id)
3216: ,l_ghr_pa_requests_rec.effective_date
3217: ,l_ghr_cpdf_temp_rec.APPROPRIATION_CODE);
3218: -- Organizational Component Code
3211: END;
3212: -- Getting appropriation code new EHRI CHanges.
3213: BEGIN
3214: get_appr_code(NVL(l_ghr_pa_requests_rec.to_position_id
3215: ,l_ghr_pa_requests_rec.from_position_id)
3216: ,l_ghr_pa_requests_rec.effective_date
3217: ,l_ghr_cpdf_temp_rec.APPROPRIATION_CODE);
3218: -- Organizational Component Code
3219: EXCEPTION
3212: -- Getting appropriation code new EHRI CHanges.
3213: BEGIN
3214: get_appr_code(NVL(l_ghr_pa_requests_rec.to_position_id
3215: ,l_ghr_pa_requests_rec.from_position_id)
3216: ,l_ghr_pa_requests_rec.effective_date
3217: ,l_ghr_cpdf_temp_rec.APPROPRIATION_CODE);
3218: -- Organizational Component Code
3219: EXCEPTION
3220: WHEN OTHERS THEN
3219: EXCEPTION
3220: WHEN OTHERS THEN
3221: l_message_name := 'get_appr_code';
3222: l_log_text := 'Error in fetching Appropriation code for the position of : '||
3223: l_ghr_pa_requests_rec.pa_request_id ||
3224: ' ; SSN/employee last name' ||
3225: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3226: l_ghr_pa_Requests_rec.employee_last_name ||
3227: ' ; first NOAC/Second NOAC: '||
3221: l_message_name := 'get_appr_code';
3222: l_log_text := 'Error in fetching Appropriation code for the position of : '||
3223: l_ghr_pa_requests_rec.pa_request_id ||
3224: ' ; SSN/employee last name' ||
3225: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3226: l_ghr_pa_Requests_rec.employee_last_name ||
3227: ' ; first NOAC/Second NOAC: '||
3228: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3229: l_ghr_pa_requests_rec.second_noa_code ||
3222: l_log_text := 'Error in fetching Appropriation code for the position of : '||
3223: l_ghr_pa_requests_rec.pa_request_id ||
3224: ' ; SSN/employee last name' ||
3225: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3226: l_ghr_pa_Requests_rec.employee_last_name ||
3227: ' ; first NOAC/Second NOAC: '||
3228: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3229: l_ghr_pa_requests_rec.second_noa_code ||
3230: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3224: ' ; SSN/employee last name' ||
3225: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3226: l_ghr_pa_Requests_rec.employee_last_name ||
3227: ' ; first NOAC/Second NOAC: '||
3228: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3229: l_ghr_pa_requests_rec.second_noa_code ||
3230: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3231:
3232: Raise EHRI_DYNRPT_ERROR;
3225: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3226: l_ghr_pa_Requests_rec.employee_last_name ||
3227: ' ; first NOAC/Second NOAC: '||
3228: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3229: l_ghr_pa_requests_rec.second_noa_code ||
3230: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3231:
3232: Raise EHRI_DYNRPT_ERROR;
3233: END;
3235: --
3236: -- 3.3) Get Sex
3237: -- Bug#2789704 Added Exception Handling
3238: BEGIN
3239: get_sex (l_ghr_pa_requests_rec.person_id
3240: ,l_ghr_pa_requests_rec.effective_date
3241: ,l_ghr_cpdf_temp_rec.sex);
3242: EXCEPTION
3243: WHEN OTHERS THEN
3236: -- 3.3) Get Sex
3237: -- Bug#2789704 Added Exception Handling
3238: BEGIN
3239: get_sex (l_ghr_pa_requests_rec.person_id
3240: ,l_ghr_pa_requests_rec.effective_date
3241: ,l_ghr_cpdf_temp_rec.sex);
3242: EXCEPTION
3243: WHEN OTHERS THEN
3244: l_message_name := 'get_sex';
3242: EXCEPTION
3243: WHEN OTHERS THEN
3244: l_message_name := 'get_sex';
3245: l_log_text := 'Error in fetching SEX for pa_request_id: '||
3246: l_ghr_pa_requests_rec.pa_request_id ||
3247: ' ; SSN/employee last name' ||
3248: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3249: l_ghr_pa_Requests_rec.employee_last_name ||
3250: ' ; first NOAC/Second NOAC: '||
3244: l_message_name := 'get_sex';
3245: l_log_text := 'Error in fetching SEX for pa_request_id: '||
3246: l_ghr_pa_requests_rec.pa_request_id ||
3247: ' ; SSN/employee last name' ||
3248: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3249: l_ghr_pa_Requests_rec.employee_last_name ||
3250: ' ; first NOAC/Second NOAC: '||
3251: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3252: l_ghr_pa_requests_rec.second_noa_code ||
3245: l_log_text := 'Error in fetching SEX for pa_request_id: '||
3246: l_ghr_pa_requests_rec.pa_request_id ||
3247: ' ; SSN/employee last name' ||
3248: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3249: l_ghr_pa_Requests_rec.employee_last_name ||
3250: ' ; first NOAC/Second NOAC: '||
3251: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3252: l_ghr_pa_requests_rec.second_noa_code ||
3253: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3247: ' ; SSN/employee last name' ||
3248: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3249: l_ghr_pa_Requests_rec.employee_last_name ||
3250: ' ; first NOAC/Second NOAC: '||
3251: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3252: l_ghr_pa_requests_rec.second_noa_code ||
3253: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3254:
3255: Raise EHRI_DYNRPT_ERROR;
3248: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3249: l_ghr_pa_Requests_rec.employee_last_name ||
3250: ' ; first NOAC/Second NOAC: '||
3251: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3252: l_ghr_pa_requests_rec.second_noa_code ||
3253: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3254:
3255: Raise EHRI_DYNRPT_ERROR;
3256: END;
3259: -- 3.4) Get person SIT - 'US Fed Perf Appraisal'
3260: --
3261: -- Bug#2789704 Added Exception Handling
3262: BEGIN
3263: get_per_sit_perf_appraisal(l_ghr_pa_requests_rec.person_id
3264: ,l_ghr_pa_requests_rec.effective_date
3265: ,l_ghr_cpdf_temp_rec.rating_of_record_level
3266: ,l_ghr_cpdf_temp_rec.rating_of_record_pattern
3267: ,l_ghr_cpdf_temp_rec.rating_of_record_period_ends
3260: --
3261: -- Bug#2789704 Added Exception Handling
3262: BEGIN
3263: get_per_sit_perf_appraisal(l_ghr_pa_requests_rec.person_id
3264: ,l_ghr_pa_requests_rec.effective_date
3265: ,l_ghr_cpdf_temp_rec.rating_of_record_level
3266: ,l_ghr_cpdf_temp_rec.rating_of_record_pattern
3267: ,l_ghr_cpdf_temp_rec.rating_of_record_period_ends
3268: ,l_ghr_cpdf_temp_rec.rating_of_record_period_starts); -- format in report
3269: EXCEPTION
3270: WHEN OTHERS THEN
3271: l_message_name := 'get_per_sit_perf_apprisal';
3272: l_log_text := 'Error in fetching Performance Apprisal details for pa_request_id: '||
3273: to_char(l_ghr_pa_requests_rec.pa_request_id) ||
3274: ' ; SSN/employee last name' ||
3275: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3276: l_ghr_pa_Requests_rec.employee_last_name ||
3277: ' ; first NOAC/Second NOAC: '||
3271: l_message_name := 'get_per_sit_perf_apprisal';
3272: l_log_text := 'Error in fetching Performance Apprisal details for pa_request_id: '||
3273: to_char(l_ghr_pa_requests_rec.pa_request_id) ||
3274: ' ; SSN/employee last name' ||
3275: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3276: l_ghr_pa_Requests_rec.employee_last_name ||
3277: ' ; first NOAC/Second NOAC: '||
3278: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3279: l_ghr_pa_requests_rec.second_noa_code ||
3272: l_log_text := 'Error in fetching Performance Apprisal details for pa_request_id: '||
3273: to_char(l_ghr_pa_requests_rec.pa_request_id) ||
3274: ' ; SSN/employee last name' ||
3275: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3276: l_ghr_pa_Requests_rec.employee_last_name ||
3277: ' ; first NOAC/Second NOAC: '||
3278: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3279: l_ghr_pa_requests_rec.second_noa_code ||
3280: ' ; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3274: ' ; SSN/employee last name' ||
3275: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3276: l_ghr_pa_Requests_rec.employee_last_name ||
3277: ' ; first NOAC/Second NOAC: '||
3278: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3279: l_ghr_pa_requests_rec.second_noa_code ||
3280: ' ; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3281: Raise EHRI_DYNRPT_ERROR;
3282:
3275: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3276: l_ghr_pa_Requests_rec.employee_last_name ||
3277: ' ; first NOAC/Second NOAC: '||
3278: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3279: l_ghr_pa_requests_rec.second_noa_code ||
3280: ' ; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3281: Raise EHRI_DYNRPT_ERROR;
3282:
3283: END;
3286: -- 3.5) Get PAR Extra Info Noa specific
3287: --
3288: -- Bug#2789704 Added Exception Handling
3289: BEGIN
3290: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
3291: ,l_ghr_pa_requests_rec.first_noa_id
3292: ,l_ghr_pa_requests_rec.second_noa_id
3293: ,l_ghr_pa_requests_rec.noa_family_code
3294: ,l_ghr_pa_requests_rec.person_id
3287: --
3288: -- Bug#2789704 Added Exception Handling
3289: BEGIN
3290: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
3291: ,l_ghr_pa_requests_rec.first_noa_id
3292: ,l_ghr_pa_requests_rec.second_noa_id
3293: ,l_ghr_pa_requests_rec.noa_family_code
3294: ,l_ghr_pa_requests_rec.person_id
3295: ,l_ghr_pa_requests_rec.effective_date
3288: -- Bug#2789704 Added Exception Handling
3289: BEGIN
3290: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
3291: ,l_ghr_pa_requests_rec.first_noa_id
3292: ,l_ghr_pa_requests_rec.second_noa_id
3293: ,l_ghr_pa_requests_rec.noa_family_code
3294: ,l_ghr_pa_requests_rec.person_id
3295: ,l_ghr_pa_requests_rec.effective_date
3296: ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
3289: BEGIN
3290: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
3291: ,l_ghr_pa_requests_rec.first_noa_id
3292: ,l_ghr_pa_requests_rec.second_noa_id
3293: ,l_ghr_pa_requests_rec.noa_family_code
3294: ,l_ghr_pa_requests_rec.person_id
3295: ,l_ghr_pa_requests_rec.effective_date
3296: ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
3297: ,l_ghr_cpdf_temp_rec.frozen_service -- no format assumed yymm?
3290: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
3291: ,l_ghr_pa_requests_rec.first_noa_id
3292: ,l_ghr_pa_requests_rec.second_noa_id
3293: ,l_ghr_pa_requests_rec.noa_family_code
3294: ,l_ghr_pa_requests_rec.person_id
3295: ,l_ghr_pa_requests_rec.effective_date
3296: ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
3297: ,l_ghr_cpdf_temp_rec.frozen_service -- no format assumed yymm?
3298: ,l_ghr_cpdf_temp_rec.from_retirement_coverage -- previous retirement coverage
3291: ,l_ghr_pa_requests_rec.first_noa_id
3292: ,l_ghr_pa_requests_rec.second_noa_id
3293: ,l_ghr_pa_requests_rec.noa_family_code
3294: ,l_ghr_pa_requests_rec.person_id
3295: ,l_ghr_pa_requests_rec.effective_date
3296: ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
3297: ,l_ghr_cpdf_temp_rec.frozen_service -- no format assumed yymm?
3298: ,l_ghr_cpdf_temp_rec.from_retirement_coverage -- previous retirement coverage
3299: ,l_ghr_cpdf_temp_rec.race_national_origin
3334: EXCEPTION
3335: WHEN OTHERS THEN
3336: l_message_name := 'get_par_ei_noac';
3337: l_log_text := 'Error in fetching PA Record Extra Information for pa_request_id: '||
3338: l_ghr_pa_requests_rec.pa_request_id ||
3339: ' ; SSN/employee last name' ||
3340: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3341: l_ghr_pa_Requests_rec.employee_last_name ||
3342: ' ; first NOAC/Second NOAC: '||
3336: l_message_name := 'get_par_ei_noac';
3337: l_log_text := 'Error in fetching PA Record Extra Information for pa_request_id: '||
3338: l_ghr_pa_requests_rec.pa_request_id ||
3339: ' ; SSN/employee last name' ||
3340: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3341: l_ghr_pa_Requests_rec.employee_last_name ||
3342: ' ; first NOAC/Second NOAC: '||
3343: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3344: l_ghr_pa_requests_rec.second_noa_code ||
3337: l_log_text := 'Error in fetching PA Record Extra Information for pa_request_id: '||
3338: l_ghr_pa_requests_rec.pa_request_id ||
3339: ' ; SSN/employee last name' ||
3340: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3341: l_ghr_pa_Requests_rec.employee_last_name ||
3342: ' ; first NOAC/Second NOAC: '||
3343: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3344: l_ghr_pa_requests_rec.second_noa_code ||
3345: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3339: ' ; SSN/employee last name' ||
3340: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3341: l_ghr_pa_Requests_rec.employee_last_name ||
3342: ' ; first NOAC/Second NOAC: '||
3343: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3344: l_ghr_pa_requests_rec.second_noa_code ||
3345: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3346:
3347: Raise EHRI_DYNRPT_ERROR;
3340: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3341: l_ghr_pa_Requests_rec.employee_last_name ||
3342: ' ; first NOAC/Second NOAC: '||
3343: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3344: l_ghr_pa_requests_rec.second_noa_code ||
3345: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3346:
3347: Raise EHRI_DYNRPT_ERROR;
3348: END;
3348: END;
3349: -- Bug#2789704 Added Exception Handling
3350:
3351: -- Bug# 1375342
3352: IF (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' OR
3353: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3354: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')) THEN
3355:
3356: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3349: -- Bug#2789704 Added Exception Handling
3350:
3351: -- Bug# 1375342
3352: IF (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' OR
3353: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3354: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')) THEN
3355:
3356: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3357: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3350:
3351: -- Bug# 1375342
3352: IF (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' OR
3353: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3354: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')) THEN
3355:
3356: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3357: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3358: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3371: -- get current appointment auth codes.
3372: -- Bug#2789704 Added Exception Handling
3373: BEGIN
3374: ghr_sf52_pre_update.get_auth_codes
3375: (p_pa_req_rec => l_ghr_pa_requests_rec
3376: ,p_auth_code1 => l_ghr_cpdf_temp_rec.current_appointment_auth1
3377: ,p_auth_code2 => l_ghr_cpdf_temp_rec.current_appointment_auth2);
3378: EXCEPTION
3379: WHEN OTHERS THEN
3378: EXCEPTION
3379: WHEN OTHERS THEN
3380: l_message_name := 'get_auth_codes';
3381: l_log_text := 'Error in fetching Current Appointment Authority Codes for pa_request_id: '||
3382: l_ghr_pa_requests_rec.pa_request_id ||
3383: ' ; SSN/employee last name' ||
3384: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3385: l_ghr_pa_Requests_rec.employee_last_name ||
3386: ' ; first NOAC/Second NOAC: '||
3380: l_message_name := 'get_auth_codes';
3381: l_log_text := 'Error in fetching Current Appointment Authority Codes for pa_request_id: '||
3382: l_ghr_pa_requests_rec.pa_request_id ||
3383: ' ; SSN/employee last name' ||
3384: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3385: l_ghr_pa_Requests_rec.employee_last_name ||
3386: ' ; first NOAC/Second NOAC: '||
3387: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3388: l_ghr_pa_requests_rec.second_noa_code ||
3381: l_log_text := 'Error in fetching Current Appointment Authority Codes for pa_request_id: '||
3382: l_ghr_pa_requests_rec.pa_request_id ||
3383: ' ; SSN/employee last name' ||
3384: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3385: l_ghr_pa_Requests_rec.employee_last_name ||
3386: ' ; first NOAC/Second NOAC: '||
3387: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3388: l_ghr_pa_requests_rec.second_noa_code ||
3389: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3383: ' ; SSN/employee last name' ||
3384: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3385: l_ghr_pa_Requests_rec.employee_last_name ||
3386: ' ; first NOAC/Second NOAC: '||
3387: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3388: l_ghr_pa_requests_rec.second_noa_code ||
3389: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3390:
3391: Raise EHRI_DYNRPT_ERROR;
3384: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3385: l_ghr_pa_Requests_rec.employee_last_name ||
3386: ' ; first NOAC/Second NOAC: '||
3387: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3388: l_ghr_pa_requests_rec.second_noa_code ||
3389: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3390:
3391: Raise EHRI_DYNRPT_ERROR;
3392: END;
3393: -- Bug#2789704 Added Exception Handling
3394: --
3395: -- 3.6) Get PRIOR Work Schedule and Pay Rate Determinant
3396: --
3397: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3398: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')
3399: OR
3400: l_ghr_pa_requests_rec.first_noa_code LIKE '2%'
3401: )
3394: --
3395: -- 3.6) Get PRIOR Work Schedule and Pay Rate Determinant
3396: --
3397: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3398: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')
3399: OR
3400: l_ghr_pa_requests_rec.first_noa_code LIKE '2%'
3401: )
3402: THEN
3396: --
3397: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3398: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')
3399: OR
3400: l_ghr_pa_requests_rec.first_noa_code LIKE '2%'
3401: )
3402: THEN
3403:
3404: -- Bug#2789704 Added Exception Handling
3402: THEN
3403:
3404: -- Bug#2789704 Added Exception Handling
3405: BEGIN
3406: get_prior_ws_prd_ds (l_ghr_pa_requests_rec.pa_request_id
3407: ,l_ghr_pa_requests_rec.altered_pa_request_id
3408: ,l_ghr_pa_requests_rec.first_noa_id
3409: ,l_ghr_pa_requests_rec.second_noa_id
3410: ,l_ghr_pa_requests_rec.person_id
3403:
3404: -- Bug#2789704 Added Exception Handling
3405: BEGIN
3406: get_prior_ws_prd_ds (l_ghr_pa_requests_rec.pa_request_id
3407: ,l_ghr_pa_requests_rec.altered_pa_request_id
3408: ,l_ghr_pa_requests_rec.first_noa_id
3409: ,l_ghr_pa_requests_rec.second_noa_id
3410: ,l_ghr_pa_requests_rec.person_id
3411: ,l_ghr_pa_requests_rec.employee_assignment_id
3404: -- Bug#2789704 Added Exception Handling
3405: BEGIN
3406: get_prior_ws_prd_ds (l_ghr_pa_requests_rec.pa_request_id
3407: ,l_ghr_pa_requests_rec.altered_pa_request_id
3408: ,l_ghr_pa_requests_rec.first_noa_id
3409: ,l_ghr_pa_requests_rec.second_noa_id
3410: ,l_ghr_pa_requests_rec.person_id
3411: ,l_ghr_pa_requests_rec.employee_assignment_id
3412: ,l_ghr_pa_requests_rec.from_position_id
3405: BEGIN
3406: get_prior_ws_prd_ds (l_ghr_pa_requests_rec.pa_request_id
3407: ,l_ghr_pa_requests_rec.altered_pa_request_id
3408: ,l_ghr_pa_requests_rec.first_noa_id
3409: ,l_ghr_pa_requests_rec.second_noa_id
3410: ,l_ghr_pa_requests_rec.person_id
3411: ,l_ghr_pa_requests_rec.employee_assignment_id
3412: ,l_ghr_pa_requests_rec.from_position_id
3413: ,l_ghr_pa_requests_rec.effective_date
3406: get_prior_ws_prd_ds (l_ghr_pa_requests_rec.pa_request_id
3407: ,l_ghr_pa_requests_rec.altered_pa_request_id
3408: ,l_ghr_pa_requests_rec.first_noa_id
3409: ,l_ghr_pa_requests_rec.second_noa_id
3410: ,l_ghr_pa_requests_rec.person_id
3411: ,l_ghr_pa_requests_rec.employee_assignment_id
3412: ,l_ghr_pa_requests_rec.from_position_id
3413: ,l_ghr_pa_requests_rec.effective_date
3414: ,l_ghr_pa_requests_rec.status
3407: ,l_ghr_pa_requests_rec.altered_pa_request_id
3408: ,l_ghr_pa_requests_rec.first_noa_id
3409: ,l_ghr_pa_requests_rec.second_noa_id
3410: ,l_ghr_pa_requests_rec.person_id
3411: ,l_ghr_pa_requests_rec.employee_assignment_id
3412: ,l_ghr_pa_requests_rec.from_position_id
3413: ,l_ghr_pa_requests_rec.effective_date
3414: ,l_ghr_pa_requests_rec.status
3415: ,l_ghr_cpdf_temp_rec.from_work_schedule
3408: ,l_ghr_pa_requests_rec.first_noa_id
3409: ,l_ghr_pa_requests_rec.second_noa_id
3410: ,l_ghr_pa_requests_rec.person_id
3411: ,l_ghr_pa_requests_rec.employee_assignment_id
3412: ,l_ghr_pa_requests_rec.from_position_id
3413: ,l_ghr_pa_requests_rec.effective_date
3414: ,l_ghr_pa_requests_rec.status
3415: ,l_ghr_cpdf_temp_rec.from_work_schedule
3416: ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
3409: ,l_ghr_pa_requests_rec.second_noa_id
3410: ,l_ghr_pa_requests_rec.person_id
3411: ,l_ghr_pa_requests_rec.employee_assignment_id
3412: ,l_ghr_pa_requests_rec.from_position_id
3413: ,l_ghr_pa_requests_rec.effective_date
3414: ,l_ghr_pa_requests_rec.status
3415: ,l_ghr_cpdf_temp_rec.from_work_schedule
3416: ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
3417: ,l_ghr_cpdf_temp_rec.from_duty_station_code);
3410: ,l_ghr_pa_requests_rec.person_id
3411: ,l_ghr_pa_requests_rec.employee_assignment_id
3412: ,l_ghr_pa_requests_rec.from_position_id
3413: ,l_ghr_pa_requests_rec.effective_date
3414: ,l_ghr_pa_requests_rec.status
3415: ,l_ghr_cpdf_temp_rec.from_work_schedule
3416: ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
3417: ,l_ghr_cpdf_temp_rec.from_duty_station_code);
3418:
3415: ,l_ghr_cpdf_temp_rec.from_work_schedule
3416: ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
3417: ,l_ghr_cpdf_temp_rec.from_duty_station_code);
3418:
3419: IF (l_ghr_pa_requests_rec.first_noa_code IN ('817') OR
3420: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3421: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')= '817')
3422: )
3423: THEN
3416: ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
3417: ,l_ghr_cpdf_temp_rec.from_duty_station_code);
3418:
3419: IF (l_ghr_pa_requests_rec.first_noa_code IN ('817') OR
3420: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3421: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')= '817')
3422: )
3423: THEN
3424: l_ghr_cpdf_temp_rec.from_work_schedule := NULL;
3417: ,l_ghr_cpdf_temp_rec.from_duty_station_code);
3418:
3419: IF (l_ghr_pa_requests_rec.first_noa_code IN ('817') OR
3420: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3421: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')= '817')
3422: )
3423: THEN
3424: l_ghr_cpdf_temp_rec.from_work_schedule := NULL;
3425: l_ghr_cpdf_temp_rec.from_pay_rate_determinant := NULL;
3429: EXCEPTION
3430: WHEN OTHERS THEN
3431: l_message_name := 'get_prior_ws_prd_ds';
3432: l_log_text := 'Error in fetching prior work schedule,prg,duty station for pa_request_id: '||
3433: l_ghr_pa_requests_rec.pa_request_id ||
3434: ' ; SSN/employee last name' ||
3435: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3436: l_ghr_pa_Requests_rec.employee_last_name ||
3437: ' ; first NOAC/Second NOAC: '||
3431: l_message_name := 'get_prior_ws_prd_ds';
3432: l_log_text := 'Error in fetching prior work schedule,prg,duty station for pa_request_id: '||
3433: l_ghr_pa_requests_rec.pa_request_id ||
3434: ' ; SSN/employee last name' ||
3435: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3436: l_ghr_pa_Requests_rec.employee_last_name ||
3437: ' ; first NOAC/Second NOAC: '||
3438: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3439: l_ghr_pa_requests_rec.second_noa_code ||
3432: l_log_text := 'Error in fetching prior work schedule,prg,duty station for pa_request_id: '||
3433: l_ghr_pa_requests_rec.pa_request_id ||
3434: ' ; SSN/employee last name' ||
3435: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3436: l_ghr_pa_Requests_rec.employee_last_name ||
3437: ' ; first NOAC/Second NOAC: '||
3438: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3439: l_ghr_pa_requests_rec.second_noa_code ||
3440: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3434: ' ; SSN/employee last name' ||
3435: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3436: l_ghr_pa_Requests_rec.employee_last_name ||
3437: ' ; first NOAC/Second NOAC: '||
3438: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3439: l_ghr_pa_requests_rec.second_noa_code ||
3440: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3441:
3442: Raise EHRI_DYNRPT_ERROR;
3435: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3436: l_ghr_pa_Requests_rec.employee_last_name ||
3437: ' ; first NOAC/Second NOAC: '||
3438: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3439: l_ghr_pa_requests_rec.second_noa_code ||
3440: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3441:
3442: Raise EHRI_DYNRPT_ERROR;
3443: END;
3461:
3462: END IF;
3463: -- Added IF Condition to fix bug#3231946
3464: IF get_loc_pay_area_code(p_duty_station_code => l_ghr_cpdf_temp_rec.from_duty_station_code,
3465: p_effective_date => l_ghr_pa_requests_rec.effective_date) = '99'
3466: AND l_ghr_pa_requests_rec.from_locality_adj = 0 THEN
3467: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3468: END IF;
3469: END IF;
3462: END IF;
3463: -- Added IF Condition to fix bug#3231946
3464: IF get_loc_pay_area_code(p_duty_station_code => l_ghr_cpdf_temp_rec.from_duty_station_code,
3465: p_effective_date => l_ghr_pa_requests_rec.effective_date) = '99'
3466: AND l_ghr_pa_requests_rec.from_locality_adj = 0 THEN
3467: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3468: END IF;
3469: END IF;
3470: --
3471: -- 3.7) Get prior ssn if it is being corrected.
3472: --
3473: -- Bug#2789704 Added Exception Handling
3474: BEGIN
3475: get_prev_ssn (l_ghr_pa_requests_rec.altered_pa_request_id
3476: ,l_ghr_pa_requests_rec.employee_national_identifier
3477: ,l_ghr_pa_requests_rec.noa_family_code
3478: ,l_ghr_cpdf_temp_rec.from_national_identifier);
3479: EXCEPTION
3472: --
3473: -- Bug#2789704 Added Exception Handling
3474: BEGIN
3475: get_prev_ssn (l_ghr_pa_requests_rec.altered_pa_request_id
3476: ,l_ghr_pa_requests_rec.employee_national_identifier
3477: ,l_ghr_pa_requests_rec.noa_family_code
3478: ,l_ghr_cpdf_temp_rec.from_national_identifier);
3479: EXCEPTION
3480: WHEN OTHERS THEN
3473: -- Bug#2789704 Added Exception Handling
3474: BEGIN
3475: get_prev_ssn (l_ghr_pa_requests_rec.altered_pa_request_id
3476: ,l_ghr_pa_requests_rec.employee_national_identifier
3477: ,l_ghr_pa_requests_rec.noa_family_code
3478: ,l_ghr_cpdf_temp_rec.from_national_identifier);
3479: EXCEPTION
3480: WHEN OTHERS THEN
3481: l_message_name := 'get_prev_ssn';
3479: EXCEPTION
3480: WHEN OTHERS THEN
3481: l_message_name := 'get_prev_ssn';
3482: l_log_text := 'Error in fetching SSN for pa_request_id: '||
3483: l_ghr_pa_requests_rec.pa_request_id ||
3484: ' ; SSN/employee last name' ||
3485: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3486: l_ghr_pa_Requests_rec.employee_last_name ||
3487: ' ; first NOAC/Second NOAC: '||
3481: l_message_name := 'get_prev_ssn';
3482: l_log_text := 'Error in fetching SSN for pa_request_id: '||
3483: l_ghr_pa_requests_rec.pa_request_id ||
3484: ' ; SSN/employee last name' ||
3485: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3486: l_ghr_pa_Requests_rec.employee_last_name ||
3487: ' ; first NOAC/Second NOAC: '||
3488: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3489: l_ghr_pa_requests_rec.second_noa_code ||
3482: l_log_text := 'Error in fetching SSN for pa_request_id: '||
3483: l_ghr_pa_requests_rec.pa_request_id ||
3484: ' ; SSN/employee last name' ||
3485: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3486: l_ghr_pa_Requests_rec.employee_last_name ||
3487: ' ; first NOAC/Second NOAC: '||
3488: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3489: l_ghr_pa_requests_rec.second_noa_code ||
3490: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3484: ' ; SSN/employee last name' ||
3485: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3486: l_ghr_pa_Requests_rec.employee_last_name ||
3487: ' ; first NOAC/Second NOAC: '||
3488: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3489: l_ghr_pa_requests_rec.second_noa_code ||
3490: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3491:
3492: Raise EHRI_DYNRPT_ERROR;
3485: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3486: l_ghr_pa_Requests_rec.employee_last_name ||
3487: ' ; first NOAC/Second NOAC: '||
3488: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3489: l_ghr_pa_requests_rec.second_noa_code ||
3490: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3491:
3492: Raise EHRI_DYNRPT_ERROR;
3493: END;
3497:
3498: --
3499: -- EHRI changes
3500: --
3501: IF ((l_ghr_pa_requests_rec.first_noa_code ='002'
3502: AND
3503: (NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '1%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '2%' or
3504: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '3%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '4%' or
3505: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '5%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '6%' or
3499: -- EHRI changes
3500: --
3501: IF ((l_ghr_pa_requests_rec.first_noa_code ='002'
3502: AND
3503: (NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '1%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '2%' or
3504: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '3%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '4%' or
3505: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '5%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '6%' or
3506: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '7%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '8%' )
3507: ) -- for 002
3500: --
3501: IF ((l_ghr_pa_requests_rec.first_noa_code ='002'
3502: AND
3503: (NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '1%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '2%' or
3504: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '3%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '4%' or
3505: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '5%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '6%' or
3506: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '7%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '8%' )
3507: ) -- for 002
3508: OR
3501: IF ((l_ghr_pa_requests_rec.first_noa_code ='002'
3502: AND
3503: (NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '1%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '2%' or
3504: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '3%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '4%' or
3505: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '5%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '6%' or
3506: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '7%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '8%' )
3507: ) -- for 002
3508: OR
3509: (l_ghr_pa_requests_rec.first_noa_code like '1%' or l_ghr_pa_requests_rec.first_noa_code like '2%' or
3502: AND
3503: (NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '1%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '2%' or
3504: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '3%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '4%' or
3505: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '5%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '6%' or
3506: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '7%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '8%' )
3507: ) -- for 002
3508: OR
3509: (l_ghr_pa_requests_rec.first_noa_code like '1%' or l_ghr_pa_requests_rec.first_noa_code like '2%' or
3510: l_ghr_pa_requests_rec.first_noa_code like '3%' or l_ghr_pa_requests_rec.first_noa_code like '4%' or
3505: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '5%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '6%' or
3506: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '7%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '8%' )
3507: ) -- for 002
3508: OR
3509: (l_ghr_pa_requests_rec.first_noa_code like '1%' or l_ghr_pa_requests_rec.first_noa_code like '2%' or
3510: l_ghr_pa_requests_rec.first_noa_code like '3%' or l_ghr_pa_requests_rec.first_noa_code like '4%' or
3511: l_ghr_pa_requests_rec.first_noa_code like '5%' OR l_ghr_pa_requests_rec.first_noa_code like '6%' or
3512: l_ghr_pa_requests_rec.first_noa_code like '7%' OR l_ghr_pa_requests_rec.first_noa_code like '8%'
3513: )
3506: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '7%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '8%' )
3507: ) -- for 002
3508: OR
3509: (l_ghr_pa_requests_rec.first_noa_code like '1%' or l_ghr_pa_requests_rec.first_noa_code like '2%' or
3510: l_ghr_pa_requests_rec.first_noa_code like '3%' or l_ghr_pa_requests_rec.first_noa_code like '4%' or
3511: l_ghr_pa_requests_rec.first_noa_code like '5%' OR l_ghr_pa_requests_rec.first_noa_code like '6%' or
3512: l_ghr_pa_requests_rec.first_noa_code like '7%' OR l_ghr_pa_requests_rec.first_noa_code like '8%'
3513: )
3514: )
3507: ) -- for 002
3508: OR
3509: (l_ghr_pa_requests_rec.first_noa_code like '1%' or l_ghr_pa_requests_rec.first_noa_code like '2%' or
3510: l_ghr_pa_requests_rec.first_noa_code like '3%' or l_ghr_pa_requests_rec.first_noa_code like '4%' or
3511: l_ghr_pa_requests_rec.first_noa_code like '5%' OR l_ghr_pa_requests_rec.first_noa_code like '6%' or
3512: l_ghr_pa_requests_rec.first_noa_code like '7%' OR l_ghr_pa_requests_rec.first_noa_code like '8%'
3513: )
3514: )
3515: THEN
3508: OR
3509: (l_ghr_pa_requests_rec.first_noa_code like '1%' or l_ghr_pa_requests_rec.first_noa_code like '2%' or
3510: l_ghr_pa_requests_rec.first_noa_code like '3%' or l_ghr_pa_requests_rec.first_noa_code like '4%' or
3511: l_ghr_pa_requests_rec.first_noa_code like '5%' OR l_ghr_pa_requests_rec.first_noa_code like '6%' or
3512: l_ghr_pa_requests_rec.first_noa_code like '7%' OR l_ghr_pa_requests_rec.first_noa_code like '8%'
3513: )
3514: )
3515: THEN
3516: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3514: )
3515: THEN
3516: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3517: -- Bug 5010784
3518: -- l_ghr_cpdf_temp_rec.bargaining_unit_status := l_ghr_pa_requests_rec.bargaining_unit_status;
3519: l_ghr_cpdf_temp_rec.bargaining_unit_status := SUBSTR(l_ghr_pa_requests_rec.bargaining_unit_status,
3520: LENGTH(l_ghr_pa_requests_rec.bargaining_unit_status)-3);
3521: -- End Bug 5010784
3522:
3515: THEN
3516: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3517: -- Bug 5010784
3518: -- l_ghr_cpdf_temp_rec.bargaining_unit_status := l_ghr_pa_requests_rec.bargaining_unit_status;
3519: l_ghr_cpdf_temp_rec.bargaining_unit_status := SUBSTR(l_ghr_pa_requests_rec.bargaining_unit_status,
3520: LENGTH(l_ghr_pa_requests_rec.bargaining_unit_status)-3);
3521: -- End Bug 5010784
3522:
3523: -- adding code for 817 NOAC
3516: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3517: -- Bug 5010784
3518: -- l_ghr_cpdf_temp_rec.bargaining_unit_status := l_ghr_pa_requests_rec.bargaining_unit_status;
3519: l_ghr_cpdf_temp_rec.bargaining_unit_status := SUBSTR(l_ghr_pa_requests_rec.bargaining_unit_status,
3520: LENGTH(l_ghr_pa_requests_rec.bargaining_unit_status)-3);
3521: -- End Bug 5010784
3522:
3523: -- adding code for 817 NOAC
3524: IF (l_ghr_pa_requests_rec.first_noa_code='817' or l_ghr_pa_requests_rec.second_noa_code='817') THEN
3520: LENGTH(l_ghr_pa_requests_rec.bargaining_unit_status)-3);
3521: -- End Bug 5010784
3522:
3523: -- adding code for 817 NOAC
3524: IF (l_ghr_pa_requests_rec.first_noa_code='817' or l_ghr_pa_requests_rec.second_noa_code='817') THEN
3525: l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
3526: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3527: l_ghr_cpdf_temp_rec.to_national_identifier
3528: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3522:
3523: -- adding code for 817 NOAC
3524: IF (l_ghr_pa_requests_rec.first_noa_code='817' or l_ghr_pa_requests_rec.second_noa_code='817') THEN
3525: l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
3526: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3527: l_ghr_cpdf_temp_rec.to_national_identifier
3528: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3529: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3530: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3524: IF (l_ghr_pa_requests_rec.first_noa_code='817' or l_ghr_pa_requests_rec.second_noa_code='817') THEN
3525: l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
3526: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3527: l_ghr_cpdf_temp_rec.to_national_identifier
3528: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3529: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3530: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3531: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3532: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3525: l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
3526: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3527: l_ghr_cpdf_temp_rec.to_national_identifier
3528: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3529: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3530: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3531: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3532: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3533: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3526: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3527: l_ghr_cpdf_temp_rec.to_national_identifier
3528: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3529: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3530: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3531: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3532: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3533: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3534: -- l_ghr_cpdf_temp_rec.employee_date_of_birth := NULL;
3527: l_ghr_cpdf_temp_rec.to_national_identifier
3528: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3529: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3530: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3531: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3532: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3533: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3534: -- l_ghr_cpdf_temp_rec.employee_date_of_birth := NULL;
3535: -- GOTO end_par_loop;
3528: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3529: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3530: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3531: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3532: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3533: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3534: -- l_ghr_cpdf_temp_rec.employee_date_of_birth := NULL;
3535: -- GOTO end_par_loop;
3536: END IF;
3534: -- l_ghr_cpdf_temp_rec.employee_date_of_birth := NULL;
3535: -- GOTO end_par_loop;
3536: END IF;
3537:
3538: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3539: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' AND
3540: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3541: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3542: )
3535: -- GOTO end_par_loop;
3536: END IF;
3537:
3538: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3539: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' AND
3540: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3541: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3542: )
3543: ) AND
3536: END IF;
3537:
3538: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3539: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' AND
3540: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3541: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3542: )
3543: ) AND
3544: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3537:
3538: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3539: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' AND
3540: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3541: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3542: )
3543: ) AND
3544: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3545: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3540: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3541: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3542: )
3543: ) AND
3544: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3545: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3546: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3547: )
3548: ) THEN
3541: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3542: )
3543: ) AND
3544: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3545: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3546: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3547: )
3548: ) THEN
3549:
3542: )
3543: ) AND
3544: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3545: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3546: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3547: )
3548: ) THEN
3549:
3550: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.to_position_title;
3546: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3547: )
3548: ) THEN
3549:
3550: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.to_position_title;
3551: l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3552:
3553: /* FOR pos_org IN cur_pos_org(l_ghr_pa_requests_rec.to_organization_id,
3554: l_ghr_pa_requests_rec.effective_date)
3547: )
3548: ) THEN
3549:
3550: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.to_position_title;
3551: l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3552:
3553: /* FOR pos_org IN cur_pos_org(l_ghr_pa_requests_rec.to_organization_id,
3554: l_ghr_pa_requests_rec.effective_date)
3555: LOOP
3549:
3550: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.to_position_title;
3551: l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3552:
3553: /* FOR pos_org IN cur_pos_org(l_ghr_pa_requests_rec.to_organization_id,
3554: l_ghr_pa_requests_rec.effective_date)
3555: LOOP
3556: l_ghr_cpdf_temp_rec.POSITION_ORG := pos_org.name;
3557: END LOOP;
3550: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.to_position_title;
3551: l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3552:
3553: /* FOR pos_org IN cur_pos_org(l_ghr_pa_requests_rec.to_organization_id,
3554: l_ghr_pa_requests_rec.effective_date)
3555: LOOP
3556: l_ghr_cpdf_temp_rec.POSITION_ORG := pos_org.name;
3557: END LOOP;
3558:
3556: l_ghr_cpdf_temp_rec.POSITION_ORG := pos_org.name;
3557: END LOOP;
3558:
3559: */
3560: l_ghr_cpdf_temp_rec.POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.to_position_org_line1 ||
3561: ' ' || l_ghr_pa_requests_rec.to_position_org_line2 ||
3562: ' ' || l_ghr_pa_requests_rec.to_position_org_line3 ||
3563: ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3564: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3557: END LOOP;
3558:
3559: */
3560: l_ghr_cpdf_temp_rec.POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.to_position_org_line1 ||
3561: ' ' || l_ghr_pa_requests_rec.to_position_org_line2 ||
3562: ' ' || l_ghr_pa_requests_rec.to_position_org_line3 ||
3563: ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3564: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3565: ' ' || l_ghr_pa_requests_rec.to_position_org_line6,1,500);
3558:
3559: */
3560: l_ghr_cpdf_temp_rec.POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.to_position_org_line1 ||
3561: ' ' || l_ghr_pa_requests_rec.to_position_org_line2 ||
3562: ' ' || l_ghr_pa_requests_rec.to_position_org_line3 ||
3563: ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3564: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3565: ' ' || l_ghr_pa_requests_rec.to_position_org_line6,1,500);
3566: ELSE
3559: */
3560: l_ghr_cpdf_temp_rec.POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.to_position_org_line1 ||
3561: ' ' || l_ghr_pa_requests_rec.to_position_org_line2 ||
3562: ' ' || l_ghr_pa_requests_rec.to_position_org_line3 ||
3563: ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3564: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3565: ' ' || l_ghr_pa_requests_rec.to_position_org_line6,1,500);
3566: ELSE
3567: l_ghr_cpdf_temp_rec.position_title := NULL;
3560: l_ghr_cpdf_temp_rec.POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.to_position_org_line1 ||
3561: ' ' || l_ghr_pa_requests_rec.to_position_org_line2 ||
3562: ' ' || l_ghr_pa_requests_rec.to_position_org_line3 ||
3563: ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3564: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3565: ' ' || l_ghr_pa_requests_rec.to_position_org_line6,1,500);
3566: ELSE
3567: l_ghr_cpdf_temp_rec.position_title := NULL;
3568: l_ghr_cpdf_temp_rec.position_number := NULL;
3561: ' ' || l_ghr_pa_requests_rec.to_position_org_line2 ||
3562: ' ' || l_ghr_pa_requests_rec.to_position_org_line3 ||
3563: ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3564: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3565: ' ' || l_ghr_pa_requests_rec.to_position_org_line6,1,500);
3566: ELSE
3567: l_ghr_cpdf_temp_rec.position_title := NULL;
3568: l_ghr_cpdf_temp_rec.position_number := NULL;
3569: l_ghr_cpdf_temp_rec.POSITION_ORG := NULL;
3573: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3574: l_ghr_cpdf_temp_rec.frozen_service := NULL;
3575: END IF;
3576:
3577: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3578: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','818','819','825',
3579: '840','841','842','843','844','845','846','847','848','849','878','879')
3580: )
3581: OR
3574: l_ghr_cpdf_temp_rec.frozen_service := NULL;
3575: END IF;
3576:
3577: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3578: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','818','819','825',
3579: '840','841','842','843','844','845','846','847','848','849','878','879')
3580: )
3581: OR
3582: l_ghr_pa_requests_rec.first_noa_code in ('815','816','817','818','819','825',
3578: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','818','819','825',
3579: '840','841','842','843','844','845','846','847','848','849','878','879')
3580: )
3581: OR
3582: l_ghr_pa_requests_rec.first_noa_code in ('815','816','817','818','819','825',
3583: '840','841','842','843','844','845','846','847','848','849','878','879')
3584: )
3585: THEN
3586:
3583: '840','841','842','843','844','845','846','847','848','849','878','879')
3584: )
3585: THEN
3586:
3587: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='M' THEN
3588: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.award_amount;
3589: END IF;
3590: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3591: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3584: )
3585: THEN
3586:
3587: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='M' THEN
3588: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.award_amount;
3589: END IF;
3590: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3591: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3592: END IF;
3586:
3587: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='M' THEN
3588: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.award_amount;
3589: END IF;
3590: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3591: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3592: END IF;
3593: IF l_ghr_pa_requests_rec.award_percentage IS NOT NULL THEN
3594: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.award_percentage;
3587: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='M' THEN
3588: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.award_amount;
3589: END IF;
3590: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3591: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3592: END IF;
3593: IF l_ghr_pa_requests_rec.award_percentage IS NOT NULL THEN
3594: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.award_percentage;
3595: END IF;
3589: END IF;
3590: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3591: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3592: END IF;
3593: IF l_ghr_pa_requests_rec.award_percentage IS NOT NULL THEN
3594: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.award_percentage;
3595: END IF;
3596:
3597: /* COMMENTED this code as the similar code is added at line 2545 to resolve the
3590: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3591: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3592: END IF;
3593: IF l_ghr_pa_requests_rec.award_percentage IS NOT NULL THEN
3594: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.award_percentage;
3595: END IF;
3596:
3597: /* COMMENTED this code as the similar code is added at line 2545 to resolve the
3598: issue of non-printing the to total salary.
3599: -- Bug#5328177 Added NOA Codes 815,816 as they also belongs to the same category.
3600: -- Bug#3941541,5168358 Separation Incentive Changes.
3601: -- If the Award Dollars value is NOT NULL, Assume that 825 is processed as Award.
3602: -- Otherwise, it is processed as Incentive.
3603: IF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3604: l_ghr_pa_requests_rec.second_noa_code IN ('815','816', '825')) AND
3605: l_ghr_cpdf_temp_rec.award_dollars IS NULL THEN
3606: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.to_total_salary;
3607: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3600: -- Bug#3941541,5168358 Separation Incentive Changes.
3601: -- If the Award Dollars value is NOT NULL, Assume that 825 is processed as Award.
3602: -- Otherwise, it is processed as Incentive.
3603: IF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3604: l_ghr_pa_requests_rec.second_noa_code IN ('815','816', '825')) AND
3605: l_ghr_cpdf_temp_rec.award_dollars IS NULL THEN
3606: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.to_total_salary;
3607: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3608: END IF;
3602: -- Otherwise, it is processed as Incentive.
3603: IF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3604: l_ghr_pa_requests_rec.second_noa_code IN ('815','816', '825')) AND
3605: l_ghr_cpdf_temp_rec.award_dollars IS NULL THEN
3606: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.to_total_salary;
3607: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3608: END IF;
3609: -- End of Bug#3941541,5168358
3610: */
3616: END IF;
3617:
3618: -- New EHRI changes MADHURI
3619: IF (
3620: NOT( (l_ghr_pa_requests_rec.first_noa_code='002' and
3621: l_ghr_pa_requests_rec.second_noa_code LIKE '1%')
3622: OR
3623: l_ghr_pa_requests_rec.first_noa_code LIKE '1%' )
3624: AND
3617:
3618: -- New EHRI changes MADHURI
3619: IF (
3620: NOT( (l_ghr_pa_requests_rec.first_noa_code='002' and
3621: l_ghr_pa_requests_rec.second_noa_code LIKE '1%')
3622: OR
3623: l_ghr_pa_requests_rec.first_noa_code LIKE '1%' )
3624: AND
3625: NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3619: IF (
3620: NOT( (l_ghr_pa_requests_rec.first_noa_code='002' and
3621: l_ghr_pa_requests_rec.second_noa_code LIKE '1%')
3622: OR
3623: l_ghr_pa_requests_rec.first_noa_code LIKE '1%' )
3624: AND
3625: NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3626: l_ghr_pa_requests_rec.second_noa_code LIKE '2%')
3627: OR
3621: l_ghr_pa_requests_rec.second_noa_code LIKE '1%')
3622: OR
3623: l_ghr_pa_requests_rec.first_noa_code LIKE '1%' )
3624: AND
3625: NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3626: l_ghr_pa_requests_rec.second_noa_code LIKE '2%')
3627: OR
3628: l_ghr_pa_requests_rec.first_noa_code LIKE '2%' )
3629: )
3622: OR
3623: l_ghr_pa_requests_rec.first_noa_code LIKE '1%' )
3624: AND
3625: NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3626: l_ghr_pa_requests_rec.second_noa_code LIKE '2%')
3627: OR
3628: l_ghr_pa_requests_rec.first_noa_code LIKE '2%' )
3629: )
3630: THEN
3624: AND
3625: NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3626: l_ghr_pa_requests_rec.second_noa_code LIKE '2%')
3627: OR
3628: l_ghr_pa_requests_rec.first_noa_code LIKE '2%' )
3629: )
3630: THEN
3631:
3632: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3628: l_ghr_pa_requests_rec.first_noa_code LIKE '2%' )
3629: )
3630: THEN
3631:
3632: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3633: l_ghr_pa_requests_rec.second_noa_code = '780')
3634: OR
3635: l_ghr_pa_requests_rec.first_noa_code='780'
3636: ) THEN
3629: )
3630: THEN
3631:
3632: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3633: l_ghr_pa_requests_rec.second_noa_code = '780')
3634: OR
3635: l_ghr_pa_requests_rec.first_noa_code='780'
3636: ) THEN
3637: --
3631:
3632: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3633: l_ghr_pa_requests_rec.second_noa_code = '780')
3634: OR
3635: l_ghr_pa_requests_rec.first_noa_code='780'
3636: ) THEN
3637: --
3638: -- Prior Names ONLY FOR NAME CHANGE
3639:
3637: --
3638: -- Prior Names ONLY FOR NAME CHANGE
3639:
3640:
3641: FOR prior_per_rec IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3642: (l_ghr_pa_requests_rec.effective_date-1) )
3643: LOOP
3644: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
3645: get_suffix_lname(prior_per_rec.last_name,
3638: -- Prior Names ONLY FOR NAME CHANGE
3639:
3640:
3641: FOR prior_per_rec IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3642: (l_ghr_pa_requests_rec.effective_date-1) )
3643: LOOP
3644: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
3645: get_suffix_lname(prior_per_rec.last_name,
3646: l_ghr_pa_requests_rec.effective_date-1,
3642: (l_ghr_pa_requests_rec.effective_date-1) )
3643: LOOP
3644: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
3645: get_suffix_lname(prior_per_rec.last_name,
3646: l_ghr_pa_requests_rec.effective_date-1,
3647: l_suffix,
3648: l_last_name);
3649: l_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME := l_last_name;
3650: l_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME := prior_per_rec.first_name;
3654: END LOOP;
3655:
3656: END IF;
3657:
3658: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3659: l_ghr_pa_requests_rec.second_noa_code = '817')
3660: OR
3661: l_ghr_pa_requests_rec.first_noa_code='817'
3662: ) THEN
3655:
3656: END IF;
3657:
3658: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3659: l_ghr_pa_requests_rec.second_noa_code = '817')
3660: OR
3661: l_ghr_pa_requests_rec.first_noa_code='817'
3662: ) THEN
3663: -- Dont report these items for 817 action
3657:
3658: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3659: l_ghr_pa_requests_rec.second_noa_code = '817')
3660: OR
3661: l_ghr_pa_requests_rec.first_noa_code='817'
3662: ) THEN
3663: -- Dont report these items for 817 action
3664: --
3665: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := l_ghr_pa_requests_rec.from_position_title;
3661: l_ghr_pa_requests_rec.first_noa_code='817'
3662: ) THEN
3663: -- Dont report these items for 817 action
3664: --
3665: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := l_ghr_pa_requests_rec.from_position_title;
3666: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := l_ghr_pa_requests_rec.from_position_number;
3667: --
3668: /* FOR prior_pos_org IN cur_prior_pos_org(l_ghr_pa_requests_rec.from_position_id,
3669: (l_ghr_pa_requests_rec.effective_date-1) )
3662: ) THEN
3663: -- Dont report these items for 817 action
3664: --
3665: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := l_ghr_pa_requests_rec.from_position_title;
3666: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := l_ghr_pa_requests_rec.from_position_number;
3667: --
3668: /* FOR prior_pos_org IN cur_prior_pos_org(l_ghr_pa_requests_rec.from_position_id,
3669: (l_ghr_pa_requests_rec.effective_date-1) )
3670: LOOP
3664: --
3665: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := l_ghr_pa_requests_rec.from_position_title;
3666: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := l_ghr_pa_requests_rec.from_position_number;
3667: --
3668: /* FOR prior_pos_org IN cur_prior_pos_org(l_ghr_pa_requests_rec.from_position_id,
3669: (l_ghr_pa_requests_rec.effective_date-1) )
3670: LOOP
3671: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := prior_pos_org.name;
3672: END LOOP; */
3665: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := l_ghr_pa_requests_rec.from_position_title;
3666: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := l_ghr_pa_requests_rec.from_position_number;
3667: --
3668: /* FOR prior_pos_org IN cur_prior_pos_org(l_ghr_pa_requests_rec.from_position_id,
3669: (l_ghr_pa_requests_rec.effective_date-1) )
3670: LOOP
3671: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := prior_pos_org.name;
3672: END LOOP; */
3673: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3669: (l_ghr_pa_requests_rec.effective_date-1) )
3670: LOOP
3671: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := prior_pos_org.name;
3672: END LOOP; */
3673: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3674: ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3675: ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3676: ' ' || l_ghr_pa_requests_rec.from_position_org_line4 ||
3677: ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3670: LOOP
3671: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := prior_pos_org.name;
3672: END LOOP; */
3673: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3674: ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3675: ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3676: ' ' || l_ghr_pa_requests_rec.from_position_org_line4 ||
3677: ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3678: ' ' || l_ghr_pa_requests_rec.from_position_org_line6,1,500);
3671: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := prior_pos_org.name;
3672: END LOOP; */
3673: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3674: ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3675: ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3676: ' ' || l_ghr_pa_requests_rec.from_position_org_line4 ||
3677: ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3678: ' ' || l_ghr_pa_requests_rec.from_position_org_line6,1,500);
3679:
3672: END LOOP; */
3673: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3674: ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3675: ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3676: ' ' || l_ghr_pa_requests_rec.from_position_org_line4 ||
3677: ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3678: ' ' || l_ghr_pa_requests_rec.from_position_org_line6,1,500);
3679:
3680: ELSE
3673: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3674: ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3675: ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3676: ' ' || l_ghr_pa_requests_rec.from_position_org_line4 ||
3677: ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3678: ' ' || l_ghr_pa_requests_rec.from_position_org_line6,1,500);
3679:
3680: ELSE
3681: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := NULL;
3674: ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3675: ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3676: ' ' || l_ghr_pa_requests_rec.from_position_org_line4 ||
3677: ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3678: ' ' || l_ghr_pa_requests_rec.from_position_org_line6,1,500);
3679:
3680: ELSE
3681: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := NULL;
3682: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := NULL;
3684: END IF;
3685: --
3686: END IF; --- NOT APPOINTMENT ACTION OR RETURN TO DUTY
3687:
3688: l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3689:
3690: -- NEW EHRI CHANGES MADHURI
3691: --
3692: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' AND l_ghr_pa_requests_rec.second_noa_code <>'817')
3688: l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3689:
3690: -- NEW EHRI CHANGES MADHURI
3691: --
3692: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' AND l_ghr_pa_requests_rec.second_noa_code <>'817')
3693: OR
3694: l_ghr_pa_requests_rec.first_noa_code NOT IN ('817')) THEN
3695: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3696: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3690: -- NEW EHRI CHANGES MADHURI
3691: --
3692: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' AND l_ghr_pa_requests_rec.second_noa_code <>'817')
3693: OR
3694: l_ghr_pa_requests_rec.first_noa_code NOT IN ('817')) THEN
3695: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3696: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3697: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3698: -- Added format_name_ehri for EHRI changes.
3691: --
3692: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' AND l_ghr_pa_requests_rec.second_noa_code <>'817')
3693: OR
3694: l_ghr_pa_requests_rec.first_noa_code NOT IN ('817')) THEN
3695: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3696: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3697: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3698: -- Added format_name_ehri for EHRI changes.
3699: FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3692: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' AND l_ghr_pa_requests_rec.second_noa_code <>'817')
3693: OR
3694: l_ghr_pa_requests_rec.first_noa_code NOT IN ('817')) THEN
3695: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3696: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3697: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3698: -- Added format_name_ehri for EHRI changes.
3699: FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3700: l_ghr_pa_requests_rec.effective_date)
3693: OR
3694: l_ghr_pa_requests_rec.first_noa_code NOT IN ('817')) THEN
3695: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3696: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3697: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3698: -- Added format_name_ehri for EHRI changes.
3699: FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3700: l_ghr_pa_requests_rec.effective_date)
3701: LOOP
3695: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3696: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3697: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3698: -- Added format_name_ehri for EHRI changes.
3699: FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3700: l_ghr_pa_requests_rec.effective_date)
3701: LOOP
3702: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
3703: get_suffix_lname(per_det.last_name,
3696: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3697: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3698: -- Added format_name_ehri for EHRI changes.
3699: FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3700: l_ghr_pa_requests_rec.effective_date)
3701: LOOP
3702: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
3703: get_suffix_lname(per_det.last_name,
3704: l_ghr_pa_requests_rec.effective_date,
3700: l_ghr_pa_requests_rec.effective_date)
3701: LOOP
3702: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
3703: get_suffix_lname(per_det.last_name,
3704: l_ghr_pa_requests_rec.effective_date,
3705: l_suffix,
3706: l_last_name);
3707: l_ghr_cpdf_temp_rec.name_title := l_suffix;
3708: -- End Bug# 4648811
3709: END LOOP;
3710:
3711:
3712:
3713: FOR scd_dates IN cur_scd_dates(l_ghr_pa_requests_rec.pa_request_id)
3714: LOOP
3715: l_ghr_cpdf_temp_rec.SCD_rif := fnd_date.canonical_to_date(scd_dates.rif);
3716: l_ghr_cpdf_temp_rec.SCD_retirement := fnd_date.canonical_to_date(scd_dates.ret);
3717: -- Added date conversion for bug#3808473-EHRI reports
3721: and l_ghr_cpdf_temp_rec.SCD_retirement IS NULL) THEN
3722:
3723: BEGIN
3724: ghr_history_fetch.fetch_peopleei
3725: (p_person_id => l_ghr_pa_requests_rec.person_id,
3726: p_information_type => 'GHR_US_PER_SCD_INFORMATION',
3727: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
3728: p_per_ei_data => ll_per_ei_data
3729: );
3723: BEGIN
3724: ghr_history_fetch.fetch_peopleei
3725: (p_person_id => l_ghr_pa_requests_rec.person_id,
3726: p_information_type => 'GHR_US_PER_SCD_INFORMATION',
3727: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
3728: p_per_ei_data => ll_per_ei_data
3729: );
3730:
3731: l_ghr_cpdf_temp_rec.SCD_rif:= fnd_date.canonical_to_date(ll_per_ei_data.pei_information5);
3734: EXCEPTION
3735: WHEN OTHERS THEN
3736: l_message_name := 'fetch_peopleei';
3737: l_log_text := 'Error in fetching SCD Information for pa_request_id: '||
3738: l_ghr_pa_requests_rec.pa_request_id ||
3739: ' ; SSN/employee last name' ||
3740: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3741: l_ghr_pa_Requests_rec.employee_last_name ||
3742: ' ; first NOAC/Second NOAC: '||
3736: l_message_name := 'fetch_peopleei';
3737: l_log_text := 'Error in fetching SCD Information for pa_request_id: '||
3738: l_ghr_pa_requests_rec.pa_request_id ||
3739: ' ; SSN/employee last name' ||
3740: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3741: l_ghr_pa_Requests_rec.employee_last_name ||
3742: ' ; first NOAC/Second NOAC: '||
3743: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3744: l_ghr_pa_requests_rec.second_noa_code ||
3737: l_log_text := 'Error in fetching SCD Information for pa_request_id: '||
3738: l_ghr_pa_requests_rec.pa_request_id ||
3739: ' ; SSN/employee last name' ||
3740: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3741: l_ghr_pa_Requests_rec.employee_last_name ||
3742: ' ; first NOAC/Second NOAC: '||
3743: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3744: l_ghr_pa_requests_rec.second_noa_code ||
3745: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3739: ' ; SSN/employee last name' ||
3740: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3741: l_ghr_pa_Requests_rec.employee_last_name ||
3742: ' ; first NOAC/Second NOAC: '||
3743: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3744: l_ghr_pa_requests_rec.second_noa_code ||
3745: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3746:
3747: Raise EHRI_DYNRPT_ERROR;
3740: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3741: l_ghr_pa_Requests_rec.employee_last_name ||
3742: ' ; first NOAC/Second NOAC: '||
3743: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3744: l_ghr_pa_requests_rec.second_noa_code ||
3745: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3746:
3747: Raise EHRI_DYNRPT_ERROR;
3748: END;
3764: /* In case of 825 instead of taking TO side values From side values are taken as
3765: to side values are not populated.
3766: in future in case to side values are populated consider only the toside values.
3767: */
3768: IF l_ghr_pa_requests_rec.first_noa_code = '825'
3769: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3770: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
3771: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3772: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3765: to side values are not populated.
3766: in future in case to side values are populated consider only the toside values.
3767: */
3768: IF l_ghr_pa_requests_rec.first_noa_code = '825'
3769: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3770: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
3771: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3772: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3773: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3766: in future in case to side values are populated consider only the toside values.
3767: */
3768: IF l_ghr_pa_requests_rec.first_noa_code = '825'
3769: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3770: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
3771: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3772: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3773: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3774: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3767: */
3768: IF l_ghr_pa_requests_rec.first_noa_code = '825'
3769: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3770: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
3771: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3772: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3773: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3774: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3775: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3768: IF l_ghr_pa_requests_rec.first_noa_code = '825'
3769: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3770: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
3771: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3772: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3773: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3774: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3775: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3776: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3769: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3770: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
3771: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3772: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3773: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3774: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3775: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3776: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3777: --
3770: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
3771: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3772: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3773: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3774: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3775: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3776: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3777: --
3778: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3771: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3772: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3773: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3774: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3775: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3776: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3777: --
3778: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3779: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3772: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3773: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3774: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3775: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3776: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3777: --
3778: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3779: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3780: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3774: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3775: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3776: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3777: --
3778: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3779: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3780: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3781:
3782: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3775: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3776: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3777: --
3778: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3779: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3780: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3781:
3782: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3783: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
3776: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3777: --
3778: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3779: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3780: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3781:
3782: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3783: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
3784: END IF;
3778: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3779: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3780: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3781:
3782: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3783: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
3784: END IF;
3785:
3786: IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
3779: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3780: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3781:
3782: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3783: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
3784: END IF;
3785:
3786: IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
3787: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3782: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3783: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
3784: END IF;
3785:
3786: IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
3787: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3788: l_ghr_pa_requests_rec.first_noa_code like '3%' ) )
3789: AND
3790: NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
3783: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
3784: END IF;
3785:
3786: IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
3787: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3788: l_ghr_pa_requests_rec.first_noa_code like '3%' ) )
3789: AND
3790: NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
3791: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3784: END IF;
3785:
3786: IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
3787: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3788: l_ghr_pa_requests_rec.first_noa_code like '3%' ) )
3789: AND
3790: NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
3791: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3792: l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
3786: IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
3787: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3788: l_ghr_pa_requests_rec.first_noa_code like '3%' ) )
3789: AND
3790: NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
3791: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3792: l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
3793: AND
3794: NOT (l_ghr_pa_requests_rec.first_noa_code = '817'
3787: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3788: l_ghr_pa_requests_rec.first_noa_code like '3%' ) )
3789: AND
3790: NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
3791: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3792: l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
3793: AND
3794: NOT (l_ghr_pa_requests_rec.first_noa_code = '817'
3795: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3788: l_ghr_pa_requests_rec.first_noa_code like '3%' ) )
3789: AND
3790: NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
3791: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3792: l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
3793: AND
3794: NOT (l_ghr_pa_requests_rec.first_noa_code = '817'
3795: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3796: l_ghr_pa_requests_rec.first_noa_code = '817' ) )
3790: NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
3791: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3792: l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
3793: AND
3794: NOT (l_ghr_pa_requests_rec.first_noa_code = '817'
3795: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3796: l_ghr_pa_requests_rec.first_noa_code = '817' ) )
3797: THEN
3798: IF l_ghr_cpdf_temp_rec.to_supervisory_differential IS NULL THEN
3791: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3792: l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
3793: AND
3794: NOT (l_ghr_pa_requests_rec.first_noa_code = '817'
3795: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3796: l_ghr_pa_requests_rec.first_noa_code = '817' ) )
3797: THEN
3798: IF l_ghr_cpdf_temp_rec.to_supervisory_differential IS NULL THEN
3799:
3792: l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
3793: AND
3794: NOT (l_ghr_pa_requests_rec.first_noa_code = '817'
3795: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3796: l_ghr_pa_requests_rec.first_noa_code = '817' ) )
3797: THEN
3798: IF l_ghr_cpdf_temp_rec.to_supervisory_differential IS NULL THEN
3799:
3800: BEGIN
3799:
3800: BEGIN
3801: ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
3802: ,p_input_value_name => 'Amount'
3803: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3804: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3805: ,p_value => l_ghr_cpdf_temp_rec.to_supervisory_differential
3806: ,p_multiple_error_flag => l_multi_error_flag);
3807: EXCEPTION
3800: BEGIN
3801: ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
3802: ,p_input_value_name => 'Amount'
3803: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3804: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3805: ,p_value => l_ghr_cpdf_temp_rec.to_supervisory_differential
3806: ,p_multiple_error_flag => l_multi_error_flag);
3807: EXCEPTION
3808: WHEN OTHERS THEN
3807: EXCEPTION
3808: WHEN OTHERS THEN
3809: l_message_name := 'retrieve_element_entry_value';
3810: l_log_text := 'Error in fetching Supervisory Differential Amount for pa_request_id: '||
3811: l_ghr_pa_requests_rec.pa_request_id ||
3812: ' ; SSN/employee last name' ||
3813: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3814: l_ghr_pa_Requests_rec.employee_last_name ||
3815: ' ; first NOAC/Second NOAC: '||
3809: l_message_name := 'retrieve_element_entry_value';
3810: l_log_text := 'Error in fetching Supervisory Differential Amount for pa_request_id: '||
3811: l_ghr_pa_requests_rec.pa_request_id ||
3812: ' ; SSN/employee last name' ||
3813: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3814: l_ghr_pa_Requests_rec.employee_last_name ||
3815: ' ; first NOAC/Second NOAC: '||
3816: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3817: l_ghr_pa_requests_rec.second_noa_code ||
3810: l_log_text := 'Error in fetching Supervisory Differential Amount for pa_request_id: '||
3811: l_ghr_pa_requests_rec.pa_request_id ||
3812: ' ; SSN/employee last name' ||
3813: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3814: l_ghr_pa_Requests_rec.employee_last_name ||
3815: ' ; first NOAC/Second NOAC: '||
3816: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3817: l_ghr_pa_requests_rec.second_noa_code ||
3818: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3812: ' ; SSN/employee last name' ||
3813: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3814: l_ghr_pa_Requests_rec.employee_last_name ||
3815: ' ; first NOAC/Second NOAC: '||
3816: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3817: l_ghr_pa_requests_rec.second_noa_code ||
3818: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3819: Raise EHRI_DYNRPT_ERROR;
3820: END;
3813: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3814: l_ghr_pa_Requests_rec.employee_last_name ||
3815: ' ; first NOAC/Second NOAC: '||
3816: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3817: l_ghr_pa_requests_rec.second_noa_code ||
3818: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3819: Raise EHRI_DYNRPT_ERROR;
3820: END;
3821:
3824:
3825: BEGIN
3826: ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
3827: ,p_input_value_name => 'Amount'
3828: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3829: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3830: ,p_value => l_ghr_cpdf_temp_rec.to_retention_allowance
3831: ,p_multiple_error_flag => l_multi_error_flag);
3832: EXCEPTION
3825: BEGIN
3826: ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
3827: ,p_input_value_name => 'Amount'
3828: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3829: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3830: ,p_value => l_ghr_cpdf_temp_rec.to_retention_allowance
3831: ,p_multiple_error_flag => l_multi_error_flag);
3832: EXCEPTION
3833: WHEN OTHERS THEN
3832: EXCEPTION
3833: WHEN OTHERS THEN
3834: l_message_name := 'retrieve_element_entry_value';
3835: l_log_text := 'Error in fetching Retention Allowance Amount for pa_request_id: '||
3836: l_ghr_pa_requests_rec.pa_request_id ||
3837: ' ; SSN/employee last name' ||
3838: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3839: l_ghr_pa_Requests_rec.employee_last_name ||
3840: ' ; first NOAC/Second NOAC: '||
3834: l_message_name := 'retrieve_element_entry_value';
3835: l_log_text := 'Error in fetching Retention Allowance Amount for pa_request_id: '||
3836: l_ghr_pa_requests_rec.pa_request_id ||
3837: ' ; SSN/employee last name' ||
3838: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3839: l_ghr_pa_Requests_rec.employee_last_name ||
3840: ' ; first NOAC/Second NOAC: '||
3841: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3842: l_ghr_pa_requests_rec.second_noa_code ||
3835: l_log_text := 'Error in fetching Retention Allowance Amount for pa_request_id: '||
3836: l_ghr_pa_requests_rec.pa_request_id ||
3837: ' ; SSN/employee last name' ||
3838: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3839: l_ghr_pa_Requests_rec.employee_last_name ||
3840: ' ; first NOAC/Second NOAC: '||
3841: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3842: l_ghr_pa_requests_rec.second_noa_code ||
3843: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3837: ' ; SSN/employee last name' ||
3838: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3839: l_ghr_pa_Requests_rec.employee_last_name ||
3840: ' ; first NOAC/Second NOAC: '||
3841: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3842: l_ghr_pa_requests_rec.second_noa_code ||
3843: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3844: Raise EHRI_DYNRPT_ERROR;
3845: END;
3838: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3839: l_ghr_pa_Requests_rec.employee_last_name ||
3840: ' ; first NOAC/Second NOAC: '||
3841: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3842: l_ghr_pa_requests_rec.second_noa_code ||
3843: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3844: Raise EHRI_DYNRPT_ERROR;
3845: END;
3846:
3853: -- including the NOACS 800 and 782 inspite they are optional for reporting
3854: -- as they will be anyways filtered under exclude_noacs
3855: BEGIN
3856:
3857: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3858: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('702','703','713','721','781','782','790','800',
3859: '850','855','867','868','891','892','893','895','899'))
3860: OR l_ghr_pa_requests_rec.first_noa_code in ('702','703','713','721','781','782','790','800',
3861: '850','855','867','868','891','892','893','895','899')
3854: -- as they will be anyways filtered under exclude_noacs
3855: BEGIN
3856:
3857: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3858: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('702','703','713','721','781','782','790','800',
3859: '850','855','867','868','891','892','893','895','899'))
3860: OR l_ghr_pa_requests_rec.first_noa_code in ('702','703','713','721','781','782','790','800',
3861: '850','855','867','868','891','892','893','895','899')
3862: ) THEN
3856:
3857: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3858: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('702','703','713','721','781','782','790','800',
3859: '850','855','867','868','891','892','893','895','899'))
3860: OR l_ghr_pa_requests_rec.first_noa_code in ('702','703','713','721','781','782','790','800',
3861: '850','855','867','868','891','892','893','895','899')
3862: ) THEN
3863:
3864: ghr_history_fetch.fetch_peopleei
3861: '850','855','867','868','891','892','893','895','899')
3862: ) THEN
3863:
3864: ghr_history_fetch.fetch_peopleei
3865: (p_person_id => l_ghr_pa_requests_rec.person_id,
3866: p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES',
3867: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
3868: p_per_ei_data => ll_per_ei_data
3869: );
3863:
3864: ghr_history_fetch.fetch_peopleei
3865: (p_person_id => l_ghr_pa_requests_rec.person_id,
3866: p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES',
3867: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
3868: p_per_ei_data => ll_per_ei_data
3869: );
3870:
3871: l_ghr_cpdf_temp_rec.creditable_military_service:= ll_per_ei_data.pei_information5;
3871: l_ghr_cpdf_temp_rec.creditable_military_service:= ll_per_ei_data.pei_information5;
3872: ll_per_ei_data :=NULL;
3873:
3874: ghr_history_fetch.fetch_peopleei
3875: (p_person_id => l_ghr_pa_requests_rec.person_id,
3876: p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
3877: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
3878: p_per_ei_data => ll_per_ei_data
3879: );
3873:
3874: ghr_history_fetch.fetch_peopleei
3875: (p_person_id => l_ghr_pa_requests_rec.person_id,
3876: p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
3877: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
3878: p_per_ei_data => ll_per_ei_data
3879: );
3880: l_ghr_cpdf_temp_rec.from_retirement_coverage := ll_per_ei_data.pei_information4;
3881: l_ghr_cpdf_temp_rec.Frozen_service:= ll_per_ei_data.pei_information5;
3901: l_ghr_cpdf_temp_rec.race_national_origin := NULL;
3902: END IF;
3903:
3904: -- Bug 5011003
3905: l_locality_pay_area_code := get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
3906: p_effective_date => l_ghr_pa_requests_rec.effective_date);
3907: l_equiv_plan := get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan));
3908:
3909: IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
3902: END IF;
3903:
3904: -- Bug 5011003
3905: l_locality_pay_area_code := get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
3906: p_effective_date => l_ghr_pa_requests_rec.effective_date);
3907: l_equiv_plan := get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan));
3908:
3909: IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
3910: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3903:
3904: -- Bug 5011003
3905: l_locality_pay_area_code := get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
3906: p_effective_date => l_ghr_pa_requests_rec.effective_date);
3907: l_equiv_plan := get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan));
3908:
3909: IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
3910: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3911: ELSE
3944: EXCEPTION
3945: WHEN OTHERS THEN
3946: l_message_name := 'fetch_peopleei';
3947: l_log_text := 'Error in fetching SCD Information for pa_request_id: '||
3948: l_ghr_pa_requests_rec.pa_request_id ||
3949: ' ; SSN/employee last name' ||
3950: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3951: l_ghr_pa_Requests_rec.employee_last_name ||
3952: ' ; first NOAC/Second NOAC: '||
3946: l_message_name := 'fetch_peopleei';
3947: l_log_text := 'Error in fetching SCD Information for pa_request_id: '||
3948: l_ghr_pa_requests_rec.pa_request_id ||
3949: ' ; SSN/employee last name' ||
3950: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3951: l_ghr_pa_Requests_rec.employee_last_name ||
3952: ' ; first NOAC/Second NOAC: '||
3953: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3954: l_ghr_pa_requests_rec.second_noa_code ||
3947: l_log_text := 'Error in fetching SCD Information for pa_request_id: '||
3948: l_ghr_pa_requests_rec.pa_request_id ||
3949: ' ; SSN/employee last name' ||
3950: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3951: l_ghr_pa_Requests_rec.employee_last_name ||
3952: ' ; first NOAC/Second NOAC: '||
3953: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3954: l_ghr_pa_requests_rec.second_noa_code ||
3955: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3949: ' ; SSN/employee last name' ||
3950: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3951: l_ghr_pa_Requests_rec.employee_last_name ||
3952: ' ; first NOAC/Second NOAC: '||
3953: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3954: l_ghr_pa_requests_rec.second_noa_code ||
3955: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3956: END;
3957: -- End of changes for CPDF reports picking creditable mil serv, frozen serv and prev retirement coverage
3950: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3951: l_ghr_pa_Requests_rec.employee_last_name ||
3952: ' ; first NOAC/Second NOAC: '||
3953: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3954: l_ghr_pa_requests_rec.second_noa_code ||
3955: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3956: END;
3957: -- End of changes for CPDF reports picking creditable mil serv, frozen serv and prev retirement coverage
3958: -- 3327389 Bug fix end
3955: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3956: END;
3957: -- End of changes for CPDF reports picking creditable mil serv, frozen serv and prev retirement coverage
3958: -- 3327389 Bug fix end
3959: IF l_ghr_pa_requests_rec.first_noa_code <> '001' THEN
3960: IF l_ghr_cpdf_temp_rec.from_retirement_coverage IS NULL THEN
3961: l_ghr_cpdf_temp_rec.from_retirement_coverage:= 'NA';
3962: ELSIF l_ghr_cpdf_temp_rec.from_retirement_coverage IN ('P','R') THEN --bug#5184157 yogi
3963: l_ghr_cpdf_temp_rec.from_retirement_coverage:= 'Y';
3969:
3970: --Bug #6158983 EHRI Status and Dynamic Report Changes
3971: BEGIN
3972:
3973: FOR bus_grp_rec in cur_per_details(l_ghr_pa_requests_rec.person_id,
3974: l_ghr_pa_requests_rec.effective_date)
3975: LOOP
3976: l_business_group_id := bus_grp_rec.business_group_id;
3977: END LOOP;
3970: --Bug #6158983 EHRI Status and Dynamic Report Changes
3971: BEGIN
3972:
3973: FOR bus_grp_rec in cur_per_details(l_ghr_pa_requests_rec.person_id,
3974: l_ghr_pa_requests_rec.effective_date)
3975: LOOP
3976: l_business_group_id := bus_grp_rec.business_group_id;
3977: END LOOP;
3978:
3982: l_message_name := 'Fetch HB Pre Tax';
3983: ghr_per_sum.get_element_details
3984: (p_element_name => 'Health Benefits Pre tax'
3985: ,p_input_value_name => 'Health Plan'
3986: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3987: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3988: ,p_value => l_value
3989: ,p_effective_start_date => l_effective_start_date
3990: ,p_business_group_id => l_business_group_id);
3983: ghr_per_sum.get_element_details
3984: (p_element_name => 'Health Benefits Pre tax'
3985: ,p_input_value_name => 'Health Plan'
3986: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3987: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3988: ,p_value => l_value
3989: ,p_effective_start_date => l_effective_start_date
3990: ,p_business_group_id => l_business_group_id);
3991:
3998: l_effective_start_date := null;
3999: ghr_per_sum.get_element_details
4000: (p_element_name => 'Health Benefits Pre tax'
4001: ,p_input_value_name => 'Enrollment'
4002: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4003: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4004: ,p_value => l_value
4005: ,p_effective_start_date => l_effective_start_date
4006: ,p_business_group_id => l_business_group_id);
3999: ghr_per_sum.get_element_details
4000: (p_element_name => 'Health Benefits Pre tax'
4001: ,p_input_value_name => 'Enrollment'
4002: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4003: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4004: ,p_value => l_value
4005: ,p_effective_start_date => l_effective_start_date
4006: ,p_business_group_id => l_business_group_id);
4007:
4013: l_message_name := 'Fetch HB plan';
4014: ghr_per_sum.get_element_details
4015: (p_element_name => 'Health Benefits'
4016: ,p_input_value_name => 'Health Plan'
4017: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4018: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4019: ,p_value => l_value
4020: ,p_effective_start_date => l_effective_start_date
4021: ,p_business_group_id => l_business_group_id);
4014: ghr_per_sum.get_element_details
4015: (p_element_name => 'Health Benefits'
4016: ,p_input_value_name => 'Health Plan'
4017: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4018: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4019: ,p_value => l_value
4020: ,p_effective_start_date => l_effective_start_date
4021: ,p_business_group_id => l_business_group_id);
4022:
4028: l_effective_start_date := null;
4029: ghr_per_sum.get_element_details
4030: (p_element_name => 'Health Benefits'
4031: ,p_input_value_name => 'Enrollment'
4032: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4033: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4034: ,p_value => l_value
4035: ,p_effective_start_date => l_effective_start_date
4036: ,p_business_group_id => l_business_group_id);
4029: ghr_per_sum.get_element_details
4030: (p_element_name => 'Health Benefits'
4031: ,p_input_value_name => 'Enrollment'
4032: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4033: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4034: ,p_value => l_value
4035: ,p_effective_start_date => l_effective_start_date
4036: ,p_business_group_id => l_business_group_id);
4037:
4040:
4041: EXCEPTION
4042: WHEN OTHERS THEN
4043: l_log_text := 'Error in fetching data for Health Benefits for pa_request_id: '||
4044: l_ghr_pa_requests_rec.pa_request_id ||
4045: ' ; SSN/employee last name' ||
4046: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4047: l_ghr_pa_Requests_rec.employee_last_name ||
4048: ' ; first NOAC/Second NOAC: '||
4042: WHEN OTHERS THEN
4043: l_log_text := 'Error in fetching data for Health Benefits for pa_request_id: '||
4044: l_ghr_pa_requests_rec.pa_request_id ||
4045: ' ; SSN/employee last name' ||
4046: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4047: l_ghr_pa_Requests_rec.employee_last_name ||
4048: ' ; first NOAC/Second NOAC: '||
4049: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4050: l_ghr_pa_requests_rec.second_noa_code ||
4043: l_log_text := 'Error in fetching data for Health Benefits for pa_request_id: '||
4044: l_ghr_pa_requests_rec.pa_request_id ||
4045: ' ; SSN/employee last name' ||
4046: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4047: l_ghr_pa_Requests_rec.employee_last_name ||
4048: ' ; first NOAC/Second NOAC: '||
4049: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4050: l_ghr_pa_requests_rec.second_noa_code ||
4051: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4045: ' ; SSN/employee last name' ||
4046: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4047: l_ghr_pa_Requests_rec.employee_last_name ||
4048: ' ; first NOAC/Second NOAC: '||
4049: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4050: l_ghr_pa_requests_rec.second_noa_code ||
4051: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4052: Raise EHRI_DYNRPT_ERROR;
4053: END;
4046: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4047: l_ghr_pa_Requests_rec.employee_last_name ||
4048: ' ; first NOAC/Second NOAC: '||
4049: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4050: l_ghr_pa_requests_rec.second_noa_code ||
4051: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4052: Raise EHRI_DYNRPT_ERROR;
4053: END;
4054:
4058: /*BEGIN
4059: ll_per_ei_data:=NULL;
4060: l_message_name := 'Fetch Retirement System Info';
4061: ghr_history_fetch.fetch_peopleei
4062: (p_person_id => l_ghr_pa_requests_rec.person_id,
4063: p_information_type => 'GHR_US_PER_RETIRMENT_SYS_INFO',
4064: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4065: p_per_ei_data => ll_per_ei_data
4066: );
4060: l_message_name := 'Fetch Retirement System Info';
4061: ghr_history_fetch.fetch_peopleei
4062: (p_person_id => l_ghr_pa_requests_rec.person_id,
4063: p_information_type => 'GHR_US_PER_RETIRMENT_SYS_INFO',
4064: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4065: p_per_ei_data => ll_per_ei_data
4066: );
4067:
4068:
4075:
4076: WHEN OTHERS THEN
4077:
4078: l_log_text := 'Error in fetching data for Retirement System Information: '||
4079: l_ghr_pa_requests_rec.pa_request_id ||
4080: ' ; SSN/employee last name' ||
4081: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4082: l_ghr_pa_Requests_rec.employee_last_name ||
4083: ' ; first NOAC/Second NOAC: '||
4077:
4078: l_log_text := 'Error in fetching data for Retirement System Information: '||
4079: l_ghr_pa_requests_rec.pa_request_id ||
4080: ' ; SSN/employee last name' ||
4081: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4082: l_ghr_pa_Requests_rec.employee_last_name ||
4083: ' ; first NOAC/Second NOAC: '||
4084: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4085: l_ghr_pa_requests_rec.second_noa_code ||
4078: l_log_text := 'Error in fetching data for Retirement System Information: '||
4079: l_ghr_pa_requests_rec.pa_request_id ||
4080: ' ; SSN/employee last name' ||
4081: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4082: l_ghr_pa_Requests_rec.employee_last_name ||
4083: ' ; first NOAC/Second NOAC: '||
4084: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4085: l_ghr_pa_requests_rec.second_noa_code ||
4086: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4080: ' ; SSN/employee last name' ||
4081: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4082: l_ghr_pa_Requests_rec.employee_last_name ||
4083: ' ; first NOAC/Second NOAC: '||
4084: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4085: l_ghr_pa_requests_rec.second_noa_code ||
4086: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4087: Raise EHRI_DYNRPT_ERROR;
4088: END;
4081: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4082: l_ghr_pa_Requests_rec.employee_last_name ||
4083: ' ; first NOAC/Second NOAC: '||
4084: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4085: l_ghr_pa_requests_rec.second_noa_code ||
4086: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4087: Raise EHRI_DYNRPT_ERROR;
4088: END;
4089:
4089:
4090: BEGIN
4091: ll_per_ei_data:=NULL;
4092: ghr_history_fetch.fetch_peopleei
4093: (p_person_id => l_ghr_pa_requests_rec.person_id,
4094: p_information_type => 'GHR_US_PER_BENEFIT_INFO',
4095: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4096: p_per_ei_data => ll_per_ei_data
4097: );
4091: ll_per_ei_data:=NULL;
4092: ghr_history_fetch.fetch_peopleei
4093: (p_person_id => l_ghr_pa_requests_rec.person_id,
4094: p_information_type => 'GHR_US_PER_BENEFIT_INFO',
4095: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4096: p_per_ei_data => ll_per_ei_data
4097: );
4098:
4099: l_ghr_cpdf_temp_rec.FEGLI_ASSG_INDICATOR := ll_per_ei_data.pei_information16;
4106:
4107: WHEN OTHERS THEN
4108:
4109: l_log_text := 'Error in fetching data for Person Benefit Information: '||
4110: l_ghr_pa_requests_rec.pa_request_id ||
4111: ' ; SSN/employee last name' ||
4112: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4113: l_ghr_pa_Requests_rec.employee_last_name ||
4114: ' ; first NOAC/Second NOAC: '||
4108:
4109: l_log_text := 'Error in fetching data for Person Benefit Information: '||
4110: l_ghr_pa_requests_rec.pa_request_id ||
4111: ' ; SSN/employee last name' ||
4112: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4113: l_ghr_pa_Requests_rec.employee_last_name ||
4114: ' ; first NOAC/Second NOAC: '||
4115: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4116: l_ghr_pa_requests_rec.second_noa_code ||
4109: l_log_text := 'Error in fetching data for Person Benefit Information: '||
4110: l_ghr_pa_requests_rec.pa_request_id ||
4111: ' ; SSN/employee last name' ||
4112: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4113: l_ghr_pa_Requests_rec.employee_last_name ||
4114: ' ; first NOAC/Second NOAC: '||
4115: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4116: l_ghr_pa_requests_rec.second_noa_code ||
4117: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4111: ' ; SSN/employee last name' ||
4112: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4113: l_ghr_pa_Requests_rec.employee_last_name ||
4114: ' ; first NOAC/Second NOAC: '||
4115: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4116: l_ghr_pa_requests_rec.second_noa_code ||
4117: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4118: Raise EHRI_DYNRPT_ERROR;
4119: END;*/
4112: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4113: l_ghr_pa_Requests_rec.employee_last_name ||
4114: ' ; first NOAC/Second NOAC: '||
4115: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4116: l_ghr_pa_requests_rec.second_noa_code ||
4117: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4118: Raise EHRI_DYNRPT_ERROR;
4119: END;*/
4120: --bug #6416590 moving sf50_approval_date to pareq_last_updated_date
4119: END;*/
4120: --bug #6416590 moving sf50_approval_date to pareq_last_updated_date
4121: -- as sf50_approval_date has to be considered for ordering of sequence number
4122: --with in effective date
4123: l_ghr_cpdf_temp_rec.pareq_last_updated_date := l_ghr_pa_requests_rec.sf50_approval_date;
4124: --End of Bug#6158983
4125:
4126:
4127: insert_row(l_ghr_cpdf_temp_rec);
4140: WHEN OTHERS THEN
4141: hr_utility.set_location('Inside WHEN_OTHERS exception ',40);
4142: l_message_name := 'Unhandled Error';
4143: l_log_text := 'Unhandled Error for pa_request_id: '||
4144: l_ghr_pa_requests_rec.pa_request_id ||
4145: ' ; SSN/employee last name' ||
4146: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4147: l_ghr_pa_Requests_rec.employee_last_name ||
4148: ' ; first NOAC/Second NOAC: '||
4142: l_message_name := 'Unhandled Error';
4143: l_log_text := 'Unhandled Error for pa_request_id: '||
4144: l_ghr_pa_requests_rec.pa_request_id ||
4145: ' ; SSN/employee last name' ||
4146: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4147: l_ghr_pa_Requests_rec.employee_last_name ||
4148: ' ; first NOAC/Second NOAC: '||
4149: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4150: l_ghr_pa_requests_rec.second_noa_code ||
4143: l_log_text := 'Unhandled Error for pa_request_id: '||
4144: l_ghr_pa_requests_rec.pa_request_id ||
4145: ' ; SSN/employee last name' ||
4146: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4147: l_ghr_pa_Requests_rec.employee_last_name ||
4148: ' ; first NOAC/Second NOAC: '||
4149: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4150: l_ghr_pa_requests_rec.second_noa_code ||
4151: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4145: ' ; SSN/employee last name' ||
4146: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4147: l_ghr_pa_Requests_rec.employee_last_name ||
4148: ' ; first NOAC/Second NOAC: '||
4149: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4150: l_ghr_pa_requests_rec.second_noa_code ||
4151: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4152: ghr_mto_int.log_message(p_procedure => l_message_name,
4153: p_message => l_log_text
4146: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4147: l_ghr_pa_Requests_rec.employee_last_name ||
4148: ' ; first NOAC/Second NOAC: '||
4149: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4150: l_ghr_pa_requests_rec.second_noa_code ||
4151: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4152: ghr_mto_int.log_message(p_procedure => l_message_name,
4153: p_message => l_log_text
4154: );