[Home] [Help]
6: g_package varchar2(32) := 'GHR_MASS_CHANGES.' ;
7:
8: Procedure create_refreshed_sf52_shadow(
9: p_action in varchar2,
10: p_sf52_data in ghr_pa_requests%rowtype);
11:
12: --- End Declaration
13:
14: Procedure create_sf52_for_mass_changes
12: --- End Declaration
13:
14: Procedure create_sf52_for_mass_changes
15: (p_mass_action_type in varchar2,
16: p_pa_request_rec in out NOCOPY ghr_pa_requests%rowtype,
17: p_errbuf out NOCOPY varchar2, --\___ error log
18: p_retcode out NOCOPY number --/ in conc. manager.
19: )
20:
20:
21: is
22:
23: l_proc varchar2(72) := g_package || 'create_sf52_for_mass_changes';
24: l_pa_request_rec ghr_pa_requests%rowtype;
25: l_noa_code ghr_pa_requests.first_noa_code%type;
26: l_scd_leave varchar2(30);
27: l_pa_remark_code1 ghr_remarks.code%type;
28: l_pa_remark_code2 ghr_remarks.code%type;
21: is
22:
23: l_proc varchar2(72) := g_package || 'create_sf52_for_mass_changes';
24: l_pa_request_rec ghr_pa_requests%rowtype;
25: l_noa_code ghr_pa_requests.first_noa_code%type;
26: l_scd_leave varchar2(30);
27: l_pa_remark_code1 ghr_remarks.code%type;
28: l_pa_remark_code2 ghr_remarks.code%type;
29: l_1_prh_object_version_number ghr_pa_requests.object_version_number%type;
25: l_noa_code ghr_pa_requests.first_noa_code%type;
26: l_scd_leave varchar2(30);
27: l_pa_remark_code1 ghr_remarks.code%type;
28: l_pa_remark_code2 ghr_remarks.code%type;
29: l_1_prh_object_version_number ghr_pa_requests.object_version_number%type;
30: l_1_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
31: l_2_prh_object_version_number ghr_pa_requests.object_version_number%type;
32: l_2_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
33: l_multiple_error_flag boolean;
27: l_pa_remark_code1 ghr_remarks.code%type;
28: l_pa_remark_code2 ghr_remarks.code%type;
29: l_1_prh_object_version_number ghr_pa_requests.object_version_number%type;
30: l_1_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
31: l_2_prh_object_version_number ghr_pa_requests.object_version_number%type;
32: l_2_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
33: l_multiple_error_flag boolean;
34: l_dummy varchar2(30);
35: l_remark_id ghr_remarks.remark_id%type;
39: l_message varchar2(2000);
40: l_error varchar2(30);
41: l_log_text varchar2(2000);
42: l_route_flag varchar2(1);
43: l_routing_group_id ghr_pa_requests.routing_group_id%type;
44: l_groupbox_id ghr_groupboxes.groupbox_id%type;
45: l_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
46: l_approving_off_work_title ghr_pa_requests.APPROVING_OFFICIAL_WORK_TITLE%type;
47: l_personnel_office_id ghr_pois.personnel_office_id%type;
42: l_route_flag varchar2(1);
43: l_routing_group_id ghr_pa_requests.routing_group_id%type;
44: l_groupbox_id ghr_groupboxes.groupbox_id%type;
45: l_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
46: l_approving_off_work_title ghr_pa_requests.APPROVING_OFFICIAL_WORK_TITLE%type;
47: l_personnel_office_id ghr_pois.personnel_office_id%type;
48: l_personnel_officer_name per_people_f.full_name%type;
49: l_remark_code_information1 ghr_pa_remarks.remark_code_information1%type;
50: l_remark_code_information2 ghr_pa_remarks.remark_code_information2%type;
51: l_remark_code_information3 ghr_pa_remarks.remark_code_information3%type;
52: l_remark_code_information4 ghr_pa_remarks.remark_code_information4%type;
53: l_remark_code_information5 ghr_pa_remarks.remark_code_information5%type;
54:
55: l_au_overtime ghr_pa_requests.to_au_overtime%TYPE;
56: l_auo_premium_pay_indicator ghr_pa_requests.to_auo_premium_pay_indicator%TYPE;
57: l_availability_pay ghr_pa_requests.to_availability_pay%TYPE;
58: l_ap_premium_pay_indicator ghr_pa_requests.to_ap_premium_pay_indicator%TYPE;
59: l_retention_allowance ghr_pa_requests.to_retention_allowance%TYPE;
52: l_remark_code_information4 ghr_pa_remarks.remark_code_information4%type;
53: l_remark_code_information5 ghr_pa_remarks.remark_code_information5%type;
54:
55: l_au_overtime ghr_pa_requests.to_au_overtime%TYPE;
56: l_auo_premium_pay_indicator ghr_pa_requests.to_auo_premium_pay_indicator%TYPE;
57: l_availability_pay ghr_pa_requests.to_availability_pay%TYPE;
58: l_ap_premium_pay_indicator ghr_pa_requests.to_ap_premium_pay_indicator%TYPE;
59: l_retention_allowance ghr_pa_requests.to_retention_allowance%TYPE;
60: l_supervisory_differential ghr_pa_requests.to_supervisory_differential%TYPE;
53: l_remark_code_information5 ghr_pa_remarks.remark_code_information5%type;
54:
55: l_au_overtime ghr_pa_requests.to_au_overtime%TYPE;
56: l_auo_premium_pay_indicator ghr_pa_requests.to_auo_premium_pay_indicator%TYPE;
57: l_availability_pay ghr_pa_requests.to_availability_pay%TYPE;
58: l_ap_premium_pay_indicator ghr_pa_requests.to_ap_premium_pay_indicator%TYPE;
59: l_retention_allowance ghr_pa_requests.to_retention_allowance%TYPE;
60: l_supervisory_differential ghr_pa_requests.to_supervisory_differential%TYPE;
61: l_staffing_differential ghr_pa_requests.to_staffing_differential%TYPE;
54:
55: l_au_overtime ghr_pa_requests.to_au_overtime%TYPE;
56: l_auo_premium_pay_indicator ghr_pa_requests.to_auo_premium_pay_indicator%TYPE;
57: l_availability_pay ghr_pa_requests.to_availability_pay%TYPE;
58: l_ap_premium_pay_indicator ghr_pa_requests.to_ap_premium_pay_indicator%TYPE;
59: l_retention_allowance ghr_pa_requests.to_retention_allowance%TYPE;
60: l_supervisory_differential ghr_pa_requests.to_supervisory_differential%TYPE;
61: l_staffing_differential ghr_pa_requests.to_staffing_differential%TYPE;
62: l_duty_station_location_id hr_locations.location_id%TYPE;
55: l_au_overtime ghr_pa_requests.to_au_overtime%TYPE;
56: l_auo_premium_pay_indicator ghr_pa_requests.to_auo_premium_pay_indicator%TYPE;
57: l_availability_pay ghr_pa_requests.to_availability_pay%TYPE;
58: l_ap_premium_pay_indicator ghr_pa_requests.to_ap_premium_pay_indicator%TYPE;
59: l_retention_allowance ghr_pa_requests.to_retention_allowance%TYPE;
60: l_supervisory_differential ghr_pa_requests.to_supervisory_differential%TYPE;
61: l_staffing_differential ghr_pa_requests.to_staffing_differential%TYPE;
62: l_duty_station_location_id hr_locations.location_id%TYPE;
63: l_duty_station_id ghr_duty_stations_v.duty_station_id%TYPE;
56: l_auo_premium_pay_indicator ghr_pa_requests.to_auo_premium_pay_indicator%TYPE;
57: l_availability_pay ghr_pa_requests.to_availability_pay%TYPE;
58: l_ap_premium_pay_indicator ghr_pa_requests.to_ap_premium_pay_indicator%TYPE;
59: l_retention_allowance ghr_pa_requests.to_retention_allowance%TYPE;
60: l_supervisory_differential ghr_pa_requests.to_supervisory_differential%TYPE;
61: l_staffing_differential ghr_pa_requests.to_staffing_differential%TYPE;
62: l_duty_station_location_id hr_locations.location_id%TYPE;
63: l_duty_station_id ghr_duty_stations_v.duty_station_id%TYPE;
64: l_duty_station_code ghr_duty_stations_v.duty_station_code%TYPE;
57: l_availability_pay ghr_pa_requests.to_availability_pay%TYPE;
58: l_ap_premium_pay_indicator ghr_pa_requests.to_ap_premium_pay_indicator%TYPE;
59: l_retention_allowance ghr_pa_requests.to_retention_allowance%TYPE;
60: l_supervisory_differential ghr_pa_requests.to_supervisory_differential%TYPE;
61: l_staffing_differential ghr_pa_requests.to_staffing_differential%TYPE;
62: l_duty_station_location_id hr_locations.location_id%TYPE;
63: l_duty_station_id ghr_duty_stations_v.duty_station_id%TYPE;
64: l_duty_station_code ghr_duty_stations_v.duty_station_code%TYPE;
65: l_duty_station_desc ghr_duty_stations_v.duty_station_desc%TYPE;
67: l_appropriation_code2 varchar2(30);
68: l_position_id hr_positions_f.position_id%TYPE;
69: l_noa_family_code ghr_families.noa_family_code%type;
70: -- Bug#4256022 Declared the variable l_desc_out
71: l_desc_out ghr_pa_requests.first_noa_desc%TYPE;
72:
73: l_pa_request_num_prefix varchar2(10);
74: groupbox_err exception;
75:
79: l_new_supervisory_differential number; ---AVR
80:
81: l_dummy_char VARCHAR2(150); --Bug 2013340 --AVR
82: l_full_name VARCHAR2(380); -- Bug 3718167 --VN
83: l_ssn ghr_pa_requests.employee_national_identifier%TYPE; -- Bug 3718167 --VN
84:
85: ---SES Changes Start
86: l_user_table_id pay_user_tables.user_table_id%type;
87:
88: -- Bug#5089732
89: l_to_grade_id NUMBER;
90: -- Bug#5089732
91: --Begin Bug# 14055214
92: l_target_occ_code ghr_pa_requests.to_occ_code%type;
93: l_target_job_id ghr_pa_requests.to_job_id%type;
94: l_target_flsa_category ghr_pa_requests.flsa_category%type;
95: l_target_barg_unit_status ghr_pa_requests.bargaining_unit_status%type;
96: l_target_agency_code_subelmt ghr_pa_requests.agency_code%type;
89: l_to_grade_id NUMBER;
90: -- Bug#5089732
91: --Begin Bug# 14055214
92: l_target_occ_code ghr_pa_requests.to_occ_code%type;
93: l_target_job_id ghr_pa_requests.to_job_id%type;
94: l_target_flsa_category ghr_pa_requests.flsa_category%type;
95: l_target_barg_unit_status ghr_pa_requests.bargaining_unit_status%type;
96: l_target_agency_code_subelmt ghr_pa_requests.agency_code%type;
97: l_target_duty_station_id ghr_mass_salaries.target_duty_station_id%type;
90: -- Bug#5089732
91: --Begin Bug# 14055214
92: l_target_occ_code ghr_pa_requests.to_occ_code%type;
93: l_target_job_id ghr_pa_requests.to_job_id%type;
94: l_target_flsa_category ghr_pa_requests.flsa_category%type;
95: l_target_barg_unit_status ghr_pa_requests.bargaining_unit_status%type;
96: l_target_agency_code_subelmt ghr_pa_requests.agency_code%type;
97: l_target_duty_station_id ghr_mass_salaries.target_duty_station_id%type;
98: l_employee_dept_or_agency ghr_pa_requests.employee_dept_or_agency%type;
91: --Begin Bug# 14055214
92: l_target_occ_code ghr_pa_requests.to_occ_code%type;
93: l_target_job_id ghr_pa_requests.to_job_id%type;
94: l_target_flsa_category ghr_pa_requests.flsa_category%type;
95: l_target_barg_unit_status ghr_pa_requests.bargaining_unit_status%type;
96: l_target_agency_code_subelmt ghr_pa_requests.agency_code%type;
97: l_target_duty_station_id ghr_mass_salaries.target_duty_station_id%type;
98: l_employee_dept_or_agency ghr_pa_requests.employee_dept_or_agency%type;
99: l_target_functional_class ghr_mass_salaries.target_functional_class%type;--Bug# 14410401
92: l_target_occ_code ghr_pa_requests.to_occ_code%type;
93: l_target_job_id ghr_pa_requests.to_job_id%type;
94: l_target_flsa_category ghr_pa_requests.flsa_category%type;
95: l_target_barg_unit_status ghr_pa_requests.bargaining_unit_status%type;
96: l_target_agency_code_subelmt ghr_pa_requests.agency_code%type;
97: l_target_duty_station_id ghr_mass_salaries.target_duty_station_id%type;
98: l_employee_dept_or_agency ghr_pa_requests.employee_dept_or_agency%type;
99: l_target_functional_class ghr_mass_salaries.target_functional_class%type;--Bug# 14410401
100: --End Bug# 14055214
94: l_target_flsa_category ghr_pa_requests.flsa_category%type;
95: l_target_barg_unit_status ghr_pa_requests.bargaining_unit_status%type;
96: l_target_agency_code_subelmt ghr_pa_requests.agency_code%type;
97: l_target_duty_station_id ghr_mass_salaries.target_duty_station_id%type;
98: l_employee_dept_or_agency ghr_pa_requests.employee_dept_or_agency%type;
99: l_target_functional_class ghr_mass_salaries.target_functional_class%type;--Bug# 14410401
100: --End Bug# 14055214
101: cursor c_pay_tab_essl is
102: select 1 from pay_user_tables
177: l_full_name := l_pa_request_rec.employee_last_name||','|| l_pa_request_rec.employee_first_name||' '|| l_pa_request_rec.employee_middle_names;
178: l_ssn := l_pa_request_rec.employee_national_identifier;
179:
180: --Bug # 9329643
181: l_employee_number := ghr_pa_requests_pkg2.get_employee_number
182: (p_person_id => l_pa_request_rec.person_id,
183: p_effective_date => l_pa_request_rec.effective_date);
184: -- These will be the ones that will be common to all the mass actions.
185: --
499: l_pa_request_rec.to_locality_adj := l_pa_request_rec.from_locality_adj;
500: l_pa_request_rec.to_total_salary := l_pa_request_rec.from_total_salary;
501: ELSIF l_pa_request_rec.first_noa_code IN ('792') THEN
502: IF l_pa_request_rec.duty_station_location_id <> l_duty_station_location_id THEN
503: ghr_pa_requests_pkg.get_SF52_loc_ddf_details
504: (p_location_id => l_duty_station_location_id
505: ,p_duty_station_id => l_target_duty_station_id);
506: IF (ghr_pay_calc.get_lpa_percentage (l_pa_request_rec.duty_station_id, l_pa_request_rec.effective_date)
507: <> ghr_pay_calc.get_lpa_percentage (l_target_duty_station_id, l_pa_request_rec.effective_date)) THEN
517: open get_sal_chg_fam(p_effective_date => l_pa_request_rec.effective_date);
518: fetch get_sal_chg_fam into l_pa_request_rec.noa_Family_Code;
519: close get_sal_chg_fam;
520: l_pa_request_rec.first_action_la_code1 := 'VGR';
521: l_pa_request_rec.first_action_la_desc1 := ghr_pa_requests_pkg.get_lookup_description
522: (800,'GHR_US_LEGAL_AUTHORITY',l_pa_request_rec.first_action_la_code1);
523:
524: END IF;
525: END IF;
553:
554: -- # Bug 711533 : Added the if condition
555: IF p_mass_action_type <> 'MASS_TRANSFER_IN' THEN
556: hr_utility.set_location(l_proc,65);
557: ghr_pa_requests_pkg.get_SF52_person_ddf_details
558: (p_person_id => l_pa_request_rec.person_id,
559: p_date_effective => l_pa_request_rec.effective_date,
560: p_citizenship => l_pa_request_rec.citizenship,
561: p_veterans_preference => l_pa_request_rec.veterans_preference,
589: l_position_id := l_pa_request_rec.from_position_id;
590: END IF;
591: IF p_mass_action_type <> 'MASS_TRANSFER_IN' THEN
592:
593: ghr_pa_requests_pkg.get_sf52_pos_ddf_details
594: (p_position_id => l_position_id
595: ,p_date_Effective => l_pa_request_rec.effective_date
596: ,p_flsa_category => l_pa_request_rec.flsa_category
597: ,p_bargaining_unit_status => l_pa_request_rec.bargaining_unit_status
609: END IF;
610:
611: IF p_mass_action_type = 'MASS_TRANSFER_IN' THEN
612:
613: ghr_pa_requests_pkg.get_sf52_pos_ddf_details
614: (p_position_id => l_position_id
615: ,p_date_Effective => l_pa_request_rec.effective_date
616: ,p_flsa_category => l_dummy
617: ,p_bargaining_unit_status => l_dummy
660: );
661:
662: END IF; -- end for the condn. not = mass transfer in for retrieving elements.
663:
664: l_pa_request_rec.fegli_desc := ghr_pa_requests_pkg.get_lookup_meaning
665: (800
666: ,'GHR_US_FEGLI'
667: ,l_pa_request_rec.fegli
668: );
667: ,l_pa_request_rec.fegli
668: );
669:
670:
671: l_pa_request_rec.retirement_plan_desc := ghr_pa_requests_pkg.get_lookup_meaning
672: (800
673: ,'GHR_US_RETIREMENT_PLAN'
674: ,l_pa_request_rec.retirement_plan
675: );
707:
708: -- Descriptions for the codes passed in
709:
710: -- Annuitant_indicator
711: l_pa_request_rec.annuitant_indicator_desc := ghr_pa_requests_pkg.get_lookup_meaning
712: (800
713: ,'GHR_US_ANNUITANT_INDICATOR'
714: ,l_pa_request_rec.annuitant_indicator
715: );
714: ,l_pa_request_rec.annuitant_indicator
715: );
716:
717: --WORK_SCHEDULE
718: l_pa_request_rec.work_schedule_desc := ghr_pa_requests_pkg.get_lookup_meaning
719: (800
720: ,'GHR_US_WORK_SCHEDULE'
721: ,l_pa_request_rec.work_schedule
722: );
728:
729:
730: --Bug #1295328 initialize
731: IF p_mass_action_type = 'MASS_TRANSFER_OUT' THEN
732: ghr_pa_requests_pkg.get_address_details
733: (p_person_id => l_pa_request_rec.person_id
734: ,p_effective_date => l_pa_request_rec.effective_date
735: ,p_address_line1 => l_pa_request_rec.forwarding_address_line1
736: ,p_address_line2 => l_pa_request_rec.forwarding_address_line2
943: -- Having got the l_1_pa_routing_history_id and l_1_prh_object_version_number
944: -- from the above procedure, use it in the ghr_prh_upd.upd to update the groupbox_id
945: -- ( use ghr_mass_actions_pkg.get_personnel_off_groupbox to get the groupbox_id and
946: -- the routing_group_id)
947: -- Also update the ghr_pa_requests with the routing_group_id passed out
948: -- ghr_par_upd.upd
949: -- If the groupbox_id is null, then rollback to create_52_for_mass_changes and then nake an
950: -- entry in the ghr_process_log -- (not sure about this part)
951:
994: -- and concatenate it with the request id.
995:
996: -- Note : I guess it is OK in case of mass actions
997: -- to write to the shadow after it has written
998: -- to the ghr_pa_requests table . If not we would have to
999: -- write to the shadow first
1000: -- and then pass the pa_request_id into the create_sf52 procedure call
1001:
1002: l_log_text := 'Error while creating PA Request Shadow row ';
1004: IF p_mass_action_type IN ('MASS_REALIGNMENT','MASS_NOA_SEL') THEN
1005: -- We need to put into the shadow table, the values that we are changing through
1006: -- the 52 which are AP/UE so that, UPDATE HR will not refresh the values.
1007:
1008: ghr_pa_requests_pkg.get_SF52_loc_ddf_details
1009: (p_location_id => l_duty_station_location_id
1010: ,p_duty_station_id => l_pa_request_rec.duty_station_id);
1011:
1012: ghr_pa_requests_pkg.get_duty_station_details
1008: ghr_pa_requests_pkg.get_SF52_loc_ddf_details
1009: (p_location_id => l_duty_station_location_id
1010: ,p_duty_station_id => l_pa_request_rec.duty_station_id);
1011:
1012: ghr_pa_requests_pkg.get_duty_station_details
1013: (p_duty_station_id => l_pa_request_rec.duty_station_id
1014: ,p_effective_date => l_pa_request_rec.effective_date
1015: ,p_duty_station_code => l_pa_request_rec.duty_station_code
1016: ,p_duty_station_desc => l_pa_request_rec.duty_station_desc);
1031: -- );
1032: --dbms_output.put_line('after creating shadow row');
1033:
1034:
1035: -- Update ghr_pa_requests with request_number and
1036: -- approval_date / approving_off._work_title
1037: -- which will then cause this SF52 to be
1038: -- picked by the concurrent program that handles the FUTURE Action.
1039:
1202: end create_sf52_for_mass_changes;
1203:
1204:
1205: Procedure create_remarks
1206: (p_pa_request_rec in ghr_pa_requests%rowtype,
1207: p_remark_code in ghr_remarks.code%type
1208: )
1209: is
1210:
1282: End create_remarks;
1283:
1284: Procedure create_refreshed_sf52_shadow(
1285: p_action in varchar2,
1286: p_sf52_data in ghr_pa_requests%rowtype) is
1287:
1288: l_sf52_data ghr_pa_requests%rowtype;
1289: l_service_comp_date varchar2(20);
1290: l_dummy varchar2(200);
1284: Procedure create_refreshed_sf52_shadow(
1285: p_action in varchar2,
1286: p_sf52_data in ghr_pa_requests%rowtype) is
1287:
1288: l_sf52_data ghr_pa_requests%rowtype;
1289: l_service_comp_date varchar2(20);
1290: l_dummy varchar2(200);
1291:
1292: Begin
1293:
1294: l_sf52_data := p_sf52_data;
1295:
1296: if nvl(p_action, 'xXxX#$%') = 'MASS_TRANSFER_IN' then
1297: ghr_pa_requests_pkg.get_SF52_person_ddf_details
1298: (p_person_id => l_sf52_data.person_id
1299: ,p_date_effective => l_sf52_data.effective_date
1300: ,p_citizenship => l_sf52_data.citizenship
1301: ,p_veterans_preference => l_sf52_data.veterans_preference
1320: -- l_sf52_data.education_level := l_education_level;
1321: -- l_sf52_data.academic_discipline := l_academic_discipline;
1322: -- l_sf52_data.year_degree_attained := l_year_degree_attained;
1323:
1324: ghr_pa_requests_pkg.get_SF52_pos_ddf_details
1325: (p_position_id => l_sf52_data.to_position_id
1326: ,p_date_effective => l_sf52_data.effective_date
1327: ,p_flsa_category => l_sf52_data.flsa_category
1328: ,p_bargaining_unit_status => l_sf52_data.bargaining_unit_status