[Home] [Help]
PACKAGE BODY: APPS.GHR_SF52_API
Source
1 PACKAGE BODY ghr_sf52_api AS
2 /* $Header: ghparapi.pkb 120.33.12020000.3 2012/07/19 08:48:54 vmididho ship $ */
3 --
4 -- Package Variables
5 g_package varchar2(33) := ' ghr_sf52_api.';
6 --
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< create_sf52>--------------------------|
10 -- ----------------------------------------------------------------------------
11 procedure create_sf52
12 (p_validate in boolean default false,
13 p_noa_family_code in varchar2,
14 p_pa_request_id in out nocopy number,
15 p_routing_group_id in number default null,
16 p_proposed_effective_asap_flag in varchar2 default 'N',
17 p_academic_discipline in varchar2 default null,
18 p_additional_info_person_id in number default null,
19 p_additional_info_tel_number in varchar2 default null,
20 p_altered_pa_request_id in number default null,
21 p_annuitant_indicator in varchar2 default null,
22 p_annuitant_indicator_desc in varchar2 default null,
23 p_appropriation_code1 in varchar2 default null,
24 p_appropriation_code2 in varchar2 default null,
25 p_approval_date in date default null,
26 p_approving_official_full_name in varchar2 default null,
27 p_approving_official_work_titl in varchar2 default null,
28 p_authorized_by_person_id in number default null,
29 p_authorized_by_title in varchar2 default null,
30 p_award_amount in number default null,
31 p_award_uom in varchar2 default null,
32 p_bargaining_unit_status in varchar2 default null,
33 p_citizenship in varchar2 default null,
34 p_concurrence_date in date default null,
35 p_custom_pay_calc_flag in varchar2 default null,
36 p_duty_station_code in varchar2 default null,
37 p_duty_station_desc in varchar2 default null,
38 p_duty_station_id in number default null,
39 p_duty_station_location_id in number default null,
40 p_education_level in varchar2 default null,
41 p_effective_date in date default null,
42 p_employee_assignment_id in number default null,
43 p_employee_date_of_birth in date default null,
44 p_employee_first_name in varchar2 default null,
45 p_employee_last_name in varchar2 default null,
46 p_employee_middle_names in varchar2 default null,
47 p_employee_national_identifier in varchar2 default null,
48 p_fegli in varchar2 default null,
49 p_fegli_desc in varchar2 default null,
50 p_first_action_la_code1 in varchar2 default null,
51 p_first_action_la_code2 in varchar2 default null,
52 p_first_action_la_desc1 in varchar2 default null,
53 p_first_action_la_desc2 in varchar2 default null,
54 p_first_noa_cancel_or_correct in varchar2 default null,
55 p_first_noa_code in varchar2 default null,
56 p_first_noa_desc in varchar2 default null,
57 p_first_noa_id in number default null,
58 p_first_noa_pa_request_id in number default null,
59 p_flsa_category in varchar2 default null,
60 p_forwarding_address_line1 in varchar2 default null,
61 p_forwarding_address_line2 in varchar2 default null,
62 p_forwarding_address_line3 in varchar2 default null,
63 p_forwarding_country in varchar2 default null,
64 p_forwarding_country_short_nam in varchar2 default null,
65 p_forwarding_postal_code in varchar2 default null,
66 p_forwarding_region_2 in varchar2 default null,
67 p_forwarding_town_or_city in varchar2 default null,
68 p_from_adj_basic_pay in number default null,
69 p_from_basic_pay in number default null,
70 p_from_grade_or_level in varchar2 default null,
71 p_from_locality_adj in number default null,
72 p_from_occ_code in varchar2 default null,
73 p_from_other_pay_amount in number default null,
74 p_from_pay_basis in varchar2 default null,
75 p_from_pay_plan in varchar2 default null,
76 -- FWFA Changes Bug#4444609
77 p_input_pay_rate_determinant in varchar2 default null,
78 p_from_pay_table_identifier in number default null,
79 -- FWFA Changes
80 p_from_position_id in number default null,
81 p_from_position_org_line1 in varchar2 default null,
82 p_from_position_org_line2 in varchar2 default null,
83 p_from_position_org_line3 in varchar2 default null,
84 p_from_position_org_line4 in varchar2 default null,
85 p_from_position_org_line5 in varchar2 default null,
86 p_from_position_org_line6 in varchar2 default null,
87 p_from_position_number in varchar2 default null,
88 p_from_position_seq_no in number default null,
89 p_from_position_title in varchar2 default null,
90 p_from_step_or_rate in varchar2 default null,
91 p_from_total_salary in number default null,
92 p_functional_class in varchar2 default null,
93 p_notepad in varchar2 default null,
94 p_part_time_hours in number default null,
95 p_pay_rate_determinant in varchar2 default null,
96 p_person_id in number default null,
97 p_position_occupied in varchar2 default null,
98 p_proposed_effective_date in date default null,
99 p_requested_by_person_id in number default null,
100 p_requested_by_title in varchar2 default null,
101 p_requested_date in date default null,
102 p_requesting_office_remarks_de in varchar2 default null,
103 p_requesting_office_remarks_fl in varchar2 default null,
104 p_request_number in varchar2 default null,
105 p_resign_and_retire_reason_des in varchar2 default null,
106 p_retirement_plan in varchar2 default null,
107 p_retirement_plan_desc in varchar2 default null,
108 p_second_action_la_code1 in varchar2 default null,
109 p_second_action_la_code2 in varchar2 default null,
110 p_second_action_la_desc1 in varchar2 default null,
111 p_second_action_la_desc2 in varchar2 default null,
112 p_second_noa_cancel_or_correct in varchar2 default null,
113 p_second_noa_code in varchar2 default null,
114 p_second_noa_desc in varchar2 default null,
115 p_second_noa_id in number default null,
116 p_second_noa_pa_request_id in number default null,
117 p_service_comp_date in date default null,
118 p_supervisory_status in varchar2 default null,
119 p_tenure in varchar2 default null,
120 p_to_adj_basic_pay in number default null,
121 p_to_basic_pay in number default null,
122 p_to_grade_id in number default null,
123 p_to_grade_or_level in varchar2 default null,
124 p_to_job_id in number default null,
125 p_to_locality_adj in number default null,
126 p_to_occ_code in varchar2 default null,
127 p_to_organization_id in number default null,
128 p_to_other_pay_amount in number default null,
129 p_to_au_overtime in number default null,
130 p_to_auo_premium_pay_indicator in varchar2 default null,
131 p_to_availability_pay in number default null,
132 p_to_ap_premium_pay_indicator in varchar2 default null,
133 p_to_retention_allowance in number default null,
134 p_to_supervisory_differential in number default null,
135 p_to_staffing_differential in number default null,
136 p_to_pay_basis in varchar2 default null,
137 p_to_pay_plan in varchar2 default null,
138 -- FWFA Changes Bug#4444609
139 p_to_pay_table_identifier in number default null,
140 -- FWFA Changes
141 p_to_position_id in number default null,
142 p_to_position_org_line1 in varchar2 default null,
143 p_to_position_org_line2 in varchar2 default null,
144 p_to_position_org_line3 in varchar2 default null,
145 p_to_position_org_line4 in varchar2 default null,
146 p_to_position_org_line5 in varchar2 default null,
147 p_to_position_org_line6 in varchar2 default null,
148 p_to_position_number in varchar2 default null,
149 p_to_position_seq_no in number default null,
150 p_to_position_title in varchar2 default null,
151 p_to_step_or_rate in varchar2 default null,
152 p_to_total_salary in number default null,
153 p_veterans_preference in varchar2 default null,
154 p_veterans_pref_for_rif in varchar2 default null,
155 p_veterans_status in varchar2 default null,
156 p_work_schedule in varchar2 default null,
157 p_work_schedule_desc in varchar2 default null,
158 p_year_degree_attained in number default null,
159 p_first_noa_information1 in varchar2 default null,
160 p_first_noa_information2 in varchar2 default null,
161 p_first_noa_information3 in varchar2 default null,
162 p_first_noa_information4 in varchar2 default null,
163 p_first_noa_information5 in varchar2 default null,
164 p_second_lac1_information1 in varchar2 default null,
165 p_second_lac1_information2 in varchar2 default null,
166 p_second_lac1_information3 in varchar2 default null,
167 p_second_lac1_information4 in varchar2 default null,
168 p_second_lac1_information5 in varchar2 default null,
169 p_second_lac2_information1 in varchar2 default null,
170 p_second_lac2_information2 in varchar2 default null,
171 p_second_lac2_information3 in varchar2 default null,
172 p_second_lac2_information4 in varchar2 default null,
173 p_second_lac2_information5 in varchar2 default null,
174 p_second_noa_information1 in varchar2 default null,
175 p_second_noa_information2 in varchar2 default null,
176 p_second_noa_information3 in varchar2 default null,
177 p_second_noa_information4 in varchar2 default null,
178 p_second_noa_information5 in varchar2 default null,
179 p_first_lac1_information1 in varchar2 default null,
180 p_first_lac1_information2 in varchar2 default null,
181 p_first_lac1_information3 in varchar2 default null,
182 p_first_lac1_information4 in varchar2 default null,
183 p_first_lac1_information5 in varchar2 default null,
184 p_first_lac2_information1 in varchar2 default null,
185 p_first_lac2_information2 in varchar2 default null,
186 p_first_lac2_information3 in varchar2 default null,
187 p_first_lac2_information4 in varchar2 default null,
188 p_first_lac2_information5 in varchar2 default null,
189 p_attribute_category in varchar2 default null,
190 p_attribute1 in varchar2 default null,
191 p_attribute2 in varchar2 default null,
192 p_attribute3 in varchar2 default null,
193 p_attribute4 in varchar2 default null,
194 p_attribute5 in varchar2 default null,
195 p_attribute6 in varchar2 default null,
196 p_attribute7 in varchar2 default null,
197 p_attribute8 in varchar2 default null,
198 p_attribute9 in varchar2 default null,
199 p_attribute10 in varchar2 default null,
200 p_attribute11 in varchar2 default null,
201 p_attribute12 in varchar2 default null,
202 p_attribute13 in varchar2 default null,
203 p_attribute14 in varchar2 default null,
204 p_attribute15 in varchar2 default null,
205 p_attribute16 in varchar2 default null,
206 p_attribute17 in varchar2 default null,
207 p_attribute18 in varchar2 default null,
208 p_attribute19 in varchar2 default null,
209 p_attribute20 in varchar2 default null,
210 p_print_sf50_flag in varchar2 default 'N',
211 p_printer_name in varchar2 default null,
212 p_print_back_page in varchar2 default 'Y',
213 p_1_attachment_modified_flag in varchar2 default 'N',
214 p_1_approved_flag in varchar2 default null,
215 p_1_user_name_acted_on in varchar2 default null,
216 p_1_action_taken in varchar2 default null,
217 p_1_approval_status in varchar2 default null,
218 p_2_user_name_routed_to in varchar2 default null,
219 p_2_groupbox_id in number default null,
220 p_2_routing_list_id in number default null,
221 p_2_routing_seq_number in number default null,
222 p_capped_other_pay in number default null,
223 p_to_retention_allow_percentag in number default null,
224 p_to_supervisory_diff_percenta in number default null,
225 p_to_staffing_diff_percentage in number default null,
226 p_award_percentage in number default null,
227 p_rpa_type in varchar2 default null,
228 p_mass_action_id in number default null,
229 p_mass_action_eligible_flag in varchar2 default null,
230 p_mass_action_select_flag in varchar2 default null,
231 p_mass_action_comments in varchar2 default null,
232 -- Bug#4486823 RRR Changes
233 p_payment_option in varchar2 default null,
234 p_award_salary in number default null,
235 -- Bug#4486823 RRR Changes
236 p_par_object_version_number out nocopy number,
237 p_1_pa_routing_history_id out nocopy number,
238 p_1_prh_object_version_number out nocopy number,
239 p_2_pa_routing_history_id out nocopy number,
240 p_2_prh_object_version_number out nocopy number
241
242 )is
243 --
244 -- Declare cursors and local variables
245 --
246
247 l_proc varchar2(72) := g_package||'create_sf52';
248 l_exists boolean := false;
249 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
250 l_effective_date date := trunc(nvl(p_effective_date,sysdate));
251 l_from_cop ghr_pa_requests.from_other_pay_amount%TYPE;
252 l_initiator_flag ghr_pa_routing_history.initiator_flag%TYPE;
253 l_requester_flag ghr_pa_routing_history.requester_flag%TYPE;
254 l_reviewer_flag ghr_pa_routing_history.reviewer_flag%TYPE;
255 l_authorizer_flag ghr_pa_routing_history.authorizer_flag%TYPE;
256 l_approver_flag ghr_pa_routing_history.approver_flag%TYPE;
257 l_approved_flag ghr_pa_routing_history.approved_flag%TYPE;
258 l_personnelist_flag ghr_pa_routing_history.personnelist_flag%TYPE;
259 l_user_name_employee_id per_people_f.person_id%TYPE;
260 l_user_name_emp_first_name per_people_f.first_name%TYPE;
261 l_user_name_emp_last_name per_people_f.last_name%TYPE;
262 l_user_name_emp_middle_names per_people_f.middle_names%TYPE;
263 l_2_routing_seq_number ghr_pa_routing_history.routing_seq_number%TYPE;
264 -- Modified to consider 100 characters as user name
265 l_forward_to_name varchar2(100);
266 l_2_groupbox_id ghr_pa_routing_history.groupbox_id%TYPE;
267 l_2_user_name ghr_pa_routing_history.user_name%TYPE;
268 l_action_taken ghr_pa_routing_history.action_taken%TYPE;
269 l_temp number(15);
270 --l_rec ghr_par_shd.g_old_rec%type;
271 l_rec ghr_pa_requests%rowtype;
272 l_rei_rec ghr_pa_request_extra_info%rowtype; -- Temporarily added till wrapper is available
273 l_par_object_version_number ghr_pa_requests.object_version_number%TYPE;
274 l_rei_object_version_number ghr_pa_request_extra_info.object_version_number%TYPE;
275 l_pa_request_extra_info_id ghr_pa_request_extra_info.pa_request_extra_info_id%TYPE;
276 l_flag varchar2(1);
277 l_position_id hr_all_positions_f.position_id%type;
278 l_employee_id per_people_f.person_id%type;
279 l_approval_date date;
280 l_approving_official_work_titl ghr_pa_requests.approving_official_work_title%type;
281 l_approving_official_full_name ghr_pa_requests.approving_official_full_name%type;
282 l_sf50_approval_date date;
283 l_sf50_approving_ofcl_work_tit ghr_pa_requests.sf50_approving_ofcl_work_title%type;
284 l_sf50_approving_ofcl_full_nam ghr_pa_requests.sf50_approving_ofcl_full_name%type;
285 l_status ghr_pa_requests.status%type;
286 l_message boolean := FALSE;
287 l_asg_sf52 ghr_api.asg_sf52_type;
288 l_asg_non_sf52 ghr_api.asg_non_sf52_type;
289 l_asg_nte_dates ghr_api.asg_nte_dates_type;
290 l_per_sf52 ghr_api.per_sf52_type;
291 l_per_group1 ghr_api.per_group1_type;
292 l_per_group2 ghr_api.per_group2_type;
293 l_per_scd_info ghr_api.per_scd_info_type;
294 l_per_retained_grade ghr_api.per_retained_grade_type;
295 l_per_probations ghr_api.per_probations_type;
296 l_per_sep_retire ghr_api.per_sep_retire_type;
297 l_per_security ghr_api.per_security_type;
298 l_per_conversions ghr_api.per_conversions_type;
299 l_per_uniformed_services ghr_api.per_uniformed_services_type;
300 l_pos_oblig ghr_api.pos_oblig_type;
301 l_pos_grp2 ghr_api.pos_grp2_type;
302 l_pos_grp1 ghr_api.pos_grp1_type;
303 l_pos_valid_grade ghr_api.pos_valid_grade_type;
304 l_pos_car_prog ghr_api.pos_car_prog_type;
305 l_loc_info ghr_api.loc_info_type;
306 l_wgi ghr_api.within_grade_increase_type;
307 l_recruitment_bonus ghr_api.recruitment_bonus_type;
308 l_relocation_bonus ghr_api.relocation_bonus_type;
309
310 --Pradeep
311 l_mddds_special_pay ghr_api.mddds_special_pay_type;
312 l_sf52_from_data ghr_api.prior_sf52_data_type;
313 l_personal_info ghr_api.personal_info_type;
314 l_gov_awards_type ghr_api.government_awards_type;
315 l_perf_appraisal_type ghr_api.performance_appraisal_type;
316 l_payroll_type ghr_api.government_payroll_type;
317 l_conduct_perf_type ghr_api.conduct_performance_type;
318 l_agency_sf52 ghr_api.agency_sf52_type;
319 l_agency_code varchar2(80);
320 l_imm_entitlement ghr_api.entitlement_type;
321 l_imm_foreign_lang_prof_pay ghr_api.foreign_lang_prof_pay_type;
322 l_imm_edp_pay ghr_api.edp_pay_type;
323 l_imm_hazard_pay ghr_api.hazard_pay_type;
324 l_imm_health_benefits ghr_api.health_benefits_type;
325 l_imm_danger_pay ghr_api.danger_pay_type;
326 l_imm_imminent_danger_pay ghr_api.imminent_danger_pay_type;
327 l_imm_living_quarters_allow ghr_api.living_quarters_allow_type;
328 l_imm_post_diff_amt ghr_api.post_diff_amt_type;
329 l_imm_post_diff_percent ghr_api.post_diff_percent_type;
330 l_imm_sep_maintenance_allow ghr_api.sep_maintenance_allow_type;
331 l_imm_supplemental_post_allow ghr_api.supplemental_post_allow_type;
332 l_imm_temp_lodge_allow ghr_api.temp_lodge_allow_type;
333 l_imm_premium_pay ghr_api.premium_pay_type;
334 l_imm_retirement_annuity ghr_api.retirement_annuity_type;
335 l_imm_severance_pay ghr_api.severance_pay_type;
336 l_imm_thrift_saving_plan ghr_api.thrift_saving_plan;
337 l_imm_retention_allow_review ghr_api.retention_allow_review_type;
338 l_imm_health_ben_pre_tax ghr_api.health_ben_pre_tax_type;
339 l_imm_per_benefit_info ghr_api.per_benefit_info_type;
340 l_imm_retirement_info ghr_api.per_retirement_info_type; --Bug# 7131104
341 --Begin Bug# 10181661
342 l_imm_asg_detail_info ghr_api.asg_detail_info_type;
343 l_imm_ipa_benefits_cont ghr_api.per_ipa_ben_cont_info_type;
344 --End Bug# 10181661
345
346 /* Cursor C_user_emp_names is
347 select usr.employee_id,
348 per.first_name,
349 per.last_name,
350 per.middle_names
351 from per_people_f per,
352 fnd_user usr
353 where upper(usr.user_name) = upper(p_1_user_name_acted_on)
354 and per.person_id = usr.employee_id
355 and l_effective_date
356 between effective_start_date
357 and effective_end_date; */
358
359 -- Bug 4863608 Perf. repository changes
360 --8229939 modified to consider sysdate
361 CURSOR c_user_emp_names IS
362 SELECT usr.employee_id,
363 per.first_name,
364 per.last_name,
365 per.middle_names
366 FROM per_people_f per,
367 fnd_user usr
368 WHERE usr.user_name = upper(p_1_user_name_acted_on)
369 AND per.person_id = usr.employee_id
370 AND trunc(sysdate)
371 BETWEEN effective_start_date
372 AND effective_end_date;
373
374
375 CURSOR C_seq_number IS
376 SELECT rlm.seq_number,
377 rlm.groupbox_id,
378 rlm.user_name
379 FROM ghr_routing_list_members rlm
380 WHERE rlm.routing_list_id = p_2_routing_list_id
381 ORDER BY rlm.seq_number ASC;
382
383
384 CURSOR c_history_exists IS
385 SELECT 1
386 FROM ghr_pa_routing_history prh
387 WHERE prh.pa_request_id = l_pa_request_id;
388
389 CURSOR c_groupbox_name IS
390 SELECT gbx.name
391 FROM ghr_groupboxes gbx
392 WHERE gbx.groupbox_id = l_2_groupbox_id;
393
394 CURSOR c_ovn IS
395 SELECT par.object_version_number
396 FROM ghr_pa_requests par
397 WHERE par.pa_request_id = l_pa_request_id;
398 --Begin Bug# 7174094
399 l_separation_flag BOOLEAN;
400
401 cursor c_sec_noa_fmly is
402 select fams.noa_family_code
403 from ghr_noa_families noafam,
404 ghr_families fams
405 where noafam.nature_of_action_id = p_second_noa_id
406 and noafam.enabled_flag = 'Y'
407 and fams.noa_family_code = noafam.noa_family_code
408 and fams.enabled_flag = 'Y'
409 and fams.update_hr_flag = 'Y'
410 and l_effective_date between nvl(noafam.start_date_active,to_date('1951/01/01','RRRR/MM/DD'))
411 and nvl(noafam.end_date_active,to_date('4712/12/31','RRRR/MM/DD'));
412 --End Bug# 7174094
413
414 l_capped_other_pay number;
415 l_assignment_id ghr_pa_requests.employee_assignment_id%type;
416
417 --Bug# 6127620
418 -- l_curr_sess_date fnd_sessions.effective_date%type;
419
420 begin
421 hr_utility.set_location('Entering:'|| l_proc, 5);
422
423 -- Issue a savepoint if operating in validation only mode.
424 -- if p_validate then
425 savepoint create_sf52;
426 -- end if;
427 --
428 -- Call Before Process User Hook
429 --
430 begin
431 ghr_sf52_bk1.create_sf52_b (
432 p_noa_family_code => p_noa_family_code,
433 p_pa_request_id => p_pa_request_id,
434 p_routing_group_id => p_routing_group_id,
435 p_proposed_effective_asap_flag => p_proposed_effective_asap_flag,
436 p_academic_discipline => p_academic_discipline,
437 p_additional_info_person_id => p_additional_info_person_id,
438 p_additional_info_tel_number => p_additional_info_tel_number,
439 p_altered_pa_request_id => p_altered_pa_request_id,
440 p_annuitant_indicator => p_annuitant_indicator,
441 p_annuitant_indicator_desc => p_annuitant_indicator_desc,
442 p_appropriation_code1 => p_appropriation_code1,
443 p_appropriation_code2 => p_appropriation_code2,
444 p_approval_date => l_approval_date,
445 p_approving_official_full_name => l_approving_official_full_name,
446 p_approving_official_work_titl => l_approving_official_work_titl,
447 p_authorized_by_person_id => p_authorized_by_person_id,
448 p_authorized_by_title => p_authorized_by_title,
449 p_award_amount => p_award_amount,
450 p_award_uom => p_award_uom,
451 p_bargaining_unit_status => p_bargaining_unit_status,
452 p_citizenship => p_citizenship,
453 p_concurrence_date => p_concurrence_date,
454 p_custom_pay_calc_flag => p_custom_pay_calc_flag,
455 p_duty_station_code => p_duty_station_code,
456 p_duty_station_desc => p_duty_station_desc,
457 p_duty_station_location_id => p_duty_station_location_id,
458 p_duty_station_id => p_duty_station_id,
459 p_education_level => p_education_level,
460 p_effective_date => p_effective_date,
461 p_employee_assignment_id => p_employee_assignment_id,
462 p_employee_date_of_birth => p_employee_date_of_birth,
463 p_employee_first_name => p_employee_first_name,
464 p_employee_last_name => p_employee_last_name,
465 p_employee_middle_names => p_employee_middle_names,
466 p_employee_national_identifier => p_employee_national_identifier,
467 p_fegli => p_fegli,
468 p_fegli_desc => p_fegli_desc,
469 p_first_action_la_code1 => p_first_action_la_code1,
470 p_first_action_la_code2 => p_first_action_la_code2,
471 p_first_action_la_desc1 => p_first_action_la_desc1,
472 p_first_action_la_desc2 => p_first_action_la_desc2,
473 p_first_noa_cancel_or_correct => p_first_noa_cancel_or_correct,
474 p_first_noa_id => p_first_noa_id,
475 p_first_noa_code => p_first_noa_code,
476 p_first_noa_desc => p_first_noa_desc,
477 p_first_noa_pa_request_id => p_first_noa_pa_request_id,
478 p_flsa_category => p_flsa_category,
479 p_forwarding_address_line1 => p_forwarding_address_line1,
480 p_forwarding_address_line2 => p_forwarding_address_line2,
481 p_forwarding_address_line3 => p_forwarding_address_line3,
482 p_forwarding_country => p_forwarding_country,
483 p_forwarding_country_short_nam => p_forwarding_country_short_nam,
484 p_forwarding_postal_code => p_forwarding_postal_code,
485 p_forwarding_region_2 => p_forwarding_region_2,
486 p_forwarding_town_or_city => p_forwarding_town_or_city ,
487 p_from_adj_basic_pay => p_from_adj_basic_pay,
488 p_from_basic_pay => p_from_basic_pay,
489 p_from_grade_or_level => p_from_grade_or_level,
490 p_from_locality_adj => p_from_locality_adj,
491 p_from_occ_code => p_from_occ_code,
492 p_from_other_pay_amount => p_from_other_pay_amount,
493 p_from_pay_basis => p_from_pay_basis,
494 p_from_pay_plan => p_from_pay_plan,
495 -- FWFA Changes Bug#4444609
496 -- p_input_pay_rate_determinant => p_input_pay_rate_determinant,
497 -- p_from_pay_table_identifier => p_from_pay_table_identifier,
498 -- FWFA Changes
499 p_from_position_id => p_from_position_id,
500 p_from_position_org_line1 => p_from_position_org_line1,
501 p_from_position_org_line2 => p_from_position_org_line2,
502 p_from_position_org_line3 => p_from_position_org_line3,
503 p_from_position_org_line4 => p_from_position_org_line4,
504 p_from_position_org_line5 => p_from_position_org_line5,
505 p_from_position_org_line6 => p_from_position_org_line6,
506 p_from_position_number => p_from_position_number,
507 p_from_position_seq_no => p_from_position_seq_no,
508 p_from_position_title => p_from_position_title,
509 p_from_step_or_rate => p_from_step_or_rate,
510 p_from_total_salary => p_from_total_salary,
511 p_functional_class => p_functional_class,
512 p_notepad => p_notepad,
513 p_part_time_hours => p_part_time_hours,
514 p_pay_rate_determinant => p_pay_rate_determinant,
515 p_person_id => p_person_id,
516 p_position_occupied => p_position_occupied,
517 p_proposed_effective_date => p_proposed_effective_date,
518 p_requested_by_person_id => p_requested_by_person_id,
519 p_requested_by_title => p_requested_by_title,
520 p_requested_date => p_requested_date,
521 p_requesting_office_remarks_de => p_requesting_office_remarks_de,
522 p_requesting_office_remarks_fl => p_requesting_office_remarks_fl,
523 p_request_number => p_request_number,
524 p_resign_and_retire_reason_des => p_resign_and_retire_reason_des,
525 p_retirement_plan => p_retirement_plan,
526 p_retirement_plan_desc => p_retirement_plan_desc,
527 p_second_action_la_code1 => p_second_action_la_code1,
528 p_second_action_la_code2 => p_second_action_la_code2,
529 p_second_action_la_desc1 => p_second_action_la_desc1,
530 p_second_action_la_desc2 => p_second_action_la_desc2,
531 p_second_noa_cancel_or_correct => p_second_noa_cancel_or_correct,
532 p_second_noa_code => p_second_noa_code,
533 p_second_noa_desc => p_second_noa_desc,
534 p_second_noa_id => p_second_noa_id,
535 p_second_noa_pa_request_id => p_second_noa_pa_request_id,
536 p_service_comp_date => p_service_comp_date,
537 p_supervisory_status => p_supervisory_status,
538 p_tenure => p_tenure,
539 p_to_adj_basic_pay => p_to_adj_basic_pay,
540 p_to_basic_pay => p_to_basic_pay,
541 p_to_grade_id => p_to_grade_id,
542 p_to_grade_or_level => p_to_grade_or_level,
543 p_to_job_id => p_to_job_id,
544 p_to_locality_adj => p_to_locality_adj,
545 p_to_occ_code => p_to_occ_code,
546 p_to_organization_id => p_to_organization_id,
547 p_to_other_pay_amount => p_to_other_pay_amount,
548 p_to_au_overtime => p_to_au_overtime,
549 p_to_auo_premium_pay_indicator => p_to_auo_premium_pay_indicator,
550 p_to_availability_pay => p_to_availability_pay,
551 p_to_ap_premium_pay_indicator => p_to_ap_premium_pay_indicator,
552 p_to_retention_allowance => p_to_retention_allowance,
553 p_to_supervisory_differential => p_to_supervisory_differential,
554 p_to_staffing_differential => p_to_staffing_differential,
555 p_to_pay_basis => p_to_pay_basis,
556 p_to_pay_plan => p_to_pay_plan,
557 -- FWFA Changes Bug#4444609
558 -- p_to_pay_table_identifier => p_to_pay_table_identifier,
559 -- FWFA Changes
560 p_to_position_id => p_to_position_id,
561 p_to_position_org_line1 => p_to_position_org_line1,
562 p_to_position_org_line2 => p_to_position_org_line2,
563 p_to_position_org_line3 => p_to_position_org_line3,
564 p_to_position_org_line4 => p_to_position_org_line4,
565 p_to_position_org_line5 => p_to_position_org_line5,
566 p_to_position_org_line6 => p_to_position_org_line6,
567 p_to_position_number => p_to_position_number,
568 p_to_position_seq_no => p_to_position_seq_no,
569 p_to_position_title => p_to_position_title,
570 p_to_step_or_rate => p_to_step_or_rate,
571 p_to_total_salary => p_to_total_salary,
572 p_veterans_pref_for_rif => p_veterans_pref_for_rif,
573 p_veterans_preference => p_veterans_preference,
574 p_veterans_status => p_veterans_status,
575 p_work_schedule => p_work_schedule,
576 p_work_schedule_desc => p_work_schedule_desc,
577 p_year_degree_attained => p_year_degree_attained,
578 p_first_noa_information1 => p_first_noa_information1,
579 p_first_noa_information2 => p_first_noa_information2,
580 p_first_noa_information3 => p_first_noa_information3,
581 p_first_noa_information4 => p_first_noa_information4,
582 p_first_noa_information5 => p_first_noa_information5,
583 p_second_lac1_information1 => p_second_lac1_information1,
584 p_second_lac1_information2 => p_second_lac1_information2,
585 p_second_lac1_information3 => p_second_lac1_information3,
586 p_second_lac1_information4 => p_second_lac1_information4,
587 p_second_lac1_information5 => p_second_lac1_information5,
588 p_second_lac2_information1 => p_second_lac2_information1,
589 p_second_lac2_information2 => p_second_lac2_information2,
590 p_second_lac2_information3 => p_second_lac2_information3,
591 p_second_lac2_information4 => p_second_lac2_information4,
592 p_second_lac2_information5 => p_second_lac2_information5,
593 p_second_noa_information1 => p_second_noa_information1,
594 p_second_noa_information2 => p_second_noa_information2,
595 p_second_noa_information3 => p_second_noa_information3,
596 p_second_noa_information4 => p_second_noa_information4,
597 p_second_noa_information5 => p_second_noa_information5,
598 p_first_lac1_information1 => p_first_lac1_information1,
599 p_first_lac1_information2 => p_first_lac1_information2,
600 p_first_lac1_information3 => p_first_lac1_information3,
601 p_first_lac1_information4 => p_first_lac1_information4,
602 p_first_lac1_information5 => p_first_lac1_information5,
603 p_first_lac2_information1 => p_first_lac2_information1,
604 p_first_lac2_information2 => p_first_lac2_information2,
605 p_first_lac2_information3 => p_first_lac2_information3,
606 p_first_lac2_information4 => p_first_lac2_information4,
607 p_first_lac2_information5 => p_first_lac2_information5,
608 p_attribute_category => p_attribute_category,
609 p_attribute1 => p_attribute1,
610 p_attribute2 => p_attribute2,
611 p_attribute3 => p_attribute3,
612 p_attribute4 => p_attribute4,
613 p_attribute5 => p_attribute5,
614 p_attribute6 => p_attribute6,
615 p_attribute7 => p_attribute7,
616 p_attribute8 => p_attribute8,
617 p_attribute9 => p_attribute9,
618 p_attribute10 => p_attribute10,
619 p_attribute11 => p_attribute11,
620 p_attribute12 => p_attribute12,
621 p_attribute13 => p_attribute13,
622 p_attribute14 => p_attribute14,
623 p_attribute15 => p_attribute15,
624 p_attribute16 => p_attribute16,
625 p_attribute17 => p_attribute17,
626 p_attribute18 => p_attribute18,
627 p_attribute19 => p_attribute19,
628 p_attribute20 => p_attribute20,
629 p_print_sf50_flag => p_print_sf50_flag,
630 p_printer_name => p_printer_name,
631 p_1_attachment_modified_flag => p_1_attachment_modified_flag,
632 p_1_approved_flag => p_1_approved_flag,
633 p_1_user_name_acted_on => p_1_user_name_acted_on,
634 p_1_action_taken => p_1_action_taken,
635 p_1_approval_status => p_1_approval_status,
636 p_2_user_name_routed_to => p_2_user_name_routed_to,
637 p_2_groupbox_id => p_2_groupbox_id,
638 p_2_routing_list_id => p_2_routing_list_id,
639 p_2_routing_seq_number => p_2_routing_seq_number,
640 p_capped_other_pay => p_capped_other_pay,
641 p_to_retention_allow_percentag => p_to_retention_allow_percentag,
642 p_to_supervisory_diff_percenta => p_to_supervisory_diff_percenta,
643 p_to_staffing_diff_percentage => p_to_staffing_diff_percentage,
644 p_award_percentage => p_award_percentage,
645 p_rpa_type => p_rpa_type,
646 p_mass_action_id => p_mass_action_id,
647 p_mass_action_eligible_flag => p_mass_action_eligible_flag,
648 p_mass_action_select_flag => p_mass_action_select_flag,
649 p_mass_action_comments => p_mass_action_comments
650 );
651 exception
652 when hr_api.cannot_find_prog_unit then
653 hr_api.cannot_find_prog_unit_error
654 (p_module_name => 'create_sf52',
655 p_hook_type => 'BP'
656 );
657 end;
658 --
659 -- End of Before Process User Hook call
660 --
661 hr_utility.set_location(l_proc, 6);
662 -- Process Logic
663
664 -- If the SF52 is processed for the person same as the user, then do not allow creation
665
666 If p_person_id is not null then
667 -- get employee_id of the user
668 for user_id in c_user_emp_names loop
669 If user_id.employee_id = p_person_id then
670 hr_utility.set_message(8301,'GHR_38503_CANNOT_INIT_FOR_SELF');
671 hr_utility.raise_error;
672 End if;
673 end loop;
674 End if;
675
676 l_approval_date := p_approval_date;
677 l_approving_official_work_titl := p_approving_official_work_titl;
678 l_approving_official_full_name := p_approving_official_full_name;
679
680 If nvl(p_1_approval_status,hr_api.g_varchar2) = 'APPROVE' then
681 If p_approval_date is null
682 then
683 hr_utility.set_location('approval date is not null',1);
684 l_effective_date := trunc(sysdate);
685 l_approval_date := sysdate;
686 -- get the full_name of the approver - format First Name MiddleName. Last Name -- p_user_name_acted_on
687 for user_emp_name in c_user_emp_names loop
688 l_approving_official_full_name := user_emp_name.first_name;
689 If user_emp_name.middle_names is not null then
690 l_approving_official_full_name := l_approving_official_full_name
691 || ' ' ||substr(user_emp_name.middle_names,1,1) || '.' || ' ' || user_emp_name.last_name ;
692 Else
693 l_approving_official_full_name := l_approving_official_full_name || ' ' || user_emp_name.last_name;
694 End if;
695 l_employee_id := user_emp_name.employee_id;
696 end loop;
697 if l_employee_id is not null then
698 -- get the working title of the approver -- would be as of today
699 l_approving_official_work_titl := ghr_pa_requests_pkg.get_position_work_title
700 (p_person_id => l_employee_id
701 );
702 End if;
703 Else
704 l_approving_official_work_titl := p_approving_official_work_titl;
705 l_approving_official_full_name := p_approving_official_full_name;
706 l_approval_date := p_approval_date;
707 End if;
708 End if;
709
710 -- Update the SF50 approver details , when the user chooses to 'Update HR' (Immediate or Future)
711 -- Derive for individual actions . For Mass Actions they are the same as the SF52 approver details
712
713 If nvl(p_1_action_taken,hr_api.g_varchar2) in ('UPDATE_HR','FUTURE_ACTION') then
714 If p_approval_date is not null then
715 l_sf50_approval_date := p_approval_date;
716 l_sf50_approving_ofcl_work_tit := p_approving_official_work_titl;
717 l_sf50_approving_ofcl_full_nam := p_approving_official_full_name;
718 Else
719 l_sf50_approval_date := sysdate;
720 for user_emp_name in c_user_emp_names loop
721 l_sf50_approving_ofcl_full_nam := user_emp_name.first_name;
722 If user_emp_name.middle_names is not null then
723 l_sf50_approving_ofcl_full_nam := l_sf50_approving_ofcl_full_nam
724 || ' ' ||substr(user_emp_name.middle_names,1,1) || '.' || ' ' || user_emp_name.last_name ;
725 Else
726 l_sf50_approving_ofcl_full_nam := l_sf50_approving_ofcl_full_nam || ' ' || user_emp_name.last_name;
727 End if;
728 l_employee_id := user_emp_name.employee_id;
729 end loop;
730 if l_employee_id is not null then
731 -- get the working title of the approver -- would be as of today
732 l_sf50_approving_ofcl_work_tit := ghr_pa_requests_pkg.get_position_work_title
733 (p_person_id => l_employee_id
734 --p_effective_date => l_effective_date
735 );
736 End if;
737 End if;
738 End if;
739
740
741
742 -- Insert a row into pa_requests by calling the ins row handler
743
744 l_pa_request_id := p_pa_request_id;
745 l_effective_date := trunc(nvl(p_effective_date,sysdate));
746 hr_utility.set_location('appr name ' || l_approving_official_full_name,1);
747 hr_utility.set_location('l_effective_date : ' ||l_effective_date, 8);
748 hr_utility.set_location('p_noa_family_code : ' ||p_noa_family_code, 8);
749
750 ghr_par_ins.ins
751 (
752 p_pa_request_id => l_pa_request_id,
753 p_noa_family_code => p_noa_family_code,
754 p_routing_group_id => p_routing_group_id,
755 p_proposed_effective_asap_flag => p_proposed_effective_asap_flag,
756 p_academic_discipline => p_academic_discipline,
757 p_additional_info_person_id => p_additional_info_person_id,
758 p_additional_info_tel_number => p_additional_info_tel_number,
759 p_altered_pa_request_id => p_altered_pa_request_id,
760 p_annuitant_indicator => p_annuitant_indicator,
761 p_annuitant_indicator_desc => p_annuitant_indicator_desc,
762 p_appropriation_code1 => p_appropriation_code1,
763 p_appropriation_code2 => p_appropriation_code2,
764 p_approval_date => l_approval_date,
765 p_approving_official_full_name => l_approving_official_full_name,
766 p_approving_official_work_titl => l_approving_official_work_titl,
767 p_sf50_approval_date => l_sf50_approval_date,
768 p_sf50_approving_ofcl_full_nam => l_sf50_approving_ofcl_full_nam,
769 p_sf50_approving_ofcl_work_tit => l_sf50_approving_ofcl_work_tit,
770 p_authorized_by_person_id => p_authorized_by_person_id,
771 p_authorized_by_title => p_authorized_by_title,
772 p_award_amount => p_award_amount,
773 p_award_uom => p_award_uom,
774 p_bargaining_unit_status => p_bargaining_unit_status,
775 p_citizenship => p_citizenship,
776 p_concurrence_date => p_concurrence_date,
777 p_custom_pay_calc_flag => p_custom_pay_calc_flag,
778 p_duty_station_code => p_duty_station_code,
779 p_duty_station_desc => p_duty_station_desc,
780 p_duty_station_location_id => p_duty_station_location_id,
781 p_duty_station_id => p_duty_station_id,
782 p_education_level => p_education_level,
783 p_effective_date => p_effective_date,
784 p_employee_assignment_id => p_employee_assignment_id,
785 p_employee_date_of_birth => p_employee_date_of_birth,
786 p_employee_first_name => p_employee_first_name,
787 p_employee_last_name => p_employee_last_name,
788 p_employee_middle_names => p_employee_middle_names,
789 p_employee_national_identifier => p_employee_national_identifier,
790 p_fegli => p_fegli,
791 p_fegli_desc => p_fegli_desc,
792 p_first_action_la_code1 => p_first_action_la_code1,
793 p_first_action_la_code2 => p_first_action_la_code2,
794 p_first_action_la_desc1 => p_first_action_la_desc1,
795 p_first_action_la_desc2 => p_first_action_la_desc2,
796 p_first_noa_cancel_or_correct => p_first_noa_cancel_or_correct,
797 p_first_noa_id => p_first_noa_id,
798 p_first_noa_code => p_first_noa_code,
799 p_first_noa_desc => p_first_noa_desc,
800 p_first_noa_pa_request_id => p_first_noa_pa_request_id,
801 p_flsa_category => p_flsa_category,
802 p_forwarding_address_line1 => p_forwarding_address_line1,
803 p_forwarding_address_line2 => p_forwarding_address_line2,
804 p_forwarding_address_line3 => p_forwarding_address_line3,
805 p_forwarding_country => p_forwarding_country,
806 p_forwarding_country_short_nam => p_forwarding_country_short_nam,
807 p_forwarding_postal_code => p_forwarding_postal_code,
808 p_forwarding_region_2 => p_forwarding_region_2,
809 p_forwarding_town_or_city => p_forwarding_town_or_city ,
810 p_from_adj_basic_pay => p_from_adj_basic_pay,
811 p_from_basic_pay => p_from_basic_pay,
812 p_from_grade_or_level => p_from_grade_or_level,
813 p_from_locality_adj => p_from_locality_adj,
814 p_from_occ_code => p_from_occ_code,
815 -- Bug 2353506
816 p_from_other_pay_amount => nvl(ghr_pa_requests_pkg2.get_cop
817 (p_employee_assignment_id, p_effective_date),
818 p_from_other_pay_amount),
819 -- End Bug 2353506
820 p_from_pay_basis => p_from_pay_basis,
821 p_from_pay_plan => p_from_pay_plan,
822 -- FWFA Changes Bug#4444609
823 p_input_pay_rate_determinant => p_input_pay_rate_determinant,
824 p_from_pay_table_identifier => p_from_pay_table_identifier,
825 -- FWFA Changes
826 p_from_position_id => p_from_position_id,
827 p_from_position_org_line1 => p_from_position_org_line1,
828 p_from_position_org_line2 => p_from_position_org_line2,
829 p_from_position_org_line3 => p_from_position_org_line3,
830 p_from_position_org_line4 => p_from_position_org_line4,
831 p_from_position_org_line5 => p_from_position_org_line5,
832 p_from_position_org_line6 => p_from_position_org_line6,
833 p_from_position_number => p_from_position_number,
834 p_from_position_seq_no => p_from_position_seq_no,
835 p_from_position_title => p_from_position_title,
836 p_from_step_or_rate => p_from_step_or_rate,
837 p_from_total_salary => p_from_total_salary,
838 p_functional_class => p_functional_class,
839 p_notepad => p_notepad,
840 p_part_time_hours => p_part_time_hours,
841 p_pay_rate_determinant => p_pay_rate_determinant,
842 p_person_id => p_person_id,
843 p_position_occupied => p_position_occupied,
844 p_proposed_effective_date => p_proposed_effective_date,
845 p_requested_by_person_id => p_requested_by_person_id,
846 p_requested_by_title => p_requested_by_title,
847 p_requested_date => p_requested_date,
848 p_requesting_office_remarks_de => p_requesting_office_remarks_de,
849 p_requesting_office_remarks_fl => p_requesting_office_remarks_fl,
850 p_request_number => p_request_number,
851 p_resign_and_retire_reason_des => p_resign_and_retire_reason_des,
852 p_retirement_plan => p_retirement_plan,
853 p_retirement_plan_desc => p_retirement_plan_desc,
854 p_second_action_la_code1 => p_second_action_la_code1,
855 p_second_action_la_code2 => p_second_action_la_code2,
856 p_second_action_la_desc1 => p_second_action_la_desc1,
857 p_second_action_la_desc2 => p_second_action_la_desc2,
858 p_second_noa_cancel_or_correct => p_second_noa_cancel_or_correct,
859 p_second_noa_code => p_second_noa_code,
860 p_second_noa_desc => p_second_noa_desc,
861 p_second_noa_id => p_second_noa_id,
862 p_second_noa_pa_request_id => p_second_noa_pa_request_id,
863 p_service_comp_date => p_service_comp_date,
864 p_supervisory_status => p_supervisory_status,
865 p_tenure => p_tenure,
866 p_to_adj_basic_pay => p_to_adj_basic_pay,
867 p_to_basic_pay => p_to_basic_pay,
868 p_to_grade_id => p_to_grade_id,
869 p_to_grade_or_level => p_to_grade_or_level,
870 p_to_job_id => p_to_job_id,
871 p_to_locality_adj => p_to_locality_adj,
872 p_to_occ_code => p_to_occ_code,
873 p_to_organization_id => p_to_organization_id,
874 -- Bug 2353506
875 p_to_other_pay_amount => nvl(p_capped_other_pay,p_to_other_pay_amount),
876 -- End Bug 2353506
877 p_to_au_overtime => p_to_au_overtime,
878 p_to_auo_premium_pay_indicator => p_to_auo_premium_pay_indicator,
879 p_to_availability_pay => p_to_availability_pay,
880 p_to_ap_premium_pay_indicator => p_to_ap_premium_pay_indicator,
881 p_to_retention_allowance => p_to_retention_allowance,
882 p_to_supervisory_differential => p_to_supervisory_differential,
883 p_to_staffing_differential => p_to_staffing_differential,
884 p_to_pay_basis => p_to_pay_basis,
885 p_to_pay_plan => p_to_pay_plan,
886 -- FWFA Changes Bug#4444609
887 p_to_pay_table_identifier => p_to_pay_table_identifier,
888 -- FWFA Changes
889 p_to_position_id => p_to_position_id,
890 p_to_position_org_line1 => p_to_position_org_line1,
891 p_to_position_org_line2 => p_to_position_org_line2,
892 p_to_position_org_line3 => p_to_position_org_line3,
893 p_to_position_org_line4 => p_to_position_org_line4,
894 p_to_position_org_line5 => p_to_position_org_line5,
895 p_to_position_org_line6 => p_to_position_org_line6,
896 p_to_position_number => p_to_position_number,
897 p_to_position_seq_no => p_to_position_seq_no,
898 p_to_position_title => p_to_position_title,
899 p_to_step_or_rate => p_to_step_or_rate,
900 p_to_total_salary => p_to_total_salary,
901 p_veterans_pref_for_rif => p_veterans_pref_for_rif,
902 p_veterans_preference => p_veterans_preference,
903 p_veterans_status => p_veterans_status,
904 p_work_schedule => p_work_schedule,
905 p_work_schedule_desc => p_work_schedule_desc,
906 p_year_degree_attained => p_year_degree_attained,
907 p_first_noa_information1 => p_first_noa_information1,
908 p_first_noa_information2 => p_first_noa_information2,
909 p_first_noa_information3 => p_first_noa_information3,
910 p_first_noa_information4 => p_first_noa_information4,
911 p_first_noa_information5 => p_first_noa_information5,
912 p_second_lac1_information1 => p_second_lac1_information1,
913 p_second_lac1_information2 => p_second_lac1_information2,
914 p_second_lac1_information3 => p_second_lac1_information3,
915 p_second_lac1_information4 => p_second_lac1_information4,
916 p_second_lac1_information5 => p_second_lac1_information5,
917 p_second_lac2_information1 => p_second_lac2_information1,
918 p_second_lac2_information2 => p_second_lac2_information2,
919 p_second_lac2_information3 => p_second_lac2_information3,
920 p_second_lac2_information4 => p_second_lac2_information4,
921 p_second_lac2_information5 => p_second_lac2_information5,
922 p_second_noa_information1 => p_second_noa_information1,
923 p_second_noa_information2 => p_second_noa_information2,
924 p_second_noa_information3 => p_second_noa_information3,
925 p_second_noa_information4 => p_second_noa_information4,
926 p_second_noa_information5 => p_second_noa_information5,
927 p_first_lac1_information1 => p_first_lac1_information1,
928 p_first_lac1_information2 => p_first_lac1_information2,
929 p_first_lac1_information3 => p_first_lac1_information3,
930 p_first_lac1_information4 => p_first_lac1_information4,
931 p_first_lac1_information5 => p_first_lac1_information5,
932 p_first_lac2_information1 => p_first_lac2_information1,
933 p_first_lac2_information2 => p_first_lac2_information2,
934 p_first_lac2_information3 => p_first_lac2_information3,
935 p_first_lac2_information4 => p_first_lac2_information4,
936 p_first_lac2_information5 => p_first_lac2_information5,
937 p_attribute_category => p_attribute_category,
938 p_attribute1 => p_attribute1,
939 p_attribute2 => p_attribute2,
940 p_attribute3 => p_attribute3,
941 p_attribute4 => p_attribute4,
942 p_attribute5 => p_attribute5,
943 p_attribute6 => p_attribute6,
944 p_attribute7 => p_attribute7,
945 p_attribute8 => p_attribute8,
946 p_attribute9 => p_attribute9,
947 p_attribute10 => p_attribute10,
948 p_attribute11 => p_attribute11,
949 p_attribute12 => p_attribute12,
950 p_attribute13 => p_attribute13,
951 p_attribute14 => p_attribute14,
952 p_attribute15 => p_attribute15,
953 p_attribute16 => p_attribute16,
954 p_attribute17 => p_attribute17,
955 p_attribute18 => p_attribute18,
956 p_attribute19 => p_attribute19,
957 p_attribute20 => p_attribute20,
958 p_object_version_number => l_par_object_version_number,
959 p_to_retention_allow_percentag => p_to_retention_allow_percentag,
960 p_to_supervisory_diff_percenta => p_to_supervisory_diff_percenta,
961 p_to_staffing_diff_percentage => p_to_staffing_diff_percentage,
962 p_award_percentage => p_award_percentage,
963 p_rpa_type => p_rpa_type,
964 p_mass_action_id => p_mass_action_id,
965 p_mass_action_eligible_flag => p_mass_action_eligible_flag,
966 p_mass_action_select_flag => p_mass_action_select_flag,
967 p_mass_action_comments => p_mass_action_comments ,
968 -- Bug#4486823 RRR Changes
969 p_payment_option => p_payment_option,
970 p_award_salary => p_award_salary
971 -- Bug#4486823 RRR Changes
972 );
973 p_par_object_version_number := l_par_object_version_number ;
974 hr_utility.set_location(l_proc, 8);
975
976
977 --2) Write into pa_remarks all mandatory remarks for the specific nature_of_action (first and second)
978
979 if p_first_noa_id is not null then
980 insert into ghr_pa_remarks
981 (pa_remark_id
982 ,pa_request_id
983 ,remark_id
984 ,description
985 ,object_version_number
986 )
987 select ghr_pa_remarks_s.nextval
988 ,l_pa_request_id
989 ,rem.remark_id
990 ,rem.description
991 ,1
992 from ghr_remarks rem,
993 ghr_noac_remarks nre
994 where nre.nature_of_action_id = p_first_noa_id
995 and nre.required_flag = 'Y'
996 and l_effective_date
997 between nre.date_from
998 and nvl(nre.date_to,l_effective_date)
999 and nre.remark_id = rem.remark_id;
1000 -- and rem.enabled_flag = 'Y'
1001 -- and l_effective_date
1002 -- between rem.date_from
1003 -- and nvl(rem.date_to,l_effective_date));
1004 end if;
1005
1006 --begin Bug# 7174094
1007 l_separation_flag := FALSE;
1008 IF p_first_noa_code in ('002') THEN
1009 For l_sec_noa_fmly in c_sec_noa_fmly loop
1010 IF l_sec_noa_fmly.noa_family_code = 'SEPARATION' THEN
1011 l_separation_flag := TRUE;
1012 END IF;
1013 end loop;
1014 END IF;
1015 -- Bug# 7174094 added l_separation_flag condition in below if condition
1016 if p_second_noa_id is not null AND NOT( l_separation_flag AND p_first_noa_code in ('002'))then
1017 --End Bug# 7174094
1018 insert into ghr_pa_remarks
1019 (pa_remark_id
1020 ,pa_request_id
1021 ,remark_id
1022 ,description
1023 ,object_version_number
1024 )
1025 select ghr_pa_remarks_s.nextval
1026 ,l_pa_request_id
1027 ,rem.remark_id
1028 ,rem.description
1029 ,1
1030 from ghr_remarks rem,
1031 ghr_noac_remarks nre
1032 where nre.nature_of_action_id = p_second_noa_id
1033 and nre.required_flag = 'Y'
1034 and l_effective_date
1035 between nre.date_from
1036 and nvl(nre.date_to,l_effective_date)
1037 and nre.remark_id = rem.remark_id;
1038 -- and rem.enabled_flag = 'Y'
1039 -- and l_effective_date
1040 -- between rem.date_from
1041 -- and nvl(rem.date_to,l_effective_date));
1042 end if;
1043
1044 -- create all generic extra information
1045
1046 --- Bug # 6127620 need to set the FND Session Date before calling extra info
1047 /*If p_first_noa_code = '800' or p_second_noa_code = '800' then
1048 GHR_HISTORY_API.get_session_date(l_curr_sess_date);
1049 ghr_session.set_fnd_session_date(l_effective_date);
1050 end if; */
1051
1052
1053 --Begin
1054 ----Bug #6127620
1055
1056 -- Create all the noa_specific extra information
1057 l_position_id := nvl(p_to_position_id,p_from_position_id);
1058
1059 -- (nvl(p_1_action_taken,hr_api.g_varchar2) = 'NOT_ROUTED' and p_mass_action_id is null and nvl(p_rpa_type,hr_api.g_varchar2) <> 'TA' )
1060 -- Comment above line because irrespective of p_mass_action_id value the if condn should be true (AVR)
1061
1062 If nvl(p_first_noa_code,hr_api.g_varchar2) <> '001'
1063 or
1064 (nvl(p_1_action_taken,hr_api.g_varchar2) = 'NOT_ROUTED' and
1065 nvl(p_rpa_type,hr_api.g_varchar2) <> 'TA' )
1066 then
1067
1068 If p_person_id is not null or p_noa_family_code = 'APP' then
1069 GHR_NON_SF52_EXTRA_INFO.fetch_generic_extra_info
1070 (p_pa_request_id => l_pa_request_id,
1071 p_person_id => p_person_id,
1072 p_assignment_id => p_employee_assignment_id,
1073 p_effective_date => trunc(nvl(p_effective_date,sysdate)),
1074 p_refresh_flag => 'N'
1075 );
1076 End if;
1077
1078 If ((p_first_noa_code is not null or p_second_noa_code is not null) and
1079 ( p_person_id is not null or p_employee_assignment_id is not null or
1080 l_position_id is not null)) then
1081
1082 GHR_NON_SF52_EXTRA_INFO.populate_noa_spec_extra_info
1083 (p_pa_request_id => l_pa_request_id,
1084 p_first_noa_id => p_first_noa_id,
1085 p_second_noa_id => p_second_noa_id,
1086 p_person_id => p_person_id,
1087 p_assignment_id => p_employee_assignment_id,
1088 p_position_id => l_position_id,
1089 p_effective_date => p_effective_date,
1090 p_refresh_flag => 'N'
1091 );
1092
1093 End if;
1094 End if;
1095 -- Bug # 6127620
1096 /* Exception
1097 when OTHERS then
1098 If p_first_noa_code = '800' or p_second_noa_code = '800' then
1099 ghr_session.set_fnd_session_date(l_curr_sess_date);
1100 raise;
1101 else
1102 raise;
1103 end if;
1104 End;
1105 If p_first_noa_code = '800' or p_second_noa_code = '800' then
1106 ghr_session.set_fnd_session_date(l_curr_sess_date);
1107 end if; */
1108 -- Bug # 6127620
1109
1110
1111
1112
1113 -- populate the generic extra info
1114
1115 -- 1. 'GHR_US_PAR_PAYROLL_TYPE
1116 -- 2. 'GHR_US_PAR_PERF_APPRAISAL
1117
1118 -- 3)Derive all parmeters required to insert routing_history records.
1119 -- Roles , Action_taken (and sequence Number if necessary)
1120
1121 if p_1_user_name_acted_on is not null then
1122 ghr_pa_requests_pkg.get_roles
1123 (l_pa_request_id,
1124 p_routing_group_id,
1125 p_1_user_name_acted_on,
1126 l_initiator_flag,
1127 l_requester_flag,
1128 l_authorizer_flag,
1129 l_personnelist_flag,
1130 l_approver_flag,
1131 l_reviewer_flag
1132 );
1133
1134 for user_emp_names in C_user_emp_names loop
1135 l_user_name_employee_id := user_emp_names.employee_id;
1136 l_user_name_emp_first_name := user_emp_names.first_name;
1137 l_user_name_emp_last_name := user_emp_names.last_name;
1138 l_user_name_emp_middle_names := user_emp_names.middle_names;
1139 exit;
1140 end loop;
1141 end if;
1142
1143 -- If action_taken is null then ,derive the action_taken
1144
1145 hr_utility.set_location('passed action taken ' ||p_1_action_taken,1);
1146 l_action_taken := p_1_action_taken;
1147 if l_action_taken is null then
1148 if nvl(p_authorized_by_person_id,hr_api.g_number) <>
1149 nvl(ghr_par_shd.g_old_rec.authorized_by_person_id,hr_api.g_number) then
1150 l_action_taken := 'AUTHORIZED';
1151 elsif nvl(p_requested_by_person_id,hr_api.g_number) <>
1152 nvl(ghr_par_shd.g_old_rec.requested_by_person_id,hr_api.g_number) then
1153 l_action_taken := 'REQUESTED';
1154 else
1155 hr_utility.set_location('Bef c_history_exists ' ||l_pa_request_id,2);
1156 for history_exists in C_history_exists loop
1157 l_exists := true;
1158 exit;
1159 end loop;
1160 if l_exists = true then
1161 l_action_taken := 'NO_ACTION';
1162 else
1163 l_action_taken := 'INITIATED';
1164 end if;
1165 end if;
1166 end if;
1167 /***dk***/
1168 -- added END_ROUTING to the following if stmt.
1169 if l_action_taken not in('NOT_ROUTED','INITIATED','REQUESTED','AUTHORIZED',
1170 'NO_ACTION','REVIEWED','CANCELED','UPDATE_HR','UPDATE_HR_COMPLETE', 'END_ROUTING')
1171 then
1172 hr_utility.set_message(8301,'GHR_38110_INVALID_ACTION_TAKEN');
1173 hr_utility.raise_error;
1174 end if;
1175
1176
1177 ghr_sf52_api.get_par_status
1178 (p_effective_date => p_effective_date,
1179 p_approval_date => l_approval_date,
1180 p_requested_by_person_id => p_requested_by_person_id,
1181 p_authorized_by_person_id => p_authorized_by_person_id,
1182 p_action_taken => p_1_action_taken,
1183 --8279908
1184 p_pa_request_id => l_pa_request_id,
1185 p_status => l_status
1186 );
1187
1188 ghr_par_upd.upd
1189 (p_pa_request_id => l_pa_request_id,
1190 p_status => l_status,
1191 p_object_version_number => l_par_object_version_number
1192 );
1193 p_par_object_version_number := l_par_object_version_number;
1194
1195 -- to check if there is any routing information, if required.
1196 /***dk***/
1197 -- added END_ROUTING to the following if stmt.
1198 hr_utility.set_location('check ' ||l_action_taken,3);
1199 hr_utility.set_location('p_2_user_name_routed_to ' ||p_2_user_name_routed_to,4);
1200 hr_utility.set_location('p_2_groupbox_id ' ||p_2_groupbox_id,5);
1201 hr_utility.set_location('p_2_routing_list_id ' ||p_2_routing_list_id,6);
1202 if l_action_taken not in ('CANCELED','UPDATE_HR','UPDATE_HR_COMPLETE','NOT_ROUTED','END_ROUTING') then
1203 if p_2_user_name_routed_to is null and
1204 p_2_groupbox_id is null and
1205 p_2_routing_list_id is null then
1206 hr_utility.set_message(8301,'GHR_38115_ROUT_INFO_REQD');
1207 hr_utility.raise_error;
1208 end if;
1209 end if;
1210
1211 -- write the first record into the routing history (actions done by the user)
1212 if nvl(l_action_taken,hr_api.g_varchar2) not in
1213 ('CANCELED','UPDATE_HR_COMPLETE') then
1214 ----If (p_mass_action_id is not null and nvl(p_rpa_type,hr_api.g_varchar2) <> 'TA') or (p_mass_action_id is null) then
1215 -- Comment above line because irrespective of p_mass_action_id value the if condn should be true (AVR)
1216
1217 If nvl(p_rpa_type,hr_api.g_varchar2) <> 'TA' then
1218 if l_action_taken = 'UPDATE_HR' and trunc(p_effective_date) > trunc(sysdate) then
1219 l_action_taken := 'FUTURE_ACTION';
1220 End if;
1221
1222 ghr_prh_ins.ins
1223 (
1224 p_pa_routing_history_id => p_1_pa_routing_history_id,
1225 p_pa_request_id => l_pa_request_id,
1226 p_attachment_modified_flag => nvl(p_1_attachment_modified_flag,'N') ,
1227 p_initiator_flag => nvl(l_initiator_flag,'N'),
1228 p_approver_flag => nvl(l_approver_flag,'N'),
1229 p_reviewer_flag => nvl(l_reviewer_flag,'N') ,
1230 p_requester_flag => nvl(l_requester_flag,'N') ,
1231 p_authorizer_flag => nvl(l_authorizer_flag,'N'),
1232 p_personnelist_flag => nvl(l_personnelist_flag,'N'),
1233 p_approved_flag => nvl(p_1_approved_flag,'N'),
1234 p_user_name => p_1_user_name_acted_on,
1235 p_user_name_employee_id => l_user_name_employee_id,
1236 p_user_name_emp_first_name => l_user_name_emp_first_name,
1237 p_user_name_emp_last_name => l_user_name_emp_last_name ,
1238 p_user_name_emp_middle_names =>l_user_name_emp_middle_names,
1239 p_notepad => p_notepad,
1240 p_action_taken => l_action_taken,
1241 p_noa_family_code => p_noa_family_code,
1242 p_nature_of_action_id => p_first_noa_id,
1243 p_second_nature_of_action_id => p_second_noa_id,
1244 p_approval_Status => p_1_approval_status,
1245 p_object_version_number => p_1_prh_object_version_number
1246 -- p_validate => false
1247 );
1248
1249
1250 l_2_routing_seq_number := p_2_routing_seq_number;
1251 l_2_groupbox_id := p_2_groupbox_id;
1252 l_2_user_name := p_2_user_name_routed_to;
1253
1254 -- derive the next sequence number for the speicific routing list if seq. number is not passed in
1255 if p_2_routing_list_id is not null and p_2_routing_seq_number is null then
1256
1257 for rout_seq_numb in C_seq_number loop
1258 l_2_routing_seq_number := rout_seq_numb.seq_number;
1259 l_2_groupbox_id := rout_seq_numb.groupbox_id;
1260 l_2_user_name := rout_seq_numb.user_name;
1261 exit;
1262 end loop;
1263
1264 if l_2_routing_seq_number is null then
1265 hr_utility.set_message(8301,'GHR_38114_NO_MORE_SEQ_NUMBER' );
1266 hr_utility.raise_error;
1267 end if;
1268
1269 end if;
1270
1271
1272 -- check for open events before attempting to route / Update HR
1273 ghr_sf52_api.check_for_open_events
1274 (
1275 p_pa_request_id => l_pa_request_id,
1276 p_message => l_message,
1277 p_action_taken => l_action_taken,
1278 p_user_name_acted_on => p_1_user_name_acted_on,
1279 p_user_name_routed_to => l_2_user_name,
1280 p_groupbox_routed_to => l_2_groupbox_id
1281 );
1282
1283 -- call events user hook
1284 ghr_agency_check.open_events_check
1285 (p_pa_request_id => l_pa_request_id,
1286 p_message_set => l_message
1287 );
1288
1289 if l_message then
1290 hr_utility.set_message(8301,'GHR_38592_OPEN_EVENTS_EXIST');
1291 hr_utility.raise_error;
1292 end if;
1293
1294 --Insert 2nd record into routing_history for routing details
1295 -- (exception when routing_status = 'NOT_ROUTED' or 'UPDATE_HR','FUTURE_ACTION','END_ROUTING')
1296 /***dk***/
1297 -- added END_ROUTING to the following if stmt.
1298 if nvl(l_action_taken,hr_api.g_varchar2) not in ('NOT_ROUTED','UPDATE_HR','FUTURE_ACTION','END_ROUTING') then
1299
1300 ghr_prh_ins.ins
1301 (p_pa_routing_history_id => p_2_pa_routing_history_id,
1302 p_pa_request_id => l_pa_request_id,
1303 p_attachment_modified_flag => 'N',
1304 p_initiator_flag => 'N',
1305 p_approver_flag => 'N',
1306 p_reviewer_flag => 'N',
1307 p_requester_flag => 'N',
1308 p_authorizer_flag => 'N',
1309 p_personnelist_flag => 'N',
1310 p_approved_flag => 'N',
1311 p_user_name => l_2_user_name,
1312 p_groupbox_id => l_2_groupbox_id,
1313 p_routing_list_id => p_2_routing_list_id,
1314 p_routing_seq_number => l_2_routing_seq_number,
1315 p_noa_family_code => p_noa_family_code,
1316 p_nature_of_action_id => p_first_noa_id,
1317 p_second_nature_of_action_id => p_second_noa_id,
1318 p_object_version_number => p_2_prh_object_version_number
1319 -- p_validate => false
1320 );
1321
1322 -- Deriving the groupbox name to be passed to workflow call
1323 if l_2_groupbox_id is not null then
1324 for groupbox_name in c_groupbox_name loop
1325 l_forward_to_name := groupbox_name.name;
1326 end loop;
1327 else
1328 l_forward_to_name := l_2_user_name;
1329 end if;
1330 end if;
1331 -- call update_hr if l_action_taken = 'UPDATE_HR'or 'FUTURE_ACTION'
1332 if l_action_taken in ('UPDATE_HR','FUTURE_ACTION','END_ROUTING') then
1333 -- call update_hr with the p_pa_request_rec data
1334
1335 l_rec.pa_request_id := l_pa_request_id;
1336 l_rec.noa_family_code := p_noa_family_code;
1337 l_rec.routing_group_id := p_routing_group_id;
1338 l_rec.proposed_effective_asap_flag := p_proposed_effective_asap_flag;
1339 l_rec.academic_discipline := p_academic_discipline;
1340 l_rec.additional_info_person_id := p_additional_info_person_id;
1341 l_rec.additional_info_tel_number := p_additional_info_tel_number;
1342 l_rec.altered_pa_request_id := p_altered_pa_request_id;
1343 l_rec.annuitant_indicator := p_annuitant_indicator;
1344 l_rec.annuitant_indicator_desc := p_annuitant_indicator_desc;
1345 l_rec.appropriation_code1 := p_appropriation_code1;
1346 l_rec.appropriation_code2 := p_appropriation_code2;
1347 l_rec.approval_date := l_approval_date;
1348 l_rec.approving_official_full_name := l_approving_official_full_name;
1349 l_rec.approving_official_work_title := l_approving_official_work_titl;
1350 l_rec.authorized_by_person_id := p_authorized_by_person_id;
1351 l_rec.authorized_by_title := p_authorized_by_title;
1352 l_rec.award_amount := p_award_amount;
1353 l_rec.award_uom := p_award_uom;
1354 l_rec.bargaining_unit_status := p_bargaining_unit_status;
1355 l_rec.citizenship := p_citizenship;
1356 l_rec.concurrence_date := p_concurrence_date;
1357 l_rec.custom_pay_calc_flag := p_custom_pay_calc_flag;
1358 l_rec.duty_station_code := p_duty_station_code;
1359 l_rec.duty_station_desc := p_duty_station_desc;
1360 l_rec.duty_station_id := p_duty_station_id;
1361 l_rec.duty_station_location_id := p_duty_station_location_id;
1362 l_rec.education_level := p_education_level;
1363 l_rec.effective_date := p_effective_date;
1364 l_rec.employee_assignment_id := p_employee_assignment_id;
1365 l_rec.employee_date_of_birth := p_employee_date_of_birth;
1366 l_rec.employee_first_name := p_employee_first_name;
1367 l_rec.employee_last_name := p_employee_last_name;
1368 l_rec.employee_middle_names := p_employee_middle_names;
1369 l_rec.employee_national_identifier := p_employee_national_identifier;
1370 l_rec.fegli := p_fegli;
1371 l_rec.fegli_desc := p_fegli_desc;
1372 l_rec.first_action_la_code1 := p_first_action_la_code1;
1373 l_rec.first_action_la_code2 := p_first_action_la_code2;
1374 l_rec.first_action_la_desc1 := p_first_action_la_desc1;
1375 l_rec.first_action_la_desc2 := p_first_action_la_desc2;
1376 l_rec.first_noa_cancel_or_correct := p_first_noa_cancel_or_correct;
1377 l_rec.first_noa_code := p_first_noa_code;
1378 l_rec.first_noa_desc := p_first_noa_desc;
1379 l_rec.first_noa_id := p_first_noa_id;
1380 l_rec.first_noa_pa_request_id := p_first_noa_pa_request_id;
1381 l_rec.flsa_category := p_flsa_category;
1382 l_rec.forwarding_address_line1 := p_forwarding_address_line1;
1383 l_rec.forwarding_address_line2 := p_forwarding_address_line2;
1384 l_rec.forwarding_address_line3 := p_forwarding_address_line3;
1385 l_rec.forwarding_country := p_forwarding_country;
1386 l_rec.forwarding_country_short_name := p_forwarding_country_short_nam;
1387 l_rec.forwarding_postal_code := p_forwarding_postal_code;
1388 l_rec.forwarding_region_2 := p_forwarding_region_2;
1389 l_rec.forwarding_town_or_city := p_forwarding_town_or_city;
1390 l_rec.from_adj_basic_pay := p_from_adj_basic_pay;
1391 l_rec.from_basic_pay := p_from_basic_pay;
1392 l_rec.from_grade_or_level := p_from_grade_or_level;
1393 l_rec.from_locality_adj := p_from_locality_adj;
1394 l_rec.from_occ_code := p_from_occ_code;
1395 l_rec.from_other_pay_amount := p_from_other_pay_amount;
1396 l_rec.from_pay_basis := p_from_pay_basis;
1397 l_rec.from_pay_plan := p_from_pay_plan;
1398 -- FWFA Changes Bug#4444609
1399 l_rec.input_pay_rate_determinant := p_input_pay_rate_determinant;
1400 l_rec.from_pay_table_identifier := p_from_pay_table_identifier;
1401 -- FWFA Changes
1402 l_rec.from_position_id := p_from_position_id;
1403 l_rec.from_position_org_line1 := p_from_position_org_line1;
1404 l_rec.from_position_org_line2 := p_from_position_org_line2;
1405 l_rec.from_position_org_line3 := p_from_position_org_line3;
1406 l_rec.from_position_org_line4 := p_from_position_org_line4;
1407 l_rec.from_position_org_line5 := p_from_position_org_line5;
1408 l_rec.from_position_org_line6 := p_from_position_org_line6;
1409 l_rec.from_position_number := p_from_position_number;
1410 l_rec.from_position_seq_no := p_from_position_seq_no;
1411 l_rec.from_position_title := p_from_position_title;
1412 l_rec.from_step_or_rate := p_from_step_or_rate;
1413 l_rec.from_total_salary := p_from_total_salary;
1414 l_rec.functional_class := p_functional_class;
1415 l_rec.notepad := p_notepad;
1416 l_rec.part_time_hours := p_part_time_hours;
1417 l_rec.pay_rate_determinant := p_pay_rate_determinant;
1418 l_rec.person_id := p_person_id;
1419 l_rec.position_occupied := p_position_occupied;
1420 l_rec.proposed_effective_date := p_proposed_effective_date;
1421 l_rec.requested_by_person_id := p_requested_by_person_id;
1422 l_rec.requested_by_title := p_requested_by_title;
1423 l_rec.requested_date := p_requested_date;
1424 l_rec.requesting_office_remarks_desc := p_requesting_office_remarks_de;
1425 l_rec.requesting_office_remarks_flag := p_requesting_office_remarks_fl;
1426 l_rec.request_number := p_request_number;
1427 l_rec.resign_and_retire_reason_desc := p_resign_and_retire_reason_des;
1428 l_rec.retirement_plan := p_retirement_plan;
1429 l_rec.retirement_plan_desc := p_retirement_plan_desc;
1430 l_rec.second_action_la_code1 := p_second_action_la_code1;
1431 l_rec.second_action_la_code2 := p_second_action_la_code2;
1432 l_rec.second_action_la_desc1 := p_second_action_la_desc1;
1433 l_rec.second_action_la_desc2 := p_second_action_la_desc2;
1434 l_rec.second_noa_cancel_or_correct := p_second_noa_cancel_or_correct;
1435 l_rec.second_noa_code := p_second_noa_code;
1436 l_rec.second_noa_desc := p_second_noa_desc;
1437 l_rec.second_noa_id := p_second_noa_id;
1438 l_rec.second_noa_pa_request_id := p_second_noa_pa_request_id;
1439 l_rec.service_comp_date := p_service_comp_date;
1440 l_rec.status := l_status;
1441 l_rec.supervisory_status := p_supervisory_status;
1442 l_rec.tenure := p_tenure;
1443 l_rec.to_adj_basic_pay := p_to_adj_basic_pay;
1444 l_rec.to_basic_pay := p_to_basic_pay;
1445 l_rec.to_grade_id := p_to_grade_id;
1446 l_rec.to_grade_or_level := p_to_grade_or_level;
1447 l_rec.to_job_id := p_to_job_id;
1448 l_rec.to_locality_adj := p_to_locality_adj;
1449 l_rec.to_occ_code := p_to_occ_code;
1450 l_rec.to_organization_id := p_to_organization_id;
1451 l_rec.to_other_pay_amount := p_to_other_pay_amount;
1452 l_rec.to_au_overtime := p_to_au_overtime;
1453 l_rec.to_auo_premium_pay_indicator := p_to_auo_premium_pay_indicator;
1454 l_rec.to_availability_pay := p_to_availability_pay;
1455 l_rec.to_ap_premium_pay_indicator := p_to_ap_premium_pay_indicator;
1456 l_rec.to_retention_allowance := p_to_retention_allowance;
1457 l_rec.to_supervisory_differential := p_to_supervisory_differential;
1458 l_rec.to_staffing_differential := p_to_staffing_differential;
1459 l_rec.to_pay_basis := p_to_pay_basis;
1460 l_rec.to_pay_plan := p_to_pay_plan;
1461 -- FWFA Changes Bug#4444609
1462 l_rec.to_pay_table_identifier := p_to_pay_table_identifier;
1463 -- FWFA Changes
1464 l_rec.to_position_id := p_to_position_id;
1465 l_rec.to_position_org_line1 := p_to_position_org_line1;
1466 l_rec.to_position_org_line2 := p_to_position_org_line2;
1467 l_rec.to_position_org_line3 := p_to_position_org_line3;
1468 l_rec.to_position_org_line4 := p_to_position_org_line4;
1469 l_rec.to_position_org_line5 := p_to_position_org_line5;
1470 l_rec.to_position_org_line6 := p_to_position_org_line6;
1471 l_rec.to_position_number := p_to_position_number;
1472 l_rec.to_position_seq_no := p_to_position_seq_no;
1473 l_rec.to_position_title := p_to_position_title;
1474 l_rec.to_step_or_rate := p_to_step_or_rate;
1475 l_rec.to_total_salary := p_to_total_salary;
1476 l_rec.veterans_preference := p_veterans_preference;
1477 l_rec.veterans_pref_for_rif := p_veterans_pref_for_rif;
1478 l_rec.veterans_status := p_veterans_status;
1479 l_rec.work_schedule := p_work_schedule;
1480 l_rec.work_schedule_desc := p_work_schedule_desc;
1481 l_rec.year_degree_attained := p_year_degree_attained;
1482 l_rec.first_noa_information1 := p_first_noa_information1;
1483 l_rec.first_noa_information2 := p_first_noa_information2;
1484 l_rec.first_noa_information3 := p_first_noa_information3;
1485 l_rec.first_noa_information4 := p_first_noa_information4;
1486 l_rec.first_noa_information5 := p_first_noa_information5;
1487 l_rec.second_lac1_information1 := p_second_lac1_information1;
1488 l_rec.second_lac1_information2 := p_second_lac1_information2;
1489 l_rec.second_lac1_information3 := p_second_lac1_information3;
1490 l_rec.second_lac1_information4 := p_second_lac1_information4;
1491 l_rec.second_lac1_information5 := p_second_lac1_information5;
1492 l_rec.second_lac2_information1 := p_second_lac2_information1;
1493 l_rec.second_lac2_information2 := p_second_lac2_information2;
1494 l_rec.second_lac2_information3 := p_second_lac2_information3;
1495 l_rec.second_lac2_information4 := p_second_lac2_information4;
1496 l_rec.second_lac2_information5 := p_second_lac2_information5;
1497 l_rec.second_noa_information1 := p_second_noa_information1;
1498 l_rec.second_noa_information2 := p_second_noa_information2;
1499 l_rec.second_noa_information3 := p_second_noa_information3;
1500 l_rec.second_noa_information4 := p_second_noa_information4;
1501 l_rec.second_noa_information5 := p_second_noa_information5;
1502 l_rec.first_lac1_information1 := p_first_lac1_information1;
1503 l_rec.first_lac1_information2 := p_first_lac1_information2;
1504 l_rec.first_lac1_information3 := p_first_lac1_information3;
1505 l_rec.first_lac1_information4 := p_first_lac1_information4;
1506 l_rec.first_lac1_information5 := p_first_lac1_information5;
1507 l_rec.first_lac2_information1 := p_first_lac2_information1;
1508 l_rec.first_lac2_information2 := p_first_lac2_information2;
1509 l_rec.first_lac2_information3 := p_first_lac2_information3;
1510 l_rec.first_lac2_information4 := p_first_lac2_information4;
1511 l_rec.first_lac2_information5 := p_first_lac2_information5;
1512 l_rec.attribute_category := p_attribute_category;
1513 l_rec.attribute1 := p_attribute1;
1514 l_rec.attribute2 := p_attribute2;
1515 l_rec.attribute3 := p_attribute3;
1516 l_rec.attribute4 := p_attribute4;
1517 l_rec.attribute5 := p_attribute5;
1518 l_rec.attribute6 := p_attribute6;
1519 l_rec.attribute7 := p_attribute7;
1520 l_rec.attribute8 := p_attribute8;
1521 l_rec.attribute9 := p_attribute9;
1522 l_rec.attribute10 := p_attribute10;
1523 l_rec.attribute11 := p_attribute11;
1524 l_rec.attribute12 := p_attribute12;
1525 l_rec.attribute13 := p_attribute13;
1526 l_rec.attribute14 := p_attribute14;
1527 l_rec.attribute15 := p_attribute15;
1528 l_rec.attribute16 := p_attribute16;
1529 l_rec.attribute17 := p_attribute17;
1530 l_rec.attribute18 := p_attribute18;
1531 l_rec.attribute19 := p_attribute19;
1532 l_rec.attribute20 := p_attribute20;
1533 l_rec.object_version_number := l_par_object_version_number;
1534 l_rec.to_retention_allow_percentage := p_to_retention_allow_percentag;
1535 l_rec.to_supervisory_diff_percentage := p_to_supervisory_diff_percenta;
1536 l_rec.to_staffing_diff_percentage := p_to_staffing_diff_percentage;
1537 l_rec.award_percentage := p_award_percentage;
1538 l_rec.rpa_type := p_rpa_type;
1539 l_rec.mass_action_id := p_mass_action_id;
1540 l_rec.mass_action_eligible_flag := p_mass_action_eligible_flag;
1541 l_rec.mass_action_select_flag := p_mass_action_select_flag;
1542 l_rec.mass_action_comments := p_mass_action_comments;
1543 -- Bug#4486823 RRR Changes
1544 l_rec.pa_incentive_payment_option := p_payment_option;
1545 l_rec.award_salary := p_award_salary;
1546 -- Bug#4486823 RRR Changes
1547
1548
1549
1550 IF l_rec.effective_date is null THEN
1551 hr_utility.set_message(8301,'GHR_38185_EFF_DATE_REQUIRED');
1552 ghr_upd_hr_validation.form_item_name := 'PAR.EFFECTIVE_DATE';
1553 hr_utility.raise_error;
1554 END IF;
1555 if (l_action_taken <> 'END_ROUTING') then
1556 ghr_process_sf52.process_sf52
1557 (p_sf52_data => l_rec
1558 ,p_capped_other_pay => p_capped_other_pay
1559 );
1560 end if;
1561 --Begin Bug# 8653508,9662954
1562 IF p_first_noa_code IN ('730','731','732','930','931','932') OR
1563 p_second_noa_code IN ('730','731','732','930','931','932') THEN
1564 ghr_sf52_api.update_detail_remarks( p_effective_date => l_effective_date,
1565 p_pa_request_id => l_pa_request_id);
1566 END IF;
1567 --End Bug# 8653508,9662954
1568 ghr_sf52_post_update.get_notification_details
1569 (p_pa_request_id => l_pa_request_id,
1570 p_effective_date => p_effective_date,
1571 -- p_object_version_number => p_imm_pa_request_rec.object_version_number,
1572 p_from_position_id => l_rec.from_position_id,
1573 p_to_position_id => l_rec.to_position_id,
1574 p_agency_code => l_rec.agency_code,
1575 p_from_agency_code => l_rec.from_agency_code,
1576 p_from_agency_desc => l_rec.from_agency_desc,
1577 p_from_office_symbol => l_rec.from_office_symbol,
1578 p_personnel_office_id => l_rec.personnel_office_id,
1579 p_employee_dept_or_agency => l_rec.employee_dept_or_agency,
1580 p_to_office_symbol => l_rec.to_office_symbol
1581 );
1582 for ovn_rec in c_ovn loop
1583 l_rec.object_version_number := ovn_rec.object_version_number;
1584 end loop;
1585 hr_utility.set_location('to pos id is '|| l_rec.to_position_id,1);
1586 hr_utility.set_location('first noa code is '|| l_rec.first_noa_code,1);
1587
1588 IF nvl(l_rec.first_noa_code,'9999') <> '002' THEN
1589 ghr_par_upd.upd
1590 (p_pa_request_id => l_pa_request_id,
1591 p_object_version_number => l_rec.object_version_number,
1592 p_from_position_id => l_rec.from_position_id,
1593 p_to_position_id => l_rec.to_position_id,
1594 p_agency_code => l_rec.agency_code,
1595 p_from_agency_code => l_rec.from_agency_code,
1596 p_from_agency_desc => l_rec.from_agency_desc,
1597 p_from_office_symbol => l_rec.from_office_symbol,
1598 p_personnel_office_id => l_rec.personnel_office_id,
1599 p_employee_dept_or_agency => l_rec.employee_dept_or_agency,
1600 p_to_office_symbol => l_rec.to_office_symbol
1601 );
1602 ELSE
1603 ghr_par_upd.upd
1604 (p_pa_request_id => l_pa_request_id,
1605 p_object_version_number => l_rec.object_version_number,
1606 p_from_position_id => l_rec.from_position_id,
1607 p_agency_code => l_rec.agency_code,
1608 p_from_agency_code => l_rec.from_agency_code,
1609 p_from_agency_desc => l_rec.from_agency_desc,
1610 p_from_office_symbol => l_rec.from_office_symbol,
1611 p_personnel_office_id => l_rec.personnel_office_id,
1612 p_employee_dept_or_agency => l_rec.employee_dept_or_agency,
1613 p_to_office_symbol => l_rec.to_office_symbol
1614 );
1615 END IF;
1616
1617 IF (l_action_taken = 'END_ROUTING') THEN
1618 ghr_agency_update.ghr_agency_upd(
1619 p_pa_request_rec => l_rec,
1620 p_asg_sf52 => l_asg_sf52,
1621 p_asg_non_sf52 => l_asg_non_sf52,
1622 p_asg_nte_dates => l_asg_nte_dates,
1623 p_per_sf52 => l_per_sf52,
1624 p_per_group1 => l_per_group1,
1625 p_per_group2 => l_per_group2,
1626 p_per_scd_info => l_per_scd_info,
1627 p_per_retained_grade => l_per_retained_grade,
1628 p_per_probations => l_per_probations,
1629 p_per_sep_retire => l_per_sep_retire,
1630 p_per_security => l_per_security,
1631 p_per_conversions => l_per_conversions,
1632 p_per_uniformed_services=> l_per_uniformed_services,
1633 p_pos_oblig => l_pos_oblig,
1634 p_pos_grp2 => l_pos_grp2,
1635 p_pos_grp1 => l_pos_grp1,
1636 p_pos_valid_grade => l_pos_valid_grade,
1637 p_pos_car_prog => l_pos_car_prog,
1638 p_loc_info => l_loc_info,
1639 p_wgi => l_wgi,
1640 p_recruitment_bonus => l_recruitment_bonus,
1641 p_relocation_bonus => l_relocation_bonus,
1642 p_sf52_from_data => l_sf52_from_data,
1643 p_personal_info => l_personal_info,
1644 p_gov_awards_type => l_gov_awards_type,
1645 p_perf_appraisal_type => l_perf_appraisal_type,
1646 p_payroll_type => l_payroll_type,
1647 p_conduct_perf_type => l_conduct_perf_type,
1648 p_agency_sf52 => l_agency_sf52,
1649 p_agency_code => l_agency_code,
1650 p_entitlement => l_imm_entitlement,
1651 p_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay,
1652 p_edp_pay => l_imm_edp_pay,
1653 p_hazard_pay => l_imm_hazard_pay,
1654 p_health_benefits => l_imm_health_benefits,
1655 p_danger_pay => l_imm_danger_pay,
1656 p_imminent_danger_pay => l_imm_imminent_danger_pay,
1657 p_living_quarters_allow => l_imm_living_quarters_allow,
1658 p_post_diff_amt => l_imm_post_diff_amt,
1659 p_post_diff_percent => l_imm_post_diff_percent,
1660 p_sep_maintenance_allow => l_imm_sep_maintenance_allow,
1661 p_supplemental_post_allow => l_imm_supplemental_post_allow,
1662 p_temp_lodge_allow => l_imm_temp_lodge_allow,
1663 p_premium_pay => l_imm_premium_pay,
1664 p_retirement_annuity => l_imm_retirement_annuity,
1665 p_severance_pay => l_imm_severance_pay,
1666 p_thrift_saving_plan => l_imm_thrift_saving_plan,
1667 p_retention_allow_review => l_imm_retention_allow_review,
1668 p_health_ben_pre_tax => l_imm_health_ben_pre_tax,
1669 p_per_benefit_info => l_imm_per_benefit_info,
1670 p_imm_retirement_info => l_imm_retirement_info, --Bug# 7131104
1671 --Begin Bug# 10181661
1672 p_asg_detail_info => l_imm_asg_detail_info,
1673 p_ipa_detail_ben_cont_info => l_imm_ipa_benefits_cont
1674 --End Bug# 10181661
1675
1676 );
1677 ghr_sf52_api.end_sf52( p_pa_request_id => p_pa_request_id,
1678 p_action_taken => 'ENDED',
1679 p_par_object_version_number => l_rec.object_version_number);
1680 end if;
1681 End if;
1682 if (l_action_taken not in ('UPDATE_HR','END_ROUTING')) then
1683 -- call workflow
1684 ghr_api.call_workflow
1685 (p_pa_request_id => l_pa_request_id
1686 ,p_action_taken => l_action_taken
1687 );
1688 end if;
1689 End if; -- Do not create routing history row for Mass Award Template
1690 Else
1691 hr_utility.set_message(8301,'GHR_38112_INVALID_API');
1692 hr_utility.raise_error;
1693 End if;
1694
1695 If p_print_sf50_flag = 'Y' then
1696 -- Make sure that it only is valid while update_hr
1697 If l_action_taken <> 'UPDATE_HR' then
1698 hr_utility.set_message(8301,'GHR_38399_52_NOT_PROCESSED');
1699 hr_utility.raise_error;
1700 End if;
1701 --Bug#3757201 Added p_back_page parameter
1702 submit_request_to_print_50
1703 (p_printer_name => p_printer_name,
1704 p_pa_request_id => l_pa_request_id,
1705 p_effective_date => p_effective_date,
1706 p_user_name => p_1_user_name_acted_on,
1707 p_back_page => p_print_back_page
1708 );
1709
1710 End if;
1711 --
1712 -- Call After Process User Hook
1713 --
1714 begin
1715 ghr_sf52_bk1.create_sf52_a (
1716 p_noa_family_code => p_noa_family_code,
1717 p_pa_request_id => l_pa_request_id,
1718 p_routing_group_id => p_routing_group_id,
1719 p_proposed_effective_asap_flag => p_proposed_effective_asap_flag,
1720 p_academic_discipline => p_academic_discipline,
1721 p_additional_info_person_id => p_additional_info_person_id,
1722 p_additional_info_tel_number => p_additional_info_tel_number,
1723 p_altered_pa_request_id => p_altered_pa_request_id,
1724 p_annuitant_indicator => p_annuitant_indicator,
1725 p_annuitant_indicator_desc => p_annuitant_indicator_desc,
1726 p_appropriation_code1 => p_appropriation_code1,
1727 p_appropriation_code2 => p_appropriation_code2,
1728 p_approval_date => l_approval_date,
1729 p_approving_official_full_name => l_approving_official_full_name,
1730 p_approving_official_work_titl => l_approving_official_work_titl,
1731 p_authorized_by_person_id => p_authorized_by_person_id,
1732 p_authorized_by_title => p_authorized_by_title,
1733 p_award_amount => p_award_amount,
1734 p_award_uom => p_award_uom,
1735 p_bargaining_unit_status => p_bargaining_unit_status,
1736 p_citizenship => p_citizenship,
1737 p_concurrence_date => p_concurrence_date,
1738 p_custom_pay_calc_flag => p_custom_pay_calc_flag,
1739 p_duty_station_code => p_duty_station_code,
1740 p_duty_station_desc => p_duty_station_desc,
1741 p_duty_station_location_id => p_duty_station_location_id,
1742 p_duty_station_id => p_duty_station_id,
1743 p_education_level => p_education_level,
1744 p_effective_date => p_effective_date,
1745 p_employee_assignment_id => p_employee_assignment_id,
1746 p_employee_date_of_birth => p_employee_date_of_birth,
1747 p_employee_first_name => p_employee_first_name,
1748 p_employee_last_name => p_employee_last_name,
1749 p_employee_middle_names => p_employee_middle_names,
1750 p_employee_national_identifier => p_employee_national_identifier,
1751 p_fegli => p_fegli,
1752 p_fegli_desc => p_fegli_desc,
1753 p_first_action_la_code1 => p_first_action_la_code1,
1754 p_first_action_la_code2 => p_first_action_la_code2,
1755 p_first_action_la_desc1 => p_first_action_la_desc1,
1756 p_first_action_la_desc2 => p_first_action_la_desc2,
1757 p_first_noa_cancel_or_correct => p_first_noa_cancel_or_correct,
1758 p_first_noa_id => p_first_noa_id,
1759 p_first_noa_code => p_first_noa_code,
1760 p_first_noa_desc => p_first_noa_desc,
1761 p_first_noa_pa_request_id => p_first_noa_pa_request_id,
1762 p_flsa_category => p_flsa_category,
1763 p_forwarding_address_line1 => p_forwarding_address_line1,
1764 p_forwarding_address_line2 => p_forwarding_address_line2,
1765 p_forwarding_address_line3 => p_forwarding_address_line3,
1766 p_forwarding_country => p_forwarding_country,
1767 p_forwarding_country_short_nam => p_forwarding_country_short_nam,
1768 p_forwarding_postal_code => p_forwarding_postal_code,
1769 p_forwarding_region_2 => p_forwarding_region_2,
1770 p_forwarding_town_or_city => p_forwarding_town_or_city ,
1771 p_from_adj_basic_pay => p_from_adj_basic_pay,
1772 p_from_basic_pay => p_from_basic_pay,
1773 p_from_grade_or_level => p_from_grade_or_level,
1774 p_from_locality_adj => p_from_locality_adj,
1775 p_from_occ_code => p_from_occ_code,
1776 p_from_other_pay_amount => p_from_other_pay_amount,
1777 p_from_pay_basis => p_from_pay_basis,
1778 p_from_pay_plan => p_from_pay_plan,
1779 -- FWFA Changes Bug#4444609
1780 -- p_input_pay_rate_determinant => p_input_pay_rate_determinant,
1781 -- p_from_pay_table_identifier => p_from_pay_table_identifier,
1782 -- FWFA Changes
1783 p_from_position_id => p_from_position_id,
1784 p_from_position_org_line1 => p_from_position_org_line1,
1785 p_from_position_org_line2 => p_from_position_org_line2,
1786 p_from_position_org_line3 => p_from_position_org_line3,
1787 p_from_position_org_line4 => p_from_position_org_line4,
1788 p_from_position_org_line5 => p_from_position_org_line5,
1789 p_from_position_org_line6 => p_from_position_org_line6,
1790 p_from_position_number => p_from_position_number,
1791 p_from_position_seq_no => p_from_position_seq_no,
1792 p_from_position_title => p_from_position_title,
1793 p_from_step_or_rate => p_from_step_or_rate,
1794 p_from_total_salary => p_from_total_salary,
1795 p_functional_class => p_functional_class,
1796 p_notepad => p_notepad,
1797 p_part_time_hours => p_part_time_hours,
1798 p_pay_rate_determinant => p_pay_rate_determinant,
1799 p_person_id => p_person_id,
1800 p_position_occupied => p_position_occupied,
1801 p_proposed_effective_date => p_proposed_effective_date,
1802 p_requested_by_person_id => p_requested_by_person_id,
1803 p_requested_by_title => p_requested_by_title,
1804 p_requested_date => p_requested_date,
1805 p_requesting_office_remarks_de => p_requesting_office_remarks_de,
1806 p_requesting_office_remarks_fl => p_requesting_office_remarks_fl,
1807 p_request_number => p_request_number,
1808 p_resign_and_retire_reason_des => p_resign_and_retire_reason_des,
1809 p_retirement_plan => p_retirement_plan,
1810 p_retirement_plan_desc => p_retirement_plan_desc,
1811 p_second_action_la_code1 => p_second_action_la_code1,
1812 p_second_action_la_code2 => p_second_action_la_code2,
1813 p_second_action_la_desc1 => p_second_action_la_desc1,
1814 p_second_action_la_desc2 => p_second_action_la_desc2,
1815 p_second_noa_cancel_or_correct => p_second_noa_cancel_or_correct,
1816 p_second_noa_code => p_second_noa_code,
1817 p_second_noa_desc => p_second_noa_desc,
1818 p_second_noa_id => p_second_noa_id,
1819 p_second_noa_pa_request_id => p_second_noa_pa_request_id,
1820 p_service_comp_date => p_service_comp_date,
1821 p_supervisory_status => p_supervisory_status,
1822 p_tenure => p_tenure,
1823 p_to_adj_basic_pay => p_to_adj_basic_pay,
1824 p_to_basic_pay => p_to_basic_pay,
1825 p_to_grade_id => p_to_grade_id,
1826 p_to_grade_or_level => p_to_grade_or_level,
1827 p_to_job_id => p_to_job_id,
1828 p_to_locality_adj => p_to_locality_adj,
1829 p_to_occ_code => p_to_occ_code,
1830 p_to_organization_id => p_to_organization_id,
1831 p_to_other_pay_amount => p_to_other_pay_amount,
1832 p_to_au_overtime => p_to_au_overtime,
1833 p_to_auo_premium_pay_indicator => p_to_auo_premium_pay_indicator,
1834 p_to_availability_pay => p_to_availability_pay,
1835 p_to_ap_premium_pay_indicator => p_to_ap_premium_pay_indicator,
1836 p_to_retention_allowance => p_to_retention_allowance,
1837 p_to_supervisory_differential => p_to_supervisory_differential,
1838 p_to_staffing_differential => p_to_staffing_differential,
1839 p_to_pay_basis => p_to_pay_basis,
1840 p_to_pay_plan => p_to_pay_plan,
1841 -- FWFA Changes Bug#4444609
1842 -- p_to_pay_table_identifier => p_to_pay_table_identifier,
1843 -- FWFA Changes
1844 p_to_position_id => p_to_position_id,
1845 p_to_position_org_line1 => p_to_position_org_line1,
1846 p_to_position_org_line2 => p_to_position_org_line2,
1847 p_to_position_org_line3 => p_to_position_org_line3,
1848 p_to_position_org_line4 => p_to_position_org_line4,
1849 p_to_position_org_line5 => p_to_position_org_line5,
1850 p_to_position_org_line6 => p_to_position_org_line6,
1851 p_to_position_number => p_to_position_number,
1852 p_to_position_seq_no => p_to_position_seq_no,
1853 p_to_position_title => p_to_position_title,
1854 p_to_step_or_rate => p_to_step_or_rate,
1855 p_to_total_salary => p_to_total_salary,
1856 p_veterans_pref_for_rif => p_veterans_pref_for_rif,
1857 p_veterans_preference => p_veterans_preference,
1858 p_veterans_status => p_veterans_status,
1859 p_work_schedule => p_work_schedule,
1860 p_work_schedule_desc => p_work_schedule_desc,
1861 p_year_degree_attained => p_year_degree_attained,
1862 p_first_noa_information1 => p_first_noa_information1,
1863 p_first_noa_information2 => p_first_noa_information2,
1864 p_first_noa_information3 => p_first_noa_information3,
1865 p_first_noa_information4 => p_first_noa_information4,
1866 p_first_noa_information5 => p_first_noa_information5,
1867 p_second_lac1_information1 => p_second_lac1_information1,
1868 p_second_lac1_information2 => p_second_lac1_information2,
1869 p_second_lac1_information3 => p_second_lac1_information3,
1870 p_second_lac1_information4 => p_second_lac1_information4,
1871 p_second_lac1_information5 => p_second_lac1_information5,
1872 p_second_lac2_information1 => p_second_lac2_information1,
1873 p_second_lac2_information2 => p_second_lac2_information2,
1874 p_second_lac2_information3 => p_second_lac2_information3,
1875 p_second_lac2_information4 => p_second_lac2_information4,
1876 p_second_lac2_information5 => p_second_lac2_information5,
1877 p_second_noa_information1 => p_second_noa_information1,
1878 p_second_noa_information2 => p_second_noa_information2,
1879 p_second_noa_information3 => p_second_noa_information3,
1880 p_second_noa_information4 => p_second_noa_information4,
1881 p_second_noa_information5 => p_second_noa_information5,
1882 p_first_lac1_information1 => p_first_lac1_information1,
1883 p_first_lac1_information2 => p_first_lac1_information2,
1884 p_first_lac1_information3 => p_first_lac1_information3,
1885 p_first_lac1_information4 => p_first_lac1_information4,
1886 p_first_lac1_information5 => p_first_lac1_information5,
1887 p_first_lac2_information1 => p_first_lac2_information1,
1888 p_first_lac2_information2 => p_first_lac2_information2,
1889 p_first_lac2_information3 => p_first_lac2_information3,
1890 p_first_lac2_information4 => p_first_lac2_information4,
1891 p_first_lac2_information5 => p_first_lac2_information5,
1892 p_attribute_category => p_attribute_category,
1893 p_attribute1 => p_attribute1,
1894 p_attribute2 => p_attribute2,
1895 p_attribute3 => p_attribute3,
1896 p_attribute4 => p_attribute4,
1897 p_attribute5 => p_attribute5,
1898 p_attribute6 => p_attribute6,
1899 p_attribute7 => p_attribute7,
1900 p_attribute8 => p_attribute8,
1901 p_attribute9 => p_attribute9,
1902 p_attribute10 => p_attribute10,
1903 p_attribute11 => p_attribute11,
1904 p_attribute12 => p_attribute12,
1905 p_attribute13 => p_attribute13,
1906 p_attribute14 => p_attribute14,
1907 p_attribute15 => p_attribute15,
1908 p_attribute16 => p_attribute16,
1909 p_attribute17 => p_attribute17,
1910 p_attribute18 => p_attribute18,
1911 p_attribute19 => p_attribute19,
1912 p_attribute20 => p_attribute20,
1913 p_print_sf50_flag => p_print_sf50_flag,
1914 p_printer_name => p_printer_name,
1915 p_1_attachment_modified_flag => p_1_attachment_modified_flag,
1916 p_1_approved_flag => p_1_approved_flag,
1917 p_1_user_name_acted_on => p_1_user_name_acted_on,
1918 p_1_action_taken => p_1_action_taken,
1919 p_1_approval_status => p_1_approval_status,
1920 p_2_user_name_routed_to => p_2_user_name_routed_to,
1921 p_2_groupbox_id => p_2_groupbox_id,
1922 p_2_routing_list_id => p_2_routing_list_id,
1923 p_2_routing_seq_number => p_2_routing_seq_number,
1924 p_capped_other_pay => p_capped_other_pay,
1925 p_par_object_version_number => l_par_object_version_number,
1926 p_1_pa_routing_history_id => p_1_pa_routing_history_id,
1927 p_1_prh_object_version_number => p_1_prh_object_version_number,
1928 p_2_pa_routing_history_id => p_2_pa_routing_history_id,
1929 p_2_prh_object_version_number => p_2_prh_object_version_number,
1930 p_to_retention_allow_percentag => p_to_retention_allow_percentag,
1931 p_to_supervisory_diff_percenta => p_to_supervisory_diff_percenta,
1932 p_to_staffing_diff_percentage => p_to_staffing_diff_percentage,
1933 p_award_percentage => p_award_percentage,
1934 p_rpa_type => p_rpa_type,
1935 p_mass_action_id => p_mass_action_id,
1936 p_mass_action_eligible_flag => p_mass_action_eligible_flag,
1937 p_mass_action_select_flag => p_mass_action_select_flag,
1938 p_mass_action_comments => p_mass_action_comments
1939 );
1940 exception
1941 when hr_api.cannot_find_prog_unit then
1942 hr_api.cannot_find_prog_unit_error
1943 (p_module_name => 'create_sf52',
1944 p_hook_type => 'AP'
1945 );
1946 end;
1947 --
1948 -- End of After Process User Hook call
1949 --
1950 -- When in validation only mode raise the Validate_Enabled exception
1951 --
1952 if p_validate then
1953 raise hr_api.validate_enabled;
1954 end if;
1955
1956 --
1957 -- Set all output arguments
1958 --
1959 p_pa_request_id := l_pa_request_id;
1960
1961 hr_utility.set_location(' Leaving:'||l_proc, 11);
1962 exception
1963 when hr_api.validate_enabled then
1964 -- As the Validate_Enabled exception has been raised
1965 -- we must rollback to the savepoint
1966
1967 ROLLBACK TO create_sf52;
1968
1969 --
1970 -- Only set output warning arguments
1971 -- (Any key or derived arguments must be set to null
1972 -- when validation only mode is being used.)
1973 --
1974 p_pa_request_id := null;
1975 p_par_object_version_number := null;
1976 p_1_pa_routing_history_id := null;
1977 p_1_prh_object_version_number := null;
1978 p_2_pa_routing_history_id := null;
1979 p_2_prh_object_version_number := null;
1980 when others then
1981 rollback to create_sf52;
1982 --
1983 -- Reset IN OUT parameters and set OUT parameters
1984 --
1985 p_pa_request_id := null;
1986 p_par_object_version_number := null;
1987 p_1_pa_routing_history_id := null;
1988 p_1_prh_object_version_number := null;
1989 p_2_pa_routing_history_id := null;
1990 p_2_prh_object_version_number := null;
1991 raise;
1992
1993 hr_utility.set_location(' Leaving:'||l_proc, 12);
1994 end create_sf52;
1995
1996 -- ----------------------------------------------------------------------------
1997 -- |--------------------------< update_sf52>--------------------------|
1998 -- ----------------------------------------------------------------------------
1999 procedure update_sf52
2000 (p_validate in boolean default false,
2001 p_pa_request_id in number,
2002 p_noa_family_code in varchar2 default hr_api.g_varchar2,
2003 p_routing_group_id in number default hr_api.g_number,
2004 p_par_object_version_number in out nocopy number,
2005 p_proposed_effective_asap_flag in varchar2 default hr_api.g_varchar2,
2006 p_academic_discipline in varchar2 default hr_api.g_varchar2,
2007 p_additional_info_person_id in number default hr_api.g_number,
2008 p_additional_info_tel_number in varchar2 default hr_api.g_varchar2,
2009 p_altered_pa_request_id in number default hr_api.g_number,
2010 p_annuitant_indicator in varchar2 default hr_api.g_varchar2,
2011 p_annuitant_indicator_desc in varchar2 default hr_api.g_varchar2,
2012 p_appropriation_code1 in varchar2 default hr_api.g_varchar2,
2013 p_appropriation_code2 in varchar2 default hr_api.g_varchar2,
2014 p_approval_date in date default hr_api.g_date,
2015 p_approving_official_full_name in varchar2 default hr_api.g_varchar2,
2016 p_approving_official_work_titl in varchar2 default hr_api.g_varchar2,
2017 p_authorized_by_person_id in number default hr_api.g_number,
2018 p_authorized_by_title in varchar2 default hr_api.g_varchar2,
2019 p_award_amount in number default hr_api.g_number,
2020 p_award_uom in varchar2 default hr_api.g_varchar2,
2021 p_bargaining_unit_status in varchar2 default hr_api.g_varchar2,
2022 p_citizenship in varchar2 default hr_api.g_varchar2,
2023 p_concurrence_date in date default hr_api.g_date,
2024 p_custom_pay_calc_flag in varchar2 default hr_api.g_varchar2,
2025 p_duty_station_code in varchar2 default hr_api.g_varchar2,
2026 p_duty_station_desc in varchar2 default hr_api.g_varchar2,
2027 p_duty_station_id in number default hr_api.g_number,
2028 p_duty_station_location_id in number default hr_api.g_number,
2029 p_education_level in varchar2 default hr_api.g_varchar2,
2030 p_effective_date in date default hr_api.g_date,
2031 p_employee_assignment_id in number default hr_api.g_number,
2032 p_employee_date_of_birth in date default hr_api.g_date,
2033 p_employee_first_name in varchar2 default hr_api.g_varchar2,
2034 p_employee_last_name in varchar2 default hr_api.g_varchar2,
2035 p_employee_middle_names in varchar2 default hr_api.g_varchar2,
2036 p_employee_national_identifier in varchar2 default hr_api.g_varchar2,
2037 p_fegli in varchar2 default hr_api.g_varchar2,
2038 p_fegli_desc in varchar2 default hr_api.g_varchar2,
2039 p_first_action_la_code1 in varchar2 default hr_api.g_varchar2,
2040 p_first_action_la_code2 in varchar2 default hr_api.g_varchar2,
2041 p_first_action_la_desc1 in varchar2 default hr_api.g_varchar2,
2042 p_first_action_la_desc2 in varchar2 default hr_api.g_varchar2,
2043 p_first_noa_cancel_or_correct in varchar2 default hr_api.g_varchar2,
2044 p_first_noa_code in varchar2 default hr_api.g_varchar2,
2045 p_first_noa_desc in varchar2 default hr_api.g_varchar2,
2046 p_first_noa_id in number default hr_api.g_number,
2047 p_first_noa_pa_request_id in number default hr_api.g_number,
2048 p_flsa_category in varchar2 default hr_api.g_varchar2,
2049 p_forwarding_address_line1 in varchar2 default hr_api.g_varchar2,
2050 p_forwarding_address_line2 in varchar2 default hr_api.g_varchar2,
2051 p_forwarding_address_line3 in varchar2 default hr_api.g_varchar2,
2052 p_forwarding_country in varchar2 default hr_api.g_varchar2,
2053 p_forwarding_country_short_nam in varchar2 default hr_api.g_varchar2,
2054 p_forwarding_postal_code in varchar2 default hr_api.g_varchar2,
2055 p_forwarding_region_2 in varchar2 default hr_api.g_varchar2,
2056 p_forwarding_town_or_city in varchar2 default hr_api.g_varchar2,
2057 p_from_adj_basic_pay in number default hr_api.g_number,
2058 p_from_basic_pay in number default hr_api.g_number,
2059 p_from_grade_or_level in varchar2 default hr_api.g_varchar2,
2060 p_from_locality_adj in number default hr_api.g_number,
2061 p_from_occ_code in varchar2 default hr_api.g_varchar2,
2062 p_from_other_pay_amount in number default hr_api.g_number,
2063 p_from_pay_basis in varchar2 default hr_api.g_varchar2,
2064 p_from_pay_plan in varchar2 default hr_api.g_varchar2,
2065 -- FWFA Changes Bug#4444609
2066 p_input_pay_rate_determinant in varchar2 default hr_api.g_varchar2,
2067 p_from_pay_table_identifier in number default hr_api.g_number,
2068 -- FWFA Changes
2069 p_from_position_id in number default hr_api.g_number,
2070 p_from_position_org_line1 in varchar2 default hr_api.g_varchar2,
2071 p_from_position_org_line2 in varchar2 default hr_api.g_varchar2,
2072 p_from_position_org_line3 in varchar2 default hr_api.g_varchar2,
2073 p_from_position_org_line4 in varchar2 default hr_api.g_varchar2,
2074 p_from_position_org_line5 in varchar2 default hr_api.g_varchar2,
2075 p_from_position_org_line6 in varchar2 default hr_api.g_varchar2,
2076 p_from_position_number in varchar2 default hr_api.g_varchar2,
2077 p_from_position_seq_no in number default hr_api.g_number,
2078 p_from_position_title in varchar2 default hr_api.g_varchar2,
2079 p_from_step_or_rate in varchar2 default hr_api.g_varchar2,
2080 p_from_total_salary in number default hr_api.g_number,
2081 p_functional_class in varchar2 default hr_api.g_varchar2,
2082 p_notepad in varchar2 default hr_api.g_varchar2,
2083 p_part_time_hours in number default hr_api.g_number,
2084 p_pay_rate_determinant in varchar2 default hr_api.g_varchar2,
2085 p_person_id in number default hr_api.g_number,
2086 p_position_occupied in varchar2 default hr_api.g_varchar2,
2087 p_proposed_effective_date in date default hr_api.g_date,
2088 p_requested_by_person_id in number default hr_api.g_number,
2089 p_requested_by_title in varchar2 default hr_api.g_varchar2,
2090 p_requested_date in date default hr_api.g_date,
2091 p_requesting_office_remarks_de in varchar2 default hr_api.g_varchar2,
2092 p_requesting_office_remarks_fl in varchar2 default hr_api.g_varchar2,
2093 p_request_number in varchar2 default hr_api.g_varchar2,
2094 p_resign_and_retire_reason_des in varchar2 default hr_api.g_varchar2,
2095 p_retirement_plan in varchar2 default hr_api.g_varchar2,
2096 p_retirement_plan_desc in varchar2 default hr_api.g_varchar2,
2097 p_second_action_la_code1 in varchar2 default hr_api.g_varchar2,
2098 p_second_action_la_code2 in varchar2 default hr_api.g_varchar2,
2099 p_second_action_la_desc1 in varchar2 default hr_api.g_varchar2,
2100 p_second_action_la_desc2 in varchar2 default hr_api.g_varchar2,
2101 p_second_noa_cancel_or_correct in varchar2 default hr_api.g_varchar2,
2102 p_second_noa_code in varchar2 default hr_api.g_varchar2,
2103 p_second_noa_desc in varchar2 default hr_api.g_varchar2,
2104 p_second_noa_id in number default hr_api.g_number,
2105 p_second_noa_pa_request_id in number default hr_api.g_number,
2106 p_service_comp_date in date default hr_api.g_date,
2107 p_supervisory_status in varchar2 default hr_api.g_varchar2,
2108 p_tenure in varchar2 default hr_api.g_varchar2,
2109 p_to_adj_basic_pay in number default hr_api.g_number,
2110 p_to_basic_pay in number default hr_api.g_number,
2111 p_to_grade_id in number default hr_api.g_number,
2112 p_to_grade_or_level in varchar2 default hr_api.g_varchar2,
2113 p_to_job_id in number default hr_api.g_number,
2114 p_to_locality_adj in number default hr_api.g_number,
2115 p_to_occ_code in varchar2 default hr_api.g_varchar2,
2116 p_to_organization_id in number default hr_api.g_number,
2117 p_to_other_pay_amount in number default hr_api.g_number,
2118 p_to_au_overtime in number default hr_api.g_number,
2119 p_to_auo_premium_pay_indicator in varchar2 default hr_api.g_varchar2,
2120 p_to_availability_pay in number default hr_api.g_number,
2121 p_to_ap_premium_pay_indicator in varchar2 default hr_api.g_varchar2,
2122 p_to_retention_allowance in number default hr_api.g_number,
2123 p_to_supervisory_differential in number default hr_api.g_number,
2124 p_to_staffing_differential in number default hr_api.g_number,
2125 p_to_pay_basis in varchar2 default hr_api.g_varchar2,
2126 p_to_pay_plan in varchar2 default hr_api.g_varchar2,
2127 -- FWFA Changes Bug#4444609
2128 p_to_pay_table_identifier in number default hr_api.g_number,
2129 -- FWFA Changes
2130 p_to_position_id in number default hr_api.g_number,
2131 p_to_position_org_line1 in varchar2 default hr_api.g_varchar2,
2132 p_to_position_org_line2 in varchar2 default hr_api.g_varchar2,
2133 p_to_position_org_line3 in varchar2 default hr_api.g_varchar2,
2134 p_to_position_org_line4 in varchar2 default hr_api.g_varchar2,
2135 p_to_position_org_line5 in varchar2 default hr_api.g_varchar2,
2136 p_to_position_org_line6 in varchar2 default hr_api.g_varchar2,
2137 p_to_position_number in varchar2 default hr_api.g_varchar2,
2138 p_to_position_seq_no in number default hr_api.g_number,
2139 p_to_position_title in varchar2 default hr_api.g_varchar2,
2140 p_to_step_or_rate in varchar2 default hr_api.g_varchar2,
2141 p_to_total_salary in number default hr_api.g_number,
2142 p_veterans_preference in varchar2 default hr_api.g_varchar2,
2143 p_veterans_pref_for_rif in varchar2 default hr_api.g_varchar2,
2144 p_veterans_status in varchar2 default hr_api.g_varchar2,
2145 p_work_schedule in varchar2 default hr_api.g_varchar2,
2146 p_work_schedule_desc in varchar2 default hr_api.g_varchar2,
2147 p_year_degree_attained in number default hr_api.g_number,
2148 p_first_noa_information1 in varchar2 default hr_api.g_varchar2,
2149 p_first_noa_information2 in varchar2 default hr_api.g_varchar2,
2150 p_first_noa_information3 in varchar2 default hr_api.g_varchar2,
2151 p_first_noa_information4 in varchar2 default hr_api.g_varchar2,
2152 p_first_noa_information5 in varchar2 default hr_api.g_varchar2,
2153 p_second_lac1_information1 in varchar2 default hr_api.g_varchar2,
2154 p_second_lac1_information2 in varchar2 default hr_api.g_varchar2,
2155 p_second_lac1_information3 in varchar2 default hr_api.g_varchar2,
2156 p_second_lac1_information4 in varchar2 default hr_api.g_varchar2,
2157 p_second_lac1_information5 in varchar2 default hr_api.g_varchar2,
2158 p_second_lac2_information1 in varchar2 default hr_api.g_varchar2,
2159 p_second_lac2_information2 in varchar2 default hr_api.g_varchar2,
2160 p_second_lac2_information3 in varchar2 default hr_api.g_varchar2,
2161 p_second_lac2_information4 in varchar2 default hr_api.g_varchar2,
2162 p_second_lac2_information5 in varchar2 default hr_api.g_varchar2,
2163 p_second_noa_information1 in varchar2 default hr_api.g_varchar2,
2164 p_second_noa_information2 in varchar2 default hr_api.g_varchar2,
2165 p_second_noa_information3 in varchar2 default hr_api.g_varchar2,
2166 p_second_noa_information4 in varchar2 default hr_api.g_varchar2,
2167 p_second_noa_information5 in varchar2 default hr_api.g_varchar2,
2168 p_first_lac1_information1 in varchar2 default hr_api.g_varchar2,
2169 p_first_lac1_information2 in varchar2 default hr_api.g_varchar2,
2170 p_first_lac1_information3 in varchar2 default hr_api.g_varchar2,
2171 p_first_lac1_information4 in varchar2 default hr_api.g_varchar2,
2172 p_first_lac1_information5 in varchar2 default hr_api.g_varchar2,
2173 p_first_lac2_information1 in varchar2 default hr_api.g_varchar2,
2174 p_first_lac2_information2 in varchar2 default hr_api.g_varchar2,
2175 p_first_lac2_information3 in varchar2 default hr_api.g_varchar2,
2176 p_first_lac2_information4 in varchar2 default hr_api.g_varchar2,
2177 p_first_lac2_information5 in varchar2 default hr_api.g_varchar2,
2178 p_attribute_category in varchar2 default hr_api.g_varchar2,
2179 p_attribute1 in varchar2 default hr_api.g_varchar2,
2180 p_attribute2 in varchar2 default hr_api.g_varchar2,
2181 p_attribute3 in varchar2 default hr_api.g_varchar2,
2182 p_attribute4 in varchar2 default hr_api.g_varchar2,
2183 p_attribute5 in varchar2 default hr_api.g_varchar2,
2184 p_attribute6 in varchar2 default hr_api.g_varchar2,
2185 p_attribute7 in varchar2 default hr_api.g_varchar2,
2186 p_attribute8 in varchar2 default hr_api.g_varchar2,
2187 p_attribute9 in varchar2 default hr_api.g_varchar2,
2188 p_attribute10 in varchar2 default hr_api.g_varchar2,
2189 p_attribute11 in varchar2 default hr_api.g_varchar2,
2190 p_attribute12 in varchar2 default hr_api.g_varchar2,
2191 p_attribute13 in varchar2 default hr_api.g_varchar2,
2192 p_attribute14 in varchar2 default hr_api.g_varchar2,
2193 p_attribute15 in varchar2 default hr_api.g_varchar2,
2194 p_attribute16 in varchar2 default hr_api.g_varchar2,
2195 p_attribute17 in varchar2 default hr_api.g_varchar2,
2196 p_attribute18 in varchar2 default hr_api.g_varchar2,
2197 p_attribute19 in varchar2 default hr_api.g_varchar2,
2198 p_attribute20 in varchar2 default hr_api.g_varchar2,
2199 p_print_sf50_flag in varchar2 default 'N',
2200 p_printer_name in varchar2 default null,
2201 p_print_back_page in varchar2 default 'Y',
2202 p_u_attachment_modified_flag in varchar2 default hr_api.g_varchar2,
2203 p_u_approved_flag in varchar2 default hr_api.g_varchar2,
2204 p_u_user_name_acted_on in varchar2 default hr_api.g_varchar2,
2205 p_u_action_taken in varchar2 default null,
2206 p_u_approval_status in varchar2 default hr_api.g_varchar2,
2207 p_i_user_name_routed_to in varchar2 default null,
2208 p_i_groupbox_id in number default null,
2209 p_i_routing_list_id in number default null,
2210 p_i_routing_seq_number in number default null,
2211 p_capped_other_pay in number default null,
2212 p_to_retention_allow_percentag in number default hr_api.g_number,
2213 p_to_supervisory_diff_percenta in number default hr_api.g_number,
2214 p_to_staffing_diff_percentage in number default hr_api.g_number,
2215 p_award_percentage in number default hr_api.g_number,
2216 p_rpa_type in varchar2 default hr_api.g_varchar2,
2217 p_mass_action_id in number default hr_api.g_number,
2218 p_mass_action_eligible_flag in varchar2 default hr_api.g_varchar2,
2219 p_mass_action_select_flag in varchar2 default hr_api.g_varchar2,
2220 p_mass_action_comments in varchar2 default hr_api.g_varchar2,
2221 -- Bug#4486823 RRR Changes
2222 p_payment_option in varchar2 default null,
2223 p_award_salary in number default hr_api.g_number,
2224 -- Bug#4486823 RRR Changes
2225 p_u_prh_object_version_number out nocopy number,
2226 p_i_pa_routing_history_id out nocopy number,
2227 p_i_prh_object_version_number out nocopy number
2228 )
2229 is
2230 --
2231 -- Declare cursors and local variables
2232 --
2233
2234 l_proc varchar2(72) := g_package||'update_sf52';
2235 l_exists boolean := false;
2236 l_cnt_history number;
2237 l_routing_group_id ghr_pa_requests.routing_group_id%TYPE;
2238 l_par_object_version_number ghr_pa_requests.object_version_number%TYPE;
2239 l_from_cop ghr_pa_requests.from_other_pay_amount%TYPE;
2240 l_object_version_number ghr_pa_requests.object_version_number%TYPE;
2241 l_u_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%TYPE;
2242 l_i_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%TYPE;
2243 l_u_prh_object_version_number ghr_pa_routing_history.object_version_number%TYPE;
2244 l_i_prh_object_version_number ghr_pa_routing_history.object_version_number%TYPE;
2245 l_effective_date date := trunc(nvl(p_effective_date,sysdate));
2246 l_initiator_flag ghr_pa_routing_history.initiator_flag%TYPE;
2247 l_requester_flag ghr_pa_routing_history.requester_flag%TYPE;
2248 l_reviewer_flag ghr_pa_routing_history.reviewer_flag%TYPE;
2249 l_personnelist_flag ghr_pa_routing_history.personnelist_flag%TYPE;
2250 l_authorizer_flag ghr_pa_routing_history.authorizer_flag%TYPE;
2251 l_approver_flag ghr_pa_routing_history.approver_flag%TYPE;
2252 l_user_name_employee_id per_people_f.person_id%TYPE;
2253 l_user_name_emp_first_name per_people_f.first_name%TYPE;
2254 l_user_name_emp_last_name per_people_f.last_name%TYPE;
2255 l_user_name_emp_middle_names per_people_f.middle_names%TYPE;
2256 l_seq_numb ghr_pa_routing_history.routing_seq_number%TYPE;
2257 l_cur_seq_numb ghr_pa_routing_history.routing_seq_number%TYPE;
2258 l_next_seq_numb ghr_pa_routing_history.routing_seq_number%TYPE;
2259 l_next_groupbox_name ghr_groupboxes.name%TYPE;
2260 l_next_groupbox_id ghr_pa_routing_history.groupbox_id%TYPE;
2261 l_next_user_name ghr_pa_routing_history.user_name%TYPE := p_i_user_name_routed_to;
2262 l_action_taken ghr_pa_routing_history.action_taken%TYPE;
2263 l_old_action_taken ghr_pa_routing_history.action_taken%TYPE;
2264 l_forward_to_name ghr_pa_routing_history.user_name%type;
2265 l_rec ghr_pa_requests%rowtype;
2266 -- l_rec ghr_par_shd.g_old_rec%type;
2267 l_rei_rec ghr_pa_request_extra_info%rowtype;
2268 l_part_time_hours ghr_pa_requests.part_time_hours%type;
2269 l_requesting_office_remarks_fl ghr_pa_requests.requesting_office_remarks_flag%type;
2270 l_proposed_effective_asap_flag ghr_pa_requests.proposed_effective_asap_flag%type;
2271 l_veterans_pref_for_rif ghr_pa_requests.veterans_pref_for_rif%type;
2272 l_year_degree_attained ghr_pa_requests.year_degree_attained%type;
2273 l_noa_family_code ghr_pa_requests.noa_family_code%type;
2274 l_last_update_date ghr_pa_requests.last_update_date%type;
2275 l_last_updated_by ghr_pa_requests.last_updated_by%type;
2276 l_last_update_login ghr_pa_requests.last_update_login%type;
2277 l_created_by ghr_pa_requests.created_by%type;
2278 l_creation_date ghr_pa_requests.creation_date%type;
2279 l_flag varchar2(1);
2280 l_information_type ghr_pa_request_info_types.information_type%type;
2281 l_set_print_options_status boolean;
2282 l_request_status number(15);
2283 l_employee_id per_people_f.person_id%type;
2284 l_approving_official_work_titl ghr_pa_requests.approving_official_work_title%type;
2285 l_approving_official_full_name ghr_pa_requests.approving_official_full_name%type;
2286 l_approval_date date;
2287 l_sf50_approval_date date;
2288 l_sf50_approving_ofcl_work_tit ghr_pa_requests.sf50_approving_ofcl_work_title%type;
2289 l_sf50_approving_ofcl_full_nam ghr_pa_requests.sf50_approving_ofcl_full_name%type;
2290 l_status ghr_pa_requests.status%type;
2291 l_message boolean := FALSE;
2292 l_asg_sf52 ghr_api.asg_sf52_type;
2293 l_asg_non_sf52 ghr_api.asg_non_sf52_type;
2294 l_asg_nte_dates ghr_api.asg_nte_dates_type;
2295 l_per_sf52 ghr_api.per_sf52_type;
2296 l_per_group1 ghr_api.per_group1_type;
2297 l_per_group2 ghr_api.per_group2_type;
2298 l_per_scd_info ghr_api.per_scd_info_type;
2299 l_per_retained_grade ghr_api.per_retained_grade_type;
2300 l_per_probations ghr_api.per_probations_type;
2301 l_per_sep_retire ghr_api.per_sep_retire_type;
2302 l_per_security ghr_api.per_security_type;
2303 l_per_conversions ghr_api.per_conversions_type;
2304 l_per_uniformed_services ghr_api.per_uniformed_services_type;
2305 l_pos_oblig ghr_api.pos_oblig_type;
2306 l_pos_grp2 ghr_api.pos_grp2_type;
2307 l_pos_grp1 ghr_api.pos_grp1_type;
2308 l_pos_valid_grade ghr_api.pos_valid_grade_type;
2309 l_pos_car_prog ghr_api.pos_car_prog_type;
2310 l_loc_info ghr_api.loc_info_type;
2311 l_wgi ghr_api.within_grade_increase_type;
2312 l_recruitment_bonus ghr_api.recruitment_bonus_type;
2313 l_relocation_bonus ghr_api.relocation_bonus_type;
2314
2315 --Pradeep
2316 l_mddds_special_pay ghr_api.mddds_special_pay_type;
2317 l_sf52_from_data ghr_api.prior_sf52_data_type;
2318 l_personal_info ghr_api.personal_info_type;
2319 l_gov_awards_type ghr_api.government_awards_type;
2320 l_perf_appraisal_type ghr_api.performance_appraisal_type;
2321 l_payroll_type ghr_api.government_payroll_type;
2322 l_conduct_perf_type ghr_api.conduct_performance_type;
2323 l_agency_sf52 ghr_api.agency_sf52_type;
2324 l_agency_code varchar2(80);
2325 l_imm_entitlement ghr_api.entitlement_type;
2326 l_imm_foreign_lang_prof_pay ghr_api.foreign_lang_prof_pay_type;
2327 l_imm_edp_pay ghr_api.edp_pay_type;
2328 l_imm_hazard_pay ghr_api.hazard_pay_type;
2329 l_imm_health_benefits ghr_api.health_benefits_type;
2330 l_imm_danger_pay ghr_api.danger_pay_type;
2331 l_imm_imminent_danger_pay ghr_api.imminent_danger_pay_type;
2332 l_imm_living_quarters_allow ghr_api.living_quarters_allow_type;
2333 l_imm_post_diff_amt ghr_api.post_diff_amt_type;
2334 l_imm_post_diff_percent ghr_api.post_diff_percent_type;
2335 l_imm_sep_maintenance_allow ghr_api.sep_maintenance_allow_type;
2336 l_imm_supplemental_post_allow ghr_api.supplemental_post_allow_type;
2337 l_imm_temp_lodge_allow ghr_api.temp_lodge_allow_type;
2338 l_imm_premium_pay ghr_api.premium_pay_type;
2339 l_imm_retirement_annuity ghr_api.retirement_annuity_type;
2340 l_imm_severance_pay ghr_api.severance_pay_type;
2341 l_imm_thrift_saving_plan ghr_api.thrift_saving_plan;
2342 l_imm_retention_allow_review ghr_api.retention_allow_review_type;
2343 l_imm_health_ben_pre_tax ghr_api.health_ben_pre_tax_type;
2344 l_rpa_type ghr_pa_requests.rpa_type%type;
2345 l_mass_action_id ghr_pa_requests.mass_action_id%type;
2346 l_imm_per_benefit_info ghr_api.per_benefit_info_type;
2347 l_imm_retirement_info ghr_api.per_retirement_info_type; --Bug# 7131104
2348 --Begin Bug# 10181661
2349 l_imm_asg_detail_info ghr_api.asg_detail_info_type;
2350 l_imm_ipa_benefits_cont ghr_api.per_ipa_ben_cont_info_type;
2351 --End Bug# 10181661
2352
2353
2354 CURSOR c_cnt_history IS
2355 SELECT count(*) cnt
2356 FROM ghr_pa_routing_history prh
2357 WHERE prh.pa_request_id = p_pa_request_id;
2358
2359 CURSOR C_routing_history_id IS
2360 SELECT prh.pa_routing_history_id,
2361 prh.object_version_number
2362 FROM ghr_pa_routing_history prh
2363 WHERE prh.pa_request_id = p_pa_request_id
2364 ORDER by prh.pa_routing_history_id desc;
2365
2366 CURSOR c_routing_group_id IS
2367 SELECT par.routing_group_id
2368 FROM ghr_pa_requests par
2369 WHERE par.pa_request_id = p_pa_request_id;
2370
2371 /* cursor c_names is
2372 select usr.employee_id,
2373 per.first_name,
2374 per.last_name,
2375 per.middle_names
2376 from fnd_user usr,
2377 per_people_f per
2378 where upper(p_u_user_name_acted_on) = upper(usr.user_name)
2379 and per.person_id = usr.employee_id
2380 and l_effective_date
2381 between effective_start_date
2382 and effective_end_date; */
2383 -- Bug 4863608 Perf. Repository changes
2384 -- 8229939 modified to consider sysdate
2385 CURSOR c_names IS
2386 SELECT usr.employee_id,
2387 per.first_name,
2388 per.last_name,
2389 per.middle_names
2390 FROM fnd_user usr,
2391 per_people_f per
2392 WHERE usr.user_name = UPPER(p_u_user_name_acted_on)
2393 AND per.person_id = usr.employee_id
2394 AND trunc(sysdate)
2395 BETWEEN effective_start_date
2396 AND effective_end_date;
2397
2398 CURSOR cur_rout_list_used IS
2399 SELECT prh.routing_seq_number
2400 FROM ghr_pa_routing_history prh
2401 WHERE prh.pa_request_id = p_pa_request_id
2402 AND prh.routing_list_id = p_i_routing_list_id
2403 ORDER BY prh.pa_routing_history_id desc;
2404
2405 CURSOR cur_next_rout_seq IS
2406 SELECT rlm.seq_number,
2407 rlm.groupbox_id,
2408 rlm.user_name
2409 FROM ghr_routing_list_members rlm
2410 WHERE rlm.routing_list_id = p_i_routing_list_id
2411 AND rlm.seq_number > l_cur_seq_numb
2412 ORDER BY rlm.seq_number asc;
2413
2414
2415 CURSOR c_history_exists IS
2416 SELECT action_taken
2417 FROM ghr_pa_routing_history prh
2418 WHERE prh.pa_request_id = p_pa_request_id;
2419
2420 CURSOR c_groupbox_name IS
2421 SELECT gbx.name
2422 FROM ghr_groupboxes gbx
2423 WHERE gbx.groupbox_id = l_next_groupbox_id;
2424
2425 CURSOR c_pa_requests IS
2426 SELECT par.noa_family_code,
2427 par.last_update_date,
2428 par.last_updated_by,
2429 par.last_update_login,
2430 par.created_by,
2431 par.creation_date
2432 FROM ghr_pa_requests par
2433 WHERE pa_request_id = p_pa_request_id;
2434
2435 CURSOR c_rei_rec IS
2436 SELECT pa_request_extra_info_id,
2437 object_version_number
2438 FROM ghr_pa_request_extra_info rei
2439 WHERE rei.pa_request_id = p_pa_request_id
2440 AND rei.information_type = l_information_type;
2441
2442 CURSOR c_ovn IS
2443 SELECT par.object_version_number
2444 FROM ghr_pa_requests par
2445 WHERE par.pa_request_id = p_pa_request_id;
2446
2447 CURSOR c_get_det_for_cop IS
2448 SELECT par.employee_assignment_id,par.effective_date
2449 FROM ghr_pa_requests par
2450 WHERE par.pa_request_id = p_pa_request_id;
2451
2452 --6976674
2453 CURSOR c_dual_cancel
2454 is
2455 select pa_request_id
2456 from ghr_pa_requests
2457 where pa_request_id in (select mass_action_id
2458 from ghr_pa_requests
2459 where pa_request_id = p_pa_request_id
2460 and first_noa_code = '001'
2461 and mass_action_id is not null
2462 and rpa_type = 'DUAL')
2463 and pa_notification_id is not null
2464 and first_noa_code = '001';
2465 --6976674
2466
2467 --8286910
2468 cursor c_dual_sec_corr
2469 is
2470 select 1
2471 from ghr_pa_requests
2472 where pa_request_id = p_pa_request_id
2473 and mass_action_id < pa_request_id
2474 and rpa_type = 'DUAL'
2475 and second_noa_code = (select second_noa_code
2476 from ghr_pa_requests
2477 where pa_request_id = (select min(pa_request_id)
2478 from ghr_pa_requests
2479 where pa_notification_id is not null
2480 connect by pa_request_id = prior altered_pa_request_id
2481 start with pa_request_id = p_pa_request_id));
2482
2483 cursor c_first_corr_npa(p_first_pa_req_id in number)
2484 is
2485 select 1
2486 from ghr_pa_requests
2487 where pa_request_id = p_first_pa_req_id
2488 and pa_notification_id is not null;
2489 --8286910
2490 --Begin Bug# 8510411
2491
2492 l_to_position_id ghr_pa_requests.to_position_id%type;
2493
2494 CURSOR c_to_pos_id IS
2495 SELECT to_position_id FROM ghr_pa_requests
2496 WHERE pa_request_id = p_pa_request_id;
2497 --End Bug# 8510411
2498
2499 --Bug# 6127620
2500 -- l_curr_sess_date fnd_sessions.effective_date%type;
2501 --Begin Bug# 7174094
2502 l_separation_flag BOOLEAN;
2503
2504 cursor c_sec_noa_fmly is
2505 select fams.noa_family_code
2506 from ghr_noa_families noafam,
2507 ghr_families fams
2508 where noafam.nature_of_action_id = p_second_noa_id
2509 and noafam.enabled_flag = 'Y'
2510 and fams.noa_family_code = noafam.noa_family_code
2511 and fams.enabled_flag = 'Y'
2512 and fams.update_hr_flag = 'Y'
2513 and l_effective_date between nvl(noafam.start_date_active,to_date('1951/01/01','RRRR/MM/DD'))
2514 and nvl(noafam.end_date_active,to_date('4712/12/31','RRRR/MM/DD'));
2515 --End Bug# 7174094
2516
2517 begin
2518 hr_utility.set_location('Entering:'|| l_proc, 5);
2519 --
2520 -- Issue a savepoint
2521 --
2522 savepoint update_sf52;
2523 --
2524 -- Call Before Process User Hook
2525 --
2526 begin
2527 ghr_sf52_bk2.update_sf52_b (
2528 p_noa_family_code => p_noa_family_code,
2529 p_pa_request_id => p_pa_request_id,
2530 p_routing_group_id => p_routing_group_id,
2531 p_proposed_effective_asap_flag => p_proposed_effective_asap_flag,
2532 p_academic_discipline => p_academic_discipline,
2533 p_additional_info_person_id => p_additional_info_person_id,
2534 p_additional_info_tel_number => p_additional_info_tel_number,
2535 p_altered_pa_request_id => p_altered_pa_request_id,
2536 p_annuitant_indicator => p_annuitant_indicator,
2537 p_annuitant_indicator_desc => p_annuitant_indicator_desc,
2538 p_appropriation_code1 => p_appropriation_code1,
2539 p_appropriation_code2 => p_appropriation_code2,
2540 p_authorized_by_person_id => p_authorized_by_person_id,
2541 p_authorized_by_title => p_authorized_by_title,
2542 p_award_amount => p_award_amount,
2543 p_award_uom => p_award_uom,
2544 p_bargaining_unit_status => p_bargaining_unit_status,
2545 p_citizenship => p_citizenship,
2546 p_concurrence_date => p_concurrence_date,
2547 p_custom_pay_calc_flag => p_custom_pay_calc_flag,
2548 p_duty_station_code => p_duty_station_code,
2549 p_duty_station_desc => p_duty_station_desc,
2550 p_duty_station_location_id => p_duty_station_location_id,
2551 p_duty_station_id => p_duty_station_id,
2552 p_education_level => p_education_level,
2553 p_effective_date => p_effective_date,
2554 p_employee_assignment_id => p_employee_assignment_id,
2555 p_employee_date_of_birth => p_employee_date_of_birth,
2556 p_employee_first_name => p_employee_first_name,
2557 p_employee_last_name => p_employee_last_name,
2558 p_employee_middle_names => p_employee_middle_names,
2559 p_employee_national_identifier => p_employee_national_identifier,
2560 p_fegli => p_fegli,
2561 p_fegli_desc => p_fegli_desc,
2562 p_first_action_la_code1 => p_first_action_la_code1,
2563 p_first_action_la_code2 => p_first_action_la_code2,
2564 p_first_action_la_desc1 => p_first_action_la_desc1,
2565 p_first_action_la_desc2 => p_first_action_la_desc2,
2566 p_first_noa_cancel_or_correct => p_first_noa_cancel_or_correct,
2567 p_first_noa_id => p_first_noa_id,
2568 p_first_noa_code => p_first_noa_code,
2569 p_first_noa_desc => p_first_noa_desc,
2570 p_first_noa_pa_request_id => p_first_noa_pa_request_id,
2571 p_flsa_category => p_flsa_category,
2572 p_forwarding_address_line1 => p_forwarding_address_line1,
2573 p_forwarding_address_line2 => p_forwarding_address_line2,
2574 p_forwarding_address_line3 => p_forwarding_address_line3,
2575 p_forwarding_country => p_forwarding_country,
2576 p_forwarding_country_short_nam => p_forwarding_country_short_nam,
2577 p_forwarding_postal_code => p_forwarding_postal_code,
2578 p_forwarding_region_2 => p_forwarding_region_2,
2579 p_forwarding_town_or_city => p_forwarding_town_or_city ,
2580 p_from_adj_basic_pay => p_from_adj_basic_pay,
2581 p_from_basic_pay => p_from_basic_pay,
2582 p_from_grade_or_level => p_from_grade_or_level,
2583 p_from_locality_adj => p_from_locality_adj,
2584 p_from_occ_code => p_from_occ_code,
2585 p_from_other_pay_amount => p_from_other_pay_amount,
2586 p_from_pay_basis => p_from_pay_basis,
2587 p_from_pay_plan => p_from_pay_plan,
2588 --FWFA Changes Bug#4444609
2589 -- p_input_pay_rate_determinant => p_input_pay_rate_determinant,
2590 -- p_from_pay_table_identifier => p_from_pay_table_identifier,
2591 -- FWFA Changes
2592 p_from_position_id => p_from_position_id,
2593 p_from_position_org_line1 => p_from_position_org_line1,
2594 p_from_position_org_line2 => p_from_position_org_line2,
2595 p_from_position_org_line3 => p_from_position_org_line3,
2596 p_from_position_org_line4 => p_from_position_org_line4,
2597 p_from_position_org_line5 => p_from_position_org_line5,
2598 p_from_position_org_line6 => p_from_position_org_line6,
2599 p_from_position_number => p_from_position_number,
2600 p_from_position_seq_no => p_from_position_seq_no,
2601 p_from_position_title => p_from_position_title,
2602 p_from_step_or_rate => p_from_step_or_rate,
2603 p_from_total_salary => p_from_total_salary,
2604 p_functional_class => p_functional_class,
2605 p_notepad => p_notepad,
2606 p_part_time_hours => p_part_time_hours,
2607 p_pay_rate_determinant => p_pay_rate_determinant,
2608 p_person_id => p_person_id,
2609 p_position_occupied => p_position_occupied,
2610 p_proposed_effective_date => p_proposed_effective_date,
2611 p_requested_by_person_id => p_requested_by_person_id,
2612 p_requested_by_title => p_requested_by_title,
2613 p_requested_date => p_requested_date,
2614 p_requesting_office_remarks_de => p_requesting_office_remarks_de,
2615 p_requesting_office_remarks_fl => p_requesting_office_remarks_fl,
2616 p_request_number => p_request_number,
2617 p_resign_and_retire_reason_des => p_resign_and_retire_reason_des,
2618 p_retirement_plan => p_retirement_plan,
2619 p_retirement_plan_desc => p_retirement_plan_desc,
2620 p_second_action_la_code1 => p_second_action_la_code1,
2621 p_second_action_la_code2 => p_second_action_la_code2,
2622 p_second_action_la_desc1 => p_second_action_la_desc1,
2623 p_second_action_la_desc2 => p_second_action_la_desc2,
2624 p_second_noa_cancel_or_correct => p_second_noa_cancel_or_correct,
2625 p_second_noa_code => p_second_noa_code,
2626 p_second_noa_desc => p_second_noa_desc,
2627 p_second_noa_id => p_second_noa_id,
2628 p_second_noa_pa_request_id => p_second_noa_pa_request_id,
2629 p_service_comp_date => p_service_comp_date,
2630 p_supervisory_status => p_supervisory_status,
2631 p_tenure => p_tenure,
2632 p_to_adj_basic_pay => p_to_adj_basic_pay,
2633 p_to_basic_pay => p_to_basic_pay,
2634 p_to_grade_id => p_to_grade_id,
2635 p_to_grade_or_level => p_to_grade_or_level,
2636 p_to_job_id => p_to_job_id,
2637 p_to_locality_adj => p_to_locality_adj,
2638 p_to_occ_code => p_to_occ_code,
2639 p_to_organization_id => p_to_organization_id,
2640 p_to_other_pay_amount => p_to_other_pay_amount,
2641 p_to_au_overtime => p_to_au_overtime,
2642 p_to_auo_premium_pay_indicator => p_to_auo_premium_pay_indicator,
2643 p_to_availability_pay => p_to_availability_pay,
2644 p_to_ap_premium_pay_indicator => p_to_ap_premium_pay_indicator,
2645 p_to_retention_allowance => p_to_retention_allowance,
2646 p_to_supervisory_differential => p_to_supervisory_differential,
2647 p_to_staffing_differential => p_to_staffing_differential,
2648 p_to_pay_basis => p_to_pay_basis,
2649 p_to_pay_plan => p_to_pay_plan,
2650 -- FWFA Changes Bug#4444609
2651 -- p_to_pay_table_identifier => p_to_pay_table_identifier,
2652 -- FWFA Changes
2653 p_to_position_id => p_to_position_id,
2654 p_to_position_org_line1 => p_to_position_org_line1,
2655 p_to_position_org_line2 => p_to_position_org_line2,
2656 p_to_position_org_line3 => p_to_position_org_line3,
2657 p_to_position_org_line4 => p_to_position_org_line4,
2658 p_to_position_org_line5 => p_to_position_org_line5,
2659 p_to_position_org_line6 => p_to_position_org_line6,
2660 p_to_position_number => p_to_position_number,
2661 p_to_position_seq_no => p_to_position_seq_no,
2662 p_to_position_title => p_to_position_title,
2663 p_to_step_or_rate => p_to_step_or_rate,
2664 p_to_total_salary => p_to_total_salary,
2665 p_veterans_pref_for_rif => p_veterans_pref_for_rif,
2666 p_veterans_preference => p_veterans_preference,
2667 p_veterans_status => p_veterans_status,
2668 p_work_schedule => p_work_schedule,
2669 p_work_schedule_desc => p_work_schedule_desc,
2670 p_year_degree_attained => p_year_degree_attained,
2671 p_first_noa_information1 => p_first_noa_information1,
2672 p_first_noa_information2 => p_first_noa_information2,
2673 p_first_noa_information3 => p_first_noa_information3,
2674 p_first_noa_information4 => p_first_noa_information4,
2675 p_first_noa_information5 => p_first_noa_information5,
2676 p_second_lac1_information1 => p_second_lac1_information1,
2677 p_second_lac1_information2 => p_second_lac1_information2,
2678 p_second_lac1_information3 => p_second_lac1_information3,
2679 p_second_lac1_information4 => p_second_lac1_information4,
2680 p_second_lac1_information5 => p_second_lac1_information5,
2681 p_second_lac2_information1 => p_second_lac2_information1,
2682 p_second_lac2_information2 => p_second_lac2_information2,
2683 p_second_lac2_information3 => p_second_lac2_information3,
2684 p_second_lac2_information4 => p_second_lac2_information4,
2685 p_second_lac2_information5 => p_second_lac2_information5,
2686 p_second_noa_information1 => p_second_noa_information1,
2687 p_second_noa_information2 => p_second_noa_information2,
2688 p_second_noa_information3 => p_second_noa_information3,
2689 p_second_noa_information4 => p_second_noa_information4,
2690 p_second_noa_information5 => p_second_noa_information5,
2691 p_first_lac1_information1 => p_first_lac1_information1,
2692 p_first_lac1_information2 => p_first_lac1_information2,
2693 p_first_lac1_information3 => p_first_lac1_information3,
2694 p_first_lac1_information4 => p_first_lac1_information4,
2695 p_first_lac1_information5 => p_first_lac1_information5,
2696 p_first_lac2_information1 => p_first_lac2_information1,
2697 p_first_lac2_information2 => p_first_lac2_information2,
2698 p_first_lac2_information3 => p_first_lac2_information3,
2699 p_first_lac2_information4 => p_first_lac2_information4,
2700 p_first_lac2_information5 => p_first_lac2_information5,
2701 p_attribute_category => p_attribute_category,
2702 p_attribute1 => p_attribute1,
2703 p_attribute2 => p_attribute2,
2704 p_attribute3 => p_attribute3,
2705 p_attribute4 => p_attribute4,
2706 p_attribute5 => p_attribute5,
2707 p_attribute6 => p_attribute6,
2708 p_attribute7 => p_attribute7,
2709 p_attribute8 => p_attribute8,
2710 p_attribute9 => p_attribute9,
2711 p_attribute10 => p_attribute10,
2712 p_attribute11 => p_attribute11,
2713 p_attribute12 => p_attribute12,
2714 p_attribute13 => p_attribute13,
2715 p_attribute14 => p_attribute14,
2716 p_attribute15 => p_attribute15,
2717 p_attribute16 => p_attribute16,
2718 p_attribute17 => p_attribute17,
2719 p_attribute18 => p_attribute18,
2720 p_attribute19 => p_attribute19,
2721 p_attribute20 => p_attribute20,
2722 p_print_sf50_flag => p_print_sf50_flag,
2723 p_printer_name => p_printer_name,
2724 p_u_attachment_modified_flag => p_u_attachment_modified_flag,
2725 p_u_approved_flag => p_u_approved_flag,
2726 p_u_user_name_acted_on => p_u_user_name_acted_on,
2727 p_u_action_taken => p_u_action_taken,
2728 p_u_approval_status => p_u_approval_status,
2729 p_i_user_name_routed_to => p_i_user_name_routed_to,
2730 p_i_groupbox_id => p_i_groupbox_id,
2731 p_i_routing_list_id => p_i_routing_list_id,
2732 p_i_routing_seq_number => p_i_routing_seq_number,
2733 p_capped_other_pay => p_capped_other_pay,
2734 p_par_object_version_number => p_par_object_version_number,
2735 p_to_retention_allow_percentag => p_to_retention_allow_percentag,
2736 p_to_supervisory_diff_percenta => p_to_supervisory_diff_percenta,
2737 p_to_staffing_diff_percentage => p_to_staffing_diff_percentage,
2738 p_award_percentage => p_award_percentage,
2739 p_rpa_type => p_rpa_type,
2740 p_mass_action_id => p_mass_action_id,
2741 p_mass_action_eligible_flag => p_mass_action_eligible_flag,
2742 p_mass_action_select_flag => p_mass_action_select_flag,
2743 p_mass_action_comments => p_mass_action_comments
2744 );
2745 exception
2746 when hr_api.cannot_find_prog_unit then
2747 hr_api.cannot_find_prog_unit_error
2748 (p_module_name => 'update_sf52',
2749 p_hook_type => 'BP'
2750 );
2751 end;
2752 --
2753 -- End of Before Process User Hook call
2754 --
2755 hr_utility.set_location(l_proc, 6);
2756 hr_utility.set_location('First LAC CODE is : ' ||l_proc || ' ' ||p_first_action_la_code1, 7);
2757 --
2758 -- Validation in addition to Row Handlers
2759
2760 -- If the SF52 is processed for the person same as the user, then do not allow creation
2761
2762 If nvl(p_person_id,hr_api.g_number) <> hr_api.g_number then
2763 -- get employee_id of the user
2764 for user_id in c_names loop
2765 If user_id.employee_id = p_person_id then
2766 hr_utility.set_message(8301,'GHR_38503_CANNOT_INIT_FOR_SELF');
2767 hr_utility.raise_error;
2768 End if;
2769 end loop;
2770 End if;
2771
2772 --
2773 -- Routing Group _Id can be changed only in a case where the request has been initiated
2774 -- but not yet routed , for instance when the user uses the task flow button to
2775 -- naviage to another form and when he comes back he can change the routing_grouip_id
2776
2777 if p_routing_group_id is not null and p_routing_group_id <> hr_api.g_number then
2778 for rout_group_id in c_routing_group_id loop
2779 l_routing_group_id := rout_group_id.routing_group_id;
2780 end loop;
2781 if nvl(l_routing_group_id,hr_api.g_number) <> p_routing_group_id then
2782 for cnt_of_history in c_cnt_history loop
2783 l_cnt_history := cnt_of_history.cnt;
2784 exit;
2785 end loop;
2786 if nvl(l_cnt_history,0) > 1 then
2787 hr_utility.set_message(8301,'GHR_38113_ROUT_GROUP_NON_UPD');
2788 hr_utility.raise_error;
2789 end if;
2790 end if;
2791 end if;
2792
2793
2794 hr_utility.set_location('approval date ' || to_char(l_approval_date),1);
2795 l_approval_date := p_approval_date;
2796 l_approving_official_work_titl := p_approving_official_work_titl;
2797 l_approving_official_full_name := p_approving_official_full_name;
2798
2799 hr_utility.set_location('approval_stat ' || p_u_approval_Status,1);
2800 If nvl(p_u_approval_status,hr_api.g_varchar2) = 'APPROVE' then
2801 If p_approval_date is null or p_approval_date = hr_api.g_date
2802 then
2803 hr_utility.set_location('approval date is not null',1);
2804 l_effective_date := trunc(sysdate);
2805 l_approval_date := sysdate;
2806 -- get the full_name of the approver - format First Name MiddleName. Last Name -- p_user_name_acted_on
2807 for user_emp_name in c_names loop
2808 l_approving_official_full_name := user_emp_name.first_name;
2809 If user_emp_name.middle_names is not null then
2810 l_approving_official_full_name := l_approving_official_full_name
2811 || ' ' ||substr(user_emp_name.middle_names,1,1) || '.' || ' ' || user_emp_name.last_name ;
2812 Else
2813 l_approving_official_full_name := l_approving_official_full_name || ' ' || user_emp_name.last_name;
2814 End if;
2815 l_employee_id := user_emp_name.employee_id;
2816 end loop;
2817 if l_employee_id is not null then
2818 -- get the working title of the approver -- would it be as of today
2819 l_approving_official_work_titl := ghr_pa_requests_pkg.get_position_work_title
2820 (p_person_id => l_employee_id,
2821 p_effective_date => l_effective_date
2822 );
2823 End if;
2824 Else
2825 l_approving_official_work_titl := p_approving_official_work_titl;
2826 l_approving_official_full_name := p_approving_official_full_name;
2827 l_approval_date := p_approval_date;
2828 End if;
2829 End if;
2830
2831
2832 -- Update the SF50 approver details , when the user chooses to 'Update HR' (Immediate or Future)
2833 -- Derive for individual actions . For Mass Actions they are the same as the SF52 approver details
2834 -- the following the lines are for bug 715020. This is to ensure that we don't update the
2835 -- sf50 approval fields to null. Previously, the local versions of these variables would never
2836 -- have been initialized unless the action_taken was ('UPDATE_HR','FUTURE_ACTION')
2837 -- (see code immediately following.
2838 l_sf50_approval_date := hr_api.g_date;
2839 l_sf50_approving_ofcl_work_tit := hr_api.g_varchar2;
2840 l_sf50_approving_ofcl_full_nam := hr_api.g_varchar2;
2841
2842 If nvl(p_u_action_taken,hr_api.g_varchar2) in ('UPDATE_HR','FUTURE_ACTION') then
2843 If p_approval_date is not null and p_approval_date <> hr_api.g_date then
2844 l_sf50_approval_date := p_approval_date;
2845 l_sf50_approving_ofcl_work_tit := p_approving_official_work_titl;
2846 l_sf50_approving_ofcl_full_nam := p_approving_official_full_name;
2847 Else
2848 l_sf50_approval_date := sysdate;
2849 for user_emp_name in c_names loop
2850 l_sf50_approving_ofcl_full_nam := user_emp_name.first_name;
2851 If user_emp_name.middle_names is not null then
2852 l_sf50_approving_ofcl_full_nam := l_sf50_approving_ofcl_full_nam
2853 || ' ' ||substr(user_emp_name.middle_names,1,1) || '.' || ' ' || user_emp_name.last_name ;
2854 Else
2855 l_sf50_approving_ofcl_full_nam := l_sf50_approving_ofcl_full_nam || ' ' || user_emp_name.last_name;
2856 End if;
2857 l_employee_id := user_emp_name.employee_id;
2858 end loop;
2859 if l_employee_id is not null then
2860 -- get the working title of the approver -- would be as of today
2861 l_sf50_approving_ofcl_work_tit := ghr_pa_requests_pkg.get_position_work_title
2862 (p_person_id => l_employee_id
2863 );
2864 End if;
2865 End if;
2866 End if;
2867
2868
2869 hr_utility.set_location(l_proc, 7);
2870
2871 l_par_object_version_number := p_par_object_version_number;
2872 l_effective_date := trunc(nvl(p_effective_date,sysdate));
2873
2874 --
2875
2876 -- Insert a row into pa_requests by calling the ins row handler
2877 l_object_version_number := p_par_object_version_number;
2878
2879 hr_utility.set_location('First LAC CODE is : ' ||l_proc || ' ' ||p_first_action_la_code1, 8);
2880 hr_utility.set_location('l_effective_date : ' ||l_effective_date, 8);
2881 hr_utility.set_location('p_noa_family_code : ' ||p_noa_family_code, 8);
2882 -- Bug 2542417
2883 -- In some cases like Mass actions/Cancellation actions the assignment id and
2884 -- and effective_date are not available to fetch the capped other pay
2885 -- Below code fetches the effective_date and assignment id to be passed to
2886 -- ghr_pa_requests_pkg2.get_cop function
2887
2888 for c_par in c_get_det_for_cop loop
2889 hr_utility.set_location('c_par.employee_assignment_id : '
2890 ||c_par.employee_assignment_id, 8);
2891 hr_utility.set_location('c_par.effective_date : '
2892 ||c_par.effective_date, 8);
2893 l_from_cop := nvl(ghr_pa_requests_pkg2.get_cop
2894 (nvl(p_employee_assignment_id,c_par.employee_assignment_id)
2895 ,c_par.effective_date)
2896 ,p_from_other_pay_amount);
2897 end loop;
2898
2899
2900 ghr_par_upd.upd
2901 (
2902 p_pa_request_id => p_pa_request_id,
2903 p_noa_family_code => p_noa_family_code,
2904 p_routing_group_id => p_routing_group_id,
2905 p_proposed_effective_asap_flag => p_proposed_effective_asap_flag,
2906 p_academic_discipline => p_academic_discipline,
2907 p_additional_info_person_id => p_additional_info_person_id,
2908 p_additional_info_tel_number => p_additional_info_tel_number,
2909 p_altered_pa_request_id => p_altered_pa_request_id,
2910 p_annuitant_indicator => p_annuitant_indicator,
2911 p_annuitant_indicator_desc => p_annuitant_indicator_desc,
2912 p_appropriation_code1 => p_appropriation_code1,
2913 p_appropriation_code2 => p_appropriation_code2,
2914 p_approval_date => l_approval_date,
2915 p_approving_official_full_name => l_approving_official_full_name,
2916 p_approving_official_work_titl => l_approving_official_work_titl,
2917 p_sf50_approval_date => l_sf50_approval_date,
2918 p_sf50_approving_ofcl_full_nam => l_sf50_approving_ofcl_full_nam,
2919 p_sf50_approving_ofcl_work_tit => l_sf50_approving_ofcl_work_tit,
2920 p_authorized_by_person_id => p_authorized_by_person_id,
2921 p_authorized_by_title => p_authorized_by_title,
2922 p_award_amount => p_award_amount,
2923 p_award_uom => p_award_uom,
2924 p_bargaining_unit_status => p_bargaining_unit_status,
2925 p_citizenship => p_citizenship,
2926 p_concurrence_date => p_concurrence_date,
2927 p_custom_pay_calc_flag => p_custom_pay_calc_flag,
2928 p_duty_station_code => p_duty_station_code,
2929 p_duty_station_desc => p_duty_station_desc,
2930 p_duty_station_location_id => p_duty_station_location_id,
2931 p_duty_station_id => p_duty_station_id,
2932 p_education_level => p_education_level,
2933 p_effective_date => p_effective_date,
2934 p_employee_assignment_id => p_employee_assignment_id,
2935 p_employee_date_of_birth => p_employee_date_of_birth,
2936 p_employee_first_name => p_employee_first_name,
2937 p_employee_last_name => p_employee_last_name,
2938 p_employee_middle_names => p_employee_middle_names,
2939 p_employee_national_identifier => p_employee_national_identifier,
2940 p_fegli => p_fegli,
2941 p_fegli_desc => p_fegli_desc,
2942 p_first_action_la_code1 => p_first_action_la_code1,
2943 p_first_action_la_code2 => p_first_action_la_code2,
2944 p_first_action_la_desc1 => p_first_action_la_desc1,
2945 p_first_action_la_desc2 => p_first_action_la_desc2,
2946 p_first_noa_cancel_or_correct => p_first_noa_cancel_or_correct,
2947 p_first_noa_id => p_first_noa_id,
2948 p_first_noa_code => p_first_noa_code,
2949 p_first_noa_desc => p_first_noa_desc,
2950 p_first_noa_pa_request_id => p_first_noa_pa_request_id,
2951 p_flsa_category => p_flsa_category,
2952 p_forwarding_address_line1 => p_forwarding_address_line1,
2953 p_forwarding_address_line2 => p_forwarding_address_line2,
2954 p_forwarding_address_line3 => p_forwarding_address_line3,
2955 p_forwarding_country => p_forwarding_country,
2956 p_forwarding_country_short_nam => p_forwarding_country_short_nam,
2957 p_forwarding_postal_code => p_forwarding_postal_code,
2958 p_forwarding_region_2 => p_forwarding_region_2,
2959 p_forwarding_town_or_city => p_forwarding_town_or_city ,
2960 p_from_adj_basic_pay => p_from_adj_basic_pay,
2961 p_from_basic_pay => p_from_basic_pay,
2962 p_from_grade_or_level => p_from_grade_or_level,
2963 p_from_locality_adj => p_from_locality_adj,
2964 p_from_occ_code => p_from_occ_code,
2965 -- Bug 2353506
2966 p_from_other_pay_amount => l_from_cop,
2967 -- End Bug 2353506
2968 p_from_pay_basis => p_from_pay_basis,
2969 p_from_pay_plan => p_from_pay_plan,
2970 -- FWFA Changes Bug#4444609
2971 p_input_pay_rate_determinant => p_input_pay_rate_determinant,
2972 p_from_pay_table_identifier => p_from_pay_table_identifier,
2973 -- FWFA Changes
2974 p_from_position_id => p_from_position_id,
2975 p_from_position_org_line1 => p_from_position_org_line1,
2976 p_from_position_org_line2 => p_from_position_org_line2,
2977 p_from_position_org_line3 => p_from_position_org_line3,
2978 p_from_position_org_line4 => p_from_position_org_line4,
2979 p_from_position_org_line5 => p_from_position_org_line5,
2980 p_from_position_org_line6 => p_from_position_org_line6,
2981 p_from_position_number => p_from_position_number,
2982 p_from_position_seq_no => p_from_position_seq_no,
2983 p_from_position_title => p_from_position_title,
2984 p_from_step_or_rate => p_from_step_or_rate,
2985 p_from_total_salary => p_from_total_salary,
2986 p_functional_class => p_functional_class,
2987 p_notepad => p_notepad,
2988 p_part_time_hours => p_part_time_hours,
2989 p_pay_rate_determinant => p_pay_rate_determinant,
2990 p_person_id => p_person_id,
2991 p_position_occupied => p_position_occupied,
2992 p_proposed_effective_date => p_proposed_effective_date,
2993 p_requested_by_person_id => p_requested_by_person_id,
2994 p_requested_by_title => p_requested_by_title,
2995 p_requested_date => p_requested_date,
2996 p_requesting_office_remarks_de => p_requesting_office_remarks_de,
2997 p_requesting_office_remarks_fl => p_requesting_office_remarks_fl,
2998 p_request_number => p_request_number,
2999 p_resign_and_retire_reason_des => p_resign_and_retire_reason_des,
3000 p_retirement_plan => p_retirement_plan,
3001 p_retirement_plan_desc => p_retirement_plan_desc,
3002 p_second_action_la_code1 => p_second_action_la_code1,
3003 p_second_action_la_code2 => p_second_action_la_code2,
3004 p_second_action_la_desc1 => p_second_action_la_desc1,
3005 p_second_action_la_desc2 => p_second_action_la_desc2,
3006 p_second_noa_cancel_or_correct => p_second_noa_cancel_or_correct,
3007 p_second_noa_code => p_second_noa_code,
3008 p_second_noa_desc => p_second_noa_desc,
3009 p_second_noa_id => p_second_noa_id,
3010 p_second_noa_pa_request_id => p_second_noa_pa_request_id,
3011 p_service_comp_date => p_service_comp_date,
3012 p_supervisory_status => p_supervisory_status,
3013 p_tenure => p_tenure,
3014 p_to_adj_basic_pay => p_to_adj_basic_pay,
3015 p_to_basic_pay => p_to_basic_pay,
3016 p_to_grade_id => p_to_grade_id,
3017 p_to_grade_or_level => p_to_grade_or_level,
3018 p_to_job_id => p_to_job_id,
3019 p_to_locality_adj => p_to_locality_adj,
3020 p_to_occ_code => p_to_occ_code,
3021 p_to_organization_id => p_to_organization_id,
3022 -- Bug 2353506
3023 p_to_other_pay_amount => nvl(p_capped_other_pay,p_to_other_pay_amount),
3024 -- End Bug 2353506
3025 p_to_au_overtime => p_to_au_overtime,
3026 p_to_auo_premium_pay_indicator => p_to_auo_premium_pay_indicator,
3027 p_to_availability_pay => p_to_availability_pay,
3028 p_to_ap_premium_pay_indicator => p_to_ap_premium_pay_indicator,
3029 p_to_retention_allowance => p_to_retention_allowance,
3030 p_to_supervisory_differential => p_to_supervisory_differential,
3031 p_to_staffing_differential => p_to_staffing_differential,
3032 p_to_pay_basis => p_to_pay_basis,
3033 p_to_pay_plan => p_to_pay_plan,
3034 -- FWFA Changes Bug#4444609
3035 p_to_pay_table_identifier => p_to_pay_table_identifier,
3036 -- FWFA Changes
3037 p_to_position_id => p_to_position_id,
3038 p_to_position_org_line1 => p_to_position_org_line1,
3039 p_to_position_org_line2 => p_to_position_org_line2,
3040 p_to_position_org_line3 => p_to_position_org_line3,
3041 p_to_position_org_line4 => p_to_position_org_line4,
3042 p_to_position_org_line5 => p_to_position_org_line5,
3043 p_to_position_org_line6 => p_to_position_org_line6,
3044 p_to_position_number => p_to_position_number,
3045 p_to_position_seq_no => p_to_position_seq_no,
3046 p_to_position_title => p_to_position_title,
3047 p_to_step_or_rate => p_to_step_or_rate,
3048 p_to_total_salary => p_to_total_salary,
3049 p_veterans_pref_for_rif => p_veterans_pref_for_rif,
3050 p_veterans_preference => p_veterans_preference,
3051 p_veterans_status => p_veterans_status,
3052 p_work_schedule => p_work_schedule,
3053 p_work_schedule_desc => p_work_schedule_desc,
3054 p_year_degree_attained => p_year_degree_attained,
3055 p_first_noa_information1 => p_first_noa_information1,
3056 p_first_noa_information2 => p_first_noa_information2,
3057 p_first_noa_information3 => p_first_noa_information3,
3058 p_first_noa_information4 => p_first_noa_information4,
3059 p_first_noa_information5 => p_first_noa_information5,
3060 p_second_lac1_information1 => p_second_lac1_information1,
3061 p_second_lac1_information2 => p_second_lac1_information2,
3062 p_second_lac1_information3 => p_second_lac1_information3,
3063 p_second_lac1_information4 => p_second_lac1_information4,
3064 p_second_lac1_information5 => p_second_lac1_information5,
3065 p_second_lac2_information1 => p_second_lac2_information1,
3066 p_second_lac2_information2 => p_second_lac2_information2,
3067 p_second_lac2_information3 => p_second_lac2_information3,
3068 p_second_lac2_information4 => p_second_lac2_information4,
3069 p_second_lac2_information5 => p_second_lac2_information5,
3070 p_second_noa_information1 => p_second_noa_information1,
3071 p_second_noa_information2 => p_second_noa_information2,
3072 p_second_noa_information3 => p_second_noa_information3,
3073 p_second_noa_information4 => p_second_noa_information4,
3074 p_second_noa_information5 => p_second_noa_information5,
3075 p_first_lac1_information1 => p_first_lac1_information1,
3076 p_first_lac1_information2 => p_first_lac1_information2,
3077 p_first_lac1_information3 => p_first_lac1_information3,
3078 p_first_lac1_information4 => p_first_lac1_information4,
3079 p_first_lac1_information5 => p_first_lac1_information5,
3080 p_first_lac2_information1 => p_first_lac2_information1,
3081 p_first_lac2_information2 => p_first_lac2_information2,
3082 p_first_lac2_information3 => p_first_lac2_information3,
3083 p_first_lac2_information4 => p_first_lac2_information4,
3084 p_first_lac2_information5 => p_first_lac2_information5,
3085 p_attribute_category => p_attribute_category,
3086 p_attribute1 => p_attribute1,
3087 p_attribute2 => p_attribute2,
3088 p_attribute3 => p_attribute3,
3089 p_attribute4 => p_attribute4,
3090 p_attribute5 => p_attribute5,
3091 p_attribute6 => p_attribute6,
3092 p_attribute7 => p_attribute7,
3093 p_attribute8 => p_attribute8,
3094 p_attribute9 => p_attribute9,
3095 p_attribute10 => p_attribute10,
3096 p_attribute11 => p_attribute11,
3097 p_attribute12 => p_attribute12,
3098 p_attribute13 => p_attribute13,
3099 p_attribute14 => p_attribute14,
3100 p_attribute15 => p_attribute15,
3101 p_attribute16 => p_attribute16,
3102 p_attribute17 => p_attribute17,
3103 p_attribute18 => p_attribute18,
3104 p_attribute19 => p_attribute19,
3105 p_attribute20 => p_attribute20,
3106 p_object_version_number => l_par_object_version_number,
3107 p_to_retention_allow_percentag => p_to_retention_allow_percentag,
3108 p_to_supervisory_diff_percenta => p_to_supervisory_diff_percenta,
3109 p_to_staffing_diff_percentage => p_to_staffing_diff_percentage,
3110 p_award_percentage => p_award_percentage,
3111 p_rpa_type => p_rpa_type,
3112 p_mass_action_id => p_mass_action_id,
3113 p_mass_action_eligible_flag => p_mass_action_eligible_flag,
3114 p_mass_action_select_flag => p_mass_action_select_flag,
3115 p_mass_action_comments => p_mass_action_comments,
3116 p_payment_option => p_payment_option,
3117 p_award_salary => p_award_salary
3118 );
3119 hr_utility.set_location('First LAC CODE is : ' ||l_proc || ' ' ||p_first_action_la_code1, 1);
3120
3121 hr_utility.set_location(l_proc || 'l_ovn' || to_char(l_par_object_version_number),2);
3122 p_par_object_version_number := l_par_object_version_number;
3123 hr_utility.set_location(l_proc, 8);
3124
3125 --2)Write into pa_remarks all mandatory remarks for the specific nature_of_action,
3126 -- in case of either a) first_nature_of_action is input for the first_time or (just insert new recds)
3127 -- b) first nature_of_action has changed (delete and then insert new records)
3128 --
3129
3130 if nvl(p_first_noa_id,hr_api.g_number)
3131 <> nvl(ghr_par_shd.g_old_rec.first_noa_id,hr_api.g_number) then
3132 hr_utility.set_location(l_proc, 9);
3133
3134 -- delete the existing remarks
3135 delete from ghr_pa_remarks pre
3136 where pre.pa_request_id = p_pa_request_id
3137 and pre.remark_id in
3138 (select remark_id
3139 from ghr_noac_remarks
3140 where nature_of_action_id = ghr_par_shd.g_old_rec.first_noa_id);
3141 if p_first_noa_id is not null then
3142 hr_utility.set_location(l_proc, 10);
3143
3144 insert into ghr_pa_remarks
3145 (pa_remark_id
3146 ,pa_request_id
3147 ,remark_id
3148 ,description
3149 ,object_version_number
3150 )
3151 select ghr_pa_remarks_s.nextval
3152 ,p_pa_request_id
3153 ,rem.remark_id
3154 ,rem.description
3155 ,1
3156 from ghr_remarks rem
3157 ,ghr_noac_remarks nre
3158 where nre.nature_of_action_id = p_first_noa_id
3159 and nre.required_flag = 'Y'
3160 and l_effective_date
3161 between nre.date_from
3162 and nvl(nre.date_to,l_effective_date)
3163 and nre.remark_id = rem.remark_id;
3164 -- and rem.enabled_flag = 'Y'
3165 -- and l_effective_date
3166 -- between rem.date_from
3167 -- and nvl(rem.date_to,l_effective_date));
3168 end if;
3169 end if;
3170
3171 if nvl(p_second_noa_id,hr_api.g_number)
3172 <> nvl(ghr_par_shd.g_old_rec.second_noa_id,hr_api.g_number) then
3173
3174 -- delete the existing remarks
3175 delete from ghr_pa_remarks pre
3176 where pre.pa_request_id = p_pa_request_id
3177 and pre.remark_id in
3178 (select remark_id
3179 from ghr_noac_remarks
3180 where nature_of_action_id = ghr_par_shd.g_old_rec.second_noa_id);
3181 --Begin Bug# 7174094
3182 l_separation_flag := FALSE;
3183 IF p_first_noa_code in ('002') THEN
3184 For l_sec_noa_fmly in c_sec_noa_fmly loop
3185 IF l_sec_noa_fmly.noa_family_code = 'SEPARATION' THEN
3186 l_separation_flag := TRUE;
3187 END IF;
3188 end loop;
3189 END IF;
3190 -- Bug# 7174094 added l_separation_flag condition in below if condition
3191 if p_second_noa_id is not null AND NOT( l_separation_flag AND p_first_noa_code in ('002'))then
3192 --End Bug# 7174094
3193 insert into ghr_pa_remarks
3194 (pa_remark_id
3195 ,pa_request_id
3196 ,remark_id
3197 ,description
3198 ,object_version_number
3199 )
3200 select ghr_pa_remarks_s.nextval
3201 ,p_pa_request_id
3202 ,rem.remark_id
3203 ,rem.description
3204 ,1
3205 from ghr_remarks rem
3206 ,ghr_noac_remarks nre
3207 where nre.nature_of_action_id = p_second_noa_id
3208 and nre.required_flag = 'Y'
3209 and l_effective_date
3210 between nre.date_from
3211 and nvl(nre.date_to,l_effective_date)
3212 and nre.remark_id = rem.remark_id;
3213 -- and rem.enabled_flag = 'Y'
3214 -- and l_effective_date
3215 -- between rem.date_from
3216 -- and nvl(rem.date_to,l_effective_date));
3217 end if;
3218 end if;
3219
3220 --- Bug # 6127620 need to set the FND Session Date before calling extra info
3221 /*If p_first_noa_code = '800' or p_second_noa_code = '800' then
3222 GHR_HISTORY_API.get_session_date(l_curr_sess_date);
3223 ghr_session.set_fnd_session_date(l_effective_date);
3224 end if;
3225
3226 Begin
3227 ----Bug #6127620 */
3228
3229
3230 If nvl(p_first_noa_code,hr_api.g_varchar2) <> '001'
3231 then
3232
3233 If p_first_noa_id = hr_api.g_number then
3234 l_rec.first_noa_id := ghr_par_shd.g_old_rec.first_noa_id;
3235 Else
3236 l_rec.first_noa_id := p_first_noa_id;
3237 End if;
3238
3239 If p_second_noa_id = hr_api.g_number then
3240 l_rec.second_noa_id := ghr_par_shd.g_old_rec.second_noa_id;
3241 Else
3242 l_rec.second_noa_id := p_second_noa_id;
3243 End if;
3244
3245 If p_effective_date = hr_api.g_date then
3246 l_rec.effective_date := ghr_par_shd.g_old_rec.effective_date;
3247 Else
3248 l_rec.effective_date := p_effective_date;
3249 End if;
3250
3251 If p_person_id = hr_api.g_number then
3252 l_rec.person_id := ghr_par_shd.g_old_rec.person_id;
3253 Else
3254 l_rec.person_id := p_person_id;
3255 End if;
3256
3257 If p_employee_assignment_id = hr_api.g_number then
3258 l_rec.employee_assignment_id := ghr_par_shd.g_old_rec.employee_assignment_id;
3259 Else
3260 l_rec.employee_assignment_id := p_employee_assignment_id;
3261 End if;
3262
3263 If p_to_position_id = hr_api.g_number then
3264 l_rec.to_position_id := ghr_par_shd.g_old_rec.to_position_id;
3265 Else
3266 l_rec.to_position_id := p_to_position_id;
3267 End if;
3268
3269 if p_u_action_taken = 'NOT_ROUTED' then
3270 l_rpa_type := p_rpa_type;
3271 l_mass_action_id := p_mass_action_id;
3272 If nvl(l_rpa_type,'##') = hr_api.g_varchar2 then
3273 l_rpa_type := ghr_par_shd.g_old_rec.rpa_type;
3274 End if;
3275 If nvl(l_mass_action_id,-9999) = hr_api.g_number then
3276 l_mass_action_id := ghr_par_shd.g_old_rec.mass_action_id;
3277 End if;
3278 End if;
3279
3280 If (nvl(p_effective_date,hr_api.g_date)
3281 <> nvl(ghr_par_shd.g_old_rec.effective_date,hr_api.g_date)) or
3282 (nvl(p_person_id,hr_api.g_number)
3283 <> nvl(ghr_par_shd.g_old_rec.person_id,hr_api.g_number))
3284 or (p_noa_family_code = 'APP') or
3285 (l_action_taken = 'NOT_ROUTED' and l_mass_action_id is null and nvl(l_rpa_type,hr_api.g_varchar2) <> 'TA' ) then
3286
3287 GHR_NON_SF52_EXTRA_INFO.fetch_generic_extra_info
3288 (p_pa_request_id => p_pa_request_id,
3289 p_person_id => l_rec.person_id,
3290 p_assignment_id => l_rec.employee_assignment_id,
3291 p_effective_date => trunc(nvl(l_rec.effective_date,sysdate)),
3292 p_refresh_flag => 'N'
3293 );
3294 End if;
3295
3296
3297 if (nvl(p_first_noa_code,hr_api.g_varchar2)
3298 <> nvl(ghr_par_shd.g_old_rec.first_noa_code,hr_api.g_varchar2)) or
3299 (nvl(p_second_noa_code,hr_api.g_varchar2)
3300 <> nvl(ghr_par_shd.g_old_rec.second_noa_code,hr_api.g_varchar2)) or
3301 (nvl(p_effective_date,hr_api.g_date)
3302 <> nvl(ghr_par_shd.g_old_rec.effective_date,hr_api.g_date)) or
3303 (nvl(p_person_id,hr_api.g_number)
3304 <> nvl(ghr_par_shd.g_old_rec.person_id,hr_api .g_number)) or
3305 (nvl(p_to_position_id,hr_api.g_number)
3306 <> nvl(ghr_par_shd.g_old_rec.to_position_id,hr_api .g_number))
3307 then
3308 hr_utility.set_location('update/ delete extra info',1);
3309
3310
3311 GHR_NON_SF52_EXTRA_INFO.populate_noa_spec_extra_info
3312 (p_pa_request_id => p_pa_request_id,
3313 p_first_noa_id => l_rec.first_noa_id,
3314 p_second_noa_id => l_rec.second_noa_id,
3315 p_person_id => l_rec.person_id,
3316 p_assignment_id => l_rec.employee_assignment_id,
3317 p_position_id => l_rec.to_position_id,
3318 p_effective_date => l_rec.effective_date,
3319 p_refresh_flag => 'N'
3320 );
3321 End if;
3322 End if;
3323 -- Bug # 6127620
3324 /*Exception
3325 when OTHERS then
3326 If p_first_noa_code = '800' or p_second_noa_code = '800' then
3327 ghr_session.set_fnd_session_date(l_curr_sess_date);
3328 raise;
3329 else
3330 raise;
3331 end if;
3332 End;
3333 If p_first_noa_code = '800' or p_second_noa_code = '800' then
3334 ghr_session.set_fnd_session_date(l_curr_sess_date);
3335 end if; */
3336 -- Bug # 6127620
3337
3338
3339
3340 --
3341 --3)Derive all parmeters required to insert routing_history records.
3342
3343
3344 l_action_taken := p_u_action_taken;
3345 hr_utility.set_location(l_proc, 11);
3346 if l_action_taken is null then
3347 if nvl(p_authorized_by_person_id,hr_api.g_number) <>
3348 nvl(ghr_par_shd.g_old_rec.authorized_by_person_id,hr_api.g_number) then
3349 l_action_taken := 'AUTHORIZED';
3350 elsif nvl(p_requested_by_person_id,hr_api.g_number) <>
3351 nvl(ghr_par_shd.g_old_rec.requested_by_person_id,hr_api.g_number) then
3352 l_action_taken := 'REQUESTED';
3353 else
3354 for history_exists in C_history_exists loop
3355 l_exists := true;
3356 l_old_action_taken := history_exists.action_taken;
3357 exit;
3358 end loop;
3359 if l_exists = true then
3360 for cnt_history in c_cnt_history loop
3361 l_cnt_history := cnt_history.cnt;
3362 end loop;
3363 if l_cnt_history = 1 and l_old_action_taken = 'NOT_ROUTED' then
3364 l_action_taken := 'INITIATED';
3365 else
3366 l_action_taken := 'NO_ACTION';
3367 end if;
3368 else
3369 l_action_taken := 'INITIATED';
3370 end if;
3371 end if;
3372 end if;
3373 if l_action_taken not in('NOT_ROUTED','INITIATED','REQUESTED','AUTHORIZED','END_ROUTING','ENDED',
3374 'NO_ACTION','REVIEWED','CANCELED','UPDATE_HR','UPDATE_HR_COMPLETE','NONE')
3375 then
3376 hr_utility.set_message(8301,'GHR_38110_INVALID_ACTION_TAKEN');
3377 hr_utility.raise_error;
3378 end if;
3379 -- Bug #1285393 Modified to add the if condition as not to update the status
3380 -- if action taken parameter is passed as NONE. (NONE is passed during the call made
3381 -- in Cancellation of APPT Sf52 to cancel the RPA's made after Appointment)
3382 if l_action_taken not in ('NONE') then
3383
3384 ghr_sf52_api.get_par_status
3385 (p_effective_date => p_effective_date,
3386 p_approval_date => l_approval_date,
3387 p_requested_by_person_id => p_requested_by_person_id,
3388 p_authorized_by_person_id => p_authorized_by_person_id,
3389 p_action_taken => p_u_action_taken,
3390 --8279908
3391 p_pa_request_id => p_pa_request_id,
3392 p_status => l_status
3393 );
3394
3395 hr_utility.set_location('befor upd of status' ,1);
3396 ghr_par_upd.upd
3397 (p_pa_request_id => p_pa_request_id,
3398 p_status => l_status,
3399 p_object_version_number => l_par_object_version_number
3400 );
3401 end if;
3402 hr_utility.set_location('l_status : ' || l_status,1);
3403 hr_utility.set_location('after upd of status' ,1);
3404 p_par_object_version_number := l_par_object_version_number;
3405 hr_utility.set_location('check ' ||l_action_taken,1);
3406 hr_utility.set_location('p_i_user_name_routed_to ' ||p_i_user_name_routed_to,1);
3407 hr_utility.set_location('p_i_groupbox_id ' ||p_i_groupbox_id,1);
3408 hr_utility.set_location('p_i_routing_list_id ' ||p_i_routing_list_id,1);
3409
3410 if l_action_taken not in ('CANCELED','UPDATE_HR','UPDATE_HR_COMPLETE','NOT_ROUTED','NONE','ENDED','END_ROUTING') then
3411 if p_i_user_name_routed_to is null and
3412 p_i_groupbox_id is null and
3413 p_i_routing_list_id is null then
3414 hr_utility.set_message(8301,'GHR_38115_ROUT_INFO_REQD');
3415 hr_utility.raise_error;
3416 end if;
3417 end if;
3418
3419 if nvl(l_action_taken,hr_api.g_varchar2) not in ('CANCELED','UPDATE_HR_COMPLETE','NONE','ENDED')then
3420 hr_utility.set_location('check ' ||l_action_taken,1);
3421 hr_utility.set_location('check ' ||l_mass_action_id,1);
3422 hr_utility.set_location('Check ' || l_rpa_type,1);
3423 -- Do not routing history if Template record for Mass Actions.
3424
3425 -- If (l_action_taken = 'NOT_ROUTED' and l_mass_action_id is not null --AVR
3426 -- and nvl(l_rpa_type,hr_api.g_varchar2) <> 'TA') or --AVR
3427 -- (l_action_taken = 'NOT_ROUTED' and l_mass_action_id is null) then --AVR
3428
3429 if nvl(l_rpa_type,hr_api.g_varchar2) <> 'TA' then --AVR
3430 if (nvl(l_rpa_type,hr_api.g_varchar2) = 'A' and l_action_taken = 'NOT_ROUTED' ) then --AVR
3431 hr_utility.set_location('Form Folder Updation ..Do not route ' ,1); --AVR
3432 else --AVR
3433
3434 for cur_routing_history_id in C_routing_history_id loop
3435 l_u_pa_routing_history_id := cur_routing_history_id.pa_routing_history_id;
3436 l_u_prh_object_version_number := cur_routing_history_id.object_version_number;
3437 exit;
3438 end loop;
3439 hr_utility.set_location('in update sf52 api , user acted on is ' || p_u_user_name_acted_on,1);
3440 if p_u_user_name_acted_on is not null
3441 -- RP
3442 and p_u_user_name_acted_on <> hr_api.g_varchar2 then
3443 hr_utility.set_location(l_proc, 12);
3444
3445 ghr_pa_requests_pkg.get_roles
3446 (p_pa_request_id,
3447 p_routing_group_id,
3448 p_u_user_name_acted_on,
3449 l_initiator_flag,
3450 l_requester_flag,
3451 l_authorizer_flag,
3452 l_personnelist_flag,
3453 l_approver_flag,
3454 l_reviewer_flag
3455 );
3456 hr_utility.set_location(l_proc, 13);
3457
3458 for name_rec in C_names loop
3459 l_user_name_employee_id := name_rec.employee_id ;
3460 l_user_name_emp_first_name := name_rec.first_name;
3461 l_user_name_emp_last_name := name_rec.last_name;
3462 l_user_name_emp_middle_names := name_rec.middle_names;
3463 exit;
3464 end loop;
3465 end if;
3466 -- Update the latest record in the routing history for the specific request_id
3467
3468 hr_utility.set_location(l_proc, 14);
3469
3470 If l_action_taken = 'UPDATE_HR' and
3471 trunc(p_effective_date) > sysdate then
3472 l_action_taken := 'FUTURE_ACTION';
3473 End if;
3474
3475 ghr_prh_upd.upd
3476 (
3477 p_pa_routing_history_id => l_u_pa_routing_history_id,
3478 p_attachment_modified_flag => nvl(p_u_attachment_modified_flag,'N'),
3479 p_initiator_flag => nvl(l_initiator_flag,'N'),
3480 p_approver_flag => nvl(l_approver_flag,'N'),
3481 p_reviewer_flag => nvl(l_reviewer_flag,'N'),
3482 p_requester_flag => nvl(l_requester_flag,'N'),
3483 p_authorizer_flag => nvl(l_authorizer_flag,'N'),
3484 p_personnelist_flag => nvl(l_personnelist_flag,'N'),
3485 p_approved_flag => nvl(p_u_approved_flag,'N'),
3486 p_user_name => p_u_user_name_acted_on,
3487 p_user_name_employee_id => l_user_name_employee_id,
3488 p_user_name_emp_first_name => l_user_name_emp_first_name,
3489 p_user_name_emp_last_name => l_user_name_emp_last_name,
3490 p_user_name_emp_middle_names => l_user_name_emp_middle_names,
3491 p_notepad => p_notepad,
3492 p_action_taken => l_action_taken,
3493 p_noa_family_code => p_noa_family_code,
3494 p_nature_of_action_id => p_first_noa_id,
3495 p_second_nature_of_action_id => p_second_noa_id,
3496 p_approval_status => p_u_approval_status,
3497 p_object_version_number => l_u_prh_object_version_number
3498 -- p_validate => p_validate
3499 );
3500
3501
3502
3503 -- if the specific routing_list has already been used,get the next seq. no.from routing_list_members.
3504 -- else sequence_number = 1
3505 -- if there are no more sequences, raise an error
3506
3507 l_next_seq_numb := p_i_routing_seq_number;
3508 l_next_groupbox_id := p_i_groupbox_id;
3509 l_next_user_name := p_i_user_name_routed_to;
3510
3511 -- fetch the next sequence number for the specific routing list, when it is not passed
3512 if p_i_routing_list_id is not null and p_i_routing_seq_number is null then
3513
3514 for rout_list_used in cur_rout_list_used loop
3515 l_cur_seq_numb := rout_list_used.routing_seq_number;
3516 exit;
3517 end loop;
3518
3519 if l_cur_seq_numb is null then
3520 l_cur_seq_numb := 0;
3521 end if;
3522 for next_rout_seq_numb in cur_next_rout_seq loop
3523 l_next_seq_numb := next_rout_seq_numb.seq_number;
3524 l_next_groupbox_id := next_rout_seq_numb.groupbox_id;
3525 l_next_user_name := next_rout_seq_numb.user_name;
3526 exit;
3527 end loop;
3528 if l_next_user_name is null then
3529 l_next_user_name := p_i_user_name_routed_to;
3530 end if;
3531 if l_next_groupbox_id is null then
3532 l_next_groupbox_id := p_i_groupbox_id;
3533 end if;
3534 if l_next_seq_numb is null then
3535 hr_utility.set_message(8301, 'GHR_38114_NO_MORE_SEQ_NUMBER');
3536 hr_utility.raise_error;
3537 end if;
3538 end if;
3539
3540 hr_utility.set_location(l_proc, 20);
3541 -- check for open events before attempting to route / Update HR
3542 hr_utility.set_location('Before check Open Events',1);
3543 ghr_sf52_api.check_for_open_events
3544 (
3545 p_pa_request_id => p_pa_request_id,
3546 p_message => l_message,
3547 p_action_taken => l_action_taken,
3548 p_user_name_acted_on => p_u_user_name_acted_on,
3549 p_user_name_routed_to => l_next_user_name,
3550 p_groupbox_routed_to => l_next_groupbox_id
3551 );
3552
3553 -- call events user hook
3554 ghr_agency_check.open_events_check
3555 (p_pa_request_id => p_pa_request_id,
3556 p_message_set => l_message
3557 );
3558
3559 if l_message then
3560 hr_utility.set_message(8301,'GHR_38592_OPEN_EVENTS_EXIST');
3561 hr_utility.raise_error;
3562 end if;
3563
3564 -- Insert 2nd record into routing_history for routing details (with exceptions )
3565
3566 /***dk***/
3567 -- Should I add ENDED and/or END_ROUTING to the following if??
3568 if nvl(l_action_taken,hr_api.g_varchar2) not in ('NOT_ROUTED','UPDATE_HR','FUTURE_ACTION','NONE','END_ROUTING') then
3569
3570 ghr_prh_ins.ins
3571 (p_pa_routing_history_id => l_i_pa_routing_history_id,
3572 p_pa_request_id => p_pa_request_id,
3573 p_attachment_modified_flag => 'N',
3574 p_initiator_flag => 'N',
3575 p_approver_flag => 'N',
3576 p_reviewer_flag => 'N',
3577 p_requester_flag => 'N',
3578 p_authorizer_flag => 'N',
3579 p_personnelist_flag => nvl(l_personnelist_flag,'N'),
3580 p_approved_flag => 'N',
3581 p_user_name => l_next_user_name,
3582 p_groupbox_id => l_next_groupbox_id,
3583 p_routing_list_id => p_i_routing_list_id,
3584 p_routing_seq_number => l_next_seq_numb,
3585 p_noa_family_code => p_noa_family_code,
3586 p_nature_of_action_id => p_first_noa_id,
3587 p_second_nature_of_action_id => p_second_noa_id,
3588 p_object_version_number => l_i_prh_object_version_number
3589 -- p_validate => p_validate
3590 );
3591
3592 end if;
3593
3594 hr_utility.set_location('pAR' || to_char(l_par_object_version_number),1);
3595
3596 if l_action_taken in ('UPDATE_HR','FUTURE_ACTION','END_ROUTING') then
3597 hr_utility.set_location(l_proc || p_award_amount,1);
3598 hr_utility.set_location(l_proc || p_award_percentage,1);
3599
3600 hr_utility.set_location(l_proc, 21);
3601 l_rec.pa_request_id := p_pa_request_id;
3602 l_rec.noa_family_code := p_noa_family_code;
3603 l_rec.routing_group_id := p_routing_group_id;
3604 If p_proposed_effective_asap_flag = hr_api.g_varchar2 then
3605 l_rec.proposed_effective_asap_flag := ghr_par_shd.g_old_rec.proposed_effective_asap_flag;
3606 Else
3607 l_rec.proposed_effective_asap_flag := p_proposed_effective_asap_flag;
3608 End if;
3609 l_rec.academic_discipline := p_academic_discipline;
3610 l_rec.additional_info_person_id := p_additional_info_person_id;
3611 l_rec.additional_info_tel_number := p_additional_info_tel_number;
3612 l_rec.altered_pa_request_id := p_altered_pa_request_id;
3613 l_rec.annuitant_indicator := p_annuitant_indicator;
3614 l_rec.annuitant_indicator_desc := p_annuitant_indicator_desc;
3615 l_rec.appropriation_code1 := p_appropriation_code1;
3616 l_rec.appropriation_code2 := p_appropriation_code2;
3617 l_rec.approval_date := l_approval_date;
3618 l_rec.approving_official_work_title := l_approving_official_work_titl;
3619 l_rec.approving_official_full_name := l_approving_official_full_name;
3620 l_rec.authorized_by_person_id := p_authorized_by_person_id;
3621 l_rec.authorized_by_title := p_authorized_by_title;
3622 l_rec.award_amount := p_award_amount;
3623 l_rec.award_uom := p_award_uom;
3624 l_rec.bargaining_unit_status := p_bargaining_unit_status;
3625 l_rec.citizenship := p_citizenship;
3626 l_rec.concurrence_date := p_concurrence_date;
3627 If p_custom_pay_calc_flag = hr_api.g_varchar2 then
3628 l_rec.custom_pay_calc_flag := ghr_par_shd.g_old_rec.custom_pay_calc_flag;
3629 Else
3630 l_rec.custom_pay_calc_flag := p_custom_pay_calc_flag;
3631 End if;
3632 l_rec.duty_station_code := p_duty_station_code;
3633 l_rec.duty_station_desc := p_duty_station_desc;
3634 l_rec.duty_station_id := p_duty_station_id;
3635 l_rec.duty_station_location_id := p_duty_station_location_id;
3636 l_rec.education_level := p_education_level;
3637 l_rec.effective_date := p_effective_date;
3638 l_rec.employee_assignment_id := p_employee_assignment_id;
3639 l_rec.employee_date_of_birth := p_employee_date_of_birth;
3640 l_rec.employee_first_name := p_employee_first_name;
3641 l_rec.employee_last_name := p_employee_last_name;
3642 l_rec.employee_middle_names := p_employee_middle_names;
3643 l_rec.employee_national_identifier := p_employee_national_identifier;
3644 l_rec.fegli := p_fegli;
3645 l_rec.fegli_desc := p_fegli_desc;
3646 hr_utility.set_location('First LAC CODE is : ' ||l_proc || ' ' ||l_rec.first_action_la_code1, 22);
3647 l_rec.first_action_la_code1 := p_first_action_la_code1;
3648 hr_utility.set_location('First LAC CODE is : ' ||l_proc || ' ' ||l_rec.first_action_la_code1, 23);
3649 l_rec.first_action_la_code2 := p_first_action_la_code2;
3650 l_rec.first_action_la_desc1 := p_first_action_la_desc1;
3651 l_rec.first_action_la_desc2 := p_first_action_la_desc2;
3652 l_rec.first_noa_cancel_or_correct := p_first_noa_cancel_or_correct;
3653 If p_first_noa_code = hr_api.g_varchar2 then
3654 l_rec.first_noa_code := ghr_par_shd.g_old_rec.first_noa_code;
3655 Else
3656 l_rec.first_noa_code := p_first_noa_code;
3657 End if;
3658 l_rec.first_noa_desc := p_first_noa_desc;
3659 l_rec.first_noa_id := p_first_noa_id;
3660 l_rec.first_noa_pa_request_id := p_first_noa_pa_request_id;
3661 l_rec.flsa_category := p_flsa_category;
3662 l_rec.forwarding_address_line1 := p_forwarding_address_line1;
3663 l_rec.forwarding_address_line2 := p_forwarding_address_line2;
3664 l_rec.forwarding_address_line3 := p_forwarding_address_line3;
3665 l_rec.forwarding_country := p_forwarding_country;
3666 l_rec.forwarding_country_short_name := p_forwarding_country_short_nam;
3667 l_rec.forwarding_postal_code := p_forwarding_postal_code;
3668 l_rec.forwarding_region_2 := p_forwarding_region_2;
3669 l_rec.forwarding_town_or_city := p_forwarding_town_or_city;
3670 l_rec.from_adj_basic_pay := p_from_adj_basic_pay;
3671 l_rec.from_basic_pay := p_from_basic_pay;
3672 l_rec.from_grade_or_level := p_from_grade_or_level;
3673 l_rec.from_locality_adj := p_from_locality_adj;
3674 l_rec.from_occ_code := p_from_occ_code;
3675 l_rec.from_other_pay_amount := p_from_other_pay_amount;
3676 l_rec.from_pay_basis := p_from_pay_basis;
3677
3678 If p_from_pay_plan = hr_api.g_varchar2 then
3679 l_rec.from_pay_plan := ghr_par_shd.g_old_rec.from_pay_plan;
3680 Else
3681 l_rec.from_pay_plan := p_from_pay_plan;
3682 End if;
3683 -- FWFA Changes Bug#4444609
3684 l_rec.input_pay_rate_determinant := p_input_pay_rate_determinant;
3685 l_rec.from_pay_table_identifier := p_from_pay_table_identifier;
3686 -- FWFA Changes
3687 l_rec.from_position_id := p_from_position_id;
3688 l_rec.from_position_org_line1 := p_from_position_org_line1;
3689 l_rec.from_position_org_line2 := p_from_position_org_line2;
3690 l_rec.from_position_org_line3 := p_from_position_org_line3;
3691 l_rec.from_position_org_line4 := p_from_position_org_line4;
3692 l_rec.from_position_org_line5 := p_from_position_org_line5;
3693 l_rec.from_position_org_line6 := p_from_position_org_line6;
3694 l_rec.from_position_number := p_from_position_number;
3695 l_rec.from_position_seq_no := p_from_position_seq_no;
3696 l_rec.from_position_title := p_from_position_title;
3697 l_rec.from_step_or_rate := p_from_step_or_rate;
3698 l_rec.from_total_salary := p_from_total_salary;
3699 l_rec.functional_class := p_functional_class;
3700 l_rec.notepad := p_notepad;
3701 If p_part_time_hours = hr_api.g_number then
3702 l_rec.part_time_hours := ghr_par_shd.g_old_rec.part_time_hours;
3703 Else
3704 l_rec.part_time_hours := p_part_time_hours;
3705 End if;
3706 l_rec.pay_rate_determinant := p_pay_rate_determinant;
3707 l_rec.person_id := p_person_id;
3708 l_rec.position_occupied := p_position_occupied;
3709 l_rec.proposed_effective_date := p_proposed_effective_date;
3710 l_rec.requested_by_person_id := p_requested_by_person_id;
3711 l_rec.requested_by_title := p_requested_by_title;
3712 l_rec.requested_date := p_requested_date;
3713 l_rec.requesting_office_remarks_desc := p_requesting_office_remarks_de;
3714 If p_requesting_office_remarks_fl = hr_api.g_varchar2 then
3715 l_rec.requesting_office_remarks_flag := ghr_par_shd.g_old_rec.requesting_office_remarks_flag;
3716 Else
3717 l_rec.requesting_office_remarks_flag := p_requesting_office_remarks_fl;
3718 End if;
3719
3720 l_rec.request_number := p_request_number;
3721 l_rec.resign_and_retire_reason_desc := p_resign_and_retire_reason_des;
3722 l_rec.retirement_plan := p_retirement_plan;
3723 l_rec.retirement_plan_desc := p_retirement_plan_desc;
3724 l_rec.second_action_la_code1 := p_second_action_la_code1;
3725 l_rec.second_action_la_code2 := p_second_action_la_code2;
3726 l_rec.second_action_la_desc1 := p_second_action_la_desc1;
3727 l_rec.second_action_la_desc2 := p_second_action_la_desc2;
3728 l_rec.second_noa_cancel_or_correct := p_second_noa_cancel_or_correct;
3729 l_rec.second_noa_code := p_second_noa_code;
3730 l_rec.second_noa_desc := p_second_noa_desc;
3731 l_rec.second_noa_id := p_second_noa_id;
3732 l_rec.second_noa_pa_request_id := p_second_noa_pa_request_id;
3733 l_rec.service_comp_date := p_service_comp_date;
3734 l_rec.status := l_status;
3735 l_rec.supervisory_status := p_supervisory_status;
3736 l_rec.tenure := p_tenure;
3737 l_rec.to_adj_basic_pay := p_to_adj_basic_pay;
3738 l_rec.to_basic_pay := p_to_basic_pay;
3739 l_rec.to_grade_id := p_to_grade_id;
3740 l_rec.to_grade_or_level := p_to_grade_or_level;
3741 l_rec.to_job_id := p_to_job_id;
3742 l_rec.to_locality_adj := p_to_locality_adj;
3743 l_rec.to_occ_code := p_to_occ_code;
3744 l_rec.to_organization_id := p_to_organization_id;
3745 l_rec.to_other_pay_amount := p_to_other_pay_amount;
3746 l_rec.to_au_overtime := p_to_au_overtime;
3747 l_rec.to_auo_premium_pay_indicator := p_to_auo_premium_pay_indicator;
3748 l_rec.to_availability_pay := p_to_availability_pay;
3749 l_rec.to_ap_premium_pay_indicator := p_to_ap_premium_pay_indicator;
3750 l_rec.to_retention_allowance := p_to_retention_allowance;
3751 l_rec.to_supervisory_differential := p_to_supervisory_differential;
3752 l_rec.to_staffing_differential := p_to_staffing_differential;
3753 l_rec.to_pay_basis := p_to_pay_basis;
3754 If p_to_pay_plan = hr_api.g_varchar2 then
3755 l_rec.to_pay_plan := ghr_par_shd.g_old_rec.to_pay_plan;
3756 Else
3757 l_rec.to_pay_plan := p_to_pay_plan;
3758 End if;
3759 -- FWFA Changes Bug# 4444609
3760 l_rec.to_pay_table_identifier := p_to_pay_table_identifier;
3761 -- FWFA Changes
3762 l_rec.to_position_id := p_to_position_id;
3763 l_rec.to_position_org_line1 := p_to_position_org_line1;
3764 l_rec.to_position_org_line2 := p_to_position_org_line2;
3765 l_rec.to_position_org_line3 := p_to_position_org_line3;
3766 l_rec.to_position_org_line4 := p_to_position_org_line4;
3767 l_rec.to_position_org_line5 := p_to_position_org_line5;
3768 l_rec.to_position_org_line6 := p_to_position_org_line6;
3769 l_rec.to_position_number := p_to_position_number;
3770 l_rec.to_position_seq_no := p_to_position_seq_no;
3771 l_rec.to_position_title := p_to_position_title;
3772 l_rec.to_step_or_rate := p_to_step_or_rate;
3773 l_rec.to_total_salary := p_to_total_salary;
3774 l_rec.veterans_preference := p_veterans_preference;
3775 l_rec.veterans_pref_for_rif := p_veterans_pref_for_rif;
3776 l_rec.veterans_status := p_veterans_status;
3777 l_rec.work_schedule := p_work_schedule;
3778 l_rec.work_schedule_desc := p_work_schedule_desc;
3779 If p_year_degree_attained = hr_api.g_number then
3780 l_rec.year_degree_attained := ghr_par_shd.g_old_rec.year_degree_attained;
3781 Else
3782 l_rec.year_degree_attained := p_year_degree_attained;
3783 End if;
3784
3785 l_rec.first_noa_information1 := p_first_noa_information1;
3786 l_rec.first_noa_information2 := p_first_noa_information2;
3787 l_rec.first_noa_information3 := p_first_noa_information3;
3788 l_rec.first_noa_information4 := p_first_noa_information4;
3789 l_rec.first_noa_information5 := p_first_noa_information5;
3790 l_rec.second_lac1_information1 := p_second_lac1_information1;
3791 l_rec.second_lac1_information2 := p_second_lac1_information2;
3792 l_rec.second_lac1_information3 := p_second_lac1_information3;
3793 l_rec.second_lac1_information4 := p_second_lac1_information4;
3794 l_rec.second_lac1_information5 := p_second_lac1_information5;
3795 l_rec.second_lac2_information1 := p_second_lac2_information1;
3796 l_rec.second_lac2_information2 := p_second_lac2_information2;
3797 l_rec.second_lac2_information3 := p_second_lac2_information3;
3798 l_rec.second_lac2_information4 := p_second_lac2_information4;
3799 l_rec.second_lac2_information5 := p_second_lac2_information5;
3800 l_rec.second_noa_information1 := p_second_noa_information1;
3801 l_rec.second_noa_information2 := p_second_noa_information2;
3802 l_rec.second_noa_information3 := p_second_noa_information3;
3803 l_rec.second_noa_information4 := p_second_noa_information4;
3804 l_rec.second_noa_information5 := p_second_noa_information5;
3805 l_rec.first_lac1_information1 := p_first_lac1_information1;
3806 l_rec.first_lac1_information2 := p_first_lac1_information2;
3807 l_rec.first_lac1_information3 := p_first_lac1_information3;
3808 l_rec.first_lac1_information4 := p_first_lac1_information4;
3809 l_rec.first_lac1_information5 := p_first_lac1_information5;
3810 l_rec.first_lac2_information1 := p_first_lac2_information1;
3811 l_rec.first_lac2_information2 := p_first_lac2_information2;
3812 l_rec.first_lac2_information3 := p_first_lac2_information3;
3813 l_rec.first_lac2_information4 := p_first_lac2_information4;
3814 l_rec.first_lac2_information5 := p_first_lac2_information5;
3815 l_rec.attribute_category := p_attribute_category;
3816 l_rec.attribute1 := p_attribute1;
3817 l_rec.attribute2 := p_attribute2;
3818 l_rec.attribute3 := p_attribute3;
3819 l_rec.attribute4 := p_attribute4;
3820 l_rec.attribute5 := p_attribute5;
3821 l_rec.attribute6 := p_attribute6;
3822 l_rec.attribute7 := p_attribute7;
3823 l_rec.attribute8 := p_attribute8;
3824 l_rec.attribute9 := p_attribute9;
3825 l_rec.attribute10 := p_attribute10;
3826 l_rec.attribute11 := p_attribute11;
3827 l_rec.attribute12 := p_attribute12;
3828 l_rec.attribute13 := p_attribute13;
3829 l_rec.attribute14 := p_attribute14;
3830 l_rec.attribute15 := p_attribute15;
3831 l_rec.attribute16 := p_attribute16;
3832 l_rec.attribute17 := p_attribute17;
3833 l_rec.attribute18 := p_attribute18;
3834 l_rec.attribute19 := p_attribute19;
3835 l_rec.attribute20 := p_attribute20;
3836 l_rec.object_version_number := l_par_object_version_number;
3837 If p_to_retention_allow_percentag = hr_api.g_number then
3838 l_rec.to_retention_allow_percentage := ghr_par_shd.g_old_rec.to_retention_allow_percentage;
3839 Else
3840 l_rec.to_retention_allow_percentage := p_to_retention_allow_percentag;
3841 End if;
3842 If p_to_supervisory_diff_percenta = hr_api.g_number then
3843 l_rec.to_supervisory_diff_percentage := ghr_par_shd.g_old_rec.to_supervisory_diff_percentage;
3844 Else
3845 l_rec.to_supervisory_diff_percentage := p_to_supervisory_diff_percenta;
3846 End if;
3847 If p_to_staffing_diff_percentage = hr_api.g_number then
3848 l_rec.to_staffing_diff_percentage := ghr_par_shd.g_old_rec.to_staffing_diff_percentage;
3849 Else
3850 l_rec.to_staffing_diff_percentage := p_to_staffing_diff_percentage;
3851 End if;
3852 If p_award_percentage = hr_api.g_number then
3853 l_rec.award_percentage := ghr_par_shd.g_old_rec.award_percentage;
3854 Else
3855 l_rec.award_percentage := p_award_percentage;
3856 End if;
3857 l_rec.rpa_type := p_rpa_type;
3858 l_rec.mass_action_id := p_mass_action_id;
3859
3860 If p_mass_action_eligible_flag = hr_api.g_varchar2 then
3861 l_rec.mass_action_eligible_flag := ghr_par_shd.g_old_rec.mass_action_eligible_flag;
3862 Else
3863 l_rec.mass_action_eligible_flag := p_mass_action_eligible_flag;
3864 End if;
3865 If p_mass_action_select_flag = hr_api.g_varchar2 then
3866 l_rec.mass_action_select_flag := ghr_par_shd.g_old_rec.mass_action_select_flag;
3867 Else
3868 l_rec.mass_action_eligible_flag := p_mass_action_eligible_flag;
3869 End if;
3870
3871 l_rec.mass_action_comments := p_mass_action_comments;
3872 -- Bug# RRR Changes
3873 hr_utility.set_location('p_payment_option: '||p_payment_option,1);
3874 l_rec.pa_incentive_payment_option := p_payment_option;
3875 hr_utility.set_location('p_award_salary: '||p_award_salary,2);
3876 l_rec.award_salary := p_award_salary;
3877 hr_utility.set_location('After p_award_salary: ',3);
3878 -- Bug# RRR Changes
3879
3880 -- Convert the default values
3881 hr_utility.set_location('after l_rec assignment ',2);
3882 hr_utility.set_location('Before First LAC CODE is : ' ||l_proc || ' ' ||l_rec.first_action_la_code1, 3);
3883 ghr_par_bus.convert_defaults(l_rec);
3884 hr_utility.set_location('After First LAC CODE is : ' ||l_proc || ' ' ||l_rec.first_action_la_code1, 4);
3885
3886 --
3887 -- call update-hr
3888 hr_utility.set_location('l_ovn' || to_char(l_rec.object_version_number),2);
3889
3890 If l_rec.effective_date is null then
3891 hr_utility.set_message(8301,'GHR_38185_EFF_DATE_REQUIRED');
3892 ghr_upd_hr_validation.form_item_name := 'PAR.EFFECTIVE_DATE';
3893 hr_utility.raise_error;
3894 End if;
3895 if (l_action_taken <> 'END_ROUTING') then
3896 ghr_process_sf52.process_sf52
3897 (p_sf52_data => l_rec
3898 ,p_capped_other_pay => p_capped_other_pay
3899 );
3900 end if;
3901 --Begin Bug# 8510411
3902 IF l_rec.noa_family_code ='CORRECT' AND l_rec.to_position_id IS NULL THEN
3903 FOR l_to_pos_id in c_to_pos_id LOOP
3904 l_to_position_id := l_to_pos_id.to_position_id;
3905 END LOOP;
3906 END IF;
3907 --End Bug# 8510411
3908
3909 --Begin Bug# 8653508,9662954
3910 IF p_first_noa_code IN ('730','731','732','930','931','932') OR
3911 p_second_noa_code IN ('730','731','732','930','931','932') THEN
3912 ghr_sf52_api.update_detail_remarks( p_effective_date => l_effective_date,
3913 p_pa_request_id => p_pa_request_id);
3914 END IF;
3915 --End Bug# 8653508,9662954
3916
3917 ghr_sf52_post_update.get_notification_details
3918 (p_pa_request_id => p_pa_request_id,
3919 p_effective_date => p_effective_date,
3920 -- p_object_version_number => p_imm_pa_request_rec.object_version_number,
3921 p_from_position_id => l_rec.from_position_id,
3922 p_to_position_id => NVL(l_rec.to_position_id,l_to_position_id), --Bug# 8510411
3923 p_agency_code => l_rec.agency_code,
3924 p_from_agency_code => l_rec.from_agency_code,
3925 p_from_agency_desc => l_rec.from_agency_desc,
3926 p_from_office_symbol => l_rec.from_office_symbol,
3927 p_personnel_office_id => l_rec.personnel_office_id,
3928 p_employee_dept_or_agency => l_rec.employee_dept_or_agency,
3929 p_to_office_symbol => l_rec.to_office_symbol
3930 );
3931 for ovn_rec in c_ovn loop
3932 l_rec.object_version_number := ovn_rec.object_version_number;
3933 end loop;
3934 hr_utility.set_location('to pos id is '|| l_rec.to_position_id,1);
3935 hr_utility.set_location('first noa code is '|| l_rec.first_noa_code,1);
3936 IF nvl(l_rec.first_noa_code,'9999') <> '002' THEN
3937 ghr_par_upd.upd
3938 (p_pa_request_id => p_pa_request_id,
3939 p_object_version_number => l_rec.object_version_number,
3940 p_from_position_id => l_rec.from_position_id,
3941 p_to_position_id => l_rec.to_position_id,
3942 p_agency_code => l_rec.agency_code,
3943 p_from_agency_code => l_rec.from_agency_code,
3944 p_from_agency_desc => l_rec.from_agency_desc,
3945 p_from_office_symbol => l_rec.from_office_symbol,
3946 p_personnel_office_id => l_rec.personnel_office_id,
3947 p_employee_dept_or_agency => l_rec.employee_dept_or_agency,
3948 p_to_office_symbol => l_rec.to_office_symbol
3949 );
3950 ELSE
3951 ghr_par_upd.upd
3952 (p_pa_request_id => p_pa_request_id,
3953 p_object_version_number => l_rec.object_version_number,
3954 p_from_position_id => l_rec.from_position_id,
3955 p_agency_code => l_rec.agency_code,
3956 p_from_agency_code => l_rec.from_agency_code,
3957 p_from_agency_desc => l_rec.from_agency_desc,
3958 p_from_office_symbol => l_rec.from_office_symbol,
3959 p_personnel_office_id => l_rec.personnel_office_id,
3960 p_employee_dept_or_agency => l_rec.employee_dept_or_agency,
3961 p_to_office_symbol => l_rec.to_office_symbol
3962 );
3963 END IF;
3964 if (l_action_taken = 'END_ROUTING') then
3965 ghr_agency_update.ghr_agency_upd(
3966 p_pa_request_rec => l_rec,
3967 p_asg_sf52 => l_asg_sf52,
3968 p_asg_non_sf52 => l_asg_non_sf52,
3969 p_asg_nte_dates => l_asg_nte_dates,
3970 p_per_sf52 => l_per_sf52,
3971 p_per_group1 => l_per_group1,
3972 p_per_group2 => l_per_group2,
3973 p_per_scd_info => l_per_scd_info,
3974 p_per_retained_grade => l_per_retained_grade,
3975 p_per_probations => l_per_probations,
3976 p_per_sep_retire => l_per_sep_retire,
3977 p_per_security => l_per_security,
3978 p_per_conversions => l_per_conversions,
3979 p_per_uniformed_services => l_per_uniformed_services,
3980 p_pos_oblig => l_pos_oblig,
3981 p_pos_grp2 => l_pos_grp2,
3982 p_pos_grp1 => l_pos_grp1,
3983 p_pos_valid_grade => l_pos_valid_grade,
3984 p_pos_car_prog => l_pos_car_prog,
3985 p_loc_info => l_loc_info,
3986 p_wgi => l_wgi,
3987 p_recruitment_bonus => l_recruitment_bonus,
3988 p_relocation_bonus => l_relocation_bonus,
3989
3990 p_sf52_from_data => l_sf52_from_data,
3991 p_personal_info => l_personal_info,
3992 p_gov_awards_type => l_gov_awards_type,
3993 p_perf_appraisal_type => l_perf_appraisal_type,
3994 p_payroll_type => l_payroll_type,
3995 p_conduct_perf_type => l_conduct_perf_type,
3996 p_agency_sf52 => l_agency_sf52,
3997 p_agency_code => l_agency_code,
3998 p_entitlement => l_imm_entitlement,
3999 p_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay,
4000 p_edp_pay => l_imm_edp_pay,
4001 p_hazard_pay => l_imm_hazard_pay,
4002 p_health_benefits => l_imm_health_benefits,
4003 p_danger_pay => l_imm_danger_pay,
4004 p_imminent_danger_pay => l_imm_imminent_danger_pay,
4005 p_living_quarters_allow => l_imm_living_quarters_allow,
4006 p_post_diff_amt => l_imm_post_diff_amt,
4007 p_post_diff_percent => l_imm_post_diff_percent,
4008 p_sep_maintenance_allow => l_imm_sep_maintenance_allow,
4009 p_supplemental_post_allow => l_imm_supplemental_post_allow,
4010 p_temp_lodge_allow => l_imm_temp_lodge_allow,
4011 p_premium_pay => l_imm_premium_pay,
4012 p_retirement_annuity => l_imm_retirement_annuity,
4013 p_severance_pay => l_imm_severance_pay,
4014 p_thrift_saving_plan => l_imm_thrift_saving_plan,
4015 p_retention_allow_review => l_imm_retention_allow_review,
4016 p_health_ben_pre_tax => l_imm_health_ben_pre_tax,
4017 p_per_benefit_info => l_imm_per_benefit_info,
4018 p_imm_retirement_info => l_imm_retirement_info, --Bug# 7131104
4019 --Begin Bug# 10181661
4020 p_asg_detail_info => l_imm_asg_detail_info,
4021 p_ipa_detail_ben_cont_info => l_imm_ipa_benefits_cont
4022 --End Bug# 10181661
4023 );
4024 ghr_sf52_api.end_sf52( p_pa_request_id => p_pa_request_id,
4025 p_action_taken => 'ENDED',
4026 p_par_object_version_number => l_rec.object_version_number);
4027 end if;
4028 end if;
4029 if (l_action_taken not in ('UPDATE_HR','END_ROUTING')) then
4030 ghr_api.call_workflow
4031 (p_pa_request_id => p_pa_request_id,
4032 p_action_taken => l_action_taken
4033 );
4034 end if;
4035 end if; -- rpa-type = A and NOT_ROUTED --- AVR
4036 end if; -- If template record for mass Award --- AVR
4037 elsif l_action_taken = 'NONE' then
4038 null;
4039 else
4040 hr_utility.set_message(8301,'GHR_38112_INVALID_API');
4041 hr_utility.raise_error;
4042 end if;
4043
4044 If p_print_sf50_flag = 'Y' then
4045
4046 If l_action_taken <> 'UPDATE_HR' then
4047 hr_utility.set_message(8301,'GHR_38399_52_NOT_PROCESSED');
4048 hr_utility.raise_error;
4049 End if;
4050 --Bug#3757201 Added p_back_page parameter
4051 -- Bug #8286910 Need to handle for dual correction
4052 if not(p_first_noa_code = '002' and p_rpa_type = 'DUAL' and p_mass_action_id is not null) then
4053 submit_request_to_print_50
4054 (p_printer_name => p_printer_name,
4055 p_pa_request_id => p_pa_request_id,
4056 p_effective_date => p_effective_date,
4057 p_user_name => p_u_user_name_acted_on,
4058 p_back_page => p_print_back_page
4059 );
4060 else ---Bug #8286910 For Dual Correction need to submit only during second correction
4061 --processing
4062 if p_first_noa_code = '002' and p_rpa_type = 'DUAL' and p_mass_action_id is not null then
4063 --checking for second dual correction
4064 for chk_dual_sec_corr in c_dual_sec_corr
4065 loop
4066 -- Modified as First NPA need to be printed only if First NPA generation is selected
4067 for chk_first_corr_npa in c_first_corr_npa(p_first_pa_req_id => p_mass_action_id)
4068 loop
4069 submit_request_to_print_50
4070 (p_printer_name => p_printer_name,
4071 p_pa_request_id => p_mass_action_id,
4072 p_effective_date => p_effective_date,
4073 p_user_name => p_u_user_name_acted_on,
4074 p_back_page => p_print_back_page
4075 );
4076 end loop;
4077 submit_request_to_print_50
4078 (p_printer_name => p_printer_name,
4079 p_pa_request_id => p_pa_request_id,
4080 p_effective_date => p_effective_date,
4081 p_user_name => p_u_user_name_acted_on,
4082 p_back_page => p_print_back_page
4083 );
4084 end loop;
4085 end if;
4086 end if;
4087 ---Bug #8286910
4088
4089 --6976674
4090 for rec in c_dual_cancel
4091 loop
4092 submit_request_to_print_50
4093 (p_printer_name => p_printer_name,
4094 p_pa_request_id => rec.pa_request_id,
4095 p_effective_date => p_effective_date,
4096 p_user_name => p_u_user_name_acted_on,
4097 p_back_page => p_print_back_page
4098 );
4099 end loop;
4100 --6976674
4101
4102 End if;
4103 --
4104 -- Call After Process User Hook
4105 --
4106 begin
4107 ghr_sf52_bk2.update_sf52_a (
4108 p_noa_family_code => p_noa_family_code,
4109 p_pa_request_id => p_pa_request_id,
4110 p_routing_group_id => p_routing_group_id,
4111 p_proposed_effective_asap_flag => p_proposed_effective_asap_flag,
4112 p_academic_discipline => p_academic_discipline,
4113 p_additional_info_person_id => p_additional_info_person_id,
4114 p_additional_info_tel_number => p_additional_info_tel_number,
4115 p_altered_pa_request_id => p_altered_pa_request_id,
4116 p_annuitant_indicator => p_annuitant_indicator,
4117 p_annuitant_indicator_desc => p_annuitant_indicator_desc,
4118 p_appropriation_code1 => p_appropriation_code1,
4119 p_appropriation_code2 => p_appropriation_code2,
4120 p_authorized_by_person_id => p_authorized_by_person_id,
4121 p_authorized_by_title => p_authorized_by_title,
4122 p_award_amount => p_award_amount,
4123 p_award_uom => p_award_uom,
4124 p_bargaining_unit_status => p_bargaining_unit_status,
4125 p_citizenship => p_citizenship,
4126 p_concurrence_date => p_concurrence_date,
4127 p_custom_pay_calc_flag => p_custom_pay_calc_flag,
4128 p_duty_station_code => p_duty_station_code,
4129 p_duty_station_desc => p_duty_station_desc,
4130 p_duty_station_location_id => p_duty_station_location_id,
4131 p_duty_station_id => p_duty_station_id,
4132 p_education_level => p_education_level,
4133 p_effective_date => p_effective_date,
4134 p_employee_assignment_id => p_employee_assignment_id,
4135 p_employee_date_of_birth => p_employee_date_of_birth,
4136 p_employee_first_name => p_employee_first_name,
4137 p_employee_last_name => p_employee_last_name,
4138 p_employee_middle_names => p_employee_middle_names,
4139 p_employee_national_identifier => p_employee_national_identifier,
4140 p_fegli => p_fegli,
4141 p_fegli_desc => p_fegli_desc,
4142 p_first_action_la_code1 => p_first_action_la_code1,
4143 p_first_action_la_code2 => p_first_action_la_code2,
4144 p_first_action_la_desc1 => p_first_action_la_desc1,
4145 p_first_action_la_desc2 => p_first_action_la_desc2,
4146 p_first_noa_cancel_or_correct => p_first_noa_cancel_or_correct,
4147 p_first_noa_id => p_first_noa_id,
4148 p_first_noa_code => p_first_noa_code,
4149 p_first_noa_desc => p_first_noa_desc,
4150 p_first_noa_pa_request_id => p_first_noa_pa_request_id,
4151 p_flsa_category => p_flsa_category,
4152 p_forwarding_address_line1 => p_forwarding_address_line1,
4153 p_forwarding_address_line2 => p_forwarding_address_line2,
4154 p_forwarding_address_line3 => p_forwarding_address_line3,
4155 p_forwarding_country => p_forwarding_country,
4156 p_forwarding_country_short_nam => p_forwarding_country_short_nam,
4157 p_forwarding_postal_code => p_forwarding_postal_code,
4158 p_forwarding_region_2 => p_forwarding_region_2,
4159 p_forwarding_town_or_city => p_forwarding_town_or_city ,
4160 p_from_adj_basic_pay => p_from_adj_basic_pay,
4161 p_from_basic_pay => p_from_basic_pay,
4162 p_from_grade_or_level => p_from_grade_or_level,
4163 p_from_locality_adj => p_from_locality_adj,
4164 p_from_occ_code => p_from_occ_code,
4165 p_from_other_pay_amount => p_from_other_pay_amount,
4166 p_from_pay_basis => p_from_pay_basis,
4167 p_from_pay_plan => p_from_pay_plan,
4168 -- FWFA Changes Bug#4444609
4169 -- p_input_pay_rate_determinant => p_input_pay_rate_determinant,
4170 -- p_from_pay_table_identifier => p_from_pay_table_identifier,
4171 -- FWFA Changes
4172 p_from_position_id => p_from_position_id,
4173 p_from_position_org_line1 => p_from_position_org_line1,
4174 p_from_position_org_line2 => p_from_position_org_line2,
4175 p_from_position_org_line3 => p_from_position_org_line3,
4176 p_from_position_org_line4 => p_from_position_org_line4,
4177 p_from_position_org_line5 => p_from_position_org_line5,
4178 p_from_position_org_line6 => p_from_position_org_line6,
4179 p_from_position_number => p_from_position_number,
4180 p_from_position_seq_no => p_from_position_seq_no,
4181 p_from_position_title => p_from_position_title,
4182 p_from_step_or_rate => p_from_step_or_rate,
4183 p_from_total_salary => p_from_total_salary,
4184 p_functional_class => p_functional_class,
4185 p_notepad => p_notepad,
4186 p_part_time_hours => p_part_time_hours,
4187 p_pay_rate_determinant => p_pay_rate_determinant,
4188 p_person_id => p_person_id,
4189 p_position_occupied => p_position_occupied,
4190 p_proposed_effective_date => p_proposed_effective_date,
4191 p_requested_by_person_id => p_requested_by_person_id,
4192 p_requested_by_title => p_requested_by_title,
4193 p_requested_date => p_requested_date,
4194 p_requesting_office_remarks_de => p_requesting_office_remarks_de,
4195 p_requesting_office_remarks_fl => p_requesting_office_remarks_fl,
4196 p_request_number => p_request_number,
4197 p_resign_and_retire_reason_des => p_resign_and_retire_reason_des,
4198 p_retirement_plan => p_retirement_plan,
4199 p_retirement_plan_desc => p_retirement_plan_desc,
4200 p_second_action_la_code1 => p_second_action_la_code1,
4201 p_second_action_la_code2 => p_second_action_la_code2,
4202 p_second_action_la_desc1 => p_second_action_la_desc1,
4203 p_second_action_la_desc2 => p_second_action_la_desc2,
4204 p_second_noa_cancel_or_correct => p_second_noa_cancel_or_correct,
4205 p_second_noa_code => p_second_noa_code,
4206 p_second_noa_desc => p_second_noa_desc,
4207 p_second_noa_id => p_second_noa_id,
4208 p_second_noa_pa_request_id => p_second_noa_pa_request_id,
4209 p_service_comp_date => p_service_comp_date,
4210 p_supervisory_status => p_supervisory_status,
4211 p_tenure => p_tenure,
4212 p_to_adj_basic_pay => p_to_adj_basic_pay,
4213 p_to_basic_pay => p_to_basic_pay,
4214 p_to_grade_id => p_to_grade_id,
4215 p_to_grade_or_level => p_to_grade_or_level,
4216 p_to_job_id => p_to_job_id,
4217 p_to_locality_adj => p_to_locality_adj,
4218 p_to_occ_code => p_to_occ_code,
4219 p_to_organization_id => p_to_organization_id,
4220 p_to_other_pay_amount => p_to_other_pay_amount,
4221 p_to_au_overtime => p_to_au_overtime,
4222 p_to_auo_premium_pay_indicator => p_to_auo_premium_pay_indicator,
4223 p_to_availability_pay => p_to_availability_pay,
4224 p_to_ap_premium_pay_indicator => p_to_ap_premium_pay_indicator,
4225 p_to_retention_allowance => p_to_retention_allowance,
4226 p_to_supervisory_differential => p_to_supervisory_differential,
4227 p_to_staffing_differential => p_to_staffing_differential,
4228 p_to_pay_basis => p_to_pay_basis,
4229 p_to_pay_plan => p_to_pay_plan,
4230 -- FWFA Changes Bug#4444609
4231 -- p_to_pay_table_identifier => p_to_pay_table_identifier,
4232 -- FWFA Changes
4233 p_to_position_id => p_to_position_id,
4234 p_to_position_org_line1 => p_to_position_org_line1,
4235 p_to_position_org_line2 => p_to_position_org_line2,
4236 p_to_position_org_line3 => p_to_position_org_line3,
4237 p_to_position_org_line4 => p_to_position_org_line4,
4238 p_to_position_org_line5 => p_to_position_org_line5,
4239 p_to_position_org_line6 => p_to_position_org_line6,
4240 p_to_position_number => p_to_position_number,
4241 p_to_position_seq_no => p_to_position_seq_no,
4242 p_to_position_title => p_to_position_title,
4243 p_to_step_or_rate => p_to_step_or_rate,
4244 p_to_total_salary => p_to_total_salary,
4245 p_veterans_pref_for_rif => p_veterans_pref_for_rif,
4246 p_veterans_preference => p_veterans_preference,
4247 p_veterans_status => p_veterans_status,
4248 p_work_schedule => p_work_schedule,
4249 p_work_schedule_desc => p_work_schedule_desc,
4250 p_year_degree_attained => p_year_degree_attained,
4251 p_first_noa_information1 => p_first_noa_information1,
4252 p_first_noa_information2 => p_first_noa_information2,
4253 p_first_noa_information3 => p_first_noa_information3,
4254 p_first_noa_information4 => p_first_noa_information4,
4255 p_first_noa_information5 => p_first_noa_information5,
4256 p_second_lac1_information1 => p_second_lac1_information1,
4257 p_second_lac1_information2 => p_second_lac1_information2,
4258 p_second_lac1_information3 => p_second_lac1_information3,
4259 p_second_lac1_information4 => p_second_lac1_information4,
4260 p_second_lac1_information5 => p_second_lac1_information5,
4261 p_second_lac2_information1 => p_second_lac2_information1,
4262 p_second_lac2_information2 => p_second_lac2_information2,
4263 p_second_lac2_information3 => p_second_lac2_information3,
4264 p_second_lac2_information4 => p_second_lac2_information4,
4265 p_second_lac2_information5 => p_second_lac2_information5,
4266 p_second_noa_information1 => p_second_noa_information1,
4267 p_second_noa_information2 => p_second_noa_information2,
4268 p_second_noa_information3 => p_second_noa_information3,
4269 p_second_noa_information4 => p_second_noa_information4,
4270 p_second_noa_information5 => p_second_noa_information5,
4271 p_first_lac1_information1 => p_first_lac1_information1,
4272 p_first_lac1_information2 => p_first_lac1_information2,
4273 p_first_lac1_information3 => p_first_lac1_information3,
4274 p_first_lac1_information4 => p_first_lac1_information4,
4275 p_first_lac1_information5 => p_first_lac1_information5,
4276 p_first_lac2_information1 => p_first_lac2_information1,
4277 p_first_lac2_information2 => p_first_lac2_information2,
4278 p_first_lac2_information3 => p_first_lac2_information3,
4279 p_first_lac2_information4 => p_first_lac2_information4,
4280 p_first_lac2_information5 => p_first_lac2_information5,
4281 p_attribute_category => p_attribute_category,
4282 p_attribute1 => p_attribute1,
4283 p_attribute2 => p_attribute2,
4284 p_attribute3 => p_attribute3,
4285 p_attribute4 => p_attribute4,
4286 p_attribute5 => p_attribute5,
4287 p_attribute6 => p_attribute6,
4288 p_attribute7 => p_attribute7,
4289 p_attribute8 => p_attribute8,
4290 p_attribute9 => p_attribute9,
4291 p_attribute10 => p_attribute10,
4292 p_attribute11 => p_attribute11,
4293 p_attribute12 => p_attribute12,
4294 p_attribute13 => p_attribute13,
4295 p_attribute14 => p_attribute14,
4296 p_attribute15 => p_attribute15,
4297 p_attribute16 => p_attribute16,
4298 p_attribute17 => p_attribute17,
4299 p_attribute18 => p_attribute18,
4300 p_attribute19 => p_attribute19,
4301 p_attribute20 => p_attribute20,
4302 p_print_sf50_flag => p_print_sf50_flag,
4303 p_printer_name => p_printer_name,
4304 p_u_attachment_modified_flag => p_u_attachment_modified_flag,
4305 p_u_approved_flag => p_u_approved_flag,
4306 p_u_user_name_acted_on => p_u_user_name_acted_on,
4307 p_u_action_taken => p_u_action_taken,
4308 p_u_approval_status => p_u_approval_status,
4309 p_i_user_name_routed_to => p_i_user_name_routed_to,
4310 p_i_groupbox_id => p_i_groupbox_id,
4311 p_i_routing_list_id => p_i_routing_list_id,
4312 p_i_routing_seq_number => p_i_routing_seq_number,
4313 p_capped_other_pay => p_capped_other_pay,
4314 p_i_pa_routing_history_id => l_i_pa_routing_history_id,
4315 p_i_prh_object_version_number => l_i_prh_object_version_number,
4316 p_par_object_version_number => l_par_object_version_number,
4317 p_to_retention_allow_percentag => p_to_retention_allow_percentag,
4318 p_to_supervisory_diff_percenta => p_to_supervisory_diff_percenta,
4319 p_to_staffing_diff_percentage => p_to_staffing_diff_percentage,
4320 p_award_percentage => p_award_percentage,
4321 p_rpa_type => p_rpa_type,
4322 p_mass_action_id => p_mass_action_id,
4323 p_mass_action_eligible_flag => p_mass_action_eligible_flag,
4324 p_mass_action_select_flag => p_mass_action_select_flag,
4325 p_mass_action_comments => p_mass_action_comments
4326 );
4327 exception
4328 when hr_api.cannot_find_prog_unit then
4329 hr_api.cannot_find_prog_unit_error
4330 (p_module_name => 'update_sf52',
4331 p_hook_type => 'AP'
4332 );
4333 end;
4334 --
4335 -- End of After Process User Hook call
4336 --
4337 -- When in validation only mode raise the Validate_Enabled exception
4338 --
4339 if p_validate then
4340 raise hr_api.validate_enabled;
4341 end if;
4342
4343 --
4344 -- Set all output arguments
4345 --
4346 p_i_pa_routing_history_id := l_i_pa_routing_history_id;
4347 p_i_prh_object_version_number := l_i_prh_object_version_number;
4348 p_par_object_version_number := l_par_object_version_number;
4349 --
4350 hr_utility.set_location(' Leaving:'||l_proc, 11);
4351 exception
4352 when hr_api.validate_enabled then
4353 --
4354 -- As the Validate_Enabled exception has been raised
4355 -- we must rollback to the savepoint
4356 --
4357 ROLLBACK TO update_sf52;
4358 --
4359 -- Only set output warning arguments
4360 -- (Any key or derived arguments must be set to null
4361 -- when validation only mode is being used.)
4362 --
4363 p_par_object_version_number := l_par_object_version_number;
4364 p_u_prh_object_version_number := null;
4365 p_i_pa_routing_history_id := null;
4366 p_i_prh_object_version_number := l_i_prh_object_version_number;
4367 When others then
4368 Rollback to update_sf52;
4369 --
4370 -- Reset IN OUT parameters and set OUT parameters
4371 --
4372 p_par_object_version_number := l_par_object_version_number;
4373 p_u_prh_object_version_number := null;
4374 p_i_pa_routing_history_id := null;
4375 p_i_prh_object_version_number := l_i_prh_object_version_number;
4376 Raise;
4377
4378 hr_utility.set_location(' Leaving:'||l_proc, 12);
4379
4380 end update_sf52;
4381
4382 -- ----------------------------------------------------------------------------
4383 -- |--------------------------< end_sf52>--------------------------|
4384 -- ----------------------------------------------------------------------------
4385
4386 procedure end_sf52
4387 (p_validate in boolean default false,
4388 p_pa_request_id in number,
4389 p_user_name in varchar2 default hr_api.g_varchar2,
4390 p_action_taken in varchar2,
4391 p_altered_pa_request_id in number default null,
4392 p_first_noa_code in varchar2 default null,
4393 p_second_noa_code in varchar2 default null,
4394 p_par_object_version_number in out nocopy number
4395 )is
4396
4397 l_proc varchar2(72) := g_package||'end_sf52';
4398 l_effective_date date;
4399 l_prh_rec ghr_pa_routing_history%rowtype;
4400 l_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%TYPE;
4401 l_count number;
4402 l_routing_group_id ghr_pa_requests.routing_group_id%TYPE;
4403 l_user_name ghr_pa_routing_history.user_name%type;
4404 l_old_action_taken ghr_pa_routing_history.action_taken%type;
4405 l_initiator_flag ghr_pa_routing_history.initiator_flag%TYPE;
4406 l_requester_flag ghr_pa_routing_history.requester_flag%TYPE;
4407 l_authorizer_flag ghr_pa_routing_history.authorizer_flag%TYPE;
4408 l_approver_flag ghr_pa_routing_history.approver_flag%TYPE;
4409 l_reviewer_flag ghr_pa_routing_history.reviewer_flag%TYPE;
4410 l_personnelist_flag ghr_pa_routing_history.personnelist_flag%TYPE;
4411 l_user_name_employee_id ghr_pa_routing_history.user_name_employee_id%TYPE;
4412 l_user_name_emp_first_name ghr_pa_routing_history.user_name_emp_first_name%TYPE;
4413 l_user_name_emp_last_name ghr_pa_routing_history.user_name_emp_last_name%TYPE;
4414 l_user_name_emp_middle_names ghr_pa_routing_history.user_name_emp_middle_names%TYPE;
4415 l_prh_object_version_number ghr_pa_routing_history.object_version_number%TYPE;
4416 l_par_object_version_number ghr_pa_routing_history.object_version_number%TYPE;
4417 l_result boolean;
4418 l_notepad ghr_pa_routing_history.notepad%type;
4419 l_noa_family_code ghr_pa_routing_history.noa_family_code%type;
4420 l_first_noa_id ghr_pa_routing_history.nature_of_action_id%type;
4421 l_Second_noa_id ghr_pa_routing_history.second_nature_of_action_id%type;
4422 l_groupbox_id ghr_pa_routing_history.groupbox_id%type;
4423 l_routing_list_id ghr_pa_routing_history.routing_list_id%type;
4424 l_routing_seq_number ghr_pa_routing_history.routing_seq_number%type;
4425
4426
4427
4428 cursor c_count_history is
4429 select count(*) cnt
4430 from ghr_pa_routing_history prh
4431 where prh.pa_request_id = p_pa_request_id;
4432
4433
4434 cursor C_routing_history_id is
4435 select prh.pa_routing_history_id,
4436 prh.pa_request_id,
4437 -- ATTACHMENT_MODIFIED_FLAG
4438 prh.initiator_flag,
4439 prh.requester_flag,
4440 prh.authorizer_flag,
4441 prh.personnelist_flag,
4442 prh.approver_flag,
4443 prh.reviewer_flag,
4444 -- prh.approved_flag,
4445 prh.user_name,
4446 prh.user_name_employee_id,
4447 prh.user_name_emp_first_name,
4448 prh.user_name_emp_last_name,
4449 prh.user_name_emp_middle_names,
4450 prh.notepad,
4451 prh.action_taken,
4452 prh.groupbox_id,
4453 prh.routing_list_id,
4454 prh.routing_seq_number,
4455 prh.nature_of_action_id,
4456 prh.noa_family_code,
4457 prh.second_nature_of_action_id,
4458 prh.object_version_number
4459 -- prh.approval_status
4460 from ghr_pa_routing_history prh
4461 where prh.pa_request_id = p_pa_request_id
4462 Order by prh.pa_routing_history_id desc;
4463
4464
4465
4466 cursor c_request_details is
4467 select par.effective_date,
4468 par.routing_group_id
4469 from ghr_pa_requests par
4470 where par.pa_request_id = p_pa_request_id;
4471
4472
4473 /* cursor c_names is
4474 select usr.employee_id,
4475 per.first_name,
4476 per.last_name,
4477 per.middle_names
4478 from fnd_user usr,
4479 per_people_f per
4480 where upper(l_user_name) = upper(usr.user_name)
4481 and per.person_id = usr.employee_id
4482 and l_effective_date
4483 between effective_start_date
4484 and effective_end_date; */
4485 -- Bug 4863608 Perf. Repository changes
4486 --8229939 modified to consider sysdate
4487 CURSOR c_names is
4488 SELECT usr.employee_id,
4489 per.first_name,
4490 per.last_name,
4491 per.middle_names
4492 FROM fnd_user usr,
4493 per_people_f per
4494 WHERE usr.user_name = UPPER(l_user_name)
4495 AND per.person_id = usr.employee_id
4496 AND trunc(sysdate)
4497 BETWEEN effective_start_date
4498 AND effective_end_date;
4499
4500
4501 begin
4502 hr_utility.set_location('Entering:'|| l_proc, 5);
4503 --
4504 -- Issue a savepoint
4505 --
4506 savepoint end_sf52;
4507 --
4508 -- Call Before Process User Hook
4509 --
4510 begin
4511 ghr_sf52_bk3.end_sf52_b (
4512 p_pa_request_id => p_pa_request_id,
4513 p_user_name => p_user_name,
4514 p_action_taken => p_action_taken,
4515 p_altered_pa_request_id => p_altered_pa_request_id,
4516 p_first_noa_code => p_first_noa_code,
4517 p_second_noa_code => p_second_noa_code,
4518 p_par_object_version_number => p_par_object_version_number
4519 );
4520 exception
4521 when hr_api.cannot_find_prog_unit then
4522 hr_api.cannot_find_prog_unit_error
4523 (p_module_name => 'end_sf52',
4524 p_hook_type => 'BP'
4525 );
4526 end;
4527 --
4528 -- End of Before Process User Hook call
4529 --
4530 hr_utility.set_location(l_proc, 6);
4531 l_par_object_version_number := p_par_object_version_number;
4532
4533
4534 If p_first_noa_code in ('001','002') then
4535 ghr_sf52_api.Cancel_Cancor
4536 (p_altered_pa_request_id => p_altered_pa_request_id,
4537 p_noa_code_correct => p_second_noa_code,
4538 p_result => l_result
4539 );
4540 End if;
4541
4542 If not l_result then
4543 hr_utility.set_message(8301,'GHR_38264_INV_PA_REQ_CAN_COR');
4544 hr_utility.raise_error;
4545 End if;
4546
4547 /***dk***/
4548 -- Added ENDED to the following if.
4549 if nvl(p_action_taken,hr_api.g_varchar2) in ('CANCELED','UPDATE_HR_COMPLETE','ENDED') then
4550
4551 for request_details in c_request_details loop
4552 l_effective_date := trunc(request_details.effective_date);
4553 l_routing_group_id := request_details.routing_group_id;
4554 end loop;
4555
4556 if l_effective_date is null then
4557 l_effective_date := trunc(sysdate);
4558 end if;
4559
4560 ghr_par_upd.upd
4561 (p_pa_request_id => p_pa_request_id
4562 ,p_status => p_action_taken
4563 ,p_object_version_number => p_par_object_version_number
4564 );
4565 /***dk***/
4566 -- Added ENDED to the following if.
4567 If p_action_taken IN ('UPDATE_HR_COMPLETE','ENDED') then
4568 for cur_routing_history in C_routing_history_id loop
4569 l_pa_routing_history_id := cur_routing_history.pa_routing_history_id;
4570 l_user_name := cur_routing_history.user_name;
4571 l_old_action_taken := cur_routing_history.action_taken;
4572 l_prh_object_version_number := cur_routing_history.object_version_number;
4573 l_initiator_flag := cur_routing_history.initiator_flag;
4574 l_requester_flag := cur_routing_history.requester_flag;
4575 l_authorizer_flag := cur_routing_history.authorizer_flag;
4576 l_personnelist_flag := cur_routing_history.personnelist_flag;
4577 l_approver_flag := cur_routing_history.approver_flag;
4578 l_reviewer_flag := cur_routing_history.reviewer_flag;
4579 l_user_name := cur_routing_history.user_name;
4580 l_USER_NAME_EMPLOYEE_ID := cur_routing_history.USER_NAME_EMPLOYEE_ID;
4581 l_USER_NAME_EMP_FIRST_NAME := cur_routing_history.USER_NAME_emp_first_name;
4582 l_USER_NAME_EMP_LAST_NAME := cur_routing_history.USER_NAME_emp_last_name;
4583 l_USER_NAME_EMP_MIDDLE_NAMES := cur_routing_history.USER_NAME_emp_middle_names;
4584 l_GROUPBOX_ID := cur_routing_history.groupbox_id;
4585 l_ROUTING_LIST_ID := cur_routing_history.routing_list_id;
4586 l_ROUTING_SEQ_NUMBER := cur_routing_history.routing_seq_number;
4587 l_first_noa_id := cur_routing_history.nature_of_action_id;
4588 l_NOA_FAMILY_CODE := cur_routing_history.noa_family_code;
4589 l_SECOND_noa_id := cur_routing_history.second_nature_of_action_id;
4590 exit;
4591 end loop;
4592 ghr_prh_ins.ins
4593 (
4594 p_pa_routing_history_id => l_pa_routing_history_id,
4595 p_pa_request_id => p_pa_request_id,
4596 p_attachment_modified_flag => 'N',
4597 p_initiator_flag => nvl(l_initiator_flag,'N'),
4598 p_approver_flag => nvl(l_approver_flag,'N'),
4599 p_reviewer_flag => nvl(l_reviewer_flag,'N') ,
4600 p_requester_flag => nvl(l_requester_flag,'N') ,
4601 p_authorizer_flag => nvl(l_authorizer_flag,'N'),
4602 p_approved_flag => 'N',
4603 p_personnelist_flag => nvl(l_personnelist_flag,'N'),
4604 p_user_name => l_user_name,
4605 p_user_name_employee_id => l_user_name_employee_id,
4606 p_user_name_emp_first_name => l_user_name_emp_first_name,
4607 p_user_name_emp_last_name => l_user_name_emp_last_name ,
4608 p_user_name_emp_middle_names => l_user_name_emp_middle_names,
4609 p_action_taken => p_action_taken,
4610 p_groupbox_id => l_groupbox_id,
4611 p_routing_list_id => l_routing_list_id,
4612 p_routing_seq_number => l_routing_seq_number,
4613 p_noa_family_code => l_noa_family_code,
4614 p_nature_of_action_id => l_first_noa_id,
4615 p_second_nature_of_action_id => l_second_noa_id,
4616 p_object_version_number => l_prh_object_version_number
4617 );
4618
4619
4620 Elsif p_action_taken = 'CANCELED' then
4621 for cur_routing_history in C_routing_history_id loop
4622 l_pa_routing_history_id := cur_routing_history.pa_routing_history_id;
4623 l_user_name := cur_routing_history.user_name;
4624 l_old_action_taken := cur_routing_history.action_taken;
4625 l_prh_object_version_number := cur_routing_history.object_version_number;
4626 exit;
4627 end loop;
4628
4629 -- If the Form calls end_sf52 directly to Cancel an SF52, then
4630 -- the user_name (acted_on) has to be passed. Therefore the one from
4631 -- the database may not necessarily be the correct one., in which case the p_user_name should be further used.
4632
4633 If nvl(p_user_name,hr_api.g_varchar2) <> hr_api.g_varchar2 then
4634 l_user_name := p_user_name;
4635 End if;
4636
4637 -- The foll. was Removed on 08/16 as per Vikram
4638
4639 /* If l_user_name is null then
4640 hr_utility.set_message(8301,'GHR_38111_USER_NAME_REQD');
4641 hr_utility.raise_error;
4642 End if;
4643 */
4644
4645
4646 If l_user_name is not null then
4647 ghr_pa_requests_pkg.get_roles
4648 (p_pa_request_id,
4649 l_routing_group_id,
4650 l_user_name,
4651 l_initiator_flag,
4652 l_requester_flag,
4653 l_authorizer_flag,
4654 l_personnelist_flag,
4655 l_approver_flag,
4656 l_reviewer_flag
4657 );
4658
4659 for name_rec in C_names loop
4660 l_user_name_employee_id := name_rec.employee_id ;
4661 l_user_name_emp_first_name := name_rec.first_name;
4662 l_user_name_emp_last_name := name_rec.last_name;
4663 l_user_name_emp_middle_names := name_rec.middle_names;
4664 exit;
4665 end loop;
4666 end if;
4667
4668 hr_utility.set_location('before upd to prh',1);
4669 hr_utility.set_location('emp id ' || to_char(l_user_name_employee_id),1);
4670
4671 ghr_prh_upd.upd
4672 (p_pa_routing_history_id => l_pa_routing_history_id
4673 ,p_user_name => l_user_name
4674 ,p_initiator_flag => nvl(l_initiator_flag,'N')
4675 ,p_requester_flag => nvl(l_requester_flag,'N')
4676 ,p_authorizer_flag => nvl(l_authorizer_flag,'N')
4677 ,p_personnelist_flag => nvl(l_personnelist_flag,'N')
4678 ,p_approver_flag => nvl(l_approver_flag,'N')
4679 ,p_reviewer_flag => nvl(l_reviewer_flag,'N')
4680 ,p_user_name_employee_id => l_user_name_employee_id
4681 ,p_user_name_emp_first_name => l_user_name_emp_first_name
4682 ,p_user_name_emp_last_name => l_user_name_emp_last_name
4683 ,p_user_name_emp_middle_names => l_user_name_emp_middle_names
4684 ,p_action_taken => p_action_taken
4685 ,p_object_version_number => l_prh_object_version_number
4686 );
4687 End if;
4688
4689
4690 -- call work flow
4691 ghr_api.call_workflow
4692 (p_pa_request_id => p_pa_Request_id,
4693 p_action_taken => p_action_taken,
4694 p_old_action_taken => l_old_action_taken
4695 );
4696
4697 else
4698 hr_utility.set_location('action_taken: ' || p_action_taken ,1);
4699 hr_utility.set_message(8301,'GH_38112_INVALID_API');
4700 hr_utility.raise_error;
4701 end if;
4702
4703 --
4704 -- Call After Process User Hook
4705 --
4706 begin
4707 ghr_sf52_bk3.end_sf52_a (
4708 p_pa_request_id => p_pa_request_id,
4709 p_user_name => p_user_name,
4710 p_action_taken => p_action_taken,
4711 p_altered_pa_request_id => p_altered_pa_request_id,
4712 p_first_noa_code => p_first_noa_code,
4713 p_second_noa_code => p_second_noa_code,
4714 p_par_object_version_number => l_par_object_version_number
4715 );
4716 exception
4717 when hr_api.cannot_find_prog_unit then
4718 hr_api.cannot_find_prog_unit_error
4719 (p_module_name => 'end_sf52',
4720 p_hook_type => 'AP'
4721 );
4722 end;
4723 --
4724 -- End of After Process User Hook call
4725 --
4726 if p_validate then
4727 raise hr_api.validate_enabled;
4728 end if;
4729
4730
4731 -- Set all output arguments
4732 --
4733
4734 hr_utility.set_location(' Leaving:'||l_proc, 11);
4735 exception
4736 when hr_api.validate_enabled then
4737 --
4738 -- As the Validate_Enabled exception has been raised
4739 -- we must rollback to the savepoint
4740 --
4741 ROLLBACK TO end_sf52;
4742 --
4743 -- Only set output warning arguments
4744 -- (Any key or derived arguments must be set to null
4745 -- when validation only mode is being used.)
4746 --
4747 p_par_object_version_number := l_par_object_version_number;
4748
4749 When others then
4750 Rollback to end_sf52;
4751 --
4752 -- Reset IN OUT parameters and set OUT parameters
4753 --
4754 p_par_object_version_number := l_par_object_version_number;
4755 Raise;
4756
4757 hr_utility.set_location(' Leaving:'||l_proc, 12);
4758 end end_sf52;
4759
4760
4761
4762 -- Procedure Cancel_cancor marks the request available for cancellation/correction.
4763 -- This procedure is called when user cancel's the request from Inbox or from sf52
4764 --
4765
4766 Procedure Cancel_Cancor
4767 (p_altered_pa_request_id in number,
4768 p_noa_code_correct in varchar2,
4769 p_result out nocopy boolean
4770 )
4771 is
4772
4773 Cursor c_get_req ( cp_pa_request_id number) is
4774 select rowid row_id,
4775 first_noa_code,
4776 second_noa_code,
4777 object_version_number
4778 from ghr_pa_requests
4779 where pa_request_id = cp_pa_request_id
4780 for update;
4781
4782 l_req c_get_req%rowtype;
4783 l_proc varchar2(30):='Cancel_corcan';
4784 l_object_version_number number;
4785
4786 Begin
4787 hr_utility.set_location( 'entering :' || l_proc , 10);
4788 for get_req in c_get_req(p_altered_pa_request_id) loop
4789 l_req.first_noa_code := get_Req.first_noa_code;
4790 l_req.second_noa_code := get_req.second_noa_code;
4791 l_object_version_number := get_req.object_version_number;
4792 if l_object_version_number is null then
4793 hr_utility.set_location( 'not found ' || l_proc, 20);
4794 p_result := FALSE;
4795 else
4796 if l_req.first_noa_code = p_noa_code_correct then
4797 hr_utility.set_location( 'first noa ' || l_proc, 30);
4798 ghr_par_upd.upd
4799 (p_pa_request_id => p_altered_pa_request_id,
4800 p_object_version_number => l_object_version_number,
4801 -- p_first_noa_cancel_or_correct => null,
4802 p_first_noa_canc_pa_request_id => null
4803 );
4804
4805 elsif l_req.second_noa_code = p_noa_code_correct then
4806 hr_utility.set_location( '2nd NOA ' || l_proc, 60);
4807 ghr_par_upd.upd
4808 (p_pa_request_id => p_altered_pa_request_id,
4809 p_object_version_number => l_object_version_number,
4810 -- p_second_noa_cancel_or_correct => null,
4811 p_second_noa_canc_pa_request_i=> null
4812 );
4813 else
4814 hr_utility.set_location( 'not found ' || l_proc, 90);
4815 p_result := FALSE;
4816 end if;
4817 end if;
4818 end loop;
4819 hr_utility.set_location( 'Leaving :' || l_proc, 100);
4820 Exception when others then
4821 --
4822 -- Reset IN OUT parameters and set OUT parameters
4823 --
4824 p_result := null;
4825 raise;
4826 End Cancel_Cancor;
4827
4828 --Bug#3757201 Added p_back_page parameter
4829 Procedure submit_request_to_print_50
4830 (p_printer_name in varchar2,
4831 p_pa_request_id in ghr_pa_requests.pa_request_id%type,
4832 p_effective_date in date,
4833 p_user_name in varchar2,
4834 p_back_page in varchar2
4835 )
4836 is
4837
4838 l_proc varchar2(72) := g_package || 'print_sf50';
4839 l_set_print_options_status boolean;
4840 l_request_status number(15);
4841 l_user_id fnd_user.user_id%type;
4842
4843 Cursor c_user_id is
4844 select user_id
4845 from fnd_user
4846 where user_name = p_user_name;
4847
4848 begin
4849 If trunc(p_effective_date) > trunc(sysdate) then
4850 hr_utility.set_message(8301,'GHR_38400_NO_50_FOR_FUT_ACT');
4851 hr_utility.raise_error;
4852 End if;
4853
4854 If p_printer_name is null then
4855 hr_utility.set_message(8301,'GHR_38394_NO_PRINTER');
4856 hr_utility.raise_error;
4857 Else
4858 l_set_print_options_status := fnd_request.set_print_options
4859 (PRINTER => p_printer_name
4860 ,STYLE => null
4861 ,COPIES => 1
4862 ,SAVE_OUTPUT => TRUE
4863 ,PRINT_TOGETHER => 'N'
4864 );
4865 If not l_set_print_options_status THEN
4866 hr_utility.set_message(8301,'GHR_38190_FAIL_SET_PRINT_OPT');
4867 hr_utility.raise_error;
4868 Else
4869 for user_id in c_user_id loop
4870 l_user_id := user_id.user_id;
4871 end loop;
4872 l_request_status := fnd_request.submit_request
4873 (
4874 APPLICATION => 'GHR'
4875 ,PROGRAM => 'GHRSF50'
4876 ,DESCRIPTION => null
4877 ,START_TIME => null
4878 ,SUB_REQUEST => null
4879 ,ARGUMENT1 => p_pa_request_id
4880 ,ARGUMENT2 => l_user_id
4881 ,ARGUMENT3 => 'Y'
4882 ,ARGUMENT4 => p_back_page
4883 );
4884 IF l_request_status = 0 THEN
4885 null;
4886 --hr_utiltity.set_message('error submitting the request');
4887 -- hr_utility.raise_error;
4888 Else
4889 commit;
4890 End if;
4891 End if;
4892 End if;
4893 End submit_request_to_print_50;
4894
4895
4896 Procedure get_par_status
4897 (p_effective_date in date,
4898 p_approval_date in date,
4899 p_requested_by_person_id in number,
4900 p_authorized_by_person_id in number,
4901 p_action_taken in varchar2,
4902 --8279908
4903 p_pa_request_id in number,
4904 p_status out nocopy varchar2
4905 )
4906 is
4907 Begin
4908 If nvl(p_action_taken,hr_api.g_varchar2) = 'CANCELED' or nvl(p_action_taken,hr_api.g_varchar2) = 'UPDATE_HR_COMPLETE'
4909 or nvl(p_action_taken,hr_api.g_varchar2) = 'UPDATE_HR' then
4910 p_status := p_action_taken;
4911 If nvl(p_action_taken,hr_api.g_varchar2) = 'UPDATE_HR' and trunc(nvl(p_effective_date,hr_api.g_date)) > sysdate then
4912 p_status := 'FUTURE_ACTION';
4913 End if;
4914 Elsif nvl(p_approval_date,hr_api.g_date) <> hr_api.g_date
4915 --8279908 added comparison for pa request id
4916 or (nvl(ghr_par_shd.g_old_rec.approval_date,hr_api.g_date) <> hr_api.g_date
4917 and ghr_par_shd.g_old_rec.pa_request_id = p_pa_request_id) then
4918 p_status := 'APPROVED';
4919 Elsif p_authorized_by_person_id is not null then
4920 p_status := 'AUTHORIZED';
4921 Elsif p_requested_by_person_id is not null then
4922 p_status := 'REQUESTED';
4923 Else
4924 p_status := 'INITIATED';
4925 End if;
4926 Exception when others then
4927 --
4928 -- Reset IN OUT parameters and set OUT parameters
4929 --
4930 p_status := null;
4931 raise;
4932 End get_par_status;
4933
4934
4935 Procedure check_for_open_events
4936 (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
4937 p_action_taken in varchar2,
4938 p_user_name_acted_on in varchar2,
4939 p_user_name_routed_to in varchar2,
4940 p_groupbox_routed_to in number,
4941 p_message out nocopy boolean
4942 )
4943 is
4944
4945 l_exists boolean := FALSE;
4946 l_proc varchar2(72) := g_package || 'Check_for_open_events';
4947
4948 cursor c_open_events is
4949 select 1
4950 from ghr_event_history
4951 where table_name = 'GHR_PA_REQUESTS'
4952 and record_id = p_pa_request_id
4953 and start_date is not null
4954 and end_date is null;
4955
4956
4957 begin
4958
4959 hr_utility.set_location('Entering ' || l_proc,5);
4960 hr_utility.set_location('PAR ' || to_char(p_pa_request_id),1);
4961 hr_utility.set_location('GB ' || to_char(p_groupbox_routed_to),1);
4962 hr_utility.set_location('UN ' || (p_user_name_routed_to),1);
4963 hr_utility.set_location('UN acted ' || (p_user_name_acted_on),1);
4964 hr_utility.set_location('Action ' || (p_action_taken),1);
4965 If
4966 ( nvl(p_action_taken,hr_api.g_varchar2) = 'UPDATE_HR' or
4967 nvl(p_action_taken,hr_api.g_varchar2) = 'FUTURE_ACTION'
4968 --**dk
4969 or nvl(p_action_taken,hr_api.g_varchar2) = 'END_ROUTING'
4970
4971 ) then
4972 -- BUG # 3420126 As per requirements in the bug need not be fired during routing
4973 /*or
4974 ( p_user_name_routed_to is not null and
4975 nvl(p_user_name_acted_on,hr_api.g_varchar2) <> p_user_name_routed_to
4976 ) or
4977 (p_groupbox_routed_to is not null )*/
4978
4979 hr_utility.set_location(l_proc,10);
4980 for open_events in c_open_events loop
4981 hr_utility.set_location(l_proc,15);
4982 l_exists := TRUE;
4983 exit;
4984 end loop;
4985 If l_exists then
4986 hr_utility.set_location(l_proc,20);
4987 p_message := TRUE;
4988 Else
4989 hr_utility.set_location(l_proc,25);
4990 p_message := FALSE;
4991 End if;
4992 End if;
4993 hr_utility.set_location('Leaving ' || l_proc,30);
4994 Exception when others then
4995 --
4996 -- Reset IN OUT parameters and set OUT parameters
4997 --
4998 p_message := null;
4999 raise;
5000
5001 end check_for_open_events;
5002
5003 --Begin Bug# 8653508,9645677,9647352
5004 Procedure update_detail_remarks
5005 (p_effective_date in date,
5006 p_pa_request_id in number
5007 )
5008 is
5009
5010 l_proc varchar2(72) := g_package || 'update_detail_remarks';
5011 l_remark_id ghr_remarks.remark_id%type;
5012 l_remark_description ghr_remarks.description%type;
5013 l_pa_remark_id ghr_pa_remarks.pa_remark_id%type;
5014 l_rem_ovn ghr_pa_remarks.OBJECT_VERSION_NUMBER%type;
5015 l_remk_desc ghr_pa_remarks.description%type;
5016
5017 cursor cur_get_detail_info is
5018 SELECT rei_information13 Organization,
5019 rei_information5 position_id,
5020 rei_information6 grade_id,
5021 rei_information7 job_id,
5022 rei_information12 location_id
5023 FROM ghr_pa_request_extra_info
5024 WHERE rei_information_category='GHR_US_PAR_DET_INFO'
5025 AND pa_request_id = p_pa_request_id;
5026
5027 CURSOR cur_det_grd(l_grade_id NUMBER) IS
5028 SELECT gdf.segment1 pay_plan
5029 ,gdf.segment2 grade_or_level
5030 FROM per_grade_definitions gdf
5031 ,per_grades grd
5032 WHERE grd.grade_id = l_grade_id
5033 AND grd.grade_definition_id = gdf.grade_definition_id
5034 AND grd.business_group_id = FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
5035
5036 --Bug# 9761810 Modified the cursor to consider Position all segments
5037 cursor c_det_pos_name(l_pos_id number) IS
5038 select pos.name pos_name
5039 from hr_all_positions_f pos
5040 where pos.position_id = l_pos_id
5041 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
5042
5043 cursor c_remk_desc is
5044 select description,pa_remark_id,
5045 object_version_number from ghr_pa_remarks
5046 where remark_id =l_remark_id
5047 and pa_request_id = p_pa_request_id;
5048
5049
5050 l_detail_position_id ghr_pa_request_extra_info.rei_information5%TYPE;
5051 l_detail_grade_id ghr_pa_request_extra_info.rei_information6%TYPE;
5052 l_detail_job_id ghr_pa_request_extra_info.rei_information7%TYPE;
5053 l_detail_location_id ghr_pa_request_extra_info.rei_information12%TYPE;
5054 l_detail_org ghr_pa_request_extra_info.rei_information13%TYPE;
5055
5056 l_detail_position_name hr_all_positions_f.name%type;
5057 l_detail_grade PER_GRADES.name%type;
5058 l_detail_job HR_LOOKUPS.MEANING%type;
5059 l_detail_pay_plan ghr_pa_requests.from_pay_plan%type;
5060 l_detail_location varchar2(200);
5061 l_record_found BOOLEAN default FALSE;
5062
5063 BEGIN
5064 hr_utility.set_location('Entering ' || l_proc,55);
5065 FOR cur_get_detail_info_rec IN cur_get_detail_info LOOP
5066 l_detail_position_id := cur_get_detail_info_rec.position_id;
5067 l_detail_grade_id := cur_get_detail_info_rec.grade_id;
5068 l_detail_job_id := cur_get_detail_info_rec.job_id;
5069 l_detail_location_id := cur_get_detail_info_rec.location_id;
5070 l_detail_org := cur_get_detail_info_rec.Organization;
5071 l_record_found := TRUE;
5072 END LOOP;
5073 IF l_record_found THEN
5074 --ZK2 remarks
5075 BEGIN
5076 ghr_mass_actions_pkg.get_remark_id_desc
5077 (p_remark_code => 'ZK2',--Bug# 9928813
5078 p_effective_date => p_effective_date,
5079 p_remark_id => l_remark_id,
5080 p_remark_desc => l_remark_description);
5081 l_remark_description := replace(l_remark_description,'_____',l_detail_org);--Bug# 12400481
5082 for c_remk_desc_rec in c_remk_desc loop
5083 l_remk_desc := c_remk_desc_rec.description;
5084 l_pa_remark_id := c_remk_desc_rec.pa_remark_id;
5085 l_rem_ovn := c_remk_desc_rec.object_version_number;
5086 END LOOP;
5087 IF l_detail_org IS NULL AND l_pa_remark_id IS NOT NULL THEN
5088 delete from ghr_pa_remarks
5089 where pa_remark_id = l_pa_remark_id;
5090 ELSIF l_pa_remark_id IS NULL AND l_detail_org IS NOT NULL THEN
5091 ghr_pa_remarks_api.create_pa_remarks
5092 (
5093 p_PA_REQUEST_ID => p_pa_request_id,
5094 p_REMARK_ID => l_remark_id,
5095 p_DESCRIPTION => l_remark_description,
5096 P_PA_REMARK_ID => l_pa_remark_id,
5097 p_remark_code_information1 => l_detail_org, --Bug# 12683854
5098 p_OBJECT_VERSION_NUMBER => l_rem_ovn);
5099 ELSIF l_pa_remark_id IS NOT NULL AND NVL(l_remk_desc,'@@') <> l_remark_description THEN
5100 ghr_pa_remarks_api.update_pa_remarks
5101 (p_pa_remark_id => l_pa_remark_id
5102 ,p_object_version_number => l_rem_ovn
5103 ,p_remark_code_information1 => l_detail_org--Bug# 12683854
5104 ,p_remark_code_information2 => NULL
5105 ,p_remark_code_information3 => NULL
5106 ,p_remark_code_information4 => NULL
5107 ,p_remark_code_information5 => NULL
5108 ,p_description => l_remark_description
5109 );
5110 END IF;
5111 END;
5112 --ZK3 remarks
5113 BEGIN
5114 l_remk_desc:= NULL;
5115 l_pa_remark_id := NULL;
5116 l_rem_ovn := NULL;
5117 FOR c_det_pos_name_rec in c_det_pos_name(l_detail_position_id) LOOP
5118 l_detail_position_name := c_det_pos_name_rec.pos_name;
5119 END LOOP;
5120 ghr_mass_actions_pkg.get_remark_id_desc
5121 (p_remark_code => 'ZK3',--Bug# 9928813
5122 p_effective_date => p_effective_date,
5123 p_remark_id => l_remark_id,
5124 p_remark_desc => l_remark_description
5125 );
5126 l_remark_description := replace(l_remark_description,'_____',l_detail_position_name);--Bug# 12400481
5127 for c_remk_desc_rec in c_remk_desc loop
5128 l_remk_desc := c_remk_desc_rec.description;
5129 l_pa_remark_id := c_remk_desc_rec.pa_remark_id;
5130 l_rem_ovn := c_remk_desc_rec.object_version_number;
5131 END LOOP;
5132 IF l_detail_position_id IS NULL AND l_pa_remark_id IS NOT NULL THEN
5133 delete from ghr_pa_remarks
5134 where pa_remark_id = l_pa_remark_id;
5135 ELSIF l_pa_remark_id IS NULL AND l_detail_position_id IS NOT NULL THEN
5136 ghr_pa_remarks_api.create_pa_remarks
5137 (
5138 p_PA_REQUEST_ID => p_pa_request_id,
5139 p_REMARK_ID => l_remark_id,
5140 p_DESCRIPTION => l_remark_description,
5141 P_PA_REMARK_ID => l_pa_remark_id,
5142 p_remark_code_information1 => l_detail_position_name, --Bug# 12683854
5143 p_OBJECT_VERSION_NUMBER => l_rem_ovn);
5144 ELSIF l_pa_remark_id IS NOT NULL AND NVL(l_remk_desc,'@@') <> l_remark_description THEN
5145 ghr_pa_remarks_api.update_pa_remarks
5146 (p_pa_remark_id => l_pa_remark_id
5147 ,p_object_version_number => l_rem_ovn
5148 ,p_remark_code_information1 => l_detail_position_name --Bug# 12683854
5149 ,p_remark_code_information2 => NULL
5150 ,p_remark_code_information3 => NULL
5151 ,p_remark_code_information4 => NULL
5152 ,p_remark_code_information5 => NULL
5153 ,p_description => l_remark_description
5154 );
5155 END IF;
5156 END;
5157
5158 --ZK4 remarks
5159 FOR cur_det_grd_rec in cur_det_grd(l_detail_grade_id) LOOP
5160 l_detail_grade := cur_det_grd_rec.grade_or_level;
5161 l_detail_pay_plan := cur_det_grd_rec.pay_plan;
5162 END LOOP;
5163 BEGIN
5164 l_remk_desc:= NULL;
5165 l_pa_remark_id := NULL;
5166 l_rem_ovn := NULL;
5167 ghr_mass_actions_pkg.get_remark_id_desc
5168 (p_remark_code => 'ZK4',--Bug# 9928813
5169 p_effective_date => p_effective_date,
5170 p_remark_id => l_remark_id,
5171 p_remark_desc => l_remark_description
5172 );
5173 l_remark_description := replace(l_remark_description,'_____', l_detail_pay_plan);--Bug# 12400481
5174 for c_remk_desc_rec in c_remk_desc loop
5175 l_remk_desc := c_remk_desc_rec.description;
5176 l_pa_remark_id := c_remk_desc_rec.pa_remark_id;
5177 l_rem_ovn := c_remk_desc_rec.object_version_number;
5178 END LOOP;
5179 IF l_pa_remark_id is NOT NULL AND l_detail_pay_plan IS NULL THEN
5180 delete from ghr_pa_remarks
5181 where pa_remark_id = l_pa_remark_id;
5182 ELSIF l_pa_remark_id is NULL AND l_detail_pay_plan IS NOT NULL THEN
5183 ghr_pa_remarks_api.create_pa_remarks
5184 (
5185 p_PA_REQUEST_ID => p_pa_request_id,
5186 p_REMARK_ID => l_remark_id,
5187 p_DESCRIPTION => l_remark_description,
5188 P_PA_REMARK_ID => l_pa_remark_id,
5189 p_remark_code_information1 => l_detail_pay_plan, --Bug# 12683854
5190 p_OBJECT_VERSION_NUMBER => l_rem_ovn);
5191
5192 ELSIF l_pa_remark_id IS NOT NULL AND NVL(l_remk_desc,'@@') <> l_remark_description THEN
5193 ghr_pa_remarks_api.update_pa_remarks
5194 (p_pa_remark_id => l_pa_remark_id
5195 ,p_object_version_number => l_rem_ovn
5196 ,p_remark_code_information1 => l_detail_pay_plan--Bug# 12683854
5197 ,p_remark_code_information2 => NULL
5198 ,p_remark_code_information3 => NULL
5199 ,p_remark_code_information4 => NULL
5200 ,p_remark_code_information5 => NULL
5201 ,p_description => l_remark_description
5202 );
5203 END IF;
5204 END;
5205 --ZK5 remarks
5206 BEGIN
5207 l_remk_desc:= NULL;
5208 l_pa_remark_id := NULL;
5209 l_rem_ovn := NULL;
5210 ghr_mass_actions_pkg.get_remark_id_desc
5211 (p_remark_code => 'ZK5',--Bug# 9928813
5212 p_effective_date => p_effective_date,
5213 p_remark_id => l_remark_id,
5214 p_remark_desc => l_remark_description
5215 );
5216 l_remark_description := replace(l_remark_description,'_____',l_detail_grade); --Bug# 9646380,12400481
5217 for c_remk_desc_rec in c_remk_desc loop
5218 l_remk_desc := c_remk_desc_rec.description;
5219 l_pa_remark_id := c_remk_desc_rec.pa_remark_id;
5220 l_rem_ovn := c_remk_desc_rec.object_version_number;
5221 END LOOP;
5222 IF l_pa_remark_id IS NOT NULL AND l_detail_grade IS NULL THEN
5223 delete from ghr_pa_remarks
5224 where pa_remark_id = l_pa_remark_id;
5225 ELSIF l_pa_remark_id IS NULL AND l_detail_grade IS NOT NULL THEN
5226 ghr_pa_remarks_api.create_pa_remarks
5227 (
5228 p_PA_REQUEST_ID => p_pa_request_id,
5229 p_REMARK_ID => l_remark_id,
5230 p_DESCRIPTION => l_remark_description,
5231 P_PA_REMARK_ID => l_pa_remark_id,
5232 p_remark_code_information1 => l_detail_grade, --Bug# 12683854
5233 p_OBJECT_VERSION_NUMBER => l_rem_ovn);
5234 ELSIF l_pa_remark_id IS NOT NULL AND NVL(l_remk_desc,'@@') <> l_remark_description THEN
5235 ghr_pa_remarks_api.update_pa_remarks
5236 (p_pa_remark_id => l_pa_remark_id
5237 ,p_object_version_number => l_rem_ovn
5238 ,p_remark_code_information1 => l_detail_grade--Bug# 12683854
5239 ,p_remark_code_information2 => NULL
5240 ,p_remark_code_information3 => NULL
5241 ,p_remark_code_information4 => NULL
5242 ,p_remark_code_information5 => NULL
5243 ,p_description => l_remark_description
5244 );
5245 END IF;
5246 END;
5247
5248 --ZK8 remarks
5249 BEGIN
5250 l_remk_desc:= NULL;
5251 l_pa_remark_id := NULL;
5252 l_rem_ovn := NULL;
5253 l_detail_job := SUBSTR(ghr_pa_requests_pkg.get_lookup_meaning(800,'GHR_US_OCC_SERIES',l_detail_job_id),1,80);
5254 ghr_mass_actions_pkg.get_remark_id_desc
5255 (p_remark_code => 'ZK8',--Bug# 9928813
5256 p_effective_date => p_effective_date,
5257 p_remark_id => l_remark_id,
5258 p_remark_desc => l_remark_description
5259 );
5260 l_remark_description := replace(l_remark_description,'_____',l_detail_job);--Bug# 12400481
5261 for c_remk_desc_rec in c_remk_desc loop
5262 l_remk_desc := c_remk_desc_rec.description;
5263 l_pa_remark_id := c_remk_desc_rec.pa_remark_id;
5264 l_rem_ovn := c_remk_desc_rec.object_version_number;
5265 END LOOP;
5266 IF l_pa_remark_id IS NOT NULL AND l_detail_job_id IS NULL THEN
5267 delete from ghr_pa_remarks
5268 where pa_remark_id = l_pa_remark_id;
5269 ELSIF l_pa_remark_id IS NULL AND l_detail_job_id IS NOT NULL THEN
5270 ghr_pa_remarks_api.create_pa_remarks
5271 (
5272 p_PA_REQUEST_ID => p_pa_request_id,
5273 p_REMARK_ID => l_remark_id,
5274 p_DESCRIPTION => l_remark_description,
5275 P_PA_REMARK_ID => l_pa_remark_id,
5276 p_remark_code_information1 => l_detail_job, --Bug# 12683854
5277 p_OBJECT_VERSION_NUMBER => l_rem_ovn);
5278 ELSIF l_pa_remark_id IS NOT NULL AND NVL(l_remk_desc,'@@') <> l_remark_description THEN
5279 ghr_pa_remarks_api.update_pa_remarks
5280 (p_pa_remark_id => l_pa_remark_id
5281 ,p_object_version_number => l_rem_ovn
5282 ,p_remark_code_information1 => l_detail_job --Bug# 12683854
5283 ,p_remark_code_information2 => NULL
5284 ,p_remark_code_information3 => NULL
5285 ,p_remark_code_information4 => NULL
5286 ,p_remark_code_information5 => NULL
5287 ,p_description => l_remark_description
5288 );
5289 END IF;
5290 END;
5291 --ZK9 remarks
5292 BEGIN
5293 l_remk_desc:= NULL;
5294 l_pa_remark_id := NULL;
5295 l_rem_ovn := NULL;
5296 l_detail_location := ghr_ss_views_pkg.get_duty_station_desc(l_detail_location_id,p_effective_date);
5297 ghr_mass_actions_pkg.get_remark_id_desc
5298 (p_remark_code => 'ZK9',--Bug# 9928813
5299 p_effective_date => p_effective_date,
5300 p_remark_id => l_remark_id,
5301 p_remark_desc => l_remark_description
5302 );
5303 l_remark_description := replace(l_remark_description,'_____',l_detail_location);--Bug# 12400481
5304 for c_remk_desc_rec in c_remk_desc loop
5305 l_remk_desc := c_remk_desc_rec.description;
5306 l_pa_remark_id := c_remk_desc_rec.pa_remark_id;
5307 l_rem_ovn := c_remk_desc_rec.object_version_number;
5308 END LOOP;
5309 IF l_pa_remark_id IS NOT NULL AND l_detail_location_id IS NULL THEN
5310 delete from ghr_pa_remarks
5311 where pa_remark_id = l_pa_remark_id;
5312 ELSIF l_pa_remark_id IS NULL AND l_detail_location_id IS NOT NULL THEN
5313 ghr_pa_remarks_api.create_pa_remarks
5314 (
5315 p_PA_REQUEST_ID => p_pa_request_id,
5316 p_REMARK_ID => l_remark_id,
5317 p_DESCRIPTION => l_remark_description,
5318 P_PA_REMARK_ID => l_pa_remark_id,
5319 p_remark_code_information1 => l_detail_location, --Bug# 12683854
5320 p_OBJECT_VERSION_NUMBER => l_rem_ovn);
5321 ELSIF l_pa_remark_id IS NOT NULL AND NVL(l_remk_desc,'@@') <> l_remark_description THEN
5322 ghr_pa_remarks_api.update_pa_remarks
5323 (p_pa_remark_id => l_pa_remark_id
5324 ,p_object_version_number => l_rem_ovn
5325 ,p_remark_code_information1 => l_detail_location --Bug# 12683854
5326 ,p_remark_code_information2 => NULL
5327 ,p_remark_code_information3 => NULL
5328 ,p_remark_code_information4 => NULL
5329 ,p_remark_code_information5 => NULL
5330 ,p_description => l_remark_description
5331 );
5332 END IF;
5333 END;
5334 END IF;
5335 hr_utility.set_location('Leaving ' || l_proc,55);
5336 END update_detail_remarks;
5337 --End Bug# 8653508
5338
5339 end ghr_sf52_api;