[Home] [Help]
PACKAGE BODY: APPS.GHR_MLC_PKG
Source
1 PACKAGE BODY GHR_MLC_PKG AS
2 /* $Header: ghmlcexe.pkb 120.19.12020000.2 2012/07/05 14:30:12 amnaraya ship $ */
3
4 g_no number := 0;
5 g_package varchar2(32) := 'GHR_MLC_PKG';
6 g_proc varchar2(72) := null;
7 g_effective_date date;
8
9 l_log_text varchar2(2000) := null;
10 l_mlcerrbuf varchar2(2000) := null;
11
12 procedure execute_mlc (p_errbuf out nocopy varchar2,
13 p_retcode out nocopy number,
14 p_mass_salary_id in number,
15 p_action in varchar2) is
16
17 p_mass_salary varchar2(32);
18 l_p_locality_area_code ghr_mass_salaries.locality_pay_area_code%type;
19
20 --
21 -- Main Cursor which fetches from per_assignments_f and per_people_f
22 --
23 -- Bug 4377361 included EMP_APL for person type condition
24
25 cursor cur_people (effective_date date, p_org_id number) is
26 select ppf.person_id PERSON_ID,
27 ppf.first_name FIRST_NAME,
28 ppf.last_name LAST_NAME,
29 ppf.middle_names MIDDLE_NAMES,
30 ppf.full_name FULL_NAME,
31 ppf.date_of_birth DATE_OF_BIRTH,
32 ppf.national_identifier NATIONAL_IDENTIFIER,
33 --Bug # 9329643
34 ppf.employee_number EMPLOYEE_NUMBER,
35 paf.position_id POSITION_ID,
36 paf.assignment_id ASSIGNMENT_ID,
37 paf.grade_id GRADE_ID,
38 paf.job_id JOB_ID,
39 paf.location_id LOCATION_ID,
40 paf.organization_id ORGANIZATION_ID,
41 paf.business_group_id BUSINESS_GROUP_ID
42 from per_assignments_f paf,
43 per_people_f ppf,
44 per_person_types ppt,
45 per_assignment_status_types pas_t,
46 hr_location_extra_info hlei,
47 ghr_duty_stations_f gdsf,
48 ghr_locality_pay_areas_f glpa
49 where ppf.person_id = paf.person_id
50 and paf.primary_flag = 'Y'
51 and paf.assignment_type <> 'B'
52 and paf.assignment_status_type_id = pas_t.assignment_status_type_id
53 and upper(pas_t.user_status) not in (
54 'TERMINATE ASSIGNMENT', 'ACTIVE APPLICATION', 'OFFER', 'ACCEPTED',
55 'TERMINATE APPLICATION', 'END', 'TERMINATE APPOINTMENT', 'SEPARATED')
56 and effective_date between paf.effective_start_date and paf.effective_end_date
57 and ppf.person_type_id = ppt.person_type_id
58 and ppt.system_person_type IN ('EMP','EMP_APL')
59 and effective_date between ppf.effective_start_date and ppf.effective_end_date
60 and paf.organization_id + 0 = nvl(p_org_id, paf.organization_id)
61 and paf.position_id is not null
62 and paf.location_id = hlei.location_id
63 and hlei.information_type = 'GHR_US_LOC_INFORMATION'
64 and hlei.lei_information3 = gdsf.duty_station_id
65 and gdsf.locality_pay_area_id = glpa.locality_pay_area_id
66 and effective_date between gdsf.effective_start_date and gdsf.effective_end_date
67 and effective_date between glpa.effective_start_date and glpa.effective_end_date
68 and glpa.locality_pay_area_code = l_p_locality_area_code
69 order by ppf.person_id;
70
71 CURSOR ghr_msl (p_msl_id number) is
72 SELECT name, effective_date, mass_salary_id, user_table_id, submit_flag,
73 executive_order_number, executive_order_date, ROWID, PA_REQUEST_ID,
74 ORGANIZATION_ID, DUTY_STATION_ID, PERSONNEL_OFFICE_ID,
75 AGENCY_CODE_SUBELEMENT, OPM_ISSUANCE_NUMBER, OPM_ISSUANCE_DATE,
76 locality_pay_area_code
77 FROM ghr_mass_salaries
78 WHERE MASS_SALARY_ID = p_msl_id
79 ---and process_type = <>
80 for update of user_table_id nowait;
81
82 CURSOR get_sal_chg_fam is
83 SELECT NOA_FAMILY_CODE
84 FROM ghr_families
85 WHERE NOA_FAMILY_CODE in
86 (SELECT NOA_FAMILY_CODE FROM ghr_noa_families
87 WHERE nature_of_action_id in
88 (SELECT nature_of_action_id
89 FROM ghr_nature_of_actions
90 WHERE code = nvl(ghr_msl_pkg.g_first_noa_code,'895') )
91 ) and proc_method_flag = 'Y';
92
93 l_assignment_id per_assignments_f.assignment_id%type;
94 l_position_id per_assignments_f.position_id%type;
95 l_grade_id per_assignments_f.grade_id%type;
96 l_business_group_id per_assignments_f.business_group_id%type;
97
98 l_position_title varchar2(300);
99 l_position_number varchar2(20);
100 l_position_seq_no varchar2(20);
101
102 l_msl_cnt number := 0;
103 l_recs_failed number := 0;
107 l_pay_rate_determinant varchar2(35);
104
105 l_tenure varchar2(35);
106 l_annuitant_indicator varchar2(35);
108 l_work_schedule varchar2(35);
109 l_part_time_hour varchar2(35);
110 l_pay_table_id number;
111 l_pay_plan varchar2(30);
112 l_grade_or_level varchar2(30);
113 l_step_or_rate varchar2(30);
114 l_pay_basis varchar2(30);
115 l_location_id number;
116 l_duty_station_id number;
117 l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
118 l_duty_station_code ghr_pa_requests.duty_station_code%type;
119 l_effective_date date;
120 l_personnel_office_id varchar2(300);
121 l_org_structure_id varchar2(300);
122 l_sub_element_code varchar2(300);
123
124 l_old_basic_pay number;
125 l_old_avail_pay number;
126 l_old_loc_diff number;
127 l_tot_old_sal number;
128 l_old_auo_pay number;
129 l_old_ADJ_basic_pay number;
130 l_other_pay number;
131
132
133 l_auo_premium_pay_indicator varchar2(30);
134 l_ap_premium_pay_indicator varchar2(30);
135 l_retention_allowance number;
136 l_retention_allow_perc number;
137 l_new_retention_allowance number;
138 l_supervisory_differential number;
139 l_supervisory_diff_perc number;
140 l_new_supervisory_differential number;
141 l_staffing_differential number;
142
143 l_new_avail_pay number;
144 l_new_loc_diff number;
145 l_tot_new_sal number;
146 l_new_auo_pay number;
147
148 l_new_basic_pay number;
149 l_new_locality_adj number;
150 l_new_adj_basic_pay number;
151 l_new_total_salary number;
152 l_new_other_pay_amount number;
153 l_new_au_overtime number;
154 l_new_availability_pay number;
155 l_out_step_or_rate varchar2(30);
156 l_out_pay_rate_determinant varchar2(30);
157 l_PT_eff_start_date date;
158 l_open_pay_fields boolean;
159 l_message_set boolean;
160 l_calculated boolean;
161
162 l_mass_salary_id number;
163 l_user_table_id number;
164 l_submit_flag varchar2(2);
165 l_executive_order_number ghr_mass_salaries.executive_order_number%TYPE;
166 l_executive_order_date ghr_mass_salaries.executive_order_date%TYPE;
167 l_opm_issuance_number ghr_mass_salaries.opm_issuance_number%TYPE;
168 l_opm_issuance_date ghr_mass_salaries.opm_issuance_date%TYPE;
169 l_pa_request_id number;
170 l_rowid varchar2(30);
171
172 l_p_ORGANIZATION_ID number;
173 l_p_DUTY_STATION_ID number;
174 l_p_PERSONNEL_OFFICE_ID varchar2(5);
175
176 L_row_cnt number := 0;
177
178 l_sf52_rec ghr_pa_requests%rowtype;
179 l_lac_sf52_rec ghr_pa_requests%rowtype;
180 l_errbuf varchar2(2000);
181
182 l_retcode number;
183
184 l_pos_ei_data per_position_extra_info%rowtype;
185 l_pos_grp1_rec per_position_extra_info%rowtype;
186
187 l_pay_calc_in_data ghr_pay_calc.pay_calc_in_rec_type;
188 l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
189 l_sel_flg varchar2(2);
190
191 l_first_action_la_code1 varchar2(30);
192 l_first_action_la_code2 varchar2(30);
193
194 l_remark_code1 varchar2(30);
195 l_remark_code2 varchar2(30);
196 l_p_AGENCY_CODE_SUBELEMENT varchar2(30);
197
198 ----Pay cap variables
199 l_entitled_other_pay NUMBER;
200 l_capped_other_pay NUMBER;
201 l_adj_basic_message BOOLEAN := FALSE;
202 l_pay_cap_message BOOLEAN := FALSE;
203 l_temp_retention_allowance NUMBER;
204 l_open_pay_fields_caps BOOLEAN;
205 l_message_set_caps BOOLEAN;
206 l_total_pay_check VARCHAR2(1);
207 l_comment VARCHAR2(150);
208 l_comment_sal VARCHAR2(150);
209 -- Bug#3968005 Commented l_pay_sel as it is not required.
210 -- l_pay_sel VARCHAR2(1) := NULL;
211 l_old_capped_other_pay NUMBER;
212 ----
213 REC_BUSY exception;
214 pragma exception_init(REC_BUSY,-54);
215
216 l_proc varchar2(72) := g_package || '.execute_mlc';
217
218 l_essl_table BOOLEAN := FALSE;
219 l_org_name hr_organization_units.name%type;
220 l_table_type VARCHAR2(2);
221
222 CURSOR c_pay_tab_type(p_user_table_id pay_user_tables.user_table_id%type)
223 is
224 SELECT range_or_match
225 FROM pay_user_tables
226 WHERE user_table_id = p_user_table_id;
227
228 BEGIN
229 g_proc := 'execute_mlc';
230 hr_utility.set_location('Entering ' || l_proc,5);
231
232 ghr_msl_pkg.g_first_noa_code := null;
233
234 p_retcode := 0;
235 BEGIN
236 FOR msl IN ghr_msl (p_mass_salary_id)
237 LOOP
238 p_mass_salary := msl.name;
239 l_effective_date := msl.effective_date;
240 l_mass_salary_id := msl.mass_salary_id;
241 l_user_table_id := msl.user_table_id;
242 l_submit_flag := msl.submit_flag;
243 l_executive_order_number := msl.executive_order_number;
244 l_executive_order_date := msl.executive_order_date;
245 l_opm_issuance_number := msl.opm_issuance_number;
246 l_opm_issuance_date := msl.opm_issuance_date;
247 l_pa_request_id := msl.pa_request_id;
251 l_p_PERSONNEL_OFFICE_ID := msl.PERSONNEL_OFFICE_ID;
248 l_rowid := msl.rowid;
249 l_p_ORGANIZATION_ID := msl.ORGANIZATION_ID;
250 l_p_DUTY_STATION_ID := msl.DUTY_STATION_ID;
252 l_p_AGENCY_CODE_SUBELEMENT := msl.AGENCY_CODE_SUBELEMENT;
253 l_p_locality_area_code := msl.locality_pay_area_code;
254
255 pr('Pa request id is '||to_char(l_pa_request_id));
256 exit;
257 END LOOP;
258 EXCEPTION
259 when REC_BUSY then
260 hr_utility.set_location('Mass Salary is in use',1);
261 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
262 hr_utility.set_message(8301, 'GHR_38477_LOCK_ON_MSL');
263 hr_utility.raise_error;
264 when others then
265 hr_utility.set_location('Error in '||l_proc||' Sql err is '||sqlerrm(sqlcode),1);
266 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
267 raise mlc_error;
268 END;
269
270 g_effective_date := l_effective_date;
271
272 IF upper(p_action) = 'CREATE' then
273 ghr_mto_int.set_log_program_name('GHR_MLC_PKG');
274 ELSE
275 ghr_mto_int.set_log_program_name('MLC_'||p_mass_salary);
276 END IF;
277
278 get_lac_dtls(l_pa_request_id,
279 l_lac_sf52_rec);
280
281 --------GPPA Update 46 start
282 if l_effective_date >= to_date('2007/01/07','YYYY/MM/DD') THEN
283 ghr_msl_pkg.g_first_noa_code := '894';
284 --Begin Bug# 10387022, Removed LAC check from if condition
285 end if;
286
287 --------GPPA Update 46 end
288 hr_utility.set_location('After fetch msl '||to_char(l_effective_date)
289 ||' '||to_char(l_user_table_id),20);
290
291 FOR per IN cur_people (l_effective_date,l_p_ORGANIZATION_ID)
292 LOOP
293 BEGIN
294 savepoint execute_mlc_sp;
295 l_msl_cnt := l_msl_cnt +1;
296 --Bug#3968005 Initialised l_sel_flg
297 l_sel_flg := NULL;
298 l_pay_calc_in_data := NULL;
299 l_pay_calc_out_data := NULL;
300
301 l_assignment_id := per.assignment_id;
302 l_position_id := per.position_id;
303 l_grade_id := per.grade_id;
304 l_business_group_id := per.business_group_iD;
305 l_location_id := per.location_id;
306
307 --BUG # 11924741 added to check whether already RPA exists with the NOA
308 IF chk_pa_req_already_exists
309 (p_person_id => per.person_id,
310 p_effective_date => l_effective_date,
311 p_first_noa_code => nvl(ghr_msl_pkg.g_first_noa_code,'895'),
312 p_action => p_action
313 ) then
314
315 hr_utility.set_location('The location id is:'||l_location_id,12345);
316 begin
317 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
318 (p_location_id => l_location_id
319 ,p_duty_station_id => l_duty_station_id);
320 exception
321 when others then
322 hr_utility.set_location(
323 'Error in Ghr_pa_requests_pkg.get_sf52_loc_ddf_details'||
324 'Err is '||sqlerrm(sqlcode),20);
325 l_mlcerrbuf := 'Error in get_sf52_loc_ddf_details '||
326 'Sql Err is '|| sqlerrm(sqlcode);
327 raise mlc_error;
328 end;
329
330 l_org_name :=GHR_MRE_PKG.GET_ORGANIZATION_NAME(per.ORGANIZATION_ID);
331
332 get_pos_grp1_ddf(l_position_id,
333 l_effective_date,
334 l_pos_grp1_rec);
335
336 l_personnel_office_id := l_pos_grp1_rec.poei_information3;
337 l_org_structure_id := l_pos_grp1_rec.poei_information5;
338
339 get_sub_element_code_pos_title(l_position_id,
340 null,
341 l_business_group_id,
342 l_assignment_id,
343 l_effective_date,
344 l_sub_element_code,
345 l_position_title,
346 l_position_number,
347 l_position_seq_no);
348
349 hr_utility.set_location('The duty station id is:'||l_duty_station_id,12345);
350
351 if check_init_eligibility(l_p_duty_station_id,
352 l_p_PERSONNEL_OFFICE_ID,
353 l_p_AGENCY_CODE_SUBELEMENT,
354 l_duty_station_id,
355 l_personnel_office_id,
356 l_sub_element_code) then
357
358 hr_utility.set_location('check_init_eligibility ' || l_proc,6);
359
360 begin
361 ghr_pa_requests_pkg.get_sf52_asg_ddf_details
362 (l_assignment_id,
363 l_effective_date,
364 l_tenure,
365 l_annuitant_indicator,
366 l_pay_rate_determinant,
367 l_work_schedule,
368 l_part_time_hour);
369 exception
370 when others then
371 hr_utility.set_location('Error in Ghr_pa_requests_pkg.get_sf52_asg_ddf_details'||
372 'Err is '||sqlerrm(sqlcode),20);
373 l_mlcerrbuf := 'Error in get_sf52_asgddf_details Sql Err is '|| sqlerrm(sqlcode);
374 raise mlc_error;
375 end;
376
377 BEGIN
378 -- Get Pay table ID and other details
379 ghr_msl_pkg.get_pay_plan_and_table_id(l_pay_rate_determinant,per.person_id,
383 l_pay_basis);
380 l_position_id,l_effective_date,
381 l_grade_id, l_assignment_id,'SHOW',l_pay_plan,
382 l_pay_table_id,l_grade_or_level, l_step_or_rate,
384 EXCEPTION
385 when ghr_msl_pkg.msl_error then
386 l_mlcerrbuf := hr_utility.get_message;
387 raise mlc_error;
388 END;
389 IF get_user_table_name(l_pay_table_id) IN ('0000','0491') THEN --Bug# 10387022
390
391 IF check_eligibility(
392 l_pay_plan,
393 per.person_id,
394 l_effective_date,
395 p_action) THEN
396
397 hr_utility.set_location('check_eligibility ' || l_proc,8);
398
399 IF upper(p_action) = 'REPORT' AND l_submit_flag = 'P' THEN
400 -- BUG 3377958 Madhuri
401 --Bug # 9329643
402 pop_dtls_from_pa_req(per.person_id,l_effective_date,l_mass_salary_id,l_org_name,per.employee_number);
403 -- BUG 3377958 Madhuri
404 ELSE
405 IF check_select_flg(per.person_id,
406 upper(p_action),
407 l_effective_date,
408 p_mass_salary_id,
409 l_sel_flg) then
410
411 hr_utility.set_location('check_select_flg ' || l_proc,7);
412 BEGIN
413 hr_utility.set_location('The duty station name is:'||l_duty_station_code,12345);
414 hr_utility.set_location('The duty station desc is:'||l_duty_station_desc,12345);
415 ghr_pa_requests_pkg.get_duty_station_details
416 (p_duty_station_id => l_duty_station_id
417 ,p_effective_date => l_effective_date
418 ,p_duty_station_code => l_duty_station_code
419 ,p_duty_station_desc => l_duty_station_desc);
420 EXCEPTION
421 when others then
422 hr_utility.set_location('Error in Ghr_pa_requests_pkg.get_duty_station_details'||
423 'Err is '||sqlerrm(sqlcode),20);
424 l_mlcerrbuf := 'Error in get_duty_station_details Sql Err is '|| sqlerrm(sqlcode);
425 raise mlc_error;
426
427 END;
428 --Begin Bug# 10387022 Commented out since LAC and remarks are straight forward.
429 /* get_other_dtls_for_rep(l_pay_rate_determinant,
430 l_executive_order_number,
431 to_char(l_executive_order_date),
432 l_first_action_la_code1,
433 l_first_action_la_code2,
434 l_remark_code1,
435 l_remark_code2);*/
436
437 get_from_sf52_data_elements
438 (l_assignment_id,
439 l_effective_date,
440 l_old_basic_pay,
441 l_old_avail_pay,
442 l_old_loc_diff,
443 l_tot_old_sal,
444 l_old_auo_pay,
445 l_old_adj_basic_pay,
446 l_other_pay,
447 l_auo_premium_pay_indicator,
448 l_ap_premium_pay_indicator,
449 l_retention_allowance,
450 l_retention_allow_perc,
451 l_supervisory_differential,
452 l_supervisory_diff_perc,
453 l_staffing_differential);
454
455 open get_sal_chg_fam;
456 fetch get_sal_chg_fam into l_pay_calc_in_data.noa_family_code;
457 close get_sal_chg_fam;
458
459 l_pay_calc_in_data.person_id := per.person_id;
460 l_pay_calc_in_data.position_id := l_position_id;
461 l_pay_calc_in_data.noa_code := nvl(ghr_msl_pkg.g_first_noa_code,'895');
462 l_pay_calc_in_data.second_noa_code := null;
463 l_pay_calc_in_data.first_action_la_code1 := l_lac_sf52_rec.first_action_la_code1;
464 l_pay_calc_in_data.effective_date := l_effective_date;
465 l_pay_calc_in_data.pay_rate_determinant := l_pay_rate_determinant;
466 l_pay_calc_in_data.pay_plan := l_pay_plan;
467 l_pay_calc_in_data.grade_or_level := l_grade_or_level;
468 l_pay_calc_in_data.step_or_rate := l_step_or_rate;
469 l_pay_calc_in_data.pay_basis := l_pay_basis;
470 l_pay_calc_in_data.user_table_id := l_pay_table_id;
471 l_pay_calc_in_data.duty_station_id := l_duty_station_id;
472 l_pay_calc_in_data.auo_premium_pay_indicator := l_auo_premium_pay_indicator;
473 l_pay_calc_in_data.ap_premium_pay_indicator := l_ap_premium_pay_indicator;
474 l_pay_calc_in_data.retention_allowance := l_retention_allowance;
475 l_pay_calc_in_data.to_ret_allow_percentage := l_retention_allow_perc;
476 l_pay_calc_in_data.supervisory_differential := l_supervisory_differential;
477 l_pay_calc_in_data.staffing_differential := l_staffing_differential;
478 l_pay_calc_in_data.current_basic_pay := l_old_basic_pay;
479 l_pay_calc_in_data.current_adj_basic_pay := l_old_adj_basic_pay;
480 l_pay_calc_in_data.current_step_or_rate := l_step_or_rate;
481 l_pay_calc_in_data.pa_request_id := null;
482
483 -- Mass Salary Percetnage Changes
487 l_table_type := pay_tab_type.range_or_match;
484 -- IF the table is of type R then populate the basic into open_pay_basic
485 FOR pay_tab_type IN c_pay_tab_type(l_pay_table_id)
486 LOOP
488 END LOOP;
489
490 IF ( l_table_type = 'R') THEN
491 l_pay_calc_in_data.open_range_out_basic_pay := l_old_basic_pay;
492 -- Bug#3968005 Added Else Condition. Setting open_range_out_basic_pay to NULL
493 -- because pay calculation will calculate values depending on this value.
494 -- See pay calculation for further details.
495 ELSE
496 l_pay_calc_in_data.open_range_out_basic_pay := NULL;
497 END IF;
498
499 BEGIN
500 ghr_pay_calc.sql_main_pay_calc (l_pay_calc_in_data
501 ,l_pay_calc_out_data
502 ,l_message_set
503 ,l_calculated);
504
505 IF l_message_set THEN
506 hr_utility.set_location( l_proc, 40);
507 l_calculated := FALSE;
508 l_mlcerrbuf := hr_utility.get_message;
509 END IF;
510 EXCEPTION
511 when mlc_error then
512 g_proc := 'ghr_pay_calc';
513 raise;
514 when others then
515 IF ghr_pay_calc.gm_unadjusted_pay_flg = 'Y' then
516 l_comment := 'MLC:Error: Unadjusted Basic Pay must be entered in Employee record.';
517 ELSE
518 l_comment := 'MLC:Error: See process log for details.';
519 END IF;
520
521 IF upper(p_action) IN ('SHOW') THEN
522 -- Bug#2383392
523 create_mass_act_prev (
524 p_effective_date => l_effective_date,
525 p_date_of_birth => per.date_of_birth,
526 p_full_name => per.full_name,
527 p_national_identifier => per.national_identifier,
528 --Bug # 9329643
529 p_employee_number => per.employee_number,
530 p_duty_station_code => l_duty_station_code,
531 p_duty_station_desc => l_duty_station_desc,
532 p_personnel_office_id => l_personnel_office_id,
533 p_basic_pay => l_old_basic_pay,
534 p_new_basic_pay => null,
535 --Bug#2383992 Added old_adj_basic_pay
536 p_adj_basic_pay => l_old_adj_basic_pay,
537 p_new_adj_basic_pay => null,
538 p_old_avail_pay => l_old_avail_pay,
539 p_new_avail_pay => null,
540 p_old_loc_diff => l_old_loc_diff,
541 p_new_loc_diff => null,
542 p_tot_old_sal => l_tot_old_sal,
543 p_tot_new_sal => null,
544 p_old_auo_pay => l_old_auo_pay,
545 p_new_auo_pay => null,
546 p_position_id => l_position_id,
547 p_position_title => l_position_title,
548 -- FWFA Changes Bug#4444609
549 p_position_number => l_position_number,
550 p_position_seq_no => l_position_seq_no,
551 -- FWFA Changes
552 p_org_structure_id => l_org_structure_id,
553 p_agency_sub_element_code => l_sub_element_code,
554 p_person_id => per.person_id,
555 p_mass_salary_id => l_mass_salary_id,
556 p_sel_flg => l_sel_flg,
557 p_first_action_la_code1 => l_first_action_la_code1,
558 p_first_action_la_code2 => l_first_action_la_code2,
559 p_remark_code1 => l_remark_code1,
560 p_remark_code2 => l_remark_code2,
561 p_grade_or_level => l_grade_or_level,
562 p_step_or_rate => l_step_or_rate,
563 p_pay_plan => l_pay_plan,
564 p_pay_rate_determinant => null,
565 p_tenure => l_tenure,
566 p_action => p_action,
567 p_assignment_id => l_assignment_id,
568 p_old_other_pay => l_other_pay,
569 p_new_other_pay => null,
570 -- Bug#2383992
571 p_old_capped_other_pay => NULL,
572 p_new_capped_other_pay => NULL,
573 p_old_retention_allowance => l_retention_allowance,
574 p_new_retention_allowance => NULL,
575 p_old_supervisory_differential => l_supervisory_differential,
576 p_new_supervisory_differential => NULL,
577 -- BUG 3377958 Madhuri
578 p_organization_name => l_org_name,
579 -- BUG 3377958 Madhuri
580 -- Bug#2383992
581 -- FWFA Changes Bug#4444609
582 p_input_pay_rate_determinant => l_pay_rate_determinant,
586 );
583 p_from_pay_table_id => l_user_table_id,
584 p_to_pay_table_id => null
585 -- FWFA Changes
587 END IF;
588 -- Bug#3968005 Replaced parameter l_pay_sel with l_sel_flg
589 ins_upd_per_extra_info
590 (per.person_id,l_effective_date, l_sel_flg, l_comment,p_mass_salary_id);
591 l_comment := NULL;
592 ------ BUG 3287299 End
593 hr_utility.set_location('Error in Ghr_pay_calc.sql_main_pay_calc '||
594 'Err is '||sqlerrm(sqlcode),20);
595 l_mlcerrbuf := 'Error in ghr_pay_calc Sql Err is '|| sqlerrm(sqlcode);
596 g_proc := 'ghr_pay_calc';
597 raise mlc_error;
598 END;
599
600 l_new_basic_pay := l_pay_calc_out_data.basic_pay;
601 l_new_locality_adj := l_pay_calc_out_data.locality_adj;
602 l_new_adj_basic_pay := l_pay_calc_out_data.adj_basic_pay;
603 l_new_au_overtime := l_pay_calc_out_data.au_overtime;
604 l_new_availability_pay := l_pay_calc_out_data.availability_pay;
605
606 l_out_pay_rate_determinant := l_pay_calc_out_data.out_pay_rate_determinant;
607 l_out_step_or_rate := l_pay_calc_out_data.out_step_or_rate;
608 l_new_retention_allowance := l_pay_calc_out_data.retention_allowance;
609 l_new_supervisory_differential := l_supervisory_differential;
610 l_new_other_pay_amount := l_pay_calc_out_data.other_pay_amount;
611 l_entitled_other_pay := l_new_other_pay_amount;
612 if l_new_other_pay_amount = 0 then
613 l_new_other_pay_amount := null;
614 end if;
615 l_new_total_salary := l_pay_calc_out_data.total_salary;
616
617 hr_utility.set_location('retention_allowance = ' || to_char(l_retention_allowance),10);
618 hr_utility.set_location('Supervisory Diff Amount = ' || to_char(l_supervisory_differential),10);
619
620 -------------Call Pay cap Procedure
621 begin
622 l_capped_other_pay := ghr_pa_requests_pkg2.get_cop( p_assignment_id => l_assignment_id
623 ,p_effective_date => l_effective_date);
624 l_old_capped_other_pay := l_capped_other_pay;
625 -- Sundar Added the following if statement to improve performance
626 if hr_utility.debug_enabled = true then
627 hr_utility.set_location('Before Pay Cap ' || l_proc,21);
628 hr_utility.set_location('l_effective_date ' || l_effective_date,21);
629 hr_utility.set_location('l_out_pay_rate_determinant ' || l_out_pay_rate_determinant,21);
630 hr_utility.set_location('l_pay_plan ' || l_pay_plan,21);
631 hr_utility.set_location('l_position_id ' || to_char(l_position_id),21);
632 hr_utility.set_location('l_pay_basis ' || l_pay_basis,21);
633 hr_utility.set_location('person_id ' || to_char(per.person_id),21);
634 hr_utility.set_location('l_new_basic_pay ' || to_char(l_new_basic_pay),21);
635 hr_utility.set_location('l_new_locality_adj ' || to_char(l_new_locality_adj),21);
636 hr_utility.set_location('l_new_adj_basic_pay ' || to_char(l_new_adj_basic_pay),21);
637 hr_utility.set_location('l_new_total_salary ' || to_char(l_new_total_salary),21);
638 hr_utility.set_location('l_entitled_other_pay ' || to_char(l_entitled_other_pay),21);
639 hr_utility.set_location('l_capped_other_pay ' || to_char(l_capped_other_pay),21);
640 hr_utility.set_location('l_new_retention_allowance ' || to_char(l_new_retention_allowance),21);
641 hr_utility.set_location('l_new_supervisory_differential ' || to_char(l_new_supervisory_differential),21);
642 hr_utility.set_location('l_staffing_differential ' || to_char(l_staffing_differential),21);
643 hr_utility.set_location('l_new_au_overtime ' || to_char(l_new_au_overtime),21);
644 hr_utility.set_location('l_new_availability_pay ' || to_char(l_new_availability_pay),21);
645 end if;
646
647 ghr_pay_caps.do_pay_caps_main
648 (p_pa_request_id => null
649 ,p_effective_date => l_effective_date
650 ,p_duty_station_id => l_duty_station_id --bug# 13414643,13403289
651 ,p_pay_rate_determinant => nvl(l_out_pay_rate_determinant,l_pay_rate_determinant)
652 ,p_pay_plan => l_pay_plan
653 ,p_to_position_id => l_position_id
654 ,p_pay_basis => l_pay_basis
655 ,p_person_id => per.person_id
656 ,p_noa_code => nvl(ghr_msl_pkg.g_first_noa_code,'895')
657 ,p_basic_pay => l_new_basic_pay
658 ,p_locality_adj => l_new_locality_adj
659 ,p_adj_basic_pay => l_new_adj_basic_pay
660 ,p_total_salary => l_new_total_salary
661 ,p_other_pay_amount => l_entitled_other_pay
662 ,p_capped_other_pay => l_capped_other_pay
663 ,p_retention_allowance => l_new_retention_allowance
664 ,p_retention_allow_percentage => l_retention_allow_perc
665 ,p_supervisory_allowance => l_new_supervisory_differential
666 ,p_staffing_differential => l_staffing_differential
667 ,p_au_overtime => l_new_au_overtime
668 ,p_availability_pay => l_new_availability_pay
669 ,p_adj_basic_message => l_adj_basic_message
670 ,p_pay_cap_message => l_pay_cap_message
674 ,p_total_pay_check => l_total_pay_check);
671 ,p_pay_cap_adj => l_temp_retention_allowance
672 ,p_open_pay_fields => l_open_pay_fields_caps
673 ,p_message_set => l_message_set_caps
675
676
677 l_new_other_pay_amount := nvl(l_capped_other_pay,l_entitled_other_pay);
678
679 -- Sundar Added the following statement to improve performance
680 if hr_utility.debug_enabled = true then
681 hr_utility.set_location('After Pay Cap ' || l_proc,22);
682 hr_utility.set_location('l_effective_date ' || l_effective_date,22);
683 hr_utility.set_location('l_out_pay_rate_determinant ' || l_out_pay_rate_determinant,22);
684 hr_utility.set_location('l_pay_plan ' || l_pay_plan,22);
685 hr_utility.set_location('l_position_id ' || to_char(l_position_id),22);
686 hr_utility.set_location('l_pay_basis ' || l_pay_basis,22);
687 hr_utility.set_location('person_id ' || to_char(per.person_id),22);
688 hr_utility.set_location('l_new_basic_pay ' || to_char(l_new_basic_pay),22);
689 hr_utility.set_location('l_new_locality_adj ' || to_char(l_new_locality_adj),22);
690 hr_utility.set_location('l_new_adj_basic_pay ' || to_char(l_new_adj_basic_pay),22);
691 hr_utility.set_location('l_new_total_salary ' || to_char(l_new_total_salary),22);
692 hr_utility.set_location('l_entitled_other_pay ' || to_char(l_entitled_other_pay),22);
693 hr_utility.set_location('l_capped_other_pay ' || to_char(l_capped_other_pay),22);
694 hr_utility.set_location('l_new_retention_allowance ' || to_char(l_new_retention_allowance),22);
695 hr_utility.set_location('l_new_supervisory_differential ' || to_char(l_new_supervisory_differential),22);
696 hr_utility.set_location('l_staffing_differential ' || to_char(l_staffing_differential),22);
697 hr_utility.set_location('l_new_au_overtime ' || to_char(l_new_au_overtime),22);
698 hr_utility.set_location('l_new_availability_pay ' || to_char(l_new_availability_pay),22);
699 end if;
700
701 IF l_pay_cap_message THEN
702 IF nvl(l_temp_retention_allowance,0) > 0 THEN
703 l_comment := 'MLC: Exceeded Total Cap - reduce Retention Allow to '
704 || to_char(l_temp_retention_allowance);
705 -- Bug#3968005 Replaced l_pay_sel with l_sel_flg
706 l_sel_flg := 'N';
707 ELSE
708 l_comment := 'MLC: Exceeded Total cap - pls review.';
709 END IF;
710 ELSIF l_adj_basic_message THEN
711 l_comment := 'MLC: Exceeded Adjusted Pay Cap - Locality reduced.';
712 END IF;
713
714 -- Bug 2639698 Sundar
715 IF (l_old_basic_pay > l_new_basic_pay) THEN
716 l_comment_sal := 'MLC: From Basic Pay exceeds To Basic Pay.';
717 END IF;
718 -- End Bug 2639698
719
720 IF l_pay_cap_message or l_adj_basic_message THEN
721 -- Bug 2639698
722 IF (l_comment_sal IS NOT NULL) THEN
723 l_comment := l_comment_sal || ' ' || l_comment;
724 END IF;
725 -- End Bug 2639698
726 -- Bug#3968005 Replaced parameter l_pay_sel with l_sel_flg
727 ins_upd_per_extra_info
728 (per.person_id,l_effective_date, l_sel_flg, l_comment,p_mass_salary_id);
729 l_comment := NULL;
730 --------------------Bug 2639698 Sundar To add comments
731 -- Should create comments only if comments need to be inserted
732 ELSIF l_comment_sal IS NOT NULL THEN
733 -- Bug#3968005 Replaced parameter l_pay_sel with l_sel_flg
734 ins_upd_per_extra_info
735 (per.person_id,l_effective_date, l_sel_flg, l_comment_sal,p_mass_salary_id);
736 END IF;
737
738 l_comment_sal := NULL; -- bug 2639698
739 exception
740 when mlc_error then
741 raise;
742 when others then
743 hr_utility.set_location('Error in ghr_pay_caps.do_pay_caps_main ' ||
744 'Err is '||sqlerrm(sqlcode),23);
745 l_mlcerrbuf := 'Error in do_pay_caps_main Sql Err is '|| sqlerrm(sqlcode);
746 raise mlc_error;
747 end;
748
749
750 IF upper(p_action) IN ('SHOW','REPORT') THEN
751 -- Bug#2383392
752 create_mass_act_prev (
753 p_effective_date => l_effective_date,
754 p_date_of_birth => per.date_of_birth,
755 p_full_name => per.full_name,
756 p_national_identifier => per.national_identifier,
757 --Bug # 9329643
758 p_employee_number => per.employee_number,
759 p_duty_station_code => l_duty_station_code,
760 p_duty_station_desc => l_duty_station_desc,
761 p_personnel_office_id => l_personnel_office_id,
762 p_basic_pay => l_old_basic_pay,
763 p_new_basic_pay => l_new_basic_pay,
764 --Bug#2383992 Added old_adj_basic_pay
765 p_adj_basic_pay => l_old_adj_basic_pay,
766 p_new_adj_basic_pay => l_new_adj_basic_pay,
767 p_old_avail_pay => l_old_avail_pay,
768 p_new_avail_pay => l_new_availability_pay,
769 p_old_loc_diff => l_old_loc_diff,
770 p_new_loc_diff => l_new_locality_adj,
771 p_tot_old_sal => l_tot_old_sal,
772 p_tot_new_sal => l_new_total_salary,
773 p_old_auo_pay => l_old_auo_pay,
774 p_new_auo_pay => l_new_au_overtime,
775 p_position_id => l_position_id,
779 p_position_seq_no => l_position_seq_no,
776 p_position_title => l_position_title,
777 -- FWFA Changes Bug#4444609
778 p_position_number => l_position_number,
780 -- FWFA Changes
781 p_org_structure_id => l_org_structure_id,
782 p_agency_sub_element_code => l_sub_element_code,
783 p_person_id => per.person_id,
784 p_mass_salary_id => l_mass_salary_id,
785 p_sel_flg => l_sel_flg,
786 p_first_action_la_code1 => l_first_action_la_code1,
787 p_first_action_la_code2 => l_first_action_la_code2,
788 p_remark_code1 => l_remark_code1,
789 p_remark_code2 => l_remark_code2,
790 p_grade_or_level => l_grade_or_level,
791 p_step_or_rate => l_step_or_rate,
792 p_pay_plan => l_pay_plan,
793 -- FWFA Changes Bug#4444609 Passed l_out_pay_rate_determinant
794 p_pay_rate_determinant => NVL(l_out_pay_rate_determinant,l_pay_rate_determinant),
795 -- FWFA Changes
796 p_tenure => l_tenure,
797 p_action => p_action,
798 p_assignment_id => l_assignment_id,
799 p_old_other_pay => l_other_pay,
800 p_new_other_pay => l_new_other_pay_amount,
801 -- Bug#2383992
802 p_old_capped_other_pay => l_old_capped_other_pay,--NULL,
803 p_new_capped_other_pay => l_capped_other_pay,
804 p_old_retention_allowance => l_retention_allowance,
805 p_new_retention_allowance => l_new_retention_allowance,
806 p_old_supervisory_differential => l_supervisory_differential,
807 p_new_supervisory_differential => l_new_supervisory_differential,
808 -- BUG 3377958 Madhuri
809 p_organization_name => l_org_name,
810 -- Bug#2383992
811 -- FWFA Changes Bug#4444609
812 p_input_pay_rate_determinant => l_pay_rate_determinant,
813 p_from_pay_table_id => l_pay_calc_out_data.pay_table_id,
814 p_to_pay_table_id => l_pay_calc_out_data.calculation_pay_table_id
815 -- FWFA Changes
816 );
817
818
819 ELSIF upper(p_action) = 'CREATE' then
820
821 BEGIN
822 ghr_msl_pkg.get_pay_plan_and_table_id
823 (l_pay_rate_determinant,per.person_id,
824 l_position_id,l_effective_date,
825 l_grade_id, l_assignment_id,'CREATE',
826 l_pay_plan,l_pay_table_id,
827 l_grade_or_level, l_step_or_rate,
828 l_pay_basis);
829 EXCEPTION
830 when ghr_msl_pkg.msl_error then
831 l_mlcerrbuf := hr_utility.get_message;
832 raise mlc_error;
833 END;
834
835 assign_to_sf52_rec(
836 per.person_id,
837 per.first_name,
838 per.last_name,
839 per.middle_names,
840 per.national_identifier,
841 per.date_of_birth,
842 l_effective_date,
843 l_assignment_id,
844 l_tenure,
845 l_step_or_rate,
846 l_annuitant_indicator,
847 -- FWFA Changes Bug#4444609
848 NVL(l_out_pay_rate_determinant,l_pay_rate_determinant),
849 -- FWFA Changes
850 l_work_schedule,
851 l_part_time_hour,
852 l_pos_ei_data.poei_information7, --FLSA Category
853 l_pos_ei_data.poei_information8, --Bargaining Unit Status
854 l_pos_ei_data.poei_information11,--Functional Class
855 l_pos_ei_data.poei_information16,--Supervisory Status,
856 l_new_basic_pay,
857 l_new_locality_adj,
858 l_new_adj_basic_pay,
859 l_new_total_salary,
860 l_other_pay,
861 l_new_other_pay_amount,
862 l_new_au_overtime,
863 l_new_availability_pay,
864 l_new_retention_allowance,
865 l_retention_allow_perc,
866 l_new_supervisory_differential,
867 l_supervisory_diff_perc,
868 l_staffing_differential,
869 l_duty_station_id,
870 l_duty_station_code,
871 l_duty_station_desc,
872 -- FWFA Changes Bug#4444609
873 l_pay_rate_determinant,
874 l_pay_calc_out_data.pay_table_id,
875 l_pay_calc_out_data.calculation_pay_table_id,
876 -- FWFA Changes
877 l_lac_sf52_rec,
878 l_sf52_rec);
879
880 BEGIN
884 EXCEPTION
881 ghr_mass_actions_pkg.pay_calc_rec_to_sf52_rec
882 (l_pay_calc_out_data,
883 l_sf52_rec);
885 when others then
886 hr_utility.set_location('Error in Ghr_mass_actions_pkg.pay_calc_rec_to_sf52_rec '||
887 'Err is '||sqlerrm(sqlcode),20);
888 l_mlcerrbuf := 'Error in ghr_mass_act_pkg.pay_calc_to_sf52 Sql Err is ' ||
889 sqlerrm(sqlcode);
890 raise mlc_error;
891 END;
892
893 BEGIN
894
895 l_sf52_rec.mass_action_id := p_mass_salary_id;
896 l_sf52_rec.rpa_type := 'MLC';
897
898 ghr_mass_changes.create_sf52_for_mass_changes
899 (p_mass_action_type => 'MASS_LOCALITY_CHG',
900 p_pa_request_rec => l_sf52_rec,
901 p_errbuf => l_errbuf,
902 p_retcode => l_retcode);
903
904 ------ Added by Dinkar for List reports problem
905
906 DECLARE
907 l_pa_request_number ghr_pa_requests.request_number%TYPE;
908 BEGIN
909
910 l_pa_request_number := l_sf52_rec.request_number||'-'||p_mass_salary_id;
911
912 ghr_par_upd.upd
913 (p_pa_request_id => l_sf52_rec.pa_request_id,
914 p_object_version_number => l_sf52_rec.object_version_number,
915 p_request_number => l_pa_request_number
916 );
917 END;
918
919 ---------------------------------------
920 IF l_errbuf is null then
921 pr('No error in create sf52 ');
922 hr_utility.set_location('Before commiting',2);
923 --Bug # 9329643 Modified SSN to Emp No
924 ghr_mto_int.log_message(
925 p_procedure => 'Successful Completion',
926 p_message => 'Name: '||per.full_name ||
927 ' Emp No: '|| per.employee_number|| ' Mass Salary : '||
928 p_mass_salary ||' SF52 Successfully completed');
929
930 create_lac_remarks(l_pa_request_id, l_sf52_rec.pa_request_id);
931
932 -- Added by Enunez 11-SEP-1999
933 IF l_lac_sf52_rec.first_action_la_code1 IS NULL THEN
934 -- Added by Edward Nunez for 895 rules
935 g_proc := 'Apply_895_Rules';
936 --Bug 2012782 fix
937 if l_out_pay_rate_determinant is null then
938 l_out_pay_rate_determinant := l_pay_rate_determinant;
939 end if;
940 --Bug 2012782 fix end
941 ghr_lacs_remarks.Apply_894_Rules(
942 l_sf52_rec.pa_request_id,
943 l_out_pay_rate_determinant,
944 l_pay_rate_determinant,
945 l_out_step_or_rate,
946 l_executive_order_number,
947 l_executive_order_date,
948 l_opm_issuance_number,
949 l_opm_issuance_date,
950 l_errbuf,
951 l_retcode
952 );
953 if l_errbuf is not null then
954 l_mlcerrbuf := l_mlcerrbuf || ' ' || l_errbuf || ' Sql Err is '
955 || sqlerrm(sqlcode);
956 raise mlc_error;
957 end if;
958 END IF; -- IF l_lac_sf52_rec.first_action_la_code1
959 g_proc := 'update_SEL_FLG';
960
961 update_SEL_FLG(PER.PERSON_ID,l_effective_date);
962
963 commit;
964 else
965 pr('Error in create sf52',l_errbuf);
966 hr_utility.set_location('Error in '||to_char(per.position_id),20);
967 --l_recs_failed := l_recs_failed + 1;
968 raise mlc_error;
969 end if; -- if l_errbuf is null then
970 exception
971 when mlc_error then raise;
972 when others then null;
973 l_mlcerrbuf := 'Error in ghr_mass_chg.create_sf52 '||
974 ' Sql Err is '|| sqlerrm(sqlcode);
975 raise mlc_error;
976 end;
977 END IF; -- IF upper(p_action) IN ('SHOW','REPORT') THEN
978 END IF; -- end if for check_select_flg
979 END IF; -- end if for p_action = 'REPORT'
980 END IF; --- end if for check_eligibility
981 END IF; -- end if get_user_table_name(l_pay_table_id) --Bug# 10387022
982 --- END IF; -- CHECK FOR PAY PLAN
983 --- END IF; -- check for PRD
984 --- END LOOP; -- Record Type Loop ends here
985 END IF; --- end if for check_init_eligibility
986 END IF; -- ENd if for check_pa_rqst_exists
987
988 L_row_cnt := L_row_cnt + 1;
989 if upper(p_action) <> 'CREATE' THEN
990 if L_row_cnt > 50 then
991 commit;
992 L_row_cnt := 0;
993 end if;
994 end if;
995 EXCEPTION
996 WHEN mlc_ERROR THEN
997 HR_UTILITY.SET_LOCATION('Error occurred in '||l_proc||' Sql error '||sqlerrm(sqlcode),10);
1001 ROLLBACK TO EXECUTE_MLC_SP;
998 begin
999 ------ BUG 3287299 -- Not to rollback for preview.
1000 if upper(p_action) <> 'SHOW' then
1002 end if;
1003 EXCEPTION
1004 WHEN OTHERS THEN NULL;
1005 END;
1006
1007 --Bug # 9329643 Modified SSN to Emp No
1008 l_log_text := 'Error in '||l_proc||' '||
1009 ' For Mass Salary Name : '||p_mass_salary||
1010 'Name: '|| per.full_name || ' Emp No: ' || per.employee_number ||' '||
1011 l_mlcerrbuf;
1012 hr_utility.set_location('before creating entry in log file',10);
1013 l_recs_failed := l_recs_failed + 1;
1014 begin
1015 ghr_mto_int.log_message(
1016 p_procedure => g_proc,
1017 p_message => l_log_text);
1018
1019 exception
1020 when others then
1021 hr_utility.set_message(8301, 'GHR_38475_ERROR_LOG_FAILURE');
1022 hr_utility.raise_error;
1023 end;
1024 when others then
1025 HR_UTILITY.SET_LOCATION('Error (Others) occurred in '||l_proc||' Sql error '||sqlerrm(sqlcode),20);
1026 BEGIN
1027 ROLLBACK TO EXECUTE_MLC_SP;
1028 EXCEPTION
1029 WHEN OTHERS THEN NULL;
1030 END;
1031
1032 --Bug # 9329643 Modified SSN to Emp No
1033 l_log_text := 'Error (others) in '||l_proc||
1034 ' For Mass Salary Name : '||p_mass_salary||
1035 'Name: '|| per.full_name || ' Emp No: ' || per.employee_number ||
1036 ' Sql Err is '||sqlerrm(sqlcode);
1037 hr_utility.set_location('before creating entry in log file',20);
1038 l_recs_failed := l_recs_failed + 1;
1039 begin
1040 ghr_mto_int.log_message(
1041 p_procedure => g_proc,
1042 p_message => l_log_text);
1043 exception
1044 when others then
1045 hr_utility.set_message(8301, 'Create Error Log failed');
1046 hr_utility.raise_error;
1047 end;
1048 END;
1049 END LOOP;
1050
1051 pr('After processing is over ',to_char(l_recs_failed));
1052
1053 IF (l_recs_failed = 0) then
1054 IF upper(p_action) = 'CREATE' THEN
1055 begin
1056 update ghr_mass_salaries
1057 set submit_flag = 'P'
1058 where rowid = l_rowid;
1059 EXCEPTION
1060 when others then
1061 HR_UTILITY.SET_LOCATION('Error in Update ghr_msl Sql error '||sqlerrm(sqlcode),30);
1062 hr_utility.set_message(8301, 'GHR_38476_UPD_GHR_MSL_FAILURE');
1063 hr_utility.raise_error;
1064 END;
1065 -----Bug 2849262. Updating extra info to null is already done by Update_sel_flg in the main loop.
1066 ----- So it is not required to do in global if you see the procedure upd_ext_info_to_null
1067 ----- Commenting the following line. Dated 14-OCT-2003.
1068 -----
1069 ----- upd_ext_info_to_null(l_effective_date);
1070 end if;
1071 ELSE
1072 --if (l_recs_failed <> 0) then
1073 p_errbuf := 'Error in '||l_proc || ' Details in GHR_PROCESS_LOG';
1074 p_retcode := 2;
1075 IF upper(p_action) = 'CREATE' THEN
1076 update ghr_mass_salaries
1077 set submit_flag = 'E'
1078 where rowid = l_rowid;
1079 END IF;
1080 end if;
1081 pr('Before commiting.....');
1082 COMMIT;
1083 pr('After commiting.....',to_char(l_recs_failed));
1084
1085 EXCEPTION
1086 when others then
1087 -- raise_application_error(-20121,'Error in EXECUTE_MLC Err is '||sqlerrm(sqlcode));
1088 HR_UTILITY.SET_LOCATION('Error (Others2) occurred in '||l_proc||' Sql error '||sqlerrm(sqlcode),30);
1089 BEGIN
1090 ROLLBACK TO EXECUTE_MLC_SP;
1091 EXCEPTION
1092 WHEN OTHERS THEN NULL;
1093 END;
1094 l_log_text := 'Error in '||l_proc||
1095 ' For Mass Salary Name : '||p_mass_salary||
1096 ' Sql Err is '||sqlerrm(sqlcode);
1097 l_recs_failed := l_recs_failed + 1;
1098 hr_utility.set_location('before creating entry in log file',30);
1099
1100 p_errbuf := 'Error in '||l_proc || ' Details in GHR_PROCESS_LOG';
1101 p_retcode := 2;
1102 IF upper(p_action) = 'CREATE' THEN
1103 update ghr_mass_salaries
1104 set submit_flag = 'E'
1105 where rowid = l_rowid;
1106 commit;
1107 END IF;
1108
1109 begin
1110 ghr_mto_int.log_message(
1111 p_procedure => g_proc,
1112 p_message => l_log_text);
1113 exception
1114 when others then
1115 hr_utility.set_message(8301, 'Create Error Log failed');
1116 hr_utility.raise_error;
1117 end;
1118 END EXECUTE_MLC;
1119
1120 --
1121 --
1122 --
1123
1124 PROCEDURE execute_msl_pay (p_errbuf OUT NOCOPY varchar2,
1125 p_retcode OUT NOCOPY number,
1126 p_mass_salary_id IN number,
1127 p_action IN varchar2) is
1128
1129 p_mass_salary varchar2(32);
1130 --
1131 -- Main Cursor which fetches from per_assignments_f and per_people_f
1132 --
1133 -- 1. Cursor with organization.
1134 --
1135 cursor cur_people_org (effective_date date, p_org_id number) is
1139 ppf.middle_names MIDDLE_NAMES,
1136 select ppf.person_id PERSON_ID,
1137 ppf.first_name FIRST_NAME,
1138 ppf.last_name LAST_NAME,
1140 ppf.full_name FULL_NAME,
1141 ppf.date_of_birth DATE_OF_BIRTH,
1142 ppf.national_identifier NATIONAL_IDENTIFIER,
1143 --Bug # 9329643
1144 ppf.employee_number EMPLOYEE_NUMBER,
1145 paf.position_id POSITION_ID,
1146 paf.assignment_id ASSIGNMENT_ID,
1147 paf.grade_id GRADE_ID,
1148 paf.job_id JOB_ID,
1149 paf.location_id LOCATION_ID,
1150 paf.organization_id ORGANIZATION_ID,
1151 paf.business_group_id BUSINESS_GROUP_ID,
1152 paf.assignment_status_type_id ASSIGNMENT_STATUS_TYPE_ID
1153 from per_assignments_f paf,
1154 per_people_f ppf,
1155 per_person_types ppt
1156 where ppf.person_id = paf.person_id
1157 and effective_date between ppf.effective_start_date and ppf.effective_end_date
1158 and effective_date between paf.effective_start_date and paf.effective_end_date
1159 and paf.primary_flag = 'Y'
1160 and paf.assignment_type <> 'B'
1161 and ppf.person_type_id = ppt.person_type_id
1162 and ppt.system_person_type IN ('EMP','EMP_APL')
1163 and paf.organization_id = p_org_id
1164 and paf.position_id is not null
1165 order by ppf.person_id;
1166
1167 ---
1168 --- Bug 3539816 Order by added to prevent snapshot old error
1169 --- 2. Cursor with no organization.
1170 ---
1171 cursor cur_people (effective_date date, p_org_id number) is
1172 select ppf.person_id PERSON_ID,
1173 ppf.first_name FIRST_NAME,
1174 ppf.last_name LAST_NAME,
1175 ppf.middle_names MIDDLE_NAMES,
1176 ppf.full_name FULL_NAME,
1177 ppf.date_of_birth DATE_OF_BIRTH,
1178 ppf.national_identifier NATIONAL_IDENTIFIER,
1179 --Bug # 9329643
1180 ppf.employee_number EMPLOYEE_NUMBER,
1181 paf.position_id POSITION_ID,
1182 paf.assignment_id ASSIGNMENT_ID,
1183 paf.grade_id GRADE_ID,
1184 paf.job_id JOB_ID,
1185 paf.location_id LOCATION_ID,
1186 paf.organization_id ORGANIZATION_ID,
1187 paf.business_group_id BUSINESS_GROUP_ID,
1188 paf.assignment_status_type_id ASSIGNMENT_STATUS_TYPE_ID
1189 from per_assignments_f paf,
1190 per_people_f ppf,
1191 per_person_types ppt
1192 where ppf.person_id = paf.person_id
1193 and effective_date between ppf.effective_start_date and ppf.effective_end_date
1194 and effective_date between paf.effective_start_date and paf.effective_end_date
1195 and paf.primary_flag = 'Y'
1196 and paf.assignment_type <> 'B'
1197 and ppf.person_type_id = ppt.person_type_id
1198 and ppt.system_person_type IN ('EMP','EMP_APL')
1199 and paf.position_id is not null
1200 order by ppf.person_id;
1201 --
1202 -- Check assignment_status_type
1203 --
1204
1205 cursor cur_ast (asg_status_type_id number) is
1206 select user_status from per_assignment_status_types
1207 where assignment_status_type_id = asg_status_type_id
1208 and upper(user_status) not in (
1209 'TERMINATE ASSIGNMENT', /* 3 */
1210 'ACTIVE APPLICATION', /* 4 */
1211 'OFFER', /* 5 */
1212 'ACCEPTED', /* 6 */
1213 'TERMINATE APPLICATION', /* 7 */
1214 'END', /* 8 */
1215 'TERMINATE APPOINTMENT', /* 126 */
1216 'SEPARATED'); /* 132 */
1217
1218 --
1219 -- Cursor to select from GHR_MASS_SALARIES - Where criteria is stored
1220 -- Before executing this package
1221 -- from ghr_mass_salary_criteria
1222
1223 cursor ghr_msl (p_msl_id number) is
1224 select name, effective_date, mass_salary_id, user_table_id, submit_flag,
1225 executive_order_number, executive_order_date, ROWID, PA_REQUEST_ID,
1226 ORGANIZATION_ID, DUTY_STATION_ID, PERSONNEL_OFFICE_ID,
1227 AGENCY_CODE_SUBELEMENT, OPM_ISSUANCE_NUMBER, OPM_ISSUANCE_DATE, PROCESS_TYPE,
1228 locality_pay_area_code
1229 from ghr_mass_salaries
1230 where MASS_SALARY_ID = p_msl_id
1231 for update of user_table_id nowait;
1232
1233 ---Fetch Noa_family_code ---800 (CHG_DATA_ELEMENT) , 894 (GHR_SAL_PAY_ADJ)
1234
1235 cursor get_sal_chg_fam (cnoacode varchar2) is
1236 select NOA_FAMILY_CODE
1237 from ghr_families
1238 where NOA_FAMILY_CODE in
1239 (select NOA_FAMILY_CODE from ghr_noa_families
1240 where nature_of_action_id in
1241 (select nature_of_action_id
1242 from ghr_nature_of_actions
1243 where code = cnoacode)
1244 ) and proc_method_flag = 'Y';
1245
1246
1247 cursor unassigned_pos (p_org_pos_id NUMBER,
1248 effective_DATE DATE) is
1249 SELECT null PERSON_ID,
1250 'VACANT' FIRST_NAME,
1251 'VACANT' LAST_NAME,
1252 'VACANT' FULL_NAME,
1253 null MIDDLE_NAMES,
1254 null DATE_OF_BIRTH,
1255 null NATIONAL_IDENTIFIER,
1256 --Bug # 9329643
1257 null EMPLOYEE_NUMBER,
1258 position_id POSITION_ID,
1259 null ASSIGNMENT_ID,
1260 to_NUMBER(null) GRADE_ID,
1261 JOB_ID,
1265 punits.name ORGANIZATION_NAME,
1262 pop.LOCATION_ID,
1263 pop.ORGANIZATION_ID,
1264 pop.BUSINESS_GROUP_ID,
1266 pop.availability_status_id
1267 from hr_positions_f pop,
1268 per_organization_units punits
1269 WHERE trunc(effective_DATE) between pop.effective_start_DATE and pop.effective_END_DATE
1270 and pop.organization_id = punits.organization_id
1271 and pop.organization_id = nvl(p_org_pos_id,pop.organization_id)
1272 and not exists
1273 (
1274 SELECT 'X'
1275 FROM per_people_f p, per_assignments_f a
1276 WHERE trunc(effective_DATE) between a.effective_start_DATE and a.effective_END_DATE
1277 AND a.primary_flag = 'Y'
1278 AND a.assignment_type <> 'B'
1279 AND p.current_employee_flag = 'Y'
1280 AND a.business_group_id = pop.business_group_id
1281 AND a.person_id = p.person_id
1282 AND a.position_id = pop.position_id
1283 AND trunc(effective_DATE) between p.effective_start_DATE and p.effective_end_DATE
1284 );
1285
1286 ----Bug 4699955
1287 cursor c_pos_name (p_position_id NUMBER)
1288 is
1289 select name from hr_positions_f
1290 where position_id = p_position_id;
1291
1292 l_pos_name hr_positions_f.name%type;
1293 ----Bug 4699955
1294
1295 l_assignment_id per_assignments_f.assignment_id%type;
1296 l_position_id per_assignments_f.position_id%type;
1297 l_grade_id per_assignments_f.grade_id%type;
1298 l_business_group_id per_assignments_f.business_group_id%type;
1299
1300 l_position_title varchar2(300);
1301 l_position_number varchar2(20);
1302 l_position_seq_no varchar2(20);
1303
1304 l_msl_cnt number := 0;
1305 l_recs_failed number := 0;
1306
1307 l_tenure varchar2(35);
1308 l_annuitant_indicator varchar2(35);
1309 l_pay_rate_determinant varchar2(35);
1310 l_work_schedule varchar2(35);
1311 l_part_time_hour varchar2(35);
1312 l_pay_table_id number;
1313 l_pay_plan varchar2(30);
1314 l_grade_or_level varchar2(30);
1315 l_step_or_rate varchar2(30);
1316 l_pay_basis varchar2(30);
1317 l_location_id number;
1318 l_duty_station_id number;
1319 l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
1320 l_duty_station_code ghr_pa_requests.duty_station_code%type;
1321 l_effective_date date;
1322 l_personnel_office_id varchar2(300);
1323 l_org_structure_id varchar2(300);
1324 l_sub_element_code varchar2(300);
1325
1326 l_old_basic_pay number;
1327 l_old_avail_pay number;
1328 l_old_loc_diff number;
1329 l_tot_old_sal number;
1330 l_old_auo_pay number;
1331 l_old_ADJ_basic_pay number;
1332 l_other_pay number;
1333
1334
1335 l_auo_premium_pay_indicator varchar2(30);
1336 l_ap_premium_pay_indicator varchar2(30);
1337 l_retention_allowance number;
1338 l_retention_allow_perc number;
1339 l_new_retention_allowance number;
1340 l_supervisory_differential number;
1341 l_supervisory_diff_perc number;
1342 l_new_supervisory_differential number;
1343 l_staffing_differential number;
1344
1345 l_new_avail_pay number;
1346 l_new_loc_diff number;
1347 l_tot_new_sal number;
1348 l_new_auo_pay number;
1349
1350 l_new_basic_pay number;
1351 l_new_locality_adj number;
1352 l_new_adj_basic_pay number;
1353 l_new_total_salary number;
1354 l_new_other_pay_amount number;
1355 l_new_au_overtime number;
1356 l_new_availability_pay number;
1357 l_out_step_or_rate varchar2(30);
1358 l_out_pay_rate_determinant varchar2(30);
1359 l_open_pay_fields boolean;
1360 l_message_set boolean;
1361 l_calculated boolean;
1362
1363 l_mass_salary_id number;
1364 l_user_table_id number;
1365 l_submit_flag varchar2(2);
1366 l_executive_order_number ghr_mass_salaries.executive_order_number%TYPE;
1367 l_executive_order_date ghr_mass_salaries.executive_order_date%TYPE;
1368 l_opm_issuance_number ghr_mass_salaries.opm_issuance_number%TYPE;
1369 l_opm_issuance_date ghr_mass_salaries.opm_issuance_date%TYPE;
1370 l_pa_request_id number;
1371 l_rowid varchar2(30);
1372
1373 l_p_ORGANIZATION_ID number;
1374 l_p_DUTY_STATION_ID number;
1375 l_p_PERSONNEL_OFFICE_ID varchar2(5);
1376
1377 L_row_cnt number := 0;
1378
1379 l_sf52_rec ghr_pa_requests%rowtype;
1380 l_lac_sf52_rec ghr_pa_requests%rowtype;
1381 l_errbuf varchar2(2000);
1382
1383 l_retcode number;
1384
1385 l_pos_ei_data per_position_extra_info%rowtype;
1386 l_pos_grp1_rec per_position_extra_info%rowtype;
1387
1388 l_pay_calc_in_data ghr_pay_calc.pay_calc_in_rec_type;
1389 l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
1390 l_sel_flg varchar2(2);
1391
1392 l_first_action_la_code1 varchar2(30);
1393 l_first_action_la_code2 varchar2(30);
1394
1395 l_remark_code1 varchar2(30);
1396 l_remark_code2 varchar2(30);
1397 l_p_AGENCY_CODE_SUBELEMENT varchar2(30);
1398 l_p_locality_area_code ghr_mass_salaries.locality_pay_area_code%type;
1399
1400 ----Pay cap variables
1401 l_entitled_other_pay NUMBER;
1402 l_capped_other_pay NUMBER;
1406 l_open_pay_fields_caps BOOLEAN;
1403 l_adj_basic_message BOOLEAN := FALSE;
1404 l_pay_cap_message BOOLEAN := FALSE;
1405 l_temp_retention_allowance NUMBER;
1407 l_message_set_caps BOOLEAN;
1408 l_total_pay_check VARCHAR2(1);
1409 l_comment VARCHAR2(150);
1410 l_comment_sal VARCHAR2(150);
1411 l_pay_sel VARCHAR2(1) := NULL;
1412 l_old_capped_other_pay NUMBER;
1413 ----
1414 l_row_low NUMBER;
1415 l_row_high NUMBER;
1416 l_comment_range VARCHAR2(150);
1417 l_comments VARCHAR2(150);
1418
1419 REC_BUSY exception;
1420 pragma exception_init(REC_BUSY,-54);
1421
1422 l_proc varchar2(72) := g_package || '.execute_msl_pay';
1423
1424 l_org_name hr_organization_units.name%type;
1425 ----FWFA Changes
1426 l_0000_id number;
1427 l_0491_id number;
1428 l_to_pay_table_id number;
1429 l_pos_valid_grade_ei_data per_position_extra_info%rowtype;
1430 l_mtcerrbuf varchar2(2000);
1431 l_avail_status_id number;
1432 l_occ_series varchar2(30);
1433 l_pt_value number;
1434 l_pp_grd_exists boolean;
1435 l_dummy varchar2(1);
1436 l_pt_eff_start_date date;
1437 l_pt_eff_end_date date;
1438 l_position_data_rec ghr_sf52_pos_UPDATE.position_data_rec_type;
1439
1440 --
1441 -- Bug 3315432 Madhuri
1442 --
1443 CURSOR cur_pp_prd_per_gr(p_msl_id ghr_mass_salary_criteria.mass_salary_id%type)
1444 IS
1445 SELECT criteria.pay_plan pay_plan,
1446 criteria.pay_rate_determinant prd,
1447 ext.grade grade
1448 FROM ghr_mass_salary_criteria criteria, ghr_mass_salary_criteria_ext ext
1449 WHERE criteria.mass_salary_id=p_msl_id
1450 AND criteria.mass_salary_criteria_id=ext.mass_salary_criteria_id(+);
1451
1452 TYPE pay_plan_prd_per_gr IS RECORD
1453 (
1454 pay_plan ghr_mass_salary_criteria.pay_plan%type,
1455 prd ghr_mass_salary_criteria.pay_rate_determinant%type,
1456 grade ghr_mass_salary_criteria_ext.grade%type
1457 );
1458
1459 TYPE pp_prd_per_gr IS TABLE OF pay_plan_prd_per_gr INDEX BY BINARY_INTEGER;
1460 rec_pp_prd_per_gr pp_prd_per_gr;
1461
1462 l_index NUMBER:=1;
1463 l_cnt NUMBER;
1464 l_count NUMBER;
1465 --
1466 --
1467
1468 cursor c_locality (effective_date date,l_loc_id number,l_loc_code varchar2) is
1469 select 1
1470 from hr_location_extra_info hlei,
1471 ghr_duty_stations_f gdsf,
1472 ghr_locality_pay_areas_f glpa
1473 where hlei.location_id = l_loc_id
1474 and hlei.information_type = 'GHR_US_LOC_INFORMATION'
1475 and hlei.lei_information3 = gdsf.duty_station_id
1476 and gdsf.locality_pay_area_id = glpa.locality_pay_area_id
1477 and effective_date between gdsf.effective_start_date and gdsf.effective_end_date
1478 and effective_date between glpa.effective_start_date and glpa.effective_end_date
1479 and glpa.locality_pay_area_code = l_loc_code;
1480
1481 l_locality_check BOOLEAN;
1482
1483 cursor c_grade_kff (grd_id NUMBER) is
1484 SELECT gdf.segment1
1485 ,gdf.segment2
1486 from per_grades grd, per_grade_definitions gdf
1487 WHERE grd.grade_id = grd_id
1488 and grd.grade_definition_id = gdf.grade_definition_id;
1489
1490 cursor c_pay_table_id (pay_table varchar2) is
1491 select user_table_id from pay_user_tables
1492 where substr(user_table_name,1,4) = pay_table;
1493
1494
1495
1496 PROCEDURE msl_pay_process(p_assignment_id per_assignments_f.assignment_id%TYPE
1497 ,p_person_id per_assignments_f.person_id%TYPE
1498 ,p_position_id per_assignments_f.position_id%TYPE
1499 ,p_grade_id per_assignments_f.grade_id%TYPE
1500 ,p_business_group_id per_assignments_f.business_group_iD%TYPE
1501 ,p_location_id per_assignments_f.location_id%TYPE
1502 ,p_organization_id per_assignments_f.organization_id%TYPE
1503 ,p_date_of_birth date
1504 ,p_first_name per_people_f.first_name%TYPE
1505 ,p_last_name per_people_f.last_name%TYPE
1506 ,p_full_name per_people_f.full_name%TYPE
1507 ,p_middle_names per_people_f.middle_names%TYPE
1508 ,p_national_identifier per_people_f.national_identifier%TYPE
1509 --Bug # 9329643
1510 ,p_employee_number per_people_f.employee_number%TYPE
1511 ) IS
1512
1513 BEGIN
1514 savepoint execute_msl_pay_sp;
1515 l_msl_cnt := l_msl_cnt +1;
1516 l_count := 0;
1517 --Bug#3968005 Initialised l_sel_flg
1518 l_sel_flg := NULL;
1519 l_pay_calc_in_data := NULL;
1520 l_pay_calc_out_data := NULL;
1521
1522
1523 l_assignment_id := p_assignment_id;
1524 l_position_id := p_position_id;
1525 l_grade_id := p_grade_id;
1526 l_business_group_id := p_business_group_iD;
1527 l_location_id := p_location_id;
1528
1529 hr_utility.set_location('The location id is:'||l_location_id,12345);
1530 begin
1531 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
1532 (p_location_id => l_location_id
1533 ,p_duty_station_id => l_duty_station_id);
1534 exception
1535 when others then
1536 hr_utility.set_location(
1537 'Error in Ghr_pa_requests_pkg.get_sf52_loc_ddf_details'||
1538 'Err is '||sqlerrm(sqlcode),20);
1539 l_mtcerrbuf := 'Error in get_sf52_loc_ddf_details '||
1540 'Sql Err is '|| sqlerrm(sqlcode);
1541 raise mtc_error;
1542 end;
1543
1544 l_org_name :=GHR_MRE_PKG.GET_ORGANIZATION_NAME(p_ORGANIZATION_ID);
1545
1549
1546 get_pos_grp1_ddf(l_position_id,
1547 l_effective_date,
1548 l_pos_grp1_rec);
1550 l_personnel_office_id := l_pos_grp1_rec.poei_information3;
1551 l_org_structure_id := l_pos_grp1_rec.poei_information5;
1552
1553 get_sub_element_code_pos_title(l_position_id,
1554 null,
1555 l_business_group_id,
1556 l_assignment_id,
1557 l_effective_date,
1558 l_sub_element_code,
1559 l_position_title,
1560 l_position_number,
1561 l_position_seq_no);
1562
1563 hr_utility.set_location('The duty station id is:'||l_duty_station_id,12345);
1564 -- Check Locality Area Code
1565
1566 l_locality_check := FALSE;
1567
1568 IF l_p_locality_area_code is null then
1569 l_locality_check := TRUE;
1570 ELSE
1571 FOR c_locality_rec IN c_locality (l_effective_date,l_location_id,l_p_locality_area_code)
1572 LOOP
1573 l_locality_check := TRUE;
1574 exit;
1575 END LOOP;
1576 END IF;
1577
1578 --1 Check Duty station code,POI,agnecy and subelement.
1579
1580 IF l_locality_check and check_init_eligibility(l_p_duty_station_id,
1581 l_p_PERSONNEL_OFFICE_ID,
1582 l_p_AGENCY_CODE_SUBELEMENT,
1583 l_duty_station_id,
1584 l_personnel_office_id,
1585 l_sub_element_code) then
1586
1587 hr_utility.set_location('check_init_eligibility ' || l_proc,6);
1588 -- Get PRD, work schedule etc form ASG EI
1589
1590 begin
1591 ghr_pa_requests_pkg.get_sf52_asg_ddf_details
1592 (l_assignment_id,
1593 l_effective_date,
1594 l_tenure,
1595 l_annuitant_indicator,
1596 l_pay_rate_determinant,
1597 l_work_schedule,
1598 l_part_time_hour);
1599 exception
1600 when others then
1601 hr_utility.set_location('Error in Ghr_pa_requests_pkg.get_sf52_asg_ddf_details'||
1602 'Err is '||sqlerrm(sqlcode),20);
1603 l_mtcerrbuf := 'Error in get_sf52_asgddf_details Sql Err is '|| sqlerrm(sqlcode);
1604 raise mtc_error;
1605 end;
1606
1607 -- Check PRD,pay plan,table id Loop start
1608 FOR l_cnt in 1..rec_pp_prd_per_gr.COUNT LOOP
1609
1610 --2 PRD Check
1611 IF nvl(rec_pp_prd_per_gr(l_cnt).prd,'ALL') = 'ALL' OR
1612 nvl(rec_pp_prd_per_gr(l_cnt).prd,l_pay_rate_determinant) = l_pay_rate_determinant THEN
1613 -- Get Pay table ID and other details
1614 BEGIN
1615 ghr_msl_pkg.get_pay_plan_and_table_id(l_pay_rate_determinant,p_person_id,
1616 l_position_id,l_effective_date,
1617 l_grade_id, l_assignment_id,'SHOW',l_pay_plan,
1618 l_pay_table_id,l_grade_or_level, l_step_or_rate,
1619 l_pay_basis);
1620 EXCEPTION
1621 when mtc_error then
1622 l_mtcerrbuf := hr_utility.get_message;
1623 raise;
1624 END;
1625
1626 --3 Pay plan and tableid check
1627 IF ( nvl(rec_pp_prd_per_gr(l_cnt).pay_plan,l_pay_plan) = l_pay_plan
1628 AND l_user_table_id = nvl(l_pay_table_id,hr_api.g_number)
1629 AND nvl(rec_pp_prd_per_gr(l_cnt).grade,l_grade_or_level)=l_grade_or_level) THEN
1630
1631 IF check_eligibility_mtc(
1632 l_pay_plan,
1633 p_person_id,
1634 l_effective_date,
1635 p_action) THEN
1636
1637 hr_utility.set_location('check_eligibility ' || l_proc,8);
1638
1639 IF upper(p_action) = 'REPORT' AND l_submit_flag = 'P' THEN
1640 --Bug # 9329643
1641 pop_dtls_from_pa_req(p_person_id,l_effective_date,l_mass_salary_id,l_org_name,p_employee_number);
1642 ELSE
1643 if check_select_flg(p_person_id
1644 ,upper(p_action)
1645 ,l_effective_date
1646 ,p_mass_salary_id
1647 ,l_sel_flg
1648 ) then
1649
1650 hr_utility.set_location('check_select_flg ' || l_proc,7);
1651 hr_utility.set_location('The duty station name is:'||l_duty_station_code,12345);
1652 hr_utility.set_location('The duty station desc is:'||l_duty_station_desc,12345);
1653
1654 begin
1655 ghr_pa_requests_pkg.get_duty_station_details
1656 (p_duty_station_id => l_duty_station_id
1657 ,p_effective_date => l_effective_date
1658 ,p_duty_station_code => l_duty_station_code
1659 ,p_duty_station_desc => l_duty_station_desc);
1660 exception
1661 when others then
1662 hr_utility.set_location('Error in Ghr_pa_requests_pkg.get_duty_station_details'||
1663 'Err is '||sqlerrm(sqlcode),20);
1664 l_mtcerrbuf := 'Error in get_duty_station_details Sql Err is '|| sqlerrm(sqlcode);
1665 raise mtc_error;
1666 end;
1667
1668 ---Replace the following procedure with LACs and remarks since it is straight forward.
1669
1670 get_other_dtls_for_rep(l_pay_rate_determinant,
1671 l_executive_order_number,
1672 to_char(l_executive_order_date),
1673 l_first_action_la_code1,
1674 l_first_action_la_code2,
1675 l_remark_code1,
1676 l_remark_code2);
1677
1678 get_from_sf52_data_elements
1679 (l_assignment_id, l_effective_date,
1680 l_old_basic_pay, l_old_avail_pay,
1681 l_old_loc_diff, l_tot_old_sal,
1682 l_old_auo_pay, l_old_adj_basic_pay,
1683 l_other_pay, l_auo_premium_pay_indicator,
1684 l_ap_premium_pay_indicator,
1685 l_retention_allowance,
1686 l_retention_allow_perc,
1687 l_supervisory_differential,
1688 l_supervisory_diff_perc,
1689 l_staffing_differential);
1690
1691 for get_sal_chg_fam_rec IN get_sal_chg_fam('894')
1692 loop
1693 l_pay_calc_in_data.noa_family_code := get_sal_chg_fam_rec.noa_family_code;
1694 exit;
1695 end loop;
1696
1697 ------ open get_sal_chg_fam;
1698 ------ fetch get_sal_chg_fam into l_pay_calc_in_data.noa_family_code;
1699 ------ close get_sal_chg_fam;
1700
1701 l_pay_calc_in_data.person_id := p_person_id;
1702 l_pay_calc_in_data.position_id := l_position_id;
1703 l_pay_calc_in_data.noa_code := '894';
1704 l_pay_calc_in_data.second_noa_code := null;
1705 l_pay_calc_in_data.first_action_la_code1 := l_lac_sf52_rec.first_action_la_code1;
1706 l_pay_calc_in_data.effective_date := l_effective_date;
1707 l_pay_calc_in_data.pay_rate_determinant := l_pay_rate_determinant;
1708 l_pay_calc_in_data.pay_plan := l_pay_plan;
1709 l_pay_calc_in_data.grade_or_level := l_grade_or_level;
1710 l_pay_calc_in_data.step_or_rate := l_step_or_rate;
1711 l_pay_calc_in_data.pay_basis := l_pay_basis;
1712 l_pay_calc_in_data.user_table_id := l_pay_table_id;
1713 l_pay_calc_in_data.duty_station_id := l_duty_station_id;
1714 l_pay_calc_in_data.auo_premium_pay_indicator := l_auo_premium_pay_indicator;
1715 l_pay_calc_in_data.ap_premium_pay_indicator := l_ap_premium_pay_indicator;
1716 l_pay_calc_in_data.retention_allowance := l_retention_allowance;
1717 l_pay_calc_in_data.to_ret_allow_percentage := l_retention_allow_perc;
1718 l_pay_calc_in_data.supervisory_differential := l_supervisory_differential;
1719 l_pay_calc_in_data.staffing_differential := l_staffing_differential;
1720 l_pay_calc_in_data.current_basic_pay := l_old_basic_pay;
1721 l_pay_calc_in_data.current_adj_basic_pay := l_old_adj_basic_pay;
1722 l_pay_calc_in_data.current_step_or_rate := l_step_or_rate;
1723 l_pay_calc_in_data.pa_request_id := null;
1724
1725 get_extra_info_comments(p_person_id,l_effective_date,l_pay_sel,
1726 l_comments,p_mass_salary_id);
1727 l_comments := NULL;
1728
1729 begin
1730 ghr_pay_calc.sql_main_pay_calc (l_pay_calc_in_data
1731 ,l_pay_calc_out_data
1732 ,l_message_set
1733 ,l_calculated);
1734
1735 IF l_message_set THEN
1736 hr_utility.set_location( l_proc, 40);
1737 l_calculated := FALSE;
1738 l_mtcerrbuf := hr_utility.get_message;
1739 -- raise mtc_error;
1740 END IF;
1741 exception
1742 when mtc_error then
1743 g_proc := 'ghr_pay_calc';
1744 raise;
1745 when others then
1746 ----BUG 3287299 Start
1747 IF ghr_pay_calc.gm_unadjusted_pay_flg = 'Y' then
1748 l_comment := 'MSL:Error: Unadjusted Basic Pay must be entered in Employee record.';
1749 ELSE
1750 l_comment := 'MSL:Error: See process log for details.';
1751 END IF;
1752
1753 IF upper(p_action) IN ('SHOW') THEN
1754 -- Bug#2383392
1755 create_mass_act_prev_mtc (
1756 p_effective_date => l_effective_date,
1757 p_date_of_birth => p_date_of_birth,
1758 p_full_name => p_full_name,
1759 p_national_identifier => p_national_identifier,
1760 --BUG # 9329643
1761 p_employee_number => p_employee_number,
1762 p_duty_station_code => l_duty_station_code,
1763 p_duty_station_desc => l_duty_station_desc,
1764 p_personnel_office_id => l_personnel_office_id,
1765 p_basic_pay => l_old_basic_pay,
1766 p_new_basic_pay => null,
1767 p_adj_basic_pay => l_old_adj_basic_pay,
1768 p_new_adj_basic_pay => null,
1769 p_old_avail_pay => l_old_avail_pay,
1770 p_new_avail_pay => null,
1771 p_old_loc_diff => l_old_loc_diff,
1772 p_new_loc_diff => null,
1773 p_tot_old_sal => l_tot_old_sal,
1774 p_tot_new_sal => null,
1775 p_old_auo_pay => l_old_auo_pay,
1776 p_new_auo_pay => null,
1777 p_position_id => l_position_id,
1778 p_position_title => l_position_title,
1779 -- FWFA Changes Bug#4444609
1780 p_position_number => l_position_number,
1781 p_position_seq_no => l_position_seq_no,
1782 -- FWFA Changes
1783 p_org_structure_id => l_org_structure_id,
1784 p_agency_sub_element_code => l_sub_element_code,
1785 p_person_id => p_person_id,
1786 p_mass_salary_id => l_mass_salary_id,
1787 p_sel_flg => l_sel_flg,
1788 p_first_action_la_code1 => l_first_action_la_code1,
1789 p_first_action_la_code2 => l_first_action_la_code2,
1790 p_remark_code1 => l_remark_code1,
1791 p_remark_code2 => l_remark_code2,
1792 p_grade_or_level => l_grade_or_level,
1793 p_step_or_rate => l_step_or_rate,
1794 p_pay_plan => l_pay_plan,
1795 p_pay_rate_determinant => null,
1796 p_tenure => l_tenure,
1797 p_action => p_action,
1798 p_assignment_id => l_assignment_id,
1799 p_old_other_pay => l_other_pay,
1800 p_new_other_pay => null,
1801 p_old_capped_other_pay => NULL,
1802 p_new_capped_other_pay => NULL,
1803 p_old_retention_allowance => l_retention_allowance,
1804 p_new_retention_allowance => NULL,
1805 p_old_supervisory_differential => l_supervisory_differential,
1806 p_new_supervisory_differential => NULL,
1807 p_organization_name => l_org_name,
1808 -- FWFA Changes Bug#4444609
1809 p_input_pay_rate_determinant => l_pay_rate_determinant,
1810 p_from_pay_table_id => l_user_table_id,
1811 p_to_pay_table_id => null
1812 );
1813 END IF;
1814 l_comments := substr(l_comments || ' ' || l_comment , 1,150);
1815 ins_upd_per_extra_info
1816 (p_person_id,l_effective_date, l_sel_flg, l_comments,p_mass_salary_id);
1817 l_comment := NULL;
1818 ------ BUG 3287299 End
1819 hr_utility.set_location('Error in Ghr_pay_calc.sql_main_pay_calc '||
1820 'Err is '||sqlerrm(sqlcode),20);
1821 l_mtcerrbuf := 'Error in ghr_pay_calc Sql Err is '|| sqlerrm(sqlcode);
1822 g_proc := 'ghr_pay_calc';
1823 raise mtc_error;
1824 end;
1825
1826 l_new_basic_pay := l_pay_calc_out_data.basic_pay;
1827 l_new_locality_adj := l_pay_calc_out_data.locality_adj;
1828 l_new_adj_basic_pay := l_pay_calc_out_data.adj_basic_pay;
1829 l_new_au_overtime := l_pay_calc_out_data.au_overtime;
1830 l_new_availability_pay := l_pay_calc_out_data.availability_pay;
1831 l_out_pay_rate_determinant := l_pay_calc_out_data.out_pay_rate_determinant;
1832 l_out_step_or_rate := l_pay_calc_out_data.out_step_or_rate;
1833 l_new_retention_allowance := l_pay_calc_out_data.retention_allowance;
1834 l_new_supervisory_differential := l_supervisory_differential;
1835 l_new_other_pay_amount := l_pay_calc_out_data.other_pay_amount;
1836 l_entitled_other_pay := l_new_other_pay_amount;
1837
1838 if l_new_other_pay_amount = 0 then
1839 l_new_other_pay_amount := null;
1840 end if;
1841 l_new_total_salary := l_pay_calc_out_data.total_salary;
1842
1843 hr_utility.set_location('retention_allowance = ' || to_char(l_retention_allowance),10);
1844 hr_utility.set_location('Supervisory Diff Amount = ' || to_char(l_supervisory_differential),10);
1845
1846
1847 -------------Call Pay cap Procedure
1848 begin
1849 l_capped_other_pay := ghr_pa_requests_pkg2.get_cop( p_assignment_id => l_assignment_id
1850 ,p_effective_date => l_effective_date);
1851 l_old_capped_other_pay := l_capped_other_pay;
1852 -- Sundar Added the following if statement to improve performance
1853 if hr_utility.debug_enabled = true then
1854 hr_utility.set_location('Before Pay Cap ' || l_proc,21);
1855 hr_utility.set_location('l_effective_date ' || l_effective_date,21);
1856 hr_utility.set_location('l_out_pay_rate_determinant ' || l_out_pay_rate_determinant,21);
1857 hr_utility.set_location('l_pay_plan ' || l_pay_plan,21);
1858 hr_utility.set_location('l_position_id ' || to_char(l_position_id),21);
1859 hr_utility.set_location('l_pay_basis ' || l_pay_basis,21);
1860 hr_utility.set_location('person_id ' || to_char(p_person_id),21);
1861 hr_utility.set_location('l_new_basic_pay ' || to_char(l_new_basic_pay),21);
1862 hr_utility.set_location('l_new_locality_adj ' || to_char(l_new_locality_adj),21);
1863 hr_utility.set_location('l_new_adj_basic_pay ' || to_char(l_new_adj_basic_pay),21);
1864 hr_utility.set_location('l_new_total_salary ' || to_char(l_new_total_salary),21);
1865 hr_utility.set_location('l_entitled_other_pay ' || to_char(l_entitled_other_pay),21);
1866 hr_utility.set_location('l_capped_other_pay ' || to_char(l_capped_other_pay),21);
1867 hr_utility.set_location('l_new_retention_allowance ' || to_char(l_new_retention_allowance),21);
1868 hr_utility.set_location('l_new_supervisory_diff ' || to_char(l_new_supervisory_differential),21);
1869 hr_utility.set_location('l_staffing_differential ' || to_char(l_staffing_differential),21);
1870 hr_utility.set_location('l_new_au_overtime ' || to_char(l_new_au_overtime),21);
1871 hr_utility.set_location('l_new_availability_pay ' || to_char(l_new_availability_pay),21);
1872 end if;
1873
1874
1875 ghr_pay_caps.do_pay_caps_main
1876 (p_pa_request_id => null
1880 ,p_pay_plan => l_pay_plan
1877 ,p_effective_date => l_effective_date
1878 ,p_duty_station_id => l_duty_station_id --bug# 13414643,13403289
1879 ,p_pay_rate_determinant => nvl(l_out_pay_rate_determinant,l_pay_rate_determinant)
1881 ,p_to_position_id => l_position_id
1882 ,p_pay_basis => l_pay_basis
1883 ,p_person_id => p_person_id
1884 ,p_noa_code => '894'
1885 ,p_basic_pay => l_new_basic_pay
1886 ,p_locality_adj => l_new_locality_adj
1887 ,p_adj_basic_pay => l_new_adj_basic_pay
1888 ,p_total_salary => l_new_total_salary
1889 ,p_other_pay_amount => l_entitled_other_pay
1890 ,p_capped_other_pay => l_capped_other_pay
1891 ,p_retention_allowance => l_new_retention_allowance
1892 ,p_retention_allow_percentage => l_retention_allow_perc
1893 ,p_supervisory_allowance => l_new_supervisory_differential
1894 ,p_staffing_differential => l_staffing_differential
1895 ,p_au_overtime => l_new_au_overtime
1896 ,p_availability_pay => l_new_availability_pay
1897 ,p_adj_basic_message => l_adj_basic_message
1898 ,p_pay_cap_message => l_pay_cap_message
1899 ,p_pay_cap_adj => l_temp_retention_allowance
1900 ,p_open_pay_fields => l_open_pay_fields_caps
1901 ,p_message_set => l_message_set_caps
1902 ,p_total_pay_check => l_total_pay_check);
1903
1904
1905 l_new_other_pay_amount := nvl(l_capped_other_pay,l_entitled_other_pay);
1906
1907 -- Sundar Added the following statement to improve performance
1908 if hr_utility.debug_enabled = true then
1909 hr_utility.set_location('After Pay Cap ' || l_proc,22);
1910 hr_utility.set_location('l_effective_date ' || l_effective_date,22);
1911 hr_utility.set_location('l_out_pay_rate_determinant ' || l_out_pay_rate_determinant,22);
1912 hr_utility.set_location('l_pay_plan ' || l_pay_plan,22);
1913 hr_utility.set_location('l_position_id ' || to_char(l_position_id),22);
1914 hr_utility.set_location('l_pay_basis ' || l_pay_basis,22);
1915 hr_utility.set_location('person_id ' || to_char(p_person_id),22);
1916 hr_utility.set_location('l_new_basic_pay ' || to_char(l_new_basic_pay),22);
1917 hr_utility.set_location('l_new_locality_adj ' || to_char(l_new_locality_adj),22);
1918 hr_utility.set_location('l_new_adj_basic_pay ' || to_char(l_new_adj_basic_pay),22);
1919 hr_utility.set_location('l_new_total_salary ' || to_char(l_new_total_salary),22);
1920 hr_utility.set_location('l_entitled_other_pay ' || to_char(l_entitled_other_pay),22);
1921 hr_utility.set_location('l_capped_other_pay ' || to_char(l_capped_other_pay),22);
1922 hr_utility.set_location('l_new_retention_allowance ' || to_char(l_new_retention_allowance),22);
1923 hr_utility.set_location('l_new_supervisory_diff ' || to_char(l_new_supervisory_differential),22);
1924 hr_utility.set_location('l_staffing_differential ' || to_char(l_staffing_differential),22);
1925 hr_utility.set_location('l_new_au_overtime ' || to_char(l_new_au_overtime),22);
1926 hr_utility.set_location('l_new_availability_pay ' || to_char(l_new_availability_pay),22);
1927 end if;
1928
1929 IF l_pay_cap_message THEN
1930 IF nvl(l_temp_retention_allowance,0) > 0 THEN
1931 l_comment := 'MSL: Exceeded Total Cap - reduce Retention Allow to '
1932 || to_char(l_temp_retention_allowance);
1933 l_sel_flg := 'N';
1934 ELSE
1935 l_comment := 'MSL: Exceeded Total cap - pls review.';
1936 END IF;
1937 ELSIF l_adj_basic_message THEN
1938 l_comment := 'MSL: Exceeded Adjusted Pay Cap - Locality reduced.';
1939 END IF;
1940
1941 IF l_pay_cap_message or l_adj_basic_message THEN
1942 -- Bug 2639698
1943 IF (l_comment_sal IS NOT NULL) THEN
1944 l_comment := l_comment_sal || ' ' || l_comment;
1945 END IF;
1946 -- End Bug 2639698
1947 l_comments := substr(l_comments || ' ' || l_comment, 1,150);
1948 ins_upd_per_extra_info
1949 (p_person_id,l_effective_date, l_sel_flg, l_comments,p_mass_salary_id);
1950 l_comment := NULL;
1951 --------------------Bug 2639698 Sundar To add comments
1952 -- Should create comments only if comments need to be inserted
1953 ELSIF l_comment_sal IS NOT NULL THEN
1954 l_comments := substr(l_comments || ' ' || l_comment_sal, 1,150);
1955 ins_upd_per_extra_info
1956 (p_person_id,l_effective_date, l_sel_flg, l_comments,p_mass_salary_id);
1957 END IF;
1958
1959 l_comment_sal := NULL; -- bug 2639698
1960 exception
1961 when mtc_error then
1962 raise;
1963 when others then
1964 hr_utility.set_location('Error in ghr_pay_caps.do_pay_caps_main ' ||
1965 'Err is '||sqlerrm(sqlcode),23);
1966 l_mtcerrbuf := 'Error in do_pay_caps_main Sql Err is '|| sqlerrm(sqlcode);
1967 raise mtc_error;
1968 end;
1969
1970
1971 IF upper(p_action) IN ('SHOW','REPORT') THEN
1972 create_mass_act_prev_mtc (
1973 p_effective_date => l_effective_date,
1974 p_date_of_birth => p_date_of_birth,
1975 p_full_name => p_full_name,
1976 p_national_identifier => p_national_identifier,
1977 --BUG # 9329643
1978 p_employee_number => p_employee_number,
1982 p_basic_pay => l_old_basic_pay,
1979 p_duty_station_code => l_duty_station_code,
1980 p_duty_station_desc => l_duty_station_desc,
1981 p_personnel_office_id => l_personnel_office_id,
1983 p_new_basic_pay => l_new_basic_pay,
1984 p_adj_basic_pay => l_old_adj_basic_pay,
1985 p_new_adj_basic_pay => l_new_adj_basic_pay,
1986 p_old_avail_pay => l_old_avail_pay,
1987 p_new_avail_pay => l_new_availability_pay,
1988 p_old_loc_diff => l_old_loc_diff,
1989 p_new_loc_diff => l_new_locality_adj,
1990 p_tot_old_sal => l_tot_old_sal,
1991 p_tot_new_sal => l_new_total_salary,
1992 p_old_auo_pay => l_old_auo_pay,
1993 p_new_auo_pay => l_new_au_overtime,
1994 p_position_id => l_position_id,
1995 p_position_title => l_position_title,
1996 -- FWFA Changes Bug#4444609
1997 p_position_number => l_position_number,
1998 p_position_seq_no => l_position_seq_no,
1999 -- FWFA Changes
2000 p_org_structure_id => l_org_structure_id,
2001 p_agency_sub_element_code => l_sub_element_code,
2002 p_person_id => p_person_id,
2003 p_mass_salary_id => l_mass_salary_id,
2004 p_sel_flg => l_sel_flg,
2005 p_first_action_la_code1 => l_first_action_la_code1,
2006 p_first_action_la_code2 => l_first_action_la_code2,
2007 p_remark_code1 => l_remark_code1,
2008 p_remark_code2 => l_remark_code2,
2009 p_grade_or_level => l_grade_or_level,
2010 p_step_or_rate => l_step_or_rate,
2011 p_pay_plan => l_pay_plan,
2012 -- FWFA Changes Bug#4444609 Added NVL condition
2013 p_pay_rate_determinant => NVL(l_out_pay_rate_determinant,l_pay_rate_determinant),
2014 -- FWFA Changes
2015 p_tenure => l_tenure,
2016 p_action => p_action,
2017 p_assignment_id => l_assignment_id,
2018 p_old_other_pay => l_other_pay,
2019 p_new_other_pay => l_new_other_pay_amount,
2020 p_old_capped_other_pay => l_old_capped_other_pay,
2021 p_new_capped_other_pay => l_capped_other_pay,
2022 p_old_retention_allowance => l_retention_allowance,
2023 p_new_retention_allowance => l_new_retention_allowance,
2024 p_old_supervisory_differential => l_supervisory_differential,
2025 p_new_supervisory_differential => l_new_supervisory_differential,
2026 p_organization_name => l_org_name,
2027 -- FWFA Changes Bug#4444609
2028 p_input_pay_rate_determinant => l_pay_rate_determinant,
2029 p_from_pay_table_id => l_pay_calc_out_data.pay_table_id,
2030 p_to_pay_table_id => l_pay_calc_out_data.calculation_pay_table_id
2031 -- FWFA Changes
2032 );
2033
2034
2035 ELSIF upper(p_action) = 'CREATE' then
2036 BEGIN
2037 ghr_msl_pkg.get_pay_plan_and_table_id
2038 (l_pay_rate_determinant,p_person_id,
2039 l_position_id,l_effective_date,
2040 l_grade_id, l_assignment_id,'CREATE',
2041 l_pay_plan,l_pay_table_id,
2042 l_grade_or_level, l_step_or_rate,
2043 l_pay_basis);
2044 EXCEPTION
2045 when mtc_error then
2046 l_mtcerrbuf := hr_utility.get_message;
2047 raise;
2048 END;
2049 assign_to_sf52_rec(
2050 p_person_id,
2051 p_first_name,
2052 p_last_name,
2053 p_middle_names,
2054 p_national_identifier,
2055 p_date_of_birth,
2056 l_effective_date,
2057 l_assignment_id,
2058 l_tenure,
2059 l_step_or_rate,
2060 l_annuitant_indicator,
2061 -- FWFA Changes Bug#4444609
2062 NVL(l_out_pay_rate_determinant,l_pay_rate_determinant),
2063 -- FWFA Changes
2064 l_work_schedule,
2065 l_part_time_hour,
2066 l_pos_ei_data.poei_information7, --FLSA Category
2067 l_pos_ei_data.poei_information8, --Bargaining Unit Status
2068 l_pos_ei_data.poei_information11,--Functional Class
2069 l_pos_ei_data.poei_information16,--Supervisory Status,
2070 l_new_basic_pay,
2071 l_new_locality_adj,
2072 l_new_adj_basic_pay,
2076 l_new_au_overtime,
2073 l_new_total_salary,
2074 l_other_pay,
2075 l_new_other_pay_amount,
2077 l_new_availability_pay,
2078 l_new_retention_allowance,
2079 l_retention_allow_perc,
2080 l_new_supervisory_differential,
2081 l_supervisory_diff_perc,
2082 l_staffing_differential,
2083 l_duty_station_id,
2084 l_duty_station_code,
2085 l_duty_station_desc,
2086 -- FWFA Changes Bug#4444609
2087 l_pay_rate_determinant,
2088 l_pay_calc_out_data.pay_table_id,
2089 l_pay_calc_out_data.calculation_pay_table_id,
2090 -- FWFA Changes
2091 l_lac_sf52_rec,
2092 l_sf52_rec);
2093
2094 begin
2095
2096 l_sf52_rec.mass_action_id := p_mass_salary_id;
2097 l_sf52_rec.rpa_type := 'MTC';
2098
2099 ghr_mass_changes.create_sf52_for_mass_changes
2100 (p_mass_action_type => 'MASS_TABLE_CHG',
2101 p_pa_request_rec => l_sf52_rec,
2102 p_errbuf => l_errbuf,
2103 p_retcode => l_retcode);
2104
2105 ------ Added by Dinkar for List reports problem
2106
2107 declare
2108 l_pa_request_number ghr_pa_requests.request_number%TYPE;
2109 begin
2110
2111 l_pa_request_number :=
2112 l_sf52_rec.request_number||'-'||p_mass_salary_id;
2113
2114 ghr_par_upd.upd
2115 (p_pa_request_id => l_sf52_rec.pa_request_id,
2116 p_object_version_number => l_sf52_rec.object_version_number,
2117 p_request_number => l_pa_request_number
2118 );
2119 end;
2120
2121 ---------------------------------------
2122 if l_errbuf is null then
2123 pr('No error in create sf52 ');
2124 hr_utility.set_location('Before commiting',2);
2125
2126 --Bug # 9329643 Modified SSN to Emp No
2127 ghr_mto_int.log_message(
2128 p_procedure => 'Successful Completion',
2129 p_message => 'Name: '||p_full_name ||
2130 ' Emp No: ' || p_employee_number|| ' Mass Salary : '||
2131 p_mass_salary ||' SF52 Successfully completed');
2132
2133
2134 create_lac_remarks(l_pa_request_id,
2135 l_sf52_rec.pa_request_id);
2136
2137 -- Added by Enunez 11-SEP-1999
2138 IF l_lac_sf52_rec.first_action_la_code1 IS NULL THEN
2139 -- Added by Edward Nunez for 894 rules
2140 g_proc := 'Apply_FWFA_Rules';
2141 -- FWFA Changes Bug#4444609
2142 ghr_lacs_remarks.Apply_fwfa_Rules(
2143 l_sf52_rec.pa_request_id,
2144 l_sf52_rec.first_noa_code,
2145 l_sf52_rec.to_pay_plan,
2146 l_errbuf,
2147 l_retcode
2148 );
2149 -- FWFA Changes
2150 if l_errbuf is not null then
2151 l_mtcerrbuf := l_mtcerrbuf || ' ' || l_errbuf || ' Sql Err is '
2152 || sqlerrm(sqlcode);
2153 raise mtc_error;
2154 end if;
2155 END IF; -- IF l_lac_sf52_rec.first_action_la_code1
2156
2157 g_proc := 'update_SEL_FLG';
2158
2159 update_SEL_FLG(p_PERSON_ID,l_effective_date);
2160
2161 commit;
2162 else
2163 pr('Error in create sf52',l_errbuf);
2164 hr_utility.set_location('Error in '||to_char(p_position_id),20);
2165 --l_recs_failed := l_recs_failed + 1;
2166 raise mtc_error;
2167 end if; -- if l_errbuf is null then
2168 exception
2169 when mtc_error then raise;
2170 when others then null;
2171 l_mtcerrbuf := 'Error in ghr_mass_chg.create_sf52 '||
2172 ' Sql Err is '|| sqlerrm(sqlcode);
2173 raise mtc_error;
2174 end;
2175 END IF; -- IF upper(p_action) IN ('SHOW','REPORT') THEN
2176 END IF; -- end if for check_select_flg
2177 END IF; -- end if for p_action = 'REPORT'
2178 END IF; -- CHECK FOR PAY PLAN
2179 END IF; -- check for PRD
2180 END IF; -- check eligibility_mtc
2181 END LOOP; -- Record Type Loop ends here
2182 --END IF; -- Check Grade and percent.
2183 END IF; --- end if for check_init_eligibility
2184
2185 L_row_cnt := L_row_cnt + 1;
2186 if upper(p_action) <> 'CREATE' THEN
2187 if L_row_cnt > 50 then
2188 commit;
2189 L_row_cnt := 0;
2190 end if;
2191 end if;
2192 EXCEPTION
2193 WHEN MTC_ERROR THEN
2194 HR_UTILITY.SET_LOCATION('Error occurred in '||l_proc||' Sql error '||sqlerrm(sqlcode),10);
2198 ROLLBACK TO execute_msl_pay_SP;
2195 begin
2196 ------ BUG 3287299 -- Not to rollback for preview.
2197 if upper(p_action) <> 'SHOW' then
2199 end if;
2200 EXCEPTION
2201 WHEN OTHERS THEN NULL;
2202 END;
2203 --Bug # 9329643 Modified SSN to Emp No
2204 l_log_text := 'Error in '||l_proc||' '||
2205 ' For Mass Salary Name : '||p_mass_salary||
2206 'Name: '|| p_full_name || ' Emp No: ' || p_employee_number ||' '||
2207 l_mtcerrbuf;
2208 hr_utility.set_location('before creating entry in log file',10);
2209 l_recs_failed := l_recs_failed + 1;
2210 begin
2211 ghr_mto_int.log_message(
2212 p_procedure => g_proc,
2213 p_message => l_log_text);
2214
2215 exception
2216 when others then
2217 hr_utility.set_message(8301, 'GHR_38475_ERROR_LOG_FAILURE');
2218 hr_utility.raise_error;
2219 end;
2220 when others then
2221 HR_UTILITY.SET_LOCATION('Error (Others) occurred in '||l_proc||' Sql error '||sqlerrm(sqlcode),20);
2222 BEGIN
2223 ROLLBACK TO execute_msl_pay_SP;
2224 EXCEPTION
2225 WHEN OTHERS THEN NULL;
2226 END;
2227 --Bug # 9329643 Modified SSN to Emp No
2228 l_log_text := 'Error (others) in '||l_proc||
2229 ' For Mass Salary Name : '||p_mass_salary||
2230 'Name: '|| p_full_name || ' Emp No: ' || p_employee_number ||
2231 ' Sql Err is '||sqlerrm(sqlcode);
2232 hr_utility.set_location('before creating entry in log file',20);
2233 l_recs_failed := l_recs_failed + 1;
2234 begin
2235 ghr_mto_int.log_message(
2236 p_procedure => g_proc,
2237 p_message => l_log_text);
2238
2239 exception
2240 when others then
2241 hr_utility.set_message(8301, 'Create Error Log failed');
2242 hr_utility.raise_error;
2243 end;
2244
2245
2246 END msl_pay_process;
2247
2248 BEGIN
2249
2250 g_proc := 'execute_msl_pay';
2251 hr_utility.set_location('Entering ' || l_proc,5);
2252 p_retcode := 0;
2253 l_count := 0;
2254
2255 ghr_msl_pkg.g_first_noa_code := null;
2256
2257
2258 BEGIN
2259 FOR msl IN ghr_msl (p_mass_salary_id)
2260 LOOP
2261 p_mass_salary := msl.name;
2262 l_effective_date := msl.effective_date;
2263 l_mass_salary_id := msl.mass_salary_id;
2264 l_user_table_id := msl.user_table_id;
2265 l_submit_flag := msl.submit_flag;
2266 l_executive_order_number := msl.executive_order_number;
2267 l_executive_order_date := msl.executive_order_date;
2268 l_opm_issuance_number := msl.opm_issuance_number;
2269 l_opm_issuance_date := msl.opm_issuance_date;
2270 l_pa_request_id := msl.pa_request_id;
2271 l_rowid := msl.rowid;
2272 l_p_ORGANIZATION_ID := msl.ORGANIZATION_ID;
2273 l_p_DUTY_STATION_ID := msl.DUTY_STATION_ID;
2274 l_p_PERSONNEL_OFFICE_ID := msl.PERSONNEL_OFFICE_ID;
2275 l_p_AGENCY_CODE_SUBELEMENT := msl.AGENCY_CODE_SUBELEMENT;
2276 l_p_locality_area_code := msl.locality_pay_area_code;
2277
2278 pr('Pa request id is '||to_char(l_pa_request_id));
2279 exit;
2280 END LOOP;
2281 EXCEPTION
2282 when REC_BUSY then
2283 hr_utility.set_location('Mass Salary is in use',1);
2284 l_mtcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2285 hr_utility.set_message(8301, 'GHR_38477_LOCK_ON_MSL');
2286 hr_utility.raise_error;
2287 --
2288 when others then
2289 hr_utility.set_location('Error in '||l_proc||' Sql err is '||sqlerrm(sqlcode),1);
2290 l_mtcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2291 raise mtc_error;
2292 END;
2293
2294 g_effective_date := l_effective_date;
2295
2296 -- Bug 3315432 Madhuri
2297 --
2298 FOR pp_prd_per_gr IN cur_pp_prd_per_gr(p_mass_salary_id)
2299 LOOP
2300 rec_pp_prd_per_gr(l_index).pay_plan := pp_prd_per_gr.pay_plan;
2301 rec_pp_prd_per_gr(l_index).prd := pp_prd_per_gr.prd;
2302 rec_pp_prd_per_gr(l_index).grade := pp_prd_per_gr.grade;
2303 l_index := l_index +1;
2304 l_count := l_count + 1;
2305 END LOOP;
2306
2307 if l_count = 0 then
2308 rec_pp_prd_per_gr(1).pay_plan := null;
2309 rec_pp_prd_per_gr(1).prd := 'ALL';
2310 rec_pp_prd_per_gr(1).grade := null;
2311 end if;
2312
2313 IF upper(p_action) = 'CREATE' then
2314 ghr_mto_int.set_log_program_name('GHR_MSL_PKG');
2315 ELSE
2316 ghr_mto_int.set_log_program_name('MSL_'||p_mass_salary);
2317 END IF;
2318
2319 get_lac_dtls(l_pa_request_id,
2320 l_lac_sf52_rec);
2321
2322 hr_utility.set_location('After fetch msl '||to_char(l_effective_date)
2323 ||' '||to_char(l_user_table_id),20);
2324
2325 IF l_p_ORGANIZATION_ID is not null then
2326 FOR per IN cur_people_org (l_effective_date,l_p_ORGANIZATION_ID)
2327 LOOP
2328 FOR ast IN cur_ast (per.assignment_status_type_id) LOOP
2329 msl_pay_process( p_assignment_id => per.assignment_id
2333 ,p_business_group_id => per.business_group_id
2330 ,p_person_id => per.person_id
2331 ,p_position_id => per.position_id
2332 ,p_grade_id => per.grade_id
2334 ,p_location_id => per.location_id
2335 ,p_organization_id => per.organization_id
2336 ,p_date_of_birth => per.date_of_birth
2337 ,p_first_name => per.first_name
2338 ,p_last_name => per.last_name
2339 ,p_full_name => per.full_name
2340 ,p_middle_names => per.middle_names
2341 ,p_national_identifier => per.national_identifier
2342 --BUG # 9329643
2343 ,p_employee_number => per.employee_number
2344 );
2345 END LOOP;
2346 END LOOP;
2347 ELSE
2348 FOR per IN cur_people (l_effective_date,l_p_ORGANIZATION_ID)
2349 LOOP
2350 FOR ast IN cur_ast (per.assignment_status_type_id) LOOP
2351 msl_pay_process( p_assignment_id => per.assignment_id
2352 ,p_person_id => per.person_id
2353 ,p_position_id => per.position_id
2354 ,p_grade_id => per.grade_id
2355 ,p_business_group_id => per.business_group_id
2356 ,p_location_id => per.location_id
2357 ,p_organization_id => per.organization_id
2358 ,p_date_of_birth => per.date_of_birth
2359 ,p_first_name => per.first_name
2360 ,p_last_name => per.last_name
2361 ,p_full_name => per.full_name
2362 ,p_middle_names => per.middle_names
2363 ,p_national_identifier => per.national_identifier
2364 --BUG # 9329643
2365 ,p_employee_number => per.employee_number
2366 );
2367
2368 END LOOP;
2369 END LOOP;
2370 END IF;
2371
2372 pr('After processing is over ',to_char(l_recs_failed));
2373
2374 -- Vacant Positions Logic
2375
2376 hr_utility.set_location('Entering Unassigned positions ' || l_proc,30);
2377
2378 FOR c_pay_table_rec IN c_pay_table_id('0000') LOOP
2379 l_0000_id := c_pay_table_rec.user_table_id;
2380 exit;
2381 END LOOP;
2382
2383 hr_utility.set_location('0000 tableid is ' || to_char(l_0000_id) || l_proc,35);
2384
2385 FOR c_pay_table_rec IN c_pay_table_id('0491') LOOP
2386 l_0491_id := c_pay_table_rec.user_table_id;
2387 exit;
2388 END LOOP;
2389
2390 hr_utility.set_location('0491 tableid is ' || to_char(l_0491_id) || l_proc,36);
2391
2392 FOR un_per IN unassigned_pos (l_p_organization_id, l_effective_date)
2393 LOOP
2394
2395 l_avail_status_id := un_per.availability_status_id;
2396
2397 -- 1 Available status check
2398 IF ( HR_GENERAL.DECODE_AVAILABILITY_STATUS(l_avail_status_id)
2399 = 'Active' ) THEN
2400 --------- not in ('Eliminated','Frozen','Deleted') ) THEN
2401
2402 hr_utility.set_location('Available status ' || HR_GENERAL.DECODE_AVAILABILITY_STATUS(l_avail_status_id) || l_proc,37);
2403 l_position_id := un_per.position_id;
2404 hr_utility.set_location('position id is ' || to_char(l_position_id) || l_proc,40);
2405
2406 ghr_history_fetch.fetch_positionei
2407 (p_position_id => l_position_id
2408 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
2409 ,p_DATE_effective => l_effective_DATE
2410 ,p_pos_ei_data => l_pos_valid_grade_ei_data
2411 );
2412
2413 l_grade_id := l_pos_valid_grade_ei_data.poei_information3;
2414 l_pay_table_id := l_pos_valid_grade_ei_data.poei_information5;
2415 l_business_group_id := un_per.business_group_iD;
2416 l_location_id := un_per.location_id;
2417
2418 hr_utility.set_location('position valid grade table id is ' || to_char(l_pay_table_id) || l_proc,45);
2419 BEGIN
2420 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
2421 (p_location_id => l_location_id
2422 ,p_duty_station_id => l_duty_station_id);
2423 END;
2424
2425 get_pos_grp1_ddf(l_position_id,
2426 l_effective_date,
2427 l_pos_grp1_rec);
2428
2429 l_personnel_office_id := l_pos_grp1_rec.poei_information3;
2430 l_org_structure_id := l_pos_grp1_rec.poei_information5;
2431
2432 l_position_title := ghr_api.get_position_title_pos
2433 (p_position_id => l_position_id
2434 ,p_business_group_id => l_business_group_id ) ;
2435
2436 l_sub_element_code := ghr_api.get_position_agency_code_pos
2437 (l_position_id,l_business_group_id);
2438
2439 l_occ_series := ghr_api.get_job_occ_series_job
2440 (p_job_id => un_per.job_id
2441 ,p_business_group_id => un_per.business_group_id);
2442
2443 l_position_NUMBER := ghr_api.get_position_desc_no_pos
2444 (p_position_id => l_position_id
2448 (p_position_id => l_position_id
2445 ,p_business_group_id => un_per.business_group_id);
2446
2447 l_position_seq_no := ghr_api.get_position_sequence_no_pos
2449 ,p_business_group_id => un_per.business_group_id);
2450
2451 -- Check Locality Area Code
2452
2453 l_locality_check := FALSE;
2454
2455 IF l_p_locality_area_code is null then
2456 l_locality_check := TRUE;
2457 ELSE
2458 FOR c_locality_rec IN c_locality (l_effective_date,l_location_id,l_p_locality_area_code)
2459 LOOP
2460 l_locality_check := TRUE;
2461 exit;
2462 END LOOP;
2463 END IF;
2464
2465 if l_locality_check then
2466 hr_utility.set_location('locality check is TRUE' || l_proc,50);
2467 else
2468 hr_utility.set_location('locality check is FALSE' || l_proc,50);
2469 end if;
2470
2471
2472 --2 Check Duty station code,POI,agnecy and subelement.
2473
2474 IF l_locality_check
2475 AND l_user_table_id = nvl(l_pay_table_id,hr_api.g_number)
2476 AND check_init_eligibility(l_p_duty_station_id,
2477 l_p_PERSONNEL_OFFICE_ID,
2478 l_p_AGENCY_CODE_SUBELEMENT,
2479 l_duty_station_id,
2480 l_personnel_office_id,
2481 l_sub_element_code) then
2482
2483 hr_utility.set_location('check_init_eligibility ' || l_proc,55);
2484
2485 FOR c_grade_kff_rec IN c_grade_kff (l_grade_id)
2486 LOOP
2487 l_pay_plan := c_grade_kff_rec.segment1;
2488 l_grade_or_level := c_grade_kff_rec.segment2;
2489 exit;
2490 END loop;
2491
2492 hr_utility.set_location('l_pay_plan ' || l_pay_plan || l_proc,56);
2493 hr_utility.set_location('l_grade_or_level ' || l_grade_or_level || l_proc,56);
2494
2495 --3 (Internal Loop) Check pay plan,table id
2496 FOR l_cnt in 1..rec_pp_prd_per_gr.COUNT LOOP
2497
2498 hr_utility.set_location('For internal loop l_cnt ' || to_char(l_cnt) || l_proc,58);
2499 --4 Pay plan , table, grade check
2500 IF ( nvl(rec_pp_prd_per_gr(l_cnt).pay_plan,l_pay_plan) = l_pay_plan
2501 AND l_user_table_id = nvl(l_pay_table_id,hr_api.g_number)
2502 AND nvl(rec_pp_prd_per_gr(l_cnt).grade,l_grade_or_level)=l_grade_or_level ) THEN
2503
2504 hr_utility.set_location('check l_pay_plan ' || l_pay_plan || l_proc,58);
2505 hr_utility.set_location('check l_grade_or_level ' || l_grade_or_level || l_proc,58);
2506 BEGIN
2507 ghr_pa_requests_pkg.get_duty_station_details
2508 (p_duty_station_id => l_duty_station_id
2509 ,p_effective_DATE => l_effective_DATE
2510 ,p_duty_station_code => l_duty_station_code
2511 ,p_duty_station_desc => l_duty_station_desc);
2512 END;
2513 check_select_flg_pos(un_per.position_id
2514 ,UPPER(p_action)
2515 ,l_effective_DATE
2516 ,p_mass_salary_id
2517 ,l_sel_flg);
2518
2519 hr_utility.set_location('Entering Before get_special_table_pay_table_value ' || l_proc,60);
2520 ghr_pay_calc.get_special_pay_table_value
2521 (p_pay_plan => l_pay_plan
2522 ,p_grade_or_level => l_grade_or_level
2523 ,p_step_or_rate => null
2524 ,p_user_table_id => l_user_table_id
2525 ,p_effective_date => l_effective_date
2526 ,p_pt_value => l_pt_value
2527 ,p_PT_eff_start_date => l_pt_eff_start_date
2528 ,p_PT_eff_end_date => l_pt_eff_end_date
2529 ,p_pp_grd_exists => l_pp_grd_exists);
2530 --
2531
2532 --5 pp_grd check
2533 IF NOT l_pp_grd_exists THEN
2534 IF ghr_pay_calc.LEO_position (l_dummy
2535 ,l_position_id
2536 ,l_dummy
2537 ,l_dummy
2538 ,l_effective_date) AND l_grade_or_level between 03 and 10 THEN
2539 l_to_pay_table_id := l_0491_id;
2540 ELSE
2541 l_to_pay_table_id := l_0000_id;
2542 END IF;
2543
2544 IF UPPER(p_action) = 'SHOW' or (UPPER(p_action) = 'REPORT') THEN
2545 create_mass_act_prev_mtc (
2546 p_effective_date => l_effective_date,
2547 p_date_of_birth => null,
2548 p_full_name => un_per.full_name,
2549 p_national_identifier => un_per.national_identifier,
2550 --Bug # 9329643
2551 p_employee_number => un_per.employee_number,
2552 p_duty_station_code => l_duty_station_code,
2553 p_duty_station_desc => l_duty_station_desc,
2554 p_personnel_office_id => l_personnel_office_id,
2555 p_basic_pay => null,
2556 p_new_basic_pay => null,
2557 p_adj_basic_pay => null,
2558 p_new_adj_basic_pay => null,
2562 p_new_loc_diff => null,
2559 p_old_avail_pay => null,
2560 p_new_avail_pay => null,
2561 p_old_loc_diff => null,
2563 p_tot_old_sal => null,
2564 p_tot_new_sal => null,
2565 p_old_auo_pay => null,
2566 p_new_auo_pay => null,
2567 p_position_id => l_position_id,
2568 p_position_title => l_position_title,
2569 -- FWFA Changes Bug#4444609
2570 p_position_number => l_position_number,
2571 p_position_seq_no => l_position_seq_no,
2572 -- FWFA Changes
2573 p_org_structure_id => l_org_structure_id,
2574 p_agency_sub_element_code => l_sub_element_code,
2575 p_person_id => null,
2576 p_mass_salary_id => l_mass_salary_id,
2577 p_sel_flg => l_sel_flg,
2578 p_first_action_la_code1 => null,
2579 p_first_action_la_code2 => null,
2580 p_remark_code1 => null,
2581 p_remark_code2 => null,
2582 p_grade_or_level => l_grade_or_level,
2583 p_step_or_rate => null,
2584 p_pay_plan => l_pay_plan,
2585 p_pay_rate_determinant => null,
2586 p_tenure => null,
2587 p_action => p_action,
2588 p_assignment_id => null,
2589 p_old_other_pay => null,
2590 p_new_other_pay => null,
2591 p_old_capped_other_pay => null,
2592 p_new_capped_other_pay => null,
2593 p_old_retention_allowance => null,
2594 p_new_retention_allowance => null,
2595 p_old_supervisory_differential => null,
2596 p_new_supervisory_differential => null,
2597 p_organization_name => l_org_name,
2598 -- FWFA Changes Bug#4444609
2599 p_input_pay_rate_determinant => null,
2600 p_from_pay_table_id => l_user_table_id,
2601 p_to_pay_table_id => l_to_pay_table_id
2602 -- FWFA Changes
2603 );
2604 exit;
2605 ELSIF upper(p_action) = 'CREATE' THEN
2606 --Bug 4699955
2607 FOR c_pos_name_rec IN c_pos_name(l_position_id) LOOP
2608 l_pos_name := c_pos_name_rec.name;
2609 exit;
2610 END LOOP;
2611 --Bug 4699955
2612 if l_sel_flg = 'Y' then
2613 begin
2614 ghr_api.g_api_dml := TRUE;
2615 ghr_position_extra_info_api.update_position_extra_info
2616 ( p_position_extra_info_id => l_pos_valid_grade_ei_data.position_extra_info_id
2617 , p_effective_date => l_effective_date
2618 , p_object_version_number => l_pos_valid_grade_ei_data.object_version_number
2619 , p_poei_information5 => l_to_pay_table_id);
2620 ghr_api.g_api_dml := FALSE;
2621
2622 ghr_validate_perwsdpo.validate_perwsdpo(l_position_id,l_effective_date);
2623 ghr_validate_perwsdpo.update_posn_status(l_position_id,l_effective_date);
2624
2625 ghr_mto_int.log_message(
2626 p_procedure => 'Successful Completion',
2627 p_message => 'Vacant Position : '||l_pos_name ||
2628 ' Mass Table Change : '||
2629 p_mass_salary ||' Vacant pos Successfully completed');
2630
2631 position_history_update (p_position_id => l_position_id,
2632 p_effective_date => l_effective_date,
2633 p_table_id => l_user_table_id,
2634 p_upd_tableid => l_to_pay_table_id);
2635
2636 upd_ext_info_to_null(l_position_id,l_effective_DATE);
2637 exit;
2638 ------
2639 /******
2640 g_proc := 'ghr_sf52.UPDATE_position_info';
2641 l_position_data_rec.position_id := l_position_id;
2642 l_position_data_rec.effective_DATE := l_effective_DATE;
2643 l_position_data_rec.organization_id := un_per.organization_id;
2644 ----ghr_mre_pkg.UPDATE_position_info (l_position_data_rec);
2645 **************************/
2646 exception when others then
2647 l_mtcerrbuf := 'Error in ghr_sf52_pos_UPDATE.UPDATE_position_info' ||
2648 ' Sql Err is '|| sqlerrm(sqlcode);
2649 ghr_mto_int.log_message(
2650 p_procedure => 'Failed',
2651 p_message => 'Vacant Position : '||l_pos_name ||
2652 ' Mass Table Change : '||
2653 p_mass_salary || l_mtcerrbuf);
2654
2658 END IF; ------pp_grd_check
2655 end;
2656 end if;
2657 END IF; ------Preview or Final
2659 END IF; ------pay plan,table, grade check
2660 END LOOP; ------Internal Loop.
2661 END IF; ------Locality Check and Eligibility check
2662 END IF; ------Check of avaiability status
2663
2664 END LOOP;
2665
2666 if (l_recs_failed = 0) then
2667 IF upper(p_action) = 'CREATE' THEN
2668 begin
2669 update ghr_mass_salaries
2670 set submit_flag = 'P'
2671 where rowid = l_rowid;
2672 EXCEPTION
2673 when others then
2674 HR_UTILITY.SET_LOCATION('Error in Update ghr_msl Sql error '||sqlerrm(sqlcode),30);
2675 hr_utility.set_message(8301, 'GHR_38476_UPD_GHR_MSL_FAILURE');
2676 hr_utility.raise_error;
2677 END;
2678 end if;
2679 ELSE
2680 p_errbuf := 'Error in '||l_proc || ' Details in GHR_PROCESS_LOG';
2681 p_retcode := 2;
2682 IF upper(p_action) = 'CREATE' THEN
2683 update ghr_mass_salaries
2684 set submit_flag = 'E'
2685 where rowid = l_rowid;
2686 END IF;
2687 end if;
2688 COMMIT;
2689
2690 EXCEPTION
2691 when others then
2692 HR_UTILITY.SET_LOCATION('Error (Others2) occurred in '||l_proc||' Sql error '||sqlerrm(sqlcode),30);
2693 BEGIN
2694 ROLLBACK TO execute_msl_pay_SP;
2695 EXCEPTION
2696 WHEN OTHERS THEN NULL;
2697 END;
2698 l_log_text := 'Error in '||l_proc||
2699 ' For Mass Salary Name : '||p_mass_salary||
2700 ' Sql Err is '||sqlerrm(sqlcode);
2701 l_recs_failed := l_recs_failed + 1;
2702 hr_utility.set_location('before creating entry in log file',30);
2703
2704 p_errbuf := 'Error in '||l_proc || ' Details in GHR_PROCESS_LOG';
2705 p_retcode := 2;
2706 IF upper(p_action) = 'CREATE' THEN
2707 update ghr_mass_salaries
2708 set submit_flag = 'E'
2709 where rowid = l_rowid;
2710 commit;
2711 END IF;
2712
2713 begin
2714 ghr_mto_int.log_message(
2715 p_procedure => g_proc,
2716 p_message => l_log_text);
2717
2718 exception
2719 when others then
2720 hr_utility.set_message(8301, 'Create Error Log failed');
2721 hr_utility.raise_error;
2722 end;
2723
2724 END execute_msl_pay;
2725
2726 --
2727 --
2728 --
2729
2730 -- Function returns the request id.
2731 -- This is coded as a wrapper for fnd_request.submit_request
2732 -- if all the params are passed as null, the submit request is passing all the
2733 -- params as null and so, we get wrong no of params passed error.
2734 --
2735
2736 function SUBMIT_CONC_REQ (P_APPLICATION IN VARCHAR2,
2737 P_PROGRAM IN VARCHAR2,
2738 P_DESCRIPTION IN VARCHAR2,
2739 P_START_TIME IN VARCHAR2,
2740 P_SUB_REQUEST IN BOOLEAN,
2741 P_ARGUMENT1 IN VARCHAR2,
2742 P_ARGUMENT2 IN VARCHAR2)
2743 RETURN NUMBER IS
2744 BEGIN
2745 return (fnd_request.submit_request(
2746 APPLICATION => p_application
2747 ,PROGRAM => p_program
2748 ,DESCRIPTION => p_description
2749 ,START_TIME => p_start_time
2750 ,SUB_REQUEST => p_sub_request
2751 ,ARGUMENT1 => p_argument1
2752 ,ARGUMENT2 => p_argument2
2753 ));
2754
2755 end submit_conc_req;
2756
2757 --
2758 --
2759 --
2760 -- Procedure Deletes all records processed by the report
2761 --
2762
2763 procedure purge_processed_recs(p_session_id in number,
2764 p_err_buf out nocopy varchar2) is
2765 begin
2766 p_err_buf := null;
2767 delete from ghr_mass_actions_preview
2768 where mass_action_type = 'SALARY'
2769 and session_id = p_session_id;
2770 commit;
2771
2772 exception
2773 when others then
2774 p_err_buf := 'Sql err '|| sqlerrm(sqlcode);
2775 end;
2776
2777 --
2778 -- Added p_org_name to this proc for MLC form changes
2779 --
2780 procedure pop_dtls_from_pa_req(p_person_id in number,p_effective_date in date,
2781 p_mass_salary_id in number, p_org_name in varchar2, p_employee_number in varchar2) is
2782
2783 cursor ghr_pa_req_cur is
2784 select EMPLOYEE_DATE_OF_BIRTH,
2785 substr(EMPLOYEE_LAST_NAME||', '||EMPLOYEE_FIRST_NAME||' '||
2786 EMPLOYEE_MIDDLE_NAMES,1,240) FULL_NAME,
2787 EMPLOYEE_NATIONAL_IDENTIFIER,
2788 DUTY_STATION_CODE,
2789 DUTY_STATION_DESC,
2790 PERSONNEL_OFFICE_ID,
2791 FROM_BASIC_PAY,
2792 TO_BASIC_PAY,
2793 --Bug#2383992
2794 FROM_ADJ_BASIC_PAY,
2795 TO_ADJ_BASIC_PAY,
2796 --Bug#2383992
2797 NULL FROM_AVAILABILITY_PAY,
2798 TO_AVAILABILITY_PAY,
2799 FROM_LOCALITY_ADJ,
2800 TO_LOCALITY_ADJ,
2801 FROM_TOTAL_SALARY,
2802 TO_TOTAL_SALARY,
2803 NULL FROM_AU_OVERTIME,
2804 TO_AU_OVERTIME,
2805 TO_POSITION_ID POSITION_ID,
2806 TO_POSITION_TITLE POSITION_TITLE,
2807 -- FWFA Changes Bug#4444609
2808 TO_POSITION_NUMBER POSITION_NUMBER,
2809 TO_POSITION_SEQ_NO POSITION_SEQ_NO,
2810 -- FWFA Changes
2811 null org_structure_id,
2812 FROM_AGENCY_CODE,
2813 PERSON_ID,
2817 first_action_la_code2,
2814 -- p_mass_salary_id
2815 'Y' Sel_flag,
2816 first_action_la_code1,
2818 NULL REMARK_CODE1,
2819 NULL REMARK_CODE2,
2820 from_grade_or_level,
2821 from_step_or_rate,
2822 from_pay_plan,
2823 PAY_RATE_DETERMINANT,
2824 TENURE,
2825 EMPLOYEE_ASSIGNMENT_ID,
2826 FROM_OTHER_PAY_AMOUNT,
2827 TO_OTHER_PAY_AMOUNT,
2828 --Bug#2383992
2829 NULL FROM_RETENTION_ALLOWANCE,
2830 TO_RETENTION_ALLOWANCE,
2831 NULL FROM_SUPERVISORY_DIFFERENTIAL,
2832 TO_SUPERVISORY_DIFFERENTIAL,
2833 NULL FROM_CAPPED_OTHER_PAY,
2834 NULL TO_CAPPED_OTHER_PAY,
2835 -- FWFA Changes Bug#4444609
2836 input_pay_rate_determinant,
2837 from_pay_table_identifier,
2838 to_pay_table_identifier
2839 -- FWFA Changes
2840 from ghr_pa_requests
2841 where person_id = p_person_id
2842 and effective_date = p_effective_date
2843 and substr(request_number,(instr(request_number,'-')+1)) = to_char(p_mass_salary_id)
2844 and first_noa_code = nvl(ghr_msl_pkg.g_first_noa_code,'895');
2845
2846 l_proc varchar2(72) := g_package || '.pop_dtls_from_pa_req';
2847 begin
2848 g_proc := 'pop_dtls_from_pa_req';
2849
2850 hr_utility.set_location('Entering ' || l_proc,5);
2851 for pa_req_rec in ghr_pa_req_cur
2852 loop
2853 create_mass_act_prev (
2854 p_effective_date => p_effective_date,
2855 p_date_of_birth => pa_req_rec.employee_date_of_birth,
2856 p_full_name => pa_req_rec.full_name,
2857 p_national_identifier => pa_req_rec.employee_national_identifier,
2858 --Bug # 9329643
2859 p_employee_number => p_employee_number,
2860 p_duty_station_code => pa_req_rec.duty_station_code,
2861 p_duty_station_desc => pa_req_rec.duty_station_desc,
2862 p_personnel_office_id => pa_req_rec.personnel_office_id,
2863 p_basic_pay =>pa_req_rec.from_basic_pay,
2864 p_new_basic_pay => pa_req_rec.to_basic_pay,
2865 --Bug#2383992 Added old_adj_basic_pay
2866 p_adj_basic_pay => pa_req_rec.from_adj_basic_pay,
2867 p_new_adj_basic_pay => pa_req_rec.to_adj_basic_pay,
2868 p_old_avail_pay => pa_req_rec.from_availability_pay,
2869 p_new_avail_pay => pa_req_rec.to_availability_pay,
2870 p_old_loc_diff => pa_req_rec.from_locality_adj,
2871 p_new_loc_diff => pa_req_rec.to_locality_adj,
2872 p_tot_old_sal => pa_req_rec.from_total_salary,
2873 p_tot_new_sal => pa_req_rec.to_total_salary,
2874 p_old_auo_pay => pa_req_rec.from_au_overtime,
2875 p_new_auo_pay => pa_req_rec.to_au_overtime,
2876 p_position_id => pa_req_rec.position_id,
2877 p_position_title => pa_req_rec.position_title,
2878 -- FWFA Changes Bug#4444609
2879 p_position_number => pa_req_rec.position_number,
2880 p_position_seq_no => pa_req_rec.position_seq_no,
2881 -- FWFA Changes
2882 p_org_structure_id => pa_req_rec.org_structure_id,
2883 p_agency_sub_element_code => pa_req_rec.from_agency_code,
2884 p_person_id => pa_req_rec.person_id,
2885 p_mass_salary_id => p_mass_salary_id,
2886 p_sel_flg => 'Y', --- Sel flag
2887 p_first_action_la_code1 => pa_req_rec.first_action_la_code1,
2888 p_first_action_la_code2 => pa_req_rec.first_action_la_code2,
2889 p_remark_code1 => pa_req_rec.remark_code1, --- will be null
2890 p_remark_code2 => pa_req_rec.remark_code2, --- will be null
2891 p_grade_or_level => pa_req_rec.from_grade_or_level,
2892 p_step_or_rate => pa_req_rec.from_step_or_rate,
2893 p_pay_plan => pa_req_rec.from_pay_plan,
2894 p_pay_rate_determinant => pa_req_rec.pay_rate_determinant,
2895 p_tenure => pa_req_rec.tenure,
2896 p_action => 'REPORT',
2897 p_assignment_id => pa_req_rec.employee_assignment_id,
2898 p_old_other_pay => pa_req_rec.from_other_pay_amount,
2899 p_new_other_pay => pa_req_rec.to_other_pay_amount,
2900 -- Bug#2383992
2901 p_old_capped_other_pay => pa_req_rec.from_capped_other_pay,
2902 p_new_capped_other_pay => pa_req_rec.to_capped_other_pay,
2903 p_old_retention_allowance => pa_req_rec.from_retention_allowance,
2904 p_new_retention_allowance => pa_req_rec.to_retention_allowance,
2905 p_old_supervisory_differential => pa_req_rec.from_supervisory_differential,
2906 p_new_supervisory_differential => pa_req_rec.to_supervisory_differential,
2907 -- BUG 3377958 Madhuri
2908 p_organization_name => p_org_name,
2909 -- Bug#2383992
2910 -- FWFA Changes Bug#4444609
2911 p_input_pay_rate_determinant => pa_req_rec.input_pay_rate_determinant,
2912 p_from_pay_table_id => pa_req_rec.from_pay_table_identifier,
2913 p_to_pay_table_id => pa_req_rec.to_pay_table_identifier
2914 -- FWFA Changes
2915 );
2916 exit;
2917 END LOOP;
2918 hr_utility.set_location('Exiting ' || l_proc,10);
2919 exception
2920 when mlc_error then raise;
2921 when others then
2922 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2923 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2924 raise mlc_error;
2925 end pop_dtls_from_pa_req;
2926 --
2927 -- Added the following procedure for MTC changes
2928 --
2929 procedure pop_dtls_from_pa_req_mtc(p_person_id in number,p_effective_date in date,
2930 p_mass_salary_id in number, p_org_name in varchar2, p_employee_number in varchar2) is
2931
2932 cursor ghr_pa_req_cur is
2936 EMPLOYEE_NATIONAL_IDENTIFIER,
2933 select EMPLOYEE_DATE_OF_BIRTH,
2934 substr(EMPLOYEE_LAST_NAME||', '||EMPLOYEE_FIRST_NAME||' '||
2935 EMPLOYEE_MIDDLE_NAMES,1,240) FULL_NAME,
2937 DUTY_STATION_CODE,
2938 DUTY_STATION_DESC,
2939 PERSONNEL_OFFICE_ID,
2940 FROM_BASIC_PAY,
2941 TO_BASIC_PAY,
2942 --Bug#2383992
2943 FROM_ADJ_BASIC_PAY,
2944 TO_ADJ_BASIC_PAY,
2945 --Bug#2383992
2946 NULL FROM_AVAILABILITY_PAY,
2947 TO_AVAILABILITY_PAY,
2948 FROM_LOCALITY_ADJ,
2949 TO_LOCALITY_ADJ,
2950 FROM_TOTAL_SALARY,
2951 TO_TOTAL_SALARY,
2952 NULL FROM_AU_OVERTIME,
2953 TO_AU_OVERTIME,
2954 TO_POSITION_ID POSITION_ID,
2955 TO_POSITION_TITLE POSITION_TITLE,
2956 -- FWFA Changes Bug#4444609
2957 TO_POSITION_NUMBER POSITION_NUMBER,
2958 TO_POSITION_SEQ_NO POSITION_SEQ_NO,
2959 -- FWFA Changes
2960 null org_structure_id,
2961 FROM_AGENCY_CODE,
2962 PERSON_ID,
2963 -- p_mass_salary_id
2964 'Y' Sel_flag,
2965 first_action_la_code1,
2966 first_action_la_code2,
2967 NULL REMARK_CODE1,
2968 NULL REMARK_CODE2,
2969 from_grade_or_level,
2970 from_step_or_rate,
2971 from_pay_plan,
2972 PAY_RATE_DETERMINANT,
2973 TENURE,
2974 EMPLOYEE_ASSIGNMENT_ID,
2975 FROM_OTHER_PAY_AMOUNT,
2976 TO_OTHER_PAY_AMOUNT,
2977 --Bug#2383992
2978 NULL FROM_RETENTION_ALLOWANCE,
2979 TO_RETENTION_ALLOWANCE,
2980 NULL FROM_SUPERVISORY_DIFFERENTIAL,
2981 TO_SUPERVISORY_DIFFERENTIAL,
2982 NULL FROM_CAPPED_OTHER_PAY,
2983 NULL TO_CAPPED_OTHER_PAY,
2984 -- FWFA Changes Bug#4444609
2985 input_pay_rate_determinant,
2986 from_pay_table_identifier,
2987 to_pay_table_identifier
2988 -- FWFA Changes
2989 from ghr_pa_requests
2990 where person_id = p_person_id
2991 and effective_date = p_effective_date
2992 and substr(request_number,(instr(request_number,'-')+1)) = to_char(p_mass_salary_id)
2993 and first_noa_code in ('894','800');
2994
2995 l_proc varchar2(72) := g_package || '.pop_dtls_from_pa_req_mtc';
2996 begin
2997 g_proc := 'pop_dtls_from_pa_req_mtc';
2998
2999 hr_utility.set_location('Entering ' || l_proc,5);
3000 for pa_req_rec in ghr_pa_req_cur
3001 loop
3002 create_mass_act_prev_mtc (
3003 p_effective_date => p_effective_date,
3004 p_date_of_birth => pa_req_rec.employee_date_of_birth,
3005 p_full_name => pa_req_rec.full_name,
3006 p_national_identifier => pa_req_rec.employee_national_identifier,
3007 --Bug # 9329643
3008 p_employee_number => p_employee_number,
3009 p_duty_station_code => pa_req_rec.duty_station_code,
3010 p_duty_station_desc => pa_req_rec.duty_station_desc,
3011 p_personnel_office_id => pa_req_rec.personnel_office_id,
3012 p_basic_pay =>pa_req_rec.from_basic_pay,
3013 p_new_basic_pay => pa_req_rec.to_basic_pay,
3014 p_adj_basic_pay => pa_req_rec.from_adj_basic_pay,
3015 p_new_adj_basic_pay => pa_req_rec.to_adj_basic_pay,
3016 p_old_avail_pay => pa_req_rec.from_availability_pay,
3017 p_new_avail_pay => pa_req_rec.to_availability_pay,
3018 p_old_loc_diff => pa_req_rec.from_locality_adj,
3019 p_new_loc_diff => pa_req_rec.to_locality_adj,
3020 p_tot_old_sal => pa_req_rec.from_total_salary,
3021 p_tot_new_sal => pa_req_rec.to_total_salary,
3022 p_old_auo_pay => pa_req_rec.from_au_overtime,
3023 p_new_auo_pay => pa_req_rec.to_au_overtime,
3024 p_position_id => pa_req_rec.position_id,
3025 p_position_title => pa_req_rec.position_title,
3026 -- FWFA Changes Bug#4444609
3027 p_position_number => pa_req_rec.position_number,
3028 p_position_seq_no => pa_req_rec.position_seq_no,
3029 -- FWFA Changes
3030 p_org_structure_id => pa_req_rec.org_structure_id,
3031 p_agency_sub_element_code => pa_req_rec.from_agency_code,
3032 p_person_id => pa_req_rec.person_id,
3033 p_mass_salary_id => p_mass_salary_id,
3034 p_sel_flg => 'Y', --- Sel flag
3035 p_first_action_la_code1 => pa_req_rec.first_action_la_code1,
3036 p_first_action_la_code2 => pa_req_rec.first_action_la_code2,
3037 p_remark_code1 => pa_req_rec.remark_code1, --- will be null
3038 p_remark_code2 => pa_req_rec.remark_code2, --- will be null
3039 p_grade_or_level => pa_req_rec.from_grade_or_level,
3040 p_step_or_rate => pa_req_rec.from_step_or_rate,
3041 p_pay_plan => pa_req_rec.from_pay_plan,
3042 p_pay_rate_determinant => pa_req_rec.pay_rate_determinant,
3043 p_tenure => pa_req_rec.tenure,
3044 p_action => 'REPORT',
3045 p_assignment_id => pa_req_rec.employee_assignment_id,
3046 p_old_other_pay => pa_req_rec.from_other_pay_amount,
3047 p_new_other_pay => pa_req_rec.to_other_pay_amount,
3048 p_old_capped_other_pay => pa_req_rec.from_capped_other_pay,
3049 p_new_capped_other_pay => pa_req_rec.to_capped_other_pay,
3050 p_old_retention_allowance => pa_req_rec.from_retention_allowance,
3051 p_new_retention_allowance => pa_req_rec.to_retention_allowance,
3052 p_old_supervisory_differential => pa_req_rec.from_supervisory_differential,
3053 p_new_supervisory_differential => pa_req_rec.to_supervisory_differential,
3054 p_organization_name => p_org_name,
3055 -- FWFA Changes Bug#4444609
3059 -- FWFA Changes
3056 p_input_pay_rate_determinant => pa_req_rec.input_pay_rate_determinant,
3057 p_from_pay_table_id => pa_req_rec.from_pay_table_identifier,
3058 p_to_pay_table_id => pa_req_rec.to_pay_table_identifier
3060 );
3061 exit;
3062 END LOOP;
3063 hr_utility.set_location('Exiting ' || l_proc,10);
3064 exception
3065 when mlc_error then raise;
3066 when others then
3067 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
3068 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
3069 raise mlc_error;
3070 end pop_dtls_from_pa_req_mtc;
3071
3072
3073 --
3074 --
3075 --
3076
3077 FUNCTION check_select_flg(p_person_id in number,
3078 p_action in varchar2,
3079 p_effective_date in date,
3080 p_mass_salary_id in number,
3081 p_sel_flg in out nocopy varchar2)
3082 RETURN boolean IS
3083
3084 l_per_ei_data per_people_extra_info%rowtype;
3085 l_comments varchar2(250);
3086 l_sel_flag varchar2(3);
3087 l_line number := 0;
3088
3089 l_proc varchar2(72) := g_package || '.check_select_flg';
3090
3091 BEGIN
3092
3093 g_proc := 'check_select_flg';
3094 hr_utility.set_location('Entering ' || l_proc,5);
3095
3096 get_extra_info_comments(p_person_id,p_effective_date,l_sel_flag,l_comments,p_mass_salary_id);
3097
3098 --------- Initialize the comments
3099 -- Now all the messages have MLC as a prefix.
3100 --
3101 IF l_comments is not null THEN
3102 IF substr(nvl(l_comments,'@#%'),1,3) = 'MLC' THEN
3103 ins_upd_per_extra_info
3104 (p_person_id,p_effective_date, l_sel_flag, null,p_mass_salary_id);
3105 END IF;
3106 END IF;
3107 ---------
3108
3109 IF l_sel_flag IS null THEN
3110 p_sel_flg := 'Y';
3111 ELSE
3112 p_sel_flg := l_sel_flag;
3113 END IF;
3114
3115 IF p_action IN ('SHOW','REPORT') THEN
3116 RETURN TRUE;
3117 ELSIF p_action = 'CREATE' THEN
3118 IF p_sel_flg = 'Y' THEN
3119 RETURN TRUE;
3120 ELSE
3121 RETURN FALSE;
3122 END IF;
3123 END IF;
3124 EXCEPTION
3125 when mlc_error then raise;
3126 when others then
3127 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
3128 l_mlcerrbuf := 'Error in '||l_proc||' @'||to_char(l_line)||' Sql Err is '|| sqlerrm(sqlcode);
3129 raise mlc_error;
3130 END;
3131
3132 --
3133 --
3134 --
3135
3136 procedure purge_old_data (p_mass_salary_id in number) is
3137 l_proc varchar2(72) := g_package || '.purge_old_data';
3138 BEGIN
3139 g_proc := 'purge_old_data';
3140
3141 hr_utility.set_location('Entering ' || l_proc,5);
3142 delete from ghr_mass_actions_preview
3143 where mass_action_type = 'SALARY'
3144 and session_id = p_mass_salary_id;
3145 commit;
3146 hr_utility.set_location('Exiting ' || l_proc,10);
3147 exception
3148 when mlc_error then raise;
3149 when others then
3150 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
3151 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
3152 raise mlc_error;
3153 END;
3154
3155 --
3156 --
3157 --
3158
3159 --Removed the procedure get_pay_plan_and_table_id
3160
3161 --
3162 --
3163 --
3164
3165 procedure update_sel_flg (p_person_id in number,p_effective_date date) is
3166
3167 l_person_extra_info_id number;
3168 l_object_version_number number;
3169 l_per_ei_data per_people_extra_info%rowtype;
3170 l_proc varchar2(72) := g_package || '.update_sel_flg';
3171 l_ind number := 1;
3172 begin
3173 g_proc := 'update_sel_flg';
3174 hr_utility.set_location('Entering ' || l_proc,5);
3175 pr('Inside '||l_proc,to_char(p_person_id));
3176 l_ind := 10;
3177 ghr_history_fetch.fetch_peopleei
3178 (p_person_id => p_person_id
3179 ,p_information_type => 'GHR_US_PER_MASS_ACTIONS'
3180 ,p_date_effective => p_effective_date
3181 ,p_per_ei_data => l_per_ei_data);
3182
3183 l_ind := 20;
3184 l_person_extra_info_id := l_per_ei_data.person_extra_info_id;
3185 l_object_version_number := l_per_ei_data.object_version_number;
3186
3187 if l_person_extra_info_id is not null then
3188 ghr_person_extra_info_api.update_person_extra_info
3189 (P_PERSON_EXTRA_INFO_ID => l_person_extra_info_id
3190 ,P_EFFECTIVE_DATE => sysdate
3191 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
3192 ,p_pei_INFORMATION3 => NULL
3193 ,p_pei_INFORMATION4 => NULL
3194 ,p_pei_INFORMATION5 => NULL
3195 ,P_PEI_INFORMATION_CATEGORY => 'GHR_US_PER_MASS_ACTIONS');
3196
3197 l_ind := 30;
3198 hr_utility.set_location('Exiting ' || l_proc,10);
3199 -- There is a trigger on PER_PEOPLE_EXTRA_INFO to make the employee INVALID
3200 -- when there is a update done on the table.
3201 ---Commented the following three lines to remove Validation functionality on Person.
3202 --- ghr_validate_perwsepi.validate_perwsepi(p_person_id);
3203 --- l_ind := 40;
3204 --- ghr_validate_perwsepi.update_person_user_type(p_person_id);
3205 end if;
3206
3207 l_ind := 50;
3208 pr('Exiting '||l_proc,to_char(p_person_id));
3209 exception
3213 ' Err is '||sqlerrm(sqlcode),20);
3210 when mlc_error then raise;
3211 when others then
3212 hr_utility.set_location('Error in '||l_proc||
3214 l_mlcerrbuf := 'Error in '||l_proc||' at '||to_char(l_ind)||
3215 ' Sql Err is '|| sqlerrm(sqlcode);
3216 raise mlc_error;
3217 end update_sel_flg;
3218
3219 --
3220 --
3221 --
3222
3223 FUNCTION GET_PAY_PLAN_NAME (PP IN VARCHAR2) RETURN VARCHAR2 IS
3224
3225 CURSOR CUR_PP IS
3226 select pay_plan,description
3227 from ghr_pay_plans
3228 WHERE PAY_PLAN = PP;
3229 l_pp_desc varchar2(150);
3230 BEGIN
3231 FOR PP_REC IN CUR_PP
3232 LOOP
3233 l_pp_desc := pp_rec.description;
3234 exit;
3235 END LOOP;
3236 return (l_pp_desc);
3237 END;
3238
3239 FUNCTION GET_USER_TABLE_name (P_USER_TABLE_id IN NUMBER) RETURN VARCHAR2 IS
3240 CURSOR MSL_CUR IS
3241 select user_table_id,substr(user_table_name,0,4) user_table_name
3242 from pay_user_tables
3243 where substr(user_table_name,6,14) in
3244 ('Oracle Federal','Federal Agency')
3245 and user_table_id = p_user_table_id;
3246 l_user_table_name varchar2(80);
3247 BEGIN
3248 for msl in msl_cur
3249 LOOP
3250 l_user_table_name := msl.user_table_name;
3251 exit;
3252 end loop;
3253 return (l_user_table_name);
3254 END;
3255
3256 --
3257 --
3258 --
3259
3260
3261 PROCEDURE get_extra_info_comments
3262 (p_person_id in number,
3263 p_effective_date in date,
3264 p_sel_flag in out nocopy varchar2,
3265 p_comments in out nocopy varchar2,
3266 p_mass_salary_id in number) is
3267
3268 l_per_ei_data per_people_extra_info%rowtype;
3269 l_proc varchar2(72) := g_package || '.get_extra_info_comments';
3270 l_eff_date date;
3271
3272 CURSOR chk_history (p_person_id in NUMBER ,
3273 eff_date in Date) IS
3274 select information9 info9
3275 ,information10 info10
3276 ,information11 info11
3277 from ghr_pa_history
3278 where person_id = p_person_id
3279 and pa_history_id IN ( select max(pa_history_id)
3280 from ghr_pa_history
3281 where person_id = p_person_id
3282 and information5 = 'GHR_US_PER_MASS_ACTIONS'
3283 and table_name = 'PER_PEOPLE_EXTRA_INFO'
3284 and effective_date = eff_date
3285 group by information11);
3286
3287 begin
3288 g_proc := 'get_extra_info_comments';
3289 hr_utility.set_location('Entering ' || l_proc,5);
3290 /*
3291 if p_effective_date > sysdate then
3292 l_eff_date := sysdate;
3293 else
3294 l_eff_date := p_effective_date;
3295 end if;
3296 */
3297 l_eff_date := p_effective_date;
3298
3299 ghr_history_fetch.fetch_peopleei
3300 (p_person_id => p_person_id
3301 ,p_information_type => 'GHR_US_PER_MASS_ACTIONS'
3302 ,p_date_effective => l_eff_date
3303 ,p_per_ei_data => l_per_ei_data);
3304
3305 if l_per_ei_data.pei_information5 <> p_mass_salary_id then
3306 p_sel_flag := 'Y';
3307 p_comments := null;
3308 else
3309 p_sel_flag := l_per_ei_data.pei_information3;
3310 p_comments := l_per_ei_data.pei_information4;
3311 end if;
3312
3313 IF p_sel_flag IS NOT NULL and (l_per_ei_data.pei_information5 <> p_mass_salary_id) THEN
3314 FOR chk_history_rec in chk_history(p_person_id => p_person_id,
3315 eff_date => l_eff_date) loop
3316 If chk_history_rec.info11 = p_mass_salary_id then
3317 p_sel_flag := chk_history_rec.info9;
3318 p_comments := chk_history_rec.info10;--Added by Ashley
3319 END IF;
3320 END LOOP;
3321 END IF;
3322
3323
3324 exception
3325 when mlc_error then raise;
3326 when others then
3327 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
3328 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
3329 raise mlc_error;
3330 end;
3331
3332 --
3333 --
3334 --
3335
3336 procedure ins_upd_per_extra_info
3337 (p_person_id in number,p_effective_date in date,
3338 p_sel_flag in varchar2, p_comment in varchar2,p_msl_id in number) is
3339
3340 l_person_extra_info_id number;
3341 l_object_version_number number;
3342 l_per_ei_data per_people_extra_info%rowtype;
3343
3344 CURSOR people_ext_cur (person number) is
3345 SELECT person_extra_info_id, object_version_number
3346 FROM PER_people_EXTRA_INFO
3347 WHERE person_ID = person
3348 and information_type = 'GHR_US_PER_MASS_ACTIONS';
3349
3350 l_proc varchar2(72) := g_package || '.ins_upd_per_extra_info';
3351 l_eff_date date;
3352
3353 begin
3354 g_proc := 'ins_upd_per_extra_info';
3355 hr_utility.set_location('Entering ' || l_proc,5);
3356 if p_effective_date > sysdate then
3357 l_eff_date := sysdate;
3358 else
3359 l_eff_date := p_effective_date;
3360 end if;
3361
3362 ghr_history_fetch.fetch_peopleei
3363 (p_person_id => p_person_id
3364 ,p_information_type => 'GHR_US_PER_MASS_ACTIONS'
3365 ,p_date_effective => l_eff_date
3366 ,p_per_ei_data => l_per_ei_data);
3367
3368 l_person_extra_info_id := l_per_ei_data.person_extra_info_id;
3372 for per_ext_rec in people_ext_cur(p_person_id)
3369 l_object_version_number := l_per_ei_data.object_version_number;
3370
3371 if l_person_extra_info_id is null then
3373 loop
3374 l_person_extra_info_id := per_ext_rec.person_extra_info_id;
3375 l_object_version_number := per_ext_rec.object_version_number;
3376 end loop;
3377 end if;
3378
3379 if l_person_extra_info_id is not null then
3380 ghr_person_extra_info_api.update_person_extra_info
3381 (P_PERSON_EXTRA_INFO_ID => l_person_extra_info_id
3382 ,P_EFFECTIVE_DATE => trunc(l_eff_date)
3383 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
3384 ,p_pei_INFORMATION3 => p_sel_flag
3385 ,p_pei_INFORMATION4 => p_comment
3386 ,p_pei_INFORMATION5 => to_char(p_msl_id)
3387 ,P_PEI_INFORMATION_CATEGORY => 'GHR_US_PER_MASS_ACTIONS');
3388 else
3389 ghr_person_extra_info_api.create_person_extra_info
3390 (P_pERSON_ID => p_PERSON_id
3391 ,P_INFORMATION_TYPE => 'GHR_US_PER_MASS_ACTIONS'
3392 ,P_EFFECTIVE_DATE => trunc(l_eff_date)
3393 ,p_pei_INFORMATION3 => p_sel_flag
3394 ,p_pei_INFORMATION4 => p_comment
3395 ,p_pei_INFORMATION5 => to_char(p_msl_id)
3396 ,P_PEI_INFORMATION_CATEGORY => 'GHR_US_PER_MASS_ACTIONS'
3397 ,P_pERSON_EXTRA_INFO_ID => l_pERSON_extra_info_id
3398 ,P_OBJECT_VERSION_NUMBER => l_object_version_number);
3399 end if;
3400
3401 ---Commented the following two lines to remove Validation functionality on Person.
3402 -- ghr_validate_perwsepi.validate_perwsepi(p_person_id);
3403 -- ghr_validate_perwsepi.update_person_user_type(p_person_id);
3404
3405 hr_utility.set_location('Exiting ' || l_proc,10);
3406 exception
3407 when mlc_error then raise;
3408 when others then
3409 pr('Error in '||l_proc);
3410 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
3411 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
3412 raise mlc_error;
3413 end ins_upd_per_extra_info;
3414
3415 --
3416 --
3417 --
3418
3419 FUNCTION check_init_eligibility(p_duty_station_id in number,
3420 p_PERSONNEL_OFFICE_ID in varchar2,
3421 p_AGENCY_CODE_SUBELEMENT in varchar2,
3422 p_l_duty_station_id in number,
3423 p_l_personnel_office_id in varchar2,
3424 p_l_sub_element_code in varchar2)
3425 return boolean is
3426
3427 CURSOR cur_valid_DS(p_ds_id NUMBER)
3428 IS
3429 SELECT effective_end_date end_date
3430 FROM ghr_duty_stations_f
3431 WHERE duty_station_id=p_ds_id
3432 AND g_effective_date between effective_start_date and effective_end_date;
3433
3434 l_ds_end_date ghr_duty_stations_f.effective_end_date%type;
3435
3436 l_proc varchar2(72) := g_package || '.check_init_eligibility';
3437 BEGIN
3438
3439 g_proc := 'check_init_eligibility';
3440 hr_utility.set_location('Entering ' || l_proc,5);
3441
3442 if p_personnel_office_id is not null then
3443 if p_personnel_office_id <> nvl(p_l_personnel_office_id,'NULL!~') then
3444 return false;
3445 end if;
3446 end if;
3447
3448 if p_agency_code_subelement is not null then
3449 if substr(p_agency_code_subelement, 1, 2) <> nvl(substr(p_l_sub_element_code, 1, 2), 'NULL!~') then
3450 return false;
3451 end if;
3452 end if;
3453
3454 if substr(p_agency_code_subelement, 3, 2) is not null then
3455 if substr(p_agency_code_subelement, 3, 2) <> nvl(substr(p_l_sub_element_code, 3, 2), 'NULL!~') then
3456 return false;
3457 end if;
3458 end if;
3459
3460 if p_duty_station_id is not null then
3461 if p_duty_station_id <> nvl(p_l_duty_station_id,0) then
3462 return false;
3463 end if;
3464 end if;
3465
3466 FOR rec_ds in cur_valid_ds(p_l_duty_station_id)
3467 LOOP
3468 l_ds_end_date := rec_ds.end_Date;
3469 END LOOP;
3470
3471 If l_ds_end_date IS NULL THEN
3472 hr_utility.set_location('Under DS null check'||p_l_duty_station_id,12345);
3473 raise mlc_error;
3474 return false;
3475 end if;
3476
3477 pr('Eligible');
3478 return true;
3479 exception
3480 when mlc_error then --raise;
3481 hr_utility.set_location('Error NO DUTY STATION '||l_proc||' Err is '||sqlerrm(sqlcode),20);
3482 l_mlcerrbuf :=
3483 'Error - No valid Location found, salary cannot be correctly calculated without the employee''s duty location ';
3484 return false;
3485 raise mlc_error;
3486 when others then
3487 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
3488 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
3489 return false;
3490 raise mlc_error;
3491 END check_init_eligibility;
3492
3493
3494 FUNCTION check_eligibility(p_pay_plan in varchar2,
3495 p_person_id in number,
3496 p_effective_date in date,
3497 p_action in varchar2)
3498 return boolean is
3499
3500 l_proc varchar2(72) := g_package || '.check_eligibility';
3501
3502 CURSOR cur_equiv_ES_pay_plan(p_pay_plan ghr_pay_plans.pay_plan%TYPE)
3503 IS
3504 SELECT 1
3505 FROM ghr_pay_plans
3506 WHERE equivalent_pay_plan ='ES'
3507 AND pay_plan=p_pay_plan;
3508
3512
3509 BEGIN
3510 g_proc := 'check_eligibility';
3511 hr_utility.set_location('Entering ' || l_proc,5);
3513 --- MSL percentage changes Madhuri
3514 ---
3515
3516 FOR es_rec IN cur_equiv_ES_pay_plan(p_pay_plan)
3517 LOOP
3518 RETURN FALSE;
3519 END LOOP;
3520 --Begin Bug 10387022 added GL, removed SL, ST in below if condition
3521 IF p_pay_plan NOT IN ('AD','AL','GG','GH','GM','GS','IP','GL',
3522 'FB','FG','FJ','FM','FX','CA','AA','EE') THEN
3523 RETURN FALSE;
3524 END IF;
3525
3526 ---Filtering the pay plans which need not
3527 ---be picked for Locality Adjustment.
3528
3529 IF p_action = 'CREATE' THEN
3530 IF person_in_pa_req_1noa
3531 (p_person_id => p_person_id,
3532 p_effective_date => p_effective_date,
3533 p_first_noa_code => nvl(ghr_msl_pkg.g_first_noa_code,'895'),
3534 p_pay_plan => p_pay_plan
3535 ) then
3536 ghr_mre_pkg.pr('1noa failed',to_char(p_person_id));
3537 RETURN FALSE;
3538 ELSE
3539 ghr_mre_pkg.pr('Eligible');
3540 RETURN TRUE;
3541 END IF;
3542 END IF;
3543
3544 RETURN TRUE;
3545 exception
3546 when mlc_error then raise;
3547 when others then
3548 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
3549 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
3550 raise mlc_error;
3551 END check_eligibility;
3552
3553 --
3554 --
3555 --
3556
3557 function person_in_pa_req_1noa
3558 (p_person_id in number,
3559 p_effective_date in date,
3560 p_first_noa_code in varchar2,
3561 p_pay_plan in varchar2,
3562 p_days in number default 350
3563 )
3564 return boolean is
3565 --
3566 l_name per_people_f.full_name%type;
3567 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
3568
3569 cursor csr_action_taken is
3570 select pr.pa_request_id, max(pa_routing_history_id) pa_routing_history_id
3571 from ghr_pa_requests pr, ghr_pa_routing_history prh
3572 where pr.pa_request_id = prh.pa_request_id
3573 and person_id = p_person_id
3574 and first_noa_code = p_first_noa_code
3575 and effective_date = p_effective_date
3576 and nvl(pr.first_noa_cancel_or_correct,'X') <> ghr_history_api.g_cancel
3577 ---- Bug # 657439
3578 --and nvl(pr.first_noa_cancel_or_correct,'X') <> 'CANCELED'
3579 group by pr.pa_request_id;
3580
3581 cursor csr_name is
3582 select substr(pr.employee_last_name || ', ' || pr.employee_first_name,1,240) fname
3583 from ghr_pa_requests pr
3584 where pr.pa_request_id = l_pa_request_id;
3585
3586 cursor csr_action_taken_fw is
3587 select pr.pa_request_id, max(pa_routing_history_id) pa_routing_history_id
3588 from ghr_pa_requests pr, ghr_pa_routing_history prh
3589 where pr.pa_request_id = prh.pa_request_id
3590 and person_id = p_person_id
3591 and first_noa_code = p_first_noa_code
3592 and effective_date = p_effective_date
3593 and nvl(pr.first_noa_cancel_or_correct,'X') <> ghr_history_api.g_cancel
3594 group by pr.pa_request_id;
3595
3596 cursor csr_eq_pay_plan is
3597 SELECT equivalent_pay_plan
3598 FROM ghr_pay_plans
3599 WHERE pay_plan = p_pay_plan;
3600
3601 cursor pa_hist_cur (p_r_hist_id number) is
3602 select nvl(action_taken,' ') action_taken
3603 from ghr_pa_routing_history
3604 where pa_routing_history_id = p_r_hist_id;
3605
3606 l_action_taken ghr_pa_routing_history.action_taken%TYPE;
3607 l_proc varchar2(72) := g_package || '.person_in_pa_req_1noa';
3608 l_eq_pay_plan ghr_pay_plans.equivalent_pay_plan%type;
3609
3610 begin
3611 g_proc := 'person_in_pa_req_1noa';
3612 hr_utility.set_location('Entering ' || l_proc,5);
3613 -- Bug 1631952 start
3614
3615 for csr_eq_pay_plan_rec in csr_eq_pay_plan loop
3616 l_eq_pay_plan := csr_eq_pay_plan_rec.equivalent_pay_plan;
3617 end loop;
3618
3619 if l_eq_pay_plan = 'FW' then
3620 for v_action_taken_fw in csr_action_taken_fw loop
3621 l_pa_request_id := v_action_taken_fw.pa_request_id;
3622 for v_name in csr_name
3623 loop
3624 l_name := v_name.fname;
3625 exit;
3626 end loop;
3627 for pa_hist_rec in pa_hist_cur (v_action_taken_fw.pa_routing_history_id)
3628 loop
3629 l_action_taken := pa_hist_rec.action_taken;
3630 exit;
3631 end loop;
3632 if l_action_taken <> 'CANCELED' then
3633 ghr_mto_int.log_message(
3634 p_procedure => 'RPA Exists Already',
3635 p_message => 'Name: '|| l_name || ' - Salary Change ' ||
3636 ' RPA Exists for the given FWS pay_lan and effective date' );
3637 return true;
3638 end if;
3639 end loop;
3640 else
3641 --- Bug 1631952 end. The same bug was extended to GS equvalent pay plans.
3642 for v_action_taken in csr_action_taken loop
3643 l_pa_request_id := v_action_taken.pa_request_id;
3644 for v_name in csr_name
3645 loop
3646 l_name := v_name.fname;
3647 exit;
3648 end loop;
3649 for pa_hist_rec in pa_hist_cur (v_action_taken.pa_routing_history_id)
3650 loop
3651 l_action_taken := pa_hist_rec.action_taken;
3652 exit;
3653 end loop;
3654 if l_action_taken <> 'CANCELED' then
3655 ghr_mto_int.log_message(
3656 p_procedure => 'RPA Exists Already',
3660 end if;
3657 p_message => 'Name: '|| l_name || ' - Salary Change ' ||
3658 ' RPA Exists for the given effective date ' );
3659 return true;
3661
3662 end loop;
3663 end if; ------Bug 1631952
3664 return false;
3665 end person_in_pa_req_1noa;
3666
3667 --
3668
3669
3670 FUNCTION check_eligibility_mtc(p_pay_plan in varchar2,
3671 p_person_id in number,
3672 p_effective_date in date,
3673 p_action in varchar2)
3674 return boolean is
3675
3676 l_proc varchar2(72) := g_package || '.check_eligibility';
3677
3678 CURSOR cur_equiv_ES_pay_plan(p_pay_plan ghr_pay_plans.pay_plan%TYPE)
3679 IS
3680 SELECT 1
3681 FROM ghr_pay_plans
3682 WHERE equivalent_pay_plan ='ES'
3683 AND pay_plan=p_pay_plan;
3684
3685 BEGIN
3686 g_proc := 'check_eligibility';
3687 hr_utility.set_location('Entering ' || l_proc,5);
3688
3689 --- MSL percentage changes Madhuri
3690 ---
3691
3692 FOR es_rec IN cur_equiv_ES_pay_plan(p_pay_plan)
3693 LOOP
3694 RETURN FALSE;
3695 END LOOP;
3696
3697 IF p_pay_plan NOT IN ('AD','AL','GG','GH','GM','GS','IP',
3698 'FB','FG','FJ','FM','FX','CA','AA','SL','ST','EE') THEN
3699 RETURN FALSE;
3700 END IF;
3701
3702 ---Filtering the pay plans which need not
3703 ---be picked for Locality Adjustment.
3704
3705 IF p_action = 'CREATE' THEN
3706 IF person_in_pa_req_1noa_mtc
3707 (p_person_id => p_person_id,
3708 p_effective_date => p_effective_date,
3709 p_first_noa_code => '894',
3710 p_pay_plan => p_pay_plan
3711 ) then
3712 ghr_mre_pkg.pr('1noa failed',to_char(p_person_id));
3713 RETURN FALSE;
3714 ELSE
3715 ghr_mre_pkg.pr('Eligible');
3716 RETURN TRUE;
3717 END IF;
3718 END IF;
3719
3720 RETURN TRUE;
3721 exception
3722 when mlc_error then raise;
3723 when others then
3724 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
3725 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
3726 raise mlc_error;
3727 END check_eligibility_mtc;
3728
3729 --
3730 --
3731 --
3732
3733 function person_in_pa_req_1noa_mtc
3734 (p_person_id in number,
3735 p_effective_date in date,
3736 p_first_noa_code in varchar2,
3737 p_pay_plan in varchar2,
3738 p_days in number default 350
3739 )
3740 return boolean is
3741 --
3742 l_name per_people_f.full_name%type;
3743 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
3744
3745 cursor csr_action_taken is
3746 select pr.pa_request_id, max(pa_routing_history_id) pa_routing_history_id
3750 and (first_noa_code = p_first_noa_code or first_noa_code = '800')
3747 from ghr_pa_requests pr, ghr_pa_routing_history prh
3748 where pr.pa_request_id = prh.pa_request_id
3749 and person_id = p_person_id
3751 and effective_date = p_effective_date
3752 and nvl(pr.first_noa_cancel_or_correct,'X') <> ghr_history_api.g_cancel
3753 group by pr.pa_request_id;
3754
3755 cursor csr_name is
3756 select substr(pr.employee_last_name || ', ' || pr.employee_first_name,1,240) fname
3757 from ghr_pa_requests pr
3758 where pr.pa_request_id = l_pa_request_id;
3759
3760 cursor csr_action_taken_fw is
3761 select pr.pa_request_id, max(pa_routing_history_id) pa_routing_history_id
3762 from ghr_pa_requests pr, ghr_pa_routing_history prh
3763 where pr.pa_request_id = prh.pa_request_id
3764 and person_id = p_person_id
3765 and (first_noa_code = p_first_noa_code or first_noa_code = '800')
3766 and effective_date = p_effective_date
3767 and nvl(pr.first_noa_cancel_or_correct,'X') <> ghr_history_api.g_cancel
3768 group by pr.pa_request_id;
3769
3770 cursor csr_eq_pay_plan is
3771 SELECT equivalent_pay_plan
3772 FROM ghr_pay_plans
3773 WHERE pay_plan = p_pay_plan;
3774
3775 cursor pa_hist_cur (p_r_hist_id number) is
3776 select nvl(action_taken,' ') action_taken
3777 from ghr_pa_routing_history
3778 where pa_routing_history_id = p_r_hist_id;
3779
3780 l_action_taken ghr_pa_routing_history.action_taken%TYPE;
3781 l_proc varchar2(72) := g_package || '.person_in_pa_req_1noa';
3782 l_eq_pay_plan ghr_pay_plans.equivalent_pay_plan%type;
3783
3784 begin
3785 g_proc := 'person_in_pa_req_1noa';
3786 hr_utility.set_location('Entering ' || l_proc,5);
3787 -- Bug 1631952 start
3788
3789 for csr_eq_pay_plan_rec in csr_eq_pay_plan loop
3790 l_eq_pay_plan := csr_eq_pay_plan_rec.equivalent_pay_plan;
3791 end loop;
3792
3793 if l_eq_pay_plan = 'FW' then
3794 for v_action_taken_fw in csr_action_taken_fw loop
3795 l_pa_request_id := v_action_taken_fw.pa_request_id;
3796 for v_name in csr_name
3797 loop
3798 l_name := v_name.fname;
3799 exit;
3800 end loop;
3801 for pa_hist_rec in pa_hist_cur (v_action_taken_fw.pa_routing_history_id)
3802 loop
3803 l_action_taken := pa_hist_rec.action_taken;
3804 exit;
3805 end loop;
3806 if l_action_taken <> 'CANCELED' then
3807 ghr_mto_int.log_message(
3808 p_procedure => 'RPA Exists Already',
3809 p_message => 'Name: '|| l_name || ' - Mass Table Change ' ||
3810 ' RPA Exists for the given FWS pay_lan and effective date' );
3811 return true;
3812 end if;
3813 end loop;
3814 else
3815 --- Bug 1631952 end. The same bug was extended to GS equvalent pay plans.
3816 for v_action_taken in csr_action_taken loop
3817 l_pa_request_id := v_action_taken.pa_request_id;
3818 for v_name in csr_name
3819 loop
3820 l_name := v_name.fname;
3821 exit;
3822 end loop;
3823 for pa_hist_rec in pa_hist_cur (v_action_taken.pa_routing_history_id)
3824 loop
3825 l_action_taken := pa_hist_rec.action_taken;
3826 exit;
3827 end loop;
3828 if l_action_taken <> 'CANCELED' then
3829 ghr_mto_int.log_message(
3830 p_procedure => 'RPA Exists Already',
3831 p_message => 'Name: '|| l_name || ' - Mass Table Change ' ||
3832 ' RPA Exists for the given effective date ' );
3833 return true;
3834 end if;
3835
3836 end loop;
3837 end if;
3838 return false;
3839 end person_in_pa_req_1noa_mtc;
3840
3841 --
3842 --
3843
3844 FUNCTION check_grade_retention(p_prd in varchar2
3845 ,p_person_id in number
3846 ,p_effective_date in date) return varchar2 is
3847
3848 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
3849 l_per_ei_data per_people_extra_info%rowtype;
3850
3851 l_proc varchar2(72) := g_package || '.check_grade_retention';
3852
3853 begin
3854 g_proc := 'check_grade_retention';
3855 hr_utility.set_location('Entering ' || l_proc,5);
3856 if p_prd in ('A','B','E','F','U','V') then
3857 if p_prd in ('A','B','E','F') then
3858 BEGIN
3859 l_retained_grade_rec :=
3860 ghr_pc_basic_pay.get_retained_grade_details
3861 ( p_person_id,
3862 p_effective_date);
3863 if l_retained_grade_rec.temp_step is not null then
3864 return 'REGULAR';
3865 end if;
3866 -- Bug 3315432 Need to write into process log if retained grade record is expired
3867 EXCEPTION
3868 WHEN GHR_PAY_CALC.PAY_CALC_MESSAGE THEN
3869 raise mlc_error;
3870 WHEN OTHERS THEN
3871 raise;
3872 END;
3873 end if;
3874 return 'RETAIN';
3875 ELSE
3876 return 'REGULAR';
3877 END IF;
3881 when others then
3878 exception
3879 when mlc_error then
3880 RETURN 'MLC_ERROR';
3882 RETURN 'OTHER_ERROR';
3883 END CHECK_GRADE_RETENTION;
3884
3885 --
3886 --
3887 --
3888
3889 procedure get_pos_grp1_ddf (p_position_id in per_assignments_f.position_id%type,
3890 p_effective_date in date,
3891 p_pos_ei_data out nocopy per_position_extra_info%rowtype)
3892 IS
3893
3894 l_proc varchar2(72) := g_package || '.get_pos_grp1_ddf';
3895 --l_pos_ei_data per_position_extra_info%type;
3896
3897 begin
3898 g_proc := 'get_pos_grp1_ddf';
3899 hr_utility.set_location('Entering ' || l_proc,5);
3900 ghr_history_fetch.fetch_positionei
3901 (p_position_id => p_position_id
3902 ,p_information_type => 'GHR_US_POS_GRP1'
3903 ,p_date_effective => p_effective_date
3904 ,p_pos_ei_data => p_pos_ei_data
3905 );
3906 hr_utility.set_location('Exiting ' || l_proc,10);
3907 exception
3908 when mlc_error then raise;
3909 when others then
3910 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
3911 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
3912 raise mlc_error;
3913 END get_pos_grp1_ddf;
3914
3915 --
3916 --
3917 --
3918
3919 procedure get_from_sf52_data_elements (p_assignment_id in number,
3920 p_effective_date in date,
3921 p_old_basic_pay out nocopy number,
3922 p_old_avail_pay out nocopy number,
3923 p_old_loc_diff out nocopy number,
3924 p_tot_old_sal out nocopy number,
3925 p_old_auo_pay out nocopy number,
3926 p_old_adj_basic_pay out nocopy number,
3927 p_other_pay out nocopy number,
3928 p_auo_premium_pay_indicator out nocopy varchar2,
3929 p_ap_premium_pay_indicator out nocopy varchar2,
3930 p_retention_allowance out nocopy number,
3931 p_retention_allow_perc out nocopy number,
3932 p_supervisory_differential out nocopy number,
3933 p_supervisory_diff_perc out nocopy number,
3934 p_staffing_differential out nocopy number) is
3935
3936 l_multi_error_flag boolean;
3937 l_total_salary number;
3938 l_basic_pay number;
3939 l_locality_adj number;
3940 l_adj_basic_pay number;
3941 l_other_pay number;
3942 l_au_overtime NUMBER;
3943 l_auo_premium_pay_indicator VARCHAR2(30);
3944 l_availability_pay NUMBER;
3945 l_ap_premium_pay_indicator VARCHAR2(30);
3946 l_retention_allowance NUMBER;
3947 l_retention_allow_perc NUMBER;
3948 l_supervisory_differential NUMBER;
3949 l_supervisory_diff_perc NUMBER;
3950 l_staffing_differential NUMBER;
3951 l_proc varchar2(72) := g_package || '.get_from_sf52_data_elements';
3952
3953 BEGIN
3954
3955 g_proc := 'get_from_sf52_data_elements';
3956 hr_utility.set_location('Entering ' || l_proc,5);
3957
3958 -- Processing Total Pay and Adjusted Basic Pay
3959 -- NAME DATE BUG COMMENTS
3960 -- Ashley 17-JUL-03 Payroll Intg Modified the Input Value name
3961 -- Changes from Total Salary -> Amount
3962 -- Adjusted Pay -> Amount
3963
3964 ghr_api.retrieve_element_entry_value
3965 (p_element_name => 'Total Pay'
3966 ,p_input_value_name => 'Amount'
3967 ,p_assignment_id => p_assignment_id
3968 ,p_effective_date => p_effective_date
3969 ,p_value => l_total_salary
3970 ,p_multiple_error_flag => l_multi_error_flag);
3971
3972 hr_utility.set_location('Total Pay = ' || to_char(l_total_salary), 6);
3973
3974 if l_multi_error_flag then
3975 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3976 hr_utility.raise_error;
3977 end if;
3978
3979 ghr_api.retrieve_element_entry_value
3980 (p_element_name => 'Basic Salary Rate'
3981 ,p_input_value_name => 'Rate'
3982 ,p_assignment_id => p_assignment_id
3983 ,p_effective_date => p_effective_date
3984 ,p_value => l_basic_pay
3985 ,p_multiple_error_flag => l_multi_error_flag);
3986
3987 hr_utility.set_location('Basic Salary Rate = ' || to_char(l_basic_pay), 6);
3988
3989 if l_multi_error_flag then
3990 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3991 hr_utility.raise_error;
3992 end if;
3993
3994 ghr_api.retrieve_element_entry_value
3995 (p_element_name => 'Locality Pay or SR Supplement'
3996 ,p_input_value_name => 'Rate'
3997 -- Changed by Ashu. 'Amount' to 'Rate'
3998 ,p_assignment_id => p_assignment_id
3999 ,p_effective_date => p_effective_date
4000 ,p_value => l_locality_adj
4001 ,p_multiple_error_flag => l_multi_error_flag);
4002
4006 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
4003 hr_utility.set_location('Locality Pay = ' || to_char(l_locality_adj), 6);
4004
4005 if l_multi_error_flag then
4007 hr_utility.raise_error;
4008 end if;
4009
4010 ghr_api.retrieve_element_entry_value
4011 (p_element_name => 'Adjusted Basic Pay'
4012 ,p_input_value_name => 'Amount'
4013 ,p_assignment_id => p_assignment_id
4014 ,p_effective_date => p_effective_date
4015 ,p_value => l_adj_basic_pay
4016 ,p_multiple_error_flag => l_multi_error_flag);
4017
4018 hr_utility.set_location('Adjusted Basic Pay = ' || to_char(l_adj_basic_pay), 6);
4019
4020 if l_multi_error_flag then
4021 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
4022 hr_utility.raise_error;
4023 end if;
4024
4025 ghr_api.retrieve_element_entry_value
4026 (p_element_name => 'Other Pay'
4027 ,p_input_value_name => 'Amount'
4028 ,p_assignment_id => p_assignment_id
4029 ,p_effective_date => p_effective_date
4030 ,p_value => l_other_pay
4031 ,p_multiple_error_flag => l_multi_error_flag);
4032
4033 hr_utility.set_location('Other Pay = ' || to_char(l_other_pay), 6);
4034
4035 if l_multi_error_flag then
4036 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
4037 hr_utility.raise_error;
4038 end if;
4039
4040 ghr_api.retrieve_element_entry_value
4041 (p_element_name => 'AUO'
4042 ,p_input_value_name => 'Amount'
4043 ,p_assignment_id => p_assignment_id
4044 ,p_effective_date => p_effective_date
4045 ,p_value => l_au_overtime
4046 ,p_multiple_error_flag => l_multi_error_flag);
4047
4048 hr_utility.set_location('AUO Amount = ' || to_char(l_au_overtime), 6);
4049
4050 if l_multi_error_flag then
4051 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
4052 hr_utility.raise_error;
4053 end if;
4054
4055 ghr_api.retrieve_element_entry_value
4056 (p_element_name => 'AUO'
4057 ,p_input_value_name => 'Premium Pay Ind'
4058 ,p_assignment_id => p_assignment_id
4059 ,p_effective_date => p_effective_date
4060 ,p_value => l_auo_premium_pay_indicator
4061 ,p_multiple_error_flag => l_multi_error_flag);
4062
4063 if l_multi_error_flag then
4064 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
4065 hr_utility.raise_error;
4066 end if;
4067
4068 ghr_api.retrieve_element_entry_value
4069 (p_element_name => 'Availability Pay'
4070 ,p_input_value_name => 'Amount'
4071 ,p_assignment_id => p_assignment_id
4072 ,p_effective_date => p_effective_date
4073 ,p_value => l_availability_pay
4074 ,p_multiple_error_flag => l_multi_error_flag);
4075
4076 hr_utility.set_location('Availability Pay Amount = ' || to_char(l_availability_pay), 6);
4077
4078 if l_multi_error_flag then
4079 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
4080 hr_utility.raise_error;
4081 end if;
4082
4083 ghr_api.retrieve_element_entry_value
4084 (p_element_name => 'Availability Pay'
4085 ,p_input_value_name => 'Premium Pay Ind'
4086 ,p_assignment_id => p_assignment_id
4087 ,p_effective_date => p_effective_date
4088 ,p_value => l_ap_premium_pay_indicator
4089 ,p_multiple_error_flag => l_multi_error_flag);
4090
4091 if l_multi_error_flag then
4092 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
4093 hr_utility.raise_error;
4094 end if;
4095 --
4096 ghr_api.retrieve_element_entry_value
4097 (p_element_name => 'Retention Allowance'
4098 ,p_input_value_name => 'Amount'
4099 ,p_assignment_id => p_assignment_id
4100 ,p_effective_date => p_effective_date
4101 ,p_value => l_retention_allowance
4102 ,p_multiple_error_flag => l_multi_error_flag);
4103
4104 hr_utility.set_location('Retention Allowance Amount = ' || to_char(l_retention_allowance), 6);
4105
4106 if l_multi_error_flag then
4107 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
4108 hr_utility.raise_error;
4109 end if;
4110
4111 ghr_api.retrieve_element_entry_value
4112 (p_element_name => 'Retention Allowance'
4113 ,p_input_value_name => 'Percentage'
4114 ,p_assignment_id => p_assignment_id
4115 ,p_effective_date => p_effective_date
4116 ,p_value => l_retention_allow_perc
4117 ,p_multiple_error_flag => l_multi_error_flag);
4118
4119 hr_utility.set_location('Retention Allowance Percent = ' || to_char(l_retention_allow_perc), 6);
4120
4121 if l_multi_error_flag then
4122 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
4123 hr_utility.raise_error;
4124 end if;
4125
4126 ghr_api.retrieve_element_entry_value
4127 (p_element_name => 'Supervisory Differential'
4128 ,p_input_value_name => 'Amount'
4129 ,p_assignment_id => p_assignment_id
4130 ,p_effective_date => p_effective_date
4131 ,p_value => l_supervisory_differential
4132 ,p_multiple_error_flag => l_multi_error_flag);
4133
4134 hr_utility.set_location('Supervisory Diff Amount = ' || to_char(l_supervisory_differential), 6);
4135
4136 if l_multi_error_flag then
4137 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
4138 hr_utility.raise_error;
4139 end if;
4140
4141 ghr_api.retrieve_element_entry_value
4142 (p_element_name => 'Supervisory Differential'
4143 ,p_input_value_name => 'Percentage'
4144 ,p_assignment_id => p_assignment_id
4145 ,p_effective_date => p_effective_date
4146 ,p_value => l_supervisory_diff_perc
4147 ,p_multiple_error_flag => l_multi_error_flag);
4148
4149 hr_utility.set_location('Supervisory Diff Percent = ' || to_char(l_supervisory_diff_perc), 6);
4150
4151 if l_multi_error_flag then
4152 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
4153 hr_utility.raise_error;
4154 end if;
4155
4156 --
4157 --
4158 ghr_api.retrieve_element_entry_value
4159 (p_element_name => 'Staffing Differential'
4160 ,p_input_value_name => 'Amount'
4161 ,p_assignment_id => p_assignment_id
4162 ,p_effective_date => p_effective_date
4163 ,p_value => l_staffing_differential
4164 ,p_multiple_error_flag => l_multi_error_flag);
4165
4166 hr_utility.set_location('Staffing Diff Amount = ' || to_char(l_staffing_differential), 6);
4167
4168 if l_multi_error_flag then
4169 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
4170 hr_utility.raise_error;
4171 end if;
4172
4173 p_tot_old_sal := round(l_total_salary,2);
4174 p_OLD_BASIC_PAY := round(l_basic_pay,2);
4175 p_OLD_LOC_DIFF := round(l_locality_adj,0);
4176 p_old_adj_basic_pay := round(l_adj_basic_pay,2);
4177 p_other_pay := l_other_pay;
4178 p_OLD_AUO_PAY := l_au_overtime;
4179 p_auo_premium_pay_indicator := l_auo_premium_pay_indicator;
4180 p_OLD_AVAIL_PAY := l_availability_pay;
4181 p_ap_premium_pay_indicator := l_ap_premium_pay_indicator;
4182 p_retention_allowance := l_retention_allowance;
4183 p_retention_allow_perc := l_retention_allow_perc;
4184 p_supervisory_differential := l_supervisory_differential;
4185 p_supervisory_diff_perc := l_supervisory_diff_perc;
4186 p_staffing_differential := l_staffing_differential;
4187
4188 hr_utility.set_location('Exiting ' || l_proc,10);
4189
4190 exception
4191 when mlc_error then raise;
4192 when others then
4193 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
4194 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
4195 raise mlc_error;
4196 END GET_FROM_SF52_DATA_ELEMENTS;
4197
4198 --
4199 --
4200 --
4201
4202 procedure get_sub_element_code_pos_title
4203 (p_position_id in per_assignments_f.position_id%type,
4204 p_person_id in number,
4208 p_sub_element_code out nocopy varchar2,
4205 p_business_group_id in per_assignments_f.business_group_id%type,
4206 p_assignment_id in per_assignments_f.assignment_id%type,
4207 p_effective_date in date,
4209 p_position_title out nocopy varchar2,
4210 p_position_number out nocopy varchar2,
4211 p_position_seq_no out nocopy varchar2) is
4212 l_proc varchar2(72) := g_package || '.get_sub_element_code_pos_title';
4213 begin
4214 g_proc := 'get_sub_element_code_pos_title';
4215 hr_utility.set_location('Entering ' || l_proc,5);
4216 p_sub_element_code := ghr_api.get_position_agency_code_pos
4217 (p_position_id,p_business_group_id,p_effective_date);
4218
4219 p_position_title := ghr_api.get_position_title_pos
4220 (p_position_id => p_position_id
4221 ,p_business_group_id => p_business_group_id
4222 ,p_effective_date => p_effective_date ) ;
4223
4224 -- if p_person_id is not null then
4225 p_position_number := ghr_api.get_position_description_no
4226 (p_person_id => p_person_id
4227 ,p_assignment_id => p_assignment_id
4228 ,p_effective_date => p_effective_date
4229 );
4230
4231 p_position_seq_no := ghr_api.get_position_sequence_no
4232 (p_person_id => p_person_id
4233 ,p_assignment_id => p_assignment_id
4234 ,p_effective_date => p_effective_date
4235 );
4236 -- end if;
4237
4238 hr_utility.set_location('Exiting ' || l_proc,10);
4239 end get_sub_element_code_pos_title;
4240
4241 --
4242 -- Get all details for the reporting...
4243 --
4244
4245 procedure get_other_dtls_for_rep(p_prd in varchar2,
4246 p_first_lac2_information1 in varchar2,
4247 p_first_lac2_information2 in varchar2,
4248 p_first_action_la_code1 out nocopy varchar2,
4249 p_first_action_la_code2 out nocopy varchar2,
4250 p_remark_code1 out nocopy varchar2,
4251 p_remark_code2 out nocopy varchar2
4252 ) is
4253 l_proc varchar2(72) := g_package || '.get_other_dtls_for_rep';
4254 BEGIN
4258 p_first_action_la_code2 := 'ZLM';
4255 g_proc := 'get_other_dtls_for_rep';
4256 hr_utility.set_location('Entering ' || l_proc,5);
4257 p_first_action_la_code1 := 'QWM';
4259
4260 /**
4261 If p_prd in ('A','B','E','F') then -- retained grade
4262 p_first_action_la_code1 := 'QWM';
4263 p_first_action_la_code2 := 'ZLM';
4264 p_remark_code1 := 'X44';
4265 Elsif p_prd in ('3','J','K') then -- retained pay
4266 p_first_action_la_code1 := 'QWM';
4267 p_first_action_la_code2 := 'ZLM';
4268 p_remark_code1 := 'X40';
4269 Elsif p_prd in ('U','V') then -- pay and grade
4270 p_first_action_la_code1 := 'QWM';
4271 p_first_action_la_code2 := 'ZLM';
4272 p_remark_code1 := 'X40';
4273 p_remark_code2 := 'X44';
4274 Else
4275 p_first_action_la_code1 := 'QWM';
4276 p_first_action_la_code2 := 'ZLM';
4277 End if;
4278 */
4279
4280 hr_utility.set_location('Exiting ' || l_proc,10);
4281
4282 exception
4283 when mlc_error then raise;
4284 when others then
4285 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
4286 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
4287 raise mlc_error;
4288 END get_other_dtls_for_rep;
4289
4290 --
4291 --
4292 --
4293
4294 procedure create_mass_act_prev (
4295 p_effective_date in date,
4296 p_date_of_birth in date,
4297 p_full_name in varchar2,
4298 p_national_identifier in varchar2,
4299 --Bug # 9329643
4300 p_employee_number in varchar2,
4301 p_duty_station_code in varchar2,
4302 p_duty_station_desc in varchar2,
4303 p_personnel_office_id in varchar2,
4304 p_basic_pay in number,
4305 p_new_basic_pay in number,
4306 -- Bug#2383992
4307 p_adj_basic_pay in number,
4308 p_new_adj_basic_pay in number,
4309 -- Bug#2383992
4310 p_old_avail_pay in number,
4311 p_new_avail_pay in number,
4312 p_old_loc_diff in number,
4313 p_new_loc_diff in number,
4314 p_tot_old_sal in number,
4315 p_tot_new_sal in number,
4316 p_old_auo_pay in number,
4317 p_new_auo_pay in number,
4318 p_position_id in per_assignments_f.position_id%type,
4319 p_position_title in varchar2,
4320 -- FWFA Changes Bug#4444609
4321 p_position_number in varchar2,
4322 p_position_seq_no in varchar2,
4323 -- FWFA Changes
4324 p_org_structure_id in varchar2,
4325 p_agency_sub_element_code in varchar2,
4326 p_person_id in number,
4327 p_mass_salary_id in number,
4328 p_sel_flg in varchar2,
4329 p_first_action_la_code1 in varchar2,
4330 p_first_action_la_code2 in varchar2,
4331 p_remark_code1 in varchar2,
4332 p_remark_code2 in varchar2,
4333 p_grade_or_level in varchar2,
4334 p_step_or_rate in varchar2,
4335 p_pay_plan in varchar2,
4336 p_pay_rate_determinant in varchar2,
4337 p_tenure in varchar2,
4338 p_action in varchar2,
4339 p_assignment_id in number,
4340 p_old_other_pay in number,
4341 p_new_other_pay in number,
4342 -- Bug#2383992
4343 p_old_capped_other_pay in number,
4344 p_new_capped_other_pay in number,
4345 p_old_retention_allowance in number,
4346 p_new_retention_allowance in number,
4347 p_old_supervisory_differential in number,
4348 p_new_supervisory_differential in number,
4349 p_organization_name in varchar2,
4350 -- Bug#2383992
4351 -- FWFA ChangesBug#4444609
4352 p_input_pay_rate_determinant in varchar2,
4353 p_from_pay_table_id in number,
4354 p_to_pay_table_id in number
4355 -- FWFA Changes
4356 )
4357 is
4358
4359 l_comb_rem varchar2(30);
4360 l_proc varchar2(72) := g_package || '.create_mass_act_prev';
4361
4362 l_cust_rec ghr_mass_act_custom.ghr_mass_custom_out_rec_type;
4363 l_cust_in_rec ghr_mass_act_custom.ghr_mass_custom_in_rec_type;
4364 l_poi_desc varchar2(80);
4365 ----Temp Promo Changes.
4366 l_step_or_rate varchar2(30);
4367 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
4368 l_check_grade_retention varchar2(30);
4369 begin
4370 g_proc := 'create_mass_act_prev';
4371 hr_utility.set_location('Entering ' || l_proc,5);
4372 if p_remark_code2 is not null then
4373 l_comb_rem := p_remark_code1||', '||p_remark_code2;
4374 else
4375 l_comb_rem := p_remark_code1;
4376 end if;
4377
4378 l_poi_desc := GHR_MRE_PKG.GET_POI_NAME (p_personnel_office_id);
4379
4380 BEGIN
4381 l_cust_in_rec.person_id := p_person_id;
4382 l_cust_in_rec.position_id := p_position_id;
4383 l_cust_in_rec.assignment_id := p_assignment_id;
4384 l_cust_in_rec.national_identifier := p_national_identifier;
4385 l_cust_in_rec.mass_action_type := 'SALARY';
4386 l_cust_in_rec.mass_action_id := p_mass_salary_id;
4387 l_cust_in_rec.effective_date := p_effective_date;
4388
4389 GHR_MASS_ACT_CUSTOM.pre_insert (
4390 p_cust_in_rec => l_cust_in_rec,
4391 p_cust_rec => l_cust_rec);
4392
4393 exception
4394 when others then
4395 hr_utility.set_location('Error in Mass Act Custom '||
4396 'Err is '||sqlerrm(sqlcode),20);
4397 l_mlcerrbuf := 'Error in Mass Act Custom '||
4398 'Err is '|| sqlerrm(sqlcode);
4399 raise mlc_error;
4400 END;
4401
4402 l_check_grade_retention := check_grade_retention(p_pay_rate_determinant,p_person_id,p_effective_date);
4403
4404 l_step_or_rate := p_step_or_rate;
4405
4406 IF p_pay_rate_determinant in ('A','B','E','F') THEN
4407 IF l_check_grade_retention = 'REGULAR' THEN
4408 begin
4409 l_retained_grade_rec :=
4410 ghr_pc_basic_pay.get_retained_grade_details
4411 ( p_person_id,
4412 p_effective_date);
4413 if l_retained_grade_rec.temp_step is not null then
4414 l_step_or_rate := l_retained_grade_rec.temp_step;
4415 end if;
4416 exception
4417 when ghr_pay_calc.pay_calc_message THEN
4418 l_mlcerrbuf := ' Retained Grade record is invalid for this Employee ';
4419 raise mlc_error;
4420 when others then
4421 l_mlcerrbuf := 'Preview - Others error in Get retained grade '||
4422 'SQL Error is '|| sqlerrm(sqlcode);
4423 raise mlc_error;
4424 end;
4425
4426 ELSIF l_check_grade_retention ='MLC_ERROR' THEN
4427 hr_utility.set_message(8301,'GHR_38927_MISSING_MA_RET_DET');
4428 raise mlc_error;
4429 ELSIF l_check_grade_retention = 'OTHER_ERROR' THEN
4430 l_mlcerrbuf := 'Others error in check_grade_retention function while fetching retained grade record. Please
4431 verify the retained grade record';
4432 raise mlc_error;
4433 END IF;
4434 END IF;
4435
4436 insert into GHR_MASS_ACTIONS_PREVIEW
4437 (
4438 mass_action_type,
4439 --report_type,
4440 ui_type,
4441 session_id,
4442 effective_date,
4443 employee_date_of_birth,
4444 full_name,
4445 national_identifier,
4446 --Bug # 9329643
4447 employee_number,
4448 duty_station_code,
4449 duty_station_desc,
4450 personnel_office_id,
4451 from_basic_pay,
4452 to_basic_pay,
4453 -- Bug#2383992
4454 from_adj_basic_pay ,
4455 to_adj_basic_pay ,
4456 -- Bug#2383992
4457 from_availability_pay,
4458 to_availability_pay,
4459 from_locality_adj,
4460 to_locality_adj,
4461 from_total_salary,
4462 to_total_salary,
4463 from_auo_pay,
4464 to_auo_pay,
4465 from_other_pay,
4466 to_other_pay,
4467 -- Bug#2383992
4468 from_capped_other_pay,
4469 to_capped_other_pay,
4470 from_retention_allowance,
4471 to_retention_allowance,
4472 from_supervisory_differential ,
4473 to_supervisory_differential ,
4474 -- Bug#2383992
4475 position_id,
4476 position_title,
4477 -- FWFA Changes Bug#4444609
4478 position_number,
4479 position_seq_no,
4480 -- FWFA Changes
4481 org_structure_id,
4482 agency_code,
4483 person_id,
4484 select_flag,
4485 first_noa_code,
4486 first_action_la_code1,
4487 first_action_la_code2,
4488 grade_or_level,
4489 step_or_rate,
4490 pay_plan,
4491 pay_rate_determinant,
4492 tenure,
4493 POI_DESC,
4494 organization_name,
4495 -- FWFA Changes Bug#4444609
4496 input_pay_rate_determinant,
4497 from_pay_table_identifier,
4498 to_pay_table_identifier,
4499 -- FWFA Changes
4500 USER_ATTRIBUTE1,
4501 USER_ATTRIBUTE2,
4502 USER_ATTRIBUTE3,
4503 USER_ATTRIBUTE4,
4504 USER_ATTRIBUTE5,
4505 USER_ATTRIBUTE6,
4506 USER_ATTRIBUTE7,
4507 USER_ATTRIBUTE8,
4508 USER_ATTRIBUTE9,
4509 USER_ATTRIBUTE10,
4510 USER_ATTRIBUTE11,
4511 USER_ATTRIBUTE12,
4512 USER_ATTRIBUTE13,
4513 USER_ATTRIBUTE14,
4514 USER_ATTRIBUTE15,
4515 USER_ATTRIBUTE16,
4516 USER_ATTRIBUTE17,
4517 USER_ATTRIBUTE18,
4518 USER_ATTRIBUTE19,
4519 USER_ATTRIBUTE20
4520 )
4521 values
4522 (
4523 'SALARY',
4524 /*--decode(p_action,'REPORT',userenv('SESSIONID'),p_mass_realignment_id),*/
4525 decode(p_action,'SHOW','FORM','REPORT'),
4526 userenv('SESSIONID'),
4527 p_effective_date,
4528 p_date_of_birth,
4529 p_full_name,
4530 p_national_identifier,
4531 --Bug # 9329643
4532 p_employee_number,
4533 p_duty_station_code,
4534 p_duty_station_desc,
4535 p_personnel_office_id,
4536 p_basic_pay,
4537 p_new_basic_pay,
4538 -- Bug#2383992
4539 p_adj_basic_pay ,
4540 p_new_adj_basic_pay ,
4541 -- Bug#2383992
4542 p_old_avail_pay,
4543 p_new_avail_pay,
4544 p_old_loc_diff,
4545 p_new_loc_diff,
4546 p_tot_old_sal,
4547 p_tot_new_sal,
4548 p_old_auo_pay,
4549 p_new_auo_pay,
4550 p_old_other_pay, ----------- nvl(p_old_auo_pay,0)+ nvl(p_old_avail_pay,0),
4551 p_new_other_pay, ----------- nvl(p_new_auo_pay,0)+ nvl(p_new_avail_pay,0),
4552 -- Bug#2383992
4553 p_old_capped_other_pay,
4554 p_new_capped_other_pay,
4555 p_old_retention_allowance,
4556 p_new_retention_allowance,
4557 p_old_supervisory_differential ,
4558 p_new_supervisory_differential ,
4559 -- Bug#2383992
4560 p_position_id,
4561 p_position_title,
4562 -- FWFA Changes Bug#4444609
4563 p_position_number,
4564 p_position_seq_no,
4565 -- FWFA Changes
4566 p_org_structure_id,
4567 p_agency_sub_element_code,
4568 p_person_id,
4569 p_sel_flg,
4570 nvl(ghr_msl_pkg.g_first_noa_code,'895'),
4571 p_first_action_la_code1,
4572 p_first_action_la_code2,
4573 p_grade_or_level,
4574 l_step_or_rate,
4575 p_pay_plan,
4576 p_pay_rate_determinant,
4577 p_tenure,
4578 l_poi_desc,
4579 p_organization_name,
4580 -- FWFA Changes Bug#4444609
4581 p_input_pay_rate_determinant,
4582 p_from_pay_table_id,
4583 p_to_pay_table_id,
4584 -- FWFA Changes
4585 l_cust_rec.user_attribute1,
4586 l_cust_rec.user_attribute2,
4587 l_cust_rec.user_attribute3,
4591 l_cust_rec.user_attribute7,
4588 l_cust_rec.user_attribute4,
4589 l_cust_rec.user_attribute5,
4590 l_cust_rec.user_attribute6,
4592 l_cust_rec.user_attribute8,
4593 l_cust_rec.user_attribute9,
4594 l_cust_rec.user_attribute10,
4595 l_cust_rec.user_attribute11,
4596 l_cust_rec.user_attribute12,
4597 l_cust_rec.user_attribute13,
4598 l_cust_rec.user_attribute14,
4599 l_cust_rec.user_attribute15,
4600 l_cust_rec.user_attribute16,
4601 l_cust_rec.user_attribute17,
4602 l_cust_rec.user_attribute18,
4603 l_cust_rec.user_attribute19,
4604 l_cust_rec.user_attribute20
4605 );
4606
4607 hr_utility.set_location('Exiting ' || l_proc,10);
4608 exception
4609 when mlc_error then raise;
4610 when others then
4611 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
4612 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
4613 raise mlc_error;
4614 end create_mass_act_prev;
4615
4616 --
4617
4618
4619 procedure create_mass_act_prev_mtc (
4620 p_effective_date in date,
4621 p_date_of_birth in date,
4622 p_full_name in varchar2,
4623 p_national_identifier in varchar2,
4624 --Bug # 9329643
4625 p_employee_number in varchar2,
4626 p_duty_station_code in varchar2,
4627 p_duty_station_desc in varchar2,
4628 p_personnel_office_id in varchar2,
4629 p_basic_pay in number,
4630 p_new_basic_pay in number,
4631 p_adj_basic_pay in number,
4632 p_new_adj_basic_pay in number,
4633 p_old_avail_pay in number,
4634 p_new_avail_pay in number,
4635 p_old_loc_diff in number,
4636 p_new_loc_diff in number,
4637 p_tot_old_sal in number,
4638 p_tot_new_sal in number,
4639 p_old_auo_pay in number,
4640 p_new_auo_pay in number,
4641 p_position_id in per_assignments_f.position_id%type,
4642 p_position_title in varchar2,
4643 -- FWFA Changes Bug#4444609
4644 p_position_number in varchar2,
4645 p_position_seq_no in varchar2,
4646 -- FWFA Changes
4647 p_org_structure_id in varchar2,
4648 p_agency_sub_element_code in varchar2,
4649 p_person_id in number,
4650 p_mass_salary_id in number,
4651 p_sel_flg in varchar2,
4652 p_first_action_la_code1 in varchar2,
4653 p_first_action_la_code2 in varchar2,
4654 p_remark_code1 in varchar2,
4655 p_remark_code2 in varchar2,
4656 p_grade_or_level in varchar2,
4657 p_step_or_rate in varchar2,
4658 p_pay_plan in varchar2,
4659 p_pay_rate_determinant in varchar2,
4660 p_tenure in varchar2,
4661 p_action in varchar2,
4662 p_assignment_id in number,
4663 p_old_other_pay in number,
4664 p_new_other_pay in number,
4665 p_old_capped_other_pay in number,
4666 p_new_capped_other_pay in number,
4667 p_old_retention_allowance in number,
4668 p_new_retention_allowance in number,
4669 p_old_supervisory_differential in number,
4670 p_new_supervisory_differential in number,
4671 p_organization_name in varchar2,
4672 -- FWFA Changes Bug#4444609
4673 p_input_pay_rate_determinant in varchar2,
4674 p_from_pay_table_id in number,
4675 p_to_pay_table_id in number
4676 -- FWFA Changes
4677 )
4678 is
4679
4680 l_comb_rem varchar2(30);
4681 l_proc varchar2(72) := g_package || '.create_mass_act_prev';
4682
4683 l_cust_rec ghr_mass_act_custom.ghr_mass_custom_out_rec_type;
4684 l_cust_in_rec ghr_mass_act_custom.ghr_mass_custom_in_rec_type;
4685 l_poi_desc varchar2(80);
4686 ----Temp Promo Changes.
4687 l_step_or_rate varchar2(30);
4688 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
4689 l_check_grade_retention varchar2(30);
4690 begin
4691 g_proc := 'create_mass_act_prev';
4692 hr_utility.set_location('Entering ' || l_proc,5);
4693 IF p_person_id is not null then
4694 if p_remark_code2 is not null then
4695 l_comb_rem := p_remark_code1||', '||p_remark_code2;
4696 else
4697 l_comb_rem := p_remark_code1;
4698 end if;
4699
4700 l_poi_desc := GHR_MRE_PKG.GET_POI_NAME (p_personnel_office_id);
4701
4702 BEGIN
4703 l_cust_in_rec.person_id := p_person_id;
4704 l_cust_in_rec.position_id := p_position_id;
4705 l_cust_in_rec.assignment_id := p_assignment_id;
4706 l_cust_in_rec.national_identifier := p_national_identifier;
4707 l_cust_in_rec.mass_action_type := 'SALARY';
4708 l_cust_in_rec.mass_action_id := p_mass_salary_id;
4709 l_cust_in_rec.effective_date := p_effective_date;
4710
4711 GHR_MASS_ACT_CUSTOM.pre_insert (
4712 p_cust_in_rec => l_cust_in_rec,
4713 p_cust_rec => l_cust_rec);
4714
4715 exception
4716 when others then
4717 hr_utility.set_location('Error in Mass Act Custom '||
4718 'Err is '||sqlerrm(sqlcode),20);
4719 l_mlcerrbuf := 'Error in Mass Act Custom '||
4720 'Err is '|| sqlerrm(sqlcode);
4721 raise mlc_error;
4722 END;
4723
4724 l_check_grade_retention := check_grade_retention(p_pay_rate_determinant,p_person_id,p_effective_date);
4725
4726 l_step_or_rate := p_step_or_rate;
4727
4728 IF p_pay_rate_determinant in ('A','B','E','F') THEN
4729 IF l_check_grade_retention = 'REGULAR' THEN
4730 begin
4731 l_retained_grade_rec :=
4732 ghr_pc_basic_pay.get_retained_grade_details
4733 ( p_person_id,
4734 p_effective_date);
4735 if l_retained_grade_rec.temp_step is not null then
4736 l_step_or_rate := l_retained_grade_rec.temp_step;
4737 end if;
4738 exception
4739 when ghr_pay_calc.pay_calc_message THEN
4743 l_mlcerrbuf := 'Preview - Others error in Get retained grade '||
4740 l_mlcerrbuf := ' Retained Grade record is invalid for this Employee ';
4741 raise mlc_error;
4742 when others then
4744 'SQL Error is '|| sqlerrm(sqlcode);
4745 raise mlc_error;
4746 end;
4747
4748 ELSIF l_check_grade_retention ='MLC_ERROR' THEN
4749 hr_utility.set_message(8301,'GHR_38927_MISSING_MA_RET_DET');
4750 raise mlc_error;
4751 ELSIF l_check_grade_retention = 'OTHER_ERROR' THEN
4752 l_mlcerrbuf := 'Others error in check_grade_retention function while fetching retained grade record. Please
4753 verify the retained grade record';
4754 raise mlc_error;
4755 END IF;
4756 END IF;
4757 END IF;
4758 insert into GHR_MASS_ACTIONS_PREVIEW
4759 (
4760 mass_action_type,
4761 --report_type,
4762 ui_type,
4763 session_id,
4764 effective_date,
4765 employee_date_of_birth,
4766 full_name,
4767 national_identifier,
4768 --Bug # 9329643
4769 employee_number,
4770 duty_station_code,
4771 duty_station_desc,
4772 personnel_office_id,
4773 from_basic_pay,
4774 to_basic_pay,
4775 -- Bug#2383992
4776 from_adj_basic_pay ,
4777 to_adj_basic_pay ,
4778 -- Bug#2383992
4779 from_availability_pay,
4780 to_availability_pay,
4781 from_locality_adj,
4782 to_locality_adj,
4783 from_total_salary,
4784 to_total_salary,
4785 from_auo_pay,
4786 to_auo_pay,
4787 from_other_pay,
4788 to_other_pay,
4789 -- Bug#2383992
4790 from_capped_other_pay,
4791 to_capped_other_pay,
4792 from_retention_allowance,
4793 to_retention_allowance,
4794 from_supervisory_differential ,
4795 to_supervisory_differential ,
4796 -- Bug#2383992
4797 position_id,
4798 position_title,
4799 -- FWFA Changes Bug#4444609
4800 position_number,
4801 position_seq_no,
4802 -- FWFA Changes
4803 org_structure_id,
4804 agency_code,
4805 person_id,
4806 select_flag,
4807 first_noa_code,
4808 first_action_la_code1,
4809 first_action_la_code2,
4810 grade_or_level,
4811 step_or_rate,
4812 pay_plan,
4813 pay_rate_determinant,
4814 tenure,
4815 POI_DESC,
4816 organization_name,
4817 -- FWFA Changes Bug#4444609
4818 input_pay_rate_determinant,
4819 from_pay_table_identifier,
4820 to_pay_table_identifier,
4821 -- FWFA Changes
4822 USER_ATTRIBUTE1,
4823 USER_ATTRIBUTE2,
4824 USER_ATTRIBUTE3,
4825 USER_ATTRIBUTE4,
4826 USER_ATTRIBUTE5,
4827 USER_ATTRIBUTE6,
4828 USER_ATTRIBUTE7,
4829 USER_ATTRIBUTE8,
4830 USER_ATTRIBUTE9,
4831 USER_ATTRIBUTE10,
4832 USER_ATTRIBUTE11,
4833 USER_ATTRIBUTE12,
4834 USER_ATTRIBUTE13,
4835 USER_ATTRIBUTE14,
4836 USER_ATTRIBUTE15,
4837 USER_ATTRIBUTE16,
4838 USER_ATTRIBUTE17,
4839 USER_ATTRIBUTE18,
4840 USER_ATTRIBUTE19,
4841 USER_ATTRIBUTE20
4842 )
4843 values
4844 (
4845 'SALARY',
4846 /*--decode(p_action,'REPORT',userenv('SESSIONID'),p_mass_realignment_id),*/
4847 decode(p_action,'SHOW','FORM','REPORT'),
4848 userenv('SESSIONID'),
4849 p_effective_date,
4850 p_date_of_birth,
4851 p_full_name,
4852 p_national_identifier,
4853 --Bug # 9329643
4854 p_employee_number,
4855 p_duty_station_code,
4856 p_duty_station_desc,
4857 p_personnel_office_id,
4858 p_basic_pay,
4859 p_new_basic_pay,
4860 p_adj_basic_pay ,
4861 p_new_adj_basic_pay ,
4862 p_old_avail_pay,
4863 p_new_avail_pay,
4864 p_old_loc_diff,
4865 p_new_loc_diff,
4866 p_tot_old_sal,
4867 p_tot_new_sal,
4868 p_old_auo_pay,
4869 p_new_auo_pay,
4870 p_old_other_pay,
4871 p_new_other_pay,
4872 p_old_capped_other_pay,
4873 p_new_capped_other_pay,
4874 p_old_retention_allowance,
4875 p_new_retention_allowance,
4876 p_old_supervisory_differential ,
4877 p_new_supervisory_differential ,
4878 p_position_id,
4879 p_position_title,
4880 -- FWFA Changes Bug#4444609
4881 p_position_number,
4882 p_position_seq_no,
4883 -- FWFA Changes
4884 p_org_structure_id,
4885 p_agency_sub_element_code,
4886 p_person_id,
4887 p_sel_flg,
4888 decode(p_basic_pay,p_new_basic_pay,'800','894'),
4889 p_first_action_la_code1,
4890 p_first_action_la_code2,
4891 p_grade_or_level,
4892 l_step_or_rate,
4893 p_pay_plan,
4894 p_pay_rate_determinant,
4895 p_tenure,
4896 l_poi_desc,
4897 p_organization_name,
4898 -- FWFA Changes Bug#4444609
4899 p_input_pay_rate_determinant,
4900 p_from_pay_table_id,
4901 p_to_pay_table_id,
4902 -- FWFA Changes
4903 l_cust_rec.user_attribute1,
4904 l_cust_rec.user_attribute2,
4905 l_cust_rec.user_attribute3,
4906 l_cust_rec.user_attribute4,
4907 l_cust_rec.user_attribute5,
4908 l_cust_rec.user_attribute6,
4909 l_cust_rec.user_attribute7,
4910 l_cust_rec.user_attribute8,
4911 l_cust_rec.user_attribute9,
4912 l_cust_rec.user_attribute10,
4913 l_cust_rec.user_attribute11,
4914 l_cust_rec.user_attribute12,
4915 l_cust_rec.user_attribute13,
4916 l_cust_rec.user_attribute14,
4917 l_cust_rec.user_attribute15,
4918 l_cust_rec.user_attribute16,
4919 l_cust_rec.user_attribute17,
4920 l_cust_rec.user_attribute18,
4921 l_cust_rec.user_attribute19,
4922 l_cust_rec.user_attribute20
4923 );
4924
4925 hr_utility.set_location('Exiting ' || l_proc,10);
4926 exception
4927 when mlc_error then raise;
4928 when others then
4932 end create_mass_act_prev_mtc;
4929 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
4930 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
4931 raise mlc_error;
4933
4934 --
4935 --
4936
4937 procedure get_lac_dtls
4938 (p_pa_request_id in number,
4939 p_sf52_rec out nocopy ghr_pa_requests%rowtype) IS
4940
4941 l_proc varchar2(72) := g_package || '.get_lac_dtls';
4942
4943 cursor cur_pa_req_cur is
4944 select * from ghr_pa_requests
4945 where pa_request_id = p_pa_request_id;
4946
4947 begin
4948 g_proc := 'get_lac_dtls';
4949 hr_utility.set_location('Entering ' || l_proc,5);
4950
4951 pr('Entering '||l_proc||' Pa req id ',to_char(p_pa_request_id));
4952 if p_pa_request_id is null then
4953 pr('PA request id is null.................');
4954 else
4955
4956 for cur_pa_rec in cur_pa_req_cur
4957 loop
4958 p_sf52_rec := cur_pa_rec;
4959 exit;
4960 end loop;
4961 end if;
4962
4963 hr_utility.set_location('Exiting ' || l_proc,10);
4964 exception
4965 when others then
4966 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
4967 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
4968 raise mlc_error;
4969 end get_lac_dtls;
4970
4971 --
4972 --
4973 --
4974
4975 procedure create_lac_remarks
4976 (p_pa_request_id in number,
4977 p_new_pa_request_id in number) is
4978
4979 l_proc varchar2(72) := g_package || '.create_lac_remarks';
4980
4981 cursor cur_pa_rem_cur is
4982 select * from ghr_pa_remarks
4983 where pa_request_id = p_pa_request_id;
4984
4985 l_remarks_rec ghr_pa_remarks%rowtype;
4986
4987 begin
4988 g_proc := 'create_lac_remarks';
4989 hr_utility.set_location('Entering ' || l_proc,5);
4990
4991 pr('Inside '||l_proc,to_char(p_pa_request_id),to_char(p_new_pa_request_id));
4992
4993 FOR CUR_PA_REM_rec in cur_pa_rem_cur
4994 loop
4995
4996 l_remarks_rec := cur_pa_rem_rec;
4997
4998 pr('Rem id '||to_char(l_remarks_rec.remark_id));
4999 ghr_pa_remarks_api.create_pa_remarks
5000 (p_validate => false
5001 ,p_pa_request_id => p_new_pa_request_id
5002 ,p_remark_id => l_remarks_rec.remark_id
5003 ,p_description => l_remarks_rec.description
5004 ,p_remark_code_information1 => l_remarks_rec.remark_code_information1
5005 ,p_remark_code_information2 => l_remarks_rec.remark_code_information2
5006 ,p_remark_code_information3 => l_remarks_rec.remark_code_information3
5007 ,p_remark_code_information4 => l_remarks_rec.remark_code_information4
5008 ,p_remark_code_information5 => l_remarks_rec.remark_code_information5
5009 ,p_pa_remark_id => l_remarks_rec.pa_remark_id
5010 ,p_object_version_number => l_remarks_rec.object_version_number
5011 );
5012
5013 end loop;
5014
5015 hr_utility.set_location('Exiting ' || l_proc,10);
5016
5017 exception
5018 when others then
5019 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
5020 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
5021 raise mlc_error;
5022 end create_lac_remarks;
5023
5024 --
5025 --
5026
5027 procedure upd_ext_info_to_null(p_position_id in NUMBER, p_effective_DATE in DATE) is
5028
5029 CURSOR POSITION_EXT_CUR (p_position NUMBER) IS
5030 SELECT position_extra_info_id, object_version_NUMBER
5031 from per_position_extra_info
5032 WHERE position_id = (p_position)
5033 and INFORMATION_TYPE = 'GHR_US_POS_MASS_ACTIONS';
5034
5035 l_Position_EXTRA_INFO_ID NUMBER;
5036 l_OBJECT_VERSION_NUMBER NUMBER;
5037 l_eff_DATE DATE;
5038
5039 l_pos_ei_data per_position_extra_info%rowtype;
5040 l_proc VARCHAR2(72) := g_package || '.upd_ext_info_api';
5041 BEGIN
5042
5043 g_proc := 'upd_ext_info_to_null';
5044
5045 if p_effective_DATE > sysDATE then
5046 l_eff_DATE := sysDATE;
5047 ELSE
5048 l_eff_DATE := p_effective_DATE;
5049 END IF;
5050
5051 ghr_history_fetch.fetch_positionei
5052 (p_position_id => p_position_id
5053 ,p_information_type => 'GHR_US_POS_MASS_ACTIONS'
5054 ,p_DATE_effective => l_eff_DATE
5055 ,p_pos_ei_data => l_pos_ei_data);
5056
5057 l_position_extra_info_id := l_pos_ei_data.position_extra_info_id;
5058 l_object_version_NUMBER := l_pos_ei_data.object_version_NUMBER;
5059
5060 if l_position_extra_info_id is not null then
5061
5062 ----- Set the global variable not to fire the trigger
5063 ghr_api.g_api_dml := TRUE;
5064
5065 BEGIN
5066
5067 ghr_position_extra_info_api.UPDATE_position_extra_info
5068 (P_POSITION_EXTRA_INFO_ID => l_position_extra_info_id
5069 ,P_OBJECT_VERSION_NUMBER => l_object_version_NUMBER
5070 ,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS'
5071 ,P_EFFECTIVE_DATE => l_eff_DATE
5072 ,P_POEI_INFORMATION19 => null
5073 ,P_POEI_INFORMATION20 => null
5074 ,P_POEI_INFORMATION21 => null);
5075
5076 EXCEPTION when others then
5077 hr_utility.set_location('UPDATE posei error 3' || l_proc,10);
5078 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
5082 ghr_api.g_api_dml := FALSE;
5079 END;
5080
5081 ----- Reset the global variable
5083
5084 END IF;
5085 END;
5086 --
5087 --
5088 --
5089 PROCEDURE assign_to_sf52_rec(
5090 p_person_id in number,
5091 p_first_name in varchar2,
5092 p_last_name in varchar2,
5093 p_middle_names in varchar2,
5094 p_national_identifier in varchar2,
5095 p_date_of_birth in date,
5096 p_effective_date in date,
5097 p_assignment_id in number,
5098 p_tenure in varchar2,
5099 p_step_or_rate in varchar2,
5100 p_annuitant_indicator in varchar2,
5101 p_pay_rate_determinant in varchar2,
5102 p_work_schedule in varchar2,
5103 p_part_time_hour in varchar2,
5104 p_flsa_category in varchar2,
5105 p_bargaining_unit_status in varchar2,
5106 p_functional_class in varchar2,
5107 p_supervisory_status in varchar2,
5108 p_basic_pay in number,
5109 p_to_locality_adj in number,
5110 p_to_adj_basic_pay in number,
5111 p_to_total_salary in number,
5112 p_from_other_pay_amount in number,
5113 p_to_other_pay_amount in number,
5114 p_to_au_overtime in number,
5115 p_to_availability_pay in number,
5116 p_to_retention_allowance in number,
5117 p_to_retention_allow_perce in number,
5118 p_to_supervisory_differential in number,
5119 p_to_supervisory_diff_perce in number,
5120 p_to_staffing_differential in number,
5121 p_duty_station_id in number,
5122 p_duty_station_code in ghr_pa_requests.duty_station_code%type,
5123 p_duty_station_desc in ghr_pa_requests.duty_station_desc%type,
5124 -- FWFA Changes Bug#4444609
5125 p_input_pay_rate_determinant in ghr_pa_requests.input_pay_rate_determinant%type,
5126 p_from_pay_table_id in ghr_pa_requests.from_pay_table_identifier%type,
5127 p_to_pay_table_id in ghr_pa_requests.to_pay_table_identifier%type,
5128 -- FWFA Changes
5129 p_lac_sf52_rec in ghr_pa_requests%rowtype,
5130 p_sf52_rec out nocopy ghr_pa_requests%rowtype) IS
5131
5132 l_proc varchar2(72) := g_package || '.assign_to_sf52_rec';
5133 begin
5134
5135 g_proc := 'assign_to_sf52_rec';
5136
5137 hr_utility.set_location('Entering ' || l_proc,5);
5138 p_sf52_rec.person_id := p_person_id;
5139 p_sf52_rec.employee_first_name := p_first_name;
5140 p_sf52_rec.employee_last_name := p_last_name;
5141 p_sf52_rec.employee_middle_names := p_middle_names;
5142 p_sf52_rec.employee_national_identifier := p_national_identifier;
5143 p_sf52_rec.employee_date_of_birth := p_date_of_birth;
5144 p_sf52_rec.effective_date := p_effective_date;
5145 p_sf52_rec.employee_assignment_id := p_assignment_id;
5146 p_sf52_rec.tenure := p_tenure;
5147 p_sf52_rec.to_step_or_rate := p_step_or_rate;
5148 p_sf52_rec.annuitant_indicator := p_annuitant_indicator;
5149 p_sf52_rec.pay_rate_determinant := p_pay_rate_determinant;
5150 p_sf52_rec.work_schedule := p_work_schedule;
5151 p_sf52_rec.part_time_hours := p_part_time_hour;
5152 p_sf52_rec.flsa_category := p_flsa_category;
5153 p_sf52_rec.bargaining_unit_status := p_bargaining_unit_status;
5154 p_sf52_rec.functional_class := p_functional_class;
5155 p_sf52_rec.supervisory_status := p_supervisory_status;
5156 p_sf52_rec.to_basic_pay := p_basic_pay;
5157 p_sf52_rec.to_locality_adj := p_to_locality_adj;
5158 p_sf52_rec.to_adj_basic_pay := p_to_adj_basic_pay;
5159 p_sf52_rec.to_total_salary := p_to_total_salary;
5160 p_sf52_rec.from_other_pay_amount := p_from_other_pay_amount;
5161 p_sf52_rec.to_other_pay_amount := p_to_other_pay_amount;
5162 p_sf52_rec.to_au_overtime := p_to_au_overtime;
5163 p_sf52_rec.to_availability_pay := p_to_availability_pay;
5164 if p_to_retention_allowance = 0 or p_to_retention_allowance is null then
5165 p_sf52_rec.to_retention_allowance := null;
5166 else
5167 p_sf52_rec.to_retention_allowance := p_to_retention_allowance;
5168 end if;
5169 p_sf52_rec.to_retention_allow_percentage := p_to_retention_allow_perce;
5170 if p_to_supervisory_differential = 0 or p_to_supervisory_differential is null then
5171 p_sf52_rec.to_supervisory_differential := null;
5172 else
5173 p_sf52_rec.to_supervisory_differential := p_to_supervisory_differential;
5174 end if;
5175 p_sf52_rec.to_supervisory_diff_percentage := p_to_supervisory_diff_perce;
5176 p_sf52_rec.to_staffing_differential := p_to_staffing_differential;
5177 p_sf52_rec.duty_station_id := p_duty_station_id;
5178 p_sf52_rec.duty_station_code := p_duty_station_code;
5179 p_sf52_rec.duty_station_desc := p_duty_station_desc;
5180 -- FWFA Changes Bug#4444609
5181 p_sf52_rec.input_pay_rate_determinant := p_input_pay_rate_determinant;
5182 p_sf52_rec.from_pay_table_identifier := p_from_pay_table_id;
5183 p_sf52_rec.to_pay_table_identifier := p_to_pay_table_id;
5184 -- FWFA Changes
5185 p_sf52_rec.FIRST_LAC1_INFORMATION1 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION1;
5186 p_sf52_rec.FIRST_LAC1_INFORMATION2 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION2;
5187 p_sf52_rec.FIRST_LAC1_INFORMATION3 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION3;
5188 p_sf52_rec.FIRST_LAC1_INFORMATION4 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION4;
5189 p_sf52_rec.FIRST_LAC1_INFORMATION5 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION5;
5190 p_sf52_rec.SECOND_LAC1_INFORMATION1 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION1;
5191 p_sf52_rec.SECOND_LAC1_INFORMATION2 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION2;
5192 p_sf52_rec.SECOND_LAC1_INFORMATION3 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION3;
5193 p_sf52_rec.SECOND_LAC1_INFORMATION4 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION4;
5194 p_sf52_rec.SECOND_LAC1_INFORMATION5 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION5;
5195 p_sf52_rec.FIRST_ACTION_LA_CODE1 := p_lac_sf52_rec.FIRST_ACTION_LA_CODE1;
5199
5196 p_sf52_rec.FIRST_ACTION_LA_CODE2 := p_lac_sf52_rec.FIRST_ACTION_LA_CODE2;
5197 p_sf52_rec.FIRST_ACTION_LA_DESC1 := p_lac_sf52_rec.FIRST_ACTION_LA_DESC1;
5198 p_sf52_rec.FIRST_ACTION_LA_DESC2 := p_lac_sf52_rec.FIRST_ACTION_LA_DESC2;
5200 hr_utility.set_location('Exiting ' || l_proc,10);
5201
5202 exception
5203 when mlc_error then raise;
5204 when others then
5205 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
5206 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
5207 raise mlc_error;
5208 end assign_to_sf52_rec;
5209
5210 --
5211 --
5212 --
5213
5214 procedure ins_upd_pos_extra_info
5215 (p_position_id in NUMBER,
5216 p_effective_DATE in DATE,
5217 p_sel_flag in VARCHAR2,
5218 p_comment in VARCHAR2,
5219 p_msl_id in NUMBER) is
5220
5221 l_position_extra_info_id NUMBER;
5222 l_object_version_NUMBER NUMBER;
5223 l_pos_ei_data per_position_extra_info%rowtype;
5224
5225 CURSOR position_ext_cur (position NUMBER) is
5226 SELECT position_extra_info_id, object_version_NUMBER
5227 FROM PER_POSITION_EXTRA_INFO
5228 WHERE POSITION_ID = position
5229 and information_type = 'GHR_US_POS_MASS_ACTIONS';
5230
5231 l_proc VARCHAR2(72) := g_package || '.ins_upd_pos_extra_info';
5232 l_eff_DATE DATE;
5233
5234 BEGIN
5235 hr_utility.set_location('Entering ' || l_proc,5);
5236 g_proc := 'ins_upd_pos_extra_info';
5237
5238 if p_effective_DATE > sysDATE then
5239 l_eff_DATE := sysDATE;
5240 ELSE
5241 l_eff_DATE := p_effective_DATE;
5242 END IF;
5243
5244 ghr_history_fetch.fetch_positionei
5245 (p_position_id => p_position_id
5246 ,p_information_type => 'GHR_US_POS_MASS_ACTIONS'
5247 ,p_DATE_effective => l_eff_DATE
5248 ,p_pos_ei_data => l_pos_ei_data);
5249
5250 l_position_extra_info_id := l_pos_ei_data.position_extra_info_id;
5251 l_object_version_NUMBER := l_pos_ei_data.object_version_NUMBER;
5252
5253 IF l_position_extra_info_id is null then
5254 for pos_ext_rec in position_ext_cur(p_position_id)
5255 loop
5256 l_position_extra_info_id := pos_ext_rec.position_extra_info_id;
5257 l_object_version_NUMBER := pos_ext_rec.object_version_NUMBER;
5258 END loop;
5259 END IF;
5260
5261 if l_position_extra_info_id is not null then
5262
5263 ----- Set the global variable not to fire the trigger
5264 ghr_api.g_api_dml := TRUE;
5265
5266 BEGIN
5267 ghr_position_extra_info_api.UPDATE_position_extra_info
5268 (P_POSITION_EXTRA_INFO_ID => l_position_extra_info_id
5269 ,P_EFFECTIVE_DATE => trunc(l_eff_DATE)
5270 ,P_OBJECT_VERSION_NUMBER => l_object_version_NUMBER
5271 ,p_poei_INFORMATION19 => p_sel_flag
5272 ,p_poei_INFORMATION20 => p_comment
5273 ,p_poei_INFORMATION21 => to_char(p_msl_id)
5274 ,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS');
5275 EXCEPTION when others then
5276 hr_utility.set_location('UPDATE posei error 1' || l_proc,10);
5277 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
5278 END;
5279 ----- Reset the global variable
5280 ghr_api.g_api_dml := FALSE;
5281
5282 ELSE
5283 ghr_position_extra_info_api.create_position_extra_info
5284 (P_POSITION_ID => p_position_id
5285 ,P_INFORMATION_TYPE => 'GHR_US_POS_MASS_ACTIONS'
5286 ,P_EFFECTIVE_DATE => trunc(l_eff_DATE)
5287 ,p_poei_INFORMATION19 => p_sel_flag
5288 ,p_poei_INFORMATION20 => p_comment
5289 ,p_poei_INFORMATION21 => to_char(p_msl_id)
5290 ,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS'
5291 ,P_POSITION_EXTRA_INFO_ID => l_position_extra_info_id
5292 ,P_OBJECT_VERSION_NUMBER => l_object_version_NUMBER);
5293 END IF;
5294 hr_utility.set_location('Exiting ' || l_proc,30);
5295
5296 EXCEPTION
5297 when mtc_error then raise;
5298 when others then
5299 pr('Error in '||l_proc);
5300 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
5301 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
5302 raise mtc_error ;
5303 END ins_upd_pos_extra_info;
5304
5305
5306 PROCEDURE get_extra_info_comments_pos
5307 (p_position_id in NUMBER,
5308 p_effective_DATE in DATE,
5309 p_sel_flag in out NOCOPY VARCHAR2,
5310 p_comments in out NOCOPY VARCHAR2,
5311 p_msl_id in out NOCOPY NUMBER) IS
5312
5313 l_sel_flag VARCHAR2(30);
5314 l_comments VARCHAR2(4000);
5315 l_msl_id NUMBER;
5316 l_pos_ei_data per_position_extra_info%rowtype;
5317 l_proc VARCHAR2(72) := g_package || '.get_extra_info_comments';
5318 l_eff_DATE DATE;
5319 l_char_msl_id VARCHAR2(30);
5320
5321 BEGIN
5322 g_proc := 'get_extra_info_comments';
5323 hr_utility.set_location('Entering ' || l_proc,5);
5324 pr('In '||l_proc);
5325
5326 -- Initialization for NOCOPY Changes
5327 l_sel_flag := p_sel_flag;
5328 l_comments := p_comments;
5329 l_msl_id := p_msl_id;
5330 --
5334
5331 l_eff_DATE := p_effective_DATE;
5332
5333 pr(l_proc||'---> before fetch pos ei');
5335 ghr_history_fetch.fetch_positionei
5336 (p_position_id => p_position_id
5337 ,p_information_type => 'GHR_US_POS_MASS_ACTIONS'
5338 ,p_DATE_effective => l_eff_DATE
5339 ,p_pos_ei_data => l_pos_ei_data);
5340
5341 pr(l_proc||'---> after fetch pos ei');
5342
5343 l_sel_flag := l_pos_ei_data.poei_information19;
5344
5345 pr(l_proc||'---> after sel_flg assignment');
5346 l_comments := l_pos_ei_data.poei_information20;
5347 pr(l_proc||'---> after comments assignment');
5348 l_char_msl_id := l_pos_ei_data.poei_information21;
5349 pr(l_proc||'---> after l_msl_id assignment');
5350 l_msl_id := to_NUMBER(l_char_msl_id);
5351 pr(l_proc||'---> after p_msl_id assignment');
5352
5353 p_sel_flag := l_sel_flag;
5354 p_comments := l_comments;
5355 p_msl_id := l_msl_id;
5356
5357 pr('position ext id',to_char(l_pos_ei_data.position_extra_info_id),
5358 to_char(l_pos_ei_data.object_version_NUMBER));
5359 EXCEPTION
5360 when mtc_error then raise;
5361 when others then
5362 -- NOCOPY Changes
5363 -- Reset INOUT Params and set OUT params
5364 --
5365 p_sel_flag := l_sel_flag;
5366 p_comments := l_comments;
5367 p_msl_id := l_msl_id;
5368 --
5369 pr('Error in '||l_proc);
5370 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
5371 l_mlcerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
5372 raise mtc_error;
5373 END get_extra_info_comments_pos;
5374
5375
5376 procedure check_select_flg_pos(p_position_id in NUMBER,
5377 p_action in VARCHAR2,
5378 p_effective_DATE in DATE,
5379 p_msl_id in NUMBER,
5380 p_sel_flg in OUT NOCOPY VARCHAR2)
5381 IS
5382
5383 l_comments VARCHAR2(150);
5384 l_msl_id NUMBER;
5385 l_sel_flg VARCHAR2(10);
5386 l_line NUMBER := 0;
5387
5388 l_proc VARCHAR2(72) := g_package || '.check_select_flg';
5389
5390 BEGIN
5391 g_proc := 'check_SELECT_flg';
5392
5393 --Initilization for NOCOPY Changes
5394 --
5395 l_sel_flg := p_sel_flg;
5396 --
5397 hr_utility.set_location('Entering ' || l_proc,5);
5398 pr('in '||l_proc);
5399 --
5400 l_line := 5;
5401 get_extra_info_comments_pos(p_position_id,p_effective_DATE,l_sel_flg,l_comments,l_msl_id);
5402
5403 pr('After get ext ');
5404 pr('Sel flg ',l_sel_flg,'msl id '||to_char(l_msl_id));
5405 pr('After pr sel fl');
5406 p_sel_flg := l_sel_flg;
5407
5408 l_line := 10;
5409 if l_sel_flg is null then
5410 p_sel_flg := 'Y';
5411 ELSIF l_sel_flg = 'Y' then
5412 if nvl(l_msl_id,0) <> nvl(p_msl_id,0) then
5413 p_sel_flg := 'N';
5414 END IF;
5415 ELSIF l_sel_flg = 'N' then
5416 if nvl(l_msl_id,0) <> nvl(p_msl_id,0) then
5417 p_sel_flg := 'Y';
5418 END IF;
5419 END IF;
5420
5421 EXCEPTION
5422 when mtc_error then raise;
5423 when others then
5424 -- NOCOPY Changes
5425 -- Reset IN OUT params and Set OUT params to null
5426 p_sel_flg := l_sel_flg;
5427 --
5428 pr('Error in '||l_proc);
5429 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
5430 l_mlcerrbuf := 'Error in '||l_proc||' @'||to_char(l_line)||' Sql Err is '|| sqlerrm(sqlcode);
5431
5432 raise mtc_error;
5433 END check_select_flg_pos;
5434
5435
5436 procedure position_history_update (p_position_id IN hr_positions_f.position_id%type,
5437 p_effective_date IN date,
5438 p_table_id IN pay_user_tables.user_table_id%type,
5439 p_upd_tableid IN pay_user_tables.user_table_id%type)
5440 is
5441
5442 CURSOR cur_hist_rows(l_tab_id NUMBER,l_eff_date date, l_pos_id NUMBER)
5443 IS
5444
5445 SELECT pah.pa_history_id,
5446 pah.information4 , -- position_id
5447 to_number(pah.information11) user_tab_id,
5448 pah.effective_date
5449 FROM ghr_pa_history pah
5450 WHERE pah.table_name = 'PER_POSITION_EXTRA_INFO'
5451 AND pah.information5 = 'GHR_US_POS_VALID_GRADE'
5452 AND to_number(pah.information4) = l_pos_id
5453 AND to_number(pah.information11) = l_tab_id
5454 AND pah.effective_date >= to_date('2005/05/01','YYYY/MM/DD')
5455 AND pah.effective_date > l_eff_date
5456 AND to_number(pah.information4) in
5457 (SELECT position_id
5458 from hr_positions_f pos
5459 WHERE pos.position_id = to_number(pah.information4)
5460 AND pah.effective_date
5461 between pos.effective_start_date and pos.effective_end_date
5462 AND HR_GENERAL.DECODE_AVAILABILITY_STATUS(pos.availability_status_id) = 'Active');
5463
5464 l_hist_id ghr_pa_history.pa_history_id%type;
5465 l_position_id per_assignments_f.position_id%type;
5466 l_his_eff_date ghr_pa_requests.effective_date%type;
5467 l_user_tab_id pay_user_tables.user_table_id%type;
5468
5469
5470 begin
5471 FOR hist_rec IN cur_hist_rows(p_table_id,p_effective_date,p_position_id)
5472 LOOP
5473
5474 l_hist_id := hist_rec.pa_history_id;
5475 l_position_id := hist_rec.information4;
5476 l_his_eff_date := hist_rec.effective_date;
5477 l_user_tab_id := hist_rec.user_tab_id;
5478
5479
5480 UPDATE GHR_PA_HISTORY upah
5481 SET information11 = to_char(p_upd_tableid)
5482 WHERE pa_history_id = l_hist_id;
5483
5484 END LOOP;
5485 end position_history_update;
5486
5487 procedure pr (msg varchar2,par1 in varchar2 default null,
5488 par2 in varchar2 default null) is
5489 begin
5490 -- g_no := g_no +1;
5491 -- insert into l_tmp values (g_no,substr(msg||'-'||par1||' -'||par2||'-',1,199));
5492 -- DBMS_OUTPUT.PUT_LINE(msg||'-'||par1||' -'||par2||'-');
5493
5494 ghr_mto_int.put_line(msg||'-'||par1||' -'||par2||'-');
5495 exception
5496 when others then
5497 hr_utility.set_location('Error in pr '||' Err is '||sqlerrm(sqlcode),20);
5498 l_mlcerrbuf := 'Error in pr Sql Err is '|| sqlerrm(sqlcode);
5499 raise mlc_error;
5500 end;
5501
5502 --Bug # 11924741 added to check whether any pa request exists already
5503 function chk_pa_req_already_exists(p_person_id in number,
5504 p_effective_date in date,
5505 p_first_noa_code in varchar2,
5506 p_action in varchar2
5507 )
5508 return boolean is
5509 --
5510 l_name per_people_f.full_name%type;
5511 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
5512
5513 cursor csr_action_taken is
5514 select pr.pa_request_id, max(pa_routing_history_id) pa_routing_history_id
5515 from ghr_pa_requests pr, ghr_pa_routing_history prh
5516 where pr.pa_request_id = prh.pa_request_id
5517 and person_id = p_person_id
5518 and first_noa_code = p_first_noa_code
5519 and effective_date = p_effective_date
5520 and nvl(pr.first_noa_cancel_or_correct,'X') <> ghr_history_api.g_cancel
5521 group by pr.pa_request_id;
5522
5523 cursor csr_name is
5524 select substr(pr.employee_last_name || ', ' || pr.employee_first_name,1,240) fname
5525 from ghr_pa_requests pr
5526 where pr.pa_request_id = l_pa_request_id;
5527
5528
5529 cursor pa_hist_cur (p_r_hist_id number) is
5530 select nvl(action_taken,' ') action_taken
5531 from ghr_pa_routing_history
5532 where pa_routing_history_id = p_r_hist_id;
5533
5534 l_action_taken ghr_pa_routing_history.action_taken%TYPE;
5535 l_proc varchar2(72) := g_package || '.chk_pa_req_already_exists';
5536 l_sel_flg varchar2(2);
5537
5538 begin
5539 g_proc := 'chk_pa_req_already_exists';
5540 hr_utility.set_location('Entering ' || l_proc,5);
5541
5542 for v_action_taken in csr_action_taken loop
5543 l_pa_request_id := v_action_taken.pa_request_id;
5544 for v_name in csr_name
5545 loop
5546 l_name := v_name.fname;
5547 exit;
5548 end loop;
5549 for pa_hist_rec in pa_hist_cur (v_action_taken.pa_routing_history_id)
5550 loop
5551 l_action_taken := pa_hist_rec.action_taken;
5552 exit;
5553 end loop;
5554
5555 if l_action_taken <> 'CANCELED' then
5556 IF upper(p_action) IN ('SHOW','REPORT') THEN
5557 ghr_mto_int.log_message(
5558 p_procedure => 'RPA Exists Already',
5559 p_message => 'Name: '|| l_name || ' - Salary Change ' ||
5560 ' RPA Exists for the given effective date ' );
5561 END IF;
5562 return false;
5563 end if;
5564 end loop;
5565 hr_utility.set_location('Exiting ' || l_proc,30);
5566 return true;
5567 end chk_pa_req_already_exists;
5568 --Bug # 11924741
5569
5570
5571 END GHR_MLC_PKG;