[Home] [Help]
PACKAGE BODY: APPS.GHR_MASS_CHANGES
Source
1 PACKAGE BODY GHR_MASS_CHANGES AS
2 /* $Header: ghmass52.pkb 120.11.12020000.10 2012/10/25 09:53:15 utokachi ship $ */
3
4 -- Begin declaration
5
6 g_package varchar2(32) := 'GHR_MASS_CHANGES.' ;
7
8 Procedure create_refreshed_sf52_shadow(
9 p_action in varchar2,
10 p_sf52_data in ghr_pa_requests%rowtype);
11
12 --- End Declaration
13
14 Procedure create_sf52_for_mass_changes
15 (p_mass_action_type in varchar2,
16 p_pa_request_rec in out NOCOPY ghr_pa_requests%rowtype,
17 p_errbuf out NOCOPY varchar2, --\___ error log
18 p_retcode out NOCOPY number --/ in conc. manager.
19 )
20
21 is
22
23 l_proc varchar2(72) := g_package || 'create_sf52_for_mass_changes';
24 l_pa_request_rec ghr_pa_requests%rowtype;
25 l_noa_code ghr_pa_requests.first_noa_code%type;
26 l_scd_leave varchar2(30);
27 l_pa_remark_code1 ghr_remarks.code%type;
28 l_pa_remark_code2 ghr_remarks.code%type;
29 l_1_prh_object_version_number ghr_pa_requests.object_version_number%type;
30 l_1_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
31 l_2_prh_object_version_number ghr_pa_requests.object_version_number%type;
32 l_2_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
33 l_multiple_error_flag boolean;
34 l_dummy varchar2(30);
35 l_remark_id ghr_remarks.remark_id%type;
36 l_description ghr_remarks.description%type;
37 l_pa_remark_id ghr_pa_remarks.pa_remark_id%type;
38 l_object_version_number ghr_pa_remarks.object_version_number%type;
39 l_message varchar2(2000);
40 l_error varchar2(30);
41 l_log_text varchar2(2000);
42 l_route_flag varchar2(1);
43 l_routing_group_id ghr_pa_requests.routing_group_id%type;
44 l_groupbox_id ghr_groupboxes.groupbox_id%type;
45 l_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
46 l_approving_off_work_title ghr_pa_requests.APPROVING_OFFICIAL_WORK_TITLE%type;
47 l_personnel_office_id ghr_pois.personnel_office_id%type;
48 l_personnel_officer_name per_people_f.full_name%type;
49 l_remark_code_information1 ghr_pa_remarks.remark_code_information1%type;
50 l_remark_code_information2 ghr_pa_remarks.remark_code_information2%type;
51 l_remark_code_information3 ghr_pa_remarks.remark_code_information3%type;
52 l_remark_code_information4 ghr_pa_remarks.remark_code_information4%type;
53 l_remark_code_information5 ghr_pa_remarks.remark_code_information5%type;
54
55 l_au_overtime ghr_pa_requests.to_au_overtime%TYPE;
56 l_auo_premium_pay_indicator ghr_pa_requests.to_auo_premium_pay_indicator%TYPE;
57 l_availability_pay ghr_pa_requests.to_availability_pay%TYPE;
58 l_ap_premium_pay_indicator ghr_pa_requests.to_ap_premium_pay_indicator%TYPE;
59 l_retention_allowance ghr_pa_requests.to_retention_allowance%TYPE;
60 l_supervisory_differential ghr_pa_requests.to_supervisory_differential%TYPE;
61 l_staffing_differential ghr_pa_requests.to_staffing_differential%TYPE;
62 l_duty_station_location_id hr_locations.location_id%TYPE;
63 l_duty_station_id ghr_duty_stations_v.duty_station_id%TYPE;
64 l_duty_station_code ghr_duty_stations_v.duty_station_code%TYPE;
65 l_duty_station_desc ghr_duty_stations_v.duty_station_desc%TYPE;
66 l_appropriation_code1 varchar2(30);
67 l_appropriation_code2 varchar2(30);
68 l_position_id hr_positions_f.position_id%TYPE;
69 l_noa_family_code ghr_families.noa_family_code%type;
70 -- Bug#4256022 Declared the variable l_desc_out
71 l_desc_out ghr_pa_requests.first_noa_desc%TYPE;
72
73 l_pa_request_num_prefix varchar2(10);
74 groupbox_err exception;
75
76 l_sid number;
77
78 l_new_retention_allowance number; ---AVR
79 l_new_supervisory_differential number; ---AVR
80
81 l_dummy_char VARCHAR2(150); --Bug 2013340 --AVR
82 l_full_name VARCHAR2(380); -- Bug 3718167 --VN
83 l_ssn ghr_pa_requests.employee_national_identifier%TYPE; -- Bug 3718167 --VN
84
85 ---SES Changes Start
86 l_user_table_id pay_user_tables.user_table_id%type;
87
88 -- Bug#5089732
89 l_to_grade_id NUMBER;
90 -- Bug#5089732
91 --Begin Bug# 14055214
92 l_target_occ_code ghr_pa_requests.to_occ_code%type;
93 l_target_job_id ghr_pa_requests.to_job_id%type;
94 l_target_flsa_category ghr_pa_requests.flsa_category%type;
95 l_target_barg_unit_status ghr_pa_requests.bargaining_unit_status%type;
96 l_target_agency_code_subelmt ghr_pa_requests.agency_code%type;
97 l_target_duty_station_id ghr_mass_salaries.target_duty_station_id%type;
98 l_employee_dept_or_agency ghr_pa_requests.employee_dept_or_agency%type;
99 l_target_functional_class ghr_mass_salaries.target_functional_class%type;--Bug# 14410401
100 --End Bug# 14055214
101 cursor c_pay_tab_essl is
102 select 1 from pay_user_tables
103 where substr(user_table_name,1,4) = 'ESSL'
104 and user_table_id = l_user_table_id;
105
106 l_essl_table BOOLEAN := FALSE;
107
108 ----SES Changes End
109
110 cursor cur_sessionid is
111 select userenv('sessionid') sesid from dual;
112
113 cursor get_sal_chg_fam(p_effective_date in date) is
114 select NOA_FAMILY_CODE
115 from ghr_families
116 where NOA_FAMILY_CODE in
117 (select NOA_FAMILY_CODE from ghr_noa_families
118 where nature_of_action_id =
119 (select nature_of_action_id
120 from ghr_nature_of_actions
121 where code = '894'
122 and p_effective_date between date_from and nvl(date_to,p_effective_date) )
123 ) and proc_method_flag = 'Y';
124
125 Cursor c_routing_history is
126 select prh.pa_routing_history_id,
127 prh.object_version_number
128 from ghr_pa_routing_history prh
129 where prh.pa_request_id = l_pa_request_rec.pa_request_id
130 order by 1 desc;
131
132 --7577249
133 cursor get_family_code(p_effective_date in date) is
134 select NOA_FAMILY_CODE
135 from ghr_families
136 where NOA_FAMILY_CODE in
137 (select NOA_FAMILY_CODE from ghr_noa_families
138 where nature_of_action_id =
139 (select nature_of_action_id
140 from ghr_nature_of_actions
141 where code = nvl(ghr_msl_pkg.g_first_noa_code,'894') and p_effective_date between date_from and nvl(date_to,p_effective_date))
142 ) and proc_method_flag = 'Y';
143 ----7577249
144
145 l_errbuf VARCHAR2(2000);
146 l_retcode NUMBER;
147 --Bug # 9329643
148 l_employee_number per_people_f.employee_number%type;
149
150 Begin
151 --INITIALIZE
152 l_errbuf := p_errbuf;
153 l_retcode := p_retcode;
154 --
155 for s_id in cur_sessionid
156 loop
157 l_sid := s_id.sesid;
158 exit;
159 end loop;
160
161 begin
162 update fnd_sessions set SESSION_ID = l_sid
163 where SESSION_ID = l_sid;
164 if sql%notfound then
165 INSERT INTO fnd_sessions
166 (SESSION_ID,EFFECTIVE_DATE)
167 VALUES
168 (l_sid,sysdate);
169 end if;
170 end;
171
172 hr_utility.set_location('Entering ' || l_proc,5);
173 savepoint create_sf52_for_mass_changes;
174
175 l_pa_request_rec := p_pa_request_rec;
176 -- Bug#3718167 Getting the employee_full name into l_full_name to use in the Log Text
177 l_full_name := l_pa_request_rec.employee_last_name||','|| l_pa_request_rec.employee_first_name||' '|| l_pa_request_rec.employee_middle_names;
178 l_ssn := l_pa_request_rec.employee_national_identifier;
179
180 --Bug # 9329643
181 l_employee_number := ghr_pa_requests_pkg2.get_employee_number
182 (p_person_id => l_pa_request_rec.person_id,
183 p_effective_date => l_pa_request_rec.effective_date);
184 -- These will be the ones that will be common to all the mass actions.
185 --
186
187 -- The foll. would be common to all the mass actions
188
189 l_pa_request_rec.additional_info_person_id := Null;
190 l_pa_request_rec.additional_info_tel_number := Null;
191 l_pa_request_rec.Proposed_Effective_Date := Null;
192 l_pa_request_rec.Proposed_Effective_ASAP_flag := 'N';
193 l_pa_request_rec.requested_by_person_id := Null;
194 l_pa_request_rec.requested_by_title := Null;
195 l_pa_request_rec.requested_date := Null;
196 l_pa_request_rec.authorized_by_person_id := Null;
197 l_pa_request_rec.authorized_by_title := Null;
198 l_pa_request_rec.concurrence_Date := Null;
199 --Bug #1295328 initialize
200 l_pa_request_rec.forwarding_address_line1 := Null;
201 l_pa_request_rec.forwarding_address_line2 := Null;
202 l_pa_request_rec.forwarding_address_line3 := Null;
203 l_pa_request_rec.forwarding_country := Null;
204 l_pa_request_rec.forwarding_country_short_name := Null;
205 l_pa_request_rec.forwarding_postal_code := Null;
206 l_pa_request_rec.forwarding_region_2 := Null;
207 l_pa_request_rec.forwarding_town_or_city := Null;
208 l_pa_request_rec.rpa_type := p_pa_request_rec.rpa_type;
209 l_pa_request_rec.mass_action_id := p_pa_request_rec.mass_action_id;
210
211 If p_mass_action_type = 'MASS_SALARY_CHG' then
212 hr_utility.set_location(l_proc,10);
213 --7577249 commented hardcoding and added to fetch noa family code as
214 -- for SES employees noa code changes by changing the lac code
215
216 open get_family_code(p_effective_date => l_pa_request_rec.effective_date);
217 fetch get_family_code into l_pa_request_rec.noa_Family_Code;
218 close get_family_code;
219 --7577249
220
221 l_pa_request_num_prefix := p_pa_request_rec.rpa_type;
222 -- l_pa_request_rec.noa_Family_Code := 'GHR_SAL_PAY_ADJ';
223 l_pa_request_rec.first_noa_code := nvl(ghr_msl_pkg.g_first_noa_code,'894');
224 ElsIf p_mass_action_type = 'MASS_LOCALITY_CHG' then
225 hr_utility.set_location(l_proc,10);
226 if ghr_msl_pkg.g_first_noa_code is not null then
227 l_pa_request_rec.noa_family_code := 'GHR_SAL_PAY_ADJ';
228 l_pa_request_rec.first_noa_code := ghr_msl_pkg.g_first_noa_code;
229 else
230 l_pa_request_rec.noa_family_code := 'GHR_SAL_LOCAL_PAY';
231 l_pa_request_rec.first_noa_code := '895';
232 end if;
233 l_pa_request_num_prefix := 'MLC';
234 ElsIf p_mass_action_type = 'MASS_TABLE_CHG' then
235 hr_utility.set_location(l_proc,10);
236 if l_pa_request_rec.from_basic_pay = l_pa_request_rec.to_basic_pay
237 AND l_pa_request_rec.input_pay_rate_determinant <> l_pa_request_rec.pay_rate_determinant then
238 l_pa_request_rec.noa_family_code := 'CHG_DATA_ELEMENT';
239 l_pa_request_rec.first_noa_code := '800';
240 else
241 l_pa_request_rec.noa_family_code := 'GHR_SAL_PAY_ADJ';
242 l_pa_request_rec.first_noa_code := '894';
243 end if;
244 l_pa_request_num_prefix := 'MTC';
245 Elsif p_mass_action_type = 'MASS_REALIGNMENT' then
246 l_pa_request_rec.noa_family_code := 'REALIGNMENT'; -- Action requested
247 l_pa_request_rec.first_noa_code := 790;
248 l_pa_request_num_prefix := 'MRE';
249 Elsif p_mass_action_type = 'MASS_TRANSFER_OUT' Then
250 l_pa_request_rec.noa_family_code := 'SEPARATION';
251 l_pa_request_rec.first_noa_code := 352;
252 l_pa_request_num_prefix := 'MTO';
253 Elsif p_mass_action_type = 'MASS_TRANSFER_IN' Then
254 l_pa_request_num_prefix := 'MTI';
255 --Begin Bug# 14055214
256 ELSIF p_mass_action_type = 'MASS_NOA_SEL' THEN
257 hr_utility.set_location('In MASS_NOA_SEL '|| ghr_msl_pkg.g_first_noa_code,220);
258 l_pa_request_rec.first_noa_code := ghr_msl_pkg.g_first_noa_code;
259 open get_family_code(p_effective_date => l_pa_request_rec.effective_date);
260 fetch get_family_code into l_pa_request_rec.noa_Family_Code;
261 close get_family_code;
262 l_pa_request_num_prefix := 'MNOA';
263 l_target_occ_code := l_pa_request_rec.to_occ_code;
264 l_target_job_id := l_pa_request_rec.to_job_id;
265 l_target_flsa_category := l_pa_request_rec.flsa_category;
266 l_target_barg_unit_status := l_pa_request_rec.bargaining_unit_status;
267 l_target_agency_code_subelmt := l_pa_request_rec.agency_code;
268 l_target_functional_class := l_pa_request_rec.functional_class;
269 --End Bug# 14055214
270 End if;
271
272 hr_utility.set_location(l_proc,35);
273 ghr_mass_actions_pkg.get_noa_id_desc
274 (p_noa_code => l_pa_request_rec.first_noa_code,
275 p_effective_date => l_pa_request_rec.effective_date,
276 p_noa_id => l_pa_request_rec.first_noa_id,
277 p_noa_desc => l_pa_request_rec.first_noa_desc
278 );
279
280 --Begin Bug# 14055214
281 hr_utility.set_location ('Mass action type '|| p_mass_action_type,233);
282 IF p_mass_action_type = 'MASS_NOA_SEL' AND l_pa_request_rec.first_noa_code IN ('460','472','772','773') THEN
283 ghr_mass_actions_pkg.replace_insertion_values
284 (p_desc => l_pa_request_rec.first_noa_desc,
285 p_information1 => to_char(fnd_date.canonical_to_date(l_pa_request_rec.first_noa_information1),'DD-MON-RRRR'),
286 p_desc_out => l_desc_out
287 );
288 l_pa_request_rec.first_noa_desc := l_desc_out;
289 END IF;
290 --End Bug# 14055214
291 IF p_mass_action_type = 'MASS_TRANSFER_OUT' THEN
292
293 -- First NOA Code has insertion values. The insertion value will be
294 -- the To Agency Code the employee is transferring to.
295 -- Bug#4256022 Passed the parameter l_desc_out and reassigned its value to
296 -- l_pa_request_rec.first_noa_desc to avoid NOCOPY related problems..
297 ghr_mass_actions_pkg.replace_insertion_values
298 (p_desc => l_pa_request_rec.first_noa_desc,
299 p_information1 => l_pa_request_rec.first_noa_information1,
300 p_desc_out => l_desc_out
301 );
302 l_pa_request_rec.first_noa_desc := l_desc_out;
303 END IF;
304
305 hr_utility.set_location(l_proc,40);
306
307
308 -- For Mass Transfer IN we don't have to fetch any information
309 -- it is all passed in.
310
311
312 -- From side and To side position information
313 -- In case of the mass salary, pay calc identified as common
314 -- b/w show and create and hence
315 -- would have to_total_salary,to_basic_pay,to_locality_adj,
316 -- to_adjusted_basic_pay,to_other_pay and its sub components.
317 -- All the rest of the To side will be the same as the From Side.
318
319 -- For Realignment the TO will be same as the FROM.
320 -- For Transfer OUT the TO side will be NULL
321
322
323 IF p_mass_action_type <> 'MASS_TRANSFER_IN' THEN
324
325 hr_utility.set_location(l_proc,55);
326
327 ghr_api.sf52_from_data_elements
328 (p_person_id => l_pa_request_rec.person_id
329 ,p_assignment_id => l_pa_request_rec.employee_assignment_id
330 ,p_effective_date => l_pa_request_rec.effective_date
331 ----added 3 parameters.
332 ,p_altered_pa_request_id => null
333 ,p_noa_id_corrected => null
334 ,p_pa_history_id => null
335 ---
336 ,p_position_title => l_pa_request_rec.from_position_title
337 ,p_position_number => l_pa_request_rec.from_position_number
338 ,p_position_seq_no => l_pa_request_rec.from_position_seq_no
339 ,p_pay_plan => l_pa_request_rec.from_pay_plan
340 ,p_job_id => l_pa_request_rec.to_job_id
341 ,p_occ_code => l_pa_request_rec.from_occ_code
342 -- Bug#5089732 Passed local variable for grade_id. For MSL, we've to consider the grade_id passed
343 -- from the execute_msl process.
344 ,p_grade_id => l_to_grade_id
345 ,p_grade_or_level => l_pa_request_rec.from_grade_or_level
346 ,p_step_or_rate => l_pa_request_rec.from_step_or_rate
347 ,p_total_salary => l_pa_request_rec.from_total_salary
348 ,p_pay_basis => l_pa_request_rec.from_pay_basis
349 -- FWFA Changes Bug#4444609
350 ,p_pay_table_identifier => l_pa_request_rec.from_pay_table_identifier
351 -- FWFA Changes
352 ,p_basic_pay => l_pa_request_rec.from_basic_pay
353 ,p_locality_adj => l_pa_request_rec.from_locality_adj
354 ,p_adj_basic_pay => l_pa_request_rec.from_adj_basic_pay
355 ,p_other_pay => l_pa_request_rec.from_other_pay_amount
356 -- All these Other Pay comps. were put on the TO side. I think it is a BUG
357 -- But confirm it with Rohini. It may be a BUG for Mass Salary.
358 ,p_au_overtime => l_au_overtime
359 ,p_auo_premium_pay_indicator => l_pa_request_rec.to_auo_premium_pay_indicator
360 ,p_availability_pay => l_availability_pay
361 ,p_ap_premium_pay_indicator => l_pa_request_rec.to_ap_premium_pay_indicator
362 ,p_retention_allowance => l_new_retention_allowance
363 --
364 ,p_retention_allow_percentage => l_pa_request_rec.to_retention_allow_percentage
365 ,p_supervisory_differential => l_new_supervisory_differential
366 --
367 ,p_supervisory_diff_percentage => l_pa_request_rec.to_supervisory_diff_percentage
368 ,p_staffing_differential => l_pa_request_rec.to_staffing_differential
369 --
370 ,p_staffing_diff_percentage => l_pa_request_rec.to_staffing_diff_percentage
371 ,p_organization_id => l_pa_request_rec.to_organization_id
372 ,p_position_org_line1 => l_pa_request_rec.from_position_org_line1
373 ,p_position_org_line2 => l_pa_request_rec.from_position_org_line2
374 ,p_position_org_line3 => l_pa_request_rec.from_position_org_line3
375 ,p_position_org_line4 => l_pa_request_rec.from_position_org_line4
376 ,p_position_org_line5 => l_pa_request_rec.from_position_org_line5
377 ,p_position_org_line6 => l_pa_request_rec.from_position_org_line6
378 ,p_position_id => l_pa_request_rec.from_position_id
379 ,p_duty_station_location_id => l_duty_station_location_id
380 ---Bug 2013340
381 ,p_pay_rate_determinant => l_dummy_char
382 ,p_work_schedule => l_pa_request_rec.work_schedule
383 );
384 -- Bug#2850747 Added MASS_REALIGNMENT
385 --Bug# 14055214 Added MASS_NOA_SEL
386 if p_mass_action_type NOT IN ('MASS_SALARY_CHG','MASS_REALIGNMENT','MASS_LOCALITY_CHG',
387 'MASS_TABLE_CHG','MASS_NOA_SEL') then
388 l_pa_request_rec.to_retention_allowance := l_new_retention_allowance;
389 l_pa_request_rec.to_supervisory_differential := l_new_supervisory_differential;
390 end if;
391
392 -- Bug#5089732 For mass salary don't assing the the grade id.
393 IF p_mass_action_type <> 'MASS_SALARY_CHG' then
394 l_pa_request_rec.to_grade_id := l_to_grade_id;
395 END IF;
396
397 -- In case of Mass realignment we don't need the value of duty station location
398 -- from the FROM side. This will be assigned the target duty station locn id.
399 --Bug# 14055214 Added MASS_NOA_SEL
400 IF p_mass_action_type NOT IN('MASS_REALIGNMENT','MASS_NOA_SEL') OR l_pa_request_rec.duty_station_location_id is NULL THEN
401 l_pa_request_rec.duty_station_location_id := l_duty_station_location_id;
402 END IF;
403
404
405 hr_utility.set_location(l_proc,60);
406
407 -- populate to side with the from side except for Mass Transfer OUT.
408
409 IF p_mass_action_type not in ('MASS_TRANSFER_OUT') THEN
410 l_pa_request_rec.to_position_title := l_pa_request_rec.from_position_title;
411 l_pa_request_rec.to_position_number := l_pa_request_rec.from_position_number;
412 l_pa_request_rec.to_position_seq_no := l_pa_request_rec.from_position_seq_no;
413 -- Bug#5089732 For mass salary don't assing the the grade id.
414 IF p_mass_action_type <> 'MASS_SALARY_CHG' then
415 l_pa_request_rec.to_pay_plan := l_pa_request_rec.from_pay_plan;
416 l_pa_request_rec.to_grade_or_level := l_pa_request_rec.from_grade_or_level;
417 END IF;
418 l_pa_request_rec.to_occ_code := l_pa_request_rec.from_occ_code;
419
420 l_pa_request_rec.to_pay_basis := l_pa_request_rec.from_pay_basis;
421 -- l_pa_request_rec.to_organization_id := l_pa_request_rec.to_organization_id;
422 l_pa_request_rec.to_position_org_line1 := l_pa_request_rec.from_position_org_line1;
423 l_pa_request_rec.to_position_org_line2 := l_pa_request_rec.from_position_org_line2;
424 l_pa_request_rec.to_position_org_line3 := l_pa_request_rec.from_position_org_line3;
425 l_pa_request_rec.to_position_org_line4 := l_pa_request_rec.from_position_org_line4;
426 l_pa_request_rec.to_position_org_line5 := l_pa_request_rec.from_position_org_line5;
427 l_pa_request_rec.to_position_org_line6 := l_pa_request_rec.from_position_org_line6;
428 l_pa_request_rec.to_position_id := l_pa_request_rec.from_position_id;
429 ----SES PAY Changes...
430 If p_mass_action_type = 'MASS_SALARY_CHG' then
431 -- Pradeep added EE to the following if list for the Bug 3604377
432 if l_pa_request_rec.to_pay_plan in ('ES','EP','FE','IE','EE') then
433
434 l_user_table_id := ghr_pay_calc.get_user_table_id(
435 p_position_id => l_pa_request_rec.to_position_id
436 ,p_effective_date => l_pa_request_rec.effective_date
437 );
438
439 l_essl_table := FALSE;
440 for c_pay_tab_essl_rec in c_pay_tab_essl loop
441 l_essl_table := TRUE;
442 exit;
443 end loop;
444
445 if l_essl_table then
446 l_pa_request_rec.to_step_or_rate := '00';
447 else
448 l_pa_request_rec.to_step_or_rate := l_pa_request_rec.from_step_or_rate;
449 end if;
450 else
451 l_pa_request_rec.to_step_or_rate := l_pa_request_rec.from_step_or_rate;
452 end if;
453
454 else
455 l_pa_request_rec.to_step_or_rate := l_pa_request_rec.from_step_or_rate;
456 end if;
457 ----SES Pay CHanges...end..
458 --Begin Bug# 14055214
459 IF p_mass_action_type = 'MASS_NOA_SEL' THEN
460 IF l_pa_request_rec.first_noa_code IN ('430','460','471','472') THEN
461 l_pa_request_rec.to_position_id := NULL;
462 l_pa_request_rec.to_position_title := NULL;
463 l_pa_request_rec.to_position_number := NULL;
464 l_pa_request_rec.to_position_seq_no := NULL;
465 l_pa_request_rec.to_job_id := NULL;
466 l_pa_request_rec.to_occ_code := NULL;
467 l_pa_request_rec.to_organization_id := NULL;
468 l_pa_request_rec.to_pay_basis := NULL;
469 l_pa_request_rec.to_step_or_rate := NULL;
470 l_pa_request_rec.to_pay_plan := NULL;
471 l_pa_request_rec.to_grade_id := NULL;
472 l_pa_request_rec.to_grade_or_level := NULL;
473 --Begin Bug# 14805985
474 l_pa_request_rec.to_position_org_line1 := NULL;
475 l_pa_request_rec.to_position_org_line2 := NULL;
476 l_pa_request_rec.to_position_org_line3 := NULL;
477 l_pa_request_rec.to_position_org_line4 := NULL;
478 l_pa_request_rec.to_position_org_line5 := NULL;
479 l_pa_request_rec.to_position_org_line6 := NULL;
480 l_pa_request_rec.to_adj_basic_pay := NULL;
481 l_pa_request_rec.to_basic_pay := NULL;
482 l_pa_request_rec.to_locality_adj := NULL;
483 l_pa_request_rec.to_total_salary := NULL;
484 l_pa_request_rec.to_other_pay_amount := NULL;
485 l_pa_request_rec.to_au_overtime := NULL;
486 l_pa_request_rec.to_auo_premium_pay_indicator := NULL;
487 l_pa_request_rec.to_availability_pay := NULL;
488 l_pa_request_rec.to_ap_premium_pay_indicator := NULL;
489 l_pa_request_rec.to_retention_allowance := NULL;
490 l_pa_request_rec.to_supervisory_differential := NULL;
491 l_pa_request_rec.to_staffing_differential := NULL;
492 l_pa_request_rec.to_retention_allow_percentage := NULL;
493 l_pa_request_rec.to_supervisory_diff_percentage := NULL;
494 l_pa_request_rec.to_staffing_diff_percentage := NULL;
495 --End Bug# 14805985
496 ELSIF l_pa_request_rec.first_noa_code IN ('800') THEN
497 l_pa_request_rec.to_adj_basic_pay := l_pa_request_rec.from_adj_basic_pay;
498 l_pa_request_rec.to_basic_pay := l_pa_request_rec.from_basic_pay;
499 l_pa_request_rec.to_locality_adj := l_pa_request_rec.from_locality_adj;
500 l_pa_request_rec.to_total_salary := l_pa_request_rec.from_total_salary;
501 ELSIF l_pa_request_rec.first_noa_code IN ('792') THEN
502 IF l_pa_request_rec.duty_station_location_id <> l_duty_station_location_id THEN
503 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
504 (p_location_id => l_duty_station_location_id
505 ,p_duty_station_id => l_target_duty_station_id);
506 IF (ghr_pay_calc.get_lpa_percentage (l_pa_request_rec.duty_station_id, l_pa_request_rec.effective_date)
507 <> ghr_pay_calc.get_lpa_percentage (l_target_duty_station_id, l_pa_request_rec.effective_date)) THEN
508 l_pa_request_rec.first_noa_code := '894';
509 --Begin Bug# 14805985 to get the noa description
510 ghr_mass_actions_pkg.get_noa_id_desc
511 (p_noa_code => l_pa_request_rec.first_noa_code,
512 p_effective_date => l_pa_request_rec.effective_date,
513 p_noa_id => l_pa_request_rec.first_noa_id,
514 p_noa_desc => l_pa_request_rec.first_noa_desc
515 );
516 --End Bug# 14805985
517 open get_sal_chg_fam(p_effective_date => l_pa_request_rec.effective_date);
518 fetch get_sal_chg_fam into l_pa_request_rec.noa_Family_Code;
519 close get_sal_chg_fam;
520 l_pa_request_rec.first_action_la_code1 := 'VGR';
521 l_pa_request_rec.first_action_la_desc1 := ghr_pa_requests_pkg.get_lookup_description
522 (800,'GHR_US_LEGAL_AUTHORITY',l_pa_request_rec.first_action_la_code1);
523
524 END IF;
525 END IF;
526 END IF;
527 END IF;
528 --End Bug# 14055214
529 END IF; -- For Seperation action, the TO position details have to be NULL
530
531 END IF; -- If it is not Mass transfer IN condn.
532
533 -------------------------------For Mass Salary----------------------
534
535 -- Assuming that the foll. blocked elements will be passed in , as the new pay calc would have been done in the
536 -- Calling procedure
537
538 -- ,p_total_salary
539 -- ,p_basic_pay
540 -- ,p_locality_adj
541 -- ,p_adj_basic_pay
542 -- ,p_other_pay
543 -- ,p_au_overtime
544 -- ,p_auo_premium_pay_indicator
545 -- ,p_availability_pay
546 -- ,p_ap_premium_pay_indicator
547 -- ,p_retention_allowance
548 -- ,p_supervisory_differential
549 -- ,p_staffing_differential
550 --------------------------------------------------------------------
551
552 -- Employee Data
553
554 -- # Bug 711533 : Added the if condition
555 IF p_mass_action_type <> 'MASS_TRANSFER_IN' THEN
556 hr_utility.set_location(l_proc,65);
557 ghr_pa_requests_pkg.get_SF52_person_ddf_details
558 (p_person_id => l_pa_request_rec.person_id,
559 p_date_effective => l_pa_request_rec.effective_date,
560 p_citizenship => l_pa_request_rec.citizenship,
561 p_veterans_preference => l_pa_request_rec.veterans_preference,
562 p_veterans_pref_for_rif => l_pa_request_rec.veterans_pref_for_rif,
563 p_veterans_status => l_pa_request_rec.veterans_status,
564 p_scd_leave => l_scd_leave
565 );
566
567 -- populate service comp date
568 hr_utility.set_location(l_proc,70);
569
570 ---l_pa_request_rec.service_comp_date := to_date(l_scd_leave, 'dd-mon-yyyy');
571 l_pa_request_rec.service_comp_date := fnd_date.canonical_to_date(l_scd_leave);
572
573 -- get education details
574 hr_utility.set_location(l_proc,75);
575 ghr_api.return_education_Details
576 (p_person_id => l_pa_request_rec.person_id,
577 p_effective_date => l_pa_request_rec.effective_date,
578 p_education_level => l_pa_request_rec.education_level,
579 p_academic_discipline => l_pa_request_rec.academic_discipline,
580 p_year_degree_attained => l_pa_request_rec.year_degree_attained
581 );
582
583 hr_utility.set_location(l_proc,80);
584 End if;
585
586 IF p_mass_action_type = 'MASS_TRANSFER_IN' THEN
587 l_position_id := l_pa_request_rec.to_position_id;
588 ELSE
589 l_position_id := l_pa_request_rec.from_position_id;
590 END IF;
591 IF p_mass_action_type <> 'MASS_TRANSFER_IN' THEN
592
593 ghr_pa_requests_pkg.get_sf52_pos_ddf_details
594 (p_position_id => l_position_id
595 ,p_date_Effective => l_pa_request_rec.effective_date
596 ,p_flsa_category => l_pa_request_rec.flsa_category
597 ,p_bargaining_unit_status => l_pa_request_rec.bargaining_unit_status
598 ,p_work_schedule => l_dummy
599 ,p_functional_class => l_pa_request_rec.functional_class
600 ,p_supervisory_status => l_pa_request_rec.supervisory_status
601 ,p_position_occupied => l_pa_request_rec.position_occupied
602 ,p_appropriation_code1 => l_appropriation_code1
603 ,p_appropriation_code2 => l_appropriation_code2
604 ,p_personnel_office_id => l_personnel_office_id
605 ,p_office_symbol => l_dummy
606 ,p_part_time_hours => l_dummy
607 );
608
609 END IF;
610
611 IF p_mass_action_type = 'MASS_TRANSFER_IN' THEN
612
613 ghr_pa_requests_pkg.get_sf52_pos_ddf_details
614 (p_position_id => l_position_id
615 ,p_date_Effective => l_pa_request_rec.effective_date
616 ,p_flsa_category => l_dummy
617 ,p_bargaining_unit_status => l_dummy
618 ,p_work_schedule => l_dummy
619 ,p_functional_class => l_dummy
620 ,p_supervisory_status => l_dummy
621 ,p_position_occupied => l_dummy
622 ,p_appropriation_code1 => l_dummy
623 ,p_appropriation_code2 => l_dummy
624 ,p_personnel_office_id => l_personnel_office_id
625 ,p_office_symbol => l_dummy
626 ,p_part_time_hours => l_dummy
627 );
628 END IF;
629
630
631 IF l_pa_request_rec.appropriation_code1 is NULL THEN
632 l_pa_request_rec.appropriation_code1 := l_appropriation_code1;
633 END IF;
634
635 IF l_pa_request_rec.appropriation_code2 is NULL THEN
636 l_pa_request_rec.appropriation_code2 := l_appropriation_code2;
637 END IF;
638
639 If p_mass_action_type <> 'MASS_TRANSFER_IN' THEN
640
641 -- get fegli,retirement_plan
642
643 ghr_api.retrieve_element_entry_value
644 (p_element_name => 'FEGLI'
645 ,p_input_value_name => 'FEGLI'
646 ,p_assignment_id => l_pa_request_rec.employee_assignment_id
647 ,p_effective_date => l_pa_request_rec.effective_date
648 ,p_value => l_pa_request_rec.fegli
649 ,p_multiple_error_flag => l_multiple_error_flag
650 );
651
652 --retirement_plan
653 ghr_api.retrieve_element_entry_value
654 (p_element_name => 'Retirement Plan'
655 ,p_input_value_name => 'Plan'
656 ,p_assignment_id => l_pa_request_rec.employee_assignment_id
657 ,p_effective_date => l_pa_request_rec.effective_date
658 ,p_value => l_pa_request_rec.retirement_plan
659 ,p_multiple_error_flag => l_multiple_error_flag
660 );
661
662 END IF; -- end for the condn. not = mass transfer in for retrieving elements.
663
664 l_pa_request_rec.fegli_desc := ghr_pa_requests_pkg.get_lookup_meaning
665 (800
666 ,'GHR_US_FEGLI'
667 ,l_pa_request_rec.fegli
668 );
669
670
671 l_pa_request_rec.retirement_plan_desc := ghr_pa_requests_pkg.get_lookup_meaning
672 (800
673 ,'GHR_US_RETIREMENT_PLAN'
674 ,l_pa_request_rec.retirement_plan
675 );
676
677
678 IF p_mass_action_type = 'MASS_TRANSFER_IN' THEN
679 -- Commented out following lines VBug # 701368
680 -- l_pa_request_rec.to_adj_basic_pay := 0;
681 -- l_pa_request_rec.to_basic_pay := 0;
682 -- l_pa_request_rec.to_total_salary := 0;
683 null;
684
685 ELSIF p_mass_action_type = 'MASS_TRANSFER_OUT' THEN
686 l_pa_request_rec.to_step_or_rate := null;
687 l_pa_request_rec.to_adj_basic_pay := null;
688 l_pa_request_rec.to_basic_pay := null;
689 l_pa_request_rec.to_total_salary := null;
690 -- VSM Bug # 714487
691 l_pa_request_rec.to_other_pay_amount := NULL;
692 l_pa_request_rec.to_au_overtime := NULL;
693 l_pa_request_rec.to_auo_premium_pay_indicator := NULL;
694 l_pa_request_rec.to_availability_pay := NULL;
695 l_pa_request_rec.to_ap_premium_pay_indicator := NULL;
696 l_pa_request_rec.to_retention_allowance := NULL;
697 l_pa_request_rec.to_supervisory_differential := NULL;
698 l_pa_request_rec.to_staffing_differential := NULL;
699 l_pa_request_rec.to_locality_adj := NULL;
700 -- R11.5 columns
701 l_pa_request_rec.to_retention_allow_percentage := NULL;
702 l_pa_request_rec.to_supervisory_diff_percentage := NULL;
703 l_pa_request_rec.to_staffing_diff_percentage := NULL;
704 END IF;
705
706
707
708 -- Descriptions for the codes passed in
709
710 -- Annuitant_indicator
711 l_pa_request_rec.annuitant_indicator_desc := ghr_pa_requests_pkg.get_lookup_meaning
712 (800
713 ,'GHR_US_ANNUITANT_INDICATOR'
714 ,l_pa_request_rec.annuitant_indicator
715 );
716
717 --WORK_SCHEDULE
718 l_pa_request_rec.work_schedule_desc := ghr_pa_requests_pkg.get_lookup_meaning
719 (800
720 ,'GHR_US_WORK_SCHEDULE'
721 ,l_pa_request_rec.work_schedule
722 );
723
724 ghr_mass_actions_pkg.get_personnel_officer_name
725 (p_personnel_office_id => l_personnel_office_id,
726 p_person_full_name => l_personnel_officer_name,
727 p_approving_off_work_title => l_approving_off_work_title);
728
729
730 --Bug #1295328 initialize
731 IF p_mass_action_type = 'MASS_TRANSFER_OUT' THEN
732 ghr_pa_requests_pkg.get_address_details
733 (p_person_id => l_pa_request_rec.person_id
734 ,p_effective_date => l_pa_request_rec.effective_date
735 ,p_address_line1 => l_pa_request_rec.forwarding_address_line1
736 ,p_address_line2 => l_pa_request_rec.forwarding_address_line2
737 ,p_address_line3 => l_pa_request_rec.forwarding_address_line3
738 ,p_town_or_city => l_pa_request_rec.forwarding_town_or_city
739 ,p_region_2 => l_pa_request_rec.forwarding_region_2
740 ,p_postal_code => l_pa_request_rec.forwarding_postal_code
741 ,p_country => l_pa_request_rec.forwarding_country
742 ,p_territory_short_name => l_pa_request_rec.forwarding_country_short_name);
743 END IF;
744
745
746 -- SF52_asg_rec_type already would have been passed ,
747 -- so is the pos_grp1 --MS
748 -- get person_ei data
749 -- fetch pos_grp2
750 -- Duty station - passed in -- MS
751 -- Create SF52
752
753 l_log_text := 'Error while creating / Updating the PA Request Rec. ';
754 ghr_sf52_api.create_sf52
755 ( p_noa_family_code => l_pa_request_rec.noa_family_code,
756 -- p_routing_group_id => l_pa_request_rec.routing_group_id, -- This would be updated after creation.
757 p_proposed_effective_asap_flag => l_pa_request_rec.proposed_effective_asap_flag,
758 p_academic_discipline => l_pa_request_rec.academic_discipline,
759 p_additional_info_person_id => l_pa_request_rec.additional_info_person_id,
760 p_additional_info_tel_number => l_pa_request_rec.additional_info_tel_number,
761 -- p_altered_pa_request_id => l_pa_request_rec.altered_pa_request_id,
762 p_annuitant_indicator => l_pa_request_rec.annuitant_indicator,
763 p_annuitant_indicator_desc => l_pa_request_rec.annuitant_indicator_desc,
764 p_appropriation_code1 => l_pa_request_rec.appropriation_code1,
765 p_appropriation_code2 => l_pa_request_rec.appropriation_code2,
766 p_authorized_by_person_id => l_pa_request_rec.authorized_by_person_id,
767 p_authorized_by_title => l_pa_request_rec.authorized_by_title,
768 p_award_amount => l_pa_request_rec.award_amount,
769 p_award_uom => l_pa_request_rec.award_uom,
770 p_bargaining_unit_status => l_pa_request_rec.bargaining_unit_status,
771 p_citizenship => l_pa_request_rec.citizenship,
772 p_concurrence_date => l_pa_request_rec.concurrence_date,
773 p_custom_pay_calc_flag => l_pa_request_rec.custom_pay_calc_flag, -- Expecxt
774 p_duty_station_code => l_pa_request_rec.duty_station_code,
775 p_duty_station_desc => l_pa_request_rec.duty_station_desc,
776 p_duty_station_id => l_pa_request_rec.duty_station_id,
777 p_duty_station_location_id => l_pa_request_rec.duty_station_location_id,
778 p_education_level => l_pa_request_rec.education_level,
779 p_effective_date => l_pa_request_rec.effective_date,
780 p_employee_assignment_id => l_pa_request_rec.employee_assignment_id,
781 p_employee_date_of_birth => l_pa_request_rec.employee_date_of_birth,
782 p_employee_first_name => l_pa_request_rec.employee_first_name,
783 p_employee_last_name => l_pa_request_rec.employee_last_name,
784 p_employee_middle_names => l_pa_request_rec.employee_middle_names,
785 p_employee_national_identifier => l_pa_request_rec.employee_national_identifier,
786 p_fegli => l_pa_request_rec.fegli,
787 p_fegli_desc => l_pa_request_rec.fegli_desc,
788 p_first_action_la_code1 => l_pa_request_rec.first_action_la_code1,
789 p_first_action_la_code2 => l_pa_request_rec.first_action_la_code2,
790 p_first_action_la_desc1 => l_pa_request_rec.first_action_la_desc1,
791 p_first_action_la_desc2 => l_pa_request_rec.first_action_la_desc2,
792 -- p_first_noa_cancel_or_correct => l_pa_request_rec.first_noa_cancel_or_correct,
793 p_first_noa_code => l_pa_request_rec.first_noa_code,
794 p_first_noa_desc => l_pa_request_rec.first_noa_desc,
795 p_first_noa_id => l_pa_request_rec.first_noa_id,
796 p_first_noa_information1 => l_pa_request_rec.first_noa_information1,
797 p_first_noa_pa_request_id => l_pa_request_rec.first_noa_pa_request_id,
798 p_flsa_category => l_pa_request_rec.flsa_category,
799 p_forwarding_address_line1 => l_pa_request_rec.forwarding_address_line1,
800 p_forwarding_address_line2 => l_pa_request_rec.forwarding_address_line2,
801 p_forwarding_address_line3 => l_pa_request_rec.forwarding_address_line3,
802 p_forwarding_country => l_pa_request_rec.forwarding_country,
803 p_forwarding_country_short_nam => l_pa_request_rec.forwarding_country_short_name,
804 p_forwarding_postal_code => l_pa_request_rec.forwarding_postal_code,
805 p_forwarding_region_2 => l_pa_request_rec.forwarding_region_2,
806 p_forwarding_town_or_city => l_pa_request_rec.forwarding_town_or_city,
807 p_from_adj_basic_pay => l_pa_request_rec.from_adj_basic_pay,
808 p_from_basic_pay => l_pa_request_rec.from_basic_pay,
809 p_from_grade_or_level => l_pa_request_rec.from_grade_or_level,
810 p_from_locality_adj => l_pa_request_rec.from_locality_adj,
811 p_from_occ_code => l_pa_request_rec.from_occ_code,
812 p_from_other_pay_amount => l_pa_request_rec.from_other_pay_amount,
813 p_from_pay_basis => l_pa_request_rec.from_pay_basis,
814 p_from_pay_plan => l_pa_request_rec.from_pay_plan,
815 p_from_position_id => l_pa_request_rec.from_position_id,
816 p_from_position_org_line1 => l_pa_request_rec.from_position_org_line1,
817 p_from_position_org_line2 => l_pa_request_rec.from_position_org_line2,
818 p_from_position_org_line3 => l_pa_request_rec.from_position_org_line3,
819 p_from_position_org_line4 => l_pa_request_rec.from_position_org_line4,
820 p_from_position_org_line5 => l_pa_request_rec.from_position_org_line5,
821 p_from_position_org_line6 => l_pa_request_rec.from_position_org_line6,
822 p_from_position_number => l_pa_request_rec.from_position_number,
823 p_from_position_seq_no => l_pa_request_rec.from_position_seq_no,
824 p_from_position_title => l_pa_request_rec.from_position_title,
825 p_from_step_or_rate => l_pa_request_rec.from_step_or_rate,
826 p_from_total_salary => l_pa_request_rec.from_total_salary,
827 p_functional_class => l_pa_request_rec.functional_class,
828 p_notepad => l_pa_request_rec.notepad,
829 p_part_time_hours => l_pa_request_rec.part_time_hours,
830 p_pay_rate_determinant => l_pa_request_rec.pay_rate_determinant,
831 p_person_id => l_pa_request_rec.person_id,
832 p_position_occupied => l_pa_request_rec.position_occupied,
833 p_proposed_effective_date => l_pa_request_rec.proposed_effective_date,
834 p_requested_by_person_id => l_pa_request_rec.requested_by_person_id,
835 p_requested_by_title => l_pa_request_rec.requested_by_title,
836 p_requested_date => l_pa_request_rec.requested_date,
837 p_requesting_office_remarks_de => l_pa_request_rec.requesting_office_remarks_desc,
838 p_requesting_office_remarks_fl => l_pa_request_rec.requesting_office_remarks_flag,
839 -- p_request_number => l_pa_request_rec.request_number,
840 p_resign_and_retire_reason_des => l_pa_request_rec.resign_and_retire_reason_desc,
841 p_retirement_plan => l_pa_request_rec.retirement_plan,
842 p_retirement_plan_desc => l_pa_request_rec.retirement_plan_desc,
843 -- p_second_action_la_code1 => l_pa_request_rec.second_action_la_code1,
844 -- p_second_action_la_code2 => l_pa_request_rec.second_action_la_code2,
845 -- p_second_action_la_desc1 => l_pa_request_rec.second_action_la_desc1,
846 -- p_second_action_la_desc2 => l_pa_request_rec.second_action_la_desc2,
847 -- p_second_noa_cancel_or_correct
848 -- p_second_noa_code => l_pa_request_rec.second_noa_code,
849 -- p_second_noa_desc => l_pa_request_rec.second_noa_desc,
850 -- p_second_noa_id => l_pa_request_rec.second_noa_id,
851 -- p_second_noa_pa_request_id => l_pa_request_rec.
852 p_service_comp_date => l_pa_request_rec.service_comp_date,
853 p_supervisory_status => l_pa_request_rec.supervisory_status,
854 p_tenure => l_pa_request_rec.tenure,
855 p_to_adj_basic_pay => l_pa_request_rec.to_adj_basic_pay,
856 p_to_basic_pay => l_pa_request_rec.to_basic_pay,
857 p_to_grade_id => l_pa_request_rec.to_grade_id,
858 p_to_grade_or_level => l_pa_request_rec.to_grade_or_level,
859 p_to_job_id => l_pa_request_rec.to_job_id,
860 p_to_locality_adj => l_pa_request_rec.to_locality_adj,
861 p_to_occ_code => l_pa_request_rec.to_occ_code,
862 p_to_organization_id => l_pa_request_rec.to_organization_id,
863 p_to_other_pay_amount => l_pa_request_rec.to_other_pay_amount,
864 p_to_au_overtime => l_pa_request_rec.to_au_overtime,
865 p_to_auo_premium_pay_indicator => l_pa_request_rec.to_auo_premium_pay_indicator,
866 p_to_availability_pay => l_pa_request_rec.to_availability_pay,
867 p_to_ap_premium_pay_indicator => l_pa_request_rec.to_ap_premium_pay_indicator,
868 p_to_retention_allowance => l_pa_request_rec.to_retention_allowance,
869 p_to_supervisory_differential => l_pa_request_rec.to_supervisory_differential,
870 p_to_staffing_differential => l_pa_request_rec.to_staffing_differential,
871 p_to_pay_basis => l_pa_request_rec.to_pay_basis,
872 p_to_pay_plan => l_pa_request_rec.to_pay_plan,
873 p_to_position_id => l_pa_request_rec.to_position_id,
874 p_to_position_org_line1 => l_pa_request_rec.to_position_org_line1,
875 p_to_position_org_line2 => l_pa_request_rec.to_position_org_line2,
876 p_to_position_org_line3 => l_pa_request_rec.to_position_org_line3,
877 p_to_position_org_line4 => l_pa_request_rec.to_position_org_line4,
878 p_to_position_org_line5 => l_pa_request_rec.to_position_org_line5,
879 p_to_position_org_line6 => l_pa_request_rec.to_position_org_line6,
880 p_to_position_number => l_pa_request_rec.to_position_number,
881 p_to_position_seq_no => l_pa_request_rec.to_position_seq_no,
882 p_to_position_title => l_pa_request_rec.to_position_title,
883 p_to_step_or_rate => l_pa_request_rec.to_step_or_rate,
884 p_to_total_salary => l_pa_request_rec.to_total_salary,
885 p_veterans_preference => l_pa_request_rec.veterans_preference,
886 p_veterans_pref_for_rif => l_pa_request_rec.veterans_pref_for_rif,
887 p_veterans_status => l_pa_request_rec.veterans_status,
888 p_work_schedule => l_pa_request_rec.work_schedule,
889 p_work_schedule_desc => l_pa_request_rec.work_schedule_desc,
890 p_year_degree_attained => l_pa_request_rec.year_degree_attained,
891 p_first_lac1_information1 => l_pa_request_rec.first_lac1_information1,
892 p_first_lac1_information2 => l_pa_request_rec.first_lac1_information2,
893 p_first_lac1_information3 => l_pa_request_rec.first_lac1_information3,
894 p_first_lac1_information4 => l_pa_request_rec.first_lac1_information4,
895 p_first_lac1_information5 => l_pa_request_rec.first_lac1_information5,
896 p_first_lac2_information1 => l_pa_request_rec.first_lac2_information1,
897 p_first_lac2_information2 => l_pa_request_rec.first_lac2_information2,
898 p_first_lac2_information3 => l_pa_request_rec.first_lac2_information3,
899 p_first_lac2_information4 => l_pa_request_rec.first_lac2_information4,
900 p_first_lac2_information5 => l_pa_request_rec.first_lac2_information5,
901 p_second_lac1_information1 => l_pa_request_rec.second_lac1_information1,
902 p_second_lac1_information2 => l_pa_request_rec.second_lac1_information1,
903 p_second_lac1_information3 => l_pa_request_rec.second_lac1_information1,
904 p_second_lac1_information4 => l_pa_request_rec.second_lac1_information1,
905 p_second_lac1_information5 => l_pa_request_rec.second_lac1_information1,
906 p_print_sf50_flag => 'N', -- true for all ??
907 p_printer_name => Null,
908 p_1_attachment_modified_flag => 'N',
909 p_1_approved_flag => 'N',
910 p_1_user_name_acted_on => Null,
911 p_1_action_taken => 'NOT_ROUTED',
912 p_2_user_name_routed_to => Null,
913 p_2_groupbox_id => Null,
914 p_2_routing_list_id => Null,
915 p_2_routing_seq_number => Null,
916 p_to_retention_allow_percentag => l_pa_request_rec.to_retention_allow_percentage,
917 p_to_supervisory_diff_percenta => l_pa_request_rec.to_supervisory_diff_percentage,
918 p_to_staffing_diff_percentage => l_pa_request_rec.to_staffing_diff_percentage ,
919 p_pa_request_id => l_pa_request_rec.pa_request_id,
920 p_par_object_version_number => l_pa_request_rec.object_version_number,
921 p_1_pa_routing_history_id => l_1_pa_routing_history_id,
922 p_1_prh_object_version_number => l_1_prh_object_version_number,
923 p_2_pa_routing_history_id => l_2_pa_routing_history_id,
924 p_2_prh_object_version_number => l_2_prh_object_version_number
925 ,p_approving_official_full_name => l_personnel_officer_name
926 ,p_approval_date => sysdate
927 ,p_approving_official_work_titl => l_approving_off_work_title
928 ,p_1_approval_status => 'APPROVE'
929 --Added for 3843306
930 ,p_rpa_type => l_pa_request_rec.rpa_type
931 ,p_mass_action_id => l_pa_request_rec.mass_action_id
932 -- FWFA changes bug#4444609
933 ,p_from_pay_table_identifier => l_pa_request_rec.from_pay_table_identifier
934 ,p_to_pay_table_identifier => l_pa_request_rec.to_pay_table_identifier
935 ,p_input_pay_rate_determinant => l_pa_request_rec.input_pay_rate_determinant
936 -- FWFA Changes
937 );
938
939
940
941
942
943 -- Having got the l_1_pa_routing_history_id and l_1_prh_object_version_number
944 -- from the above procedure, use it in the ghr_prh_upd.upd to update the groupbox_id
945 -- ( use ghr_mass_actions_pkg.get_personnel_off_groupbox to get the groupbox_id and
946 -- the routing_group_id)
947 -- Also update the ghr_pa_requests with the routing_group_id passed out
948 -- ghr_par_upd.upd
949 -- If the groupbox_id is null, then rollback to create_52_for_mass_changes and then nake an
950 -- entry in the ghr_process_log -- (not sure about this part)
951
952 BEGIN
953
954 ghr_mass_actions_pkg.get_personnel_off_groupbox(
955 nvl(l_pa_request_rec.from_position_id,
956 l_pa_request_rec.to_position_id),
957 l_pa_request_rec.effective_date,
958 l_groupbox_id, -- Out put parameters
959 l_routing_group_id);
960
961 EXCEPTION
962
963 WHEN OTHERS THEN
964 -- Bug#4355764 Modified the Group box error message.
965 --Bug # 9329643 Modified SSN to Emp No
966 l_log_text := 'Groupbox error for employee with Emp No: '||l_employee_number||'; Name:'||l_full_name
967 || '; Error: '||sqlerrm(sqlcode);
968 RAISE groupbox_err;
969 END;
970
971 -- Call ghr_prh_upd.upd here
972
973 ghr_prh_upd.upd(
974 p_pa_routing_history_id => l_1_pa_routing_history_id,
975 p_groupbox_id => l_groupbox_id,
976 p_object_version_number => l_1_prh_object_version_number);
977
978
979 -- Call ghr_par_upd.upd here
980 ghr_par_upd.upd(
981 p_pa_request_id => l_pa_request_rec.pa_request_id,
982 p_routing_group_id => l_routing_group_id,
983 p_object_version_number => l_pa_request_rec.object_version_number);
984
985 l_pa_request_rec.to_retention_allowance := l_new_retention_allowance;
986 l_pa_request_rec.to_supervisory_differential := l_new_supervisory_differential;
987
988 p_pa_request_rec := l_pa_request_rec;
989
990
991 hr_utility.set_location('After creation of PA REQUEST '||to_char(l_pa_request_rec.pa_request_id),10);
992
993 -- Update the request number with a prefix to indicate what mass action it is.
994 -- and concatenate it with the request id.
995
996 -- Note : I guess it is OK in case of mass actions
997 -- to write to the shadow after it has written
998 -- to the ghr_pa_requests table . If not we would have to
999 -- write to the shadow first
1000 -- and then pass the pa_request_id into the create_sf52 procedure call
1001
1002 l_log_text := 'Error while creating PA Request Shadow row ';
1003 --Bug# 14055214 added MASS_NOA_SEL
1004 IF p_mass_action_type IN ('MASS_REALIGNMENT','MASS_NOA_SEL') THEN
1005 -- We need to put into the shadow table, the values that we are changing through
1006 -- the 52 which are AP/UE so that, UPDATE HR will not refresh the values.
1007
1008 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
1009 (p_location_id => l_duty_station_location_id
1010 ,p_duty_station_id => l_pa_request_rec.duty_station_id);
1011
1012 ghr_pa_requests_pkg.get_duty_station_details
1013 (p_duty_station_id => l_pa_request_rec.duty_station_id
1014 ,p_effective_date => l_pa_request_rec.effective_date
1015 ,p_duty_station_code => l_pa_request_rec.duty_station_code
1016 ,p_duty_station_desc => l_pa_request_rec.duty_station_desc);
1017
1018 -- Bug # 654126
1019 l_pa_request_rec.duty_station_location_id := l_duty_station_location_id;
1020 l_pa_request_rec.appropriation_code1 := l_appropriation_code1;
1021 l_pa_request_rec.appropriation_code2 := l_appropriation_code2;
1022
1023 END IF;
1024
1025 -- # Bug 51133
1026 create_refreshed_sf52_shadow(
1027 p_action => p_mass_action_type,
1028 p_sf52_data => l_pa_request_rec);
1029 -- ghr_process_sf52.create_shadow_row
1030 -- (P_SF52_DATA => l_pa_request_rec
1031 -- );
1032 --dbms_output.put_line('after creating shadow row');
1033
1034
1035 -- Update ghr_pa_requests with request_number and
1036 -- approval_date / approving_off._work_title
1037 -- which will then cause this SF52 to be
1038 -- picked by the concurrent program that handles the FUTURE Action.
1039
1040 l_log_text := 'Error while creating / Updating PA Request Rec. ';
1041
1042 --dbms_output.put_line('before updating par');
1043 -- Bug # 711220 (Field Employing Dept. or Agency, Agency Code, Per. office id
1044 ghr_sf52_post_update.get_notification_details(
1045 p_pa_request_id => l_pa_request_rec.pa_request_id,
1046 p_effective_date => l_pa_request_rec.effective_date,
1047 p_from_position_id => l_pa_request_rec.from_position_id,
1048 p_to_position_id => l_pa_request_rec.to_position_id,
1049 p_agency_code => l_pa_request_rec.agency_code,
1050 p_from_agency_code => l_pa_request_rec.from_agency_code,
1051 p_from_agency_desc => l_pa_request_rec.from_agency_desc,
1052 p_from_office_symbol => l_pa_request_rec.from_office_symbol,
1053 p_personnel_office_id => l_pa_request_rec.personnel_office_id,
1054 p_employee_dept_or_agency => l_pa_request_rec.employee_dept_or_agency,
1055 p_to_office_symbol => l_pa_request_rec.to_office_symbol);
1056
1057 -- VSM : Bug # 677880, Added sf50 parameters to populate the values.
1058 ghr_par_upd.upd
1059 (p_pa_request_id => l_pa_request_rec.pa_request_id,
1060 p_object_version_number => l_pa_request_rec.object_version_number,
1061 p_agency_code => l_pa_request_rec.agency_code,
1062 p_from_agency_code => l_pa_request_rec.from_agency_code,
1063 p_from_agency_desc => l_pa_request_rec.from_agency_desc,
1064 p_from_office_symbol => l_pa_request_rec.from_office_symbol,
1065 p_personnel_office_id => l_pa_request_rec.personnel_office_id,
1066 p_employee_dept_or_agency => l_pa_request_rec.employee_dept_or_agency,
1067 p_to_office_symbol => l_pa_request_rec.to_office_symbol,
1068 p_request_number => l_pa_request_num_prefix || to_char(l_pa_request_rec.pa_request_id),
1069 p_sf50_approving_ofcl_full_nam => l_personnel_officer_name,
1070 p_sf50_approval_date => sysdate ,
1071 p_sf50_approving_ofcl_work_tit => l_approving_off_work_title
1072 );
1073
1074 --BEGIN BUG# 14055214
1075 IF p_mass_action_type = 'MASS_NOA_SEL' THEN
1076 IF l_pa_request_rec.first_noa_code IN ('800') THEN
1077 IF l_target_flsa_category IS NOT NULL THEN
1078 ghr_par_upd.upd(
1079 p_pa_request_id => l_pa_request_rec.pa_request_id,
1080 p_flsa_category => l_target_flsa_category,
1081 p_object_version_number => l_pa_request_rec.object_version_number);
1082 END IF;
1083 IF l_target_barg_unit_status IS NOT NULL THEN
1084 ghr_par_upd.upd(
1085 p_pa_request_id => l_pa_request_rec.pa_request_id,
1086 p_bargaining_unit_status => l_target_barg_unit_status,
1087 p_object_version_number => l_pa_request_rec.object_version_number);
1088 END IF;
1089 IF l_target_occ_code IS NOT NULL THEN
1090 ghr_par_upd.upd(
1091 p_pa_request_id => l_pa_request_rec.pa_request_id,
1092 p_to_occ_code => l_target_occ_code,
1093 p_to_job_id => l_target_job_id,
1094 p_object_version_number => l_pa_request_rec.object_version_number);
1095 END IF;
1096 --Begin Bug# 14410401
1097 IF l_target_functional_class IS NOT NULL THEN
1098 ghr_par_upd.upd(
1099 p_pa_request_id => l_pa_request_rec.pa_request_id,
1100 p_functional_class => l_target_functional_class,
1101 p_object_version_number => l_pa_request_rec.object_version_number);
1102 END IF;
1103 --End Bug# 14410401
1104 IF l_target_agency_code_subelmt IS NOT NULL THEN
1105 l_employee_dept_or_agency := hr_general.decode_lookup
1106 (p_lookup_type => 'GHR_US_AGENCY_CODE',
1107 p_lookup_code => l_target_agency_code_subelmt
1108 );
1109 ghr_par_upd.upd(
1110 p_pa_request_id => l_pa_request_rec.pa_request_id,
1111 p_agency_code => l_target_agency_code_subelmt,
1112 p_employee_dept_or_agency => l_employee_dept_or_agency,
1113 p_object_version_number => l_pa_request_rec.object_version_number);
1114 END IF;
1115 END IF;
1116 IF l_pa_request_rec.first_noa_code IN ('792') THEN
1117 IF l_target_barg_unit_status IS NOT NULL THEN
1118 ghr_par_upd.upd(
1119 p_pa_request_id => l_pa_request_rec.pa_request_id,
1120 p_bargaining_unit_status => l_target_barg_unit_status,
1121 p_object_version_number => l_pa_request_rec.object_version_number);
1122 END IF;
1123 END IF;
1124 END IF;
1125 --End BUG# 14055214
1126
1127 --dbms_output.put_line('after updating par');
1128
1129 l_pa_request_rec.request_number := l_pa_request_num_prefix|| to_char(l_pa_request_rec.pa_request_id);
1130
1131
1132 -- Since we are not calling the process_sf52 by passing
1133 -- 'FUTURE_ACTION' as the action_taken in the
1134 -- above procedure, updating it on the routing history table
1135 -- by using the row handler, just in
1136 -- case .
1137
1138 for routing_history_id in c_routing_history loop
1139 l_pa_routing_history_id := routing_history_id.pa_routing_history_id;
1140 l_object_version_number := routing_history_id.object_version_number;
1141 exit;
1142 end loop;
1143
1144 ghr_prh_upd.upd
1145 (p_pa_routing_history_id => l_pa_routing_history_id,
1146 p_object_version_number => l_object_version_number,
1147 p_action_taken => 'FUTURE_ACTION'
1148 );
1149 --dbms_output.put_line('after updating prh');
1150
1151 -- Sending Back the PA Request ID to the calling program to
1152 -- create the extra info record.
1153
1154 p_pa_request_rec := l_pa_request_rec;
1155
1156 Exception
1157 When groupbox_err then
1158 rollback to create_sf52_for_mass_changes;
1159 hr_utility.set_location('Error occured in ' || l_proc , 1);
1160
1161 IF l_log_text is NULL THEN
1162 l_log_text := 'Error while create / Update the PA Request Rec. ';
1163 END IF;
1164 p_errbuf := substr(l_log_text || 'Details in GHR_PROCESS_LOG',1,2000);
1165 p_retcode := 1;
1166 -- For group box error, name and SSN are added before raising this error.
1167 l_log_text := substr(l_log_text,1,2000);
1168
1169 hr_utility.set_location('before creating entry in log file',10);
1170 ghr_mto_int.log_message( p_procedure => 'While Creating SF52'
1171 ,p_message => l_log_text);
1172
1173 hr_utility.set_location('created entry in log file',20);
1174 COMMIT;
1175 WHEN others THEN
1176 p_pa_request_rec := l_pa_request_rec;
1177
1178 rollback to create_sf52_for_mass_changes;
1179 hr_utility.set_location('Error occured in ' || l_proc , 1);
1180
1181 IF l_log_text is NULL THEN
1182 l_log_text := 'Error while create / Update the PA Request Rec. ';
1183 END IF;
1184 p_errbuf := l_log_text || 'Details in GHR_PROCESS_LOG';
1185 p_retcode := 1;
1186
1187 -- Bug#3718167 Added Full Name, SSN in the log text
1188 --Bug # 9329643 Modified SSN to Emp No
1189 l_log_text := l_log_text ||' for '||l_full_name||' Emp No: '||l_employee_number||' Sql error : '||sqlerrm(sqlcode);
1190
1191 hr_utility.set_location('before creating entry in log file',10);
1192
1193 ghr_mto_int.log_message(
1194 p_procedure => 'While Creating SF52'
1195 ,p_message => l_log_text);
1196
1197
1198 hr_utility.set_location('created entry in log file',20);
1199
1200 commit;
1201
1202 end create_sf52_for_mass_changes;
1203
1204
1205 Procedure create_remarks
1206 (p_pa_request_rec in ghr_pa_requests%rowtype,
1207 p_remark_code in ghr_remarks.code%type
1208 )
1209 is
1210
1211 l_proc varchar2(72) := g_package || 'create_remarks';
1212 l_remark_id ghr_remarks.remark_id%type;
1213 l_description ghr_pa_remarks.description%type;
1214 -- Bug#4256022 Declared the variable l_description_out
1215 l_description_out ghr_pa_remarks.description%type;
1216 l_remark_code_information1 ghr_pa_remarks.remark_code_information1%type;
1217 l_remark_code_information2 ghr_pa_remarks.remark_code_information2%type;
1218 l_remark_code_information3 ghr_pa_remarks.remark_code_information3%type;
1219 l_remark_code_information4 ghr_pa_remarks.remark_code_information4%type;
1220 l_remark_code_information5 ghr_pa_remarks.remark_code_information5%type;
1221 l_retained_grade_rec ghr_pay_calc.retained_Grade_rec_type;
1222 l_pa_remark_id ghr_pa_remarks.pa_remark_id%type;
1223 l_object_version_number ghr_pa_remarks.object_version_number%type;
1224
1225 begin
1226
1227 -- get remark_id remark_desc, remark_code_information1, ...
1228 hr_utility.set_location('Entering ' || l_proc,5);
1229
1230 ghr_mass_actions_pkg.get_remark_id_desc
1231 (p_remark_code => p_remark_code,
1232 p_effective_date => trunc(nvl(p_pa_request_rec.effective_date,sysdate)),
1233 p_remark_id => l_remark_id,
1234 p_remark_desc => l_description
1235 );
1236 hr_utility.set_location(l_proc,10);
1237 l_remark_code_information1 := Null;
1238 l_remark_code_information2 := Null;
1239 l_remark_code_information3 := Null;
1240 l_remark_code_information4 := Null;
1241 l_remark_code_information5 := Null;
1242
1243 If p_remark_code = 'X44' then
1244 hr_utility.set_location(l_proc,15);
1245 -- if there are entries for insertion values then alter the description accordingly.
1246 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details
1247 (p_person_id => p_pa_request_rec.person_id,
1248 p_effective_Date => trunc(nvl(p_pa_request_rec.effective_Date,sysdate))
1249 );
1250 -- handle the exception if this fails. ??!!!!!
1251 -- get retention grade details
1252 l_remark_code_information1 := l_retained_grade_rec.step_or_rate;
1253 l_remark_code_information2 := l_retained_grade_rec.pay_plan;
1254 -- l_remark_code_information3 := l_retained_grade_rec.grade_or_level;
1255 -- Bug#4256022 Passed parameter l_description_out to the procedure call
1256 -- and reassigned it back to l_description to avoid NOCOPY related problems..
1257 ghr_mass_actions_pkg.replace_insertion_values
1258 (p_desc => l_description,
1259 p_information1 => l_remark_code_information1,
1260 p_information2 => l_remark_code_information2,
1261 p_information3 => l_remark_code_information3,
1262 p_information4 => l_remark_code_information4,
1263 p_information5 => l_remark_code_information5,
1264 p_desc_out => l_description_out
1265 );
1266 l_description := l_description_out;
1267 End if;
1268
1269 ghr_pa_remarks_api.create_pa_remarks
1270 (
1271 p_PA_REQUEST_ID => p_pa_request_rec.pa_request_id,
1272 p_REMARK_ID => l_remark_id,
1273 p_DESCRIPTION => l_description,
1274 P_REMARK_CODE_INFORMATION1 => l_remark_code_information1,
1275 P_REMARK_CODE_INFORMATION2 => l_remark_code_information2,
1276 P_REMARK_CODE_INFORMATION3 => l_remark_code_information3,
1277 P_REMARK_CODE_INFORMATION4 => l_remark_code_information4,
1278 P_REMARK_CODE_INFORMATION5 => l_remark_code_information5,
1279 P_PA_REMARK_ID => l_pa_remark_id,
1280 p_OBJECT_VERSION_NUMBER => l_object_version_number
1281 );
1282 End create_remarks;
1283
1284 Procedure create_refreshed_sf52_shadow(
1285 p_action in varchar2,
1286 p_sf52_data in ghr_pa_requests%rowtype) is
1287
1288 l_sf52_data ghr_pa_requests%rowtype;
1289 l_service_comp_date varchar2(20);
1290 l_dummy varchar2(200);
1291
1292 Begin
1293
1294 l_sf52_data := p_sf52_data;
1295
1296 if nvl(p_action, 'xXxX#$%') = 'MASS_TRANSFER_IN' then
1297 ghr_pa_requests_pkg.get_SF52_person_ddf_details
1298 (p_person_id => l_sf52_data.person_id
1299 ,p_date_effective => l_sf52_data.effective_date
1300 ,p_citizenship => l_sf52_data.citizenship
1301 ,p_veterans_preference => l_sf52_data.veterans_preference
1302 ,p_veterans_pref_for_rif => l_sf52_data.veterans_pref_for_rif
1303 ,p_veterans_status => l_sf52_data.veterans_status
1304 ,p_scd_leave => l_service_comp_date);
1305
1306 -- l_sf52_data.citizenship := l_citizenship;
1307 -- l_sf52_data.veterans_preference := l_veterans_preference;
1308 -- l_sf52_data.veterans_pref_for_rif := l_veterans_pref_for_rif;
1309 -- l_sf52_data.veterans_status := l_veterans_status;
1310 ---l_sf52_data.service_comp_date := to_date(l_service_comp_date, 'DD-MON-YYYY');
1311 l_sf52_data.service_comp_date := fnd_date.canonical_to_date(l_service_comp_date);
1312
1313 ghr_api.return_education_details(
1314 p_person_id => l_sf52_data.person_id
1315 ,p_effective_date => l_sf52_data.effective_date
1316 ,p_education_level => l_sf52_data.education_level
1317 ,p_academic_discipline => l_sf52_data.academic_discipline
1318 ,p_year_degree_attained => l_sf52_data.year_degree_attained);
1319
1320 -- l_sf52_data.education_level := l_education_level;
1321 -- l_sf52_data.academic_discipline := l_academic_discipline;
1322 -- l_sf52_data.year_degree_attained := l_year_degree_attained;
1323
1324 ghr_pa_requests_pkg.get_SF52_pos_ddf_details
1325 (p_position_id => l_sf52_data.to_position_id
1326 ,p_date_effective => l_sf52_data.effective_date
1327 ,p_flsa_category => l_sf52_data.flsa_category
1328 ,p_bargaining_unit_status => l_sf52_data.bargaining_unit_status
1329 ,p_work_schedule => l_sf52_data.work_schedule
1330 ,p_functional_class => l_sf52_data.functional_class
1331 ,p_supervisory_status => l_sf52_data.supervisory_status
1332 ,p_position_occupied => l_sf52_data.position_occupied
1333 ,p_appropriation_code1 => l_sf52_data.appropriation_code1
1334 ,p_appropriation_code2 => l_sf52_data.appropriation_code2
1335 ,p_personnel_office_id => l_dummy
1336 ,p_office_symbol => l_dummy
1337 ,p_part_time_hours => l_sf52_data.part_time_hours);
1338 -- Bug#2708682 Set Tenure to Null
1339 l_sf52_data.tenure := null;
1340 End if;
1341
1342 ghr_process_sf52.create_shadow_row
1343 (P_SF52_DATA => l_sf52_data);
1344
1345 End create_refreshed_sf52_shadow;
1346
1347 end ghr_mass_changes;