[Home] [Help]
PACKAGE BODY: APPS.GHR_MRE_PKG
Source
1 PACKAGE BODY GHR_MRE_PKG AS
2 /* $Header: ghmreexe.pkb 120.14.12020000.4 2012/07/05 14:33:10 amnaraya ship $ */
3
4 --
5 -- Global Declaration
6 --
7
8 g_no NUMBER := 0;
9 g_package VARCHAR2(32) := 'GHR_MRE_PKG';
10 g_proc VARCHAR2(32) := null;
11 g_business_group_id NUMBER := null;
12 l_log_text VARCHAR2(2000) := null;
13 l_mslerrbuf VARCHAR2(2000) := null;
14
15 Procedure UPDATE_position_info
16 (p_position_data_rec ghr_sf52_pos_UPDATE.position_data_rec_type);
17
18 --
19 -- End Global declaration
20 --
21
22 procedure execute_mre (p_errbuf out NOCOPY VARCHAR2,
23 p_retcode out NOCOPY NUMBER,
24 p_mass_realignment_id in NUMBER,
25 p_action in VARCHAR2,
26 p_show_vacant_pos in VARCHAR2 default 'NO') is
27
28 cursor child_orgs (cp_org_pos_id NUMBER,
29 child_fl VARCHAR2,
30 org_pos_fl VARCHAR2,
31 org_str_id NUMBER) is
32 SELECT a.organization_id_child org_pos_id
33 from per_org_structure_elements a,
34 per_org_structure_versions b
35 WHERE a.org_structure_version_id = b.org_structure_version_id
36 and a.org_structure_version_id = org_str_id
37 and child_fl = 'Y'
38 and org_pos_fl = 'O'
39 and a.org_structure_element_id in
40 (
41 SELECT org_structure_element_id
42 from per_org_structure_elements
43 start with organization_id_parent = cp_org_pos_id
44 connect by prior organization_id_child = organization_id_parent
45 )
46 union
47 SELECT b.ORGANIZATION_ID org_pos_id
48 from per_organization_units b
49 -- VSM added nvl( .. to the start... clause
50 -- enhancement in SELECTion criteria as org_id can be be null [Masscrit.doc]
51 WHERE b.organization_id = nvl(cp_org_pos_id, b.organization_id)
52 and b.business_group_id = g_business_group_id
53 and org_pos_fl = 'O'
54 union
55 SELECT a.subordinate_position_id org_pos_id
56 from per_pos_structure_elements a,
57 per_pos_structure_versions b
58 WHERE a.pos_structure_version_id = b.pos_structure_version_id
59 and a.pos_structure_version_id = org_str_id
60 and child_fl = 'Y'
61 and org_pos_fl = 'P'
62 and a.pos_structure_element_id in
63 (
64 SELECT pos_structure_element_id
65 from per_pos_structure_elements
66 start with parent_position_id = cp_org_pos_id
67 connect by prior subordinate_position_id = parent_position_id
68 )
69 union
70 SELECT b.position_id org_pos_id
71 from hr_positions_f b
72 WHERE b.position_id = cp_org_pos_id
73 and b.business_group_id = g_business_group_id
74 and org_pos_fl = 'P';
75
76 /* and child_fl = 'N';*/
77 -- Bug 4377361 included EMP_APL for person type condition
78
79 cursor cur_people (p_org_pos_id NUMBER,
80 org_pos_fl VARCHAR2,
81 effective_DATE DATE) is
82 SELECT ppf.person_id PERSON_ID,
83 ppf.first_name FIRST_NAME,
84 ppf.last_name LAST_NAME,
85 ppf.middle_names MIDDLE_NAMES,
86 ppf.full_name FULL_NAME,
87 ppf.DATE_of_birth DATE_OF_BIRTH,
88 ppf.national_identifier NATIONAL_IDENTIFIER,
89 --Bug # 9329643
90 ppf.employee_number EMPLOYEE_NUMBER,
91 paf.position_id POSITION_ID,
92 paf.assignment_id ASSIGNMENT_ID,
93 paf.grade_id GRADE_ID,
94 paf.job_id JOB_ID,
95 paf.location_id LOCATION_ID,
96 paf.organization_id ORGANIZATION_ID,
97 paf.business_group_id BUSINESS_GROUP_ID,
98 punits.name ORGANIZATION_NAME
99 from per_assignments_f paf,
100 per_people_f ppf,
101 per_person_types ppt,
102 per_organization_units punits
103 -- VSM added nvl( .. to the start... clause
104 -- enhancement in SELECTion criteria as org_id can be be null [Masscrit.doc]
105 WHERE (paf.organization_id = nvl(p_org_pos_id, paf.organization_id)
106 and
107 org_pos_fl = 'O')
108 and ppf.person_id = paf.person_id
109 and trunc(effective_DATE) between paf.effective_start_DATE
110 and paf.effective_END_DATE
111 and paf.primary_flag = 'Y'
112 and paf.assignment_type <> 'B'
113 and ppf.current_employee_flag = 'Y'
114 and trunc(effective_DATE) between ppf.effective_start_DATE
115 and ppf.effective_END_DATE
116 and ppf.person_type_id = ppt.person_type_id
117 and ppt.system_person_type IN ('EMP','EMP_APL')
118 and paf.organization_id = punits.organization_id
119 and paf.business_group_id = g_business_group_id
120 and paf.position_id is not null
121 union
122 SELECT ppf.person_id PERSON_ID,
123 ppf.first_name FIRST_NAME,
124 ppf.last_name LAST_NAME,
125 ppf.middle_names MIDDLE_NAMES,
126 ppf.full_name FULL_NAME,
127 ppf.DATE_of_birth DATE_OF_BIRTH,
128 ppf.national_identifier NATIONAL_IDENTIFIER,
129 --Bug # 9329643
130 ppf.employee_number EMPLOYEE_NUMBER,
131 paf.position_id POSITION_ID,
132 paf.assignment_id ASSIGNMENT_ID,
133 paf.grade_id GRADE_ID,
134 paf.job_id JOB_ID,
135 paf.location_id LOCATION_ID,
136 paf.organization_id ORGANIZATION_ID,
137 paf.business_group_id BUSINESS_GROUP_ID,
138 punits.name ORGANIZATION_NAME
139 from per_assignments_f paf,
140 per_people_f ppf,
141 per_person_types ppt,
142 per_organization_units punits
143 WHERE (paf.position_id = nvl(p_org_pos_id,paf.position_id)
144 and
145 org_pos_fl = 'P')
146 and ppf.person_id = paf.person_id
147 and trunc(effective_DATE) between paf.effective_start_DATE
148 and paf.effective_END_DATE
149 and paf.primary_flag = 'Y'
150 and paf.assignment_type <> 'B'
151 and ppf.current_employee_flag = 'Y'
152 and trunc(effective_DATE) between ppf.effective_start_DATE
153 and ppf.effective_END_DATE
154 and ppf.person_type_id = ppt.person_type_id
155 and ppt.system_person_type IN ('EMP','EMP_APL')
156 and paf.organization_id = punits.organization_id
157 and paf.business_group_id = g_business_group_id
158 and paf.position_id is not null;
159
160 -- Modified this cursor to unSELECT the eliminated positions
161 -- using hr_genral call for bug 3215722
162 cursor unassigned_pos (p_org_pos_id NUMBER,
163 org_pos_fl VARCHAR2,
164 effective_DATE DATE) is
165 SELECT null PERSON_ID,
166 'VACANT' FIRST_NAME,
167 'VACANT' LAST_NAME,
168 'VACANT' FULL_NAME,
169 null MIDDLE_NAMES,
170 null DATE_OF_BIRTH,
171 null NATIONAL_IDENTIFIER,
172 --Bug # 9329643
173 null EMPLOYEE_NUMBER,
174 position_id POSITION_ID,
175 null ASSIGNMENT_ID,
176 to_NUMBER(null) GRADE_ID,
177 JOB_ID,
178 pop.LOCATION_ID,
179 pop.ORGANIZATION_ID,
180 pop.BUSINESS_GROUP_ID,
181 punits.name ORGANIZATION_NAME,
182 pop.availability_status_id
183 from hr_positions_f pop,
184 per_organization_units punits
185 WHERE pop.business_group_id = g_business_group_id
186 and trunc(effective_DATE) between pop.effective_start_DATE and pop.effective_END_DATE
187 and pop.organization_id = punits.organization_id
188 and (pop.organization_id = nvl(p_org_pos_id,pop.organization_id) and org_pos_fl = 'O'
189 or
190 pop.position_id = nvl(p_org_pos_id,pop.position_id) and org_pos_fl = 'P')
191 and not exists
192 (
193 SELECT 'X'
194 FROM per_people_f p, per_assignments_f a
195 WHERE trunc(effective_DATE) between a.effective_start_DATE and a.effective_END_DATE
196 AND a.primary_flag = 'Y'
197 AND a.assignment_type <> 'B'
198 AND p.current_employee_flag = 'Y'
199 AND a.business_group_id = g_business_group_id
200 AND a.person_id = p.person_id
201 AND a.position_id = pop.position_id
202 AND trunc(effective_DATE) between p.effective_start_DATE and p.effective_end_DATE
203 );
204
205 /**** Commented as on 09-DEC_2004 and tuned the sql as above.
206 SELECT null PERSON_ID,
207 'VACANT' FIRST_NAME,
208 'VACANT' LAST_NAME,
209 'VACANT' FULL_NAME,
210 null MIDDLE_NAMES,
211 null DATE_OF_BIRTH,
212 null NATIONAL_IDENTIFIER,
213 position_id POSITION_ID,
214 null ASSIGNMENT_ID,
215 to_NUMBER(null) GRADE_ID,
216 JOB_ID,
217 pop.LOCATION_ID,
218 pop.ORGANIZATION_ID,
219 pop.BUSINESS_GROUP_ID,
220 punits.name ORGANIZATION_NAME,
221 pop.availability_status_id
222 from hr_positions_f pop,
223 per_organization_units punits
224 WHERE pop.business_group_id = g_business_group_id
225 and pop.position_id in
226 (
227 SELECT position_id POSITION_ID
228 from hr_positions_f
229 WHERE (organization_id = nvl(p_org_pos_id,organization_id) and org_pos_fl = 'O'
230 or
231 position_id = nvl(p_org_pos_id,position_id) and org_pos_fl = 'P')
232 and trunc(effective_DATE)
233 between effective_start_DATE and effective_END_DATE
234 and business_group_id = g_business_group_id
235 MINUS
236 SELECT a.position_id
237 from per_people_f p, per_assignments_f a
238 WHERE (a.organization_id = nvl(p_org_pos_id,organization_id) and org_pos_fl = 'O'
239 or
240 a.position_id = nvl(p_org_pos_id,a.position_id) and org_pos_fl = 'P')
241 and trunc(effective_DATE) between a.effective_start_DATE
242 and a.effective_END_DATE
243 and a.primary_flag = 'Y'
244 and a.assignment_type <> 'B'
245 and p.current_employee_flag = 'Y'
246 and a.business_group_id = g_business_group_id
247 and a.person_id = p.person_id
248 and a.position_id = pop.position_id
249 and trunc(effective_DATE) between p.effective_start_DATE
250 and p.effective_END_DATE
251 )
252 and trunc(effective_DATE)
253 between pop.effective_start_DATE and pop.effective_END_DATE
254 and pop.organization_id = punits.organization_id;
255 ******************** Commented as of 09-DEC-2004 ***/
256 -- added Join for tables a,p. a.person_id=p.person_id and a.position_id=pop.position_id
257 -- Bug 3677516
258 cursor c_grade_kff (grd_id NUMBER) is
259 SELECT gdf.segment1
260 ,gdf.segment2
261 from per_grades grd, per_grade_definitions gdf
262 WHERE grd.grade_id = grd_id
263 and grd.grade_definition_id = gdf.grade_definition_id;
264
265 cursor ghr_mre (p_mass_realignment_id NUMBER) is
266 SELECT name,
267 effective_DATE,
268 old_organization_id,
269 new_organization_id,
270 status,
271 reason,
272 org_structure_id,
273 office_symbol,
274 agency_code_subelement agency_sub_elem_code,
275 personnel_office_id,
276 -- added this for 3191704
277 target_personnel_office_id,
278 old_org_structure_version_id old_organization_structure_id,
279 old_position_id,
280 old_pos_structure_version_id old_position_structure_id,
281 PA_REQUEST_ID,
282 business_group_id
283 from ghr_mass_realignment
284 WHERE mass_realignment_id = p_mass_realignment_id
285 for UPDATE of status nowait;
286
287 -- added this for 3191704
288 target_personnel_office_id ghr_mass_realignment.personnel_office_id%type;
289
290 -- Bug#2651909 Cursor to get OPM Organizational Component of Target Organization
291 cursor c_opm_org_component(p_organization_id IN NUMBER) is
292 Select org_information4
293 from HR_ORGANIZATION_INFORMATION
294 WHERE organization_id = p_organization_id
295 and ORG_INFORMATION_CONTEXT = 'GHR_US_ORG_REPORTING_INFO';
296
297 l_assignment_id per_assignments_f.assignment_id%type;
298 l_position_id per_assignments_f.position_id%type;
299 l_grade_id per_assignments_f.grade_id%type;
300 l_asg_extra_info_rec per_assignment_extra_info%rowtype;
301 l_business_group_id per_assignments_f.business_group_id%type;
302
303 l_position_title VARCHAR2(300);
304 l_position_NUMBER VARCHAR2(20);
305 l_position_seq_no VARCHAR2(20);
306
307 l_mass_cnt NUMBER := 0;
308 l_recs_failed NUMBER := 0;
309
310 l_tenure VARCHAR2(35);
311 l_annuitant_indicator VARCHAR2(35);
312 l_pay_rate_determinant VARCHAR2(35);
313 l_work_schedule VARCHAR2(35);
314 l_part_time_hour VARCHAR2(35);
315 l_pay_table_id NUMBER;
316 l_pay_plan VARCHAR2(30);
317 l_grade_or_level VARCHAR2(30);
318 l_step_or_rate VARCHAR2(30);
319 l_pay_basis VARCHAR2(30);
320 l_location_id NUMBER;
321 l_duty_station_id NUMBER;
322 l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
323 l_duty_station_code ghr_pa_requests.duty_station_code%type;
324
325 l_check_child VARCHAR2(2);
326 l_check_org_pos VARCHAR2(2);
327 l_avail_status_id NUMBER;
328 -- 3215722
329 l_org_pos_id NUMBER;
330 l_org_pos_str_id NUMBER;
331
332 l_effective_DATE DATE;
333 r_effective_DATE DATE;
334
335 p_mass_realignment_name VARCHAR2(80);
336 p_old_organization_id NUMBER;
337 p_old_org_structure_id NUMBER;
338 p_old_position_id NUMBER;
339 p_old_pos_structure_id NUMBER;
340 p_new_organization_id NUMBER;
341 p_status VARCHAR2(1);
342 p_reason VARCHAR2(240);
343 p_org_structure_id VARCHAR2(30);
344 p_new_org_structure_id VARCHAR2(30);
345 p_office_symbol VARCHAR2(30);
346 p_agency_sub_elem_code VARCHAR2(30);
347 p_personnel_office_id VARCHAR2(30);
348 p_duty_station_id NUMBER(15);
349 p_position_title VARCHAR2(240);
350 p_pay_plan VARCHAR2(2);
351 p_occ_code VARCHAR2(9);
352 l_pa_request_id NUMBER;
353
354 l_personnel_office_id VARCHAR2(300);
355 l_org_structure_id VARCHAR2(300);
356 l_office_symbol VARCHAR2(30);
357 l_occ_series VARCHAR2(30);
358 l_sub_element_code VARCHAR2(30);
359
360 l_payroll_office_id VARCHAR2(30);
361 l_org_func_code VARCHAR2(30);
362 l_appropriation_code1 VARCHAR2(30);
363 l_appropriation_code2 VARCHAR2(30);
364 l_position_organization VARCHAR2(240);
365
366 t_personnel_office_id VARCHAR2(300);
367 t_sub_element_code VARCHAR2(300);
368 t_duty_station_id NUMBER(15);
369 t_duty_station_desc ghr_pa_requests.duty_station_desc%type;
370 t_duty_station_code ghr_pa_requests.duty_station_code%type;
371 t_duty_station_locn_id NUMBER(15);
372 t_office_symbol VARCHAR2(30);
373 t_payroll_office_id VARCHAR2(30);
374 t_org_func_code VARCHAR2(30);
375 t_appropriation_code1 VARCHAR2(30);
376 t_appropriation_code2 VARCHAR2(30);
377 t_position_organization VARCHAR2(240);
378
379 l_auo_premium_pay_indicator VARCHAR2(30);
380 l_ap_premium_pay_indicator VARCHAR2(30);
381 l_retention_allowance NUMBER;
382 l_supervisory_differential NUMBER;
383 l_staffing_differential NUMBER;
384
385 l_out_step_or_rate VARCHAR2(30);
386 l_out_pay_rate_determinant VARCHAR2(30);
387 l_PT_eff_start_DATE DATE;
388 l_open_pay_fields BOOLEAN;
389 l_message_set BOOLEAN;
390 l_calculated BOOLEAN;
391
392 l_old_basic_pay NUMBER;
393 l_old_avail_pay NUMBER;
394 l_old_loc_diff NUMBER;
395 l_tot_old_sal NUMBER;
396 l_old_auo_pay NUMBER;
397 l_old_ADJ_basic_pay NUMBER;
398 l_other_pay NUMBER;
399
400
401 l_retention_allow_perc NUMBER; ---AVR
402 l_new_retention_allowance NUMBER; ---AVR
403 l_supervisory_diff_perc NUMBER; ---AVR
404 l_new_supervisory_differential NUMBER; ---AVR
405
406
407 l_new_avail_pay NUMBER;
408 l_new_loc_diff NUMBER;
409 l_tot_new_sal NUMBER;
410 l_new_auo_pay NUMBER;
411
412 l_new_basic_pay NUMBER;
413 l_new_locality_adj NUMBER;
414 l_new_adj_basic_pay NUMBER;
415 l_new_total_salary NUMBER;
416 l_new_other_pay_amount NUMBER;
417 l_new_au_overtime NUMBER;
418 l_new_availability_pay NUMBER;
419
420 l_user_table_id NUMBER;
421 l_executive_order_no VARCHAR2(30);
422 l_executive_order_DATE DATE;
423
424 l_row_cnt NUMBER := 0;
425
426 l_sf52_rec ghr_pa_requests%rowtype;
427 l_lac_sf52_rec ghr_pa_requests%rowtype;
428 l_errbuf VARCHAR2(2000);
429
430 l_retcode NUMBER;
431
432 l_pos_ei_data per_position_extra_info%rowtype;
433 l_pos_valid_grade_ei_data per_position_extra_info%rowtype;
434 l_pos_grp1_rec per_position_extra_info%rowtype;
435 --l_pos_grp2_rec per_position_extra_info%rowtype;
436
437 l_pay_calc_in_data ghr_pay_calc.pay_calc_in_rec_type;
438 l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
439 l_sel_flg VARCHAR2(2);
440 l_sel_status VARCHAR2(32);
441
442 l_first_action_la_code1 VARCHAR2(30);
443 l_first_action_la_code2 VARCHAR2(30);
444
445 l_remark_code1 VARCHAR2(30);
446 l_remark_code2 VARCHAR2(30);
447
448 ----Pay cap variables
449 l_entitled_other_pay NUMBER;
450 l_capped_other_pay NUMBER;
451 l_adj_basic_message BOOLEAN := FALSE;
452 l_pay_cap_message BOOLEAN := FALSE;
453 l_temp_retention_allowance NUMBER;
454 l_open_pay_fields_caps BOOLEAN;
455 l_message_set_caps BOOLEAN;
456 l_total_pay_check VARCHAR2(1);
457 l_comment VARCHAR2(100);
458 l_pay_sel VARCHAR2(1) := NULL;
459
460
461 REC_BUSY EXCEPTION;
462 pragma EXCEPTION_init(REC_BUSY,-54);
463
464 l_proc VARCHAR2(72) := g_package || '.execute_mre';
465 l_ind NUMBER := 0;
466 l_dummy NUMBER;
467 l_break VARCHAR2(1) := 'N';
468 l_appl_id VARCHAR2(20);-- Bug#4114068
469
470 -- Bug 3663808 Variable to store retained grade details
471 p_retained_grade ghr_pay_calc.retained_grade_rec_type;
472
473 CURSOR c_pay_tab_type(p_user_table_id pay_user_tables.user_table_id%type)
474 IS
475 SELECT range_or_match
476 FROM pay_user_tables
477 WHERE user_table_id = p_user_table_id;
478
479 l_table_type VARCHAR2(2);
480 BEGIN
481 p_retcode := 0;
482 pr('Inside execute mre');
483 pr('Mass Realignment id is '||p_mass_realignment_id,' Action is '|| p_action);
484
485 g_proc := 'execute_mre';
486
487 hr_utility.set_location('Entering ' || l_proc,5);
488 l_ind := 10;
489
490 BEGIN
491 FOR mre IN ghr_mre (p_mass_realignment_id)
492 LOOP
493 p_mass_realignment_name := mre.name;
494 l_effective_DATE := mre.effective_DATE;
495 p_old_organization_id := mre.old_organization_id;
496 p_old_org_structure_id := mre.old_organization_structure_id;
497 p_new_organization_id := mre.new_organization_id;
498 p_status := mre.status;
499 p_reason := mre.reason;
500 p_org_structure_id := mre.org_structure_id;
501 p_office_symbol := mre.office_symbol;
502 p_agency_sub_elem_code := mre.agency_sub_elem_code;
503 p_personnel_office_id := mre.personnel_office_id;
504 -- bug 3191704
505 target_personnel_office_id := mre.target_personnel_office_id;
506 p_old_position_id := mre.old_position_id;
507 p_old_pos_structure_id := mre.old_position_structure_id;
508 l_pa_request_id := mre.pa_request_id;
509 g_business_group_id := mre.business_group_id;
510 exit;
511 END LOOP;
512 EXCEPTION
513 when REC_BUSY then
514 hr_utility.set_location('Mass Realignment is in use',1);
515 l_mslerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
516 hr_utility.set_message(8301, 'GHR_38477_LOCK_ON_MRE');
517 hr_utility.raise_error;
518 when others then
519 hr_utility.set_location
520 ('Error in '||l_proc||' Sql err is '||sqlerrm(sqlcode),1);
521 l_mslerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
522 raise mass_error;
523 END;
524
525 IF UPPER(p_action) = 'CREATE' then
526 ghr_mto_int.set_log_program_name('GHR_MRE_PKG');
527 ELSE
528 ghr_mto_int.set_log_program_name('MRE_'||p_mass_realignment_name);
529 END IF;
530 --Begin Bug# 3163576, added custom hook
531 ghr_mre_custom_pkg.business_rules(
532 p_mass_realignment_id => p_mass_realignment_id
533 ,p_action => p_action
534 ,p_show_vacant_pos => p_show_vacant_pos);
535 --End Bug# 3163576
536 l_ind := 20;
537 IF UPPER(p_action) = 'CREATE' then
538 if l_pa_request_id is null then
539 hr_utility.set_message(8301, 'GHR_38567_SELECT_LAC_REMARKS');
540 hr_utility.raise_error;
541 END IF;
542 END IF;
543
544 ghr_msl_pkg.get_lac_dtls(l_pa_request_id,
545 l_lac_sf52_rec);
546
547 --purge_old_data(p_mass_realignment_id);
548
549 l_ind := 30;
550
551 hr_utility.set_location('After fetch mre '||to_char(l_effective_DATE),1);
552
553 if p_old_organization_id is not null then
554 l_check_org_pos := 'O';
555 l_org_pos_id := p_old_organization_id;
556 l_org_pos_str_id := p_old_org_structure_id;
557 if p_old_org_structure_id is null then
558 l_check_child := 'N';
559 ELSE
560 l_check_child := 'Y';
561 END IF;
562 END IF;
563 if p_old_position_id is not null then
564 l_check_org_pos := 'P';
565 l_org_pos_id := p_old_position_id;
566 l_org_pos_str_id := p_old_pos_structure_id;
567 if p_old_pos_structure_id is null then
568 l_check_child := 'N';
569 ELSE
570 l_check_child := 'Y';
571 END IF;
572 END IF;
573
574 -- VSM [Masscrit.doc]
575 if p_old_organization_id is null and
576 p_old_position_id is null then
577 -- if neither org nor position is entered then employees from
578 -- all the organizations will be fetched.
579 l_break := 'Y';
580 l_org_pos_id := null;
581 l_org_pos_str_id := null;
582 l_check_org_pos := 'O';
583 l_check_child := 'N';
584 END IF;
585
586 /****
587 FOR per_count_rec IN CUr_people_cnt (null,
588 l_check_org_pos,
589 l_effective_DATE)
590 LOOP
591 l_mass_cnt := per_count_rec.COUNT;
592 exit;
593 END LOOP;
594 ***/
595
596
597 FOR org in child_orgs (l_org_pos_id,
598 l_check_child,
599 l_check_org_pos,
600 l_org_pos_str_id)
601 LOOP
602 BEGIN
603 if UPPER(p_action) = 'REPORT' and p_status = 'P' THEN
604 r_effective_DATE := l_effective_DATE - 1;
605 ELSE
606 r_effective_DATE := l_effective_DATE;
607 END IF;
608
609 if l_break = 'Y' then
610 org.org_pos_id := null;
611 END IF;
612
613 FOR per IN cur_people (org.org_pos_id,
614 l_check_org_pos,
615 r_effective_DATE)
616 LOOP
617 --Bug #5416994 Validating External User Person Type
618 IF instr(upper(hr_person_type_usage_info.get_user_person_type(nvl(r_effective_date,trunc(sysdate)),per.person_id)),
619 upper('external')) = 0 THEN
620 BEGIN
621
622 pr('AFTER FET PEOPLE');
623
624 SAVEPOINT EXECUTE_MRE_SP;
625
626 l_ind := 40;
627 l_assignment_id := per.assignment_id;
628 l_position_id := per.position_id;
629 l_grade_id := per.grade_id;
630 l_business_group_id := per.business_group_iD;
631 l_location_id := per.location_id;
632
633 pr(' Assign Id/Pos ',to_char(per.assignment_id),to_char(per.position_id));
634 pr(' Grade/Bus grp ',to_char(per.grade_id),to_char(per.business_group_id));
635 pr(' Location/Eff dt ',to_char(per.location_id),to_char(l_effective_DATE));
636 pr('Person_id', to_char(per.person_id));
637
638 l_ind := 50;
639 if UPPER(p_action) = 'REPORT' AND p_status = 'P' THEN
640 pop_dtls_from_pa_req(per.person_id,l_effective_DATE,
641 p_mass_realignment_id, per.employee_number);
642 ELSE
643 if check_SELECT_flg(per.position_id,UPPER(p_action),
644 l_effective_DATE,
645 p_mass_realignment_id,
646 l_sel_flg) then
647 pr('After check sel flg value is '||l_sel_flg);
648
649 get_pos_grp1_ddf(l_position_id,
650 l_effective_DATE,
651 l_personnel_office_id,
652 l_org_structure_id,
653 l_office_symbol,
654 l_position_organization,
655 l_pos_grp1_rec);
656
657 ghr_msl_pkg.get_sub_element_code_pos_title(l_position_id,
658 per.person_id,
659 l_business_group_id,
660 l_assignment_id,
661 l_effective_DATE,
662 l_sub_element_code,
663 l_position_title,
664 l_position_NUMBER,
665 l_position_seq_no);
666
667 IF check_eligibility(
668 p_org_structure_id,
669 p_office_symbol,
670 p_personnel_office_id,
671 p_agency_sub_elem_code,
672
673 l_org_structure_id,
674 l_office_symbol,
675 l_personnel_office_id,
676 l_sub_element_code,
677 per.person_id,
678 l_effective_DATE,
679 UPPER(p_action)) THEN
680
681 l_occ_series := ghr_api.get_job_occ_series_job
682 (p_job_id => per.job_id
683 ,p_business_group_id => per.business_group_id
684 );
685 l_ind := 60;
686 BEGIN
687 ghr_pa_requests_pkg.get_sf52_asg_ddf_details
688 (l_assignment_id,
689 l_effective_DATE,
690 l_tenure,
691 l_annuitant_indicator,
692 l_pay_rate_determinant,
693 l_work_schedule,
694 l_part_time_hour);
695 EXCEPTION
696 when others then
697 pr('Error in Ghr_pa_requests_pkg.get_sf52_asg_ddf_details');
698 hr_utility.set_location('Error in Ghr_pa_requests_pkg.get_sf52_asg_ddf_details'||
699 'Err is '||sqlerrm(sqlcode),20);
700 l_mslerrbuf := 'Error in get_sf52_asgddf_details Sql Err is '||
701 sqlerrm(sqlcode);
702 raise mass_error;
703 END;
704
705 l_ind := 65;
706 BEGIN
707 ghr_msl_pkg.get_pay_plan_and_table_id(l_pay_rate_determinant,
708 per.person_id,
709 l_position_id,l_effective_DATE,
710 l_grade_id, l_assignment_id,'SHOW',l_pay_plan,
711 l_pay_table_id,l_grade_or_level, l_step_or_rate,
712 l_pay_basis);
713 EXCEPTION
714 --Bug#4179270,4126137,4086677 Added the get_message call.
715 when ghr_msl_pkg.msl_error then
716 l_mslerrbuf := hr_utility.get_message;
717 raise mass_error;
718 END;
719 l_ind := 70;
720 BEGIN
721 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
722 (p_location_id => l_location_id
723 ,p_duty_station_id => l_duty_station_id);
724 EXCEPTION
725 when others then
726 pr('Error in Ghr_pa_requests_pkg.get_sf52_asg_ddf_details');
727 hr_utility.set_location('Error in Ghr_pa_requests_pkg.get_sf52_loc_ddf_details'||
728 'Err is '||sqlerrm(sqlcode),20);
729 -- Bug 3718167 Added Person,SSN in the message instead of person_id
730 l_mslerrbuf := 'Error in get_sf52_loc_ddf_details Sql Err is '||
731 sqlerrm(sqlcode);
732 raise mass_error;
733 END;
734 l_ind := 80;
735
736 get_pos_grp2_ddf(l_position_id,
737 l_effective_DATE,
738 l_org_func_code,
739 l_appropriation_code1,
740 l_appropriation_code2);
741 --l_pos_grp2_rec);
742 l_ind := 90;
743 BEGIN
744 ghr_pa_requests_pkg.get_duty_station_details
745 (p_duty_station_id => l_duty_station_id
746 ,p_effective_DATE => l_effective_DATE
747 ,p_duty_station_code => l_duty_station_code
748 ,p_duty_station_desc => l_duty_station_desc);
749 EXCEPTION
750 when others then
751 pr('Error in Ghr_pa_requests_pkg.get_duty_station_details');
752 hr_utility.set_location('Error in Ghr_pa_requests_pkg.get_duty_station_details'||
753 'Err is '||sqlerrm(sqlcode),20);
754 l_mslerrbuf := 'Error in get_duty_station_details Sql Err is '||
755 sqlerrm(sqlcode);
756 raise mass_error;
757 END;
758
759 --added for bug 3191704
760 if (target_personnel_office_id is not null) then
761 t_personnel_office_id := target_personnel_office_id;
762 ELSE
763 t_personnel_office_id := l_personnel_office_id;
764 END IF;
765 --t_personnel_office_id := l_personnel_office_id;
766 t_sub_element_code := l_sub_element_code;
767 t_duty_station_id := l_duty_station_id;
768 t_duty_station_locn_id:= null;
769 t_duty_station_code := l_duty_station_code;
770 t_duty_station_desc := l_duty_station_desc;
771 t_office_symbol := l_office_symbol;
772
773 -- Developer forgot to get the payroll office id. Changed by Dinkar
774
775 t_payroll_office_id := l_pos_grp1_rec.poei_information18;
776 t_org_func_code := l_org_func_code;
777 t_appropriation_code1 := l_appropriation_code1;
778 t_appropriation_code2 := l_appropriation_code2;
779 t_position_organization := l_position_organization;
780
781 pr('Bef get new org dtls pos org is',l_position_organization,t_position_organization);
782
783 get_new_org_dtls(
784 p_mass_realignment_id,
785 l_position_id,
786 l_effective_DATE,
787 t_personnel_office_id,
788 t_sub_element_code,
789 t_duty_station_id,
790 t_duty_station_code,
791 t_duty_station_desc,
792 t_duty_station_locn_id,
793 t_office_symbol,
794 t_payroll_office_id,
795 t_org_func_code,
796 t_appropriation_code1,
797 t_appropriation_code2,
798 t_position_organization);
799 pr('after get new org dtls pos t_org is',t_position_organization);
800 pr('Duty station is '||to_char(t_duty_station_id),
801 'Code '||t_duty_station_code||
802 'Desc '||t_duty_station_desc,
803 'Locn id '||to_char(t_duty_station_locn_id));
804 l_ind := 130;
805 --Start of Bug 3944729
806 IF ( l_duty_station_id <> t_duty_station_id )
807 AND ( ghr_pay_calc.get_lpa_percentage(l_duty_station_id,l_effective_date) <>
808 ghr_pay_calc.get_lpa_percentage(t_duty_station_id,l_effective_date) )
809
810 THEN
811 -- Bug#4388288
812 g_proc := 'Invalid_Duty_Station';
813 l_mslerrbuf := 'The duty station entered results in a change in Locality Percentage.'||
814 'This change is not permitted with NOA 790 Realignment. '||
815 'Refer to OPM GPPA chapter 17 for the appropriate transaction.';
816 raise mass_error;
817
818 END IF;
819 --End of Bug 3944729
820 IF UPPER(p_action) IN ('SHOW','REPORT') THEN
821 pr('Bef create ghr cpdf temp');
822 create_mass_act_prev (
823 l_effective_DATE,
824 per.DATE_of_birth,
825 per.full_name,
826 per.national_identifier,
827 --Bug # 9329643
828 per.employee_number,
829 l_duty_station_code,
830 l_duty_station_desc,
831 l_personnel_office_id,
832 l_position_id,
833 l_position_title,
834 l_position_NUMBER,
835 l_position_seq_no,
836 l_org_structure_id,
837 l_sub_element_code,
838 per.person_id,
839 p_mass_realignment_id,
840 l_sel_flg,
841 l_grade_or_level,
842 l_step_or_rate,
843 l_pay_plan,
844 l_occ_series,
845 l_office_symbol,
846 per.organization_id,
847 per.organization_name,
848 l_position_organization,
849 t_personnel_office_id,
850 t_sub_element_code,
851 t_duty_station_id,
852 t_duty_station_code,
853 t_duty_station_desc,
854 t_office_symbol,
855 t_payroll_office_id,
856 t_org_func_code,
857 t_appropriation_code1,
858 t_appropriation_code2,
859 t_position_organization,
860 p_action,
861 l_assignment_id,
862 l_pay_rate_determinant);
863
864 l_ind := 180;
865 ELSIF UPPER(p_action) = 'CREATE' then ---- Not in Show, Report
866 pr('Bef get pay plan and table id');
867 l_ind := 190;
868 BEGIN
869 ghr_msl_pkg.get_pay_plan_and_table_id
870 (l_pay_rate_determinant,per.person_id,
871 l_position_id,l_effective_DATE,
872 l_grade_id, l_assignment_id,'CREATE',
873 l_pay_plan,l_pay_table_id,
874 l_grade_or_level, l_step_or_rate,
875 l_pay_basis);
876 EXCEPTION
877 --Bug#4179270,4126137,4086677 Added the get_message call.
878 when ghr_msl_pkg.msl_error then
879 l_mslerrbuf := hr_utility.get_message;
880 raise mass_error;
881 END;
882 l_ind := 200;
883 pr('Bef assign to sf52 rec');
884 -- assign_to_sf52_rec assigns all the following elements to a 52 record type.
885
886 assign_to_sf52_rec(
887 per.person_id,
888 per.first_name,
889 per.last_name,
890 per.middle_names,
891 per.national_identifier,
892 per.DATE_of_birth,
893 l_effective_DATE,
894 l_assignment_id,
895 l_tenure,
896 l_step_or_rate,
897 l_annuitant_indicator,
898 l_pay_rate_determinant,
899 l_work_schedule,
900 l_part_time_hour,
901 l_pos_ei_data.poei_information7, --FLSA Category
902 l_pos_ei_data.poei_information8, --Bargaining Unit Status
903 l_pos_ei_data.poei_information11,--Functional Class
904 l_pos_ei_data.poei_information16,--Supervisory Status,
905 l_personnel_office_id,
906 l_sub_element_code,
907 t_duty_station_id,
908 t_duty_station_locn_id,
909 t_duty_station_code,
910 t_duty_station_desc,
911 l_office_symbol,
912 l_payroll_office_id,
913 l_org_func_code,
914 t_appropriation_code1,
915 t_appropriation_code2,
916 l_position_organization,
917 l_lac_sf52_rec,
918 l_sf52_rec);
919
920 -- PAY CALCULATION Bug#2850747
921 ghr_msl_pkg.get_from_sf52_data_elements(l_assignment_id, l_effective_DATE,
922 l_old_basic_pay, l_old_avail_pay,
923 l_old_loc_diff, l_tot_old_sal,
924 l_old_auo_pay, l_old_adj_basic_pay,
925 l_other_pay, l_auo_premium_pay_indicator,
926 l_ap_premium_pay_indicator,
927 l_retention_allowance,
928 l_retention_allow_perc,
929 l_supervisory_differential,
930 l_supervisory_diff_perc,
931 l_staffing_differential);
932
933 l_pay_calc_in_data.person_id := per.person_id;
934 l_pay_calc_in_data.position_id := l_position_id;
935 l_pay_calc_in_data.noa_family_code := 'REALIGNMENT';
936 l_pay_calc_in_data.noa_code := '790';
937 l_pay_calc_in_data.second_noa_code := null;
938 l_pay_calc_in_data.effective_DATE := l_effective_DATE;
939 l_pay_calc_in_data.pay_rate_determinant := l_pay_rate_determinant;
940 l_pay_calc_in_data.pay_plan := l_pay_plan;
941 l_pay_calc_in_data.grade_or_level := l_grade_or_level;
942 l_pay_calc_in_data.step_or_rate := l_step_or_rate;
943 l_pay_calc_in_data.pay_basis := l_pay_basis;
944 l_pay_calc_in_data.user_table_id := l_pay_table_id;
945 l_pay_calc_in_data.duty_station_id := t_duty_station_id;
946 l_pay_calc_in_data.auo_premium_pay_indicator := l_auo_premium_pay_indicator;
947 l_pay_calc_in_data.ap_premium_pay_indicator := l_ap_premium_pay_indicator;
948 l_pay_calc_in_data.retention_allowance := l_retention_allowance;
949 l_pay_calc_in_data.to_ret_allow_percentage := l_retention_allow_perc;
950 l_pay_calc_in_data.supervisory_differential := l_supervisory_differential;
951 l_pay_calc_in_data.staffing_differential := l_staffing_differential;
952 l_pay_calc_in_data.current_basic_pay := l_old_basic_pay;
953 l_pay_calc_in_data.current_adj_basic_pay := l_old_adj_basic_pay;
954 l_pay_calc_in_data.current_step_or_rate := l_step_or_rate;
955 l_pay_calc_in_data.pa_request_id := null;
956 ---Begin Bug# 13636639
957 IF l_pay_plan ='GP' THEN
958 l_pay_calc_in_data.open_out_locality_adj := l_old_loc_diff;
959 ELSE
960 l_pay_calc_in_data.open_out_locality_adj := null;
961 END IF;
962 ---End Bug# 13636639
963
964 --BUG# 3719226 - JAN 05
965 -- IF the table is of type R then populate the basic into open_pay_basic
966 FOR pay_tab_type IN c_pay_tab_type(l_pay_table_id)
967 LOOP
968 l_table_type := pay_tab_type.range_or_match;
969 END LOOP;
970
971 IF ( l_table_type = 'R') THEN
972 l_pay_calc_in_data.open_range_out_basic_pay := l_old_basic_pay;
973 -- Bug#3968005 Added Else Condition. Setting open_range_out_basic_pay to NULL
974 -- because pay calculation will calculate values depending on this value.
975 -- See pay calculation for further details.
976 ELSE
977 l_pay_calc_in_data.open_range_out_basic_pay := NULL;
978 END IF;
979 --
980 --BUG# 3719226 - JAN 05
981 -- Bug 3663808 Need to assign retained grade pay basis for Retained grade employees
982 IF nvl(l_pay_rate_determinant,'X') in ('A','B','E','F') THEN
983 BEGIN
984 p_retained_grade :=
985 ghr_pc_basic_pay.get_retained_grade_details
986 ( per.person_id,
987 l_effective_DATE);
988 l_pay_calc_in_data.pay_basis := p_retained_grade.pay_basis;
989 hr_utility.set_location('l_pay_calc_in_data.pay_basis ' || l_pay_calc_in_data.pay_basis,1000);
990 EXCEPTION
991 WHEN OTHERS THEN
992 l_mslerrbuf := 'Preview - Others error in Get retained grade '||
993 'Error is '||' Sql Err is '|| sqlerrm(sqlcode);
994 ghr_mre_pkg.pr('Person ID '||to_char(per.person_id),'ERROR 2',l_mslerrbuf);
995 RAISE mass_error;
996 END;
997 END IF;
998
999 BEGIN
1000 ghr_pay_calc.sql_main_pay_calc (l_pay_calc_in_data
1001 ,l_pay_calc_out_data
1002 ,l_message_set
1003 ,l_calculated);
1004
1005 IF l_message_set THEN
1006 hr_utility.set_location( l_proc, 40);
1007 l_calculated := FALSE;
1008 --Begin Bug#4114068
1009 hr_utility.get_message_details(l_mslerrbuf,l_appl_id);
1010 --Bug# 10194273 added GHR_38260,38253,39261,38262
1011 IF l_mslerrbuf in ('GHR_38260_NO_CALC_PAY_PLAN', 'GHR_38254_NO_CALC_PRD','GHR_38253_NO_PAY_PLAN_VALUE',
1012 'GHR_38261_NO_CALC_FAMILY','GHR_38262_NO_CALC_PAY_BASIS') THEN
1013 raise ghr_pay_calc.unable_to_calculate;
1014 ELSE
1015 raise mass_error;
1016 END IF;
1017 ELSE
1018 -- FWFA Changes Bug#4444609 Setting Calc Pay Table ID, PRD
1019 l_sf52_rec.input_pay_rate_determinant := l_pay_rate_determinant;
1020 l_sf52_rec.from_pay_table_identifier := l_pay_calc_out_data.pay_table_id;
1021 l_sf52_rec.to_pay_table_identifier := l_pay_calc_out_data.calculation_pay_table_id;
1022 -- FWFA Changes
1023 l_new_basic_pay := l_pay_calc_out_data.basic_pay;
1024 l_new_locality_adj := l_pay_calc_out_data.locality_adj;
1025 l_new_adj_basic_pay := l_pay_calc_out_data.adj_basic_pay;
1026 l_new_au_overtime := l_pay_calc_out_data.au_overtime;
1027 l_new_availability_pay := l_pay_calc_out_data.availability_pay;
1028 l_out_pay_rate_determinant := l_pay_calc_out_data.out_pay_rate_determinant;
1029 l_new_retention_allowance := l_pay_calc_out_data.retention_allowance;
1030 l_new_supervisory_differential := l_supervisory_differential;
1031 l_new_other_pay_amount := l_pay_calc_out_data.other_pay_amount;
1032 l_entitled_other_pay := l_new_other_pay_amount;
1033
1034 if l_new_other_pay_amount = 0 then
1035 l_new_other_pay_amount := null;
1036 END IF;
1037 l_new_total_salary := l_pay_calc_out_data.total_salary;
1038 END IF;
1039 EXCEPTION
1040 when mass_error then
1041 raise;
1042 when ghr_pay_calc.unable_to_calculate THEN
1043 l_new_basic_pay := l_pay_calc_in_data.current_basic_pay;
1044 l_new_locality_adj := l_pay_calc_in_data.current_adj_basic_pay - l_pay_calc_in_data.current_basic_pay ;
1045 l_new_adj_basic_pay := l_pay_calc_in_data.current_adj_basic_pay ;
1046 l_new_au_overtime := ghr_pay_calc.get_ppi_amount (l_pay_calc_in_data.auo_premium_pay_indicator
1047 ,l_pay_calc_in_data.current_adj_basic_pay
1048 ,l_pay_calc_in_data.pay_basis);
1049 l_new_availability_pay := ghr_pay_calc.get_ppi_amount (l_pay_calc_in_data.ap_premium_pay_indicator
1050 ,l_pay_calc_in_data.current_adj_basic_pay
1051 ,l_pay_calc_in_data.pay_basis);
1052
1053 l_out_pay_rate_determinant := l_pay_calc_in_data.pay_rate_determinant;
1054 l_new_retention_allowance := l_pay_calc_in_data.retention_allowance;
1055 l_new_supervisory_differential := l_supervisory_differential;
1056 l_new_other_pay_amount := NVL(l_new_au_overtime,0) +
1057 NVL(l_new_availability_pay,0) +
1058 NVL(l_new_retention_allowance,0) +
1059 NVL(l_new_supervisory_differential,0);
1060 l_entitled_other_pay := l_new_other_pay_amount;
1061 l_new_total_salary := NVL(l_new_adj_basic_pay,0) +
1062 NVL(l_new_other_pay_amount,0);
1063 if l_new_other_pay_amount = 0 then
1064 l_new_other_pay_amount := null;
1065 END IF;
1066 --end Bug#4114068
1067 when others then
1068 hr_utility.set_location('Error in Ghr_pay_calc.sql_main_pay_calc '||
1069 'Err is '||sqlerrm(sqlcode),20);
1070 l_mslerrbuf := 'Error in ghr_pay_calc Sql Err is '||
1071 sqlerrm(sqlcode);
1072 raise mass_error;
1073 END;
1074
1075 --Call Pay cap Procedure
1076 BEGIN
1077 l_capped_other_pay := ghr_pa_requests_pkg2.get_cop( p_assignment_id => l_assignment_id
1078 ,p_effective_DATE => l_effective_DATE);
1079 ghr_pay_caps.do_pay_caps_main
1080 (p_pa_request_id => null
1081 ,p_effective_DATE => l_effective_DATE
1082 ,p_duty_station_id => t_duty_station_id --bug# 13414643,13403289
1083 ,p_pay_rate_determinant => nvl(l_out_pay_rate_determinant,l_pay_rate_determinant)
1084 ,p_pay_plan => l_pay_plan
1085 ,p_to_position_id => l_position_id
1086 ,p_pay_basis => l_pay_basis
1087 ,p_person_id => per.person_id
1088 ,p_noa_code => '790'
1089 ,p_basic_pay => l_new_basic_pay
1090 ,p_locality_adj => l_new_locality_adj
1091 ,p_adj_basic_pay => l_new_adj_basic_pay
1092 ,p_total_salary => l_new_total_salary
1093 ,p_other_pay_amount => l_entitled_other_pay
1094 ,p_capped_other_pay => l_capped_other_pay
1095 ,p_retention_allowance => l_new_retention_allowance
1096 ,p_retention_allow_percentage => l_retention_allow_perc
1097 ,p_supervisory_allowance => l_new_supervisory_differential
1098 ,p_staffing_differential => l_staffing_differential
1099 ,p_au_overtime => l_new_au_overtime
1100 ,p_availability_pay => l_new_availability_pay
1101 ,p_adj_basic_message => l_adj_basic_message
1102 ,p_pay_cap_message => l_pay_cap_message
1103 ,p_pay_cap_adj => l_temp_retention_allowance
1104 ,p_open_pay_fields => l_open_pay_fields_caps
1105 ,p_message_set => l_message_set_caps
1106 ,p_total_pay_check => l_total_pay_check);
1107
1108 l_new_other_pay_amount := nvl(l_capped_other_pay,l_entitled_other_pay);
1109
1110 if l_pay_cap_message then
1111 if nvl(l_temp_retention_allowance,0) > 0 then
1112 l_comment := 'Exceeded Total Salary cap - reduce Retention Allowance to '
1113 || to_char(l_temp_retention_allowance);
1114 l_pay_sel := 'N';
1115 ELSE
1116 l_comment := 'Exceeded Total Salary cap - please review';
1117 END IF;
1118 ELSIF l_adj_basic_message then
1119 l_comment := 'Exceeded Adjusted Basic Pay cap - Locality Pay has been reduced';
1120 END IF;
1121
1122 if l_pay_cap_message or l_adj_basic_message then
1123 --Bug#3225758
1124 /* Commented the below call as MRE comments needs to be stored
1125 in position extra info not in Person Extra Info.
1126 */
1127 /*
1128 ghr_msl_pkg.ins_upd_per_extra_info
1129 (per.person_id,l_effective_DATE, l_pay_sel, l_comment,p_mass_realignment_id);
1130 */
1131 ins_upd_pos_extra_info(l_position_id,l_effective_DATE,'Y', l_comment, p_mass_realignment_id );
1132 l_comment := null;
1133 END IF;
1134
1135 EXCEPTION
1136 when mass_error then
1137 raise;
1138 when others then
1139 hr_utility.set_location('Error in ghr_pay_caps.do_pay_caps_main ' ||
1140 'Err is '||sqlerrm(sqlcode),23);
1141 l_mslerrbuf := 'Error in do_pay_caps_main Sql Err is '|| sqlerrm(sqlcode);
1142 raise mass_error;
1143 END;
1144
1145 l_sf52_rec.to_basic_pay := l_new_basic_pay;
1146 l_sf52_rec.to_locality_adj := l_new_locality_adj;
1147 l_sf52_rec.to_adj_basic_pay := l_new_adj_basic_pay;
1148 l_sf52_rec.to_au_overtime := l_new_au_overtime;
1149 l_sf52_rec.to_availability_pay := l_new_availability_pay;
1150 l_sf52_rec.pay_rate_determinant := NVL(l_out_pay_rate_determinant,l_pay_rate_determinant);
1151 l_sf52_rec.to_retention_allowance := l_new_retention_allowance;
1152 l_sf52_rec.to_supervisory_differential := l_new_supervisory_differential;
1153 l_sf52_rec.to_other_pay_amount := l_new_other_pay_amount;
1154 l_sf52_rec.to_total_salary := l_new_total_salary;
1155
1156
1157 -- Bug#2850747 End of PAY CALCULATIONPAY CALCULATION
1158
1159 pr('Bef create sf52 for mass chgs');
1160
1161 -- This procedure will create 52s for doing a NOA 790 for realignment.
1162 -- The record will accept a IN/OUT p_pa_request_rec variable.
1163 -- Once the 52 is created, it returns the PA request id and
1164 -- we create the PA request extra INFO for GHR_PAR_REALIGNMENT which
1165 -- is necessary process method for UPDATE HR. Pay calc is not run
1166 -- as no values except for organization will change for the position.
1167
1168 BEGIN
1169 if l_sf52_rec.person_id is null then
1170 l_mslerrbuf := 'Error before create sf52 : PERSON ID is NULL';
1171 raise mass_error;
1172 END IF;
1173 -- Adding the following code to keep track of the RPA type and Mass action id
1174 --
1175 l_sf52_rec.rpa_type := 'MRE';
1176 l_sf52_rec.mass_action_id := p_mass_realignment_id;
1177 --
1178 --
1179
1180 ghr_mass_changes.create_sf52_for_mass_changes
1181 (p_mass_action_type => 'MASS_REALIGNMENT',
1182 p_pa_request_rec => l_sf52_rec,
1183 p_errbuf => l_errbuf,
1184 p_retcode => l_retcode);
1185
1186 pr('Create sf52 success');
1187
1188 if l_errbuf is null then
1189 pr('No error in create sf52 sel flg is '||l_sel_flg);
1190 hr_utility.set_location('Before COMMITing',2);
1191
1192 ghr_mto_int.log_message(
1193 p_procedure => 'Successful Completion',
1194 p_message => 'Name: '||per.full_name ||
1195 ' Emp No: ' || per.employee_number||
1196 ' Mass realignment : '||
1197 p_mass_realignment_name ||' SF52 Successfully completed');
1198
1199 ghr_msl_pkg.create_lac_remarks(l_pa_request_id,
1200 l_sf52_rec.pa_request_id);
1201 upd_ext_info_to_null(per.position_id,l_effective_DATE);
1202 COMMIT;
1203 ELSE
1204 pr('Error in create sf52',l_errbuf);
1205 hr_utility.set_location('Error in '||to_char(per.position_id),20);
1206 l_mslerrbuf := 'Error in create sf52 '|| l_errbuf;
1207 --l_recs_failed := l_recs_failed + 1;
1208 raise mass_error;
1209 END IF;
1210 EXCEPTION
1211 when mass_error then
1212 raise;
1213 when others then
1214 null;
1215 l_mslerrbuf := 'Error in ghr_mass_chg.create_sf52 '||
1216 ' Sql Err is '|| sqlerrm(sqlcode);
1217 pr('Error ---> create sf52 Err is '||
1218 l_errbuf||' '||to_char(l_retcode));
1219 pr('Err is '||sqlerrm(sqlcode));
1220 raise mass_error;
1221 END;
1222
1223 -------------Added by Dinkar for creation of 52s-----------------------
1224
1225 pr('Bef hist fetch');
1226 BEGIN
1227 ghr_history_fetch.fetch_asgei
1228 ( p_assignment_id => l_assignment_id,
1229 p_information_type => 'GHR_US_ASG_NON_SF52',
1230 p_DATE_effective => l_effective_DATE,
1231 p_asg_ei_data => l_asg_extra_info_rec
1232 );
1233 EXCEPTION
1234 when others then
1235 null;
1236 pr('Error in create sf52 3- Err is '||
1237 l_errbuf||' '||to_char(l_retcode));
1238 pr('Err is '||sqlerrm(sqlcode));
1239 l_mslerrbuf := 'Error after fetch asgei'||
1240 ' Sql Err is '|| sqlerrm(sqlcode);
1241 raise mass_error;
1242 END;
1243
1244 pr('Bef create pa req ext info');
1245 BEGIN
1246 -- Bug#2651909 Getting target organization's OPM Organizational Component
1247 for c_opm_org_rec in c_opm_org_component(p_new_organization_id)
1248 loop
1249 p_new_org_structure_id := c_opm_org_rec.org_information4;
1250 END loop;
1251 -- Bug#2651909
1252
1253 ghr_par_extra_info_api.create_pa_request_extra_info
1254 (p_valiDATE => false,
1255 p_pa_request_id => l_sf52_rec.pa_request_id,
1256 p_information_type => 'GHR_US_PAR_REALIGNMENT',
1257 p_rei_information_category => 'GHR_US_PAR_REALIGNMENT',
1258 p_rei_information3 => l_asg_extra_info_rec.aei_information3,
1259 p_rei_information4 => t_payroll_office_id,
1260 p_rei_information5 => t_personnel_office_id,
1261 p_rei_information6 => t_office_symbol,
1262 p_rei_information7 => t_org_func_code,
1263 p_rei_information8 => t_position_organization,
1264 p_rei_information9 => p_new_organization_id,
1265 p_rei_information10 => t_sub_element_code,
1266 p_rei_information11 => p_new_org_structure_id,
1267 p_pa_request_extra_info_id => l_dummy,
1268 p_object_version_NUMBER => l_dummy
1269 );
1270 EXCEPTION
1271 when others then
1272 null;
1273 pr('Error in create sf52 2- Err is '||
1274 l_errbuf||' '||to_char(l_retcode));
1275 pr('Err is '||sqlerrm(sqlcode));
1276 l_mslerrbuf := 'Error in creating PAR DDF '||
1277 ' Sql Err is '|| sqlerrm(sqlcode);
1278 raise mass_error;
1279 END;
1280 ---------------------------End of Dinkar's addition-----------------------
1281
1282 -- We call the package that Sue Grant has written for checking the
1283 -- org id in the GHR_PAR_REALIGNMENT DDF and if there is one it returns
1284 -- the 6 lines of address, and I would UPDATE the PA request with it.
1285
1286 DECLARE
1287 l_organization_id VARCHAR2(15);
1288 l_position_org_line1 VARCHAR2(40);
1289 l_position_org_line2 VARCHAR2(40);
1290 l_position_org_line3 VARCHAR2(40);
1291 l_position_org_line4 VARCHAR2(40);
1292 l_position_org_line5 VARCHAR2(40);
1293 l_position_org_line6 VARCHAR2(40);
1294 l_par_object_version_NUMBER NUMBER := l_sf52_rec.object_version_NUMBER;
1295 l_dummy NUMBER;
1296 l_personnel_officer_name per_people_f.full_name%type;
1297 l_approving_off_work_title ghr_pa_requests.APPROVING_OFFICIAL_WORK_TITLE%type;
1298 BEGIN
1299 ghr_pa_requests_pkg.get_rei_org_lines(
1300 p_pa_request_id => l_sf52_rec.pa_request_id,
1301 p_organization_id => l_organization_id,
1302 p_position_org_line1 => l_position_org_line1,
1303 p_position_org_line2 => l_position_org_line2,
1304 p_position_org_line3 => l_position_org_line3,
1305 p_position_org_line4 => l_position_org_line4,
1306 p_position_org_line5 => l_position_org_line5,
1307 p_position_org_line6 => l_position_org_line6
1308 );
1309 -- Combined 6 lines of Org thing with Electronic to just do only one
1310 -- UpDATE.
1311 -- This addition is for Electronic Signature while doing a realignment
1312 -- and there is a change in the POI
1313
1314 ghr_mass_actions_pkg.get_personnel_officer_name
1315 (p_personnel_office_id => t_personnel_office_id,
1316 p_person_full_name => l_personnel_officer_name,
1317 p_approving_off_work_title => l_approving_off_work_title);
1318
1319 -- If the organization id is not null then we UPDATE the PA Request record
1320 -- to position org line1 to line 6 with the address information
1321 -- returned by the above function.
1322 -- If the Organization id is Null then there is some problem.
1323
1324 IF l_organization_id is NOT NULL THEN
1325 ghr_par_upd.upd(
1326 p_pa_request_id => l_sf52_rec.pa_request_id
1327 ,p_to_position_org_line1 => l_position_org_line1
1328 ,p_to_position_org_line2 => l_position_org_line2
1329 ,p_to_position_org_line3 => l_position_org_line3
1330 ,p_to_position_org_line4 => l_position_org_line4
1331 ,p_to_position_org_line5 => l_position_org_line5
1332 ,p_to_position_org_line6 => l_position_org_line6
1333 ,p_approving_official_full_name => l_personnel_officer_name
1334 ,p_approving_official_work_titl => l_approving_off_work_title
1335 ,p_object_version_NUMBER => l_par_object_version_NUMBER);
1336 END IF;
1337
1338 -- Added by Dinkar for reports
1339 declare
1340 l_pa_request_NUMBER ghr_pa_requests.request_NUMBER%TYPE;
1341 --
1342 BEGIN
1343 l_pa_request_NUMBER :=
1344 l_sf52_rec.request_NUMBER||'-'||p_mass_realignment_id;
1345
1346 ghr_par_upd.upd
1347 (p_pa_request_id => l_sf52_rec.pa_request_id,
1348 p_object_version_NUMBER => l_par_object_version_NUMBER,
1349 p_request_NUMBER => l_pa_request_NUMBER
1350 );
1351 END;
1352 EXCEPTION
1353 WHEN OTHERS THEN
1354 null;
1355 pr('Error in fetch/UPDATE of 6 lines of pos org'||
1356 l_errbuf||' '||to_char(l_retcode));
1357 pr('Err is '||sqlerrm(sqlcode));
1358 l_mslerrbuf := 'Error while fetching/updating 6 lines of
1359 org info and/or Elec. Authentication'||
1360 ' Sql Err is '|| sqlerrm(sqlcode);
1361 raise mass_error;
1362 END; -- End of Sub block of 6 lines of positions org UPDATE.
1363
1364 l_ind := 230;
1365 END IF; ---- End if for p_action = 'CREATE' ----
1366 END IF; --- End if for Check Eligibility ----
1367 ELSE ------ Else for Check Select flag ----
1368 l_ind := 260;
1369 --UPDATE_SEL_FLG(PER.PERSON_ID,l_effective_DATE);
1370 null; ---Commented needs to check
1371 END IF; ---- End if for check SELECT flag ----
1372 END IF; ---- End if for p_action
1373 l_ind := 270;
1374 L_row_cnt := L_row_cnt + 1;
1375 l_mass_cnt := l_mass_cnt +1;
1376 if UPPER(p_action) <> 'CREATE' THEN
1377 if L_row_cnt > 50 then
1378 COMMIT;
1379 L_row_cnt := 0;
1380 END IF;
1381 END IF;
1382 EXCEPTION
1383 WHEN mass_ERROR THEN
1384 HR_UTILITY.SET_LOCATION('Error occurred in '||l_proc||' Sql error '||sqlerrm(sqlcode),10);
1385
1386 BEGIN
1387 ROLLBACK TO EXECUTE_MRE_SP;
1388 EXCEPTION
1389 WHEN OTHERS THEN NULL;
1390 END;
1391
1392 p_retcode := 2;
1393 l_log_text := 'Error in '||l_proc||' '||
1394 ' For Mass Realignment Name : '||p_mass_realignment_name||
1395 ' for Name : '||per.full_name ||
1396 ' Emp No: ' || per.employee_number||
1397 l_mslerrbuf;
1398 hr_utility.set_location('before creating entry in log file',10);
1399 l_recs_failed := l_recs_failed + 1;
1400
1401 BEGIN
1402 ghr_mto_int.log_message(
1403 p_procedure => g_proc,
1404 p_message => l_log_text);
1405 COMMIT;
1406 EXCEPTION
1407 when others then
1408 hr_utility.set_message(8301, 'GHR_38475_ERROR_LOG_FAILURE');
1409 hr_utility.raise_error;
1410 END;
1411
1412 --6997689
1413 BEGIN
1414 l_comment := NULL;
1415 if UPPER(p_action) <> 'CREATE' THEN
1416 ins_upd_pos_extra_info(l_position_id,l_effective_DATE,'N', l_comment, p_mass_realignment_id );
1417 COMMIT;
1418 end if;
1419 EXCEPTION
1420 when others then
1421 null;
1422 END;
1423 --6997689
1424 WHEN others then
1425 hr_utility.set_location('Error (Others) occurred in '||l_proc||
1426 ' Sql error '||sqlerrm(sqlcode),20);
1427
1428 BEGIN
1429 ROLLBACK TO EXECUTE_MRE_SP;
1430 EXCEPTION
1431 WHEN OTHERS THEN NULL;
1432 END;
1433
1434 l_log_text := 'Error (others) in '||l_proc||
1435 'Line is '|| to_char(l_ind)||
1436 ' For Mass Realignment Name : '||p_mass_realignment_name||
1437 ' for Name : '||per.full_name ||
1438 ' Emp No: ' || per.employee_number||
1439 ' Sql Err is '||sqlerrm(sqlcode);
1440 hr_utility.set_location('before creating entry in log file',20);
1441 l_recs_failed := l_recs_failed + 1;
1442 p_retcode := 2;
1443 p_errbuf := 'Error in '||l_proc || ' Details in GHR_PROCESS_LOG';
1444
1445 BEGIN
1446 ghr_mto_int.log_message(
1447 p_procedure => g_proc,
1448 p_message => l_log_text);
1449 COMMIT;
1450 EXCEPTION
1451 when others then
1452 hr_utility.set_message(8301, 'Create Error Log failed');
1453 hr_utility.raise_error;
1454 END;
1455 END;
1456 END IF;
1457 END LOOP;
1458
1459 if UPPER(p_action) = 'SHOW'
1460 or (UPPER(p_action) = 'REPORT' and p_show_vacant_pos = 'YES' ) THEN
1461 FOR per IN unassigned_pos (org.org_pos_id,
1462 l_check_org_pos,
1463 l_effective_DATE)
1464 LOOP
1465 l_avail_status_id := per.availability_status_id;
1466
1467 --Begin Bug# 13684234
1468 /* commented below code and added func get_pos_availability_status, since
1469 hr_general.decode_availability_status is return pos_aval_status description not the system code
1470 IF ( HR_GENERAL.DECODE_AVAILABILITY_STATUS(l_avail_status_id)
1471 not in ('Eliminated','Frozen','Deleted') ) THEN*/
1472
1473 IF ghr_pa_requests_pkg.get_pos_availability_status(l_avail_status_id)
1474 NOT IN ('DELETED','FROZEN','ELIMINATED') THEN
1475 --End Bug# 13684234
1476
1477 l_position_id := per.position_id;
1478 ghr_history_fetch.fetch_positionei
1479 (p_position_id => l_position_id
1480 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
1481 ,p_DATE_effective => l_effective_DATE
1482 ,p_pos_ei_data => l_pos_valid_grade_ei_data
1483 );
1484 l_grade_id := l_pos_valid_grade_ei_data.poei_information3;
1485 l_business_group_id := per.business_group_iD;
1486 l_location_id := per.location_id;
1487
1488 if check_SELECT_flg(per.position_id,UPPER(p_action),
1489 l_effective_DATE,
1490 p_mass_realignment_id,
1491 l_sel_flg) then
1492 pr('After check sel flg value is ',l_sel_flg,l_sel_status);
1493 null;
1494 END IF;
1495 l_position_title := ghr_api.get_position_title_pos
1496 (p_position_id => l_position_id
1497 ,p_business_group_id => l_business_group_id ) ;
1498
1499 l_sub_element_code := ghr_api.get_position_agency_code_pos
1500 (l_position_id,l_business_group_id);
1501
1502 l_occ_series := ghr_api.get_job_occ_series_job
1503 (p_job_id => per.job_id
1504 ,p_business_group_id => per.business_group_id
1505 );
1506
1507 l_position_NUMBER := ghr_api.get_position_desc_no_pos
1508 (p_position_id => l_position_id
1509 ,p_business_group_id => per.business_group_id
1510 );
1511
1512 l_position_seq_no := ghr_api.get_position_sequence_no_pos
1513 (p_position_id => l_position_id
1514 ,p_business_group_id => per.business_group_id
1515 );
1516
1517 FOR c_grade_kff_rec IN c_grade_kff (l_grade_id)
1518 LOOP
1519 l_pay_plan := c_grade_kff_rec.segment1;
1520 l_grade_or_level := c_grade_kff_rec.segment2;
1521 exit;
1522 END loop;
1523
1524 get_pos_grp1_ddf(l_position_id,
1525 l_effective_DATE,
1526 l_personnel_office_id,
1527 l_org_structure_id,
1528 l_office_symbol,
1529 l_position_organization,
1530 l_pos_grp1_rec);
1531
1532 get_pos_grp2_ddf(l_position_id,
1533 l_effective_DATE,
1534 l_org_func_code,
1535 l_appropriation_code1,
1536 l_appropriation_code2);
1537
1538 BEGIN
1539 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
1540 (p_location_id => l_location_id
1541 ,p_duty_station_id => l_duty_station_id);
1542 END;
1543
1544 BEGIN
1545 ghr_pa_requests_pkg.get_duty_station_details
1546 (p_duty_station_id => l_duty_station_id
1547 ,p_effective_DATE => l_effective_DATE
1548 ,p_duty_station_code => l_duty_station_code
1549 ,p_duty_station_desc => l_duty_station_desc);
1550 END;
1551
1552 IF check_eligibility(
1553 p_org_structure_id,
1554 p_office_symbol,
1555 p_personnel_office_id,
1556 p_agency_sub_elem_code,
1557
1558 l_org_structure_id,
1559 l_office_symbol,
1560 l_personnel_office_id,
1561 l_sub_element_code,
1562 null,
1563 l_effective_DATE,
1564 UPPER(p_action)) THEN
1565
1566 t_personnel_office_id := l_personnel_office_id;
1567 t_sub_element_code := l_sub_element_code;
1568 t_duty_station_id := l_duty_station_id;
1569 t_duty_station_locn_id:= null;
1570 t_duty_station_code := l_duty_station_code;
1571 t_duty_station_desc := l_duty_station_desc;
1572 t_office_symbol := l_office_symbol;
1573 --t_payroll_office_id := l_payroll_office_id;
1574 t_payroll_office_id := l_pos_grp1_rec.poei_information18;
1575 t_org_func_code := l_org_func_code;
1576 t_appropriation_code1 := l_appropriation_code1;
1577 t_appropriation_code2 := l_appropriation_code2;
1578 t_position_organization := l_position_organization;
1579
1580 pr('Bef get new org dtls pos org is',l_position_organization,t_position_organization);
1581
1582 get_new_org_dtls(
1583 p_mass_realignment_id,
1584 l_position_id,
1585 l_effective_DATE,
1586 t_personnel_office_id,
1587 t_sub_element_code,
1588 t_duty_station_id,
1589 t_duty_station_code,
1590 t_duty_station_desc,
1591 t_duty_station_locn_id,
1592 t_office_symbol,
1593 t_payroll_office_id,
1594 t_org_func_code,
1595 t_appropriation_code1,
1596 t_appropriation_code2,
1597 t_position_organization);
1598
1599 --Start of Bug 3944729
1600 IF ( l_duty_station_id <> t_duty_station_id )
1601 AND ( ghr_pay_calc.get_lpa_percentage(l_duty_station_id,l_effective_date) <>
1602 ghr_pay_calc.get_lpa_percentage(t_duty_station_id,l_effective_date) )
1603
1604 THEN
1605 l_log_text := 'Error in MRE: '||p_mass_realignment_name||
1606 ' for Vacant Position : '||l_position_title||'.'||l_position_NUMBER
1607 ||'.'||l_position_seq_no||'.'||l_sub_element_code||'. Error: ';
1608 l_log_text := l_log_text||'The duty station entered results in a change in Locality Percentage.'||
1609 'This change is not permitted with NOA 790 Realignment. '||
1610 'Refer to OPM GPPA chapter 17 for the appropriate transaction.';
1611 BEGIN
1612 ghr_mto_int.log_message(
1613 p_procedure => 'Invalid_Duty_Station',
1614 p_message => l_log_text);
1615 EXCEPTION
1616 when others then
1617 hr_utility.set_message(8301, 'GHR_38475_ERROR_LOG_FAILURE');
1618 hr_utility.raise_error;
1619 END;
1620 l_log_text := NULL;
1621 l_recs_failed := l_recs_failed + 1;
1622 ELSE
1623 pr(' Assign Id/Pos ',null, to_char(per.position_id));
1624 create_mass_act_prev (
1625 l_effective_DATE,
1626 per.DATE_of_birth,
1627 per.full_name,
1628 per.national_identifier,
1629 --Bug# 9329643
1630 per.employee_number,
1631 l_duty_station_code,
1632 l_duty_station_desc,
1633 l_personnel_office_id,
1634 l_position_id,
1635 l_position_title,
1636 l_position_NUMBER,
1637 l_position_seq_no,
1638 l_org_structure_id,
1639 l_sub_element_code,
1640 per.person_id,
1641 p_mass_realignment_id,
1642 l_sel_flg,
1643 l_grade_or_level,
1644 null, ---l_step_or_rate,
1645 l_pay_plan,
1646 l_occ_series,
1647 l_office_symbol,
1648 per.organization_id,
1649 per.organization_name,
1650 l_position_organization,
1651
1652 t_personnel_office_id,
1653 t_sub_element_code,
1654 t_duty_station_id,
1655 t_duty_station_code,
1656 t_duty_station_desc,
1657 t_office_symbol,
1658 t_payroll_office_id,
1659 t_org_func_code,
1660 t_appropriation_code1,
1661 t_appropriation_code2,
1662 t_position_organization,
1663 p_action,
1664 null,null);
1665 END IF;
1666 --End of Bug 3944729
1667 END IF; ---- Check eligibility
1668 l_mass_cnt := l_mass_cnt +1;
1669 END IF; -- End of If which checks for Availability Status
1670 END LOOP;
1671 END IF; --- End for unass - p_action = show/report
1672
1673 ----------------------------
1674 if UPPER(p_action) = 'CREATE' then
1675 --- For all the vacant positions. Once this program is called with
1676 -- CREATE Option. The positions will be realigned. No 52s will be created
1677 -- and it is agreed in the design review meeting by MACROSS and JMACGOY.
1678
1679 DECLARE
1680 l_new_position_id hr_positions_f.position_id%TYPE;
1681 l_position_data_rec ghr_sf52_pos_UPDATE.position_data_rec_type;
1682 l_pos_ei_data_rec per_position_extra_info%rowtype;
1683 l_new_pos_grp1_rec per_position_extra_info%rowtype;
1684 l_target_personnel_office_id per_position_extra_info.poei_information1%TYPE;
1685 l_target_agency_code per_position_extra_info.poei_information1%TYPE;
1686 l_target_office_symbol per_position_extra_info.poei_information1%TYPE;
1687 l_target_payroll_office_id per_position_extra_info.poei_information1%TYPE;
1688 l_target_org_func_code per_position_extra_info.poei_information1%TYPE;
1689 l_target_appropriation_code1 per_position_extra_info.poei_information1%TYPE;
1690 l_target_appropriation_code2 per_position_extra_info.poei_information1%TYPE;
1691 l_target_position_organization per_position_extra_info.poei_information1%TYPE;
1692 -- We don't need Duty Station for vacant positions
1693 l_target_dummy1 per_position_extra_info.poei_information1%TYPE;
1694 l_target_dummy2 per_position_extra_info.poei_information1%TYPE;
1695 --Begin Bug# 4648802
1696 l_target_dummy3 per_position_extra_info.poei_information1%TYPE;
1697 --End Bug# 4648802
1698 l_pos_business_group_id NUMBER;
1699 l_target_duty_station_locn_id NUMBER(15); -- Bug 3490826
1700
1701 BEGIN
1702 FOR per_vacant IN unassigned_pos (org.org_pos_id,
1703 l_check_org_pos,
1704 l_effective_DATE)
1705 LOOP
1706 --Bug # 13872029 added for exception
1707 BEGIN
1708 l_avail_status_id := per_vacant.availability_status_id;
1709 --Begin Bug# 13684234
1710 /* commented below code and added func get_pos_availability_status, since
1711 hr_general.decode_availability_status is return pos_aval_status description not the system code
1712 IF ( HR_GENERAL.DECODE_AVAILABILITY_STATUS(l_avail_status_id)
1713 not in ('Eliminated','Frozen','Deleted') ) THEN*/
1714 IF ghr_pa_requests_pkg.get_pos_availability_status(l_avail_status_id)
1715 NOT IN ('DELETED','FROZEN','ELIMINATED') THEN
1716 --End Bug# 13684234
1717
1718 IF check_SELECT_flg(per_vacant.position_id,UPPER(p_action),
1719 l_effective_DATE,
1720 p_mass_realignment_id,
1721 l_sel_flg) then
1722 l_new_position_id := per_vacant.position_id;
1723 l_position_data_rec.position_id := l_new_position_id;
1724 l_position_data_rec.effective_DATE := l_effective_DATE;
1725 l_position_data_rec.organization_id := p_new_organization_id;
1726 l_pos_business_group_id := per_vacant.business_group_id;
1727 l_target_agency_code := ghr_api.get_position_agency_code_pos(l_new_position_id,l_pos_business_group_id);
1728 l_position_title := ghr_api.get_position_title_pos(p_position_id => l_new_position_id
1729 ,p_business_group_id => l_pos_business_group_id ) ;
1730
1731 -----Added by AVR for checking the eligibility of Vacant Position
1732 get_pos_grp1_ddf(l_new_position_id,
1733 l_effective_DATE,
1734 l_personnel_office_id,
1735 l_org_structure_id,
1736 l_office_symbol,
1737 l_position_organization,
1738 l_pos_grp1_rec);
1739
1740 l_sub_element_code := ghr_api.get_position_agency_code_pos
1741 (l_new_position_id,l_pos_business_group_id);
1742
1743 -- Bug#4388288 Added the following..
1744 l_position_NUMBER := ghr_api.get_position_desc_no_pos
1745 (p_position_id => l_new_position_id
1746 ,p_business_group_id => l_pos_business_group_id);
1747
1748 l_position_seq_no := ghr_api.get_position_sequence_no_pos
1749 (p_position_id => l_new_position_id
1750 ,p_business_group_id => l_pos_business_group_id
1751 );
1752
1753 l_location_id := per_vacant.location_id;
1754
1755 --BUG #13872029 Added Exception procedure wise
1756 BEGIN
1757 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
1758 (p_location_id => l_location_id
1759 ,p_duty_station_id => l_duty_station_id);
1760 EXCEPTION
1761 WHEN OTHERS THEN
1762 pr('Error in get_SF52_loc_ddf_details is '||
1763 l_errbuf||' '||to_char(l_retcode));
1764 pr('Err is '||sqlerrm(sqlcode));
1765 l_mslerrbuf := 'Error in Fetching Location details'||
1766 ' Sql Err is '|| sqlerrm(sqlcode);
1767 raise mass_error;
1768 END;
1769
1770 --BUG #13872029 Added Exception procedure wise
1771 BEGIN
1772 ghr_pa_requests_pkg.get_duty_station_details
1773 (p_duty_station_id => l_duty_station_id
1774 ,p_effective_DATE => l_effective_DATE
1775 ,p_duty_station_code => l_duty_station_code
1776 ,p_duty_station_desc => l_duty_station_desc);
1777 EXCEPTION
1778 WHEN OTHERS THEN
1779 pr('Error in get_duty_station_details is '||
1780 l_errbuf||' '||to_char(l_retcode));
1781 pr('Err is '||sqlerrm(sqlcode));
1782 l_mslerrbuf := 'Error in Fetching Dutystation details'||
1783 ' Sql Err is '|| sqlerrm(sqlcode);
1784 raise mass_error;
1785 END;
1786 -- Bug#4388288
1787
1788 hr_utility.set_location('Vac.POS-l_position_title ' || l_position_title,5);
1789 hr_utility.set_location('Vac.POS-l_personnel_office_id ' || l_personnel_office_id,5);
1790 hr_utility.set_location('Vac.POS-l_org_structure_id ' || l_org_structure_id,5);
1791 hr_utility.set_location('Vac.POS-l_office_symbol ' || l_office_symbol,5);
1792 hr_utility.set_location('Vac.POS-l_sub_element_code ' || l_sub_element_code,5);
1793
1794 IF check_eligibility(
1795 p_org_structure_id,
1796 p_office_symbol,
1797 p_personnel_office_id,
1798 p_agency_sub_elem_code,
1799 l_org_structure_id,
1800 l_office_symbol,
1801 l_personnel_office_id,
1802 l_sub_element_code,
1803 null,
1804 l_effective_DATE,
1805 null) THEN
1806 -----AVR
1807 hr_utility.set_location('Vac Pos Selected ' || l_position_title,5);
1808 ---dbms_output.put_line('After assigning agency code');
1809 -- Added by Dinkar for Updating Position details for vacant positions
1810 -- Getting Pos group2 data.
1811
1812 --BUG #13872029 Added Exception procedure wise
1813 BEGIN
1814 ghr_history_fetch.fetch_positionei
1815 (p_position_id => l_new_position_id
1816 ,p_information_type => 'GHR_US_POS_GRP2'
1817 ,p_DATE_effective => l_effective_DATE
1818 ,p_pos_ei_data => l_pos_ei_data_rec);
1819 EXCEPTION
1820 WHEN OTHERS THEN
1821 pr('Error in fetch_positionei is '||
1822 l_errbuf||' '||to_char(l_retcode));
1823 pr('Err is '||sqlerrm(sqlcode));
1824 l_mslerrbuf := 'Error in Fetching Position Group2 details'||
1825 ' Sql Err is '|| sqlerrm(sqlcode);
1826 raise mass_error;
1827 END;
1828
1829 l_target_org_func_code := l_pos_ei_data_rec.poei_information4;
1830 l_target_appropriation_code1 := l_pos_ei_data_rec.poei_information13;
1831 l_target_appropriation_code2 := l_pos_ei_data_rec.poei_information14;
1832
1833 get_pos_grp1_ddf(l_new_position_id,
1834 l_effective_DATE,
1835 l_target_personnel_office_id,
1836 l_target_dummy1,
1837 l_target_office_symbol,
1838 l_target_position_organization,
1839 l_new_pos_grp1_rec);
1840
1841 l_target_payroll_office_id := l_new_pos_grp1_rec.poei_information18;
1842 -- Bug#4388288 Added the following assignment statement.
1843 t_duty_station_id := l_duty_station_id;
1844 --BUG #13872029 Added Exception procedure wise
1845 BEGIN
1846 get_new_org_dtls(p_mass_realignment_id => p_mass_realignment_id,
1847 p_position_id => l_new_position_id,
1848 p_effective_DATE => l_effective_DATE,
1849 p_personnel_office_id => l_target_personnel_office_id,
1850 p_sub_element_code => l_target_agency_code,
1851 p_duty_station_id => t_duty_station_id, -- Bug 4388288
1852 p_duty_station_code => l_target_dummy2, -- Bug 4388288
1853 p_duty_station_desc => l_target_dummy3, -- Bug 4648802
1854 -- p_duty_station_locn_id => l_target_dummy2, -- Bug 3490826
1855 p_duty_station_locn_id =>l_target_duty_station_locn_id , -- Bug 3490826
1856 p_office_symbol => l_target_office_symbol,
1857 p_payroll_office_id => l_target_payroll_office_id,
1858 p_org_func_code => l_target_org_func_code,
1859 p_appropriation_code1 => l_target_appropriation_code1,
1860 p_appropriation_code2 => l_target_appropriation_code2,
1861 p_position_organization => l_target_position_organization);
1862 EXCEPTION
1863 WHEN OTHERS THEN
1864 pr('Error in get_new_org_dtls is '||
1865 l_errbuf||' '||to_char(l_retcode));
1866 pr('Err is '||sqlerrm(sqlcode));
1867 l_mslerrbuf := 'Error in Fetching new organization details'||
1868 ' Sql Err is '|| sqlerrm(sqlcode);
1869 raise mass_error;
1870 END;
1871
1872
1873 l_position_data_rec.agency_code_subelement := l_target_agency_code;
1874 l_position_data_rec.location_id := l_target_duty_station_locn_id ; -- Bug 3490826
1875
1876 -- Bug# 4388288
1877 IF ( l_duty_station_id <> t_duty_station_id )
1878 AND ( ghr_pay_calc.get_lpa_percentage(l_duty_station_id,l_effective_date) <>
1879 ghr_pay_calc.get_lpa_percentage(t_duty_station_id,l_effective_date)) THEN
1880 l_log_text := 'Error in MRE: '||p_mass_realignment_name||
1881 ' for Vacant Position : '||l_position_title||'.'||l_position_NUMBER
1882 ||'.'||l_position_seq_no||'.'||l_sub_element_code||'. Error: ';
1883 l_log_text := l_log_text||'The duty station entered results in a change in Locality Percentage.'||
1884 'This change is not permitted with NOA 790 Realignment. '||
1885 'Refer to OPM GPPA chapter 17 for the appropriate transaction.';
1886 BEGIN
1887 ghr_mto_int.log_message(
1888 p_procedure => 'Invalid_Duty_Station',
1889 p_message => l_log_text);
1890 EXCEPTION
1891 when others then
1892 hr_utility.set_message(8301, 'GHR_38475_ERROR_LOG_FAILURE');
1893 hr_utility.raise_error;
1894 END;
1895 l_log_text := NULL;
1896 l_recs_failed := l_recs_failed + 1;
1897 ELSE
1898 -- Updating Position Extra Information for Position GRP1
1899 g_proc := 'UpDATE Vacant Position';
1900 if l_new_pos_grp1_rec.position_extra_info_id is Not NULL THEN
1901 ----- Set the global variable not to fire the trigger
1902 ghr_api.g_api_dml := TRUE;
1903 --BUG #13872029 Added Exception procedure wise
1904 BEGIN
1905 ghr_position_extra_info_api.UPDATE_position_extra_info
1906 (p_position_extra_info_id => l_new_pos_grp1_rec.position_extra_info_id
1907 ,p_effective_DATE => l_effective_DATE
1908 ,p_object_version_NUMBER => l_new_pos_grp1_rec.object_version_NUMBER
1909 ,p_poei_information3 => l_target_personnel_office_id
1910 ,p_poei_information4 => l_target_office_symbol
1911 ,p_poei_information18 => l_target_payroll_office_id
1912 ,p_poei_information21 => l_target_position_organization
1913 ,p_poei_information_category => 'GHR_US_POS_GRP1');
1914 EXCEPTION
1915 WHEN OTHERS THEN
1916 pr('Error in UPDATE_position_extra_info is '||
1917 l_errbuf||' '||to_char(l_retcode));
1918 pr('Err is '||sqlerrm(sqlcode));
1919 l_mslerrbuf := 'Error in Updating Position extra info'||
1920 ' Sql Err is '|| sqlerrm(sqlcode);
1921 raise mass_error;
1922 END;
1923
1924 ----- Reset the global variable
1925 ghr_api.g_api_dml := FALSE;
1926 END IF;
1927
1928 if l_pos_ei_data_rec.position_extra_info_id is Not NULL THEN
1929 ----- Set the global variable not to fire the trigger
1930 ghr_api.g_api_dml := TRUE;
1931 --BUG #13872029 Added Exception procedure wise
1932 BEGIN
1933 ghr_position_extra_info_api.UPDATE_position_extra_info
1934 (p_position_extra_info_id => l_pos_ei_data_rec.position_extra_info_id
1935 ,p_effective_DATE => l_effective_DATE
1936 ,p_object_version_NUMBER => l_pos_ei_data_rec.object_version_NUMBER
1937 ,p_poei_information4 => l_target_org_func_code
1938 ,p_poei_information13 => l_target_appropriation_code1
1939 ,p_poei_information14 => l_target_appropriation_code2
1940 ,p_poei_information_category => 'GHR_US_POS_GRP2');
1941 ---- Reset the global variable
1942 ghr_api.g_api_dml := FALSE;
1943 EXCEPTION
1944 WHEN OTHERS THEN
1945 pr('Error in UPDATE_position_extra_info is '||
1946 l_errbuf||' '||to_char(l_retcode));
1947 pr('Err is '||sqlerrm(sqlcode));
1948 l_mslerrbuf := 'Error in Updating Position extra info'||
1949 ' Sql Err is '|| sqlerrm(sqlcode);
1950 raise mass_error;
1951 END;
1952 END IF;
1953
1954 ghr_mto_int.log_message(
1955 p_procedure => 'Successful Completion',
1956 p_message =>
1957 'Vacant Position : '||l_position_title ||
1958 ' Mass realignment : '||
1959 p_mass_realignment_name ||' Vacant pos Successfully completed');
1960
1961 upd_ext_info_to_null(l_new_position_id,l_effective_DATE);
1962
1963 -- There is a trigger on Position extra Info. Whenever UPDATEd/created the
1964 -- main position associated with it becomes invalid.
1965 -- We shall call valiDATE_perwsdpo procedure to set the status = VALID.
1966 -- Actually there should be a global flag called fire_trigger in session_var
1967 -- but it doesn't seem to be functional right now.
1968
1969 --- Commented the following two lines to remove Validation functionality on Position.
1970 -- ghr_valiDATE_perwsdpo.valiDATE_perwsdpo(l_new_position_id);
1971 -- ghr_valiDATE_perwsdpo.UPDATE_posn_status(l_new_position_id);
1972 g_proc := 'ghr_sf52.UPDATE_position_info';
1973 pr('Position id /org id'||to_char(l_new_position_id),
1974 to_char(p_new_organization_id));
1975 -- This Position UpDATE procedure will UPDATE both Organization
1976 -- and agency_code Subelement.
1977 -- VSM- Bug # 758441
1978 -- Position history not created for Date END and org id
1979 -- Created wrapper procedure UPDATE_position_info for
1980 -- ghr_sf52_pos_UPDATE.UPDATE_position_info
1981 -- #### ghr_sf52_pos_UPDATE.UPDATE_position_info
1982 BEGIN
1983 UPDATE_position_info (l_position_data_rec);
1984 EXCEPTION
1985 WHEN OTHERS THEN
1986 l_mslerrbuf := 'Error in ghr_sf52_pos_UPDATE.UPDATE_position_info'||' Sql Err is '|| sqlerrm(sqlcode);
1987 raise mass_error;
1988 END;
1989
1990 ------ghr_sf52_pos_UPDATE.UPDATE_position_info
1991 ------ ( p_pos_data_rec => l_position_data_rec);
1992 END IF; -- Bug#4388288 End.
1993 END IF; --- Eligibility
1994 END IF; --- Select flag
1995 END IF; -- Check of availability status id ENDs here
1996 --BUG # 13872029 Modified to handle Exception inside the LOOP so that
1997 -- even after any error it will continue with next vacant position
1998 EXCEPTION
1999 WHEN mass_ERROR THEN
2000
2001 HR_UTILITY.SET_LOCATION('Error occurred in '||l_proc||' Sql error '||sqlerrm(sqlcode),10);
2002 BEGIN
2003 ROLLBACK TO EXECUTE_MRE_SP;
2004 EXCEPTION
2005 WHEN OTHERS THEN NULL;
2006 END;
2007 l_log_text := 'Error in '||l_proc||' '||
2008 ' For Mass Realignment Name : '||p_mass_realignment_name||l_mslerrbuf;
2009 hr_utility.set_location('before creating entry in log file',10);
2010 l_recs_failed := l_recs_failed + 1;
2011 p_retcode := 2;
2012 p_errbuf := 'Error in '||l_proc || ' Details in GHR_PROCESS_LOG';
2013
2014 BEGIN
2015 ghr_mto_int.log_message(
2016 p_procedure => g_proc,
2017 p_message => l_log_text);
2018 COMMIT;
2019 EXCEPTION
2020 when others then
2021 hr_utility.set_message(8301, 'GHR_38475_ERROR_LOG_FAILURE');
2022 hr_utility.raise_error;
2023 END;
2024
2025
2026 WHEN OTHERS THEN
2027 HR_UTILITY.SET_LOCATION('Error (Others2) occurred in '||l_proc||' Sql error '||sqlerrm(sqlcode),30);
2028 BEGIN
2029 ROLLBACK TO EXECUTE_MRE_SP;
2030 EXCEPTION
2031 WHEN OTHERS THEN NULL;
2032 END;
2033 l_log_text := 'Error in '||l_proc||
2034 ' For Mass Realignment Name : '||p_mass_realignment_name||
2035 ' Sql Err is '||sqlerrm(sqlcode);
2036 l_recs_failed := l_recs_failed + 1;
2037 hr_utility.set_location('before creating entry in log file',30);
2038
2039 p_errbuf := 'Error in '||l_proc || ' Details in GHR_PROCESS_LOG';
2040 p_retcode := 2;
2041
2042 BEGIN
2043 ghr_mto_int.log_message(
2044 p_procedure => g_proc,
2045 p_message => l_log_text);
2046 COMMIT;
2047 EXCEPTION
2048 when others then
2049 hr_utility.set_message(8301, 'Create Error Log failed');
2050 hr_utility.raise_error;
2051 END;
2052 END;
2053 END LOOP;
2054 EXCEPTION
2055 WHEN OTHERS THEN
2056 l_mslerrbuf := 'Error in ghr_sf52_pos_UPDATE.UPDATE_position_info'||' Sql Err is '|| sqlerrm(sqlcode);
2057 raise mass_error;
2058 END;
2059 END IF; --- End for unass - p_action = CREATE
2060 END;
2061 if l_break = 'Y' then
2062 exit;
2063 END IF;
2064 END LOOP;
2065
2066 /*
2067 if (l_recs_failed < (l_mass_cnt * (1/3))) then
2068 */
2069 if (l_recs_failed = 0) then
2070 IF UPPER(p_action) = 'CREATE' THEN
2071 BEGIN
2072 UPDATE ghr_mass_realignment
2073 set status = 'P'
2074 WHERE mass_realignment_id = p_mass_realignment_id;
2075 EXCEPTION
2076 when others then
2077 HR_UTILITY.SET_LOCATION('Error in UpDATE ghr_mre Sql error '||sqlerrm(sqlcode),30);
2078 hr_utility.set_message(8301, 'GHR_38570_UPD_GHR_MRE_FAILURE');
2079 hr_utility.raise_error;
2080 END;
2081 END IF;
2082 END IF;
2083 COMMIT;
2084 pr(' Recs failed '||to_char(l_recs_failed)||
2085 'msl cnt is '||to_char(l_mass_cnt));
2086 /*
2087 if (l_recs_failed > (l_mass_cnt * (1/3))) then
2088 */
2089 if (l_recs_failed <> 0) then
2090 p_errbuf := 'Error in '||l_proc || ' Details in GHR_PROCESS_LOG';
2091 p_retcode := 2;
2092 IF UPPER(p_action) = 'CREATE' THEN
2093 UPDATE ghr_mass_realignment
2094 set status = 'E'
2095 WHERE mass_realignment_id = p_mass_realignment_id;
2096 COMMIT;
2097 END IF;
2098 END IF;
2099 EXCEPTION
2100 WHEN mass_ERROR THEN
2101 HR_UTILITY.SET_LOCATION('Error occurred in '||l_proc||' Sql error '||sqlerrm(sqlcode),10);
2102 BEGIN
2103 ROLLBACK TO EXECUTE_MRE_SP;
2104 EXCEPTION
2105 WHEN OTHERS THEN NULL;
2106 END;
2107 IF UPPER(p_action) = 'CREATE' THEN
2108 UPDATE ghr_mass_realignment
2109 set status = 'E'
2110 WHERE mass_realignment_id = p_mass_realignment_id;
2111 COMMIT;
2112 END IF;
2113 l_log_text := 'Error in '||l_proc||' '||
2114 ' For Mass Realignment Name : '||p_mass_realignment_name||
2115 l_mslerrbuf;
2116 hr_utility.set_location('before creating entry in log file',10);
2117 l_recs_failed := l_recs_failed + 1;
2118 p_retcode := 2;
2119 p_errbuf := 'Error in '||l_proc || ' Details in GHR_PROCESS_LOG';
2120
2121 BEGIN
2122 ghr_mto_int.log_message(
2123 p_procedure => g_proc,
2124 p_message => l_log_text);
2125 COMMIT;
2126 EXCEPTION
2127 when others then
2128 hr_utility.set_message(8301, 'GHR_38475_ERROR_LOG_FAILURE');
2129 hr_utility.raise_error;
2130 END;
2131
2132
2133 WHEN OTHERS THEN
2134 HR_UTILITY.SET_LOCATION('Error (Others2) occurred in '||l_proc||' Sql error '||sqlerrm(sqlcode),30);
2135 BEGIN
2136 ROLLBACK TO EXECUTE_MRE_SP;
2137 EXCEPTION
2138 WHEN OTHERS THEN NULL;
2139 END;
2140 l_log_text := 'Error in '||l_proc||
2141 ' For Mass Realignment Name : '||p_mass_realignment_name||
2142 ' Sql Err is '||sqlerrm(sqlcode);
2143 l_recs_failed := l_recs_failed + 1;
2144 hr_utility.set_location('before creating entry in log file',30);
2145
2146 p_errbuf := 'Error in '||l_proc || ' Details in GHR_PROCESS_LOG';
2147 p_retcode := 2;
2148 IF UPPER(p_action) = 'CREATE' THEN
2149 UPDATE ghr_mass_realignment
2150 set status = 'E'
2151 WHERE mass_realignment_id = p_mass_realignment_id;
2152 COMMIT;
2153 END IF;
2154
2155 BEGIN
2156 ghr_mto_int.log_message(
2157 p_procedure => g_proc,
2158 p_message => l_log_text);
2159 COMMIT;
2160 EXCEPTION
2161 when others then
2162 hr_utility.set_message(8301, 'Create Error Log failed');
2163 hr_utility.raise_error;
2164 END;
2165 END EXECUTE_MRE;
2166
2167 --
2168 --
2169 --
2170 -- Procedure Deletes all records processed by the report
2171 --
2172
2173 procedure purge_processed_recs(p_session_id in NUMBER,
2174 p_err_buf out NOCOPY VARCHAR2) is
2175 BEGIN
2176 p_err_buf := null;
2177 delete from ghr_mass_actions_preview
2178 WHERE mass_action_type = 'REALIGNMENT'
2179 and session_id = p_session_id;
2180 COMMIT;
2181
2182 EXCEPTION
2183 when others then
2184 p_err_buf := 'Sql err '|| sqlerrm(sqlcode);
2185 END;
2186
2187 --
2188 --
2189 --
2190 --Bug # 9329643 added p_employee_number
2191 procedure pop_dtls_from_pa_req(p_person_id in NUMBER,p_effective_DATE in DATE,
2192 p_mass_realignment_id in NUMBER, p_employee_number in VARCHAR2) is
2193
2194 cursor ghr_pa_req_cur is
2195 SELECT EMPLOYEE_DATE_OF_BIRTH,
2196 substr(EMPLOYEE_LAST_NAME||', '||EMPLOYEE_FIRST_NAME||' '||
2197 EMPLOYEE_MIDDLE_NAMES,1,240) FULL_NAME,
2198 EMPLOYEE_NATIONAL_IDENTIFIER,
2199 DUTY_STATION_CODE,
2200 DUTY_STATION_DESC,
2201 PERSONNEL_OFFICE_ID,
2202 TO_POSITION_ID POSITION_ID,
2203 TO_POSITION_TITLE POSITION_TITLE,
2204 TO_POSITION_NUMBER POSITION_NUMBER,
2205 TO_POSITION_SEQ_NO POSITION_SEQ_NO,
2206 null org_structure_id,
2207 FROM_AGENCY_CODE,
2208 PERSON_ID,
2209 'Y' Sel_flag,
2210 first_action_la_code1,
2211 first_action_la_code2,
2212 NULL REMARK_CODE1,
2213 NULL REMARK_CODE2,
2214 from_grade_or_level,
2215 from_step_or_rate,
2216 FROM_OFFICE_SYMBOL,
2217 from_pay_plan,
2218 FROM_OCC_CODE,
2219 TO_ORGANIZATION_ID ORGANIZATION_ID,
2220 /*
2221 B.NAME ORGANIZATION_NAME,
2222 */
2223 EMPLOYEE_ASSIGNMENT_ID,
2224 PAY_RATE_DETERMINANT
2225 from ghr_pa_requests /*, per_organization_units B*/
2226 WHERE person_id = p_person_id
2227 and effective_DATE = p_effective_DATE
2228 -- Added by Dinkar for reports
2229 and substr(request_NUMBER,(instr(request_NUMBER,'-')+1)) = to_char(p_mass_realignment_id)
2230 and first_noa_code = '790';
2231 /*
2232 and TO_organization_id = B.organization_id;
2233 */
2234
2235 l_proc VARCHAR2(72)
2236 := g_package || '.pop_dtls_from_pa_req';
2237 BEGIN
2238 g_proc := 'pop_dtls_from_pa_req';
2239
2240 hr_utility.set_location('Entering ' || l_proc,5);
2241 for pa_req_rec in ghr_pa_req_cur
2242 loop
2243 create_mass_act_prev (p_effective_DATE,
2244 pa_req_rec.employee_DATE_of_birth,
2245 pa_req_rec.full_name,
2246 pa_req_rec.employee_national_identifier,
2247 --Bug# 9329643
2248 p_employee_number,
2249 pa_req_rec.duty_station_code,
2250 pa_req_rec.duty_station_desc,
2251 pa_req_rec.personnel_office_id,
2252 pa_req_rec.position_id,
2253 pa_req_rec.position_title,
2254 pa_req_rec.position_NUMBER,
2255 pa_req_rec.position_seq_no,
2256 pa_req_rec.org_structure_id,
2257 pa_req_rec.from_agency_code,
2258 pa_req_rec.person_id,
2259 p_mass_realignment_id,
2260 'Y', --- Sel flag
2261 pa_req_rec.from_grade_or_level,
2262 pa_req_rec.from_step_or_rate,
2263 pa_req_rec.from_pay_plan,
2264 pa_req_rec.from_occ_code,
2265 pa_req_rec.from_office_symbol,
2266 pa_req_rec.organization_id,
2267 null,---pa_req_rec.organization_name,
2268 null,
2269 null, null, null, null, null,
2270 null, null, null, null, null, null,
2271 'REPORT',
2272 pa_req_rec.EMPLOYEE_ASSIGNMENT_ID,
2273 pa_req_rec.PAY_RATE_DETERMINANT);
2274 exit;
2275 END LOOP;
2276 hr_utility.set_location('Exiting ' || l_proc,10);
2277 EXCEPTION
2278 when mass_error then raise;
2279 when others then
2280 pr('Error in '||l_proc);
2281 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2282 l_mslerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2283 raise mass_error;
2284 END pop_dtls_from_pa_req;
2285
2286 --
2287 --
2288 --
2289 --
2290
2291 function check_SELECT_flg(p_position_id in NUMBER,
2292 p_action in VARCHAR2,
2293 p_effective_DATE in DATE,
2294 p_mre_id in NUMBER,
2295 p_sel_flg in out NOCOPY VARCHAR2)
2296 return BOOLEAN IS
2297 l_comments VARCHAR2(150);
2298 l_mre_id NUMBER;
2299 l_sel_flg VARCHAR2(10);
2300 l_line NUMBER := 0;
2301
2302 l_proc VARCHAR2(72) := g_package || '.check_SELECT_flg';
2303
2304 BEGIN
2305 g_proc := 'check_SELECT_flg';
2306
2307 --Initilization for NOCOPY Changes
2308 --
2309 l_sel_flg := p_sel_flg;
2310 --
2311 hr_utility.set_location('Entering ' || l_proc,5);
2312 pr('in '||l_proc);
2313 l_line := 5;
2314 get_extra_info_comments(p_position_id,p_effective_DATE,l_sel_flg,
2315 l_comments,l_mre_id);
2316 pr('After get ext ');
2317 pr('Sel flg ',l_sel_flg,'Mre id '||to_char(l_mre_id));
2318
2319 pr('After pr sel fl');
2320
2321 p_sel_flg := l_sel_flg;
2322
2323 l_line := 10;
2324 if l_sel_flg is null then
2325 p_sel_flg := 'Y';
2326 l_line := 15;
2327 --Bug#4126137 Commented ins_upd_pos_extra_info as this is invalidating all the positions.
2328 -- ins_upd_pos_extra_info(p_position_id,p_effective_DATE,'Y', null, p_mre_id);
2329 ELSIF l_sel_flg = 'Y' then
2330 if nvl(l_mre_id,0) <> nvl(p_mre_id,0) then
2331 p_sel_flg := 'N';
2332 --ins_upd_pos_extra_info(p_position_id,p_effective_DATE,'N', l_comments,
2333 -- p_mre_name);
2334 END IF;
2335 ELSIF l_sel_flg = 'N' then
2336 if nvl(l_mre_id,0) <> nvl(p_mre_id,0) then
2337 p_sel_flg := 'Y';
2338 l_line := 20;
2339 --Bug#4126137 Commented ins_upd_pos_extra_info as this is invalidating all the positions.
2340 -- ins_upd_pos_extra_info(p_position_id,p_effective_DATE,'Y', null, p_mre_id);
2341 END IF;
2342 END IF;
2343
2344 l_line := 25;
2345 pr('Sel fl '||p_sel_flg,'Mre id '||to_char(l_mre_id));
2346 if p_action IN ('SHOW','REPORT') THEN
2347 return TRUE;
2348 ELSIF p_action = 'CREATE' THEN
2349 if p_sel_flg = 'Y' THEN
2350 return TRUE;
2351 ELSE
2352 return FALSE;
2353 END IF;
2354 END IF;
2355 EXCEPTION
2356 when mass_error then raise;
2357 when others then
2358 -- NOCOPY Changes
2359 -- Reset IN OUT params and Set OUT params to null
2360 p_sel_flg := l_sel_flg;
2361 --
2362 pr('Error in '||l_proc);
2363 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2364 l_mslerrbuf := 'Error in '||l_proc||' @'||to_char(l_line)||' Sql Err is '|| sqlerrm(sqlcode);
2365
2366 raise mass_error;
2367 END;
2368
2369 --
2370 --
2371 --
2372
2373 procedure ins_upd_pos_extra_info
2374 (p_position_id in NUMBER,
2375 p_effective_DATE in DATE,
2376 p_sel_flag in VARCHAR2,
2377 p_comment in VARCHAR2,
2378 p_mre_id in NUMBER) is
2379
2380 l_position_extra_info_id NUMBER;
2381 l_object_version_NUMBER NUMBER;
2382 l_pos_ei_data per_position_extra_info%rowtype;
2383
2384 CURSOR position_ext_cur (position NUMBER) is
2385 SELECT position_extra_info_id, object_version_NUMBER
2386 FROM PER_POSITION_EXTRA_INFO
2387 WHERE POSITION_ID = position
2388 and information_type = 'GHR_US_POS_MASS_ACTIONS';
2389
2390 l_proc VARCHAR2(72) := g_package || '.ins_upd_pos_extra_info';
2391 l_eff_DATE DATE;
2392
2393 BEGIN
2394 hr_utility.set_location('Entering ' || l_proc,5);
2395 g_proc := 'ins_upd_pos_extra_info';
2396
2397 if p_effective_DATE > sysDATE then
2398 l_eff_DATE := sysDATE;
2399 ELSE
2400 l_eff_DATE := p_effective_DATE;
2401 END IF;
2402
2403 ghr_history_fetch.fetch_positionei
2404 (p_position_id => p_position_id
2405 ,p_information_type => 'GHR_US_POS_MASS_ACTIONS'
2406 ,p_DATE_effective => l_eff_DATE
2407 ,p_pos_ei_data => l_pos_ei_data);
2408
2409 l_position_extra_info_id := l_pos_ei_data.position_extra_info_id;
2410 l_object_version_NUMBER := l_pos_ei_data.object_version_NUMBER;
2411
2412 if l_position_extra_info_id is null then
2413 for pos_ext_rec in position_ext_cur(p_position_id)
2414 loop
2415 l_position_extra_info_id := pos_ext_rec.position_extra_info_id;
2416 l_object_version_NUMBER := pos_ext_rec.object_version_NUMBER;
2417 END loop;
2418 END IF;
2419
2420 if l_position_extra_info_id is not null then
2421
2422 ----- Set the global variable not to fire the trigger
2423 ghr_api.g_api_dml := TRUE;
2424
2425 BEGIN
2426 ghr_position_extra_info_api.UPDATE_position_extra_info
2427 (P_POSITION_EXTRA_INFO_ID => l_position_extra_info_id
2428 ,P_EFFECTIVE_DATE => trunc(l_eff_DATE)
2429 ,P_OBJECT_VERSION_NUMBER => l_object_version_NUMBER
2430 ,p_poei_INFORMATION3 => p_sel_flag
2431 ,p_poei_INFORMATION4 => p_comment
2432 ,p_poei_INFORMATION14 => to_char(p_mre_id)
2433 ,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS');
2434 EXCEPTION when others then
2435 hr_utility.set_location('UPDATE posei error 1' || l_proc,10);
2436 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2437 END;
2438 ----- Reset the global variable
2439 ghr_api.g_api_dml := FALSE;
2440
2441 ELSE
2442 -- Bug#4215231 Set the global variable not to fire the trigger
2443 ghr_api.g_api_dml := TRUE;
2444 ghr_position_extra_info_api.create_position_extra_info
2445 (P_POSITION_ID => p_position_id
2446 ,P_INFORMATION_TYPE => 'GHR_US_POS_MASS_ACTIONS'
2447 ,P_EFFECTIVE_DATE => trunc(l_eff_DATE)
2448 ,p_poei_INFORMATION3 => p_sel_flag
2449 ,p_poei_INFORMATION4 => p_comment
2450 ,p_poei_INFORMATION14 => to_char(p_mre_id)
2451 ,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS'
2452 ,P_POSITION_EXTRA_INFO_ID => l_position_extra_info_id
2453 ,P_OBJECT_VERSION_NUMBER => l_object_version_NUMBER);
2454
2455 --Bug#4215231 Reset the global variable
2456 ghr_api.g_api_dml := FALSE;
2457
2458 END IF;
2459 hr_utility.set_location('Exiting ' || l_proc,30);
2460
2461 -- There is a trigger on Position extra Info. Whenever UPDATEd/created the
2462 -- main position associated with it becomes invalid.
2463 -- We shall call valiDATE_perwsdpo procedure to set the status = VALID.
2464 -- Actually there should be a global flag called fire_trigger in session_var
2465 -- but it doesn't seem to be functional right now.
2466
2467 --- Commented the following two lines to remove Validation functionality on Position.
2468 -- ghr_valiDATE_perwsdpo.valiDATE_perwsdpo(p_position_id);
2469 -- ghr_valiDATE_perwsdpo.UPDATE_posn_status(p_position_id);
2470
2471
2472 EXCEPTION
2473 when mass_error then raise;
2474 when others then
2475 pr('Error in '||l_proc);
2476 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2477 l_mslerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2478 raise mass_error;
2479 END ins_upd_pos_extra_info;
2480
2481 --
2482 --
2483 --
2484
2485 function get_mre_name(p_mre_id in NUMBER) return VARCHAR2 is
2486
2487 CURSOR mre_cur is
2488 SELECT NAME
2489 FROM GHR_MASS_REALIGNMENT
2490 WHERE MASS_REALIGNMENT_ID = p_mre_id;
2491
2492 l_mre_name VARCHAR2(150);
2493 l_proc VARCHAR2(72) := g_package || '.get_mre_name';
2494 BEGIN
2495 g_proc := 'get_mre_name';
2496 hr_utility.set_location('Entering ' || l_proc,5);
2497 FOR mre_REC IN mre_cur
2498 LOOP
2499 l_mre_name := mre_rec.name;
2500 exit;
2501 END LOOP;
2502 return (l_mre_name);
2503 END;
2504
2505 --
2506 --
2507 --
2508
2509 procedure purge_old_data (p_mass_session_id in NUMBER) is
2510 l_proc VARCHAR2(72)
2511 := g_package || '.purge_old_data';
2512 BEGIN
2513 g_proc := 'purge_old_data';
2514
2515 hr_utility.set_location('Entering ' || l_proc,5);
2516 delete from ghr_mass_actions_preview
2517 WHERE mass_action_type = 'REALIGNMENT'
2518 and session_id = userenv('sessionid');
2519 COMMIT;
2520 hr_utility.set_location('Exiting ' || l_proc,10);
2521 EXCEPTION
2522 when mass_error then raise;
2523 when others then
2524 pr('Error in '||l_proc);
2525 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2526 l_mslerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2527 raise mass_error;
2528 END;
2529
2530 --
2531 --
2532 --
2533
2534 procedure UPDATE_sel_flg (p_position_id in NUMBER,p_effective_DATE in DATE) is
2535
2536 l_position_extra_info_id NUMBER;
2537 l_object_version_NUMBER NUMBER;
2538 l_pos_ei_data per_position_extra_info%rowtype;
2539 l_proc VARCHAR2(72) := g_package || '.UPDATE_sel_flg';
2540 l_eff_DATE DATE;
2541 BEGIN
2542 g_proc := 'UPDATE_sel_flg';
2543
2544 hr_utility.set_location('Entering ' || l_proc,5);
2545 if p_effective_DATE > sysDATE then
2546 l_eff_DATE := sysDATE;
2547 ELSE
2548 l_eff_DATE := p_effective_DATE;
2549 END IF;
2550
2551 ghr_history_fetch.fetch_positionei
2552 (p_position_id => p_position_id
2553 ,p_information_type => 'GHR_US_POS_MASS_ACTIONS'
2554 ,p_DATE_effective => l_eff_DATE
2555 ,p_pos_ei_data => l_pos_ei_data);
2556
2557 l_position_extra_info_id := l_pos_ei_data.position_extra_info_id;
2558 l_object_version_NUMBER := l_pos_ei_data.object_version_NUMBER;
2559
2560 if l_position_extra_info_id is not null then
2561
2562 ----- Set the global variable not to fire the trigger
2563 ghr_api.g_api_dml := TRUE;
2564
2565 BEGIN
2566 ghr_position_extra_info_api.UPDATE_position_extra_info
2567 (P_POSITION_EXTRA_INFO_ID => l_position_extra_info_id
2568 ,P_EFFECTIVE_DATE => trunc(l_eff_DATE)
2569 ,P_OBJECT_VERSION_NUMBER => l_object_version_NUMBER
2570 ,p_poei_INFORMATION5 => NULL
2571 ,p_poei_INFORMATION6 => NULL
2572 ,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS');
2573 EXCEPTION when others then
2574 hr_utility.set_location('UPDATE posei error 2' || l_proc,10);
2575 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2576 END;
2577
2578 ----- Reset the global variable
2579 ghr_api.g_api_dml := FALSE;
2580
2581
2582 --- Commented the following two lines to remove Validation functionality on Position.
2583 ---- ghr_valiDATE_perwsdpo.valiDATE_perwsdpo(p_position_id);
2584 ---- ghr_valiDATE_perwsdpo.UPDATE_posn_status(p_position_id);
2585
2586 END IF;
2587 hr_utility.set_location('Exiting ' || l_proc,30);
2588 EXCEPTION
2589 when mass_error then raise;
2590 when others then
2591 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2592 l_mslerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2593 raise mass_error;
2594 END UPDATE_sel_flg;
2595
2596 --
2597 --
2598 --
2599
2600 FUNCTION check_eligibility(p_org_structure_id in VARCHAR2,
2601 p_office_symbol in VARCHAR2,
2602 p_personnel_office_id in VARCHAR2,
2603 p_agency_sub_element_code in VARCHAR2,
2604 p_l_org_structure_id in VARCHAR2,
2605 p_l_office_symbol in VARCHAR2,
2606 p_l_personnel_office_id in VARCHAR2,
2607 p_l_agency_sub_element_code in VARCHAR2,
2608 p_person_id in NUMBER,
2609 p_effective_DATE in DATE,
2610 p_action in VARCHAR2
2611 )
2612 return BOOLEAN is
2613
2614 l_row_cnt NUMBER := 0;
2615 l_proc VARCHAR2(72) := g_package || '.check_eligibility';
2616 BEGIN
2617 g_proc := 'check_eligibility';
2618 hr_utility.set_location('Entering ' || l_proc,5);
2619
2620 if p_org_structure_id is not null then
2621 if p_org_structure_id <> nvl(p_l_org_structure_id,'NULL!~') then
2622 return false;
2623 END IF;
2624 END IF;
2625
2626 if p_office_symbol is not null then
2627 if p_office_symbol <> nvl(p_l_office_symbol,'NULL!~') then
2628 return false;
2629 END IF;
2630 END IF;
2631
2632 if p_personnel_office_id is not null then
2633 if p_personnel_office_id <> nvl(p_l_personnel_office_id,'NULL!~') then
2634 return false;
2635 END IF;
2636 END IF;
2637
2638 -- VSM - p_agency_sub_element_code can have 2 or 4 chars.
2639 -- 2 char - Check for agency code only
2640 -- 4 char - Check for agency code and subelement
2641 if p_agency_sub_element_code is not null then
2642 if substr(p_agency_sub_element_code, 1, 2) <>
2643 nvl(substr(p_l_agency_sub_element_code, 1, 2), 'NULL!~') then
2644 return false;
2645 END IF;
2646 END IF;
2647
2648 if substr(p_agency_sub_element_code, 3, 2) is not null then
2649 if substr(p_agency_sub_element_code, 3, 2) <>
2650 nvl(substr(p_l_agency_sub_element_code, 3, 2), 'NULL!~') then
2651 return false;
2652 END IF;
2653 END IF;
2654 --
2655 -- VSM END enhancement
2656 --
2657
2658 if p_action = 'CREATE' THEN
2659 if person_in_pa_req_1noa
2660 (p_person_id => p_person_id,
2661 p_effective_DATE => p_effective_DATE,
2662 p_first_noa_code => '790'
2663 ) then
2664 return false;
2665 END IF;
2666 /*************
2667 if person_in_pa_req_2noa
2668 (p_person_id => p_person_id,
2669 p_effective_DATE => p_effective_DATE,
2670 p_second_noa_code => '790'
2671 ) then
2672 return false;
2673 END IF;
2674 *************/
2675 END IF;
2676
2677 pr('Eligible');
2678 return true;
2679
2680 EXCEPTION
2681 when mass_error then raise;
2682 when others then
2683 pr('Error in '||l_proc);
2684 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2685 l_mslerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2686 raise mass_error;
2687 END check_eligibility;
2688
2689 --
2690 --
2691 --
2692
2693 function person_in_pa_req_1noa
2694 (p_person_id in NUMBER,
2695 p_effective_DATE in DATE,
2696 p_first_noa_code in VARCHAR2
2697 )
2698 return BOOLEAN is
2699 --
2700 l_name per_people_f.full_name%type;
2701 -- Bug#3718167 Added l_ssn
2702 l_ssn per_people_f.national_identifier%TYPE;
2703 l_code_action VARCHAR2(65);
2704 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
2705 --Bug # 9329643
2706 l_employee_number per_people_f.employee_number%type;
2707
2708
2709 cursor csr_action_taken is
2710 SELECT pr.pa_request_id, max(pa_routing_history_id) pa_routing_history_id
2711 from ghr_pa_requests pr, ghr_pa_routing_history prh
2712 WHERE pr.pa_request_id = prh.pa_request_id
2713 and person_id = p_person_id
2714 and first_noa_code = p_first_noa_code
2715 and effective_DATE = p_effective_DATE
2716 and nvl(pr.first_noa_cancel_or_correct,'X') <> ghr_history_api.g_cancel
2717 --Bug 657439
2718 -- and nvl(pr.first_noa_cancel_or_correct,'X') <> 'CANCELED'
2719 group by pr.pa_request_id;
2720
2721 -- Bug#3718167 Added SSN in the cursor
2722 cursor csr_name is
2723 SELECT substr(pr.employee_last_name || ', ' || pr.employee_first_name,1,240) fname,
2724 pr.employee_national_identifier SSN
2725 from ghr_pa_requests pr
2726 WHERE pr.pa_request_id = l_pa_request_id;
2727
2728 cursor pa_hist_cur (p_r_hist_id NUMBER) is
2729 SELECT nvl(action_taken,' ') action_taken
2730 from ghr_pa_routing_history
2731 WHERE pa_routing_history_id = p_r_hist_id;
2732
2733 l_action_taken ghr_pa_routing_history.action_taken%TYPE;
2734
2735
2736 BEGIN
2737 g_proc := 'person_in_pa_req_1noa';
2738
2739 if p_first_noa_code = '790' then
2740 l_code_action := ' - Realignment ';
2741 ELSIF p_first_noa_code = '352' then
2742 l_code_action := ' - Transfer Out ';
2743 ELSIF p_first_noa_code = '132' then
2744 l_code_action := ' - Transfer In ';
2745 END IF;
2746
2747 for v_action_taken in csr_action_taken
2748 loop
2749 l_pa_request_id := v_action_taken.pa_request_id;
2750 for v_name in csr_name
2751 loop
2752 l_name := v_name.fname;
2753 -- Bug#3718167 Added l_ssn statement
2754 l_ssn := v_name.ssn;
2755 exit;
2756 END loop;
2757 for pa_hist_rec in pa_hist_cur (v_action_taken.pa_routing_history_id)
2758 loop
2759 l_action_taken := pa_hist_rec.action_taken;
2760 exit;
2761 END loop;
2762 --Bug # 9329643
2763 If p_person_id is not null then
2764 l_employee_number := ghr_pa_requests_pkg2.get_employee_number
2765 (p_person_id => p_person_id,
2766 p_effective_date => p_effective_date);
2767 End if;
2768 if l_action_taken <> 'CANCELED' then
2769 -- Bug#3718167 Added SSN in the following message
2770 ghr_mto_int.log_message(
2771 p_procedure => 'RPA Exists Already',
2772 p_message => 'Name: '|| l_name || '; Emp No: ' || l_employee_number||
2773 l_code_action ||
2774 ' RPA Exists for the given effective DATE ' );
2775 return true;
2776 END IF;
2777 END loop;
2778 return false;
2779 END person_in_pa_req_1noa;
2780
2781 --
2782 --
2783 --
2784
2785 function person_in_pa_req_2noa
2786 (p_person_id in NUMBER,
2787 p_effective_DATE in DATE,
2788 p_second_noa_code in VARCHAR2
2789 )
2790 return BOOLEAN is
2791 --
2792 cursor csr_action_taken is
2793 SELECT pr.pa_request_id, max(pa_routing_history_id) pa_routing_history_id
2794 from ghr_pa_requests pr, ghr_pa_routing_history prh
2795 WHERE pr.pa_request_id = prh.pa_request_id
2796 and nvl(person_id,0) = p_person_id
2797 and nvl(second_noa_code,0) = p_second_noa_code
2798 and trunc(nvl(effective_DATE,sysDATE)) = trunc(p_effective_DATE)
2799 and nvl(pr.second_noa_cancel_or_correct,'X') <> ghr_history_api.g_cancel
2800 --Bug 657439
2801 -- and nvl(pr.first_noa_cancel_or_correct,'X') <> 'CANCELED'
2802 group by pr.pa_request_id;
2803
2804 cursor pa_hist_cur (p_r_hist_id NUMBER) is
2805 SELECT nvl(action_taken,' ') action_taken
2806 from ghr_pa_routing_history
2807 WHERE pa_routing_history_id = p_r_hist_id;
2808
2809 l_action_taken ghr_pa_routing_history.action_taken%TYPE;
2810 BEGIN
2811 g_proc := 'person_in_pa_req_2noa';
2812 for v_action_taken in csr_action_taken
2813 loop
2814 for pa_hist_rec in pa_hist_cur (v_action_taken.pa_routing_history_id)
2815 loop
2816 l_action_taken := pa_hist_rec.action_taken;
2817 exit;
2818 END loop;
2819 if l_action_taken <> 'CANCELED' then
2820 return true;
2821 END IF;
2822 END loop;
2823 return false;
2824 END person_in_pa_req_2noa;
2825
2826 --
2827 --
2828 --
2829
2830 procedure get_pos_grp1_ddf (p_position_id in
2831 per_assignments_f.position_id%type,
2832 p_effective_DATE in DATE,
2833 p_personnel_office_id out NOCOPY VARCHAR2,
2834 p_org_structure_id out NOCOPY VARCHAR2,
2835 p_office_symbol out NOCOPY VARCHAR2,
2836 p_position_organization out NOCOPY VARCHAR2,
2837 p_pos_ei_data OUT NOCOPY
2838 per_position_extra_info%rowtype)
2839 IS
2840
2841 l_proc VARCHAR2(72)
2842 := g_package || '.get_pos_grp1_ddf';
2843 --l_pos_ei_data per_position_extra_info%type;
2844
2845 BEGIN
2846 g_proc := 'get_pos_grp1_ddf';
2847
2848 hr_utility.set_location('Entering ' || l_proc,5);
2849 ghr_history_fetch.fetch_positionei
2850 (p_position_id => p_position_id
2851 ,p_information_type => 'GHR_US_POS_GRP1'
2852 ,p_DATE_effective => p_effective_DATE
2853 ,p_pos_ei_data => p_pos_ei_data
2854 );
2855 p_personnel_office_id := p_pos_ei_data.poei_information3;
2856 p_office_symbol := p_pos_ei_data.poei_information4;
2857 p_org_structure_id := p_pos_ei_data.poei_information5;
2858 p_position_organization := p_pos_ei_data.poei_information21;
2859
2860 hr_utility.set_location('Exiting ' || l_proc,10);
2861 EXCEPTION
2862 when mass_error then raise;
2863 when others then
2864 -- NOCOPY changes
2865 -- Reset IN OUT params and set OUT params
2866 p_personnel_office_id := NULL;
2867 p_office_symbol := NULL;
2868 p_org_structure_id := NULL;
2869 p_position_organization := NULL;
2870 p_pos_ei_data := NULL;
2871
2872 pr('Error in '||l_proc);
2873 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2874 l_mslerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2875 raise mass_error;
2876 END get_pos_grp1_ddf;
2877
2878 --
2879 --
2880 --
2881
2882 procedure get_pos_grp2_ddf (p_position_id in
2883 per_assignments_f.position_id%type,
2884 p_effective_DATE in DATE,
2885 p_org_func_code out NOCOPY VARCHAR2,
2886 p_appropriation_code1 out NOCOPY VARCHAR2,
2887 p_appropriation_code2 out NOCOPY VARCHAR2)
2888 --- p_pos_ei_data OUT per_position_extra_info%rowtype)
2889 IS
2890
2891 l_proc VARCHAR2(72)
2892 := g_package || '.get_pos_grp2_ddf';
2893 l_pos_ei_data per_position_extra_info%rowtype;
2894
2895 BEGIN
2896 g_proc := 'get_pos_grp2_ddf';
2897 hr_utility.set_location('Entering ' || l_proc,5);
2898 ghr_history_fetch.fetch_positionei
2899 (p_position_id => p_position_id
2900 ,p_information_type => 'GHR_US_POS_GRP2'
2901 ,p_DATE_effective => p_effective_DATE
2902 ,p_pos_ei_data => l_pos_ei_data
2903 );
2904 p_org_func_code := l_pos_ei_data.poei_information4;
2905 p_appropriation_code1 := l_pos_ei_data.poei_information13;
2906 p_appropriation_code2 := l_pos_ei_data.poei_information14;
2907
2908 hr_utility.set_location('Exiting ' || l_proc,10);
2909 EXCEPTION
2910 when mass_error then raise;
2911 when others then
2912 -- NOCOPY changes
2913 -- Reset IN OUT params and set OUT params
2914 p_org_func_code := NULL;
2915 p_appropriation_code1 := NULL;
2916 p_appropriation_code2 := NULL;
2917 --
2918 pr('Error in '||l_proc);
2919 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2920 l_mslerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2921 raise mass_error;
2922 END get_pos_grp2_ddf;
2923
2924 --
2925 --
2926 --
2927
2928 PROCEDURE GET_FIELD_DESC (p_agency_code in VARCHAR2,
2929 p_to_agency_code in VARCHAR2,
2930 p_approp_code1 in VARCHAR2,
2931 p_approp_code2 in VARCHAR2,
2932 p_pay_plan in VARCHAR2,
2933 p_poi_code in VARCHAR2,
2934 p_to_poi_code in VARCHAR2,
2935 p_org_id in NUMBER,
2936 p_to_org_id in NUMBER,
2937
2938 p_agency_desc out NOCOPY VARCHAR2,
2939 p_to_agency_desc out NOCOPY VARCHAR2,
2940 p_approp_code1_desc out NOCOPY VARCHAR2,
2941 p_approp_code2_desc out NOCOPY VARCHAR2,
2942 p_pay_plan_desc out NOCOPY VARCHAR2,
2943 p_poi_name out NOCOPY VARCHAR2,
2944 p_to_poi_name out NOCOPY VARCHAR2,
2945 p_org_name out NOCOPY VARCHAR2,
2946 p_to_org_name out NOCOPY VARCHAR2)
2947 IS
2948 l_proc VARCHAR2(72)
2949 := g_package || '.get_field_desc';
2950 BEGIN
2951 g_proc := 'GET_FIELD_DESC';
2952
2953 hr_utility.set_location('Entering ' || l_proc,5);
2954 p_agency_desc := GET_FND_COMMON_LOOKUP (p_agency_code,'GHR_US_AGENCY_CODE');
2955 p_to_agency_desc := GET_FND_COMMON_LOOKUP (p_to_agency_code,
2956 'GHR_US_AGENCY_CODE');
2957 p_approp_code1_desc := GET_FND_COMMON_LOOKUP (p_approp_code1,
2958 'GHR_US_APPROPRIATION_CODE1');
2959 p_approp_code2_desc := GET_FND_COMMON_LOOKUP (p_approp_code2,
2960 'GHR_US_APPROPRIATION_CODE2');
2961 p_poi_name := GET_POI_NAME (p_poi_code);
2962 p_to_poi_name := GET_POI_NAME (p_to_poi_code);
2963 p_pay_plan_desc := GET_PP_NAME (p_pay_plan);
2964 /*
2965 p_org_name := get_organization_name (to_NUMBER(p_org_id));
2966 p_to_org_name := get_organization_name (to_NUMBER(p_to_org_id));
2967 */
2968 p_org_name := get_organization_name (to_NUMBER(p_org_id));
2969 p_to_org_name := get_organization_name (to_NUMBER(p_to_org_id));
2970
2971 -- NOCOPY Changes
2972 -- reset IN OUT Params and set OUT Params
2973 EXCEPTION
2974 when others then
2975 p_agency_desc := NULL;
2976 p_to_agency_desc := NULL;
2977 p_approp_code1_desc := NULL;
2978 p_approp_code2_desc := NULL;
2979 p_pay_plan_desc := NULL;
2980 p_poi_name := NULL;
2981 p_to_poi_name := NULL;
2982 p_org_name := NULL;
2983 p_to_org_name := NULL;
2984 END;
2985
2986
2987 FUNCTION GET_FND_COMMON_LOOKUP
2988 (p_lookup_code in VARCHAR2,
2989 p_type in VARCHAR2)
2990 RETURN VARCHAR2 IS
2991 CURSOR CUR_lookup IS
2992 SELECT LOOKUP_CODE,MEANING
2993 FROM HR_LOOKUPS
2994 WHERE LOOKUP_TYPE = p_type
2995 AND ENABLED_FLAG = 'Y'
2996 AND trunc(sysDATE)
2997 BETWEEN NVL(START_DATE_ACTIVE,trunc(sysDATE))
2998 AND NVL(END_DATE_ACTIVE,trunc(sysDATE))
2999 AND LOOKUP_CODE = p_lookup_code;
3000
3001 l_meaning VARCHAR2(150);
3002 l_proc VARCHAR2(72) := g_package || '.get_fnd_common_lookup';
3003 BEGIN
3004 g_proc := 'GET_FND_COMMON_LOOKUP';
3005
3006 hr_utility.set_location('Entering ' || l_proc,5);
3007 FOR lookup_rec IN CUR_lookup
3008 LOOP
3009 l_meaning := lookup_rec.meaning;
3010 exit;
3011 END LOOP;
3012 return (l_meaning);
3013 END;
3014
3015 --
3016 --
3017 --
3018
3019 FUNCTION GET_PP_NAME (PP IN VARCHAR2) RETURN VARCHAR2 IS
3020
3021 CURSOR CUR_PP IS
3022 SELECT pay_plan,description
3023 from ghr_pay_plans
3024 WHERE PAY_PLAN = PP;
3025
3026 l_pp_desc VARCHAR2(150);
3027 l_proc VARCHAR2(72) := g_package || '.get_pp_name';
3028 BEGIN
3029 g_proc := 'GET_PP_NAME';
3030 hr_utility.set_location('Entering ' || l_proc,5);
3031 FOR PP_REC IN CUR_PP
3032 LOOP
3033 l_pp_desc := pp_rec.description;
3034 exit;
3035 END LOOP;
3036 return (l_pp_desc);
3037 END;
3038
3039 --
3040 --
3041 --
3042
3043 FUNCTION GET_POI_NAME (P_POI IN VARCHAR2) RETURN VARCHAR2 IS
3044
3045 CURSOR CUR_POI IS
3046 SELECT description
3047 from ghr_pois
3048 WHERE PERSONNEL_OFFICE_ID = p_poi;
3049
3050 l_poi_desc VARCHAR2(150);
3051 l_proc VARCHAR2(72) := g_package || '.get_poi_name';
3052 BEGIN
3053 g_proc := 'GET_POI_NAME';
3054 hr_utility.set_location('Entering ' || l_proc,5);
3055 FOR POI_REC IN CUR_POI
3056 LOOP
3057 l_poi_desc := poi_rec.description;
3058 exit;
3059 END LOOP;
3060 return (l_poi_desc);
3061 END;
3062
3063 --
3064 --
3065 --
3066
3067 FUNCTION get_organization_name (p_org_id in NUMBER) RETURN VARCHAR2 IS
3068
3069 CURSOR MRE_ORG_CUR (ORG_id IN NUMBER) IS
3070 SELECT name, organization_id
3071 from per_organization_units
3072 WHERE internal_external_flag = 'INT'
3073 and trunc(sysDATE) between DATE_from and nvl(DATE_to,trunc(sysDATE+1))
3074 and organization_id = org_id;
3075
3076 /* business_group_id + 0 = :ctl_globals.business_group_id*/
3077
3078 l_org_name hr_organization_units.name%type;
3079 l_org_id NUMBER;
3080
3081 l_proc VARCHAR2(72)
3082 := g_package || '.get_organization_name';
3083 BEGIN
3084 g_proc := 'get_organization_name';
3085 hr_utility.set_location('Entering ' || l_proc,5);
3086 for mre_rec in mre_org_cur (p_org_id)
3087 LOOP
3088 l_org_name := mre_rec.name;
3089 l_org_id := mre_rec.organization_id;
3090 exit;
3091 END loop;
3092 return(l_org_name);
3093 END;
3094
3095 --
3096 --
3097 --
3098
3099 PROCEDURE get_extra_info_comments
3100 (p_position_id in NUMBER,
3101 p_effective_DATE in DATE,
3102 p_sel_flag in out NOCOPY VARCHAR2,
3103 p_comments in out NOCOPY VARCHAR2,
3104 p_mre_id in out NOCOPY NUMBER) IS
3105
3106 l_sel_flag VARCHAR2(30);
3107 l_comments VARCHAR2(4000);
3108 l_mre_id NUMBER;
3109 l_pos_ei_data per_position_extra_info%rowtype;
3110 l_proc VARCHAR2(72) := g_package || '.get_extra_info_comments';
3111 l_eff_DATE DATE;
3112 l_char_mre_id VARCHAR2(30);
3113
3114 BEGIN
3115 g_proc := 'get_extra_info_comments';
3116 hr_utility.set_location('Entering ' || l_proc,5);
3117 pr('In '||l_proc);
3118
3119 -- Initialization for NOCOPY Changes
3120 l_sel_flag := p_sel_flag;
3121 l_comments := p_comments;
3122 l_mre_id := p_mre_id;
3123 --
3124 /*
3125 if p_effective_DATE > sysDATE then
3126 l_eff_DATE := sysDATE;
3127 ELSE
3128 l_eff_DATE := p_effective_DATE;
3129 END IF;
3130 */
3131 l_eff_DATE := p_effective_DATE;
3132
3133 pr(l_proc||'---> before fetch pos ei');
3134
3135 ghr_history_fetch.fetch_positionei
3136 (p_position_id => p_position_id
3137 ,p_information_type => 'GHR_US_POS_MASS_ACTIONS'
3138 ,p_DATE_effective => l_eff_DATE
3139 ,p_pos_ei_data => l_pos_ei_data);
3140
3141 pr(l_proc||'---> after fetch pos ei');
3142
3143 l_sel_flag := l_pos_ei_data.poei_information3;
3144
3145 pr(l_proc||'---> after sel_flg assignment');
3146 l_comments := l_pos_ei_data.poei_information4;
3147 pr(l_proc||'---> after comments assignment');
3148 l_char_mre_id := l_pos_ei_data.poei_information14;
3149 pr(l_proc||'---> after l_mre_id assignment');
3150 l_mre_id := to_NUMBER(l_char_mre_id);
3151 pr(l_proc||'---> after p_mre_id assignment');
3152
3153 p_sel_flag := l_sel_flag;
3154 p_comments := l_comments;
3155 p_mre_id := l_mre_id;
3156
3157 pr('position ext id',to_char(l_pos_ei_data.position_extra_info_id),
3158 to_char(l_pos_ei_data.object_version_NUMBER));
3159 EXCEPTION
3160 when mass_error then raise;
3161 when others then
3162 -- NOCOPY Changes
3163 -- Reset INOUT Params and set OUT params
3164 --
3165 p_sel_flag := l_sel_flag;
3166 p_comments := l_comments;
3167 p_mre_id := l_mre_id;
3168 --
3169 pr('Error in '||l_proc);
3170 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
3171 l_mslerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
3172 raise mass_error;
3173 END;
3174
3175 --
3176 --
3177 --
3178
3179 procedure create_mass_act_prev (
3180 p_effective_DATE in DATE,
3181 p_DATE_of_birth in DATE,
3182 p_full_name in VARCHAR2,
3183 p_national_identifier in VARCHAR2,
3184 --Bug# 9329643
3185 p_employee_number in VARCHAR2,
3186 p_duty_station_code in VARCHAR2,
3187 p_duty_station_desc in VARCHAR2,
3188 p_personnel_office_id in VARCHAR2,
3189 p_position_id in per_assignments_f.position_id%type,
3190 p_position_title in VARCHAR2,
3191 p_position_NUMBER in VARCHAR2,
3192 p_position_seq_no in VARCHAR2,
3193 p_org_structure_id in VARCHAR2,
3194 p_agency_sub_element_code in VARCHAR2,
3195 p_person_id in NUMBER,
3196 p_mass_realignment_id in NUMBER,
3197 p_sel_flg in VARCHAR2,
3198 p_grade_or_level in VARCHAR2,
3199 p_step_or_rate in VARCHAR2,
3200 p_pay_plan in VARCHAR2,
3201 p_occ_series in VARCHAR2,
3202 p_office_symbol in VARCHAR2,
3203 p_organization_id in NUMBER,
3204 p_organization_name in VARCHAR2,
3205 p_positions_organization in VARCHAR2,
3206 t_personnel_office_id in VARCHAR2,
3207 t_sub_element_code in VARCHAR2,
3208 t_duty_station_id in NUMBER,
3209 t_duty_station_code in VARCHAR2,
3210 t_duty_station_desc in VARCHAR2,
3211 t_office_symbol in VARCHAR2,
3212 t_payroll_office_id in VARCHAR2,
3213 t_org_func_code in VARCHAR2,
3214 t_appropriation_code1 in VARCHAR2,
3215 t_appropriation_code2 in VARCHAR2,
3216 t_position_organization in VARCHAR2,
3217 p_action in VARCHAR2,
3218 p_assignment_id in NUMBER,
3219 p_pay_rate_determinant in VARCHAR2)
3220 is
3221
3222 l_comb_rem VARCHAR2(30);
3223 l_proc VARCHAR2(72)
3224 := g_package || '.create_mass_act_prev';
3225
3226 l_agency_sub_elem_desc VARCHAR2(80);
3227 t_sub_element_desc VARCHAR2(80);
3228 t_appropriation_code1_desc VARCHAR2(80);
3229 t_appropriation_code2_desc VARCHAR2(80);
3230 l_pay_plan_desc VARCHAR2(80);
3231 l_position_organization_name VARCHAR2(240);
3232 l_poi_desc VARCHAR2(80);
3233 t_poi_desc VARCHAR2(80);
3234 t_position_organization_name VARCHAR2(240);
3235
3236 l_cust_rec ghr_mass_act_custom.ghr_mass_custom_out_rec_type;
3237 l_cust_in_rec ghr_mass_act_custom.ghr_mass_custom_in_rec_type;
3238 ----Temp Promo Changes.
3239 l_step_or_rate VARCHAR2(30);
3240 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
3241
3242 BEGIN
3243 g_proc := 'create_mass_act_prev';
3244
3245 hr_utility.set_location('Entering ' || l_proc,5);
3246
3247 pr('Inside ghr_cpdf_temp insert realign id ',to_char(p_mass_realignment_id),null);
3248 pr('t_pos_org is',t_position_organization);
3249
3250 GET_FIELD_DESC (p_agency_sub_element_code,
3251 t_sub_element_code,
3252 t_appropriation_code1,
3253 t_appropriation_code2,
3254 p_pay_plan,
3255 p_personnel_office_id,
3256 t_personnel_office_id,
3257 p_positions_organization,
3258 t_position_organization,
3259
3260 l_agency_sub_elem_desc,
3261 t_sub_element_desc,
3262 t_appropriation_code1_desc,
3263 t_appropriation_code2_desc,
3264 l_pay_plan_desc,
3265 l_poi_desc,
3266 t_poi_desc,
3267 l_position_organization_name,
3268 t_position_organization_name);
3269
3270 BEGIN
3271 l_cust_in_rec.person_id := p_person_id;
3272 l_cust_in_rec.position_id := p_position_id;
3273 l_cust_in_rec.assignment_id := p_assignment_id;
3274 l_cust_in_rec.national_identifier := p_national_identifier;
3275 l_cust_in_rec.mass_action_type := 'REALIGNMENT';
3276 l_cust_in_rec.mass_action_id := p_mass_realignment_id;
3277 l_cust_in_rec.effective_DATE := p_effective_DATE;
3278
3279 GHR_MASS_ACT_CUSTOM.pre_insert (
3280 p_cust_in_rec => l_cust_in_rec,
3281 p_cust_rec => l_cust_rec);
3282
3283 EXCEPTION
3284 when others then
3285 hr_utility.set_location('Error in Mass Act Custom '||
3286 'Err is '||sqlerrm(sqlcode),20);
3287 l_mslerrbuf := 'Error in Mass Act Custom '||
3288 'Err is '|| sqlerrm(sqlcode);
3289 raise mass_error;
3290 END;
3291
3292 l_step_or_rate := p_step_or_rate;
3293
3294 IF nvl(p_pay_rate_determinant,'X') in ('A','B','E','F') AND
3295 ghr_msl_pkg.check_grade_retention(p_pay_rate_determinant
3296 ,p_person_id,p_effective_DATE) = 'REGULAR' THEN
3297 BEGIN
3298 l_retained_grade_rec :=
3299 ghr_pc_basic_pay.get_retained_grade_details
3300 ( p_person_id,
3301 p_effective_DATE);
3302 if l_retained_grade_rec.temp_step is not null then
3303 l_step_or_rate := l_retained_grade_rec.temp_step;
3304 END IF;
3305 EXCEPTION
3306 when others then
3307 l_mslerrbuf := 'Preview - Others error in Get retained grade '||
3308 'Error is '||' Sql Err is '|| sqlerrm(sqlcode);
3309 ghr_mre_pkg.pr('Person ID '||to_char(p_person_id),'ERROR 2',l_mslerrbuf);
3310 raise mass_error;
3311 END;
3312 END IF;
3313
3314
3315 insert into GHR_MASS_ACTIONS_PREVIEW
3316 (
3317 mass_action_type,
3318 --report_type,
3319 ui_type,
3320 session_id,
3321 effective_DATE,
3322 employee_DATE_of_birth,
3323 full_name,
3324 national_identifier,
3325 --Bug# 9329643
3326 employee_number,
3327 duty_station_code,
3328 duty_station_desc,
3329 personnel_office_id,
3330 position_id,
3331 position_title,
3332 position_NUMBER,
3333 position_seq_no,
3334 org_structure_id,
3335 agency_code,
3336 person_id,
3337 SELECT_flag,
3338 first_noa_code,
3339 grade_or_level,
3340 step_or_rate,
3341 pay_plan,
3342 office_symbol,
3343 organization_id,
3344 organization_name,
3345 occ_code,
3346 positions_organization,
3347 to_personnel_office_id,
3348 to_agency_code,
3349 to_duty_station_id,
3350 to_duty_station_code,
3351 to_duty_station_desc,
3352 to_office_symbol,
3353 to_payroll_office_id,
3354 to_org_func_code,
3355 to_appropriation_code1,
3356 to_appropriation_code2,
3357 to_positions_organization,
3358
3359 AGENCY_DESC,
3360 TO_AGENCY_DESC,
3361 TO_APPROPRIATION_CODE1_DESC,
3362 TO_APPROPRIATION_CODE2_DESC,
3363 PAY_PLAN_DESC,
3364 POI_DESC,
3365 TO_POI_DESC,
3366 POSITIONS_ORGANIZATION_NAME,
3367 TO_POSITIONS_ORG_NAME,
3368 USER_ATTRIBUTE1,
3369 USER_ATTRIBUTE2,
3370 USER_ATTRIBUTE3,
3371 USER_ATTRIBUTE4,
3372 USER_ATTRIBUTE5,
3373 USER_ATTRIBUTE6,
3374 USER_ATTRIBUTE7,
3375 USER_ATTRIBUTE8,
3376 USER_ATTRIBUTE9,
3377 USER_ATTRIBUTE10,
3378 USER_ATTRIBUTE11,
3379 USER_ATTRIBUTE12,
3380 USER_ATTRIBUTE13,
3381 USER_ATTRIBUTE14,
3382 USER_ATTRIBUTE15,
3383 USER_ATTRIBUTE16,
3384 USER_ATTRIBUTE17,
3385 USER_ATTRIBUTE18,
3386 USER_ATTRIBUTE19,
3387 USER_ATTRIBUTE20,
3388 USER_ATTRIBUTE21,
3389 USER_ATTRIBUTE22,
3390 USER_ATTRIBUTE23,
3391 USER_ATTRIBUTE24,
3392 USER_ATTRIBUTE25,
3393 USER_ATTRIBUTE26,
3394 USER_ATTRIBUTE27,
3395 USER_ATTRIBUTE28,
3396 USER_ATTRIBUTE29,
3397 USER_ATTRIBUTE30
3398 )
3399 values
3400 (
3401 'REALIGNMENT',
3402 /*--decode(p_action,'REPORT',userenv('SESSIONID'),p_mass_realignment_id),*/
3403 decode(p_action,'SHOW','FORM','REPORT'),
3404 userenv('SESSIONID'),
3405 p_effective_DATE,
3406 p_DATE_of_birth,
3407 p_full_name,
3408 p_national_identifier,
3409 --Bug# 9329643
3410 p_employee_number,
3411 p_duty_station_code,
3412 p_duty_station_desc,
3413 p_personnel_office_id,
3414 p_position_id,
3415 p_position_title,
3416 p_position_NUMBER,
3417 to_NUMBER(p_position_seq_no),
3418 p_org_structure_id,
3419 p_agency_sub_element_code,
3420 p_person_id,
3421 p_sel_flg,
3422 '790',
3423 p_grade_or_level,
3424 l_step_or_rate,
3425 p_pay_plan,
3426 p_office_symbol,
3427 p_organization_id,
3428 p_organization_name,
3429 p_occ_series,
3430 p_positions_organization,
3431 decode(p_sel_flg,'N',NULL,t_personnel_office_id),
3432 decode(p_sel_flg,'N',NULL,t_sub_element_code),
3433 decode(p_sel_flg,'N',NULL,t_duty_station_id),
3434 decode(p_sel_flg,'N',NULL,t_duty_station_code),
3435 decode(p_sel_flg,'N',NULL,t_duty_station_desc),
3436 decode(p_sel_flg,'N',NULL,t_office_symbol),
3437 decode(p_sel_flg,'N',NULL,t_payroll_office_id),
3438 decode(p_sel_flg,'N',NULL,t_org_func_code),
3439 decode(p_sel_flg,'N',NULL,t_appropriation_code1),
3440 decode(p_sel_flg,'N',NULL,t_appropriation_code2),
3441
3442 decode(p_sel_flg,'N',NULL,t_position_organization),
3443 l_agency_sub_elem_desc,
3444 decode(p_sel_flg,'N',NULL,t_sub_element_desc),
3445 decode(p_sel_flg,'N',NULL,t_appropriation_code1_desc),
3446 decode(p_sel_flg,'N',NULL,t_appropriation_code2_desc),
3447 l_pay_plan_desc,
3448 l_poi_desc,
3449 decode(p_sel_flg,'N',NULL,t_poi_desc),
3450 l_position_organization_name,
3451 decode(p_sel_flg,'N',NULL,t_position_organization_name),
3452 l_cust_rec.user_attribute1,
3453 l_cust_rec.user_attribute2,
3454 l_cust_rec.user_attribute3,
3455 l_cust_rec.user_attribute4,
3456 l_cust_rec.user_attribute5,
3457 l_cust_rec.user_attribute6,
3458 l_cust_rec.user_attribute7,
3459 l_cust_rec.user_attribute8,
3460 l_cust_rec.user_attribute9,
3461 l_cust_rec.user_attribute10,
3462 l_cust_rec.user_attribute11,
3463 l_cust_rec.user_attribute12,
3464 l_cust_rec.user_attribute13,
3465 l_cust_rec.user_attribute14,
3466 l_cust_rec.user_attribute15,
3467 l_cust_rec.user_attribute16,
3468 l_cust_rec.user_attribute17,
3469 l_cust_rec.user_attribute18,
3470 l_cust_rec.user_attribute19,
3471 l_cust_rec.user_attribute20,
3472 l_cust_rec.user_attribute21,
3473 l_cust_rec.user_attribute22,
3474 l_cust_rec.user_attribute23,
3475 l_cust_rec.user_attribute24,
3476 l_cust_rec.user_attribute25,
3477 l_cust_rec.user_attribute26,
3478 l_cust_rec.user_attribute27,
3479 l_cust_rec.user_attribute28,
3480 l_cust_rec.user_attribute29,
3481 l_cust_rec.user_attribute30
3482 );
3483
3484 hr_utility.set_location('Exiting ' || l_proc,10);
3485 EXCEPTION
3486 when mass_error then raise;
3487 when others then
3488 pr('Error in '||l_proc);
3489 pr('Position title is '||p_position_title||' Length is '||to_char(length(p_position_title)));
3490 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
3491 l_mslerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
3492 raise mass_error;
3493 END create_mass_act_prev;
3494
3495 --
3496 --
3497 --
3498
3499 procedure get_new_org_dtls( p_mass_realignment_id in NUMBER,
3500 p_position_id in NUMBER,
3501 p_effective_DATE in DATE,
3502 p_personnel_office_id in out NOCOPY VARCHAR2,
3503 p_sub_element_code in out NOCOPY VARCHAR2,
3504 p_duty_station_id in out NOCOPY NUMBER,
3505 p_duty_station_code in out NOCOPY VARCHAR2,
3506 p_duty_station_desc in out NOCOPY VARCHAR2,
3507 p_duty_station_locn_id in out NOCOPY NUMBER,
3508 p_office_symbol in out NOCOPY VARCHAR2,
3509 p_payroll_office_id in out NOCOPY VARCHAR2,
3510 p_org_func_code in out NOCOPY VARCHAR2,
3511 p_appropriation_code1 in out NOCOPY VARCHAR2,
3512 p_appropriation_code2 in out NOCOPY VARCHAR2,
3513 p_position_organization in out NOCOPY VARCHAR2) is
3514
3515 cursor cur_realign_pos_info is
3516 SELECT personnel_office_id,
3517 agency_code_subelement,
3518 duty_station_code,
3519 duty_station_id target_duty_station_id,
3520 LOCATION_ID target_duty_stn_locn_id,
3521 office_symbol,
3522 payroll_office_id,
3523 org_function_code,
3524 appropriation_code1,
3525 appropriation_code2,
3526 position_organization_id
3527 from ghr_mass_real_pos_info_v
3528 WHERE mass_realignment_id = p_mass_realignment_id;
3529
3530 /*
3531 CURSOR POS_EXTRA_CUR (position NUMBER) IS
3532 SELECT position_extra_info_id,
3533 POEI_INFORMATION5,
3534 POEI_INFORMATION6,
3535 POEI_INFORMATION7,
3536 POEI_INFORMATION8,
3537 POEI_INFORMATION9,
3538 POEI_INFORMATION10,
3539 POEI_INFORMATION11,
3540 POEI_INFORMATION12,
3541 POEI_INFORMATION13
3542 POEI_INFORMATION18
3543 from per_position_extra_info
3544 WHERE position_id = (position)
3545 and INFORMATION_TYPE = 'GHR_US_POS_MASS_ACTIONS';
3546 */
3547
3548 l_get_position_extra_info_id NUMBER;
3549
3550 o_poi VARCHAR2(30) := null;
3551 o_agency_code VARCHAR2(30) := null;
3552 o_duty_station_id VARCHAR2(30) := null;
3553 o_duty_stn_locn_id VARCHAR2(30) := null;
3554 o_duty_station_code VARCHAR2(30) := null;
3555 o_office_symbol VARCHAR2(30) := null;
3556 o_payroll_office_id VARCHAR2(30) := null;
3557 o_org_func_code VARCHAR2(30) := null;
3558 o_appropriation_code1 VARCHAR2(30) := null;
3559 o_appropriation_code2 VARCHAR2(30) := null;
3560 o_position_org VARCHAR2(240) := null;
3561
3562 l_poi VARCHAR2(30) := null;
3563 l_agency_code VARCHAR2(30) := null;
3564 l_duty_station_id VARCHAR2(30) := null;
3565 l_duty_stn_locn_id VARCHAR2(30) := null;
3566 l_duty_station_code VARCHAR2(30) := null;
3567 l_duty_station_desc VARCHAR2(150) := null; --Changed the size for Bug# 4648802
3568 l_office_symbol VARCHAR2(30) := null;
3569 l_payroll_office_id VARCHAR2(30) := null;
3570 l_org_func_code VARCHAR2(30) := null;
3571 l_appropriation_code1 VARCHAR2(30) := null;
3572 l_appropriation_code2 VARCHAR2(30) := null;
3573 l_position_org VARCHAR2(240) := null;
3574
3575 ll_poi VARCHAR2(30);
3576 ll_agency_code VARCHAR2(30);
3577 ll_duty_station_id VARCHAR2(30);
3578 ll_duty_stn_locn_id VARCHAR2(30);
3579 ll_duty_station_code VARCHAR2(30);
3580 ll_duty_station_desc VARCHAR2(150); --Changed the size for Bug# 4648802
3581 ll_office_symbol VARCHAR2(30);
3582 ll_payroll_office_id VARCHAR2(30);
3583 ll_org_func_code VARCHAR2(30);
3584 ll_appropriation_code1 VARCHAR2(30);
3585 ll_appropriation_code2 VARCHAR2(30);
3586 ll_position_org VARCHAR2(240);
3587
3588 l_pos_ei_data per_position_extra_info%rowtype;
3589 l_eff_DATE DATE;
3590
3591 BEGIN
3592 g_proc := 'get_new_org_dtls';
3593
3594 -- Initialization for NOCOPY Changes
3595 --
3596 ll_poi := p_personnel_office_id;
3597 ll_agency_code := p_sub_element_code;
3598 ll_duty_station_id := p_duty_station_id;
3599 ll_duty_stn_locn_id := p_duty_station_locn_id;
3600 ll_duty_station_code := p_duty_station_code;
3601 ll_duty_station_desc := p_duty_station_desc;
3602 ll_office_symbol := p_office_symbol;
3603 ll_payroll_office_id := p_payroll_office_id;
3604 ll_org_func_code := p_org_func_code;
3605 ll_appropriation_code1 := p_appropriation_code1;
3606 ll_appropriation_code2 := p_appropriation_code2;
3607 ll_position_org := p_position_organization;
3608 --
3609
3610 if p_effective_DATE > sysDATE then
3611 l_eff_DATE := sysDATE;
3612 ELSE
3613 l_eff_DATE := p_effective_DATE;
3614 END IF;
3615
3616 for r_pos_rec in cur_realign_pos_info
3617 loop
3618 o_poi := r_pos_rec.personnel_office_id;
3619 o_agency_code := r_pos_rec.agency_code_subelement;
3620 o_duty_station_code := r_pos_rec.duty_station_code;
3621 o_duty_station_id := r_pos_rec.target_duty_station_id;
3622 o_duty_stn_locn_id := r_pos_rec.target_duty_stn_locn_id;
3623 /*
3624 get_duty_station_id (o_duty_station_code
3625 ,l_eff_DATE
3626 ,o_duty_station_id);
3627 */
3628 o_office_symbol := r_pos_rec.office_symbol;
3629 o_payroll_office_id := r_pos_rec.payroll_office_id;
3630 o_org_func_code := r_pos_rec.org_function_code;
3631 o_appropriation_code1 := r_pos_rec.appropriation_code1;
3632 o_appropriation_code2 := r_pos_rec.appropriation_code2;
3633 o_position_org := r_pos_rec.position_organization_id;
3634
3635 exit;
3636 END loop;
3637
3638 ghr_history_fetch.fetch_positionei
3639 (p_position_id => p_position_id
3640 ,p_information_type => 'GHR_US_POS_MASS_ACTIONS'
3641 ,p_DATE_effective => l_eff_DATE
3642 ,p_pos_ei_data => l_pos_ei_data);
3643
3644 l_poi := l_pos_ei_data.poei_information5;
3645 l_agency_code := l_pos_ei_data.poei_information6;
3646 l_duty_station_id := l_pos_ei_data.poei_information7;
3647 l_office_symbol := l_pos_ei_data.poei_information8;
3648 l_payroll_office_id := l_pos_ei_data.poei_information9;
3649 l_org_func_code := l_pos_ei_data.poei_information10;
3650 l_appropriation_code1 := l_pos_ei_data.poei_information11;
3651 l_appropriation_code2 := l_pos_ei_data.poei_information12;
3652 l_position_org := l_pos_ei_data.poei_information13;
3653 l_duty_stn_locn_id := l_pos_ei_data.poei_information18;
3654
3655 /*
3656 for pos_extra_rec in pos_extra_cur(p_position_id)
3657 loop
3658 l_poi := pos_extra_rec.poei_information5;
3659 l_agency_code := pos_extra_rec.poei_information6;
3660 l_duty_station_id := to_NUMBER(pos_extra_rec.poei_information7);
3661 l_office_symbol := pos_extra_rec.poei_information8;
3662 l_payroll_office_id := pos_extra_rec.poei_information9;
3663 l_org_func_code := pos_extra_rec.poei_information10;
3664 l_appropriation_code1 := pos_extra_rec.poei_information11;
3665 l_appropriation_code2 := pos_extra_rec.poei_information12;
3666 l_position_org := pos_extra_rec.poei_information13;
3667
3668 exit;
3669 END loop;
3670 */
3671
3672 if l_poi is not null then
3673 ll_poi := l_poi;
3674 ELSIF o_poi is not null then
3675 ll_poi := o_poi;
3676 END IF;
3677
3678 if l_agency_code is not null then
3679 ll_agency_code := l_agency_code;
3680 ELSIF o_agency_code is not null then
3681 ll_agency_code := o_agency_code;
3682 END IF;
3683 if l_duty_station_id is not null then
3684 ll_duty_station_id := to_NUMBER(l_duty_station_id);
3685 ll_duty_stn_locn_id := to_NUMBER(l_duty_stn_locn_id);
3686 ELSIF o_duty_station_id is not null then
3687 ll_duty_station_id := to_NUMBER(o_duty_station_id);
3688 ll_duty_stn_locn_id := to_NUMBER(o_duty_stn_locn_id);
3689 END IF;
3690
3691 if l_duty_station_id is not null or o_duty_station_id is not null then
3692 ----- Duty station is changed
3693 ghr_pa_requests_pkg.get_duty_station_details
3694 (p_duty_station_id => ll_duty_station_id
3695 ,p_effective_DATE => p_effective_DATE
3696 ,p_duty_station_code => ll_duty_station_code
3697 ,p_duty_station_desc => ll_duty_station_desc);
3698
3699 END IF;
3700
3701 if l_office_symbol is not null then
3702 ll_office_symbol := l_office_symbol;
3703 ELSIF o_office_symbol is not null then
3704 ll_office_symbol := o_office_symbol;
3705 END IF;
3706
3707 if l_payroll_office_id is not null then
3708 ll_payroll_office_id := l_payroll_office_id;
3709 ELSIF o_payroll_office_id is not null then
3710 ll_payroll_office_id := o_payroll_office_id;
3711 END IF;
3712
3713 if l_org_func_code is not null then
3714 ll_org_func_code := l_org_func_code;
3715 ELSIF o_org_func_code is not null then
3716 ll_org_func_code := o_org_func_code;
3717 END IF;
3718
3719 if l_appropriation_code1 is not null then
3720 ll_appropriation_code1 := l_appropriation_code1;
3721 ELSIF o_appropriation_code1 is not null then
3722 ll_appropriation_code1 := o_appropriation_code1;
3723 END IF;
3724
3725 if l_appropriation_code2 is not null then
3726 ll_appropriation_code2 := l_appropriation_code2;
3727 pr('pos appr is ',l_appropriation_code2);
3728 ELSIF o_appropriation_code2 is not null then
3729 ll_appropriation_code2 := o_appropriation_code2;
3730 pr('param appr is ',o_appropriation_code2);
3731 ELSE
3732 pr('both are null param appr is ',o_appropriation_code2,l_appropriation_code2);
3733 END IF;
3734
3735 ---dbms_output.put_line('Just before pos org');
3736
3737 if l_position_org is not null then
3738 ll_position_org := l_position_org;
3739 ELSIF o_position_org is not null then
3740 ll_position_org := o_position_org;
3741 END IF;
3742 ---dbms_output.put_line('Just after pos org and I am leaving');
3743
3744 p_personnel_office_id := ll_poi;
3745 p_sub_element_code := ll_agency_code;
3746 p_duty_station_id := ll_duty_station_id;
3747 p_duty_station_code := ll_duty_station_code;
3748 p_duty_station_desc := ll_duty_station_desc;
3749 p_duty_station_locn_id := ll_duty_stn_locn_id;
3750 p_office_symbol := ll_office_symbol;
3751 p_payroll_office_id := ll_payroll_office_id;
3752 p_org_func_code := ll_org_func_code;
3753 p_appropriation_code1 := ll_appropriation_code1;
3754 p_appropriation_code2 := ll_appropriation_code2;
3755 p_position_organization := ll_position_org;
3756
3757 EXCEPTION
3758 when others then
3759 -- Reset for IN OUT params and set OUT params
3760 -- NOCOPY changes
3761 --
3762 p_personnel_office_id := ll_poi;
3763 p_sub_element_code := ll_agency_code;
3764 p_duty_station_id := ll_duty_station_id;
3765 p_duty_station_code := ll_duty_station_code;
3766 p_duty_station_desc := ll_duty_station_desc;
3767 p_duty_station_locn_id := ll_duty_stn_locn_id;
3768 p_office_symbol := ll_office_symbol;
3769 p_payroll_office_id := ll_payroll_office_id;
3770 p_org_func_code := ll_org_func_code;
3771 p_appropriation_code1 := ll_appropriation_code1;
3772 p_appropriation_code2 := ll_appropriation_code2;
3773 p_position_organization := ll_position_org;
3774 --
3775
3776 END get_new_org_dtls;
3777
3778 --
3779 --
3780 --
3781
3782 PROCEDURE assign_to_sf52_rec(
3783 p_person_id in NUMBER,
3784 p_first_name in VARCHAR2,
3785 p_last_name in VARCHAR2,
3786 p_middle_names in VARCHAR2,
3787 p_national_identifier in VARCHAR2,
3788 p_DATE_of_birth in DATE,
3789 p_effective_DATE in DATE,
3790 p_assignment_id in NUMBER,
3791 p_tenure in VARCHAR2,
3792 p_step_or_rate in VARCHAR2,
3793 p_annuitant_indicator in VARCHAR2,
3794 p_pay_rate_determinant in VARCHAR2,
3795 p_work_schedule in VARCHAR2,
3796 p_part_time_hour in VARCHAR2,
3797 p_flsa_category in VARCHAR2,
3798 p_bargaining_unit_status in VARCHAR2,
3799 p_functional_class in VARCHAR2,
3800 p_supervisory_status in VARCHAR2,
3801 p_personnel_office_id in VARCHAR2,
3802 p_sub_element_code in VARCHAR2,
3803 p_duty_station_id in NUMBER,
3804 p_duty_station_locn_id in NUMBER,
3805 p_duty_station_code in ghr_pa_requests.duty_station_code%type,
3806 p_duty_station_desc in ghr_pa_requests.duty_station_desc%type,
3807 p_office_symbol in VARCHAR2,
3808 p_payroll_office_id in VARCHAR2,
3809 p_org_func_code in VARCHAR2,
3810 p_appropriation_code1 in VARCHAR2,
3811 p_appropriation_code2 in VARCHAR2,
3812 p_position_organization in VARCHAR2,
3813 p_lac_sf52_rec in ghr_pa_requests%rowtype,
3814 p_sf52_rec out NOCOPY ghr_pa_requests%rowtype) IS
3815
3816 l_proc VARCHAR2(72)
3817 := g_package || '.assign_to_sf52_rec';
3818 BEGIN
3819
3820 g_proc := 'assign_to_sf52_rec';
3821
3822 hr_utility.set_location('Entering ' || l_proc,5);
3823 p_sf52_rec.person_id := p_person_id;
3824 p_sf52_rec.employee_first_name := p_first_name;
3825 p_sf52_rec.employee_last_name := p_last_name;
3826 p_sf52_rec.employee_middle_names := p_middle_names;
3827 p_sf52_rec.employee_national_identifier := p_national_identifier;
3828 p_sf52_rec.employee_DATE_of_birth := p_DATE_of_birth;
3829 p_sf52_rec.effective_DATE := p_effective_DATE;
3830 p_sf52_rec.employee_assignment_id := p_assignment_id;
3831 p_sf52_rec.tenure := p_tenure;
3832 p_sf52_rec.to_step_or_rate := p_step_or_rate;
3833 p_sf52_rec.annuitant_indicator := p_annuitant_indicator;
3834 p_sf52_rec.pay_rate_determinant := p_pay_rate_determinant;
3835 p_sf52_rec.work_schedule := p_work_schedule;
3836 p_sf52_rec.part_time_hours := p_part_time_hour;
3837 p_sf52_rec.flsa_category := p_flsa_category;
3838 p_sf52_rec.bargaining_unit_status := p_bargaining_unit_status;
3839 p_sf52_rec.functional_class := p_functional_class;
3840 p_sf52_rec.supervisory_status := p_supervisory_status;
3841 p_sf52_rec.personnel_office_id := p_personnel_office_id;
3842 p_sf52_rec.agency_code := p_sub_element_code;
3843 p_sf52_rec.duty_station_id := p_duty_station_id;
3844 p_sf52_rec.duty_station_location_id := p_duty_station_locn_id;
3845 p_sf52_rec.duty_station_code := p_duty_station_code;
3846 p_sf52_rec.duty_station_desc := p_duty_station_desc;
3847 p_sf52_rec.to_office_symbol := p_office_symbol;
3848 p_sf52_rec.appropriation_code1 := p_appropriation_code1;
3849 p_sf52_rec.appropriation_code2 := p_appropriation_code2;
3850
3851 p_sf52_rec.FIRST_LAC1_INFORMATION1 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION1;
3852 p_sf52_rec.FIRST_LAC1_INFORMATION2 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION2;
3853 p_sf52_rec.FIRST_LAC1_INFORMATION3 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION3;
3854 p_sf52_rec.FIRST_LAC1_INFORMATION4 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION4;
3855 p_sf52_rec.FIRST_LAC1_INFORMATION5 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION5;
3856 p_sf52_rec.SECOND_LAC1_INFORMATION1 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION1;
3857 p_sf52_rec.SECOND_LAC1_INFORMATION2 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION2;
3858 p_sf52_rec.SECOND_LAC1_INFORMATION3 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION3;
3859 p_sf52_rec.SECOND_LAC1_INFORMATION4 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION4;
3860 p_sf52_rec.SECOND_LAC1_INFORMATION5 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION5;
3861 p_sf52_rec.FIRST_ACTION_LA_CODE1 := p_lac_sf52_rec.FIRST_ACTION_LA_CODE1;
3862 p_sf52_rec.FIRST_ACTION_LA_CODE2 := p_lac_sf52_rec.FIRST_ACTION_LA_CODE2;
3863 p_sf52_rec.FIRST_ACTION_LA_DESC1 := p_lac_sf52_rec.FIRST_ACTION_LA_DESC1;
3864 p_sf52_rec.FIRST_ACTION_LA_DESC2 := p_lac_sf52_rec.FIRST_ACTION_LA_DESC2;
3865
3866 hr_utility.set_location('Exiting ' || l_proc,10);
3867
3868 EXCEPTION
3869 when mass_error then raise;
3870 when others then
3871 -- NOCOPY Changes
3872 -- Reset IN OUT Params and set OUT params
3873 --
3874 p_sf52_rec := null;
3875 --
3876 pr('Error in '||l_proc);
3877 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
3878 l_mslerrbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
3879 raise mass_error;
3880 END assign_to_sf52_rec;
3881
3882 procedure upd_ext_info_to_null(p_position_id in NUMBER, p_effective_DATE in DATE) is
3883
3884 CURSOR POSITION_EXT_CUR (p_position NUMBER) IS
3885 SELECT position_extra_info_id, object_version_NUMBER
3886 from per_position_extra_info
3887 WHERE position_id = (p_position)
3888 and INFORMATION_TYPE = 'GHR_US_POS_MASS_ACTIONS';
3889
3890 l_Position_EXTRA_INFO_ID NUMBER;
3891 l_OBJECT_VERSION_NUMBER NUMBER;
3892 l_eff_DATE DATE;
3893
3894 l_pos_ei_data per_position_extra_info%rowtype;
3895 l_proc VARCHAR2(72) := g_package || '.upd_ext_info_api';
3896 BEGIN
3897
3898 g_proc := 'upd_ext_info_to_null';
3899
3900 if p_effective_DATE > sysDATE then
3901 l_eff_DATE := sysDATE;
3902 ELSE
3903 l_eff_DATE := p_effective_DATE;
3904 END IF;
3905
3906 -- Bug#2944091 Instead of trunc(sysDATE) , l_eff_DATE is passed.
3907 ghr_history_fetch.fetch_positionei
3908 (p_position_id => p_position_id
3909 ,p_information_type => 'GHR_US_POS_MASS_ACTIONS'
3910 ,p_DATE_effective => l_eff_DATE
3911 ,p_pos_ei_data => l_pos_ei_data);
3912
3913 l_position_extra_info_id := l_pos_ei_data.position_extra_info_id;
3914 l_object_version_NUMBER := l_pos_ei_data.object_version_NUMBER;
3915
3916 if l_position_extra_info_id is not null then
3917
3918 ----- Set the global variable not to fire the trigger
3919 ghr_api.g_api_dml := TRUE;
3920
3921 BEGIN
3922
3923 -- Bug#2944091 Instead of trunc(sysDATE) , l_eff_DATE is passed.
3924 ghr_position_extra_info_api.UPDATE_position_extra_info
3925 (P_POSITION_EXTRA_INFO_ID => l_position_extra_info_id
3926 ,P_OBJECT_VERSION_NUMBER => l_object_version_NUMBER
3927 ,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS'
3928 ,P_EFFECTIVE_DATE => l_eff_DATE
3929 ,P_POEI_INFORMATION3 => null
3930 ,P_POEI_INFORMATION4 => null
3931 ,P_POEI_INFORMATION5 => null
3932 ,P_POEI_INFORMATION6 => null
3933 ,P_POEI_INFORMATION7 => null
3934 ,P_POEI_INFORMATION8 => null
3935 ,P_POEI_INFORMATION9 => null
3936 ,P_POEI_INFORMATION10 => null
3937 ,P_POEI_INFORMATION11 => null
3938 ,P_POEI_INFORMATION12 => null
3939 ,P_POEI_INFORMATION13 => null
3940 ,P_POEI_INFORMATION14 => null
3941 ,P_POEI_INFORMATION18 => null);
3942 EXCEPTION when others then
3943 hr_utility.set_location('UPDATE posei error 3' || l_proc,10);
3944 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
3945 END;
3946
3947 ----- Reset the global variable
3948 ghr_api.g_api_dml := FALSE;
3949
3950
3951 --- Commented the following two lines to remove Validation functionality on Position.
3952 ---- ghr_valiDATE_perwsdpo.valiDATE_perwsdpo(p_position_id);
3953 ---- ghr_valiDATE_perwsdpo.UPDATE_posn_status(p_position_id);
3954 END IF;
3955 END;
3956
3957 PROCEDURE upd_ext_info_api (p_position_id in NUMBER,
3958 info5 in VARCHAR2,
3959 info6 in VARCHAR2,
3960 info7 in VARCHAR2,
3961 info8 in VARCHAR2,
3962 info9 in VARCHAR2,
3963 info10 in VARCHAR2,
3964 info11 in VARCHAR2,
3965 info12 in VARCHAR2,
3966 info13 in VARCHAR2,
3967 info18 in VARCHAR2,
3968 p_effective_DATE in DATE) IS
3969 CURSOR POSITION_EXT_CUR (p_position NUMBER) IS
3970 SELECT position_extra_info_id, object_version_NUMBER
3971 from per_position_extra_info
3972 WHERE position_id = (p_position)
3973 and INFORMATION_TYPE = 'GHR_US_POS_MASS_ACTIONS';
3974
3975 l_cnt NUMBER;
3976 l_Position_EXTRA_INFO_ID NUMBER;
3977 l_OBJECT_VERSION_NUMBER NUMBER;
3978
3979 l_pos_ei_data per_position_extra_info%rowtype;
3980 l_proc VARCHAR2(72) := g_package || '.upd_ext_info_api';
3981 l_eff_DATE DATE;
3982
3983 BEGIN
3984 g_proc := 'upd_ext_info_api';
3985 hr_utility.set_location('Entering ' || l_proc,5);
3986 if p_effective_DATE > sysDATE then
3987 l_eff_DATE := sysDATE;
3988 ELSE
3989 l_eff_DATE := p_effective_DATE;
3990 END IF;
3991
3992 ghr_history_fetch.fetch_positionei
3993 (p_position_id => p_position_id
3994 ,p_information_type => 'GHR_US_POS_MASS_ACTIONS'
3995 ,p_DATE_effective => l_eff_DATE
3996 ,p_pos_ei_data => l_pos_ei_data);
3997
3998 l_position_extra_info_id := l_pos_ei_data.position_extra_info_id;
3999 l_object_version_NUMBER := l_pos_ei_data.object_version_NUMBER;
4000
4001 if l_position_extra_info_id is null then
4002 for pos_ext_rec in position_ext_cur(p_position_id)
4003 loop
4004 l_position_extra_info_id := pos_ext_rec.position_extra_info_id;
4005 l_object_version_NUMBER := pos_ext_rec.object_version_NUMBER;
4006 END loop;
4007 END IF;
4008
4009
4010 if l_position_extra_info_id is null then
4011 ghr_position_extra_info_api.create_position_extra_info
4012 (p_position_id => p_position_id
4013 ,p_information_type => 'GHR_US_POS_MASS_ACTIONS'
4014 ,P_EFFECTIVE_DATE => trunc(l_eff_DATE)
4015 ,P_POEI_information_category => 'GHR_US_POS_MASS_ACTIONS'
4016 ,P_POEI_INFORMATION5 => info5
4017 ,P_POEI_INFORMATION6 => info6
4018 ,P_POEI_INFORMATION7 => info7
4019 ,P_POEI_INFORMATION8 => info8
4020 ,P_POEI_INFORMATION9 => info9
4021 ,P_POEI_INFORMATION10 => info10
4022 ,P_POEI_INFORMATION11 => info11
4023 ,P_POEI_INFORMATION12 => info12
4024 ,P_POEI_INFORMATION13 => info13
4025 ,P_POEI_INFORMATION18 => info18
4026 ,p_POSITION_EXTRA_INFO_ID => l_POSITION_EXTRA_INFO_ID
4027 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
4028 ELSE
4029
4030 ----- Set the global variable not to fire the trigger
4031 ghr_api.g_api_dml := TRUE;
4032
4033 BEGIN
4034 ghr_position_extra_info_api.UPDATE_position_extra_info
4035 (P_POSITION_EXTRA_INFO_ID => l_position_extra_info_id
4036 ,P_OBJECT_VERSION_NUMBER => l_object_version_NUMBER
4037 ,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS'
4038 ,P_EFFECTIVE_DATE => trunc(l_eff_DATE)
4039 ,P_POEI_INFORMATION5 => info5
4040 ,P_POEI_INFORMATION6 => info6
4041 ,P_POEI_INFORMATION7 => info7
4042 ,P_POEI_INFORMATION8 => info8
4043 ,P_POEI_INFORMATION9 => info9
4044 ,P_POEI_INFORMATION10 => info10
4045 ,P_POEI_INFORMATION11 => info11
4046 ,P_POEI_INFORMATION12 => info12
4047 ,P_POEI_INFORMATION13 => info13
4048 ,P_POEI_INFORMATION18 => info18);
4049 EXCEPTION when others then
4050 hr_utility.set_location('UPDATE posei error 4' || l_proc,10);
4051 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
4052 END;
4053
4054 ----- Reset the global variable
4055 ghr_api.g_api_dml := FALSE;
4056
4057 END IF;
4058
4059 --- Commented the following two lines to remove Validation functionality on Position.
4060 --- ghr_valiDATE_perwsdpo.valiDATE_perwsdpo(p_position_id);
4061 --- ghr_valiDATE_perwsdpo.UPDATE_posn_status(p_position_id);
4062 END;
4063
4064 --
4065 --
4066 --
4067
4068 procedure pr (msg VARCHAR2,par1 in VARCHAR2 default null,
4069 par2 in VARCHAR2 default null) is
4070 BEGIN
4071 g_no := g_no +1;
4072 -- insert into l_tmp values (g_no,substr(msg||'-'||par1||' -'||par2||'-',1,199));
4073 ---DBMS_OUTPUT.PUT_LINE(msg||'-'||par1||' -'||par2||'-');
4074 EXCEPTION
4075 when others then
4076 pr('Error in '||'pr');
4077 hr_utility.set_location('Error in pr '||' Err is '||sqlerrm(sqlcode),20);
4078 l_mslerrbuf := 'Error in pr Sql Err is '|| sqlerrm(sqlcode);
4079 raise mass_error;
4080 END;
4081
4082 Procedure UPDATE_position_info
4083 (p_position_data_rec ghr_sf52_pos_UPDATE.position_data_rec_type) is
4084 l_proc VARCHAR2(30):='UPDATE_position_info';
4085 Begin
4086 hr_utility.set_location('Entering ' || l_proc, 10);
4087 hr_utility.set_location('Vacant Position ID ' || to_char(p_position_data_rec.position_id), 10);
4088 ghr_session.set_session_var_for_core( p_position_data_rec.effective_END_DATE );
4089 ghr_sf52_pos_UPDATE.UPDATE_position_info
4090 ( p_pos_data_rec => p_position_data_rec);
4091 hr_utility.set_location('Calling Pust_UPDATE_process ' || l_proc, 50);
4092 ghr_history_api.post_UPDATE_process;
4093 hr_utility.set_location('Leaving ' || l_proc, 100);
4094
4095 END;
4096
4097
4098 END GHR_MRE_PKG;