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