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