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