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