222: ,p_information_type IN VARCHAR2
223: ,p_rei_rec OUT NOCOPY ghr_pa_request_extra_info%ROWTYPE) IS
224: CURSOR c_par IS
225: SELECT par.pa_request_id
226: FROM ghr_pa_requests par
227: CONNECT BY par.pa_request_id = prior par.altered_pa_request_id
228: START WITH par.pa_request_id = p_pa_request_id;
229: --
230: CURSOR cur_rei (cp_pa_request_id IN NUMBER) IS
807: CURSOR get_asg_id(p_request_id NUMBER,
808: p_person_id NUMBER)
809: IS
810: SELECT employee_assignment_id
811: FROM ghr_pa_requests
812: WHERE pa_request_id=p_request_id
813: AND person_id=p_person_id;
814: --
815: l_asg_id per_assignments_f.assignment_id%TYPE;
873: --8275231
874: cursor get_first_noa_id
875: is
876: select first_noa_id
877: from ghr_pa_requests
878: where pa_request_id = (select min(pa_request_id)
879: from ghr_pa_requests
880: connect by pa_request_id = prior altered_pa_request_id
881: start with pa_request_id = p_pa_request_id);
875: is
876: select first_noa_id
877: from ghr_pa_requests
878: where pa_request_id = (select min(pa_request_id)
879: from ghr_pa_requests
880: connect by pa_request_id = prior altered_pa_request_id
881: start with pa_request_id = p_pa_request_id);
882:
883: cursor get_dual_det
885: select rpa_type,
886: mass_action_id,
887: first_noa_code,
888: second_noa_code
889: from ghr_pa_requests
890: where pa_request_id = p_pa_request_id;
891: --8275231
892: BEGIN
893: -- If the PAR has happened then need to go to history to get it
944: ,p_noa_id_corrected => l_noa_id
945: ,p_date_effective => p_effective_date
946: ,p_assignment_data => l_asgn_data);
947: --
948: ghr_pa_requests_pkg.get_SF52_loc_ddf_details (l_asgn_data.location_id
949: ,l_duty_station_id);
950: --
951: ghr_pa_requests_pkg.get_duty_station_details (l_duty_station_id
952: ,p_effective_date
947: --
948: ghr_pa_requests_pkg.get_SF52_loc_ddf_details (l_asgn_data.location_id
949: ,l_duty_station_id);
950: --
951: ghr_pa_requests_pkg.get_duty_station_details (l_duty_station_id
952: ,p_effective_date
953: ,l_duty_station_code
954: ,l_dummy_varchar);
955: p_from_duty_station_code := l_duty_station_code;
1004: ,p_duty_station_location_id => l_location_id
1005: ,p_pay_rate_determinant => p_from_pay_rate_determinant
1006: ,p_work_schedule => p_from_work_schedule);
1007: --
1008: ghr_pa_requests_pkg.get_SF52_loc_ddf_details (l_location_id
1009: ,l_duty_station_id);
1010:
1011: ghr_pa_requests_pkg.get_duty_station_details (l_duty_station_id
1012: ,p_effective_date
1007: --
1008: ghr_pa_requests_pkg.get_SF52_loc_ddf_details (l_location_id
1009: ,l_duty_station_id);
1010:
1011: ghr_pa_requests_pkg.get_duty_station_details (l_duty_station_id
1012: ,p_effective_date
1013: ,l_duty_station_code
1014: ,l_dummy_varchar);
1015: p_from_duty_station_code := l_duty_station_code;
1033: ,p_from_national_identifier OUT NOCOPY VARCHAR2) IS
1034: --
1035: CURSOR cur_prev_ssn IS
1036: SELECT par.employee_national_identifier prev_ssn
1037: FROM ghr_pa_requests par
1038: WHERE par.pa_request_id = p_altered_pa_request_id;
1039: --
1040: BEGIN
1041: IF p_noa_family_code = 'CORRECT' THEN
2410: --
2411: l_info_type VARCHAR2(200) := NULL;
2412: l_api_assignment_id per_assignments.assignment_id%TYPE;
2413: --
2414: l_first_noa_id ghr_pa_requests.first_noa_id%TYPE;
2415: l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
2416: l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
2417: l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
2418: l_noa_family_code ghr_pa_requests.noa_family_code%TYPE;
2411: l_info_type VARCHAR2(200) := NULL;
2412: l_api_assignment_id per_assignments.assignment_id%TYPE;
2413: --
2414: l_first_noa_id ghr_pa_requests.first_noa_id%TYPE;
2415: l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
2416: l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
2417: l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
2418: l_noa_family_code ghr_pa_requests.noa_family_code%TYPE;
2419: l_multi_error_flag boolean;
2412: l_api_assignment_id per_assignments.assignment_id%TYPE;
2413: --
2414: l_first_noa_id ghr_pa_requests.first_noa_id%TYPE;
2415: l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
2416: l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
2417: l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
2418: l_noa_family_code ghr_pa_requests.noa_family_code%TYPE;
2419: l_multi_error_flag boolean;
2420: --
2413: --
2414: l_first_noa_id ghr_pa_requests.first_noa_id%TYPE;
2415: l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
2416: l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
2417: l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
2418: l_noa_family_code ghr_pa_requests.noa_family_code%TYPE;
2419: l_multi_error_flag boolean;
2420: --
2421: -- This cursor drives of the PAR table to first see which PA Requests had 'Update HR'
2414: l_first_noa_id ghr_pa_requests.first_noa_id%TYPE;
2415: l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
2416: l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
2417: l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
2418: l_noa_family_code ghr_pa_requests.noa_family_code%TYPE;
2419: l_multi_error_flag boolean;
2420: --
2421: -- This cursor drives of the PAR table to first see which PA Requests had 'Update HR'
2422: -- selected by a user in the given period
2421: -- This cursor drives of the PAR table to first see which PA Requests had 'Update HR'
2422: -- selected by a user in the given period
2423: -- may as well select everyting from the PA Request table (saves going back!)
2424:
2425: -- 3/13/02 -- Joined the ghr_pa_requests table with per_people_f table to view the
2426: -- records based on business group id/security group id
2427: -- Not added outer join for per_people_f because we are expecting existence of person_id
2428: -- in ghr_pa_requests for the actions with status in ('UPDATE_HR_COMPLETE','FUTURE_ACTION')
2429:
2424:
2425: -- 3/13/02 -- Joined the ghr_pa_requests table with per_people_f table to view the
2426: -- records based on business group id/security group id
2427: -- Not added outer join for per_people_f because we are expecting existence of person_id
2428: -- in ghr_pa_requests for the actions with status in ('UPDATE_HR_COMPLETE','FUTURE_ACTION')
2429:
2430: -- 24-OCT-2002 JH truncated sf50_approval_date because it does contain time on the db.
2431: -- which causes some rows to not be included on last day.
2432:
2438:
2439: CURSOR cur_get_pars(p_agencies_with_se in varchar2,
2440: p_agencies_without_se in varchar2) IS
2441: SELECT par.*
2442: FROM ghr_pa_requests par,
2443: per_all_people_f per
2444: --8486208 added for new parameter
2445: WHERE ((p_agency is not null and NVL(par.agency_code,par.from_agency_code) LIKE p_agency)
2446: OR
2462: AND decode(hr_general.get_xbg_profile,'Y',per.business_group_id , hr_general.get_business_group_id) = per.business_group_id
2463: --Bug # 10158391 added the union condition to consider the records approved in last month and effective in the reporting period
2464: UNION
2465: SELECT par.*
2466: FROM ghr_pa_requests par,
2467: per_all_people_f per
2468: --8486208 added for new parameter
2469: WHERE ((p_agency is not null and NVL(par.agency_code,par.from_agency_code) LIKE p_agency)
2470: OR
2501: l_ghr_cpdf_temp_rec ghr_cpdf_temp%ROWTYPE;
2502: l_ghr_empty_cpdf_temp ghr_cpdf_temp%ROWTYPE;
2503: -- l_ghr_cpdf_cancel_rec ghr_cpdf_temp%ROWTYPE;
2504:
2505: l_ghr_pa_requests_rec ghr_pa_requests%ROWTYPE;
2506: l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2507: l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
2508: l_retained_grade_or_level ghr_pa_requests.to_grade_or_level%type;
2509: l_retained_step_or_rate ghr_pa_requests.to_step_or_rate%type;
2503: -- l_ghr_cpdf_cancel_rec ghr_cpdf_temp%ROWTYPE;
2504:
2505: l_ghr_pa_requests_rec ghr_pa_requests%ROWTYPE;
2506: l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2507: l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
2508: l_retained_grade_or_level ghr_pa_requests.to_grade_or_level%type;
2509: l_retained_step_or_rate ghr_pa_requests.to_step_or_rate%type;
2510: l_sf52_rec1 ghr_pa_requests%ROWTYPE;
2511: l_sf52_rec2 ghr_pa_requests%ROWTYPE;
2504:
2505: l_ghr_pa_requests_rec ghr_pa_requests%ROWTYPE;
2506: l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2507: l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
2508: l_retained_grade_or_level ghr_pa_requests.to_grade_or_level%type;
2509: l_retained_step_or_rate ghr_pa_requests.to_step_or_rate%type;
2510: l_sf52_rec1 ghr_pa_requests%ROWTYPE;
2511: l_sf52_rec2 ghr_pa_requests%ROWTYPE;
2512: l_dual_flg BOOLEAN:=FALSE;
2505: l_ghr_pa_requests_rec ghr_pa_requests%ROWTYPE;
2506: l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2507: l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
2508: l_retained_grade_or_level ghr_pa_requests.to_grade_or_level%type;
2509: l_retained_step_or_rate ghr_pa_requests.to_step_or_rate%type;
2510: l_sf52_rec1 ghr_pa_requests%ROWTYPE;
2511: l_sf52_rec2 ghr_pa_requests%ROWTYPE;
2512: l_dual_flg BOOLEAN:=FALSE;
2513: l_single_flg BOOLEAN:=TRUE;
2506: l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2507: l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
2508: l_retained_grade_or_level ghr_pa_requests.to_grade_or_level%type;
2509: l_retained_step_or_rate ghr_pa_requests.to_step_or_rate%type;
2510: l_sf52_rec1 ghr_pa_requests%ROWTYPE;
2511: l_sf52_rec2 ghr_pa_requests%ROWTYPE;
2512: l_dual_flg BOOLEAN:=FALSE;
2513: l_single_flg BOOLEAN:=TRUE;
2514: l_loop NUMBER :=1;
2507: l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
2508: l_retained_grade_or_level ghr_pa_requests.to_grade_or_level%type;
2509: l_retained_step_or_rate ghr_pa_requests.to_step_or_rate%type;
2510: l_sf52_rec1 ghr_pa_requests%ROWTYPE;
2511: l_sf52_rec2 ghr_pa_requests%ROWTYPE;
2512: l_dual_flg BOOLEAN:=FALSE;
2513: l_single_flg BOOLEAN:=TRUE;
2514: l_loop NUMBER :=1;
2515: l_index NUMBER :=1;
2525: -- the following cursor can be used for dual purposes
2526: -- can pass the exact eff date or the eff_date-1 to pick the prior details
2527: --
2528: CURSOR cur_per_details( p_person_id per_all_people_f.person_id%type,
2529: p_eff_date ghr_pa_requests.effective_date%TYPE)
2530: IS
2531: SELECT last_name, middle_names, first_name,title,business_group_id
2532: FROM per_all_people_f
2533: WHERE person_id = p_person_id
2534: AND p_eff_date between effective_start_date and effective_end_date;
2535:
2536: -- Bug 5010844
2537: -- Cursor for getting authorization person details
2538: CURSOR cur_approver_id(c_pa_request_id ghr_pa_requests.pa_request_id%type)
2539: IS
2540: SELECT user_name_employee_id approver_id
2541: FROM ghr_pa_routing_history
2542: WHERE pa_request_id = c_pa_request_id
2556: --
2557: -- This cursor can be used to pick the prior num, org details of that pos
2558: --
2559: CURSOR cur_prior_pos_org(p_position_id hr_positions_f.position_id%TYPE,
2560: p_eff_date ghr_pa_requests.effective_date%TYPE)
2561: IS
2562: SELECT name
2563: FROM hr_organization_units
2564: WHERE organization_id = ( SELECT organization_id
2566: WHERE position_id=p_position_id
2567: AND p_eff_date between effective_start_date and effective_end_date);
2568: --
2569: CURSOR cur_pos_org(p_org_id hr_organization_units.organization_id%TYPE,
2570: p_eff_date ghr_pa_requests.effective_date%TYPE)
2571: IS
2572: SELECT name
2573: FROM hr_organization_units
2574: WHERE organization_id = p_org_id
2575: AND p_eff_date between date_from and NVL(date_to,to_Date('31/12/4712','DD/MM/YYYY'));
2576: --
2577: -- added date cond and other fields for ENW EHRI changes
2578: --
2579: CURSOR cur_scd_dates(p_pa_request_id ghr_pa_requests.pa_request_id%type)
2580: IS
2581: SELECT REI_INFORMATION3 rif ,REI_INFORMATION2 ret
2582: FROM ghr_pa_request_extra_info parei
2583: WHERE parei.pa_request_id=p_pa_request_id
2583: WHERE parei.pa_request_id=p_pa_request_id
2584: AND parei.information_type='GHR_US_PAR_SCD_INFO'; --Bug# 12557463 modified GHR_US_PAR_CHG_SCD to GHR_US_PAR_SCD_INFO
2585:
2586: --BUG# 6458070 -- ssn corrected issue
2587: cursor cur_ssn_corr(p_altered_pa_request_id ghr_pa_requests.altered_pa_request_id%type,
2588: p_to_national_identifier ghr_pa_requests.employee_national_identifier%type)
2589: is
2590: SELECT employee_national_identifier
2591: FROM ghr_pa_requests
2584: AND parei.information_type='GHR_US_PAR_SCD_INFO'; --Bug# 12557463 modified GHR_US_PAR_CHG_SCD to GHR_US_PAR_SCD_INFO
2585:
2586: --BUG# 6458070 -- ssn corrected issue
2587: cursor cur_ssn_corr(p_altered_pa_request_id ghr_pa_requests.altered_pa_request_id%type,
2588: p_to_national_identifier ghr_pa_requests.employee_national_identifier%type)
2589: is
2590: SELECT employee_national_identifier
2591: FROM ghr_pa_requests
2592: WHERE pa_request_id = p_altered_pa_request_id
2587: cursor cur_ssn_corr(p_altered_pa_request_id ghr_pa_requests.altered_pa_request_id%type,
2588: p_to_national_identifier ghr_pa_requests.employee_national_identifier%type)
2589: is
2590: SELECT employee_national_identifier
2591: FROM ghr_pa_requests
2592: WHERE pa_request_id = p_altered_pa_request_id
2593: AND employee_national_identifier <> p_to_national_identifier;
2594:
2595: l_records_found BOOLEAN;
2603: l_last_name per_all_people.last_name%type;
2604: l_suffix ghr_cpdf_temp.prior_name_suffix%type;
2605:
2606: -- For Dual Actions PRD is becoming null so preserving it using a local variable.
2607: l_pay_rate_determinant ghr_pa_requests.pay_rate_determinant%TYPE;
2608: --
2609: CURSOR cur_rem(p_pa_request_id ghr_pa_requests.pa_request_id%TYPE)
2610: IS
2611: SELECT Description
2605:
2606: -- For Dual Actions PRD is becoming null so preserving it using a local variable.
2607: l_pay_rate_determinant ghr_pa_requests.pay_rate_determinant%TYPE;
2608: --
2609: CURSOR cur_rem(p_pa_request_id ghr_pa_requests.pa_request_id%TYPE)
2610: IS
2611: SELECT Description
2612: FROM ghr_pa_remarks
2613: WHERE pa_request_id=p_pa_request_id;
2668: FOR cur_get_pars_rec IN cur_get_pars(p_agencies_with_se => l_agencies_with_se,
2669: p_agencies_without_se => l_agencies_without_se)
2670: LOOP
2671: -- 1) Get PA Request data
2672: l_ghr_pa_requests_rec := cur_get_pars_rec;
2673:
2674:
2675: l_sf52_rec1 := l_ghr_pa_requests_rec;
2676: l_sf52_rec2 := l_ghr_pa_requests_rec;
2671: -- 1) Get PA Request data
2672: l_ghr_pa_requests_rec := cur_get_pars_rec;
2673:
2674:
2675: l_sf52_rec1 := l_ghr_pa_requests_rec;
2676: l_sf52_rec2 := l_ghr_pa_requests_rec;
2677:
2678: IF l_ghr_pa_requests_rec.second_noa_code IS NOT NULL
2679: AND l_ghr_pa_requests_rec.first_noa_code NOT IN ('001','002') THEN
2672: l_ghr_pa_requests_rec := cur_get_pars_rec;
2673:
2674:
2675: l_sf52_rec1 := l_ghr_pa_requests_rec;
2676: l_sf52_rec2 := l_ghr_pa_requests_rec;
2677:
2678: IF l_ghr_pa_requests_rec.second_noa_code IS NOT NULL
2679: AND l_ghr_pa_requests_rec.first_noa_code NOT IN ('001','002') THEN
2680: l_loop := 2;
2674:
2675: l_sf52_rec1 := l_ghr_pa_requests_rec;
2676: l_sf52_rec2 := l_ghr_pa_requests_rec;
2677:
2678: IF l_ghr_pa_requests_rec.second_noa_code IS NOT NULL
2679: AND l_ghr_pa_requests_rec.first_noa_code NOT IN ('001','002') THEN
2680: l_loop := 2;
2681: l_dual_flg := TRUE;
2682: l_single_flg := FALSE;
2675: l_sf52_rec1 := l_ghr_pa_requests_rec;
2676: l_sf52_rec2 := l_ghr_pa_requests_rec;
2677:
2678: IF l_ghr_pa_requests_rec.second_noa_code IS NOT NULL
2679: AND l_ghr_pa_requests_rec.first_noa_code NOT IN ('001','002') THEN
2680: l_loop := 2;
2681: l_dual_flg := TRUE;
2682: l_single_flg := FALSE;
2683: ELSE
2685: l_single_flg := TRUE;
2686: l_dual_flg := FALSE;
2687: END IF;
2688:
2689: /* If ( l_ghr_pa_requests_rec.first_noa_code like '3%'and
2690: l_ghr_pa_requests_rec.second_noa_code ='825' ) THEN
2691: l_loop := 2;
2692: l_dual_flg := TRUE;
2693: l_single_flg := FALSE;
2686: l_dual_flg := FALSE;
2687: END IF;
2688:
2689: /* If ( l_ghr_pa_requests_rec.first_noa_code like '3%'and
2690: l_ghr_pa_requests_rec.second_noa_code ='825' ) THEN
2691: l_loop := 2;
2692: l_dual_flg := TRUE;
2693: l_single_flg := FALSE;
2694: ELSE
2705: BEGIN
2706: -- Loop twice for dual action
2707: IF ( l_dual_flg = TRUE and l_index = 1 ) then
2708: --6850492 modified for dual action to assign pay rate determinant
2709: /* l_pay_rate_determinant := l_ghr_pa_requests_rec.pay_rate_determinant;
2710: ghr_process_sf52.assign_new_rg( p_action_num => 1,
2711: p_pa_req => l_sf52_rec1);
2712:
2713: l_ghr_pa_requests_rec := l_sf52_rec1;
2709: /* l_pay_rate_determinant := l_ghr_pa_requests_rec.pay_rate_determinant;
2710: ghr_process_sf52.assign_new_rg( p_action_num => 1,
2711: p_pa_req => l_sf52_rec1);
2712:
2713: l_ghr_pa_requests_rec := l_sf52_rec1;
2714: if l_sf52_rec1.pay_rate_determinant is null then
2715: l_ghr_pa_requests_rec.pay_rate_determinant := l_pay_rate_determinant;
2716: end if; */
2717: --8275231
2711: p_pa_req => l_sf52_rec1);
2712:
2713: l_ghr_pa_requests_rec := l_sf52_rec1;
2714: if l_sf52_rec1.pay_rate_determinant is null then
2715: l_ghr_pa_requests_rec.pay_rate_determinant := l_pay_rate_determinant;
2716: end if; */
2717: --8275231
2718: ghr_process_sf52.null_2ndNoa_cols(l_sf52_rec1);
2719: l_ghr_pa_requests_rec := l_sf52_rec1;
2715: l_ghr_pa_requests_rec.pay_rate_determinant := l_pay_rate_determinant;
2716: end if; */
2717: --8275231
2718: ghr_process_sf52.null_2ndNoa_cols(l_sf52_rec1);
2719: l_ghr_pa_requests_rec := l_sf52_rec1;
2720: ELSIF ( l_dual_flg = TRUE and l_index = 2 ) then
2721: -- In case of Dual Actin assign_new_rg is nulling out the PRD.
2722: /*l_pay_rate_determinant := l_ghr_pa_requests_rec.pay_rate_determinant;
2723: ghr_process_sf52.assign_new_rg( p_action_num => 2,
2718: ghr_process_sf52.null_2ndNoa_cols(l_sf52_rec1);
2719: l_ghr_pa_requests_rec := l_sf52_rec1;
2720: ELSIF ( l_dual_flg = TRUE and l_index = 2 ) then
2721: -- In case of Dual Actin assign_new_rg is nulling out the PRD.
2722: /*l_pay_rate_determinant := l_ghr_pa_requests_rec.pay_rate_determinant;
2723: ghr_process_sf52.assign_new_rg( p_action_num => 2,
2724: p_pa_req => l_sf52_rec2);
2725:
2726: l_ghr_pa_requests_rec := l_sf52_rec2;
2722: /*l_pay_rate_determinant := l_ghr_pa_requests_rec.pay_rate_determinant;
2723: ghr_process_sf52.assign_new_rg( p_action_num => 2,
2724: p_pa_req => l_sf52_rec2);
2725:
2726: l_ghr_pa_requests_rec := l_sf52_rec2;
2727: if l_sf52_rec2.pay_rate_determinant is null then
2728: l_ghr_pa_requests_rec.pay_rate_determinant := l_pay_rate_determinant;
2729: end if; */
2730: --8275231
2724: p_pa_req => l_sf52_rec2);
2725:
2726: l_ghr_pa_requests_rec := l_sf52_rec2;
2727: if l_sf52_rec2.pay_rate_determinant is null then
2728: l_ghr_pa_requests_rec.pay_rate_determinant := l_pay_rate_determinant;
2729: end if; */
2730: --8275231
2731: ghr_process_sf52.copy_2ndNoa_to_1stNoa(l_sf52_rec2);
2732: ghr_process_sf52.null_2ndNoa_cols(l_sf52_rec2);
2733: for noa_family_rec in c_noa_family(l_sf52_rec2.first_noa_id,l_sf52_rec2.effective_date) loop --Bug# 8275231
2734: l_sf52_rec2.noa_family_code := noa_family_rec.noa_family_code;
2735: end loop;
2736: --8275231
2737: l_ghr_pa_requests_rec := l_sf52_rec2;
2738: l_dual_flg := FALSE;
2739: ELSIF (l_single_flg = TRUE and l_dual_flg <> TRUE ) THEN
2740: l_ghr_pa_requests_rec := cur_get_pars_rec;
2741: END IF;
2736: --8275231
2737: l_ghr_pa_requests_rec := l_sf52_rec2;
2738: l_dual_flg := FALSE;
2739: ELSIF (l_single_flg = TRUE and l_dual_flg <> TRUE ) THEN
2740: l_ghr_pa_requests_rec := cur_get_pars_rec;
2741: END IF;
2742:
2743: --- 8490723/8490327 formating the noac before doing any comparison
2744: --- 9184710 Modified the parameter of passing as second NOAC should be NULl for DUal Actions
2741: END IF;
2742:
2743: --- 8490723/8490327 formating the noac before doing any comparison
2744: --- 9184710 Modified the parameter of passing as second NOAC should be NULl for DUal Actions
2745: l_ghr_pa_requests_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
2746: l_ghr_pa_requests_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);
2747:
2748: -- Bug# 4648811 getting the suffix from the lastname and also removing suffix from lastname
2749: get_suffix_lname(l_ghr_pa_requests_rec.employee_last_name,
2742:
2743: --- 8490723/8490327 formating the noac before doing any comparison
2744: --- 9184710 Modified the parameter of passing as second NOAC should be NULl for DUal Actions
2745: l_ghr_pa_requests_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
2746: l_ghr_pa_requests_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);
2747:
2748: -- Bug# 4648811 getting the suffix from the lastname and also removing suffix from lastname
2749: get_suffix_lname(l_ghr_pa_requests_rec.employee_last_name,
2750: l_ghr_pa_requests_rec.effective_date,
2745: l_ghr_pa_requests_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
2746: l_ghr_pa_requests_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);
2747:
2748: -- Bug# 4648811 getting the suffix from the lastname and also removing suffix from lastname
2749: get_suffix_lname(l_ghr_pa_requests_rec.employee_last_name,
2750: l_ghr_pa_requests_rec.effective_date,
2751: l_suffix,
2752: l_last_name);
2753:
2746: l_ghr_pa_requests_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);
2747:
2748: -- Bug# 4648811 getting the suffix from the lastname and also removing suffix from lastname
2749: get_suffix_lname(l_ghr_pa_requests_rec.employee_last_name,
2750: l_ghr_pa_requests_rec.effective_date,
2751: l_suffix,
2752: l_last_name);
2753:
2754: l_ghr_pa_requests_rec.employee_last_name := l_last_name;
2750: l_ghr_pa_requests_rec.effective_date,
2751: l_suffix,
2752: l_last_name);
2753:
2754: l_ghr_pa_requests_rec.employee_last_name := l_last_name;
2755: --End Bug# 4648811
2756:
2757: --Bug # 9329643
2758: If l_ghr_pa_requests_rec.person_id is not null then
2754: l_ghr_pa_requests_rec.employee_last_name := l_last_name;
2755: --End Bug# 4648811
2756:
2757: --Bug # 9329643
2758: If l_ghr_pa_requests_rec.person_id is not null then
2759: l_employee_number := ghr_pa_requests_pkg2.get_employee_number
2760: (p_person_id => l_ghr_pa_requests_rec.person_id,
2761: p_effective_date => l_ghr_pa_requests_rec.effective_date);
2762: End if;
2755: --End Bug# 4648811
2756:
2757: --Bug # 9329643
2758: If l_ghr_pa_requests_rec.person_id is not null then
2759: l_employee_number := ghr_pa_requests_pkg2.get_employee_number
2760: (p_person_id => l_ghr_pa_requests_rec.person_id,
2761: p_effective_date => l_ghr_pa_requests_rec.effective_date);
2762: End if;
2763:
2756:
2757: --Bug # 9329643
2758: If l_ghr_pa_requests_rec.person_id is not null then
2759: l_employee_number := ghr_pa_requests_pkg2.get_employee_number
2760: (p_person_id => l_ghr_pa_requests_rec.person_id,
2761: p_effective_date => l_ghr_pa_requests_rec.effective_date);
2762: End if;
2763:
2764: hr_utility.set_location(l_proc||' Check non_us_citizen_and_foreign_ds' ,30);
2757: --Bug # 9329643
2758: If l_ghr_pa_requests_rec.person_id is not null then
2759: l_employee_number := ghr_pa_requests_pkg2.get_employee_number
2760: (p_person_id => l_ghr_pa_requests_rec.person_id,
2761: p_effective_date => l_ghr_pa_requests_rec.effective_date);
2762: End if;
2763:
2764: hr_utility.set_location(l_proc||' Check non_us_citizen_and_foreign_ds' ,30);
2765:
2764: hr_utility.set_location(l_proc||' Check non_us_citizen_and_foreign_ds' ,30);
2765:
2766: --
2767: -- 2.1) Do not include PAR's for a non US Citizen in a foreign country
2768: IF non_us_citizen_and_foreign_ds (p_citizenship => l_ghr_pa_requests_rec.citizenship
2769: ,p_duty_station_code => l_ghr_pa_requests_rec.duty_station_code) THEN
2770: GOTO end_par_loop; -- loop for the next one!
2771: END IF;
2772: --
2765:
2766: --
2767: -- 2.1) Do not include PAR's for a non US Citizen in a foreign country
2768: IF non_us_citizen_and_foreign_ds (p_citizenship => l_ghr_pa_requests_rec.citizenship
2769: ,p_duty_station_code => l_ghr_pa_requests_rec.duty_station_code) THEN
2770: GOTO end_par_loop; -- loop for the next one!
2771: END IF;
2772: --
2773: hr_utility.set_location(l_proc||' Customer exclusion hook' ,40);
2772: --
2773: hr_utility.set_location(l_proc||' Customer exclusion hook' ,40);
2774: --
2775: -- Bug 714944 -- Added exclusion of NAF:
2776: IF exclude_position (p_position_id => NVL(l_ghr_pa_requests_rec.to_position_id
2777: ,l_ghr_pa_requests_rec.from_position_id)
2778: ,p_effective_date => l_ghr_pa_requests_rec.effective_date) THEN
2779: GOTO end_par_loop; -- loop for the next one!
2780: END IF;
2773: hr_utility.set_location(l_proc||' Customer exclusion hook' ,40);
2774: --
2775: -- Bug 714944 -- Added exclusion of NAF:
2776: IF exclude_position (p_position_id => NVL(l_ghr_pa_requests_rec.to_position_id
2777: ,l_ghr_pa_requests_rec.from_position_id)
2778: ,p_effective_date => l_ghr_pa_requests_rec.effective_date) THEN
2779: GOTO end_par_loop; -- loop for the next one!
2780: END IF;
2781:
2774: --
2775: -- Bug 714944 -- Added exclusion of NAF:
2776: IF exclude_position (p_position_id => NVL(l_ghr_pa_requests_rec.to_position_id
2777: ,l_ghr_pa_requests_rec.from_position_id)
2778: ,p_effective_date => l_ghr_pa_requests_rec.effective_date) THEN
2779: GOTO end_par_loop; -- loop for the next one!
2780: END IF;
2781:
2782: -- Obtain Retained Grade information
2785: l_retained_pay_plan := NULL;
2786: l_retained_grade_or_level := NULL;
2787: l_retained_step_or_rate := NULL;
2788: l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details (
2789: p_person_id => l_ghr_pa_requests_rec.person_id,
2790: p_effective_date => l_ghr_pa_requests_rec.effective_date
2791: );
2792:
2793: l_retained_pay_plan := l_retained_grade_rec.pay_plan;
2786: l_retained_grade_or_level := NULL;
2787: l_retained_step_or_rate := NULL;
2788: l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details (
2789: p_person_id => l_ghr_pa_requests_rec.person_id,
2790: p_effective_date => l_ghr_pa_requests_rec.effective_date
2791: );
2792:
2793: l_retained_pay_plan := l_retained_grade_rec.pay_plan;
2794: l_retained_grade_or_level := l_retained_grade_rec.grade_or_level;
2815: -- 3.1) Get all info from PAR table itself
2816: --
2817: hr_utility.set_location(l_proc||' populate cpdf temp from par' ,50);
2818: --
2819: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
2820: -- Agency Subelement Code
2821: l_ghr_cpdf_temp_rec.to_national_identifier := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
2822: -- SSN
2823: -- SSN corrected
2817: hr_utility.set_location(l_proc||' populate cpdf temp from par' ,50);
2818: --
2819: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
2820: -- Agency Subelement Code
2821: l_ghr_cpdf_temp_rec.to_national_identifier := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
2822: -- SSN
2823: -- SSN corrected
2824: IF (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2825: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '1%') THEN
2820: -- Agency Subelement Code
2821: l_ghr_cpdf_temp_rec.to_national_identifier := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
2822: -- SSN
2823: -- SSN corrected
2824: IF (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2825: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '1%') THEN
2826: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2827: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2828: for cur_ssn_corr_rec in cur_ssn_corr(l_ghr_pa_requests_rec.altered_pa_request_id,
2821: l_ghr_cpdf_temp_rec.to_national_identifier := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
2822: -- SSN
2823: -- SSN corrected
2824: IF (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2825: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '1%') THEN
2826: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2827: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2828: for cur_ssn_corr_rec in cur_ssn_corr(l_ghr_pa_requests_rec.altered_pa_request_id,
2829: l_ghr_pa_requests_rec.employee_national_identifier)
2824: IF (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2825: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '1%') THEN
2826: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2827: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2828: for cur_ssn_corr_rec in cur_ssn_corr(l_ghr_pa_requests_rec.altered_pa_request_id,
2829: l_ghr_pa_requests_rec.employee_national_identifier)
2830: loop
2831: l_ghr_cpdf_temp_rec.SSN_CORRECTED := format_ni(cur_ssn_corr_rec.employee_national_identifier);
2832: end loop;
2825: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '1%') THEN
2826: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2827: --Bug# 6458070 added the below cursor to check whether ssn is corrected
2828: for cur_ssn_corr_rec in cur_ssn_corr(l_ghr_pa_requests_rec.altered_pa_request_id,
2829: l_ghr_pa_requests_rec.employee_national_identifier)
2830: loop
2831: l_ghr_cpdf_temp_rec.SSN_CORRECTED := format_ni(cur_ssn_corr_rec.employee_national_identifier);
2832: end loop;
2833: END IF;
2832: end loop;
2833: END IF;
2834: --
2835:
2836: IF (l_ghr_pa_requests_rec.first_noa_code = '001' AND
2837: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '350') OR
2838: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2839: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2840: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2833: END IF;
2834: --
2835:
2836: IF (l_ghr_pa_requests_rec.first_noa_code = '001' AND
2837: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '350') OR
2838: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2839: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2840: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2841: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2834: --
2835:
2836: IF (l_ghr_pa_requests_rec.first_noa_code = '001' AND
2837: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '350') OR
2838: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2839: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2840: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2841: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2842: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2835:
2836: IF (l_ghr_pa_requests_rec.first_noa_code = '001' AND
2837: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '350') OR
2838: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2839: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2840: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2841: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2842: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2843: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2836: IF (l_ghr_pa_requests_rec.first_noa_code = '001' AND
2837: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '350') OR
2838: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2839: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2840: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2841: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2842: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2843: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2844: ELSE
2837: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '350') OR
2838: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2839: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2840: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2841: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2842: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2843: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2844: ELSE
2845: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2838: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2839: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2840: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2841: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2842: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2843: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2844: ELSE
2845: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2846: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2839: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2840: (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2841: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2842: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2843: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2844: ELSE
2845: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2846: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2847: END IF;
2841: IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2842: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2843: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2844: ELSE
2845: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2846: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2847: END IF;
2848: END IF;
2849:
2842: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2843: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2844: ELSE
2845: l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2846: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2847: END IF;
2848: END IF;
2849:
2850: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date; -- format in report
2846: l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2847: END IF;
2848: END IF;
2849:
2850: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date; -- format in report
2851: l_ghr_cpdf_temp_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
2852: l_ghr_cpdf_temp_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);-- Moved here for bug# 1399854
2853:
2854: -- All NOACS need to report details regarding the approval date, authorizer details etc.
2847: END IF;
2848: END IF;
2849:
2850: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date; -- format in report
2851: l_ghr_cpdf_temp_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
2852: l_ghr_cpdf_temp_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);-- Moved here for bug# 1399854
2853:
2854: -- All NOACS need to report details regarding the approval date, authorizer details etc.
2855: --7610341 added the following to consider effective date if approval date is greater than effective date
2848: END IF;
2849:
2850: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date; -- format in report
2851: l_ghr_cpdf_temp_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
2852: l_ghr_cpdf_temp_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);-- Moved here for bug# 1399854
2853:
2854: -- All NOACS need to report details regarding the approval date, authorizer details etc.
2855: --7610341 added the following to consider effective date if approval date is greater than effective date
2856: IF l_ghr_pa_requests_rec.APPROVAL_DATE > l_ghr_pa_requests_rec.effective_date and l_ghr_pa_requests_rec.first_noa_code not in ('001','002') then
2852: l_ghr_cpdf_temp_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);-- Moved here for bug# 1399854
2853:
2854: -- All NOACS need to report details regarding the approval date, authorizer details etc.
2855: --7610341 added the following to consider effective date if approval date is greater than effective date
2856: IF l_ghr_pa_requests_rec.APPROVAL_DATE > l_ghr_pa_requests_rec.effective_date and l_ghr_pa_requests_rec.first_noa_code not in ('001','002') then
2857: l_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE := l_ghr_pa_requests_rec.effective_date;
2858: else
2859: l_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE := l_ghr_pa_requests_rec.APPROVAL_DATE;
2860: end if;
2853:
2854: -- All NOACS need to report details regarding the approval date, authorizer details etc.
2855: --7610341 added the following to consider effective date if approval date is greater than effective date
2856: IF l_ghr_pa_requests_rec.APPROVAL_DATE > l_ghr_pa_requests_rec.effective_date and l_ghr_pa_requests_rec.first_noa_code not in ('001','002') then
2857: l_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE := l_ghr_pa_requests_rec.effective_date;
2858: else
2859: l_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE := l_ghr_pa_requests_rec.APPROVAL_DATE;
2860: end if;
2861:
2855: --7610341 added the following to consider effective date if approval date is greater than effective date
2856: IF l_ghr_pa_requests_rec.APPROVAL_DATE > l_ghr_pa_requests_rec.effective_date and l_ghr_pa_requests_rec.first_noa_code not in ('001','002') then
2857: l_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE := l_ghr_pa_requests_rec.effective_date;
2858: else
2859: l_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE := l_ghr_pa_requests_rec.APPROVAL_DATE;
2860: end if;
2861:
2862: -- 5010844
2863: -- Setting l_approver_id to NULL to find the proper approver's name.
2861:
2862: -- 5010844
2863: -- Setting l_approver_id to NULL to find the proper approver's name.
2864: l_approver_id := NULL;
2865: FOR l_cur_auth_id IN cur_approver_id(l_ghr_pa_requests_rec.pa_request_id) LOOP
2866: l_approver_id := l_cur_auth_id.approver_id;
2867: END LOOP;
2868: -- Bug#5209089 If Approver's ID is NULL in the pa_routing, the action may be a mass action.
2869: -- For mass actions pick the approver's ID from the approver name attached to the Personnel Office.
2867: END LOOP;
2868: -- Bug#5209089 If Approver's ID is NULL in the pa_routing, the action may be a mass action.
2869: -- For mass actions pick the approver's ID from the approver name attached to the Personnel Office.
2870: IF l_approver_id IS NULL THEN
2871: FOR l_approver_rec IN cur_poid_approver(l_ghr_pa_requests_rec.personnel_office_id)
2872: LOOP
2873: l_approver_id := l_approver_rec.person_id;
2874: END LOOP;
2875: END IF;
2874: END LOOP;
2875: END IF;
2876:
2877: FOR auth_det IN cur_per_details(l_approver_id,
2878: l_ghr_pa_requests_rec.effective_date)
2879: LOOP
2880: -- End Bug 5010844
2881: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
2882: get_suffix_lname(auth_det.last_name,
2879: LOOP
2880: -- End Bug 5010844
2881: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
2882: get_suffix_lname(auth_det.last_name,
2883: l_ghr_pa_requests_rec.effective_date,
2884: l_suffix,
2885: l_last_name);
2886: l_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME := l_last_name;
2887: l_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME := auth_det.first_name;
2891:
2892:
2893: END LOOP;
2894:
2895: l_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE := l_ghr_pa_requests_rec.sf50_approving_ofcl_work_title;
2896:
2897: -- Restricting Remarks to 2000 characters.
2898: FOR rem_rec IN cur_rem(l_ghr_pa_requests_rec.pa_request_id) LOOP
2899: l_ghr_cpdf_temp_rec.remarks_text := SUBSTR(l_ghr_cpdf_temp_rec.remarks_text||rem_rec.description,1,2000);
2894:
2895: l_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE := l_ghr_pa_requests_rec.sf50_approving_ofcl_work_title;
2896:
2897: -- Restricting Remarks to 2000 characters.
2898: FOR rem_rec IN cur_rem(l_ghr_pa_requests_rec.pa_request_id) LOOP
2899: l_ghr_cpdf_temp_rec.remarks_text := SUBSTR(l_ghr_cpdf_temp_rec.remarks_text||rem_rec.description,1,2000);
2900: IF LENGTH(l_ghr_cpdf_temp_rec.remarks_text) = 2000 THEN
2901: EXIT;
2902: END IF;
2913: -- Insert_row in GHR_CPDF_TEMP, and continue in the LOOP for the next PAR row.
2914: --- EHRI_EMPLOYEE_ID IS TO BE REPORTED FOR ALL THE EMPLOYEES AND FOR ALL RPA's
2915: --- esp for 001 action
2916: BEGIN
2917: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
2918: ,l_ghr_pa_requests_rec.first_noa_id
2919: ,l_ghr_pa_requests_rec.second_noa_id
2920: ,l_ghr_pa_requests_rec.noa_family_code
2921: ,l_ghr_pa_requests_rec.person_id
2914: --- EHRI_EMPLOYEE_ID IS TO BE REPORTED FOR ALL THE EMPLOYEES AND FOR ALL RPA's
2915: --- esp for 001 action
2916: BEGIN
2917: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
2918: ,l_ghr_pa_requests_rec.first_noa_id
2919: ,l_ghr_pa_requests_rec.second_noa_id
2920: ,l_ghr_pa_requests_rec.noa_family_code
2921: ,l_ghr_pa_requests_rec.person_id
2922: ,l_ghr_pa_requests_rec.effective_date
2915: --- esp for 001 action
2916: BEGIN
2917: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
2918: ,l_ghr_pa_requests_rec.first_noa_id
2919: ,l_ghr_pa_requests_rec.second_noa_id
2920: ,l_ghr_pa_requests_rec.noa_family_code
2921: ,l_ghr_pa_requests_rec.person_id
2922: ,l_ghr_pa_requests_rec.effective_date
2923: ,l_dummy
2916: BEGIN
2917: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
2918: ,l_ghr_pa_requests_rec.first_noa_id
2919: ,l_ghr_pa_requests_rec.second_noa_id
2920: ,l_ghr_pa_requests_rec.noa_family_code
2921: ,l_ghr_pa_requests_rec.person_id
2922: ,l_ghr_pa_requests_rec.effective_date
2923: ,l_dummy
2924: ,l_dummy
2917: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
2918: ,l_ghr_pa_requests_rec.first_noa_id
2919: ,l_ghr_pa_requests_rec.second_noa_id
2920: ,l_ghr_pa_requests_rec.noa_family_code
2921: ,l_ghr_pa_requests_rec.person_id
2922: ,l_ghr_pa_requests_rec.effective_date
2923: ,l_dummy
2924: ,l_dummy
2925: ,l_dummy
2918: ,l_ghr_pa_requests_rec.first_noa_id
2919: ,l_ghr_pa_requests_rec.second_noa_id
2920: ,l_ghr_pa_requests_rec.noa_family_code
2921: ,l_ghr_pa_requests_rec.person_id
2922: ,l_ghr_pa_requests_rec.effective_date
2923: ,l_dummy
2924: ,l_dummy
2925: ,l_dummy
2926: ,l_dummy
2959: WHEN OTHERS THEN
2960: l_message_name := 'get_par_ei_noac';
2961: --Bug # 9329643 Modified SSN to Emp No
2962: l_log_text := 'Error in fetching EHRI Employee id for pa_request_id: '||
2963: l_ghr_pa_requests_rec.pa_request_id ||
2964: ' ; Emp No/employee last name' ||
2965: l_employee_number ||' / '||
2966: l_ghr_pa_Requests_rec.employee_last_name ||
2967: ' ; first NOAC/Second NOAC: '||
2962: l_log_text := 'Error in fetching EHRI Employee id for pa_request_id: '||
2963: l_ghr_pa_requests_rec.pa_request_id ||
2964: ' ; Emp No/employee last name' ||
2965: l_employee_number ||' / '||
2966: l_ghr_pa_Requests_rec.employee_last_name ||
2967: ' ; first NOAC/Second NOAC: '||
2968: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2969: l_ghr_pa_requests_rec.second_noa_code ||
2970: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2964: ' ; Emp No/employee last name' ||
2965: l_employee_number ||' / '||
2966: l_ghr_pa_Requests_rec.employee_last_name ||
2967: ' ; first NOAC/Second NOAC: '||
2968: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2969: l_ghr_pa_requests_rec.second_noa_code ||
2970: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2971:
2972: Raise EHRI_DYNRPT_ERROR;
2965: l_employee_number ||' / '||
2966: l_ghr_pa_Requests_rec.employee_last_name ||
2967: ' ; first NOAC/Second NOAC: '||
2968: l_ghr_pa_requests_rec.first_noa_code || ' / '||
2969: l_ghr_pa_requests_rec.second_noa_code ||
2970: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2971:
2972: Raise EHRI_DYNRPT_ERROR;
2973: END;
2973: END;
2974:
2975: -- Bug 5063301
2976: --Birth Date
2977: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
2978: -- End Bug 5063301
2979:
2980: IF l_ghr_pa_requests_rec.first_noa_code = '001' THEN
2981: insert_row(l_ghr_cpdf_temp_rec);
2976: --Birth Date
2977: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
2978: -- End Bug 5063301
2979:
2980: IF l_ghr_pa_requests_rec.first_noa_code = '001' THEN
2981: insert_row(l_ghr_cpdf_temp_rec);
2982: l_records_found:=TRUE;
2983: GOTO end_par_loop; -- loop for the next one!
2984: END IF;
2983: GOTO end_par_loop; -- loop for the next one!
2984: END IF;
2985:
2986: -- Obtain Family Code
2987: l_noa_family_code := l_ghr_pa_requests_rec.noa_family_code;
2988: IF l_noa_family_code = 'CORRECT' THEN
2989: -- Bug#2789704 Added Exception Handling
2990: -- Bug#5172710 Modified the function to determine the noa family code.
2991: BEGIN
2988: IF l_noa_family_code = 'CORRECT' THEN
2989: -- Bug#2789704 Added Exception Handling
2990: -- Bug#5172710 Modified the function to determine the noa family code.
2991: BEGIN
2992: l_noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family
2993: (l_ghr_pa_requests_rec.second_noa_id,
2994: l_ghr_pa_requests_rec.effective_date);
2995: EXCEPTION
2996: WHEN OTHERS THEN
2989: -- Bug#2789704 Added Exception Handling
2990: -- Bug#5172710 Modified the function to determine the noa family code.
2991: BEGIN
2992: l_noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family
2993: (l_ghr_pa_requests_rec.second_noa_id,
2994: l_ghr_pa_requests_rec.effective_date);
2995: EXCEPTION
2996: WHEN OTHERS THEN
2997: l_message_name := 'get_family_code';
2990: -- Bug#5172710 Modified the function to determine the noa family code.
2991: BEGIN
2992: l_noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family
2993: (l_ghr_pa_requests_rec.second_noa_id,
2994: l_ghr_pa_requests_rec.effective_date);
2995: EXCEPTION
2996: WHEN OTHERS THEN
2997: l_message_name := 'get_family_code';
2998: --Bug # 9329643 Modified SSN to Emp No
2996: WHEN OTHERS THEN
2997: l_message_name := 'get_family_code';
2998: --Bug # 9329643 Modified SSN to Emp No
2999: l_log_text := 'Error in getting family code for pa_request_id: '||
3000: l_ghr_pa_requests_rec.pa_request_id ||
3001: ' ; Emp No/employee last name' ||
3002: l_employee_number ||' / '||
3003: l_ghr_pa_Requests_rec.employee_last_name ||
3004: ' ; first NOAC/Second NOAC: '||
2999: l_log_text := 'Error in getting family code for pa_request_id: '||
3000: l_ghr_pa_requests_rec.pa_request_id ||
3001: ' ; Emp No/employee last name' ||
3002: l_employee_number ||' / '||
3003: l_ghr_pa_Requests_rec.employee_last_name ||
3004: ' ; first NOAC/Second NOAC: '||
3005: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3006: l_ghr_pa_requests_rec.second_noa_code ||
3007: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3001: ' ; Emp No/employee last name' ||
3002: l_employee_number ||' / '||
3003: l_ghr_pa_Requests_rec.employee_last_name ||
3004: ' ; first NOAC/Second NOAC: '||
3005: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3006: l_ghr_pa_requests_rec.second_noa_code ||
3007: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3008:
3009: Raise EHRI_DYNRPT_ERROR;
3002: l_employee_number ||' / '||
3003: l_ghr_pa_Requests_rec.employee_last_name ||
3004: ' ; first NOAC/Second NOAC: '||
3005: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3006: l_ghr_pa_requests_rec.second_noa_code ||
3007: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3008:
3009: Raise EHRI_DYNRPT_ERROR;
3010: END;
3011: -- Bug#2789704 Added Exception Handling
3012: END IF;
3013:
3014: -- Moved POI to this place for bug# 1402287 to not print for Cancellations.
3015: l_ghr_cpdf_temp_rec.personnel_office_id := l_ghr_pa_requests_rec.personnel_office_id;
3016: -- Personnel Office Identifier Code
3017: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
3018: --Birth Date
3019: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
3013:
3014: -- Moved POI to this place for bug# 1402287 to not print for Cancellations.
3015: l_ghr_cpdf_temp_rec.personnel_office_id := l_ghr_pa_requests_rec.personnel_office_id;
3016: -- Personnel Office Identifier Code
3017: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
3018: --Birth Date
3019: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
3020: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
3021: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
3015: l_ghr_cpdf_temp_rec.personnel_office_id := l_ghr_pa_requests_rec.personnel_office_id;
3016: -- Personnel Office Identifier Code
3017: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
3018: --Birth Date
3019: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
3020: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
3021: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
3022: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
3023: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
3016: -- Personnel Office Identifier Code
3017: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
3018: --Birth Date
3019: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
3020: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
3021: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
3022: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
3023: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
3024: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
3017: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
3018: --Birth Date
3019: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
3020: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
3021: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
3022: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
3023: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
3024: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
3025: -- Retirement System Type Code (retierment plan)
3018: --Birth Date
3019: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
3020: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
3021: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
3022: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
3023: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
3024: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
3025: -- Retirement System Type Code (retierment plan)
3026:
3019: l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
3020: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
3021: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
3022: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
3023: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
3024: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
3025: -- Retirement System Type Code (retierment plan)
3026:
3027:
3020: l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
3021: l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
3022: l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
3023: l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
3024: l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
3025: -- Retirement System Type Code (retierment plan)
3026:
3027:
3028: --Start of Bug #6158983
3027:
3028: --Start of Bug #6158983
3029: --start of Bug #6522440 adding one more validation of showing the hyp_full_reg_duty_part_emp
3030: -- only for part time employees
3031: if l_ghr_pa_requests_rec.part_time_hours is not null then
3032: --Bug# 15941036 added Retirement codes MR
3033: if l_ghr_cpdf_temp_rec.retirement_plan in ('E','M','MR','T') then
3034: l_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp := 72.00;
3035: else
3041:
3042:
3043:
3044:
3045: l_ghr_cpdf_temp_rec.veterans_status := l_ghr_pa_requests_rec.veterans_status;
3046: l_ghr_cpdf_temp_rec.FLSA_category := l_ghr_pa_requests_rec.FLSA_category;
3047:
3048: IF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF in ('P','R') ) THEN
3049: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'Y';
3042:
3043:
3044:
3045: l_ghr_cpdf_temp_rec.veterans_status := l_ghr_pa_requests_rec.veterans_status;
3046: l_ghr_cpdf_temp_rec.FLSA_category := l_ghr_pa_requests_rec.FLSA_category;
3047:
3048: IF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF in ('P','R') ) THEN
3049: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'Y';
3050: ELSIF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF = 'N') THEN
3044:
3045: l_ghr_cpdf_temp_rec.veterans_status := l_ghr_pa_requests_rec.veterans_status;
3046: l_ghr_cpdf_temp_rec.FLSA_category := l_ghr_pa_requests_rec.FLSA_category;
3047:
3048: IF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF in ('P','R') ) THEN
3049: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'Y';
3050: ELSIF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF = 'N') THEN
3051: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'N';
3052: ELSE
3046: l_ghr_cpdf_temp_rec.FLSA_category := l_ghr_pa_requests_rec.FLSA_category;
3047:
3048: IF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF in ('P','R') ) THEN
3049: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'Y';
3050: ELSIF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF = 'N') THEN
3051: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'N';
3052: ELSE
3053: l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'NA';
3054: END IF;
3055:
3056: -- APPOINTMENT_NTE_DATE
3057: BEGIN
3058:
3059: get_asg_details( l_ghr_pa_requests_rec.pa_request_id,
3060: l_ghr_pa_requests_rec.person_id,
3061: l_ghr_pa_requests_rec.effective_date,
3062: l_ghr_cpdf_temp_rec.appointment_nte_date);
3063:
3056: -- APPOINTMENT_NTE_DATE
3057: BEGIN
3058:
3059: get_asg_details( l_ghr_pa_requests_rec.pa_request_id,
3060: l_ghr_pa_requests_rec.person_id,
3061: l_ghr_pa_requests_rec.effective_date,
3062: l_ghr_cpdf_temp_rec.appointment_nte_date);
3063:
3064: EXCEPTION
3057: BEGIN
3058:
3059: get_asg_details( l_ghr_pa_requests_rec.pa_request_id,
3060: l_ghr_pa_requests_rec.person_id,
3061: l_ghr_pa_requests_rec.effective_date,
3062: l_ghr_cpdf_temp_rec.appointment_nte_date);
3063:
3064: EXCEPTION
3065: WHEN OTHERS THEN
3065: WHEN OTHERS THEN
3066: l_message_name := 'get_asg_details';
3067: --Bug # 9329643 Modified SSN to Emp No
3068: l_log_text := 'Error in getting appointment_nte_date for pa_request_id: '||
3069: l_ghr_pa_requests_rec.pa_request_id ||
3070: ' ; Emp No/employee last name' ||
3071: l_employee_number ||' / '||
3072: l_ghr_pa_Requests_rec.employee_last_name ||
3073: ' ; first NOAC/Second NOAC: '||
3068: l_log_text := 'Error in getting appointment_nte_date for pa_request_id: '||
3069: l_ghr_pa_requests_rec.pa_request_id ||
3070: ' ; Emp No/employee last name' ||
3071: l_employee_number ||' / '||
3072: l_ghr_pa_Requests_rec.employee_last_name ||
3073: ' ; first NOAC/Second NOAC: '||
3074: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3075: l_ghr_pa_requests_rec.second_noa_code ||
3076: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3070: ' ; Emp No/employee last name' ||
3071: l_employee_number ||' / '||
3072: l_ghr_pa_Requests_rec.employee_last_name ||
3073: ' ; first NOAC/Second NOAC: '||
3074: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3075: l_ghr_pa_requests_rec.second_noa_code ||
3076: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3077:
3078: Raise EHRI_DYNRPT_ERROR;
3071: l_employee_number ||' / '||
3072: l_ghr_pa_Requests_rec.employee_last_name ||
3073: ' ; first NOAC/Second NOAC: '||
3074: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3075: l_ghr_pa_requests_rec.second_noa_code ||
3076: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3077:
3078: Raise EHRI_DYNRPT_ERROR;
3079: END;
3080:
3081: --
3082: --Bug#5328177 Added the following conditions to report the incentive percentage, amount
3083: -- in the Award percentage(for noac 827) and award amount(for noacs 815,816,825) fields.
3084: IF l_ghr_pa_requests_rec.first_noa_code = '827' OR
3085: l_ghr_pa_requests_rec.second_noa_code = '827' THEN
3086: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
3087: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3088: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
3081: --
3082: --Bug#5328177 Added the following conditions to report the incentive percentage, amount
3083: -- in the Award percentage(for noac 827) and award amount(for noacs 815,816,825) fields.
3084: IF l_ghr_pa_requests_rec.first_noa_code = '827' OR
3085: l_ghr_pa_requests_rec.second_noa_code = '827' THEN
3086: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
3087: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3088: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
3089: l_ghr_pa_requests_rec.award_amount IS NULL THEN
3082: --Bug#5328177 Added the following conditions to report the incentive percentage, amount
3083: -- in the Award percentage(for noac 827) and award amount(for noacs 815,816,825) fields.
3084: IF l_ghr_pa_requests_rec.first_noa_code = '827' OR
3085: l_ghr_pa_requests_rec.second_noa_code = '827' THEN
3086: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
3087: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3088: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
3089: l_ghr_pa_requests_rec.award_amount IS NULL THEN
3090: l_ghr_pa_requests_rec.award_amount := l_ghr_pa_requests_rec.to_total_salary;
3083: -- in the Award percentage(for noac 827) and award amount(for noacs 815,816,825) fields.
3084: IF l_ghr_pa_requests_rec.first_noa_code = '827' OR
3085: l_ghr_pa_requests_rec.second_noa_code = '827' THEN
3086: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
3087: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3088: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
3089: l_ghr_pa_requests_rec.award_amount IS NULL THEN
3090: l_ghr_pa_requests_rec.award_amount := l_ghr_pa_requests_rec.to_total_salary;
3091: END IF;
3084: IF l_ghr_pa_requests_rec.first_noa_code = '827' OR
3085: l_ghr_pa_requests_rec.second_noa_code = '827' THEN
3086: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
3087: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3088: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
3089: l_ghr_pa_requests_rec.award_amount IS NULL THEN
3090: l_ghr_pa_requests_rec.award_amount := l_ghr_pa_requests_rec.to_total_salary;
3091: END IF;
3092:
3085: l_ghr_pa_requests_rec.second_noa_code = '827' THEN
3086: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
3087: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3088: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
3089: l_ghr_pa_requests_rec.award_amount IS NULL THEN
3090: l_ghr_pa_requests_rec.award_amount := l_ghr_pa_requests_rec.to_total_salary;
3091: END IF;
3092:
3093:
3086: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
3087: ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3088: l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
3089: l_ghr_pa_requests_rec.award_amount IS NULL THEN
3090: l_ghr_pa_requests_rec.award_amount := l_ghr_pa_requests_rec.to_total_salary;
3091: END IF;
3092:
3093:
3094: -- IF l_noa_family_code = 'AWARD' THEN --BUG#5328177
3093:
3094: -- IF l_noa_family_code = 'AWARD' THEN --BUG#5328177
3095: IF l_noa_family_code IN ('AWARD','GHR_INCENTIVE') THEN
3096:
3097: l_ghr_pa_requests_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3098: l_ghr_pa_requests_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3099: l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3100: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3101: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
3094: -- IF l_noa_family_code = 'AWARD' THEN --BUG#5328177
3095: IF l_noa_family_code IN ('AWARD','GHR_INCENTIVE') THEN
3096:
3097: l_ghr_pa_requests_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3098: l_ghr_pa_requests_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3099: l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3100: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3101: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
3102: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3095: IF l_noa_family_code IN ('AWARD','GHR_INCENTIVE') THEN
3096:
3097: l_ghr_pa_requests_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3098: l_ghr_pa_requests_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3099: l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3100: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3101: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
3102: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3103: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3096:
3097: l_ghr_pa_requests_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3098: l_ghr_pa_requests_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3099: l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3100: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3101: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
3102: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3103: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3104: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3097: l_ghr_pa_requests_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3098: l_ghr_pa_requests_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3099: l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3100: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3101: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
3102: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3103: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3104: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3105: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3098: l_ghr_pa_requests_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3099: l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3100: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3101: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
3102: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3103: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3104: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3105: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3106:
3099: l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3100: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3101: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
3102: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3103: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3104: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3105: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3106:
3107: END IF;
3100: l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3101: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
3102: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3103: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3104: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3105: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3106:
3107: END IF;
3108:
3101: l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
3102: l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3103: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3104: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3105: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3106:
3107: END IF;
3108:
3109: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
3105: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3106:
3107: END IF;
3108:
3109: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
3110: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3111: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3106:
3107: END IF;
3108:
3109: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
3110: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3111: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3107: END IF;
3108:
3109: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
3110: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3111: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3115: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
3108:
3109: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
3110: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3111: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3115: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
3116: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
3109: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
3110: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3111: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3115: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
3116: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
3117: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
3110: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3111: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3115: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
3116: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
3117: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
3118:
3111: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3115: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
3116: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
3117: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
3118:
3119:
3112: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3115: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
3116: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
3117: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
3118:
3119:
3120: IF l_noa_family_code <> 'AWARD' THEN
3113: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
3114: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
3115: l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
3116: l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
3117: l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
3118:
3119:
3120: IF l_noa_family_code <> 'AWARD' THEN
3121: -- Added following 'IF' according to bug# 1375333
3118:
3119:
3120: IF l_noa_family_code <> 'AWARD' THEN
3121: -- Added following 'IF' according to bug# 1375333
3122: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3123: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
3124: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3125: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
3126: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
3119:
3120: IF l_noa_family_code <> 'AWARD' THEN
3121: -- Added following 'IF' according to bug# 1375333
3122: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3123: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
3124: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3125: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
3126: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
3127: )
3120: IF l_noa_family_code <> 'AWARD' THEN
3121: -- Added following 'IF' according to bug# 1375333
3122: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3123: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
3124: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3125: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
3126: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
3127: )
3128: ) THEN
3121: -- Added following 'IF' according to bug# 1375333
3122: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3123: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
3124: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3125: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
3126: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
3127: )
3128: ) THEN
3129: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3122: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3123: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
3124: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3125: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
3126: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
3127: )
3128: ) THEN
3129: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3130: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3125: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
3126: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
3127: )
3128: ) THEN
3129: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3130: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3131: ELSE
3132: l_ghr_cpdf_temp_rec.to_pay_rate_determinant := NULL;
3133: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3126: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
3127: )
3128: ) THEN
3129: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3130: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
3131: ELSE
3132: l_ghr_cpdf_temp_rec.to_pay_rate_determinant := NULL;
3133: l_ghr_cpdf_temp_rec.to_occ_code := NULL;
3134: --
3144: END IF;
3145: END IF;
3146:
3147: -- Added following 'IF' according to bug# 1375333
3148: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3149: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
3150: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3151: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
3152: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
3145: END IF;
3146:
3147: -- Added following 'IF' according to bug# 1375333
3148: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3149: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
3150: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3151: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
3152: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
3153: )
3146:
3147: -- Added following 'IF' according to bug# 1375333
3148: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3149: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
3150: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3151: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
3152: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
3153: )
3154: ) THEN
3147: -- Added following 'IF' according to bug# 1375333
3148: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3149: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
3150: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3151: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
3152: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
3153: )
3154: ) THEN
3155:
3148: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3149: l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
3150: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3151: (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
3152: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
3153: )
3154: ) THEN
3155:
3156:
3153: )
3154: ) THEN
3155:
3156:
3157: l_ghr_cpdf_temp_rec.to_work_schedule := l_ghr_pa_requests_rec.work_schedule;
3158: l_ghr_cpdf_temp_rec.part_time_hours := l_ghr_pa_requests_rec.part_time_hours;
3159: --- commented for bug# 2257630 as duty station code required for all NOA codes except for cancellation action
3160: -- l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
3161:
3154: ) THEN
3155:
3156:
3157: l_ghr_cpdf_temp_rec.to_work_schedule := l_ghr_pa_requests_rec.work_schedule;
3158: l_ghr_cpdf_temp_rec.part_time_hours := l_ghr_pa_requests_rec.part_time_hours;
3159: --- commented for bug# 2257630 as duty station code required for all NOA codes except for cancellation action
3160: -- l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
3161:
3162: ELSE
3156:
3157: l_ghr_cpdf_temp_rec.to_work_schedule := l_ghr_pa_requests_rec.work_schedule;
3158: l_ghr_cpdf_temp_rec.part_time_hours := l_ghr_pa_requests_rec.part_time_hours;
3159: --- commented for bug# 2257630 as duty station code required for all NOA codes except for cancellation action
3160: -- l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
3161:
3162: ELSE
3163: l_ghr_cpdf_temp_rec.to_work_schedule := NULL;
3164: l_ghr_cpdf_temp_rec.part_time_hours := NULL;
3177: END IF;
3178: END IF;
3179: --End of BUG# 6631879
3180:
3181: l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
3182: l_ghr_cpdf_temp_rec.position_occupied := l_ghr_pa_requests_rec.position_occupied;
3183: l_ghr_cpdf_temp_rec.supervisory_status := l_ghr_pa_requests_rec.supervisory_status;
3184: -- Supervisory Status
3185:
3178: END IF;
3179: --End of BUG# 6631879
3180:
3181: l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
3182: l_ghr_cpdf_temp_rec.position_occupied := l_ghr_pa_requests_rec.position_occupied;
3183: l_ghr_cpdf_temp_rec.supervisory_status := l_ghr_pa_requests_rec.supervisory_status;
3184: -- Supervisory Status
3185:
3186: l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
3179: --End of BUG# 6631879
3180:
3181: l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
3182: l_ghr_cpdf_temp_rec.position_occupied := l_ghr_pa_requests_rec.position_occupied;
3183: l_ghr_cpdf_temp_rec.supervisory_status := l_ghr_pa_requests_rec.supervisory_status;
3184: -- Supervisory Status
3185:
3186: l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
3187:
3182: l_ghr_cpdf_temp_rec.position_occupied := l_ghr_pa_requests_rec.position_occupied;
3183: l_ghr_cpdf_temp_rec.supervisory_status := l_ghr_pa_requests_rec.supervisory_status;
3184: -- Supervisory Status
3185:
3186: l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
3187:
3188: -- Added IF for bug# 1375342
3189: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3190: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','825','826','827',
3185:
3186: l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
3187:
3188: -- Added IF for bug# 1375342
3189: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3190: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','825','826','827',
3191: '840','841','842','843','844','845','846','847','848','849','878','879')
3192: )
3193: OR
3186: l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
3187:
3188: -- Added IF for bug# 1375342
3189: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3190: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','825','826','827',
3191: '840','841','842','843','844','845','846','847','848','849','878','879')
3192: )
3193: OR
3194: l_ghr_pa_requests_rec.first_noa_code in ('815','816','817','825','826','827',
3190: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','825','826','827',
3191: '840','841','842','843','844','845','846','847','848','849','878','879')
3192: )
3193: OR
3194: l_ghr_pa_requests_rec.first_noa_code in ('815','816','817','825','826','827',
3195: '840','841','842','843','844','845','846','847','848','849','878','879')
3196: )
3197: AND
3198: NOT ( (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' ) OR
3194: l_ghr_pa_requests_rec.first_noa_code in ('815','816','817','825','826','827',
3195: '840','841','842','843','844','845','846','847','848','849','878','879')
3196: )
3197: AND
3198: NOT ( (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' ) OR
3199: (l_ghr_pa_requests_rec.first_noa_code ='002' and
3200: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%'
3201: )
3202: )
3195: '840','841','842','843','844','845','846','847','848','849','878','879')
3196: )
3197: AND
3198: NOT ( (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' ) OR
3199: (l_ghr_pa_requests_rec.first_noa_code ='002' and
3200: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%'
3201: )
3202: )
3203: THEN
3196: )
3197: AND
3198: NOT ( (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' ) OR
3199: (l_ghr_pa_requests_rec.first_noa_code ='002' and
3200: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%'
3201: )
3202: )
3203: THEN
3204:
3201: )
3202: )
3203: THEN
3204:
3205: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3206: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3207: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3202: )
3203: THEN
3204:
3205: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3206: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3207: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3210: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3203: THEN
3204:
3205: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3206: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3207: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3210: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3211: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3204:
3205: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3206: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3207: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3210: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3211: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3212: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3205: l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3206: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3207: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3210: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3211: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3212: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3213: END IF;
3206: l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3207: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3210: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3211: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3212: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3213: END IF;
3214:
3207: l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3210: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3211: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3212: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3213: END IF;
3214:
3215: -- NEW EHRI changes need these prior details
3208: l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3209: l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3210: l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3211: l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3212: l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3213: END IF;
3214:
3215: -- NEW EHRI changes need these prior details
3216: -- Madhuri
3214:
3215: -- NEW EHRI changes need these prior details
3216: -- Madhuri
3217:
3218: IF get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
3219: p_effective_date => l_ghr_pa_requests_rec.effective_date) <> '99' THEN
3220: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3221: l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
3222: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
3215: -- NEW EHRI changes need these prior details
3216: -- Madhuri
3217:
3218: IF get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
3219: p_effective_date => l_ghr_pa_requests_rec.effective_date) <> '99' THEN
3220: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3221: l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
3222: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
3223: (NVL(l_ghr_pa_requests_rec.first_noa_code,'@#') <> '002' OR
3216: -- Madhuri
3217:
3218: IF get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
3219: p_effective_date => l_ghr_pa_requests_rec.effective_date) <> '99' THEN
3220: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3221: l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
3222: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
3223: (NVL(l_ghr_pa_requests_rec.first_noa_code,'@#') <> '002' OR
3224: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') NOT LIKE '2%'
3217:
3218: IF get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
3219: p_effective_date => l_ghr_pa_requests_rec.effective_date) <> '99' THEN
3220: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3221: l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
3222: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
3223: (NVL(l_ghr_pa_requests_rec.first_noa_code,'@#') <> '002' OR
3224: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') NOT LIKE '2%'
3225: )
3218: IF get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
3219: p_effective_date => l_ghr_pa_requests_rec.effective_date) <> '99' THEN
3220: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3221: l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
3222: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
3223: (NVL(l_ghr_pa_requests_rec.first_noa_code,'@#') <> '002' OR
3224: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') NOT LIKE '2%'
3225: )
3226: ) THEN
3219: p_effective_date => l_ghr_pa_requests_rec.effective_date) <> '99' THEN
3220: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3221: l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
3222: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
3223: (NVL(l_ghr_pa_requests_rec.first_noa_code,'@#') <> '002' OR
3224: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') NOT LIKE '2%'
3225: )
3226: ) THEN
3227:
3220: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3221: l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
3222: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
3223: (NVL(l_ghr_pa_requests_rec.first_noa_code,'@#') <> '002' OR
3224: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') NOT LIKE '2%'
3225: )
3226: ) THEN
3227:
3228: --7507154 added Incentive Family
3226: ) THEN
3227:
3228: --7507154 added Incentive Family
3229: IF ( l_noa_family_code NOT IN ('AWARD','GHR_INCENTIVE') or
3230: (l_ghr_pa_requests_rec.first_noa_code ='885' or
3231: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')='885') )
3232: THEN
3233: --
3234: IF ((get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan)) <> 'FW') OR
3227:
3228: --7507154 added Incentive Family
3229: IF ( l_noa_family_code NOT IN ('AWARD','GHR_INCENTIVE') or
3230: (l_ghr_pa_requests_rec.first_noa_code ='885' or
3231: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')='885') )
3232: THEN
3233: --
3234: IF ((get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan)) <> 'FW') OR
3235: --9862674 pay plans starting with N or D need to have locality adjustment.
3230: (l_ghr_pa_requests_rec.first_noa_code ='885' or
3231: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')='885') )
3232: THEN
3233: --
3234: IF ((get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan)) <> 'FW') OR
3235: --9862674 pay plans starting with N or D need to have locality adjustment.
3236: (substr(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan),1,1) IN ('N','D')))
3237: THEN
3238: l_ghr_cpdf_temp_rec.from_locality_adj := NVL(l_ghr_pa_requests_rec.from_locality_adj, 0);
3232: THEN
3233: --
3234: IF ((get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan)) <> 'FW') OR
3235: --9862674 pay plans starting with N or D need to have locality adjustment.
3236: (substr(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan),1,1) IN ('N','D')))
3237: THEN
3238: l_ghr_cpdf_temp_rec.from_locality_adj := NVL(l_ghr_pa_requests_rec.from_locality_adj, 0);
3239: ELSE
3240: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3234: IF ((get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan)) <> 'FW') OR
3235: --9862674 pay plans starting with N or D need to have locality adjustment.
3236: (substr(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan),1,1) IN ('N','D')))
3237: THEN
3238: l_ghr_cpdf_temp_rec.from_locality_adj := NVL(l_ghr_pa_requests_rec.from_locality_adj, 0);
3239: ELSE
3240: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3241: END IF;
3242: --
3246: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3247: END IF;
3248:
3249:
3250: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3251: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '3%' AND
3252: (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
3253: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
3254: )
3247: END IF;
3248:
3249:
3250: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3251: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '3%' AND
3252: (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
3253: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
3254: )
3255: ) AND
3248:
3249:
3250: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3251: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '3%' AND
3252: (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
3253: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
3254: )
3255: ) AND
3256: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '4%' AND
3249:
3250: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3251: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '3%' AND
3252: (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
3253: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
3254: )
3255: ) AND
3256: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '4%' AND
3257: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3252: (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
3253: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
3254: )
3255: ) AND
3256: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '4%' AND
3257: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3258: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '4%'
3259: )
3260: ) THEN
3253: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
3254: )
3255: ) AND
3256: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '4%' AND
3257: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3258: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '4%'
3259: )
3260: ) THEN
3261: IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan)) <> 'FW' THEN
3254: )
3255: ) AND
3256: (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '4%' AND
3257: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3258: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '4%'
3259: )
3260: ) THEN
3261: IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan)) <> 'FW' THEN
3262: l_ghr_cpdf_temp_rec.to_locality_adj := NVL(l_ghr_pa_requests_rec.to_locality_adj, 0);
3257: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3258: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '4%'
3259: )
3260: ) THEN
3261: IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan)) <> 'FW' THEN
3262: l_ghr_cpdf_temp_rec.to_locality_adj := NVL(l_ghr_pa_requests_rec.to_locality_adj, 0);
3263: END IF;
3264: ELSE
3265: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3258: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '4%'
3259: )
3260: ) THEN
3261: IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan)) <> 'FW' THEN
3262: l_ghr_cpdf_temp_rec.to_locality_adj := NVL(l_ghr_pa_requests_rec.to_locality_adj, 0);
3263: END IF;
3264: ELSE
3265: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3266: END IF;
3270: -- l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3271: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3272: END IF;
3273:
3274: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3275: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3276: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3277: )
3278: ) AND
3271: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3272: END IF;
3273:
3274: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3275: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3276: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3277: )
3278: ) AND
3279: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3272: END IF;
3273:
3274: IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3275: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3276: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3277: )
3278: ) AND
3279: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3280: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3275: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3276: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3277: )
3278: ) AND
3279: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3280: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3281: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3282: )
3283: )
3276: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3277: )
3278: ) AND
3279: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3280: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3281: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3282: )
3283: )
3284: THEN -- Issue 20 4257213
3277: )
3278: ) AND
3279: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3280: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3281: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3282: )
3283: )
3284: THEN -- Issue 20 4257213
3285: l_ghr_cpdf_temp_rec.to_staffing_differential := l_ghr_pa_requests_rec.to_staffing_differential; -- format in report
3281: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3282: )
3283: )
3284: THEN -- Issue 20 4257213
3285: l_ghr_cpdf_temp_rec.to_staffing_differential := l_ghr_pa_requests_rec.to_staffing_differential; -- format in report
3286: l_ghr_cpdf_temp_rec.to_supervisory_differential := l_ghr_pa_requests_rec.to_supervisory_differential;-- format in report
3287: l_ghr_cpdf_temp_rec.to_retention_allowance := l_ghr_pa_requests_rec.to_retention_allowance; -- format in report
3288: ELSE
3289: l_ghr_cpdf_temp_rec.to_staffing_differential := NULL;
3282: )
3283: )
3284: THEN -- Issue 20 4257213
3285: l_ghr_cpdf_temp_rec.to_staffing_differential := l_ghr_pa_requests_rec.to_staffing_differential; -- format in report
3286: l_ghr_cpdf_temp_rec.to_supervisory_differential := l_ghr_pa_requests_rec.to_supervisory_differential;-- format in report
3287: l_ghr_cpdf_temp_rec.to_retention_allowance := l_ghr_pa_requests_rec.to_retention_allowance; -- format in report
3288: ELSE
3289: l_ghr_cpdf_temp_rec.to_staffing_differential := NULL;
3290: l_ghr_cpdf_temp_rec.to_supervisory_differential := NULL;
3283: )
3284: THEN -- Issue 20 4257213
3285: l_ghr_cpdf_temp_rec.to_staffing_differential := l_ghr_pa_requests_rec.to_staffing_differential; -- format in report
3286: l_ghr_cpdf_temp_rec.to_supervisory_differential := l_ghr_pa_requests_rec.to_supervisory_differential;-- format in report
3287: l_ghr_cpdf_temp_rec.to_retention_allowance := l_ghr_pa_requests_rec.to_retention_allowance; -- format in report
3288: ELSE
3289: l_ghr_cpdf_temp_rec.to_staffing_differential := NULL;
3290: l_ghr_cpdf_temp_rec.to_supervisory_differential := NULL;
3291: l_ghr_cpdf_temp_rec.to_retention_allowance := NULL;
3293:
3294:
3295:
3296: IF l_noa_family_code IN ('AWARD', 'OTHER_PAY','GHR_INCENTIVE') THEN -- Bug# 1400486 --GHR_INCENTIVE added for bug # 5328177
3297: IF l_ghr_pa_requests_rec.first_noa_code IN ('818', '819') OR
3298: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3299: l_ghr_pa_requests_rec.second_noa_code IN ('818', '819')) THEN
3300: IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
3301: -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
3294:
3295:
3296: IF l_noa_family_code IN ('AWARD', 'OTHER_PAY','GHR_INCENTIVE') THEN -- Bug# 1400486 --GHR_INCENTIVE added for bug # 5328177
3297: IF l_ghr_pa_requests_rec.first_noa_code IN ('818', '819') OR
3298: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3299: l_ghr_pa_requests_rec.second_noa_code IN ('818', '819')) THEN
3300: IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
3301: -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
3302: IF l_ghr_pa_requests_rec.first_noa_code = '818' OR
3295:
3296: IF l_noa_family_code IN ('AWARD', 'OTHER_PAY','GHR_INCENTIVE') THEN -- Bug# 1400486 --GHR_INCENTIVE added for bug # 5328177
3297: IF l_ghr_pa_requests_rec.first_noa_code IN ('818', '819') OR
3298: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3299: l_ghr_pa_requests_rec.second_noa_code IN ('818', '819')) THEN
3300: IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
3301: -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
3302: IF l_ghr_pa_requests_rec.first_noa_code = '818' OR
3303: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3298: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3299: l_ghr_pa_requests_rec.second_noa_code IN ('818', '819')) THEN
3300: IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
3301: -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
3302: IF l_ghr_pa_requests_rec.first_noa_code = '818' OR
3303: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3304: l_ghr_pa_requests_rec.second_noa_code = '818') THEN
3305: -- Bug#2789704 Added Exception Handling
3306: BEGIN
3299: l_ghr_pa_requests_rec.second_noa_code IN ('818', '819')) THEN
3300: IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
3301: -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
3302: IF l_ghr_pa_requests_rec.first_noa_code = '818' OR
3303: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3304: l_ghr_pa_requests_rec.second_noa_code = '818') THEN
3305: -- Bug#2789704 Added Exception Handling
3306: BEGIN
3307: ghr_api.retrieve_element_entry_value (p_element_name => 'AUO'
3300: IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
3301: -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
3302: IF l_ghr_pa_requests_rec.first_noa_code = '818' OR
3303: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3304: l_ghr_pa_requests_rec.second_noa_code = '818') THEN
3305: -- Bug#2789704 Added Exception Handling
3306: BEGIN
3307: ghr_api.retrieve_element_entry_value (p_element_name => 'AUO'
3308: ,p_input_value_name => 'Amount'
3305: -- Bug#2789704 Added Exception Handling
3306: BEGIN
3307: ghr_api.retrieve_element_entry_value (p_element_name => 'AUO'
3308: ,p_input_value_name => 'Amount'
3309: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3310: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3311: ,p_value => l_ghr_cpdf_temp_rec.award_amount
3312: ,p_multiple_error_flag => l_multi_error_flag);
3313: -- Bug# 10218675 modified to assigned the award amount as it is used for calculating
3306: BEGIN
3307: ghr_api.retrieve_element_entry_value (p_element_name => 'AUO'
3308: ,p_input_value_name => 'Amount'
3309: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3310: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3311: ,p_value => l_ghr_cpdf_temp_rec.award_amount
3312: ,p_multiple_error_flag => l_multi_error_flag);
3313: -- Bug# 10218675 modified to assigned the award amount as it is used for calculating
3314: -- award dollars
3311: ,p_value => l_ghr_cpdf_temp_rec.award_amount
3312: ,p_multiple_error_flag => l_multi_error_flag);
3313: -- Bug# 10218675 modified to assigned the award amount as it is used for calculating
3314: -- award dollars
3315: l_ghr_pa_requests_rec.award_amount := l_ghr_cpdf_temp_rec.award_amount;
3316: EXCEPTION
3317: WHEN OTHERS THEN
3318: l_message_name := 'retrieve_element_entry_value';
3319: --Bug # 9329643 Modified SSN to Emp No
3317: WHEN OTHERS THEN
3318: l_message_name := 'retrieve_element_entry_value';
3319: --Bug # 9329643 Modified SSN to Emp No
3320: l_log_text := 'Error in fetching AUO Amount for pa_request_id: '||
3321: l_ghr_pa_requests_rec.pa_request_id ||
3322: ' ; Emp No/employee last name' ||
3323: l_employee_number ||' / '||
3324: l_ghr_pa_Requests_rec.employee_last_name ||
3325: ' ; first NOAC/Second NOAC: '||
3320: l_log_text := 'Error in fetching AUO Amount for pa_request_id: '||
3321: l_ghr_pa_requests_rec.pa_request_id ||
3322: ' ; Emp No/employee last name' ||
3323: l_employee_number ||' / '||
3324: l_ghr_pa_Requests_rec.employee_last_name ||
3325: ' ; first NOAC/Second NOAC: '||
3326: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3327: l_ghr_pa_requests_rec.second_noa_code ||
3328: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3322: ' ; Emp No/employee last name' ||
3323: l_employee_number ||' / '||
3324: l_ghr_pa_Requests_rec.employee_last_name ||
3325: ' ; first NOAC/Second NOAC: '||
3326: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3327: l_ghr_pa_requests_rec.second_noa_code ||
3328: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3329:
3330: Raise EHRI_DYNRPT_ERROR;
3323: l_employee_number ||' / '||
3324: l_ghr_pa_Requests_rec.employee_last_name ||
3325: ' ; first NOAC/Second NOAC: '||
3326: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3327: l_ghr_pa_requests_rec.second_noa_code ||
3328: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3329:
3330: Raise EHRI_DYNRPT_ERROR;
3331: END;
3334: -- Bug#2789704 Added Exception Handling
3335: BEGIN
3336: ghr_api.retrieve_element_entry_value (p_element_name => 'Availability Pay'
3337: ,p_input_value_name => 'Amount'
3338: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3339: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3340: ,p_value => l_ghr_cpdf_temp_rec.award_amount
3341: ,p_multiple_error_flag => l_multi_error_flag);
3342: -- Bug# 10218675 modified to assigned the award amount as it is used for calculating
3335: BEGIN
3336: ghr_api.retrieve_element_entry_value (p_element_name => 'Availability Pay'
3337: ,p_input_value_name => 'Amount'
3338: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3339: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3340: ,p_value => l_ghr_cpdf_temp_rec.award_amount
3341: ,p_multiple_error_flag => l_multi_error_flag);
3342: -- Bug# 10218675 modified to assigned the award amount as it is used for calculating
3343: -- award dollars
3340: ,p_value => l_ghr_cpdf_temp_rec.award_amount
3341: ,p_multiple_error_flag => l_multi_error_flag);
3342: -- Bug# 10218675 modified to assigned the award amount as it is used for calculating
3343: -- award dollars
3344: l_ghr_pa_requests_rec.award_amount := l_ghr_cpdf_temp_rec.award_amount;
3345: EXCEPTION
3346: WHEN OTHERS THEN
3347: l_message_name := 'retrieve_element_entry_value';
3348: --Bug # 9329643 Modified SSN to Emp No
3346: WHEN OTHERS THEN
3347: l_message_name := 'retrieve_element_entry_value';
3348: --Bug # 9329643 Modified SSN to Emp No
3349: l_log_text := 'Error in fetching Availability Pay Amount for pa_request_id: '||
3350: l_ghr_pa_requests_rec.pa_request_id ||
3351: ' ; Emp No/employee last name' ||
3352: l_employee_number ||' / '||
3353: l_ghr_pa_Requests_rec.employee_last_name ||
3354: ' ; first NOAC/Second NOAC: '||
3349: l_log_text := 'Error in fetching Availability Pay Amount for pa_request_id: '||
3350: l_ghr_pa_requests_rec.pa_request_id ||
3351: ' ; Emp No/employee last name' ||
3352: l_employee_number ||' / '||
3353: l_ghr_pa_Requests_rec.employee_last_name ||
3354: ' ; first NOAC/Second NOAC: '||
3355: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3356: l_ghr_pa_requests_rec.second_noa_code ||
3357: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3351: ' ; Emp No/employee last name' ||
3352: l_employee_number ||' / '||
3353: l_ghr_pa_Requests_rec.employee_last_name ||
3354: ' ; first NOAC/Second NOAC: '||
3355: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3356: l_ghr_pa_requests_rec.second_noa_code ||
3357: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3358:
3359: Raise EHRI_DYNRPT_ERROR;
3352: l_employee_number ||' / '||
3353: l_ghr_pa_Requests_rec.employee_last_name ||
3354: ' ; first NOAC/Second NOAC: '||
3355: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3356: l_ghr_pa_requests_rec.second_noa_code ||
3357: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3358:
3359: Raise EHRI_DYNRPT_ERROR;
3360: END;
3368: -- Bug#2789704 Added Exception Handling
3369: BEGIN
3370: ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
3371: ,p_input_value_name => 'Amount'
3372: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3373: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3374: ,p_value => l_ghr_cpdf_temp_rec.to_supervisory_differential
3375: ,p_multiple_error_flag => l_multi_error_flag);
3376: EXCEPTION
3369: BEGIN
3370: ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
3371: ,p_input_value_name => 'Amount'
3372: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3373: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3374: ,p_value => l_ghr_cpdf_temp_rec.to_supervisory_differential
3375: ,p_multiple_error_flag => l_multi_error_flag);
3376: EXCEPTION
3377: WHEN OTHERS THEN
3377: WHEN OTHERS THEN
3378: l_message_name := 'retrieve_element_entry_value';
3379: --Bug # 9329643 Modified SSN to Emp No
3380: l_log_text := 'Error in fetching Supervisory Differential Amount for pa_request_id: '||
3381: l_ghr_pa_requests_rec.pa_request_id ||
3382: ' ; Emp No/employee last name' ||
3383: l_employee_number ||' / '||
3384: l_ghr_pa_Requests_rec.employee_last_name ||
3385: ' ; first NOAC/Second NOAC: '||
3380: l_log_text := 'Error in fetching Supervisory Differential Amount for pa_request_id: '||
3381: l_ghr_pa_requests_rec.pa_request_id ||
3382: ' ; Emp No/employee last name' ||
3383: l_employee_number ||' / '||
3384: l_ghr_pa_Requests_rec.employee_last_name ||
3385: ' ; first NOAC/Second NOAC: '||
3386: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3387: l_ghr_pa_requests_rec.second_noa_code ||
3388: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3382: ' ; Emp No/employee last name' ||
3383: l_employee_number ||' / '||
3384: l_ghr_pa_Requests_rec.employee_last_name ||
3385: ' ; first NOAC/Second NOAC: '||
3386: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3387: l_ghr_pa_requests_rec.second_noa_code ||
3388: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3389:
3390: Raise EHRI_DYNRPT_ERROR;
3383: l_employee_number ||' / '||
3384: l_ghr_pa_Requests_rec.employee_last_name ||
3385: ' ; first NOAC/Second NOAC: '||
3386: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3387: l_ghr_pa_requests_rec.second_noa_code ||
3388: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3389:
3390: Raise EHRI_DYNRPT_ERROR;
3391: END;
3396: -- Bug#2789704 Added Exception Handling
3397: BEGIN
3398: ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
3399: ,p_input_value_name => 'Amount'
3400: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3401: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3402: ,p_value => l_ghr_cpdf_temp_rec.to_retention_allowance
3403: ,p_multiple_error_flag => l_multi_error_flag);
3404: EXCEPTION
3397: BEGIN
3398: ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
3399: ,p_input_value_name => 'Amount'
3400: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3401: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3402: ,p_value => l_ghr_cpdf_temp_rec.to_retention_allowance
3403: ,p_multiple_error_flag => l_multi_error_flag);
3404: EXCEPTION
3405: WHEN OTHERS THEN
3405: WHEN OTHERS THEN
3406: l_message_name := 'retrieve_element_entry_value';
3407: --Bug # 9329643 Modified SSN to Emp No
3408: l_log_text := 'Error in fetching Retention Allowance Amount for pa_request_id: '||
3409: l_ghr_pa_requests_rec.pa_request_id ||
3410: ' ; Emp No/employee last name' ||
3411: l_employee_number ||' / '||
3412: l_ghr_pa_Requests_rec.employee_last_name ||
3413: ' ; first NOAC/Second NOAC: '||
3408: l_log_text := 'Error in fetching Retention Allowance Amount for pa_request_id: '||
3409: l_ghr_pa_requests_rec.pa_request_id ||
3410: ' ; Emp No/employee last name' ||
3411: l_employee_number ||' / '||
3412: l_ghr_pa_Requests_rec.employee_last_name ||
3413: ' ; first NOAC/Second NOAC: '||
3414: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3415: l_ghr_pa_requests_rec.second_noa_code ||
3416: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3410: ' ; Emp No/employee last name' ||
3411: l_employee_number ||' / '||
3412: l_ghr_pa_Requests_rec.employee_last_name ||
3413: ' ; first NOAC/Second NOAC: '||
3414: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3415: l_ghr_pa_requests_rec.second_noa_code ||
3416: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3417:
3418: Raise EHRI_DYNRPT_ERROR;
3411: l_employee_number ||' / '||
3412: l_ghr_pa_Requests_rec.employee_last_name ||
3413: ' ; first NOAC/Second NOAC: '||
3414: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3415: l_ghr_pa_requests_rec.second_noa_code ||
3416: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3417:
3418: Raise EHRI_DYNRPT_ERROR;
3419: END;
3423: END IF;
3424:
3425:
3426: -- Sundar Changes for education
3427: ghr_api.return_education_details(p_person_id => l_ghr_pa_requests_rec.person_id,
3428: p_effective_date => l_ghr_pa_requests_rec.effective_date,
3429: p_education_level => l_ghr_cpdf_temp_rec.education_level,
3430: p_academic_discipline => l_ghr_cpdf_temp_rec.academic_discipline,
3431: p_year_degree_attained => l_ghr_cpdf_temp_rec.year_degree_attained);
3424:
3425:
3426: -- Sundar Changes for education
3427: ghr_api.return_education_details(p_person_id => l_ghr_pa_requests_rec.person_id,
3428: p_effective_date => l_ghr_pa_requests_rec.effective_date,
3429: p_education_level => l_ghr_cpdf_temp_rec.education_level,
3430: p_academic_discipline => l_ghr_cpdf_temp_rec.academic_discipline,
3431: p_year_degree_attained => l_ghr_cpdf_temp_rec.year_degree_attained);
3432: -- End Sundar changes for education Commented below code
3439: -- it must come from to_position if there, if not from the from_position
3440: --
3441: -- Bug#2789704 Added Exception Handling
3442: BEGIN
3443: get_org_comp (NVL(l_ghr_pa_requests_rec.to_position_id
3444: ,l_ghr_pa_requests_rec.from_position_id)
3445: ,l_ghr_pa_requests_rec.effective_date
3446: ,l_ghr_cpdf_temp_rec.organizational_component);
3447: -- Organizational Component Code
3440: --
3441: -- Bug#2789704 Added Exception Handling
3442: BEGIN
3443: get_org_comp (NVL(l_ghr_pa_requests_rec.to_position_id
3444: ,l_ghr_pa_requests_rec.from_position_id)
3445: ,l_ghr_pa_requests_rec.effective_date
3446: ,l_ghr_cpdf_temp_rec.organizational_component);
3447: -- Organizational Component Code
3448: EXCEPTION
3441: -- Bug#2789704 Added Exception Handling
3442: BEGIN
3443: get_org_comp (NVL(l_ghr_pa_requests_rec.to_position_id
3444: ,l_ghr_pa_requests_rec.from_position_id)
3445: ,l_ghr_pa_requests_rec.effective_date
3446: ,l_ghr_cpdf_temp_rec.organizational_component);
3447: -- Organizational Component Code
3448: EXCEPTION
3449: WHEN OTHERS THEN
3449: WHEN OTHERS THEN
3450: l_message_name := 'get_org_comp';
3451: --Bug # 9329643 Modified SSN to Emp No
3452: l_log_text := 'Error in fetching OPM Organizational Component for pa_request_id: '||
3453: l_ghr_pa_requests_rec.pa_request_id ||
3454: ' ; Emp No/employee last name' ||
3455: l_employee_number ||' / '||
3456: l_ghr_pa_Requests_rec.employee_last_name ||
3457: ' ; first NOAC/Second NOAC: '||
3452: l_log_text := 'Error in fetching OPM Organizational Component for pa_request_id: '||
3453: l_ghr_pa_requests_rec.pa_request_id ||
3454: ' ; Emp No/employee last name' ||
3455: l_employee_number ||' / '||
3456: l_ghr_pa_Requests_rec.employee_last_name ||
3457: ' ; first NOAC/Second NOAC: '||
3458: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3459: l_ghr_pa_requests_rec.second_noa_code ||
3460: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3454: ' ; Emp No/employee last name' ||
3455: l_employee_number ||' / '||
3456: l_ghr_pa_Requests_rec.employee_last_name ||
3457: ' ; first NOAC/Second NOAC: '||
3458: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3459: l_ghr_pa_requests_rec.second_noa_code ||
3460: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3461:
3462: Raise EHRI_DYNRPT_ERROR;
3455: l_employee_number ||' / '||
3456: l_ghr_pa_Requests_rec.employee_last_name ||
3457: ' ; first NOAC/Second NOAC: '||
3458: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3459: l_ghr_pa_requests_rec.second_noa_code ||
3460: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3461:
3462: Raise EHRI_DYNRPT_ERROR;
3463: END;
3462: Raise EHRI_DYNRPT_ERROR;
3463: END;
3464: -- Getting appropriation code new EHRI CHanges.
3465: BEGIN
3466: get_appr_code(NVL(l_ghr_pa_requests_rec.to_position_id
3467: ,l_ghr_pa_requests_rec.from_position_id)
3468: ,l_ghr_pa_requests_rec.effective_date
3469: ,l_ghr_cpdf_temp_rec.APPROPRIATION_CODE);
3470: -- Organizational Component Code
3463: END;
3464: -- Getting appropriation code new EHRI CHanges.
3465: BEGIN
3466: get_appr_code(NVL(l_ghr_pa_requests_rec.to_position_id
3467: ,l_ghr_pa_requests_rec.from_position_id)
3468: ,l_ghr_pa_requests_rec.effective_date
3469: ,l_ghr_cpdf_temp_rec.APPROPRIATION_CODE);
3470: -- Organizational Component Code
3471: EXCEPTION
3464: -- Getting appropriation code new EHRI CHanges.
3465: BEGIN
3466: get_appr_code(NVL(l_ghr_pa_requests_rec.to_position_id
3467: ,l_ghr_pa_requests_rec.from_position_id)
3468: ,l_ghr_pa_requests_rec.effective_date
3469: ,l_ghr_cpdf_temp_rec.APPROPRIATION_CODE);
3470: -- Organizational Component Code
3471: EXCEPTION
3472: WHEN OTHERS THEN
3472: WHEN OTHERS THEN
3473: l_message_name := 'get_appr_code';
3474: --Bug # 9329643 Modified SSN to Emp No
3475: l_log_text := 'Error in fetching Appropriation code for the position of : '||
3476: l_ghr_pa_requests_rec.pa_request_id ||
3477: ' ; Emp No/employee last name' ||
3478: l_employee_number ||' / '||
3479: l_ghr_pa_Requests_rec.employee_last_name ||
3480: ' ; first NOAC/Second NOAC: '||
3475: l_log_text := 'Error in fetching Appropriation code for the position of : '||
3476: l_ghr_pa_requests_rec.pa_request_id ||
3477: ' ; Emp No/employee last name' ||
3478: l_employee_number ||' / '||
3479: l_ghr_pa_Requests_rec.employee_last_name ||
3480: ' ; first NOAC/Second NOAC: '||
3481: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3482: l_ghr_pa_requests_rec.second_noa_code ||
3483: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3477: ' ; Emp No/employee last name' ||
3478: l_employee_number ||' / '||
3479: l_ghr_pa_Requests_rec.employee_last_name ||
3480: ' ; first NOAC/Second NOAC: '||
3481: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3482: l_ghr_pa_requests_rec.second_noa_code ||
3483: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3484:
3485: Raise EHRI_DYNRPT_ERROR;
3478: l_employee_number ||' / '||
3479: l_ghr_pa_Requests_rec.employee_last_name ||
3480: ' ; first NOAC/Second NOAC: '||
3481: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3482: l_ghr_pa_requests_rec.second_noa_code ||
3483: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3484:
3485: Raise EHRI_DYNRPT_ERROR;
3486: END;
3488: --
3489: -- 3.3) Get Sex
3490: -- Bug#2789704 Added Exception Handling
3491: BEGIN
3492: get_sex (l_ghr_pa_requests_rec.person_id
3493: ,l_ghr_pa_requests_rec.effective_date
3494: ,l_ghr_cpdf_temp_rec.sex);
3495: EXCEPTION
3496: WHEN OTHERS THEN
3489: -- 3.3) Get Sex
3490: -- Bug#2789704 Added Exception Handling
3491: BEGIN
3492: get_sex (l_ghr_pa_requests_rec.person_id
3493: ,l_ghr_pa_requests_rec.effective_date
3494: ,l_ghr_cpdf_temp_rec.sex);
3495: EXCEPTION
3496: WHEN OTHERS THEN
3497: l_message_name := 'get_sex';
3496: WHEN OTHERS THEN
3497: l_message_name := 'get_sex';
3498: --Bug # 9329643 Modified SSN to Emp No
3499: l_log_text := 'Error in fetching SEX for pa_request_id: '||
3500: l_ghr_pa_requests_rec.pa_request_id ||
3501: ' ; Emp No/employee last name' ||
3502: l_employee_number ||' / '||
3503: l_ghr_pa_Requests_rec.employee_last_name ||
3504: ' ; first NOAC/Second NOAC: '||
3499: l_log_text := 'Error in fetching SEX for pa_request_id: '||
3500: l_ghr_pa_requests_rec.pa_request_id ||
3501: ' ; Emp No/employee last name' ||
3502: l_employee_number ||' / '||
3503: l_ghr_pa_Requests_rec.employee_last_name ||
3504: ' ; first NOAC/Second NOAC: '||
3505: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3506: l_ghr_pa_requests_rec.second_noa_code ||
3507: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3501: ' ; Emp No/employee last name' ||
3502: l_employee_number ||' / '||
3503: l_ghr_pa_Requests_rec.employee_last_name ||
3504: ' ; first NOAC/Second NOAC: '||
3505: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3506: l_ghr_pa_requests_rec.second_noa_code ||
3507: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3508:
3509: Raise EHRI_DYNRPT_ERROR;
3502: l_employee_number ||' / '||
3503: l_ghr_pa_Requests_rec.employee_last_name ||
3504: ' ; first NOAC/Second NOAC: '||
3505: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3506: l_ghr_pa_requests_rec.second_noa_code ||
3507: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3508:
3509: Raise EHRI_DYNRPT_ERROR;
3510: END;
3513: -- 3.4) Get person SIT - 'US Fed Perf Appraisal'
3514: --
3515: -- Bug#2789704 Added Exception Handling
3516: BEGIN
3517: get_per_sit_perf_appraisal(l_ghr_pa_requests_rec.person_id
3518: ,l_ghr_pa_requests_rec.effective_date
3519: ,l_ghr_cpdf_temp_rec.rating_of_record_level
3520: ,l_ghr_cpdf_temp_rec.rating_of_record_pattern
3521: ,l_ghr_cpdf_temp_rec.rating_of_record_period_ends
3514: --
3515: -- Bug#2789704 Added Exception Handling
3516: BEGIN
3517: get_per_sit_perf_appraisal(l_ghr_pa_requests_rec.person_id
3518: ,l_ghr_pa_requests_rec.effective_date
3519: ,l_ghr_cpdf_temp_rec.rating_of_record_level
3520: ,l_ghr_cpdf_temp_rec.rating_of_record_pattern
3521: ,l_ghr_cpdf_temp_rec.rating_of_record_period_ends
3522: ,l_ghr_cpdf_temp_rec.rating_of_record_period_starts); -- format in report
3524: WHEN OTHERS THEN
3525: l_message_name := 'get_per_sit_perf_apprisal';
3526: --Bug # 9329643 Modified SSN to Emp No
3527: l_log_text := 'Error in fetching Performance Apprisal details for pa_request_id: '||
3528: to_char(l_ghr_pa_requests_rec.pa_request_id) ||
3529: ' ; Emp No/employee last name' ||
3530: l_employee_number ||' / '||
3531: l_ghr_pa_Requests_rec.employee_last_name ||
3532: ' ; first NOAC/Second NOAC: '||
3527: l_log_text := 'Error in fetching Performance Apprisal details for pa_request_id: '||
3528: to_char(l_ghr_pa_requests_rec.pa_request_id) ||
3529: ' ; Emp No/employee last name' ||
3530: l_employee_number ||' / '||
3531: l_ghr_pa_Requests_rec.employee_last_name ||
3532: ' ; first NOAC/Second NOAC: '||
3533: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3534: l_ghr_pa_requests_rec.second_noa_code ||
3535: ' ; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3529: ' ; Emp No/employee last name' ||
3530: l_employee_number ||' / '||
3531: l_ghr_pa_Requests_rec.employee_last_name ||
3532: ' ; first NOAC/Second NOAC: '||
3533: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3534: l_ghr_pa_requests_rec.second_noa_code ||
3535: ' ; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3536: Raise EHRI_DYNRPT_ERROR;
3537:
3530: l_employee_number ||' / '||
3531: l_ghr_pa_Requests_rec.employee_last_name ||
3532: ' ; first NOAC/Second NOAC: '||
3533: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3534: l_ghr_pa_requests_rec.second_noa_code ||
3535: ' ; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3536: Raise EHRI_DYNRPT_ERROR;
3537:
3538: END;
3541: -- 3.5) Get PAR Extra Info Noa specific
3542: --
3543: -- Bug#2789704 Added Exception Handling
3544: BEGIN
3545: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
3546: ,l_ghr_pa_requests_rec.first_noa_id
3547: ,l_ghr_pa_requests_rec.second_noa_id
3548: ,l_ghr_pa_requests_rec.noa_family_code
3549: ,l_ghr_pa_requests_rec.person_id
3542: --
3543: -- Bug#2789704 Added Exception Handling
3544: BEGIN
3545: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
3546: ,l_ghr_pa_requests_rec.first_noa_id
3547: ,l_ghr_pa_requests_rec.second_noa_id
3548: ,l_ghr_pa_requests_rec.noa_family_code
3549: ,l_ghr_pa_requests_rec.person_id
3550: ,l_ghr_pa_requests_rec.effective_date
3543: -- Bug#2789704 Added Exception Handling
3544: BEGIN
3545: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
3546: ,l_ghr_pa_requests_rec.first_noa_id
3547: ,l_ghr_pa_requests_rec.second_noa_id
3548: ,l_ghr_pa_requests_rec.noa_family_code
3549: ,l_ghr_pa_requests_rec.person_id
3550: ,l_ghr_pa_requests_rec.effective_date
3551: ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
3544: BEGIN
3545: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
3546: ,l_ghr_pa_requests_rec.first_noa_id
3547: ,l_ghr_pa_requests_rec.second_noa_id
3548: ,l_ghr_pa_requests_rec.noa_family_code
3549: ,l_ghr_pa_requests_rec.person_id
3550: ,l_ghr_pa_requests_rec.effective_date
3551: ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
3552: ,l_ghr_cpdf_temp_rec.frozen_service -- no format assumed yymm?
3545: get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
3546: ,l_ghr_pa_requests_rec.first_noa_id
3547: ,l_ghr_pa_requests_rec.second_noa_id
3548: ,l_ghr_pa_requests_rec.noa_family_code
3549: ,l_ghr_pa_requests_rec.person_id
3550: ,l_ghr_pa_requests_rec.effective_date
3551: ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
3552: ,l_ghr_cpdf_temp_rec.frozen_service -- no format assumed yymm?
3553: ,l_ghr_cpdf_temp_rec.from_retirement_coverage -- previous retirement coverage
3546: ,l_ghr_pa_requests_rec.first_noa_id
3547: ,l_ghr_pa_requests_rec.second_noa_id
3548: ,l_ghr_pa_requests_rec.noa_family_code
3549: ,l_ghr_pa_requests_rec.person_id
3550: ,l_ghr_pa_requests_rec.effective_date
3551: ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
3552: ,l_ghr_cpdf_temp_rec.frozen_service -- no format assumed yymm?
3553: ,l_ghr_cpdf_temp_rec.from_retirement_coverage -- previous retirement coverage
3554: ,l_ghr_cpdf_temp_rec.race_national_origin
3590: WHEN OTHERS THEN
3591: l_message_name := 'get_par_ei_noac';
3592: --Bug # 9329643 Modified SSN to Emp No
3593: l_log_text := 'Error in fetching PA Record Extra Information for pa_request_id: '||
3594: l_ghr_pa_requests_rec.pa_request_id ||
3595: ' ; Emp No/employee last name' ||
3596: l_employee_number ||' / '||
3597: l_ghr_pa_Requests_rec.employee_last_name ||
3598: ' ; first NOAC/Second NOAC: '||
3593: l_log_text := 'Error in fetching PA Record Extra Information for pa_request_id: '||
3594: l_ghr_pa_requests_rec.pa_request_id ||
3595: ' ; Emp No/employee last name' ||
3596: l_employee_number ||' / '||
3597: l_ghr_pa_Requests_rec.employee_last_name ||
3598: ' ; first NOAC/Second NOAC: '||
3599: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3600: l_ghr_pa_requests_rec.second_noa_code ||
3601: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3595: ' ; Emp No/employee last name' ||
3596: l_employee_number ||' / '||
3597: l_ghr_pa_Requests_rec.employee_last_name ||
3598: ' ; first NOAC/Second NOAC: '||
3599: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3600: l_ghr_pa_requests_rec.second_noa_code ||
3601: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3602:
3603: Raise EHRI_DYNRPT_ERROR;
3596: l_employee_number ||' / '||
3597: l_ghr_pa_Requests_rec.employee_last_name ||
3598: ' ; first NOAC/Second NOAC: '||
3599: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3600: l_ghr_pa_requests_rec.second_noa_code ||
3601: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3602:
3603: Raise EHRI_DYNRPT_ERROR;
3604: END;
3604: END;
3605: -- Bug#2789704 Added Exception Handling
3606:
3607: -- Bug# 1375342
3608: IF (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' OR
3609: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3610: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')) THEN
3611:
3612: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3605: -- Bug#2789704 Added Exception Handling
3606:
3607: -- Bug# 1375342
3608: IF (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' OR
3609: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3610: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')) THEN
3611:
3612: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3613: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3606:
3607: -- Bug# 1375342
3608: IF (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' OR
3609: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3610: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')) THEN
3611:
3612: l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3613: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3614: l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3627: -- get current appointment auth codes.
3628: -- Bug#2789704 Added Exception Handling
3629: BEGIN
3630: ghr_sf52_pre_update.get_auth_codes
3631: (p_pa_req_rec => l_ghr_pa_requests_rec
3632: ,p_auth_code1 => l_ghr_cpdf_temp_rec.current_appointment_auth1
3633: ,p_auth_code2 => l_ghr_cpdf_temp_rec.current_appointment_auth2);
3634:
3635: --Bug # 13028724 Modified to get first 3 characters
3640: WHEN OTHERS THEN
3641: l_message_name := 'get_auth_codes';
3642: --Bug # 9329643 Modified SSN to Emp No
3643: l_log_text := 'Error in fetching Current Appointment Authority Codes for pa_request_id: '||
3644: l_ghr_pa_requests_rec.pa_request_id ||
3645: ' ; Emp No/employee last name' ||
3646: l_employee_number ||' / '||
3647: l_ghr_pa_Requests_rec.employee_last_name ||
3648: ' ; first NOAC/Second NOAC: '||
3643: l_log_text := 'Error in fetching Current Appointment Authority Codes for pa_request_id: '||
3644: l_ghr_pa_requests_rec.pa_request_id ||
3645: ' ; Emp No/employee last name' ||
3646: l_employee_number ||' / '||
3647: l_ghr_pa_Requests_rec.employee_last_name ||
3648: ' ; first NOAC/Second NOAC: '||
3649: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3650: l_ghr_pa_requests_rec.second_noa_code ||
3651: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3645: ' ; Emp No/employee last name' ||
3646: l_employee_number ||' / '||
3647: l_ghr_pa_Requests_rec.employee_last_name ||
3648: ' ; first NOAC/Second NOAC: '||
3649: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3650: l_ghr_pa_requests_rec.second_noa_code ||
3651: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3652:
3653: Raise EHRI_DYNRPT_ERROR;
3646: l_employee_number ||' / '||
3647: l_ghr_pa_Requests_rec.employee_last_name ||
3648: ' ; first NOAC/Second NOAC: '||
3649: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3650: l_ghr_pa_requests_rec.second_noa_code ||
3651: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3652:
3653: Raise EHRI_DYNRPT_ERROR;
3654: END;
3655: -- Bug#2789704 Added Exception Handling
3656: --
3657: -- 3.6) Get PRIOR Work Schedule and Pay Rate Determinant
3658: --
3659: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3660: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')
3661: OR
3662: l_ghr_pa_requests_rec.first_noa_code LIKE '2%'
3663: )
3656: --
3657: -- 3.6) Get PRIOR Work Schedule and Pay Rate Determinant
3658: --
3659: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3660: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')
3661: OR
3662: l_ghr_pa_requests_rec.first_noa_code LIKE '2%'
3663: )
3664: THEN
3658: --
3659: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3660: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')
3661: OR
3662: l_ghr_pa_requests_rec.first_noa_code LIKE '2%'
3663: )
3664: THEN
3665:
3666: -- Bug#2789704 Added Exception Handling
3664: THEN
3665:
3666: -- Bug#2789704 Added Exception Handling
3667: BEGIN
3668: get_prior_ws_prd_ds (l_ghr_pa_requests_rec.pa_request_id
3669: ,l_ghr_pa_requests_rec.altered_pa_request_id
3670: ,l_ghr_pa_requests_rec.first_noa_id
3671: ,l_ghr_pa_requests_rec.second_noa_id
3672: ,l_ghr_pa_requests_rec.person_id
3665:
3666: -- Bug#2789704 Added Exception Handling
3667: BEGIN
3668: get_prior_ws_prd_ds (l_ghr_pa_requests_rec.pa_request_id
3669: ,l_ghr_pa_requests_rec.altered_pa_request_id
3670: ,l_ghr_pa_requests_rec.first_noa_id
3671: ,l_ghr_pa_requests_rec.second_noa_id
3672: ,l_ghr_pa_requests_rec.person_id
3673: ,l_ghr_pa_requests_rec.employee_assignment_id
3666: -- Bug#2789704 Added Exception Handling
3667: BEGIN
3668: get_prior_ws_prd_ds (l_ghr_pa_requests_rec.pa_request_id
3669: ,l_ghr_pa_requests_rec.altered_pa_request_id
3670: ,l_ghr_pa_requests_rec.first_noa_id
3671: ,l_ghr_pa_requests_rec.second_noa_id
3672: ,l_ghr_pa_requests_rec.person_id
3673: ,l_ghr_pa_requests_rec.employee_assignment_id
3674: ,l_ghr_pa_requests_rec.from_position_id
3667: BEGIN
3668: get_prior_ws_prd_ds (l_ghr_pa_requests_rec.pa_request_id
3669: ,l_ghr_pa_requests_rec.altered_pa_request_id
3670: ,l_ghr_pa_requests_rec.first_noa_id
3671: ,l_ghr_pa_requests_rec.second_noa_id
3672: ,l_ghr_pa_requests_rec.person_id
3673: ,l_ghr_pa_requests_rec.employee_assignment_id
3674: ,l_ghr_pa_requests_rec.from_position_id
3675: ,l_ghr_pa_requests_rec.effective_date
3668: get_prior_ws_prd_ds (l_ghr_pa_requests_rec.pa_request_id
3669: ,l_ghr_pa_requests_rec.altered_pa_request_id
3670: ,l_ghr_pa_requests_rec.first_noa_id
3671: ,l_ghr_pa_requests_rec.second_noa_id
3672: ,l_ghr_pa_requests_rec.person_id
3673: ,l_ghr_pa_requests_rec.employee_assignment_id
3674: ,l_ghr_pa_requests_rec.from_position_id
3675: ,l_ghr_pa_requests_rec.effective_date
3676: ,l_ghr_pa_requests_rec.status
3669: ,l_ghr_pa_requests_rec.altered_pa_request_id
3670: ,l_ghr_pa_requests_rec.first_noa_id
3671: ,l_ghr_pa_requests_rec.second_noa_id
3672: ,l_ghr_pa_requests_rec.person_id
3673: ,l_ghr_pa_requests_rec.employee_assignment_id
3674: ,l_ghr_pa_requests_rec.from_position_id
3675: ,l_ghr_pa_requests_rec.effective_date
3676: ,l_ghr_pa_requests_rec.status
3677: ,l_ghr_cpdf_temp_rec.from_work_schedule
3670: ,l_ghr_pa_requests_rec.first_noa_id
3671: ,l_ghr_pa_requests_rec.second_noa_id
3672: ,l_ghr_pa_requests_rec.person_id
3673: ,l_ghr_pa_requests_rec.employee_assignment_id
3674: ,l_ghr_pa_requests_rec.from_position_id
3675: ,l_ghr_pa_requests_rec.effective_date
3676: ,l_ghr_pa_requests_rec.status
3677: ,l_ghr_cpdf_temp_rec.from_work_schedule
3678: ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
3671: ,l_ghr_pa_requests_rec.second_noa_id
3672: ,l_ghr_pa_requests_rec.person_id
3673: ,l_ghr_pa_requests_rec.employee_assignment_id
3674: ,l_ghr_pa_requests_rec.from_position_id
3675: ,l_ghr_pa_requests_rec.effective_date
3676: ,l_ghr_pa_requests_rec.status
3677: ,l_ghr_cpdf_temp_rec.from_work_schedule
3678: ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
3679: ,l_ghr_cpdf_temp_rec.from_duty_station_code);
3672: ,l_ghr_pa_requests_rec.person_id
3673: ,l_ghr_pa_requests_rec.employee_assignment_id
3674: ,l_ghr_pa_requests_rec.from_position_id
3675: ,l_ghr_pa_requests_rec.effective_date
3676: ,l_ghr_pa_requests_rec.status
3677: ,l_ghr_cpdf_temp_rec.from_work_schedule
3678: ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
3679: ,l_ghr_cpdf_temp_rec.from_duty_station_code);
3680:
3677: ,l_ghr_cpdf_temp_rec.from_work_schedule
3678: ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
3679: ,l_ghr_cpdf_temp_rec.from_duty_station_code);
3680:
3681: IF (l_ghr_pa_requests_rec.first_noa_code IN ('817') OR
3682: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3683: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')= '817')
3684: )
3685: THEN
3678: ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
3679: ,l_ghr_cpdf_temp_rec.from_duty_station_code);
3680:
3681: IF (l_ghr_pa_requests_rec.first_noa_code IN ('817') OR
3682: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3683: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')= '817')
3684: )
3685: THEN
3686: l_ghr_cpdf_temp_rec.from_work_schedule := NULL;
3679: ,l_ghr_cpdf_temp_rec.from_duty_station_code);
3680:
3681: IF (l_ghr_pa_requests_rec.first_noa_code IN ('817') OR
3682: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3683: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')= '817')
3684: )
3685: THEN
3686: l_ghr_cpdf_temp_rec.from_work_schedule := NULL;
3687: l_ghr_cpdf_temp_rec.from_pay_rate_determinant := NULL;
3692: WHEN OTHERS THEN
3693: l_message_name := 'get_prior_ws_prd_ds';
3694: --Bug # 9329643 Modified SSN to Emp No
3695: l_log_text := 'Error in fetching prior work schedule,prg,duty station for pa_request_id: '||
3696: l_ghr_pa_requests_rec.pa_request_id ||
3697: ' ; Emp No/employee last name' ||
3698: l_employee_number ||' / '||
3699: l_ghr_pa_Requests_rec.employee_last_name ||
3700: ' ; first NOAC/Second NOAC: '||
3695: l_log_text := 'Error in fetching prior work schedule,prg,duty station for pa_request_id: '||
3696: l_ghr_pa_requests_rec.pa_request_id ||
3697: ' ; Emp No/employee last name' ||
3698: l_employee_number ||' / '||
3699: l_ghr_pa_Requests_rec.employee_last_name ||
3700: ' ; first NOAC/Second NOAC: '||
3701: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3702: l_ghr_pa_requests_rec.second_noa_code ||
3703: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3697: ' ; Emp No/employee last name' ||
3698: l_employee_number ||' / '||
3699: l_ghr_pa_Requests_rec.employee_last_name ||
3700: ' ; first NOAC/Second NOAC: '||
3701: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3702: l_ghr_pa_requests_rec.second_noa_code ||
3703: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3704:
3705: Raise EHRI_DYNRPT_ERROR;
3698: l_employee_number ||' / '||
3699: l_ghr_pa_Requests_rec.employee_last_name ||
3700: ' ; first NOAC/Second NOAC: '||
3701: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3702: l_ghr_pa_requests_rec.second_noa_code ||
3703: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3704:
3705: Raise EHRI_DYNRPT_ERROR;
3706: END;
3724:
3725: END IF;
3726: -- Added IF Condition to fix bug#3231946
3727: IF get_loc_pay_area_code(p_duty_station_code => l_ghr_cpdf_temp_rec.from_duty_station_code,
3728: p_effective_date => l_ghr_pa_requests_rec.effective_date) = '99'
3729: AND l_ghr_pa_requests_rec.from_locality_adj = 0 THEN
3730: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3731: END IF;
3732: END IF;
3725: END IF;
3726: -- Added IF Condition to fix bug#3231946
3727: IF get_loc_pay_area_code(p_duty_station_code => l_ghr_cpdf_temp_rec.from_duty_station_code,
3728: p_effective_date => l_ghr_pa_requests_rec.effective_date) = '99'
3729: AND l_ghr_pa_requests_rec.from_locality_adj = 0 THEN
3730: l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3731: END IF;
3732: END IF;
3733: --
3734: -- 3.7) Get prior ssn if it is being corrected.
3735: --
3736: -- Bug#2789704 Added Exception Handling
3737: BEGIN
3738: get_prev_ssn (l_ghr_pa_requests_rec.altered_pa_request_id
3739: ,l_ghr_pa_requests_rec.employee_national_identifier
3740: ,l_ghr_pa_requests_rec.noa_family_code
3741: ,l_ghr_cpdf_temp_rec.from_national_identifier);
3742: EXCEPTION
3735: --
3736: -- Bug#2789704 Added Exception Handling
3737: BEGIN
3738: get_prev_ssn (l_ghr_pa_requests_rec.altered_pa_request_id
3739: ,l_ghr_pa_requests_rec.employee_national_identifier
3740: ,l_ghr_pa_requests_rec.noa_family_code
3741: ,l_ghr_cpdf_temp_rec.from_national_identifier);
3742: EXCEPTION
3743: WHEN OTHERS THEN
3736: -- Bug#2789704 Added Exception Handling
3737: BEGIN
3738: get_prev_ssn (l_ghr_pa_requests_rec.altered_pa_request_id
3739: ,l_ghr_pa_requests_rec.employee_national_identifier
3740: ,l_ghr_pa_requests_rec.noa_family_code
3741: ,l_ghr_cpdf_temp_rec.from_national_identifier);
3742: EXCEPTION
3743: WHEN OTHERS THEN
3744: l_message_name := 'get_prev_ssn';
3743: WHEN OTHERS THEN
3744: l_message_name := 'get_prev_ssn';
3745: --Bug # 9329643 Modified SSN to Emp No
3746: l_log_text := 'Error in fetching SSN for pa_request_id: '||
3747: l_ghr_pa_requests_rec.pa_request_id ||
3748: ' ; Emp No/employee last name' ||
3749: l_employee_number ||' / '||
3750: l_ghr_pa_Requests_rec.employee_last_name ||
3751: ' ; first NOAC/Second NOAC: '||
3746: l_log_text := 'Error in fetching SSN for pa_request_id: '||
3747: l_ghr_pa_requests_rec.pa_request_id ||
3748: ' ; Emp No/employee last name' ||
3749: l_employee_number ||' / '||
3750: l_ghr_pa_Requests_rec.employee_last_name ||
3751: ' ; first NOAC/Second NOAC: '||
3752: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3753: l_ghr_pa_requests_rec.second_noa_code ||
3754: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3748: ' ; Emp No/employee last name' ||
3749: l_employee_number ||' / '||
3750: l_ghr_pa_Requests_rec.employee_last_name ||
3751: ' ; first NOAC/Second NOAC: '||
3752: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3753: l_ghr_pa_requests_rec.second_noa_code ||
3754: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3755:
3756: Raise EHRI_DYNRPT_ERROR;
3749: l_employee_number ||' / '||
3750: l_ghr_pa_Requests_rec.employee_last_name ||
3751: ' ; first NOAC/Second NOAC: '||
3752: l_ghr_pa_requests_rec.first_noa_code || ' / '||
3753: l_ghr_pa_requests_rec.second_noa_code ||
3754: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3755:
3756: Raise EHRI_DYNRPT_ERROR;
3757: END;
3761:
3762: --
3763: -- EHRI changes
3764: --
3765: IF ((l_ghr_pa_requests_rec.first_noa_code ='002'
3766: AND
3767: (NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '1%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '2%' or
3768: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '3%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '4%' or
3769: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '5%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '6%' or
3763: -- EHRI changes
3764: --
3765: IF ((l_ghr_pa_requests_rec.first_noa_code ='002'
3766: AND
3767: (NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '1%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '2%' or
3768: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '3%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '4%' or
3769: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '5%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '6%' or
3770: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '7%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '8%' )
3771: ) -- for 002
3764: --
3765: IF ((l_ghr_pa_requests_rec.first_noa_code ='002'
3766: AND
3767: (NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '1%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '2%' or
3768: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '3%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '4%' or
3769: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '5%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '6%' or
3770: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '7%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '8%' )
3771: ) -- for 002
3772: OR
3765: IF ((l_ghr_pa_requests_rec.first_noa_code ='002'
3766: AND
3767: (NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '1%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '2%' or
3768: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '3%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '4%' or
3769: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '5%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '6%' or
3770: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '7%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '8%' )
3771: ) -- for 002
3772: OR
3773: (l_ghr_pa_requests_rec.first_noa_code like '1%' or l_ghr_pa_requests_rec.first_noa_code like '2%' or
3766: AND
3767: (NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '1%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '2%' or
3768: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '3%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '4%' or
3769: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '5%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '6%' or
3770: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '7%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '8%' )
3771: ) -- for 002
3772: OR
3773: (l_ghr_pa_requests_rec.first_noa_code like '1%' or l_ghr_pa_requests_rec.first_noa_code like '2%' or
3774: l_ghr_pa_requests_rec.first_noa_code like '3%' or l_ghr_pa_requests_rec.first_noa_code like '4%' or
3769: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '5%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '6%' or
3770: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '7%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '8%' )
3771: ) -- for 002
3772: OR
3773: (l_ghr_pa_requests_rec.first_noa_code like '1%' or l_ghr_pa_requests_rec.first_noa_code like '2%' or
3774: l_ghr_pa_requests_rec.first_noa_code like '3%' or l_ghr_pa_requests_rec.first_noa_code like '4%' or
3775: l_ghr_pa_requests_rec.first_noa_code like '5%' OR l_ghr_pa_requests_rec.first_noa_code like '6%' or
3776: l_ghr_pa_requests_rec.first_noa_code like '7%' OR l_ghr_pa_requests_rec.first_noa_code like '8%'
3777: )
3770: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '7%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '8%' )
3771: ) -- for 002
3772: OR
3773: (l_ghr_pa_requests_rec.first_noa_code like '1%' or l_ghr_pa_requests_rec.first_noa_code like '2%' or
3774: l_ghr_pa_requests_rec.first_noa_code like '3%' or l_ghr_pa_requests_rec.first_noa_code like '4%' or
3775: l_ghr_pa_requests_rec.first_noa_code like '5%' OR l_ghr_pa_requests_rec.first_noa_code like '6%' or
3776: l_ghr_pa_requests_rec.first_noa_code like '7%' OR l_ghr_pa_requests_rec.first_noa_code like '8%'
3777: )
3778: )
3771: ) -- for 002
3772: OR
3773: (l_ghr_pa_requests_rec.first_noa_code like '1%' or l_ghr_pa_requests_rec.first_noa_code like '2%' or
3774: l_ghr_pa_requests_rec.first_noa_code like '3%' or l_ghr_pa_requests_rec.first_noa_code like '4%' or
3775: l_ghr_pa_requests_rec.first_noa_code like '5%' OR l_ghr_pa_requests_rec.first_noa_code like '6%' or
3776: l_ghr_pa_requests_rec.first_noa_code like '7%' OR l_ghr_pa_requests_rec.first_noa_code like '8%'
3777: )
3778: )
3779: THEN
3772: OR
3773: (l_ghr_pa_requests_rec.first_noa_code like '1%' or l_ghr_pa_requests_rec.first_noa_code like '2%' or
3774: l_ghr_pa_requests_rec.first_noa_code like '3%' or l_ghr_pa_requests_rec.first_noa_code like '4%' or
3775: l_ghr_pa_requests_rec.first_noa_code like '5%' OR l_ghr_pa_requests_rec.first_noa_code like '6%' or
3776: l_ghr_pa_requests_rec.first_noa_code like '7%' OR l_ghr_pa_requests_rec.first_noa_code like '8%'
3777: )
3778: )
3779: THEN
3780: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3778: )
3779: THEN
3780: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3781: -- Bug 5010784
3782: -- l_ghr_cpdf_temp_rec.bargaining_unit_status := l_ghr_pa_requests_rec.bargaining_unit_status;
3783: l_ghr_cpdf_temp_rec.bargaining_unit_status := SUBSTR(l_ghr_pa_requests_rec.bargaining_unit_status,
3784: LENGTH(l_ghr_pa_requests_rec.bargaining_unit_status)-3);
3785: -- End Bug 5010784
3786:
3779: THEN
3780: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3781: -- Bug 5010784
3782: -- l_ghr_cpdf_temp_rec.bargaining_unit_status := l_ghr_pa_requests_rec.bargaining_unit_status;
3783: l_ghr_cpdf_temp_rec.bargaining_unit_status := SUBSTR(l_ghr_pa_requests_rec.bargaining_unit_status,
3784: LENGTH(l_ghr_pa_requests_rec.bargaining_unit_status)-3);
3785: -- End Bug 5010784
3786:
3787: -- adding code for 817 NOAC
3780: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3781: -- Bug 5010784
3782: -- l_ghr_cpdf_temp_rec.bargaining_unit_status := l_ghr_pa_requests_rec.bargaining_unit_status;
3783: l_ghr_cpdf_temp_rec.bargaining_unit_status := SUBSTR(l_ghr_pa_requests_rec.bargaining_unit_status,
3784: LENGTH(l_ghr_pa_requests_rec.bargaining_unit_status)-3);
3785: -- End Bug 5010784
3786:
3787: -- adding code for 817 NOAC
3788: IF (l_ghr_pa_requests_rec.first_noa_code='817' or l_ghr_pa_requests_rec.second_noa_code='817') THEN
3784: LENGTH(l_ghr_pa_requests_rec.bargaining_unit_status)-3);
3785: -- End Bug 5010784
3786:
3787: -- adding code for 817 NOAC
3788: IF (l_ghr_pa_requests_rec.first_noa_code='817' or l_ghr_pa_requests_rec.second_noa_code='817') THEN
3789: l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
3790: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3791: l_ghr_cpdf_temp_rec.to_national_identifier
3792: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3786:
3787: -- adding code for 817 NOAC
3788: IF (l_ghr_pa_requests_rec.first_noa_code='817' or l_ghr_pa_requests_rec.second_noa_code='817') THEN
3789: l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
3790: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3791: l_ghr_cpdf_temp_rec.to_national_identifier
3792: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3793: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3794: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3788: IF (l_ghr_pa_requests_rec.first_noa_code='817' or l_ghr_pa_requests_rec.second_noa_code='817') THEN
3789: l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
3790: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3791: l_ghr_cpdf_temp_rec.to_national_identifier
3792: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3793: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3794: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3795: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3796: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3789: l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
3790: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3791: l_ghr_cpdf_temp_rec.to_national_identifier
3792: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3793: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3794: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3795: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3796: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3797: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3790: l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3791: l_ghr_cpdf_temp_rec.to_national_identifier
3792: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3793: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3794: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3795: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3796: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3797: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3798: --Bug # 12847216
3791: l_ghr_cpdf_temp_rec.to_national_identifier
3792: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3793: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3794: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3795: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3796: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3797: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3798: --Bug # 12847216
3799: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth;
3792: := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3793: l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3794: l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3795: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3796: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3797: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3798: --Bug # 12847216
3799: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth;
3800: --End of Bug # 12847216
3795: l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3796: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3797: l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3798: --Bug # 12847216
3799: l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth;
3800: --End of Bug # 12847216
3801: -- GOTO end_par_loop;
3802: END IF;
3803:
3800: --End of Bug # 12847216
3801: -- GOTO end_par_loop;
3802: END IF;
3803:
3804: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3805: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' AND
3806: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3807: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3808: )
3801: -- GOTO end_par_loop;
3802: END IF;
3803:
3804: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3805: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' AND
3806: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3807: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3808: )
3809: ) AND
3802: END IF;
3803:
3804: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3805: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' AND
3806: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3807: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3808: )
3809: ) AND
3810: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3803:
3804: IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3805: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' AND
3806: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3807: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3808: )
3809: ) AND
3810: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3811: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3806: (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3807: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3808: )
3809: ) AND
3810: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3811: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3812: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3813: )
3814: ) THEN
3807: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3808: )
3809: ) AND
3810: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3811: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3812: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3813: )
3814: ) THEN
3815:
3808: )
3809: ) AND
3810: NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3811: (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3812: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3813: )
3814: ) THEN
3815:
3816: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.to_position_title;
3812: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3813: )
3814: ) THEN
3815:
3816: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.to_position_title;
3817: l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3818:
3819: /* FOR pos_org IN cur_pos_org(l_ghr_pa_requests_rec.to_organization_id,
3820: l_ghr_pa_requests_rec.effective_date)
3813: )
3814: ) THEN
3815:
3816: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.to_position_title;
3817: l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3818:
3819: /* FOR pos_org IN cur_pos_org(l_ghr_pa_requests_rec.to_organization_id,
3820: l_ghr_pa_requests_rec.effective_date)
3821: LOOP
3815:
3816: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.to_position_title;
3817: l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3818:
3819: /* FOR pos_org IN cur_pos_org(l_ghr_pa_requests_rec.to_organization_id,
3820: l_ghr_pa_requests_rec.effective_date)
3821: LOOP
3822: l_ghr_cpdf_temp_rec.POSITION_ORG := pos_org.name;
3823: END LOOP;
3816: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.to_position_title;
3817: l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3818:
3819: /* FOR pos_org IN cur_pos_org(l_ghr_pa_requests_rec.to_organization_id,
3820: l_ghr_pa_requests_rec.effective_date)
3821: LOOP
3822: l_ghr_cpdf_temp_rec.POSITION_ORG := pos_org.name;
3823: END LOOP;
3824:
3822: l_ghr_cpdf_temp_rec.POSITION_ORG := pos_org.name;
3823: END LOOP;
3824:
3825: */
3826: l_ghr_cpdf_temp_rec.POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.to_position_org_line1 ||
3827: ' ' || l_ghr_pa_requests_rec.to_position_org_line2 ||
3828: ' ' || l_ghr_pa_requests_rec.to_position_org_line3 ||
3829: ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3830: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3823: END LOOP;
3824:
3825: */
3826: l_ghr_cpdf_temp_rec.POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.to_position_org_line1 ||
3827: ' ' || l_ghr_pa_requests_rec.to_position_org_line2 ||
3828: ' ' || l_ghr_pa_requests_rec.to_position_org_line3 ||
3829: ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3830: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3831: ' ' || l_ghr_pa_requests_rec.to_position_org_line6,1,500);
3824:
3825: */
3826: l_ghr_cpdf_temp_rec.POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.to_position_org_line1 ||
3827: ' ' || l_ghr_pa_requests_rec.to_position_org_line2 ||
3828: ' ' || l_ghr_pa_requests_rec.to_position_org_line3 ||
3829: ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3830: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3831: ' ' || l_ghr_pa_requests_rec.to_position_org_line6,1,500);
3832: ELSE
3825: */
3826: l_ghr_cpdf_temp_rec.POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.to_position_org_line1 ||
3827: ' ' || l_ghr_pa_requests_rec.to_position_org_line2 ||
3828: ' ' || l_ghr_pa_requests_rec.to_position_org_line3 ||
3829: ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3830: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3831: ' ' || l_ghr_pa_requests_rec.to_position_org_line6,1,500);
3832: ELSE
3833: l_ghr_cpdf_temp_rec.position_title := NULL;
3826: l_ghr_cpdf_temp_rec.POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.to_position_org_line1 ||
3827: ' ' || l_ghr_pa_requests_rec.to_position_org_line2 ||
3828: ' ' || l_ghr_pa_requests_rec.to_position_org_line3 ||
3829: ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3830: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3831: ' ' || l_ghr_pa_requests_rec.to_position_org_line6,1,500);
3832: ELSE
3833: l_ghr_cpdf_temp_rec.position_title := NULL;
3834: l_ghr_cpdf_temp_rec.position_number := NULL;
3827: ' ' || l_ghr_pa_requests_rec.to_position_org_line2 ||
3828: ' ' || l_ghr_pa_requests_rec.to_position_org_line3 ||
3829: ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3830: ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3831: ' ' || l_ghr_pa_requests_rec.to_position_org_line6,1,500);
3832: ELSE
3833: l_ghr_cpdf_temp_rec.position_title := NULL;
3834: l_ghr_cpdf_temp_rec.position_number := NULL;
3835: l_ghr_cpdf_temp_rec.POSITION_ORG := NULL;
3842:
3843: -- Bug # 8510442 Added 885 into the list to display award amount for 885 action
3844: -- Bug # 13810071 Added 886,887,889 to the list to display award amount
3845:
3846: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3847: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','818','819','825',
3848: '840','841','842','843','844','845','846','847','848','849','878','879','885','886','887','889')
3849: )
3850: OR
3843: -- Bug # 8510442 Added 885 into the list to display award amount for 885 action
3844: -- Bug # 13810071 Added 886,887,889 to the list to display award amount
3845:
3846: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3847: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','818','819','825',
3848: '840','841','842','843','844','845','846','847','848','849','878','879','885','886','887','889')
3849: )
3850: OR
3851: l_ghr_pa_requests_rec.first_noa_code in ('815','816','817','818','819','825',
3847: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','818','819','825',
3848: '840','841','842','843','844','845','846','847','848','849','878','879','885','886','887','889')
3849: )
3850: OR
3851: l_ghr_pa_requests_rec.first_noa_code in ('815','816','817','818','819','825',
3852: '840','841','842','843','844','845','846','847','848','849','878','879','885','886','887','889')
3853: )
3854: THEN
3855:
3854: THEN
3855:
3856:
3857:
3858: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='M' THEN
3859: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.award_amount;
3860: END IF;
3861: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3862: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3855:
3856:
3857:
3858: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='M' THEN
3859: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.award_amount;
3860: END IF;
3861: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3862: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3863: END IF;
3857:
3858: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='M' THEN
3859: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.award_amount;
3860: END IF;
3861: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3862: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3863: END IF;
3864: IF l_ghr_pa_requests_rec.award_percentage IS NOT NULL THEN
3865: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.award_percentage;
3858: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='M' THEN
3859: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.award_amount;
3860: END IF;
3861: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3862: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3863: END IF;
3864: IF l_ghr_pa_requests_rec.award_percentage IS NOT NULL THEN
3865: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.award_percentage;
3866: END IF;
3860: END IF;
3861: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3862: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3863: END IF;
3864: IF l_ghr_pa_requests_rec.award_percentage IS NOT NULL THEN
3865: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.award_percentage;
3866: END IF;
3867:
3868: /* COMMENTED this code as the similar code is added at line 2545 to resolve the
3861: IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3862: l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3863: END IF;
3864: IF l_ghr_pa_requests_rec.award_percentage IS NOT NULL THEN
3865: l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.award_percentage;
3866: END IF;
3867:
3868: /* COMMENTED this code as the similar code is added at line 2545 to resolve the
3869: issue of non-printing the to total salary.
3870: -- Bug#5328177 Added NOA Codes 815,816 as they also belongs to the same category.
3871: -- Bug#3941541,5168358 Separation Incentive Changes.
3872: -- If the Award Dollars value is NOT NULL, Assume that 825 is processed as Award.
3873: -- Otherwise, it is processed as Incentive.
3874: IF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3875: l_ghr_pa_requests_rec.second_noa_code IN ('815','816', '825')) AND
3876: l_ghr_cpdf_temp_rec.award_dollars IS NULL THEN
3877: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.to_total_salary;
3878: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3871: -- Bug#3941541,5168358 Separation Incentive Changes.
3872: -- If the Award Dollars value is NOT NULL, Assume that 825 is processed as Award.
3873: -- Otherwise, it is processed as Incentive.
3874: IF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3875: l_ghr_pa_requests_rec.second_noa_code IN ('815','816', '825')) AND
3876: l_ghr_cpdf_temp_rec.award_dollars IS NULL THEN
3877: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.to_total_salary;
3878: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3879: END IF;
3873: -- Otherwise, it is processed as Incentive.
3874: IF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3875: l_ghr_pa_requests_rec.second_noa_code IN ('815','816', '825')) AND
3876: l_ghr_cpdf_temp_rec.award_dollars IS NULL THEN
3877: l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.to_total_salary;
3878: l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3879: END IF;
3880: -- End of Bug#3941541,5168358
3881: */
3887: END IF;
3888:
3889: -- New EHRI changes MADHURI
3890: IF (
3891: NOT( (l_ghr_pa_requests_rec.first_noa_code='002' and
3892: l_ghr_pa_requests_rec.second_noa_code LIKE '1%')
3893: OR
3894: l_ghr_pa_requests_rec.first_noa_code LIKE '1%' )
3895: AND
3888:
3889: -- New EHRI changes MADHURI
3890: IF (
3891: NOT( (l_ghr_pa_requests_rec.first_noa_code='002' and
3892: l_ghr_pa_requests_rec.second_noa_code LIKE '1%')
3893: OR
3894: l_ghr_pa_requests_rec.first_noa_code LIKE '1%' )
3895: AND
3896: NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3890: IF (
3891: NOT( (l_ghr_pa_requests_rec.first_noa_code='002' and
3892: l_ghr_pa_requests_rec.second_noa_code LIKE '1%')
3893: OR
3894: l_ghr_pa_requests_rec.first_noa_code LIKE '1%' )
3895: AND
3896: NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3897: l_ghr_pa_requests_rec.second_noa_code LIKE '2%')
3898: OR
3892: l_ghr_pa_requests_rec.second_noa_code LIKE '1%')
3893: OR
3894: l_ghr_pa_requests_rec.first_noa_code LIKE '1%' )
3895: AND
3896: NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3897: l_ghr_pa_requests_rec.second_noa_code LIKE '2%')
3898: OR
3899: l_ghr_pa_requests_rec.first_noa_code LIKE '2%' )
3900: )
3893: OR
3894: l_ghr_pa_requests_rec.first_noa_code LIKE '1%' )
3895: AND
3896: NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3897: l_ghr_pa_requests_rec.second_noa_code LIKE '2%')
3898: OR
3899: l_ghr_pa_requests_rec.first_noa_code LIKE '2%' )
3900: )
3901: THEN
3895: AND
3896: NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3897: l_ghr_pa_requests_rec.second_noa_code LIKE '2%')
3898: OR
3899: l_ghr_pa_requests_rec.first_noa_code LIKE '2%' )
3900: )
3901: THEN
3902:
3903: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3899: l_ghr_pa_requests_rec.first_noa_code LIKE '2%' )
3900: )
3901: THEN
3902:
3903: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3904: l_ghr_pa_requests_rec.second_noa_code = '780')
3905: OR
3906: l_ghr_pa_requests_rec.first_noa_code='780'
3907: ) THEN
3900: )
3901: THEN
3902:
3903: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3904: l_ghr_pa_requests_rec.second_noa_code = '780')
3905: OR
3906: l_ghr_pa_requests_rec.first_noa_code='780'
3907: ) THEN
3908: --
3902:
3903: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3904: l_ghr_pa_requests_rec.second_noa_code = '780')
3905: OR
3906: l_ghr_pa_requests_rec.first_noa_code='780'
3907: ) THEN
3908: --
3909: -- Prior Names ONLY FOR NAME CHANGE
3910:
3908: --
3909: -- Prior Names ONLY FOR NAME CHANGE
3910:
3911:
3912: FOR prior_per_rec IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3913: (l_ghr_pa_requests_rec.effective_date-1) )
3914: LOOP
3915: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
3916: get_suffix_lname(prior_per_rec.last_name,
3909: -- Prior Names ONLY FOR NAME CHANGE
3910:
3911:
3912: FOR prior_per_rec IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3913: (l_ghr_pa_requests_rec.effective_date-1) )
3914: LOOP
3915: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
3916: get_suffix_lname(prior_per_rec.last_name,
3917: l_ghr_pa_requests_rec.effective_date-1,
3913: (l_ghr_pa_requests_rec.effective_date-1) )
3914: LOOP
3915: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
3916: get_suffix_lname(prior_per_rec.last_name,
3917: l_ghr_pa_requests_rec.effective_date-1,
3918: l_suffix,
3919: l_last_name);
3920: l_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME := l_last_name;
3921: l_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME := prior_per_rec.first_name;
3925: END LOOP;
3926:
3927: END IF;
3928:
3929: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3930: l_ghr_pa_requests_rec.second_noa_code = '817')
3931: OR
3932: l_ghr_pa_requests_rec.first_noa_code='817'
3933: ) THEN
3926:
3927: END IF;
3928:
3929: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3930: l_ghr_pa_requests_rec.second_noa_code = '817')
3931: OR
3932: l_ghr_pa_requests_rec.first_noa_code='817'
3933: ) THEN
3934: -- Dont report these items for 817 action
3928:
3929: IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3930: l_ghr_pa_requests_rec.second_noa_code = '817')
3931: OR
3932: l_ghr_pa_requests_rec.first_noa_code='817'
3933: ) THEN
3934: -- Dont report these items for 817 action
3935: --
3936: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := l_ghr_pa_requests_rec.from_position_title;
3932: l_ghr_pa_requests_rec.first_noa_code='817'
3933: ) THEN
3934: -- Dont report these items for 817 action
3935: --
3936: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := l_ghr_pa_requests_rec.from_position_title;
3937: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := l_ghr_pa_requests_rec.from_position_number;
3938: --
3939: /* FOR prior_pos_org IN cur_prior_pos_org(l_ghr_pa_requests_rec.from_position_id,
3940: (l_ghr_pa_requests_rec.effective_date-1) )
3933: ) THEN
3934: -- Dont report these items for 817 action
3935: --
3936: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := l_ghr_pa_requests_rec.from_position_title;
3937: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := l_ghr_pa_requests_rec.from_position_number;
3938: --
3939: /* FOR prior_pos_org IN cur_prior_pos_org(l_ghr_pa_requests_rec.from_position_id,
3940: (l_ghr_pa_requests_rec.effective_date-1) )
3941: LOOP
3935: --
3936: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := l_ghr_pa_requests_rec.from_position_title;
3937: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := l_ghr_pa_requests_rec.from_position_number;
3938: --
3939: /* FOR prior_pos_org IN cur_prior_pos_org(l_ghr_pa_requests_rec.from_position_id,
3940: (l_ghr_pa_requests_rec.effective_date-1) )
3941: LOOP
3942: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := prior_pos_org.name;
3943: END LOOP; */
3936: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := l_ghr_pa_requests_rec.from_position_title;
3937: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := l_ghr_pa_requests_rec.from_position_number;
3938: --
3939: /* FOR prior_pos_org IN cur_prior_pos_org(l_ghr_pa_requests_rec.from_position_id,
3940: (l_ghr_pa_requests_rec.effective_date-1) )
3941: LOOP
3942: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := prior_pos_org.name;
3943: END LOOP; */
3944: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3940: (l_ghr_pa_requests_rec.effective_date-1) )
3941: LOOP
3942: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := prior_pos_org.name;
3943: END LOOP; */
3944: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3945: ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3946: ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3947: ' ' || l_ghr_pa_requests_rec.from_position_org_line4 ||
3948: ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3941: LOOP
3942: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := prior_pos_org.name;
3943: END LOOP; */
3944: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3945: ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3946: ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3947: ' ' || l_ghr_pa_requests_rec.from_position_org_line4 ||
3948: ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3949: ' ' || l_ghr_pa_requests_rec.from_position_org_line6,1,500);
3942: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := prior_pos_org.name;
3943: END LOOP; */
3944: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3945: ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3946: ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3947: ' ' || l_ghr_pa_requests_rec.from_position_org_line4 ||
3948: ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3949: ' ' || l_ghr_pa_requests_rec.from_position_org_line6,1,500);
3950:
3943: END LOOP; */
3944: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3945: ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3946: ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3947: ' ' || l_ghr_pa_requests_rec.from_position_org_line4 ||
3948: ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3949: ' ' || l_ghr_pa_requests_rec.from_position_org_line6,1,500);
3950:
3951: ELSE
3944: l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3945: ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3946: ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3947: ' ' || l_ghr_pa_requests_rec.from_position_org_line4 ||
3948: ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3949: ' ' || l_ghr_pa_requests_rec.from_position_org_line6,1,500);
3950:
3951: ELSE
3952: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := NULL;
3945: ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3946: ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3947: ' ' || l_ghr_pa_requests_rec.from_position_org_line4 ||
3948: ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3949: ' ' || l_ghr_pa_requests_rec.from_position_org_line6,1,500);
3950:
3951: ELSE
3952: l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := NULL;
3953: l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := NULL;
3955: END IF;
3956: --
3957: END IF; --- NOT APPOINTMENT ACTION OR RETURN TO DUTY
3958:
3959: l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3960:
3961: -- NEW EHRI CHANGES MADHURI
3962: --
3963: -- Displaying names for correction of 817 modified the if condition for 002 of 817
3960:
3961: -- NEW EHRI CHANGES MADHURI
3962: --
3963: -- Displaying names for correction of 817 modified the if condition for 002 of 817
3964: IF NOT((l_ghr_pa_requests_rec.first_noa_code='002' AND l_ghr_pa_requests_rec.second_noa_code = '817')
3965: OR
3966: (l_ghr_pa_requests_rec.first_noa_code = '817')) THEN
3967: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3968: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3962: --
3963: -- Displaying names for correction of 817 modified the if condition for 002 of 817
3964: IF NOT((l_ghr_pa_requests_rec.first_noa_code='002' AND l_ghr_pa_requests_rec.second_noa_code = '817')
3965: OR
3966: (l_ghr_pa_requests_rec.first_noa_code = '817')) THEN
3967: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3968: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3969: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3970: -- Added format_name_ehri for EHRI changes.
3963: -- Displaying names for correction of 817 modified the if condition for 002 of 817
3964: IF NOT((l_ghr_pa_requests_rec.first_noa_code='002' AND l_ghr_pa_requests_rec.second_noa_code = '817')
3965: OR
3966: (l_ghr_pa_requests_rec.first_noa_code = '817')) THEN
3967: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3968: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3969: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3970: -- Added format_name_ehri for EHRI changes.
3971: FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3964: IF NOT((l_ghr_pa_requests_rec.first_noa_code='002' AND l_ghr_pa_requests_rec.second_noa_code = '817')
3965: OR
3966: (l_ghr_pa_requests_rec.first_noa_code = '817')) THEN
3967: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3968: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3969: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3970: -- Added format_name_ehri for EHRI changes.
3971: FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3972: l_ghr_pa_requests_rec.effective_date)
3965: OR
3966: (l_ghr_pa_requests_rec.first_noa_code = '817')) THEN
3967: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3968: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3969: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3970: -- Added format_name_ehri for EHRI changes.
3971: FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3972: l_ghr_pa_requests_rec.effective_date)
3973: LOOP
3967: l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3968: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3969: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3970: -- Added format_name_ehri for EHRI changes.
3971: FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3972: l_ghr_pa_requests_rec.effective_date)
3973: LOOP
3974: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
3975: get_suffix_lname(per_det.last_name,
3968: l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3969: l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3970: -- Added format_name_ehri for EHRI changes.
3971: FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3972: l_ghr_pa_requests_rec.effective_date)
3973: LOOP
3974: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
3975: get_suffix_lname(per_det.last_name,
3976: l_ghr_pa_requests_rec.effective_date,
3972: l_ghr_pa_requests_rec.effective_date)
3973: LOOP
3974: -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
3975: get_suffix_lname(per_det.last_name,
3976: l_ghr_pa_requests_rec.effective_date,
3977: l_suffix,
3978: l_last_name);
3979: l_ghr_cpdf_temp_rec.name_title := l_suffix;
3980: -- End Bug# 4648811
3981: END LOOP;
3982:
3983:
3984:
3985: FOR scd_dates IN cur_scd_dates(l_ghr_pa_requests_rec.pa_request_id)
3986: LOOP
3987: l_ghr_cpdf_temp_rec.SCD_rif := fnd_date.canonical_to_date(scd_dates.rif);
3988: l_ghr_cpdf_temp_rec.SCD_retirement := fnd_date.canonical_to_date(scd_dates.ret);
3989: -- Added date conversion for bug#3808473-EHRI reports
3993: and l_ghr_cpdf_temp_rec.SCD_retirement IS NULL) THEN
3994:
3995: BEGIN
3996: ghr_history_fetch.fetch_peopleei
3997: (p_person_id => l_ghr_pa_requests_rec.person_id,
3998: p_information_type => 'GHR_US_PER_SCD_INFORMATION',
3999: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4000: p_per_ei_data => ll_per_ei_data
4001: );
3995: BEGIN
3996: ghr_history_fetch.fetch_peopleei
3997: (p_person_id => l_ghr_pa_requests_rec.person_id,
3998: p_information_type => 'GHR_US_PER_SCD_INFORMATION',
3999: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4000: p_per_ei_data => ll_per_ei_data
4001: );
4002:
4003: l_ghr_cpdf_temp_rec.SCD_rif:= fnd_date.canonical_to_date(ll_per_ei_data.pei_information5);
4007: WHEN OTHERS THEN
4008: l_message_name := 'fetch_peopleei';
4009: --Bug # 9329643 Modified SSN to Emp No
4010: l_log_text := 'Error in fetching SCD Information for pa_request_id: '||
4011: l_ghr_pa_requests_rec.pa_request_id ||
4012: ' ; Emp No/employee last name' ||
4013: l_employee_number ||' / '||
4014: l_ghr_pa_Requests_rec.employee_last_name ||
4015: ' ; first NOAC/Second NOAC: '||
4010: l_log_text := 'Error in fetching SCD Information for pa_request_id: '||
4011: l_ghr_pa_requests_rec.pa_request_id ||
4012: ' ; Emp No/employee last name' ||
4013: l_employee_number ||' / '||
4014: l_ghr_pa_Requests_rec.employee_last_name ||
4015: ' ; first NOAC/Second NOAC: '||
4016: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4017: l_ghr_pa_requests_rec.second_noa_code ||
4018: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4012: ' ; Emp No/employee last name' ||
4013: l_employee_number ||' / '||
4014: l_ghr_pa_Requests_rec.employee_last_name ||
4015: ' ; first NOAC/Second NOAC: '||
4016: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4017: l_ghr_pa_requests_rec.second_noa_code ||
4018: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4019:
4020: Raise EHRI_DYNRPT_ERROR;
4013: l_employee_number ||' / '||
4014: l_ghr_pa_Requests_rec.employee_last_name ||
4015: ' ; first NOAC/Second NOAC: '||
4016: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4017: l_ghr_pa_requests_rec.second_noa_code ||
4018: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4019:
4020: Raise EHRI_DYNRPT_ERROR;
4021: END;
4038: /* In case of 825 instead of taking TO side values From side values are taken as
4039: to side values are not populated.
4040: in future in case to side values are populated consider only the toside values.
4041: */
4042: IF l_ghr_pa_requests_rec.first_noa_code = '825'
4043: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4044: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
4045: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4039: to side values are not populated.
4040: in future in case to side values are populated consider only the toside values.
4041: */
4042: IF l_ghr_pa_requests_rec.first_noa_code = '825'
4043: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4044: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
4045: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4047: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
4040: in future in case to side values are populated consider only the toside values.
4041: */
4042: IF l_ghr_pa_requests_rec.first_noa_code = '825'
4043: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4044: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
4045: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4047: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
4048: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
4041: */
4042: IF l_ghr_pa_requests_rec.first_noa_code = '825'
4043: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4044: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
4045: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4047: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
4048: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
4049: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
4042: IF l_ghr_pa_requests_rec.first_noa_code = '825'
4043: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4044: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
4045: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4047: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
4048: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
4049: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
4050: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
4043: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4044: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
4045: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4047: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
4048: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
4049: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
4050: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
4051: --
4044: l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
4045: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4047: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
4048: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
4049: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
4050: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
4051: --
4052: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
4045: l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4047: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
4048: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
4049: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
4050: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
4051: --
4052: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
4053: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
4046: l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
4047: l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
4048: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
4049: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
4050: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
4051: --
4052: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
4053: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
4054: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
4048: l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
4049: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
4050: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
4051: --
4052: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
4053: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
4054: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
4055:
4056: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
4049: l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
4050: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
4051: --
4052: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
4053: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
4054: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
4055:
4056: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
4057: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
4050: l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
4051: --
4052: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
4053: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
4054: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
4055:
4056: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
4057: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
4058: END IF;
4052: l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
4053: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
4054: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
4055:
4056: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
4057: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
4058: END IF;
4059:
4060: IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
4053: l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
4054: l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
4055:
4056: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
4057: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
4058: END IF;
4059:
4060: IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
4061: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4056: l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
4057: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
4058: END IF;
4059:
4060: IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
4061: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4062: l_ghr_pa_requests_rec.first_noa_code like '3%' ) )
4063: AND
4064: NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
4057: l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
4058: END IF;
4059:
4060: IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
4061: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4062: l_ghr_pa_requests_rec.first_noa_code like '3%' ) )
4063: AND
4064: NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
4065: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4058: END IF;
4059:
4060: IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
4061: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4062: l_ghr_pa_requests_rec.first_noa_code like '3%' ) )
4063: AND
4064: NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
4065: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4066: l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
4060: IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
4061: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4062: l_ghr_pa_requests_rec.first_noa_code like '3%' ) )
4063: AND
4064: NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
4065: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4066: l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
4067: AND
4068: NOT (l_ghr_pa_requests_rec.first_noa_code = '817'
4061: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4062: l_ghr_pa_requests_rec.first_noa_code like '3%' ) )
4063: AND
4064: NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
4065: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4066: l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
4067: AND
4068: NOT (l_ghr_pa_requests_rec.first_noa_code = '817'
4069: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4062: l_ghr_pa_requests_rec.first_noa_code like '3%' ) )
4063: AND
4064: NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
4065: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4066: l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
4067: AND
4068: NOT (l_ghr_pa_requests_rec.first_noa_code = '817'
4069: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4070: l_ghr_pa_requests_rec.first_noa_code = '817' ) )
4064: NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
4065: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4066: l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
4067: AND
4068: NOT (l_ghr_pa_requests_rec.first_noa_code = '817'
4069: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4070: l_ghr_pa_requests_rec.first_noa_code = '817' ) )
4071: THEN
4072: IF l_ghr_cpdf_temp_rec.to_supervisory_differential IS NULL THEN
4065: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4066: l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
4067: AND
4068: NOT (l_ghr_pa_requests_rec.first_noa_code = '817'
4069: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4070: l_ghr_pa_requests_rec.first_noa_code = '817' ) )
4071: THEN
4072: IF l_ghr_cpdf_temp_rec.to_supervisory_differential IS NULL THEN
4073:
4066: l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
4067: AND
4068: NOT (l_ghr_pa_requests_rec.first_noa_code = '817'
4069: OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
4070: l_ghr_pa_requests_rec.first_noa_code = '817' ) )
4071: THEN
4072: IF l_ghr_cpdf_temp_rec.to_supervisory_differential IS NULL THEN
4073:
4074: BEGIN
4073:
4074: BEGIN
4075: ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
4076: ,p_input_value_name => 'Amount'
4077: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4078: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4079: ,p_value => l_ghr_cpdf_temp_rec.to_supervisory_differential
4080: ,p_multiple_error_flag => l_multi_error_flag);
4081: EXCEPTION
4074: BEGIN
4075: ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
4076: ,p_input_value_name => 'Amount'
4077: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4078: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4079: ,p_value => l_ghr_cpdf_temp_rec.to_supervisory_differential
4080: ,p_multiple_error_flag => l_multi_error_flag);
4081: EXCEPTION
4082: WHEN OTHERS THEN
4082: WHEN OTHERS THEN
4083: l_message_name := 'retrieve_element_entry_value';
4084: --Bug # 9329643 Modified SSN to Emp No
4085: l_log_text := 'Error in fetching Supervisory Differential Amount for pa_request_id: '||
4086: l_ghr_pa_requests_rec.pa_request_id ||
4087: ' ; Emp No/employee last name' ||
4088: l_employee_number ||' / '||
4089: l_ghr_pa_Requests_rec.employee_last_name ||
4090: ' ; first NOAC/Second NOAC: '||
4085: l_log_text := 'Error in fetching Supervisory Differential Amount for pa_request_id: '||
4086: l_ghr_pa_requests_rec.pa_request_id ||
4087: ' ; Emp No/employee last name' ||
4088: l_employee_number ||' / '||
4089: l_ghr_pa_Requests_rec.employee_last_name ||
4090: ' ; first NOAC/Second NOAC: '||
4091: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4092: l_ghr_pa_requests_rec.second_noa_code ||
4093: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4087: ' ; Emp No/employee last name' ||
4088: l_employee_number ||' / '||
4089: l_ghr_pa_Requests_rec.employee_last_name ||
4090: ' ; first NOAC/Second NOAC: '||
4091: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4092: l_ghr_pa_requests_rec.second_noa_code ||
4093: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4094: Raise EHRI_DYNRPT_ERROR;
4095: END;
4088: l_employee_number ||' / '||
4089: l_ghr_pa_Requests_rec.employee_last_name ||
4090: ' ; first NOAC/Second NOAC: '||
4091: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4092: l_ghr_pa_requests_rec.second_noa_code ||
4093: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4094: Raise EHRI_DYNRPT_ERROR;
4095: END;
4096:
4099:
4100: BEGIN
4101: ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
4102: ,p_input_value_name => 'Amount'
4103: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4104: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4105: ,p_value => l_ghr_cpdf_temp_rec.to_retention_allowance
4106: ,p_multiple_error_flag => l_multi_error_flag);
4107: EXCEPTION
4100: BEGIN
4101: ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
4102: ,p_input_value_name => 'Amount'
4103: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4104: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4105: ,p_value => l_ghr_cpdf_temp_rec.to_retention_allowance
4106: ,p_multiple_error_flag => l_multi_error_flag);
4107: EXCEPTION
4108: WHEN OTHERS THEN
4108: WHEN OTHERS THEN
4109: l_message_name := 'retrieve_element_entry_value';
4110: --Bug # 9329643 Modified SSN to Emp No
4111: l_log_text := 'Error in fetching Retention Allowance Amount for pa_request_id: '||
4112: l_ghr_pa_requests_rec.pa_request_id ||
4113: ' ; Emp No/employee last name' ||
4114: l_employee_number ||' / '||
4115: l_ghr_pa_Requests_rec.employee_last_name ||
4116: ' ; first NOAC/Second NOAC: '||
4111: l_log_text := 'Error in fetching Retention Allowance Amount for pa_request_id: '||
4112: l_ghr_pa_requests_rec.pa_request_id ||
4113: ' ; Emp No/employee last name' ||
4114: l_employee_number ||' / '||
4115: l_ghr_pa_Requests_rec.employee_last_name ||
4116: ' ; first NOAC/Second NOAC: '||
4117: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4118: l_ghr_pa_requests_rec.second_noa_code ||
4119: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4113: ' ; Emp No/employee last name' ||
4114: l_employee_number ||' / '||
4115: l_ghr_pa_Requests_rec.employee_last_name ||
4116: ' ; first NOAC/Second NOAC: '||
4117: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4118: l_ghr_pa_requests_rec.second_noa_code ||
4119: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4120: Raise EHRI_DYNRPT_ERROR;
4121: END;
4114: l_employee_number ||' / '||
4115: l_ghr_pa_Requests_rec.employee_last_name ||
4116: ' ; first NOAC/Second NOAC: '||
4117: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4118: l_ghr_pa_requests_rec.second_noa_code ||
4119: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4120: Raise EHRI_DYNRPT_ERROR;
4121: END;
4122:
4129: -- including the NOACS 800 and 782 inspite they are optional for reporting
4130: -- as they will be anyways filtered under exclude_noacs
4131: BEGIN
4132:
4133: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
4134: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('702','703','713','721','781','782','790','800',
4135: '850','855','867','868','891','892','893','895','899'))
4136: OR l_ghr_pa_requests_rec.first_noa_code in ('702','703','713','721','781','782','790','800',
4137: '850','855','867','868','891','892','893','895','899')
4130: -- as they will be anyways filtered under exclude_noacs
4131: BEGIN
4132:
4133: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
4134: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('702','703','713','721','781','782','790','800',
4135: '850','855','867','868','891','892','893','895','899'))
4136: OR l_ghr_pa_requests_rec.first_noa_code in ('702','703','713','721','781','782','790','800',
4137: '850','855','867','868','891','892','893','895','899')
4138: ) THEN
4132:
4133: IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
4134: NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('702','703','713','721','781','782','790','800',
4135: '850','855','867','868','891','892','893','895','899'))
4136: OR l_ghr_pa_requests_rec.first_noa_code in ('702','703','713','721','781','782','790','800',
4137: '850','855','867','868','891','892','893','895','899')
4138: ) THEN
4139:
4140: ghr_history_fetch.fetch_peopleei
4137: '850','855','867','868','891','892','893','895','899')
4138: ) THEN
4139:
4140: ghr_history_fetch.fetch_peopleei
4141: (p_person_id => l_ghr_pa_requests_rec.person_id,
4142: p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES',
4143: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4144: p_per_ei_data => ll_per_ei_data
4145: );
4139:
4140: ghr_history_fetch.fetch_peopleei
4141: (p_person_id => l_ghr_pa_requests_rec.person_id,
4142: p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES',
4143: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4144: p_per_ei_data => ll_per_ei_data
4145: );
4146:
4147: l_ghr_cpdf_temp_rec.creditable_military_service:= ll_per_ei_data.pei_information5;
4147: l_ghr_cpdf_temp_rec.creditable_military_service:= ll_per_ei_data.pei_information5;
4148: ll_per_ei_data :=NULL;
4149:
4150: ghr_history_fetch.fetch_peopleei
4151: (p_person_id => l_ghr_pa_requests_rec.person_id,
4152: p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
4153: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4154: p_per_ei_data => ll_per_ei_data
4155: );
4149:
4150: ghr_history_fetch.fetch_peopleei
4151: (p_person_id => l_ghr_pa_requests_rec.person_id,
4152: p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
4153: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4154: p_per_ei_data => ll_per_ei_data
4155: );
4156: l_ghr_cpdf_temp_rec.from_retirement_coverage := ll_per_ei_data.pei_information4;
4157: l_ghr_cpdf_temp_rec.Frozen_service:= ll_per_ei_data.pei_information5;
4177: l_ghr_cpdf_temp_rec.race_national_origin := NULL;
4178: END IF;
4179:
4180: -- Bug 5011003
4181: l_locality_pay_area_code := get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
4182: p_effective_date => l_ghr_pa_requests_rec.effective_date);
4183: l_equiv_plan := get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan));
4184:
4185: IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
4178: END IF;
4179:
4180: -- Bug 5011003
4181: l_locality_pay_area_code := get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
4182: p_effective_date => l_ghr_pa_requests_rec.effective_date);
4183: l_equiv_plan := get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan));
4184:
4185: IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
4186: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4179:
4180: -- Bug 5011003
4181: l_locality_pay_area_code := get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
4182: p_effective_date => l_ghr_pa_requests_rec.effective_date);
4183: l_equiv_plan := get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan));
4184:
4185: IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
4186: l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
4187: ELSE
4221: WHEN OTHERS THEN
4222: l_message_name := 'fetch_peopleei';
4223: --Bug # 9329643 Modified SSN to Emp No
4224: l_log_text := 'Error in fetching SCD Information for pa_request_id: '||
4225: l_ghr_pa_requests_rec.pa_request_id ||
4226: ' ; Emp No/employee last name' ||
4227: l_employee_number ||' / '||
4228: l_ghr_pa_Requests_rec.employee_last_name ||
4229: ' ; first NOAC/Second NOAC: '||
4224: l_log_text := 'Error in fetching SCD Information for pa_request_id: '||
4225: l_ghr_pa_requests_rec.pa_request_id ||
4226: ' ; Emp No/employee last name' ||
4227: l_employee_number ||' / '||
4228: l_ghr_pa_Requests_rec.employee_last_name ||
4229: ' ; first NOAC/Second NOAC: '||
4230: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4231: l_ghr_pa_requests_rec.second_noa_code ||
4232: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4226: ' ; Emp No/employee last name' ||
4227: l_employee_number ||' / '||
4228: l_ghr_pa_Requests_rec.employee_last_name ||
4229: ' ; first NOAC/Second NOAC: '||
4230: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4231: l_ghr_pa_requests_rec.second_noa_code ||
4232: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4233: END;
4234: -- End of changes for CPDF reports picking creditable mil serv, frozen serv and prev retirement coverage
4227: l_employee_number ||' / '||
4228: l_ghr_pa_Requests_rec.employee_last_name ||
4229: ' ; first NOAC/Second NOAC: '||
4230: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4231: l_ghr_pa_requests_rec.second_noa_code ||
4232: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4233: END;
4234: -- End of changes for CPDF reports picking creditable mil serv, frozen serv and prev retirement coverage
4235: -- 3327389 Bug fix end
4232: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4233: END;
4234: -- End of changes for CPDF reports picking creditable mil serv, frozen serv and prev retirement coverage
4235: -- 3327389 Bug fix end
4236: IF l_ghr_pa_requests_rec.first_noa_code <> '001' THEN
4237: -- Bug # 9865785 Removed NA when previous retirement coverage is Null and also
4238: -- not to display Previous retirement coverage for 815-819, 825-827, 840-849 878-879 885-887,889
4239: IF l_ghr_cpdf_temp_rec.from_retirement_coverage IS NOT NULL and
4240: (NVL(l_ghr_pa_requests_rec.first_noa_code, '@#') in
4236: IF l_ghr_pa_requests_rec.first_noa_code <> '001' THEN
4237: -- Bug # 9865785 Removed NA when previous retirement coverage is Null and also
4238: -- not to display Previous retirement coverage for 815-819, 825-827, 840-849 878-879 885-887,889
4239: IF l_ghr_cpdf_temp_rec.from_retirement_coverage IS NOT NULL and
4240: (NVL(l_ghr_pa_requests_rec.first_noa_code, '@#') in
4241: ('815','816','817','818','819','825','826','827','840','841',
4242: '842','843','844','845','846','847','848','849','878','879',
4243: '885','886','887','888','889') OR
4244: NVL(l_ghr_pa_requests_rec.first_noa_code, '@#') = '002' and
4240: (NVL(l_ghr_pa_requests_rec.first_noa_code, '@#') in
4241: ('815','816','817','818','819','825','826','827','840','841',
4242: '842','843','844','845','846','847','848','849','878','879',
4243: '885','886','887','888','889') OR
4244: NVL(l_ghr_pa_requests_rec.first_noa_code, '@#') = '002' and
4245: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') in
4246: ('815','816','817','818','819','825','826','827','840','841',
4247: '842','843','844','845','846','847','848','849','878','879',
4248: '885','886','887','888','889')) THEN
4241: ('815','816','817','818','819','825','826','827','840','841',
4242: '842','843','844','845','846','847','848','849','878','879',
4243: '885','886','887','888','889') OR
4244: NVL(l_ghr_pa_requests_rec.first_noa_code, '@#') = '002' and
4245: NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') in
4246: ('815','816','817','818','819','825','826','827','840','841',
4247: '842','843','844','845','846','847','848','849','878','879',
4248: '885','886','887','888','889')) THEN
4249: l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
4257:
4258: --Bug #6158983 EHRI Status and Dynamic Report Changes
4259: BEGIN
4260:
4261: FOR bus_grp_rec in cur_per_details(l_ghr_pa_requests_rec.person_id,
4262: l_ghr_pa_requests_rec.effective_date)
4263: LOOP
4264: l_business_group_id := bus_grp_rec.business_group_id;
4265: END LOOP;
4258: --Bug #6158983 EHRI Status and Dynamic Report Changes
4259: BEGIN
4260:
4261: FOR bus_grp_rec in cur_per_details(l_ghr_pa_requests_rec.person_id,
4262: l_ghr_pa_requests_rec.effective_date)
4263: LOOP
4264: l_business_group_id := bus_grp_rec.business_group_id;
4265: END LOOP;
4266:
4270: l_message_name := 'Fetch HB Pre Tax';
4271: ghr_per_sum.get_element_details
4272: (p_element_name => 'Health Benefits Pre tax'
4273: ,p_input_value_name => 'Health Plan'
4274: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4275: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4276: ,p_value => l_value
4277: ,p_effective_start_date => l_effective_start_date
4278: ,p_business_group_id => l_business_group_id);
4271: ghr_per_sum.get_element_details
4272: (p_element_name => 'Health Benefits Pre tax'
4273: ,p_input_value_name => 'Health Plan'
4274: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4275: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4276: ,p_value => l_value
4277: ,p_effective_start_date => l_effective_start_date
4278: ,p_business_group_id => l_business_group_id);
4279:
4286: l_effective_start_date := null;
4287: ghr_per_sum.get_element_details
4288: (p_element_name => 'Health Benefits Pre tax'
4289: ,p_input_value_name => 'Enrollment'
4290: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4291: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4292: ,p_value => l_value
4293: ,p_effective_start_date => l_effective_start_date
4294: ,p_business_group_id => l_business_group_id);
4287: ghr_per_sum.get_element_details
4288: (p_element_name => 'Health Benefits Pre tax'
4289: ,p_input_value_name => 'Enrollment'
4290: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4291: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4292: ,p_value => l_value
4293: ,p_effective_start_date => l_effective_start_date
4294: ,p_business_group_id => l_business_group_id);
4295:
4301: l_message_name := 'Fetch HB plan';
4302: ghr_per_sum.get_element_details
4303: (p_element_name => 'Health Benefits'
4304: ,p_input_value_name => 'Health Plan'
4305: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4306: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4307: ,p_value => l_value
4308: ,p_effective_start_date => l_effective_start_date
4309: ,p_business_group_id => l_business_group_id);
4302: ghr_per_sum.get_element_details
4303: (p_element_name => 'Health Benefits'
4304: ,p_input_value_name => 'Health Plan'
4305: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4306: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4307: ,p_value => l_value
4308: ,p_effective_start_date => l_effective_start_date
4309: ,p_business_group_id => l_business_group_id);
4310:
4316: l_effective_start_date := null;
4317: ghr_per_sum.get_element_details
4318: (p_element_name => 'Health Benefits'
4319: ,p_input_value_name => 'Enrollment'
4320: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4321: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4322: ,p_value => l_value
4323: ,p_effective_start_date => l_effective_start_date
4324: ,p_business_group_id => l_business_group_id);
4317: ghr_per_sum.get_element_details
4318: (p_element_name => 'Health Benefits'
4319: ,p_input_value_name => 'Enrollment'
4320: ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4321: ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4322: ,p_value => l_value
4323: ,p_effective_start_date => l_effective_start_date
4324: ,p_business_group_id => l_business_group_id);
4325:
4329: EXCEPTION
4330: WHEN OTHERS THEN
4331: --Bug # 9329643 Modified SSN to Emp No
4332: l_log_text := 'Error in fetching data for Health Benefits for pa_request_id: '||
4333: l_ghr_pa_requests_rec.pa_request_id ||
4334: ' ; Emp No/employee last name' ||
4335: l_employee_number ||' / '||
4336: l_ghr_pa_Requests_rec.employee_last_name ||
4337: ' ; first NOAC/Second NOAC: '||
4332: l_log_text := 'Error in fetching data for Health Benefits for pa_request_id: '||
4333: l_ghr_pa_requests_rec.pa_request_id ||
4334: ' ; Emp No/employee last name' ||
4335: l_employee_number ||' / '||
4336: l_ghr_pa_Requests_rec.employee_last_name ||
4337: ' ; first NOAC/Second NOAC: '||
4338: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4339: l_ghr_pa_requests_rec.second_noa_code ||
4340: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4334: ' ; Emp No/employee last name' ||
4335: l_employee_number ||' / '||
4336: l_ghr_pa_Requests_rec.employee_last_name ||
4337: ' ; first NOAC/Second NOAC: '||
4338: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4339: l_ghr_pa_requests_rec.second_noa_code ||
4340: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4341: Raise EHRI_DYNRPT_ERROR;
4342: END;
4335: l_employee_number ||' / '||
4336: l_ghr_pa_Requests_rec.employee_last_name ||
4337: ' ; first NOAC/Second NOAC: '||
4338: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4339: l_ghr_pa_requests_rec.second_noa_code ||
4340: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4341: Raise EHRI_DYNRPT_ERROR;
4342: END;
4343:
4347: /*BEGIN
4348: ll_per_ei_data:=NULL;
4349: l_message_name := 'Fetch Retirement System Info';
4350: ghr_history_fetch.fetch_peopleei
4351: (p_person_id => l_ghr_pa_requests_rec.person_id,
4352: p_information_type => 'GHR_US_PER_RETIRMENT_SYS_INFO',
4353: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4354: p_per_ei_data => ll_per_ei_data
4355: );
4349: l_message_name := 'Fetch Retirement System Info';
4350: ghr_history_fetch.fetch_peopleei
4351: (p_person_id => l_ghr_pa_requests_rec.person_id,
4352: p_information_type => 'GHR_US_PER_RETIRMENT_SYS_INFO',
4353: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4354: p_per_ei_data => ll_per_ei_data
4355: );
4356:
4357:
4364:
4365: WHEN OTHERS THEN
4366:
4367: l_log_text := 'Error in fetching data for Retirement System Information: '||
4368: l_ghr_pa_requests_rec.pa_request_id ||
4369: ' ; SSN/employee last name' ||
4370: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4371: l_ghr_pa_Requests_rec.employee_last_name ||
4372: ' ; first NOAC/Second NOAC: '||
4366:
4367: l_log_text := 'Error in fetching data for Retirement System Information: '||
4368: l_ghr_pa_requests_rec.pa_request_id ||
4369: ' ; SSN/employee last name' ||
4370: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4371: l_ghr_pa_Requests_rec.employee_last_name ||
4372: ' ; first NOAC/Second NOAC: '||
4373: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4374: l_ghr_pa_requests_rec.second_noa_code ||
4367: l_log_text := 'Error in fetching data for Retirement System Information: '||
4368: l_ghr_pa_requests_rec.pa_request_id ||
4369: ' ; SSN/employee last name' ||
4370: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4371: l_ghr_pa_Requests_rec.employee_last_name ||
4372: ' ; first NOAC/Second NOAC: '||
4373: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4374: l_ghr_pa_requests_rec.second_noa_code ||
4375: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4369: ' ; SSN/employee last name' ||
4370: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4371: l_ghr_pa_Requests_rec.employee_last_name ||
4372: ' ; first NOAC/Second NOAC: '||
4373: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4374: l_ghr_pa_requests_rec.second_noa_code ||
4375: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4376: Raise EHRI_DYNRPT_ERROR;
4377: END;
4370: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4371: l_ghr_pa_Requests_rec.employee_last_name ||
4372: ' ; first NOAC/Second NOAC: '||
4373: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4374: l_ghr_pa_requests_rec.second_noa_code ||
4375: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4376: Raise EHRI_DYNRPT_ERROR;
4377: END;
4378:
4378:
4379: BEGIN
4380: ll_per_ei_data:=NULL;
4381: ghr_history_fetch.fetch_peopleei
4382: (p_person_id => l_ghr_pa_requests_rec.person_id,
4383: p_information_type => 'GHR_US_PER_BENEFIT_INFO',
4384: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4385: p_per_ei_data => ll_per_ei_data
4386: );
4380: ll_per_ei_data:=NULL;
4381: ghr_history_fetch.fetch_peopleei
4382: (p_person_id => l_ghr_pa_requests_rec.person_id,
4383: p_information_type => 'GHR_US_PER_BENEFIT_INFO',
4384: p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4385: p_per_ei_data => ll_per_ei_data
4386: );
4387:
4388: l_ghr_cpdf_temp_rec.FEGLI_ASSG_INDICATOR := ll_per_ei_data.pei_information16;
4395:
4396: WHEN OTHERS THEN
4397:
4398: l_log_text := 'Error in fetching data for Person Benefit Information: '||
4399: l_ghr_pa_requests_rec.pa_request_id ||
4400: ' ; SSN/employee last name' ||
4401: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4402: l_ghr_pa_Requests_rec.employee_last_name ||
4403: ' ; first NOAC/Second NOAC: '||
4397:
4398: l_log_text := 'Error in fetching data for Person Benefit Information: '||
4399: l_ghr_pa_requests_rec.pa_request_id ||
4400: ' ; SSN/employee last name' ||
4401: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4402: l_ghr_pa_Requests_rec.employee_last_name ||
4403: ' ; first NOAC/Second NOAC: '||
4404: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4405: l_ghr_pa_requests_rec.second_noa_code ||
4398: l_log_text := 'Error in fetching data for Person Benefit Information: '||
4399: l_ghr_pa_requests_rec.pa_request_id ||
4400: ' ; SSN/employee last name' ||
4401: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4402: l_ghr_pa_Requests_rec.employee_last_name ||
4403: ' ; first NOAC/Second NOAC: '||
4404: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4405: l_ghr_pa_requests_rec.second_noa_code ||
4406: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4400: ' ; SSN/employee last name' ||
4401: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4402: l_ghr_pa_Requests_rec.employee_last_name ||
4403: ' ; first NOAC/Second NOAC: '||
4404: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4405: l_ghr_pa_requests_rec.second_noa_code ||
4406: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4407: Raise EHRI_DYNRPT_ERROR;
4408: END;*/
4401: l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4402: l_ghr_pa_Requests_rec.employee_last_name ||
4403: ' ; first NOAC/Second NOAC: '||
4404: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4405: l_ghr_pa_requests_rec.second_noa_code ||
4406: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4407: Raise EHRI_DYNRPT_ERROR;
4408: END;*/
4409: --bug #6416590 moving sf50_approval_date to pareq_last_updated_date
4408: END;*/
4409: --bug #6416590 moving sf50_approval_date to pareq_last_updated_date
4410: -- as sf50_approval_date has to be considered for ordering of sequence number
4411: --with in effective date
4412: l_ghr_cpdf_temp_rec.pareq_last_updated_date := l_ghr_pa_requests_rec.sf50_approval_date;
4413: --End of Bug#6158983
4414:
4415: -- Bug # 6850492 added for dual actions need to order on basis of order of processing
4416: -- as two actions will refer to same pa_request_id
4413: --End of Bug#6158983
4414:
4415: -- Bug # 6850492 added for dual actions need to order on basis of order of processing
4416: -- as two actions will refer to same pa_request_id
4417: for rec_ord_of_proc in get_ord_of_proc(p_noa_code => l_ghr_pa_requests_rec.first_noa_code,
4418: p_effective_date => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)))
4419: loop
4420: l_ghr_cpdf_temp_rec.noac_order_of_processing := rec_ord_of_proc.order_of_processing;
4421: end loop;
4414:
4415: -- Bug # 6850492 added for dual actions need to order on basis of order of processing
4416: -- as two actions will refer to same pa_request_id
4417: for rec_ord_of_proc in get_ord_of_proc(p_noa_code => l_ghr_pa_requests_rec.first_noa_code,
4418: p_effective_date => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)))
4419: loop
4420: l_ghr_cpdf_temp_rec.noac_order_of_processing := rec_ord_of_proc.order_of_processing;
4421: end loop;
4422:
4439: hr_utility.set_location('Inside WHEN_OTHERS exception ',40);
4440: l_message_name := 'Unhandled Error';
4441: --Bug # 9329643 Modified SSN to Emp No
4442: l_log_text := 'Unhandled Error for pa_request_id: '||
4443: l_ghr_pa_requests_rec.pa_request_id ||
4444: ' ; Emp No/employee last name' ||
4445: l_employee_number ||' / '||
4446: l_ghr_pa_Requests_rec.employee_last_name ||
4447: ' ; first NOAC/Second NOAC: '||
4442: l_log_text := 'Unhandled Error for pa_request_id: '||
4443: l_ghr_pa_requests_rec.pa_request_id ||
4444: ' ; Emp No/employee last name' ||
4445: l_employee_number ||' / '||
4446: l_ghr_pa_Requests_rec.employee_last_name ||
4447: ' ; first NOAC/Second NOAC: '||
4448: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4449: l_ghr_pa_requests_rec.second_noa_code ||
4450: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4444: ' ; Emp No/employee last name' ||
4445: l_employee_number ||' / '||
4446: l_ghr_pa_Requests_rec.employee_last_name ||
4447: ' ; first NOAC/Second NOAC: '||
4448: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4449: l_ghr_pa_requests_rec.second_noa_code ||
4450: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4451: ghr_mto_int.log_message(p_procedure => l_message_name,
4452: p_message => l_log_text
4445: l_employee_number ||' / '||
4446: l_ghr_pa_Requests_rec.employee_last_name ||
4447: ' ; first NOAC/Second NOAC: '||
4448: l_ghr_pa_requests_rec.first_noa_code || ' / '||
4449: l_ghr_pa_requests_rec.second_noa_code ||
4450: '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4451: ghr_mto_int.log_message(p_procedure => l_message_name,
4452: p_message => l_log_text
4453: );