[Home] [Help]
PACKAGE BODY: APPS.GHR_MTO_PKG
Source
1 PACKAGE BODY GHR_MTO_PKG AS
2 /* $Header: ghmtoexe.pkb 120.5.12020000.3 2012/07/05 14:34:37 amnaraya ship $ */
3
4 --
5 -- Global Declaration
6 --
7
8 g_no number := 0;
9 g_package varchar2(32) := 'GHR_MTO_PKG';
10 g_proc varchar2(32) := null;
11
12 l_log_text varchar2(2000) := null;
13 l_mass_errbuf varchar2(2000) := null;
14
15
16
17 Procedure update_position_info
18 (p_position_data_rec in ghr_sf52_pos_update.position_data_rec_type);
19
20 Procedure upd_per_extra_info_to_null(p_person_id in number) ;
21
22 -- Procedure to create remarks M67 Test Sundar 1295
23
24
25 PROCEDURE create_lac_remarks
26 (p_pa_request_id IN ghr_pa_requests.pa_request_id%type,
27 p_new_pa_request_id IN ghr_pa_requests.pa_request_id%type,
28 p_effective_date IN ghr_pa_requests.effective_date%type,
29 p_pa_request_rec IN ghr_pa_requests%rowtype) is
30
31 l_proc VARCHAR2(72) := g_package || '.create_lac_remarks';
32
33 CURSOR cur_pa_rem_cur is
34 SELECT * FROM ghr_pa_remarks
35 WHERE pa_request_id = p_pa_request_id;
36
37 CURSOR cur_rem_code(c_effective_date ghr_pa_requests.effective_date%type) IS
38 SELECT remark_id
39 FROM ghr_remarks
40 WHERE code = 'M67'
41 AND enabled_flag = 'Y'
42 AND nvl(c_effective_date,trunc(sysdate))
43 BETWEEN date_from AND nvl(date_to,nvl(c_effective_date, trunc(sysdate)));
44
45 l_remarks_rec ghr_pa_remarks%rowtype;
46 l_remark_id ghr_pa_remarks.remark_id%type;
47
48 BEGIN
49 g_proc := 'create_lac_remarks';
50 hr_utility.set_location('Entering ' || l_proc,5);
51
52 pr('Inside '||l_proc,to_char(p_pa_request_id),to_char(p_new_pa_request_id));
53 --hr_utility.trace_on(null,'sundar');
54 FOR l_rem_code IN cur_rem_code(p_effective_date) LOOP
55 l_remark_id := l_rem_code.remark_id;
56 END LOOP;
57
58 FOR CUR_PA_REM_rec IN cur_pa_rem_cur
59 LOOP
60
61 l_remarks_rec := cur_pa_rem_rec;
62 -- If the remarks is M67, Then need to populate the address lines
63 hr_utility.set_location('Remark id ' || l_remarks_rec.remark_id,10);
64 hr_utility.set_location('Remark id ' || l_remark_id,10);
65 IF (l_remarks_rec.remark_id = l_remark_id) THEN
66
67 IF p_pa_request_rec.forwarding_address_line1 IS NOT NULL THEN
68 l_remarks_rec.remark_code_information1 := p_pa_request_rec.forwarding_address_line1;
69 END IF;
70 --hr_utility.set_location('1.l_remark_code_information1' || l_remark_code_information1,10);
71
72 IF p_pa_request_rec.forwarding_address_line2 IS NOT NULL THEN
73 l_remarks_rec.remark_code_information1 := l_remarks_rec.remark_code_information1 || ', ' || p_pa_request_rec.forwarding_address_line2;
74 END IF;
75 --hr_utility.set_location('2.l_remark_code_information1' || l_remark_code_information1,11);
76
77 IF p_pa_request_rec.forwarding_address_line3 IS NOT NULL THEN
78 l_remarks_rec.remark_code_information1 := l_remarks_rec.remark_code_information1 || ', ' || p_pa_request_rec.forwarding_address_line3;
79 END IF;
80 --hr_utility.set_location('3.l_remark_code_information1' || l_remark_code_information1,12);
81
82 IF p_pa_request_rec.forwarding_town_or_city IS NOT NULL THEN
83 l_remarks_rec.remark_code_information1 := l_remarks_rec.remark_code_information1 || ', ' || p_pa_request_rec.forwarding_town_or_city;
84 END IF;
85 --hr_utility.set_location('4.l_remark_code_information1' || l_remark_code_information1,13);
86
87 IF p_pa_request_rec.forwarding_region_2 IS NOT NULL THEN
88 l_remarks_rec.remark_code_information1 := l_remarks_rec.remark_code_information1 || ', ' || p_pa_request_rec.forwarding_region_2;
89 END IF;
90 --hr_utility.set_location('5.l_remark_code_information1' || l_remark_code_information1,14);
91
92 IF p_pa_request_rec.forwarding_postal_code IS NOT NULL THEN
93 l_remarks_rec.remark_code_information1 := l_remarks_rec.remark_code_information1 || ', ' || p_pa_request_rec.forwarding_postal_code;
94 END IF;
95 --hr_utility.set_location('6.l_remark_code_information1' || l_remark_code_information1,15);
96
97 IF p_pa_request_rec.forwarding_country IS NOT NULL THEN
98 l_remarks_rec.remark_code_information1 := l_remarks_rec.remark_code_information1 || ', ' || p_pa_request_rec.forwarding_country;
99 END IF;
100 --hr_utility.set_location('7.l_remark_code_information1' || l_remark_code_information1,16);
101
102 IF p_pa_request_rec.forwarding_country_short_name IS NOT NULL THEN
103 l_remarks_rec.remark_code_information1 := l_remarks_rec.remark_code_information1 || ', ' || p_pa_request_rec.forwarding_country_short_name;
104 END IF;
105 --hr_utility.set_location('8.l_remark_code_information1' || l_remark_code_information1,17);
106
107 l_remarks_rec.remark_code_information2 := Null;
108 l_remarks_rec.remark_code_information3 := Null;
109 l_remarks_rec.remark_code_information4 := Null;
110 l_remarks_rec.remark_code_information5 := Null;
111 l_remarks_rec.description := 'Forwarding address: ' || l_remarks_rec.remark_code_information1;
112 hr_utility.set_location('description ' || l_remarks_rec.description,10);
113 END IF;
114 hr_utility.set_location('l_remarks_rec.object_version_number ' ||l_remarks_rec.object_version_number,10);
115 pr('Rem id '||to_char(l_remarks_rec.remark_id));
116 ghr_pa_remarks_api.create_pa_remarks
117 (p_validate => false
118 ,p_pa_request_id => p_new_pa_request_id
119 ,p_remark_id => l_remarks_rec.remark_id
120 ,p_description => l_remarks_rec.description
121 ,p_remark_code_information1 => l_remarks_rec.remark_code_information1
122 ,p_remark_code_information2 => l_remarks_rec.remark_code_information2
123 ,p_remark_code_information3 => l_remarks_rec.remark_code_information3
124 ,p_remark_code_information4 => l_remarks_rec.remark_code_information4
125 ,p_remark_code_information5 => l_remarks_rec.remark_code_information5
126 ,p_pa_remark_id => l_remarks_rec.pa_remark_id
127 ,p_object_version_number => l_remarks_rec.object_version_number
128 );
129
130 END LOOP;
131 --hr_utility.trace_off;
132 hr_utility.set_location('Exiting ' || l_proc,10);
133
134 EXCEPTION
135 WHEN OTHERS THEN
136 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
137 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm;
138 raise mass_error;
139 END create_lac_remarks;
140
141 --
142 -- End Global declaration
143 --
144
145 procedure execute_mto (p_errbuf out NOCOPY varchar2,
146 p_retcode out NOCOPY number,
147 p_mass_transfer_id in number,
148 p_action in varchar2,
149 p_show_vacant_pos in varchar2 default 'NO') is
150
151 cursor child_orgs(cp_orgid number,
152 child_fl varchar2,
153 org_pos_fl varchar2,
154 org_str_id number) is
155 select a.organization_id_child org_pos_id
156 from per_org_structure_elements a,
157 per_org_structure_versions b
158 where a.org_structure_version_id = b.org_structure_version_id
159 and a.org_structure_version_id = org_str_id
160 and child_fl = 'Y'
161 and org_pos_fl = 'O'
162 and a.org_structure_element_id in
163 (
164 select org_structure_element_id
165 from per_org_structure_elements
166 -- VSM added nvl( .. to the start... clause
167 -- enhancement in selection criteria as org_id can be be null [Masscrit.doc]
168 start with organization_id_parent = cp_orgid
169 connect by prior organization_id_child = organization_id_parent
170 )
171 union
172 select b.ORGANIZATION_ID org_pos_id
173 from per_organization_units b
174 -- VSM added nvl( .. to the start... clause
175 -- enhancement in selection criteria as org_id can be be null [Masscrit.doc]
176 where b.organization_id = nvl(cp_orgid, b.organization_id)
177 and org_pos_fl = 'O'
178 union
179 select a.subordinate_position_id org_pos_id
180 from per_pos_structure_elements a,
181 per_pos_structure_versions b
182 where a.pos_structure_version_id = b.pos_structure_version_id
183 and a.pos_structure_version_id = org_str_id
184 and child_fl = 'Y'
185 and org_pos_fl = 'P'
186 and a.pos_structure_element_id in
187 (
188 select pos_structure_element_id
189 from per_pos_structure_elements
190 start with parent_position_id = cp_orgid
191 connect by prior subordinate_position_id = parent_position_id
192 )
193 union
194 select b.position_id org_pos_id
195 from hr_positions_f b
196 where b.position_id = cp_orgid
197 and org_pos_fl = 'P';
198
199 /* and child_fl = 'N';*/
200
201 -- Bug 4377361 included EMP_APL for person type condition
202 -- Bug # 13061568 Modified the cursor to compare agency code if it is available
203 -- to improve the performance
204 cursor cur_people (p_org_id number,
205 org_pos_fl varchar2,
206 effective_date date,
207 p_agency varchar2) is
208 select ppf.person_id PERSON_ID,
209 ppf.first_name FIRST_NAME,
210 ppf.last_name LAST_NAME,
211 ppf.middle_names MIDDLE_NAMES,
212 ppf.full_name FULL_NAME,
213 ppf.date_of_birth DATE_OF_BIRTH,
214 ppf.national_identifier NATIONAL_IDENTIFIER,
215 --Bug # 9329643
216 ppf.employee_number EMPLOYEE_NUMBER,
217 paf.position_id POSITION_ID,
218 paf.assignment_id ASSIGNMENT_ID,
219 paf.grade_id GRADE_ID,
220 paf.job_id JOB_ID,
221 paf.location_id LOCATION_ID,
222 paf.organization_id ORGANIZATION_ID,
223 paf.business_group_id BUSINESS_GROUP_ID,
224 punits.name ORGANIZATION_NAME
225 from per_assignments_f paf,
226 per_people_f ppf,
227 per_person_types ppt,
228 per_organization_units punits
229 -- VSM added nvl( .. to the start... clause
230 -- enhancement in selection criteria as org_id can be be null [Masscrit.doc]
231 where (paf.organization_id = nvl(p_org_id, paf.organization_id)
232 and
233 org_pos_fl = 'O')
234 and ppf.person_id = paf.person_id
235 and trunc(effective_date) between paf.effective_start_date
236 and paf.effective_end_date
237 and paf.primary_flag = 'Y'
238 and paf.assignment_type <> 'B'
239 and ppf.current_employee_flag = 'Y'
240 and trunc(effective_date) between ppf.effective_start_date
241 and ppf.effective_end_date
242 and ppf.person_type_id = ppt.person_type_id
243 and ppt.system_person_type IN ('EMP','EMP_APL')
244 and paf.organization_id = punits.organization_id
245 and paf.position_id is not null
246 and (p_agency is not null and
247 ghr_api.get_position_agency_code_pos(paf.position_id,paf.business_group_id) like p_agency
248 OR
249 p_agency is null)
250 union
251 select ppf.person_id PERSON_ID,
252 ppf.first_name FIRST_NAME,
253 ppf.last_name LAST_NAME,
254 ppf.middle_names MIDDLE_NAMES,
255 ppf.full_name FULL_NAME,
256 ppf.date_of_birth DATE_OF_BIRTH,
257 ppf.national_identifier NATIONAL_IDENTIFIER,
258 --Bug # 9329643
259 ppf.employee_number EMPLOYEE_NUMBER,
260 paf.position_id POSITION_ID,
261 paf.assignment_id ASSIGNMENT_ID,
262 paf.grade_id GRADE_ID,
263 paf.job_id JOB_ID,
264 paf.location_id LOCATION_ID,
265 paf.organization_id ORGANIZATION_ID,
266 paf.business_group_id BUSINESS_GROUP_ID,
267 punits.name ORGANIZATION_NAME
268 from per_assignments_f paf,
269 per_people_f ppf,
270 per_person_types ppt,
271 per_organization_units punits
272 where (paf.position_id = nvl(p_org_id,paf.position_id)
273 and
274 org_pos_fl = 'P')
275 and ppf.person_id = paf.person_id
276 and trunc(effective_date) between paf.effective_start_date
277 and paf.effective_end_date
278 and paf.primary_flag = 'Y'
279 and paf.assignment_type <> 'B'
280 and ppf.current_employee_flag = 'Y'
281 and trunc(effective_date) between ppf.effective_start_date
282 and ppf.effective_end_date
283 and ppf.person_type_id = ppt.person_type_id
284 and ppt.system_person_type IN ('EMP','EMP_APL')
285 and paf.organization_id = punits.organization_id
286 and paf.position_id is not null
287 and (p_agency is not null and
288 ghr_api.get_position_agency_code_pos(paf.position_id,paf.business_group_id) like p_agency
289 OR
290 p_agency is null);
291
292 cursor unassigned_pos (p_org_id number,
293 org_pos_fl varchar2,
294 effective_date date,
295 p_agency varchar2
296 ) is
297 select null PERSON_ID,
298 'VACANT' FIRST_NAME,
299 'VACANT' LAST_NAME,
300 'VACANT' FULL_NAME,
301 null MIDDLE_NAMES,
302 null DATE_OF_BIRTH,
303 null NATIONAL_IDENTIFIER,
304 --Bug # 9329643
305 null EMPLOYEE_NUMBER,
306 position_id POSITION_ID,
307 null ASSIGNMENT_ID,
308 to_number(null) GRADE_ID,
309 JOB_ID,
310 pop.LOCATION_ID,
311 pop.ORGANIZATION_ID,
312 pop.BUSINESS_GROUP_ID,
313 punits.name ORGANIZATION_NAME,
314 pop.availability_status_id
315 from hr_positions_f pop,
316 per_organization_units punits
317 where pop.position_id in
318 (
319 select position_id POSITION_ID
320 from hr_positions_f
321 where (organization_id = nvl(p_org_id,organization_id) and org_pos_fl = 'O'
322 or position_id = nvl(p_org_id,position_id) and org_pos_fl = 'P')
323 and trunc(effective_date) between
324 effective_start_date and effective_end_date
325 and (p_agency is not null and
326 ghr_api.get_position_agency_code_pos(position_id,business_group_id) like p_agency
327 OR
328 p_agency is null)
329 MINUS
330 select a.position_id
331 from per_people_f p, per_assignments_f a
332 where (a.organization_id = nvl(p_org_id,organization_id) and org_pos_fl = 'O'
333 or a.position_id = nvl(p_org_id,position_id) and org_pos_fl = 'P')
334 and trunc(effective_date) between a.effective_start_date
335 and a.effective_end_date
336 and a.primary_flag = 'Y'
337 and a.assignment_type <> 'B'
338 and p.current_employee_flag = 'Y'
339 and p.person_id =a.person_id
340 and a.position_id = pop.position_id
341 and trunc(effective_date) between p.effective_start_date
342 and p.effective_end_date
343 and (p_agency is not null and
344 ghr_api.get_position_agency_code_pos(a.position_id,a.business_group_id) like p_agency
345 OR
346 p_agency is null)
347 )
348 and trunc(effective_date)
349 between pop.effective_start_date and pop.effective_end_date
350 and pop.organization_id = punits.organization_id;
351 -- added Join for tables a,p. a.person_id=p.person_id and a.position_id=pop.position_id
352 -- Bug 3804526
353
354 cursor c_grade_kff (grd_id number) is
355 select gdf.segment1
356 ,gdf.segment2
357 from per_grades grd,
358 per_grade_definitions gdf
359 where grd.grade_id = grd_id
360 and grd.grade_definition_id = gdf.grade_definition_id;
361
362 cursor ghr_mto (p_mass_transfer_id number) is
363 select name, effective_date, old_organization_id,
364 OLD_ORG_STRUCTURE_VERSION_ID, status,
365 reason, org_structure_id, office_symbol,
366 AGENCY_CODE_SUBELEMENT,
367 PERSONNEL_OFFICE_ID, duty_station_code,duty_station_id,
368 old_position_id,
369 old_pos_structure_version_id,
370 TO_AGENCY_CODE_SUBELEMENT,
371 NVL(INTERFACE_FLAG,'N') INTERFACE_FLAG,
372 PA_REQUEST_ID
373 from ghr_mass_transfers
374 where mass_transfer_id = p_mass_transfer_id
375 and TRANSFER_TYPE = 'OUT'
376 for update of status nowait;
377
378 ----- Added cursor by AVR
379 CURSOR PA_REQ_EXT_INFO_CUR (p_pa_request_id number) is
380 SELECT PA_REQUEST_EXTRA_INFO_ID,
381 OBJECT_VERSION_NUMBER
382 FROM GHR_PA_REQUEST_EXTRA_INFO
383 WHERE INFORMATION_TYPE = 'GHR_US_PAR_MASS_TERM'
384 and pa_request_id = p_pa_request_id;
385
386
387 l_PA_REQUEST_EXTRA_INFO_ID number;
388 l_pa_OBJECT_VERSION_NUMBER number;
389 l_dummy varchar2(35);
390 l_agency_code ghr_pa_requests.agency_code%type;
391 --------
392
393 l_assignment_id per_assignments_f.assignment_id%type;
394 l_position_id per_assignments_f.position_id%type;
395 l_grade_id per_assignments_f.grade_id%type;
396 --l_grade_id number;
397
398 l_business_group_id per_assignments_f.business_group_id%type;
399
400 l_position_title varchar2(300);
401 l_position_number varchar2(20);
402 l_position_seq_no varchar2(20);
403
404 l_mass_cnt number := 0;
405 l_recs_failed number := 0;
406
407 l_tenure varchar2(35);
408 l_annuitant_indicator varchar2(35);
409 l_pay_rate_determinant varchar2(35);
410 l_work_schedule varchar2(35);
411 l_part_time_hour varchar2(35);
412 l_pay_table_id number;
413 l_pay_plan varchar2(30);
414 l_grade_or_level varchar2(30);
415 l_step_or_rate varchar2(30);
416 l_pay_basis varchar2(30);
417 l_location_id number;
418 l_duty_station_id number;
419 l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
420 l_duty_station_code ghr_pa_requests.duty_station_code%type;
421
422 l_check_child varchar2(2);
423 l_check_org_pos varchar2(2);
424 l_org_pos_id number;
425 l_org_pos_str_id number;
426
427 l_effective_date date;
428 r_effective_date date;
429 p_mass_transfer_name varchar2(80);
430 p_organization_id number;
431 p_org_hierarchy_id number;
432 p_position_id number;
433 p_pos_hierarchy_id number;
434 p_interface_flag varchar2(1);
435 l_pa_request_id number;
436
437 p_status varchar2(1);
438 p_reason varchar2(240);
439 p_org_structure_id varchar2(30);
440 p_office_symbol varchar2(30);
441 p_agency_sub_elem_code varchar2(30);
442 p_to_agency_code varchar2(30);
443 p_personnel_office_id varchar2(30);
444 p_duty_station_id number(15);
445 p_duty_station_code varchar2(10);
446 p_position_title varchar2(240);
447 p_pay_plan varchar2(2);
448 p_occ_code varchar2(9);
449
450 l_personnel_office_id varchar2(300);
451 l_org_structure_id varchar2(300);
452 l_office_symbol varchar2(30);
453 l_occ_series varchar2(30);
454 l_sub_element_code varchar2(300);
455
456 l_payroll_office_id varchar2(30);
457 l_org_func_code varchar2(30);
458 l_appropriation_code1 varchar2(30);
459 l_appropriation_code2 varchar2(30);
460 l_position_organization varchar2(240);
461
462 t_personnel_office_id varchar2(300);
463 t_sub_element_code varchar2(300);
464 t_duty_station_id number(15);
465 t_duty_station_desc ghr_pa_requests.duty_station_desc%type;
466 t_duty_station_code ghr_pa_requests.duty_station_code%type;
467 t_office_symbol varchar2(30);
468 t_payroll_office_id varchar2(30);
469 t_org_func_code varchar2(30);
470 t_appropriation_code1 varchar2(30);
471 t_appropriation_code2 varchar2(30);
472 t_position_organization varchar2(240);
473
474 l_auo_premium_pay_indicator varchar2(30);
475 l_ap_premium_pay_indicator varchar2(30);
476 l_retention_allowance number;
477 l_supervisory_differential number;
478 l_staffing_differential number;
479
480 l_out_step_or_rate varchar2(30);
481 l_out_pay_rate_determinant varchar2(30);
482 l_PT_eff_start_date date;
483 l_open_pay_fields boolean;
484 l_message_set boolean;
485 l_calculated boolean;
486
487 l_user_table_id number;
488 l_executive_order_no varchar2(30);
489 l_executive_order_date date;
490
491 l_row_cnt number := 0;
492
493 l_sf52_rec ghr_pa_requests%rowtype;
494 l_lac_sf52_rec ghr_pa_requests%rowtype;
495 l_errbuf varchar2(2000);
496
497 l_retcode number;
498
499 l_pos_ei_data per_position_extra_info%rowtype;
500 l_pos_grp1_rec per_position_extra_info%rowtype;
501 l_pos_grp2_rec per_position_extra_info%rowtype;
502
503 l_pay_calc_in_data ghr_pay_calc.pay_calc_in_rec_type;
504 l_pay_calc_out_data ghr_pay_calc.pay_calc_out_rec_type;
505 l_sel_flg varchar2(2);
506 l_sel_status varchar2(32);
507
508 l_first_action_la_code1 varchar2(30);
509 l_first_action_la_code2 varchar2(30);
510
511 l_remark_code1 varchar2(30);
512 l_remark_code2 varchar2(30);
513 l_avail_status_id hr_positions_f.availability_status_id%type;
514 --
515
516 REC_BUSY exception;
517 pragma exception_init(REC_BUSY,-54);
518
519 l_proc varchar2(72)
520 := g_package || '.execute_mto';
521 l_ind number := 0;
522 l_break varchar2(1) := 'N';
523
524 --7533027
525 l_pos_ei_grade_data per_position_extra_info%rowtype;
526 --7533027
527
528 --7186094
529 l_appl_name varchar2(10);
530 l_last_msg_set varchar2(30);
531 l_ret_sel_flag varchar2(2);
532 l_ret_comments varchar2(150);
533 l_ret_mtfr_id number;
534 l_insert_log varchar2(1):= 'Y';
535 l_par_agency VARCHAR2(10);
536
537 BEGIN
538 p_retcode := 0;
539 g_proc := 'execute_mto';
540 --hr_utility.trace_on(null,'sundar');
541 pr('Inside execute mto');
542 pr('Mass Transfer id is '||p_mass_transfer_id,' Action is '|| p_action);
543 hr_utility.set_location('Entering ' || l_proc,5);
544 l_ind := 10;
545 --7186094
546 l_insert_log := 'Y';
547 pr('Before set log');
548 --ghr_mto_int.set_log_program_name('Mass Transfer OUT');
549 pr('After set log');
550
551 BEGIN
552 FOR mto IN ghr_mto (p_mass_transfer_id)
553 LOOP
554 p_mass_transfer_name := mto.name;
555 l_effective_date := mto.effective_date;
556 p_organization_id := mto.old_organization_id;
557 p_org_hierarchy_id := mto.OLD_ORG_STRUCTURE_VERSION_ID;
558 p_status := mto.status;
559 p_reason := mto.reason;
560 p_org_structure_id := mto.org_structure_id;
561 p_office_symbol := mto.office_symbol;
562 p_agency_sub_elem_code:= mto.AGENCY_CODE_SUBELEMENT;
563 p_personnel_office_id := mto.PERSONNEL_OFFICE_ID;
564 p_duty_station_code := mto.duty_station_code;
565 --p_position_title := mto.position_title;
566 p_duty_station_id := mto.duty_station_id;
567 p_to_agency_code := mto.TO_AGENCY_CODE_SUBELEMENT;
568 p_position_id := mto.old_position_id;
569 p_pos_hierarchy_id := mto.old_pos_structure_version_id;
570 p_interface_flag := mto.interface_flag;
571 l_pa_request_id := mto.pa_request_id;
572
573 exit;
574 END LOOP;
575
576 EXCEPTION
577 when REC_BUSY then
578 hr_utility.set_location('Mass Transfer is in use',1);
579 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
580 hr_utility.set_message(8301, 'GHR_38477_LOCK_ON_MTO');
581 hr_utility.raise_error;
582 when others then
583 hr_utility.set_location('Error in '||l_proc||' Sql err is '||sqlerrm(sqlcode),1);
584 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
585 raise mass_error;
586 END;
587
588 IF upper(p_action) = 'CREATE' then
589 ghr_mto_int.set_log_program_name('GHR_MTO_PKG');
590 ELSE
591 ghr_mto_int.set_log_program_name('MTO_'||p_mass_transfer_name);
592 END IF;
593
594 IF upper(p_action) = 'CREATE' then
595 if l_pa_request_id is null then
596 hr_utility.set_message(8301, 'GHR_38567_SELECT_LAC_REMARKS');
597 hr_utility.raise_error;
598 END IF;
599 END IF;
600
601 ghr_msl_pkg.get_lac_dtls(l_pa_request_id,
602 l_lac_sf52_rec);
603
604 if upper(p_action) = 'CREATE'
605 /* and p_interface_flag = 'N' */ then
606
607 DECLARE
608 l_loc_errbuf varchar2(2000);
609 l_loc_retcode number;
610 BEGIN
611 --- Call dump out to make sure its interfaced
612 pr('Before ghr_mto_pkg.execute mto for dump out');
613
614 ghr_mto_pkg.execute_mto (l_loc_errbuf,
615 l_loc_retcode,
616 p_mass_transfer_id,
617 'DUMP OUT');
618 pr('After execute mto - for dump out Error is ',l_loc_errbuf);
619
620 if l_loc_errbuf is not null then
621 l_mass_errbuf := 'DUMP OUT Failed '||l_loc_errbuf;
622 raise mass_error;
623 else
624 pr('No error in execute mto dump out ');
625 commit;
626 begin
627 FOR mto IN ghr_mto (p_mass_transfer_id)
628 LOOP
629 p_mass_transfer_name := mto.name;
630 p_interface_flag := mto.interface_flag;
631 exit;
632 END LOOP;
633 EXCEPTION
634 when REC_BUSY then
635 hr_utility.set_location('Mass Transfer is in use',1);
636 l_mass_errbuf := 'Error in '||l_proc||
637 ' This Mass Trasnfer is in use';
638 hr_utility.set_message(8301, 'GHR_38477_LOCK_ON_MTO');
639 hr_utility.raise_error;
640 when others then
641 hr_utility.set_location('Error in '||l_proc||
642 ' at select from mass tfr'||
643 ' Sql err is '||sqlerrm(sqlcode),1);
644 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '||
645 sqlerrm(sqlcode);
646 raise mass_error;
647 END;
648 END IF;
649 EXCEPTION
650 when mass_error then raise;
651 when others then
652 hr_utility.set_location('Error in '||l_proc||
653 ' Sql err is '||sqlerrm(sqlcode),1);
654 l_mass_errbuf := 'Error in '||l_proc||' at call for dump proc'||
655 ' Sql Err is '|| sqlerrm(sqlcode);
656 raise mass_error;
657 END;
658
659 if p_interface_flag = 'N' then
660 l_mass_errbuf := 'Interface has failed already - Cannot process';
661 raise mass_error;
662 end if;
663
664 END IF; ---- End if for /*interface flag = 'N' and */ ACTION = 'CREATE'
665
666
667 l_ind := 20;
668 --purge_old_data(p_mass_transfer_id);
669
670 l_ind := 30;
671 hr_utility.set_location('After fetch mto '||to_char(l_effective_date),1);
672 pr('After sel mass transfers',to_char(l_effective_date));
673
674 if p_position_id is not null then
675 l_check_org_pos := 'P';
676 l_org_pos_id := p_position_id;
677 l_org_pos_str_id := p_pos_hierarchy_id;
678 if p_pos_hierarchy_id is null then
679 l_check_child := 'N';
680 else
681 l_check_child := 'Y';
682 end if;
683 else
684 -- VSM [Enhancement Masscrit.doc]
685 -- if neither Org nor Position is entered then system will fetch records for all the organization
686 --if p_organization_id is not null then
687 l_check_org_pos := 'O';
688 l_org_pos_id := p_organization_id;
689 l_org_pos_str_id := p_org_hierarchy_id;
690 if p_organization_id is null then
691 l_break := 'Y';
692 end if;
693 if p_org_hierarchy_id is null then
694 l_check_child := 'N';
695 else
696 l_check_child := 'Y';
697 end if;
698 end if;
699
700 --BUG # 13061568
701 l_par_agency := p_agency_sub_elem_code;
702 IF p_agency_sub_elem_code is not null AND length(p_agency_sub_elem_code) = 2 THEN
703 l_par_agency := p_agency_sub_elem_code||'%';
704 END IF;
705
706 pr('Org id ',to_char(p_organization_id));
707 pr('Org hier id',to_char(p_org_hierarchy_id));
708
709 FOR org in child_orgs (l_org_pos_id,
710 l_check_child,
711 l_check_org_pos,
712 l_org_pos_str_id)
713 LOOP
714 if upper(p_action) = 'REPORT' and p_status = 'P' THEN
715 r_effective_date := l_effective_date - 1;
716 else
717 r_effective_date := l_effective_date;
718 end if;
719
720 if l_break = 'Y' then
721 org.org_pos_id := null;
722 end if;
723
724 BEGIN
725
726 pr ('After child orgs ',to_char(org.org_pos_id));
727 FOR per IN cur_people (org.org_pos_id,
728 l_check_org_pos,
729 r_effective_date,
730 l_par_agency)
731 LOOP
732 BEGIN
733 pr('AFTER FET PEOPLE');
734
735 savepoint execute_mto_sp;
736
737 l_ind := 40;
738 l_assignment_id := per.assignment_id;
739 l_position_id := per.position_id;
740 l_grade_id := per.grade_id;
741 l_business_group_id := per.business_group_iD;
742 l_location_id := per.location_id;
743
744 pr(' Assign Id/Pos ',to_char(per.assignment_id),to_char(per.position_id));
745 pr(' Grade/Bus grp ',to_char(per.grade_id),to_char(per.business_group_id));
746 pr(' Location/Eff dt ',to_char(per.location_id),to_char(l_effective_date));
747
748 l_ind := 50;
749 /******************
750 if upper(p_action) = 'DUMP OUT' then
751 ---------------------
752 if check_select_flg(per.position_id,upper(p_action),
753 l_effective_date,
754 p_mass_transfer_id,
755 l_sel_flg) then
756 begin
757 l_errbuf := null;
758 ghr_mto_int.mass_transfer_out (
759 l_errbuf,
760 l_retcode,
761 p_mass_transfer_id,
762 per.person_id);
763
764 if l_errbuf is not null then
765 pr('Error in ghr_mto_int.mass_transfer_out'||l_errbuf);
766 hr_utility.set_location
767 ('Error in ghr_mto_int.mass_transfer_out'||
768 'Err is '||l_errbuf,20);
769 l_mass_errbuf := 'Error in ghr_mto_int.mass_transfer_out'||
770 ' Err is '|| l_errbuf;
771 raise mass_error;
772 end if;
773 exception
774 when mass_error then raise;
775 when others then null;
776 pr('Error in create sf52 - Err is '||
777 l_errbuf||' '||to_char(l_retcode));
778 pr('Err is '||sqlerrm(sqlcode));
779 l_mass_errbuf := 'Error in ghr_mto_int.mass_transfer_out'||
780 ' Sql Err is '|| sqlerrm(sqlcode);
781 raise mass_error;
782 end;
783 end if;
784 ***************/
785 ---------------------
786 if upper(p_action) = 'REPORT' AND p_status = 'P' THEN
787 pop_dtls_from_pa_req(per.person_id,l_effective_date,
788 p_mass_transfer_id, per.employee_number);
789 ELSE
790 if check_select_flg(per.position_id,upper(p_action),
791 l_effective_date,
792 p_mass_transfer_id,
793 l_sel_flg) then
794
795 pr('After check sel flg value is ',l_sel_flg,l_sel_status);
796 l_ind := 70;
797 begin
798 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
799 (p_location_id => l_location_id
800 ,p_duty_station_id => l_duty_station_id);
801 exception
802 when others then
803 pr('Error in Ghr_pa_requests_pkg.get_sf52_asg_ddf_details');
804 hr_utility.set_location('Error in Ghr_pa_requests_pkg.get_sf52_loc_ddf_details'||
805 'Err is '||sqlerrm(sqlcode),20);
806 l_mass_errbuf := 'Error in get_sf52_loc_ddf_details Sql Err is '||
807 sqlerrm(sqlcode);
808 raise mass_error;
809 end;
810
811 get_pos_grp1_ddf(l_position_id,
812 l_effective_date,
813 l_personnel_office_id,
814 l_org_structure_id,
815 l_office_symbol,
816 l_position_organization,
817 l_pos_grp1_rec);
818
819 l_occ_series := ghr_api.get_job_occ_series_job
820 (p_job_id => per.job_id
821 ,p_business_group_id => per.business_group_id
822 );
823
824 ghr_msl_pkg.get_sub_element_code_pos_title(l_position_id,
825 per.person_id,
826 l_business_group_id,
827 l_assignment_id,
828 l_effective_date,
829 l_sub_element_code,
830 l_position_title,
831 l_position_number,
832 l_position_seq_no);
833
834 pr('before check eleg Sub element code ',l_sub_element_code);
835
836 IF check_eligibility(
837 p_org_structure_id,
838 p_office_symbol,
839 p_personnel_office_id,
840 p_agency_sub_elem_code,
841 p_duty_station_id,
842
843 l_org_structure_id,
844 l_office_symbol,
845 l_personnel_office_id,
846 l_sub_element_code,
847 l_duty_station_id,
848 l_occ_series,
849 p_mass_transfer_id,
850 upper(p_action),
851 l_effective_date,
852 per.person_id) then
853
854 l_ind := 60;
855 BEGIN
856 ghr_pa_requests_pkg.get_sf52_asg_ddf_details
857 (l_assignment_id,
858 l_effective_date,
859 l_tenure,
860 l_annuitant_indicator,
861 l_pay_rate_determinant,
862 l_work_schedule,
863 l_part_time_hour);
864 EXCEPTION
865 when others then
866 pr('Error in Ghr_pa_requests_pkg.get_sf52_asg_ddf_details');
867 hr_utility.set_location('Error in Ghr_pa_requests_pkg.get_sf52_asg_ddf_details'||
868 'Err is '||sqlerrm(sqlcode),20);
869 l_mass_errbuf := 'Error in get_sf52_asgddf_details Sql Err is '||
870 sqlerrm(sqlcode);
871 raise mass_error;
872 END;
873 --Bug#4126137 Moved get_pay_plan_and_table_id from line number 886
874 l_ind := 65;
875 BEGIN
876 ghr_msl_pkg.get_pay_plan_and_table_id(l_pay_rate_determinant,
877 per.person_id,
878 l_position_id,l_effective_date,
879 l_grade_id, l_assignment_id,'SHOW',l_pay_plan,
880 l_pay_table_id,l_grade_or_level, l_step_or_rate,
881 l_pay_basis);
882 EXCEPTION
883 when ghr_msl_pkg.msl_error then
884 --7186094 added to insert the log for this expired retained details
885 -- message only for show and preview and not for create and dump
886 -- when the selection flag is not available
887 l_appl_name := 'GHR';
888 hr_utility.get_message_details (msg_name => l_last_msg_set,
889 msg_appl => l_appl_name);
890 if l_last_msg_set = 'GHR_38927_MISSING_MA_RET_DET' then
891 get_extra_info_comments(l_position_id,
892 l_effective_date,
893 l_ret_sel_flag,
894 l_ret_comments,
895 l_ret_mtfr_id);
896 g_proc := 'check_eligibility';
897 IF l_ret_sel_flag is NULL and upper(p_action) IN ('CREATE','DUMP OUT') THEN
898 l_insert_log := 'N';
899 end if;
900 end if;
901 l_mass_errbuf := hr_utility.get_message;
902 raise mass_error;
903 END;
904
905 get_pos_grp2_ddf(l_position_id,
906 l_effective_date,
907 l_org_func_code,
908 l_appropriation_code1,
909 l_appropriation_code2,
910 l_pos_grp2_rec);
911 l_ind := 90;
912 BEGIN
913 ghr_pa_requests_pkg.get_duty_station_details
914 (p_duty_station_id => l_duty_station_id
915 ,p_effective_date => l_effective_date
916 ,p_duty_station_code => l_duty_station_code
917 ,p_duty_station_desc => l_duty_station_desc);
918 EXCEPTION
919 when others then
920 pr('Error in Ghr_pa_requests_pkg.get_duty_station_details');
921 hr_utility.set_location('Error in Ghr_pa_requests_pkg.get_duty_station_details'||
922 'Err is '||sqlerrm(sqlcode),20);
923 l_mass_errbuf := 'Error in get_duty_station_details Sql Err is '||
924 sqlerrm(sqlcode);
925 raise mass_error;
926
927 END;
928
929 l_ind := 130;
930 IF upper(p_action) IN ('SHOW','REPORT') THEN
931 pr('Bef create ghr cpdf temp');
932 create_mass_act_prev (
933 l_effective_date,
934 per.date_of_birth,
935 per.full_name,
936 per.national_identifier,
937 --Bug # 9329643
938 per.employee_number,
939 l_duty_station_code,
940 l_duty_station_desc,
941 l_personnel_office_id,
942 l_position_id,
943 l_position_title,
944 l_position_number,
945 l_position_seq_no,
946 l_org_structure_id,
947 l_sub_element_code,
948 per.person_id,
949 p_mass_transfer_id,
950 l_sel_flg,
951 l_grade_or_level,
952 l_step_or_rate,
953 l_pay_plan,
954 l_occ_series,
955 l_office_symbol,
956 per.organization_id,
957 per.organization_name,
958 null, null, null, null, null, null,
959 null, null, null, null, null, null,
960 p_to_agency_code,
961 /*
962
963 l_position_organization,
964 t_personnel_office_id,
965 t_sub_element_code,
966 t_duty_station_id,
967 t_duty_station_code,
968 t_duty_station_desc,
969 t_office_symbol,
970 t_payroll_office_id,
971 t_org_func_code,
972 t_appropriation_code1,
973 t_appropriation_code2,
974 t_position_organization,
975 */
976 l_tenure,
977 l_pay_rate_determinant,
978 p_action,
979 l_assignment_id);
980
981 elsif upper(p_action) = 'DUMP OUT' then
982 begin
983 l_errbuf := null;
984 ghr_mto_int.mass_transfer_out (
985 l_errbuf,
986 l_retcode,
987 p_mass_transfer_id,
988 per.person_id);
989
990 if l_errbuf is not null then
991 pr('Error in ghr_mto_int.mass_transfer_out'||
992 l_errbuf);
993 hr_utility.set_location
994 ('Error in ghr_mto_int.mass_transfer_out'||
995 'Err is '||l_errbuf,20);
996 l_mass_errbuf :=
997 'Error in ghr_mto_int.mass_transfer_out'||
998 ' Err is '|| l_errbuf;
999 raise mass_error;
1000 end if;
1001 exception
1002 when mass_error then raise;
1003 when others then null;
1004 pr('Error in create sf52 - Err is '||
1005 l_errbuf||' '||to_char(l_retcode));
1006 pr('Err is '||sqlerrm(sqlcode));
1007 l_mass_errbuf :=
1008 'Error in ghr_mto_int.mass_transfer_out'||
1009 ' Sql Err is '|| sqlerrm(sqlcode);
1010 raise mass_error;
1011 end;
1012 l_ind := 180;
1013 ELSIF upper(p_action) = 'CREATE' then ---- Not in Show, Report
1014 pr('Bef get pay plan and table id');
1015 l_ind := 190;
1016 BEGIN
1017 ghr_msl_pkg.get_pay_plan_and_table_id
1018 (l_pay_rate_determinant,per.person_id,
1019 l_position_id,l_effective_date,
1020 l_grade_id, l_assignment_id,'CREATE',
1021 l_pay_plan,l_pay_table_id,
1022 l_grade_or_level, l_step_or_rate,
1023 l_pay_basis);
1024 EXCEPTION
1025 when ghr_msl_pkg.msl_error then
1026 l_mass_errbuf := hr_utility.get_message;
1027 raise mass_error;
1028 END;
1029 l_ind := 200;
1030
1031 --Added by Dinkar for quick fix.
1032 ---declare -- Commented by AVR
1033 ---l_agency_code ghr_pa_requests.agency_code%type;
1034 begin
1035 get_to_agency (per.person_id,
1036 l_effective_date,
1037 l_agency_code);
1038
1039 hr_utility.set_location('Agency Code in quick fix ' || l_agency_code,5);
1040
1041 if l_agency_code is null then
1042 l_agency_code := p_to_agency_code;
1043 end if;
1044
1045 hr_utility.set_location('Agency Code in Next ' || l_agency_code,6);
1046
1047
1048 pr('Bef assign to sf52 rec');
1049 assign_to_sf52_rec(
1050 per.person_id,
1051 per.first_name,
1052 per.last_name,
1053 per.middle_names,
1054 per.national_identifier,
1055 per.date_of_birth,
1056 l_effective_date,
1057 l_assignment_id,
1058 l_tenure,
1059 l_step_or_rate,
1060 l_annuitant_indicator,
1061 l_pay_rate_determinant,
1062 l_work_schedule,
1063 l_part_time_hour,
1064 l_pos_ei_data.poei_information7, --FLSA Category
1065 l_pos_ei_data.poei_information8, --Bargaining Unit Status
1066 l_pos_ei_data.poei_information11,--Functional Class
1067 l_pos_ei_data.poei_information16,--Supervisory Status,
1068 l_personnel_office_id,
1069 l_sub_element_code,
1070 l_duty_station_id,
1071 l_duty_station_code,
1072 l_duty_station_desc,
1073 l_office_symbol,
1074 l_payroll_office_id,
1075 l_org_func_code,
1076 l_appropriation_code1,
1077 l_appropriation_code2,
1078 l_position_organization,
1079 HR_GENERAL.DECODE_LOOKUP('GHR_US_AGENCY_CODE_2',substr(l_agency_code,1,2)), --AVR
1080 l_agency_code, -- p_to_position_org_line1 -- AVR
1081 ------- l_agency_code, -- p_first_noa_information1 (in earlier version)
1082 l_lac_sf52_rec,
1083 l_sf52_rec);
1084 end;
1085
1086 pr('Bef create sf52 for mass chgs');
1087
1088 --------------------Create SF-52 ---------------------------
1089
1090 begin
1091 -- Adding the following code to keep track of the RPA type and Mass action id
1092 --
1093 l_sf52_rec.rpa_type := 'MTO';
1094 l_sf52_rec.mass_action_id := p_mass_transfer_id;
1095 --
1096 ghr_mass_changes.create_sf52_for_mass_changes
1097 (p_mass_action_type => 'MASS_TRANSFER_OUT',
1098 p_pa_request_rec => l_sf52_rec,
1099 p_errbuf => l_errbuf,
1100 p_retcode => l_retcode);
1101
1102 ------ Added by Dinkar for List reports problem
1103
1104 declare
1105 l_pa_request_number ghr_pa_requests.request_number%TYPE;
1106 begin
1107
1108 l_pa_request_number :=
1109 l_sf52_rec.request_number||'-'||p_mass_transfer_id;
1110
1111 ghr_par_upd.upd
1112 (p_pa_request_id => l_sf52_rec.pa_request_id,
1113 p_object_version_number => l_sf52_rec.object_version_number,
1114 p_request_number => l_pa_request_number
1115 );
1116 end;
1117
1118 ---------------------------------------
1119
1120 ----Added AVR
1121 begin
1122 for pa_rec in PA_REQ_EXT_INFO_CUR (l_sf52_rec.pa_request_id)
1123 loop
1124 l_PA_REQUEST_EXTRA_INFO_ID := pa_rec.PA_REQUEST_EXTRA_INFO_ID;
1125 l_pa_OBJECT_VERSION_NUMBER := pa_rec.OBJECT_VERSION_NUMBER;
1126 exit;
1127 end loop;
1128
1129 if l_pa_request_extra_info_id is null then
1130 ghr_par_extra_info_api.create_pa_request_extra_info
1131 (p_validate => false,
1132 p_pa_request_id => l_sf52_rec.pa_request_id,
1133 p_information_type => 'GHR_US_PAR_MASS_TERM',
1134 p_rei_information_category => 'GHR_US_PAR_MASS_TERM',
1135 p_rei_information3 => l_agency_code,
1136 p_pa_request_extra_info_id => l_dummy,
1137 p_object_version_number => l_dummy);
1138 else
1139 ghr_par_extra_info_api.update_pa_request_extra_info
1140 (p_validate => false,
1141 p_rei_information3 => l_agency_code,
1142 p_pa_request_extra_info_id => l_PA_REQUEST_EXTRA_INFO_ID,
1143 p_object_version_number => l_pa_OBJECT_VERSION_NUMBER);
1144 end if;
1145 ----- commit;
1146 exception
1147 when others then
1148 hr_utility.set_location('Error in ghr_par_extra info.create pa req'||
1149 ' Sql Err is '|| sqlerrm(sqlcode) || l_proc, 225);
1150 l_mass_errbuf := 'Error in ghr_par_extra info.create pa req'||
1151 ' Sql Err is '|| sqlerrm(sqlcode);
1152 raise mass_error;
1153 end;
1154 --------------------- Added AVR end
1155
1156 if l_errbuf is null then
1157 pr('No error in create sf52 sel flg is '||l_sel_flg);
1158 hr_utility.set_location('Before commiting',2);
1159
1160 --Bug # 9329643 Modified SSN to Emp No
1161 ghr_mto_int.log_message(
1162 p_procedure => 'Successful Completion',
1163 p_message =>
1164 'Name: '||per.full_name ||' Emp No: '||
1165 per.employee_number|| ' Mass Transfer : '||
1166 p_mass_transfer_name ||' SF52 Successfully completed');
1167 create_lac_remarks(l_pa_request_id,l_sf52_rec.pa_request_id,l_effective_date,l_sf52_rec);
1168 /*ghr_msl_pkg.create_lac_remarks(l_pa_request_id,
1169 l_sf52_rec.pa_request_id); */
1170 --create_remarks(l_sf52_rec,'M67');
1171 upd_ext_info_to_null(per.position_id);
1172 upd_per_extra_info_to_null(per.person_id);
1173
1174 commit;
1175 else
1176 pr('Error in create sf52',l_errbuf);
1177 hr_utility.set_location('Error in '||to_char(per.position_id),20);
1178 --l_recs_failed := l_recs_failed + 1;
1179 raise mass_error;
1180 end if;
1181 exception
1182 when mass_error then raise;
1183 when others then null;
1184 pr('Error in create sf52 - Err is '||
1185 l_errbuf||' '||to_char(l_retcode));
1186 pr('Err is '||sqlerrm(sqlcode));
1187 l_mass_errbuf := 'Error in ghr_mass_chg.create_sf52 '||
1188 ' Sql Err is '|| sqlerrm(sqlcode);
1189 raise mass_error;
1190 end;
1191
1192 END IF; ---- End if for p_action = 'CREATE' ----
1193
1194
1195
1196 END IF; --- End if for Check Eligibility ----
1197 ELSE ------ Else for Check Select flag ----
1198 l_ind := 260;
1199 --update_SEL_FLG(PER.PERSON_ID,l_effective_date);
1200 null; ---commented
1201 END IF; ---- End if for check select flag ----
1202 END IF; ---- End if for p_action
1203
1204 l_ind := 270;
1205 L_row_cnt := L_row_cnt + 1;
1206 l_mass_cnt := l_mass_cnt +1;
1207 if upper(p_action) <> 'CREATE' THEN
1208 if L_row_cnt > 50 then
1209 commit;
1210 L_row_cnt := 0;
1211 end if;
1212 end if;
1213 EXCEPTION
1214 WHEN mass_ERROR THEN
1215 HR_UTILITY.SET_LOCATION('Error occurred in '||l_proc||' Sql error '||sqlerrm(sqlcode),10);
1216 begin
1217 ROLLBACK TO EXECUTE_MTO_SP;
1218 exception
1219 when others then null;
1220 end;
1221
1222 --7186094 added to check before insertion
1223 if NVL(l_insert_log,'Y') = 'Y' then
1224 -- Bug 3718167 Added Person Full Name,SSN in the message.
1225 --Bug # 9329643 Modified SSN to Emp No
1226 l_log_text := 'Error in '||l_proc||' '||
1227 ' For Mass Transfer Name : '||p_mass_transfer_name||
1228 ' for Employee: '||per.full_name||
1229 ' Emp No: ' ||per.employee_number||
1230 l_mass_errbuf;
1231 hr_utility.set_location('before creating entry in log file',10);
1232 l_recs_failed := l_recs_failed + 1;
1233 begin
1234 ghr_mto_int.log_message(
1235 p_procedure => g_proc,
1236 p_message => l_log_text);
1237 end;
1238 end if;
1239 WHEN others then
1240 hr_utility.set_location('Error (Others) occurred in '||l_proc||
1241 ' Sql error '||sqlerrm(sqlcode),20);
1242 begin
1243 ROLLBACK TO EXECUTE_MTO_SP;
1244 exception
1245 when others then null;
1246 END;
1247 -- Bug 3718167 Added Person Full Name,SSN in the message.
1248 --Bug # 9329643 Modified SSN to Emp No
1249 l_log_text := 'Error (others) in '||l_proc||
1250 'Line is '|| to_char(l_ind)||
1251 ' For Mass Transfer Name : '||p_mass_transfer_name||
1252 ' for Employee: '||per.full_name||
1253 ' Emp No: ' || per.employee_number||
1254 ' Sql Err is '||sqlerrm(sqlcode);
1255 hr_utility.set_location('before creating entry in log file',20);
1256 l_recs_failed := l_recs_failed + 1;
1257 begin
1258 ghr_mto_int.log_message(
1259 p_procedure => g_proc,
1260 p_message => l_log_text);
1261 end;
1262 END;
1263 END LOOP;
1264 if (upper(p_action) = 'SHOW' or (upper(p_action) = 'REPORT' and
1265 p_show_vacant_pos = 'YES' )) THEN
1266 FOR per IN unassigned_pos (org.org_pos_id,
1267 l_check_org_pos,
1268 l_effective_date,
1269 l_par_agency)
1270 LOOP
1271 l_avail_status_id := per.availability_status_id;
1272 --Begin Bug# 13684234
1273 /* commented below code and added func get_pos_availability_status, since
1274 hr_general.decode_availability_status is return pos_aval_status description not the system code
1275 IF ( HR_GENERAL.DECODE_AVAILABILITY_STATUS(l_avail_status_id)
1276 not in ('Eliminated','Frozen','Deleted') ) THEN*/
1277
1278 IF ghr_pa_requests_pkg.get_pos_availability_status(l_avail_status_id)
1279 NOT IN ('DELETED','FROZEN','ELIMINATED') THEN
1280 --End Bug# 13684234
1281
1282 l_position_id := per.position_id;
1283 l_grade_id := per.grade_id;
1284 l_business_group_id := per.business_group_iD;
1285 l_location_id := per.location_id;
1286
1287 if check_select_flg(per.position_id,upper(p_action),
1288 l_effective_date,
1289 p_mass_transfer_id,
1290 l_sel_flg) then
1291 pr('After check sel flg value is ',l_sel_flg,l_sel_status);
1292 null;
1293 end if;
1294
1295 l_position_title := ghr_api.get_position_title_pos
1296 (p_position_id => l_position_id
1297 ,p_business_group_id => l_business_group_id ) ;
1298
1299 l_sub_element_code := ghr_api.get_position_agency_code_pos
1300 (l_position_id,l_business_group_id);
1301
1302 l_occ_series := ghr_api.get_job_occ_series_job
1303 (p_job_id => per.job_id
1304 ,p_business_group_id => per.business_group_id
1305 );
1306
1307 l_position_number := ghr_api.get_position_desc_no_pos
1308 (p_position_id => l_position_id
1309 ,p_business_group_id => per.business_group_id
1310 );
1311
1312 l_position_seq_no := ghr_api.get_position_sequence_no_pos
1313 (p_position_id => l_position_id
1314 ,p_business_group_id => per.business_group_id
1315 );
1316
1317 --Bug # 7533027 Added to fetch the grade id for vacant position
1318 --from extra information
1319 ghr_history_fetch.fetch_positionei
1320 (p_position_id => l_position_id
1321 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
1322 ,p_date_effective => l_effective_date
1323 ,p_pos_ei_data => l_pos_ei_grade_data
1324 );
1325
1326 l_grade_id := l_pos_ei_grade_data.poei_information3;
1327 l_pay_plan := NULL;
1328 l_grade_or_level := NULL;
1329 --7533027
1330
1331 FOR c_grade_kff_rec IN c_grade_kff (l_grade_id)
1332 LOOP
1333 l_pay_plan := c_grade_kff_rec.segment1;
1334 l_grade_or_level := c_grade_kff_rec.segment2;
1335 exit;
1336 end loop;
1337
1338 get_pos_grp1_ddf(l_position_id,
1339 l_effective_date,
1340 l_personnel_office_id,
1341 l_org_structure_id,
1342 l_office_symbol,
1343 l_position_organization,
1344 l_pos_grp1_rec);
1345
1346 begin
1347 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
1348 (p_location_id => l_location_id
1349 ,p_duty_station_id => l_duty_station_id);
1350 end;
1351
1352 begin
1353 ghr_pa_requests_pkg.get_duty_station_details
1354 (p_duty_station_id => l_duty_station_id
1355 ,p_effective_date => l_effective_date
1356 ,p_duty_station_code => l_duty_station_code
1357 ,p_duty_station_desc => l_duty_station_desc);
1358 end;
1359
1360 pr(' Assign Id/Pos ',null, to_char(per.position_id));
1361
1362 IF check_eligibility(
1363 p_org_structure_id,
1364 p_office_symbol,
1365 p_personnel_office_id,
1366 p_agency_sub_elem_code,
1367 null, -- p_duty_station_id, passed as null so that duty station
1368 -- will not be validated
1369
1370 l_org_structure_id,
1371 l_office_symbol,
1372 l_personnel_office_id,
1373 l_sub_element_code,
1374 null, ---- l_duty_station_id,
1375 l_occ_series,
1376 p_mass_transfer_id,
1377 upper(p_action),
1378 l_effective_date,
1379 null, ---- person_id
1380 null) then
1381
1382 create_mass_act_prev (
1383 l_effective_date,
1384 per.date_of_birth,
1385 per.full_name,
1386 per.national_identifier,
1387 --Bug # 9329643
1388 per.employee_number,
1389 l_duty_station_code,
1390 l_duty_station_desc,
1391 l_personnel_office_id,
1392 l_position_id,
1393 l_position_title,
1394 l_position_number,
1395 l_position_seq_no,
1396 l_org_structure_id,
1397 l_sub_element_code,
1398 per.person_id,
1399 p_mass_transfer_id,
1400 l_sel_flg,
1401 l_grade_or_level,
1402 null, ---l_step_or_rate,
1403 l_pay_plan,
1404 l_occ_series,
1405 l_office_symbol,
1406 per.organization_id,
1407 per.organization_name,
1408 l_position_organization,
1409 null, ---t_personnel_office_id,
1410 null, ---t_sub_element_code,
1411 null, ---t_duty_station_id,
1412 null, ---t_duty_station_code,
1413 null, ---t_duty_station_desc,
1414 null, ---t_office_symbol,
1415 null, ---t_payroll_office_id,
1416 null, ---t_org_func_code,
1417 null, ---t_appropriation_code1,
1418 null, ---t_appropriation_code2,
1419 null, ---t_position_organization,
1420 p_to_agency_code,
1421 null, ---l_tenure,
1422 null, ---l_pay_rate_determinant,
1423 p_action,
1424 null);
1425 l_mass_cnt := l_mass_cnt +1;
1426 end if; --------- if Check eligiblity
1427 End if; --- check for Eliminated, deleted and frozen positions
1428 END LOOP; --------- Unassigned pos loop
1429 end if; -------- If action = show or ( report and vacant pos = yes)
1430
1431 if upper(p_action) = 'CREATE' then
1432 --- For all the vacant positions. Once this program is called with
1433 -- CREATE Option. The positions will be end date.
1434 -- No 52s will be created
1435 -- and it is agreed in the design review meeting by MACROSS and JMACGOY.
1436 DECLARE
1437
1438 l_position_id hr_positions_f.position_id%TYPE;
1439 l_position_data_rec ghr_sf52_pos_update.position_data_rec_type;
1440
1441 BEGIN
1442 l_avail_status_id := NULL;
1443 FOR per_vacant IN unassigned_pos (org.org_pos_id,
1444 l_check_org_pos,
1445 l_effective_date,
1446 l_par_agency)
1447 LOOP
1448 -- Bug#4201666 Added the check to Restrict Eliminated, Frozen, Deleted Positions.
1449 l_avail_status_id := per_vacant.availability_status_id;
1450 --Begin Bug# 13684234
1451 /* commented below code and added func get_pos_availability_status, since
1452 hr_general.decode_availability_status is return pos_aval_status description not the system code
1453 IF ( HR_GENERAL.DECODE_AVAILABILITY_STATUS(l_avail_status_id)
1454 not in ('Eliminated','Frozen','Deleted') ) THEN*/
1455
1456 IF ghr_pa_requests_pkg.get_pos_availability_status(l_avail_status_id)
1457 NOT IN ('DELETED','FROZEN','ELIMINATED') THEN
1458 --End Bug# 13684234
1459
1460 IF check_select_flg(per_vacant.position_id,upper(p_action),
1461 l_effective_date,
1462 p_mass_transfer_id,
1463 l_sel_flg) then
1464
1465 l_position_id := per_vacant.position_id;
1466 l_position_data_rec.position_id := l_position_id;
1467 /* l_position_data_rec.effective_end_date
1468 := l_effective_date;
1469 l_position_data_rec.effective_date
1470 := l_effective_date; */
1471 -- Bug 3531540 Need to end date only on the next date
1472 l_position_data_rec.effective_end_date
1473 := l_effective_date + 1;
1474 l_position_data_rec.effective_date
1475 := l_effective_date + 1;
1476 -- End Bug 3531540
1477 l_business_group_id := per_vacant.business_group_iD;
1478
1479 l_position_title := ghr_api.get_position_title_pos
1480 (p_position_id => l_position_id
1481 ,p_business_group_id => l_business_group_id ) ;
1482
1483 ---Added by AVR Check eligibility is missing 03/30/00
1484 l_sub_element_code := ghr_api.get_position_agency_code_pos
1485 (l_position_id,l_business_group_id);
1486
1487 get_pos_grp1_ddf(l_position_id,
1488 l_effective_date,
1489 l_personnel_office_id,
1490 l_org_structure_id,
1491 l_office_symbol,
1492 l_position_organization,
1493 l_pos_grp1_rec);
1494
1495 hr_utility.set_location('Vac.POS-l_position_title ' || l_position_title,5);
1496 hr_utility.set_location('Vac.POS-l_personnel_office_id ' || l_personnel_office_id,5);
1497 hr_utility.set_location('Vac.POS-l_org_structure_id ' || l_org_structure_id,5);
1498 hr_utility.set_location('Vac.POS-l_office_symbol ' || l_office_symbol,5);
1499 hr_utility.set_location('Vac.POS-l_sub_element_code ' || l_sub_element_code,5);
1500
1501 IF check_eligibility(
1502 p_org_structure_id,
1503 p_office_symbol,
1504 p_personnel_office_id,
1505 p_agency_sub_elem_code,
1506 null, -- p_duty_station_id, passed as null so that duty station
1507 -- will not be validated
1508 l_org_structure_id,
1509 l_office_symbol,
1510 l_personnel_office_id,
1511 l_sub_element_code,
1512 null, ---- l_duty_station_id,
1513 l_occ_series,
1514 p_mass_transfer_id,
1515 null, --- Action sent as null for vacant position
1516 l_effective_date,
1517 null, ---- person_id
1518 null) then
1519
1520 hr_utility.set_location('Vac Pos Selected ' || l_position_title,5);
1521 ---AVR end
1522 -- VSM- Bug # 758441
1523 -- Position history not created for Date end and org id
1524 -- Created wrapper procedure update_position_info for
1525 -- ghr_sf52_pos_update.update_position_info
1526 -- #### ghr_sf52_pos_update.update_position_info
1527 update_position_info
1528 (l_position_data_rec);
1529
1530 upd_ext_info_to_null(per_vacant.position_id);
1531
1532 ghr_mto_int.log_message(
1533 p_procedure => 'Successful Completion',
1534 p_message =>
1535 'Vacant Position : '||l_position_title
1536 || ' Mass Transfer : '||
1537 p_mass_transfer_name ||' Vacant pos Successfully completed');
1538
1539 END IF; -- Check eligibility
1540 END IF; -- Check select_flag
1541 END IF;
1542 END LOOP;
1543 EXCEPTION
1544 WHEN OTHERS THEN
1545 l_mass_errbuf := 'Error in ghr_sf52_pos_update.update_position_info'||' Sql Err is '|| sqlerrm(sqlcode);
1546 raise mass_error;
1547
1548 END;
1549 end if; -------- If action = create
1550
1551 END;
1552 if l_break = 'Y' then
1553 exit;
1554 end if;
1555
1556 END LOOP;
1557
1558 pr('Count is ',to_char(l_mass_cnt),'Failed recs '||to_char(l_recs_failed));
1559
1560 if (l_recs_failed = 0 ) then
1561 IF upper(p_action) in ('CREATE','DUMP OUT') THEN
1562 begin
1563 update ghr_mass_transfers
1564 set status = decode(upper(p_action),'CREATE','P',status),
1565 interface_flag = decode(upper(p_action),'DUMP OUT','Y',
1566 interface_flag)
1567 where mass_transfer_id = p_mass_transfer_id;
1568 EXCEPTION
1569 when others then
1570 HR_UTILITY.SET_LOCATION('Error in Update ghr_mto Sql error '||sqlerrm(sqlcode),30);
1571 hr_utility.set_message(8301, 'GHR_38571_UPD_GHR_MTO_FAILURE');
1572 hr_utility.raise_error;
1573 END;
1574 end if;
1575 ELSE
1576 -- 4215268
1577 IF upper(p_action) = 'DUMP OUT' and nvl(l_recs_failed,0) > 0 and nvl(l_recs_failed,0) < nvl(l_mass_cnt,0) THEN
1578 update ghr_mass_transfers
1579 set status = decode(upper(p_action),'CREATE','P',status),
1580 interface_flag = decode(upper(p_action),'DUMP OUT','Y',
1581 interface_flag)
1582 where mass_transfer_id = p_mass_transfer_id;
1583
1584 l_log_text := ' Error(s) occurred during creation of the Interface Records for few employees.'||
1585 ' To process the Mass Transfer Out for that employees, correct the error(s) reported' ||
1586 ' in the Process Log ';
1587 ghr_mto_int.log_message(
1588 p_procedure => 'Interface Generation Failed',
1589 p_message => l_log_text);
1590
1591 ELSE
1592 -- 4215268
1593 p_errbuf := 'Error in '||l_proc || ' Details in GHR_PROCESS_LOG';
1594 p_retcode := 2;
1595 IF upper(p_action) = 'CREATE' THEN
1596 update ghr_mass_transfers
1597 set status = 'E'
1598 where mass_transfer_id = p_mass_transfer_id;
1599 END IF;
1600 -- Bug#4183516/4201876 Added/Modified the message text and message name.
1601 IF upper(p_action) = 'DUMP OUT' THEN
1602 l_log_text := ' Error(s) occurred during creation of the Interface Records.'||
1603 ' To process the Mass Transfer Out, correct the error(s) reported' ||
1604 ' in the Process Log or deselect the employees from the' ||
1605 ' Mass Transfer Out Preview before executing.';
1606 ghr_mto_int.log_message(
1607 p_procedure => 'Interface Generation Failed',
1608 p_message => l_log_text);
1609 END IF;
1610 END IF;
1611 end if;
1612 pr(' Recs failed '||to_char(l_recs_failed)||
1613 'mass cnt is '||to_char(l_mass_cnt));
1614 COMMIT;
1615
1616 EXCEPTION
1617 when mass_error then
1618 begin
1619 ROLLBACK TO EXECUTE_MTO_SP;
1620 exception
1621 when others then null;
1622 end;
1623 IF upper(p_action) = 'CREATE' THEN
1624 update ghr_mass_transfers
1625 set status = 'E'
1626 where mass_transfer_id = p_mass_transfer_id;
1627 END IF;
1628 HR_UTILITY.SET_LOCATION('Error occurred in '||l_proc||' Sql error '||sqlerrm(sqlcode),10);
1629 p_errbuf := 'Error in '||l_proc || 'Details in GHR_PROCESS_LOG';
1630 p_retcode := 2;
1631 hr_utility.set_location('before creating entry in log file',10);
1632 --Bug#4183516 Skip this process log entry as one entry is already
1633 -- written during DUMP OUT Process at l_recs_failed NOT NULL part .
1634 IF l_mass_errbuf NOT LIKE 'DUMP OUT Failed%' THEN
1635 l_log_text := 'Error in '||l_proc||' '||
1636 ' For Mass Transfer Name : '||p_mass_transfer_name||
1637 l_mass_errbuf;
1638 ghr_mto_int.log_message(
1639 p_procedure => g_proc,
1640 p_message => l_log_text);
1641 END IF;
1642 when others then
1643 begin
1644 ROLLBACK TO EXECUTE_MTO_SP;
1645 exception
1646 when others then null;
1647 end;
1648 IF upper(p_action) = 'CREATE' THEN
1649 update ghr_mass_transfers
1650 set status = 'E'
1651 where mass_transfer_id = p_mass_transfer_id;
1652 END IF;
1653 HR_UTILITY.SET_LOCATION('Error (Others2) occurred in '||l_proc||' Sql error '||sqlerrm(sqlcode),30);
1654 l_log_text := 'Error in '||l_proc||
1655 ' For Mass Transfer Name : '||p_mass_transfer_name||
1656 ' Sql Err is '||sqlerrm(sqlcode);
1657 l_recs_failed := l_recs_failed + 1;
1658 p_errbuf := 'Error in '||l_proc || 'Details in GHR_PROCESS_LOG';
1659 p_retcode := 2;
1660 hr_utility.set_location('before creating entry in log file',30);
1661 begin
1662 ghr_mto_int.log_message(
1663 p_procedure => g_proc,
1664 p_message => l_log_text);
1665 end;
1666 END EXECUTE_MTO;
1667
1668 --
1669 --
1670 --
1671 -- Procedure Deletes all records processed by the report
1672 --
1673
1674 procedure purge_processed_recs(p_session_id in number,
1675 p_err_buf out NOCOPY varchar2) is
1676 begin
1677 p_err_buf := null;
1678 delete from ghr_mass_actions_preview
1679 where mass_action_type = 'TRANSFER'
1680 and session_id = p_session_id;
1681 commit;
1682
1683 exception
1684 when others then
1685 p_err_buf := 'Sql err '|| sqlerrm(sqlcode);
1686 end;
1687
1688 procedure pop_dtls_from_pa_req(p_person_id in number,p_effective_date in date,
1689 p_mass_transfer_id in number, p_employee_number in varchar2) is
1690
1691 cursor ghr_pa_req_cur is
1692 select EMPLOYEE_DATE_OF_BIRTH,
1693 substr(EMPLOYEE_LAST_NAME||', '||EMPLOYEE_FIRST_NAME||' '||
1694 EMPLOYEE_MIDDLE_NAMES,1,240) FULL_NAME,
1695 EMPLOYEE_NATIONAL_IDENTIFIER,
1696 DUTY_STATION_CODE,
1697 DUTY_STATION_DESC,
1698 PERSONNEL_OFFICE_ID,
1699 TO_POSITION_ID POSITION_ID,
1700 TO_POSITION_TITLE POSITION_TITLE,
1701 TO_POSITION_NUMBER POSITION_NUMBER,
1702 TO_POSITION_SEQ_NO POSITION_SEQ_NO,
1703 null org_structure_id,
1704 FROM_AGENCY_CODE,
1705 PERSON_ID,
1706 'Y' Sel_flag,
1707 first_action_la_code1,
1708 first_action_la_code2,
1709 NULL REMARK_CODE1,
1710 NULL REMARK_CODE2,
1711 from_grade_or_level,
1712 from_step_or_rate,
1713 FROM_OFFICE_SYMBOL,
1714 from_pay_plan,
1715 FROM_OCC_CODE,
1716 TO_ORGANIZATION_ID ORGANIZATION_ID,
1717 ---B.NAME ORGANIZATION_NAME,
1718 EMPLOYEE_ASSIGNMENT_ID
1719 from ghr_pa_requests /**, per_organization_units B*/
1720 where person_id = p_person_id
1721 and effective_date = p_effective_date
1722 and first_noa_code = '352'
1723 -- Added by Dinkar for reports
1724 and substr(request_number,(instr(request_number,'-')+1))
1725 = to_char(p_mass_transfer_id);
1726
1727 l_proc varchar2(72)
1728 := g_package || '.pop_dtls_from_pa_req';
1729 begin
1730 hr_utility.set_location('Entering ' || l_proc,5);
1731 pr('Entering ' || l_proc,to_char(p_person_id),to_char(p_effective_date));
1732 g_proc := 'pop_dtls_from_pa_req';
1733 for pa_req_rec in ghr_pa_req_cur
1734 loop
1735 pr('name is '||pa_req_rec.full_name);
1736 create_mass_act_prev (p_effective_date,
1737 pa_req_rec.employee_date_of_birth,
1738 pa_req_rec.full_name,
1739 pa_req_rec.employee_national_identifier,
1740 --Bug # 9329643
1741 p_employee_number,
1742 pa_req_rec.duty_station_code,
1743 pa_req_rec.duty_station_desc,
1744 pa_req_rec.personnel_office_id,
1745 pa_req_rec.position_id,
1746 pa_req_rec.position_title,
1747 pa_req_rec.position_number,
1748 pa_req_rec.position_seq_no,
1749 pa_req_rec.org_structure_id,
1750 pa_req_rec.from_agency_code,
1751 pa_req_rec.person_id,
1752 p_mass_transfer_id,
1753 'Y', --- Sel flag
1754 pa_req_rec.from_grade_or_level,
1755 pa_req_rec.from_step_or_rate,
1756 pa_req_rec.from_pay_plan,
1757 pa_req_rec.from_occ_code,
1758 pa_req_rec.from_office_symbol,
1759 pa_req_rec.organization_id,
1760 null,--pa_req_rec.organization_name,
1761 null,
1762 null, null, null, null, null,
1763 null, null, null, null, null, null, null,
1764 null, ---l_tenure,
1765 null, ---l_pay_rate_determinant,
1766 'REPORT',
1767 pa_req_rec.EMPLOYEE_ASSIGNMENT_ID);
1768 exit;
1769 END LOOP;
1770 hr_utility.set_location('Exiting ' || l_proc,10);
1771 exception
1772 when mass_error then raise;
1773 when others then
1774 pr('Error in '||l_proc);
1775 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
1776 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
1777 raise mass_error;
1778 end pop_dtls_from_pa_req;
1779
1780 --
1781 --
1782 --
1783
1784 function check_select_flg(p_position_id in number,
1785 p_action in varchar2,
1786 p_effective_date in date,
1787 p_mtfr_id in number,
1788 p_sel_flg in out NOCOPY varchar2)
1789 return boolean IS
1790 l_comments varchar2(150);
1791 l_mtfr_id number;
1792
1793 l_proc varchar2(72) := g_package || '.check_select_flg';
1794 l_sel_flg varchar2(3);
1795 begin
1796 l_sel_flg := p_sel_flg;
1797 hr_utility.set_location('Entering ' || l_proc,5);
1798 g_proc := 'check_select_flg';
1799 get_extra_info_comments(p_position_id,p_effective_date,p_sel_flg,
1800 l_comments,l_mtfr_id);
1801 --p_sel_status := l_sel_status;
1802 --pr('Sel Status ',l_sel_status,p_mtfr_name);
1803
1804 if p_sel_flg is null then
1805 p_sel_flg := 'Y';
1806 --Bug#4126137 Commented ins_upd_pos_extra_info as this is invalidating all the positions.
1807 -- ins_upd_pos_extra_info(p_position_id,p_effective_date,'Y', null, p_mtfr_id);
1808 elsif p_sel_flg = 'Y' then
1809 if nvl(l_mtfr_id,0) <> nvl(p_mtfr_id,0) then
1810 p_sel_flg := 'N';
1811 end if;
1812 elsif p_sel_flg = 'N' then
1813 if nvl(l_mtfr_id,0) <> nvl(p_mtfr_id,0) then
1814 p_sel_flg := 'Y';
1815 --Bug#4126137 Commented ins_upd_pos_extra_info as this is invalidating all the positions.
1816 -- ins_upd_pos_extra_info(p_position_id,p_effective_date,'Y', null, p_mtfr_id);
1817 end if;
1818 end if;
1819
1820 pr('Sel flg is '||p_sel_flg||'position id is '|| to_char(p_position_id));
1821
1822 if p_action IN ('SHOW','REPORT') THEN
1823 return TRUE;
1824 elsif p_action in ('CREATE','DUMP OUT') THEN
1825 if p_sel_flg = 'Y' THEN
1826 return TRUE;
1827 else
1828 return FALSE;
1829 end if;
1830 end if;
1831 exception
1832 when mass_error then raise;
1833 when others then
1834 p_sel_flg := l_sel_flg;
1835 pr('Error in '||l_proc);
1836 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
1837 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
1838 raise mass_error;
1839 end;
1840
1841 --
1842 --
1843 --
1844
1845 procedure purge_old_data (p_mass_transfer_id in number) is
1846 l_proc varchar2(72)
1847 := g_package || '.purge_old_data';
1848 BEGIN
1849 hr_utility.set_location('Entering ' || l_proc,5);
1850 g_proc := 'purge_old_data';
1851 pr('Mass Transfer id is '||to_char(p_mass_transfer_id));
1852 delete from ghr_mass_actions_preview
1853 where mass_action_type = 'TRANSFER'
1854 and session_id = p_mass_transfer_id;
1855 commit;
1856 hr_utility.set_location('Exiting ' || l_proc,10);
1857 exception
1858 when others then
1859 pr('Error in '||l_proc);
1860 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
1861 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
1862 raise mass_error;
1863 END;
1864
1865 procedure ins_upd_pos_extra_info
1866 (p_position_id in number,p_effective_date in date,
1867 p_sel_flag in varchar2, p_comment in varchar2,
1868 p_mtfr_id in number) is
1869
1870 l_position_extra_info_id number;
1871 l_object_version_number number;
1872 l_pos_ei_data per_position_extra_info%rowtype;
1873
1874 CURSOR position_ext_cur (position number) is
1875 SELECT position_extra_info_id, object_version_number
1876 FROM PER_POSITION_EXTRA_INFO
1877 WHERE POSITION_ID = position
1878 and information_type = 'GHR_US_POS_MASS_ACTIONS';
1879
1880 l_eff_date date;
1881
1882 l_proc varchar2(72)
1883 := g_package || '.ins_upd_pos_extra_info';
1884 begin
1885 hr_utility.set_location('Entering ' || l_proc,5);
1886 g_proc := 'ins_upd_pos_extra_info';
1887 if p_effective_date > sysdate then
1888 l_eff_date := sysdate;
1889 else
1890 l_eff_date := p_effective_date;
1891 end if;
1892
1893 ghr_history_fetch.fetch_positionei
1894 (p_position_id => p_position_id
1895 ,p_information_type => 'GHR_US_POS_MASS_ACTIONS'
1896 ,p_date_effective => l_eff_date
1897 ,p_pos_ei_data => l_pos_ei_data);
1898
1899 l_position_extra_info_id := l_pos_ei_data.position_extra_info_id;
1900 l_object_version_number := l_pos_ei_data.object_version_number;
1901
1902 if l_position_extra_info_id is null then
1903 for pos_ext_rec in position_ext_cur(p_position_id)
1904 loop
1905 l_position_extra_info_id := pos_ext_rec.position_extra_info_id;
1906 l_object_version_number := pos_ext_rec.object_version_number;
1907 end loop;
1908 end if;
1909
1910 if l_position_extra_info_id is not null then
1911 ----- Set the global variable not to fire the trigger
1912 ghr_api.g_api_dml := TRUE;
1913 ghr_position_extra_info_api.update_position_extra_info
1914 (P_POSITION_EXTRA_INFO_ID => l_position_extra_info_id
1915 ,P_EFFECTIVE_DATE => trunc(l_eff_date)
1916 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
1917 ,p_poei_INFORMATION15 => p_sel_flag
1918 ,p_poei_INFORMATION16 => p_comment
1919 ,p_poei_INFORMATION17 => to_char(p_mtfr_id)
1920 ,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS');
1921 ----- Reset the global variable
1922 ghr_api.g_api_dml := FALSE;
1923 else
1924 -- Bug#4125231 Set the global variable not to fire the trigger
1925 ghr_api.g_api_dml := TRUE;
1926 ghr_position_extra_info_api.create_position_extra_info
1927 (P_POSITION_ID => p_position_id
1928 ,P_INFORMATION_TYPE => 'GHR_US_POS_MASS_ACTIONS'
1929 ,P_EFFECTIVE_DATE => trunc(l_eff_date)
1930 ,p_poei_INFORMATION15 => p_sel_flag
1931 ,p_poei_INFORMATION16 => p_comment
1932 ,p_poei_INFORMATION17 => to_char(p_mtfr_id)
1933 ,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS'
1934 ,P_POSITION_EXTRA_INFO_ID => l_position_extra_info_id
1935 ,P_OBJECT_VERSION_NUMBER => l_object_version_number);
1936 --Bug#4215231 Reset the global variable
1937 ghr_api.g_api_dml := FALSE;
1938 end if;
1939 hr_utility.set_location('Exiting ' || l_proc,10);
1940
1941 -- There is a trigger on Position extra Info. Whenever updated/created the
1942 -- main position associated with it becomes invalid.
1943 -- We shall call validate_perwsdpo procedure to set the status = VALID.
1944 -- Actually there should be a global flag called fire_trigger in session_var
1945 -- but it doesn't seem to be functional right now.
1946
1947 --- Commented the following two lines to remove Validation functionality on Position.
1948 -- ghr_validate_perwsdpo.validate_perwsdpo(p_position_id);
1949 -- ghr_validate_perwsdpo.update_posn_status(p_position_id);
1950
1951 exception
1952 when mass_error then raise;
1953 when others then
1954 pr('Error in '||l_proc);
1955 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
1956 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
1957 raise mass_error;
1958 end ins_upd_pos_extra_info;
1959
1960 --
1961 --
1962 --
1963
1964 procedure update_sel_flg (p_position_id in number,p_effective_date in date) is
1965
1966 l_position_extra_info_id number;
1967 l_object_version_number number;
1968 l_pos_ei_data per_position_extra_info%rowtype;
1969 l_eff_date date;
1970 l_proc varchar2(72)
1971 := g_package || '.update_sel_flg';
1972 begin
1973 hr_utility.set_location('Entering ' || l_proc,5);
1974 g_proc := 'update_sel_flg';
1975
1976 if p_effective_date > sysdate then
1977 l_eff_date := sysdate;
1978 else
1979 l_eff_date := p_effective_date;
1980 end if;
1981
1982 ghr_history_fetch.fetch_positionei
1983 (p_position_id => p_position_id
1984 ,p_information_type => 'GHR_US_POS_MASS_ACTIONS'
1985 ,p_date_effective => p_effective_date
1986 ,p_pos_ei_data => l_pos_ei_data);
1987
1988 l_position_extra_info_id := l_pos_ei_data.position_extra_info_id;
1989 l_object_version_number := l_pos_ei_data.object_version_number;
1990
1991 if l_position_extra_info_id is not null then
1992 ----- Set the global variable not to fire the trigger
1993 ghr_api.g_api_dml := TRUE;
1994 ghr_position_extra_info_api.update_position_extra_info
1995 (P_POSITION_EXTRA_INFO_ID => l_position_extra_info_id
1996 ,P_EFFECTIVE_DATE => trunc(l_eff_date)
1997 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
1998 ,p_poei_INFORMATION15 => NULL
1999 ,p_poei_INFORMATION16 => NULL
2000 ,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS');
2001 ----- Reset the global variable
2002 ghr_api.g_api_dml := FALSE;
2003
2004
2005 --- Commented the following two lines to remove Validation functionality on Position.
2006 -- ghr_validate_perwsdpo.validate_perwsdpo(p_position_id);
2007 -- ghr_validate_perwsdpo.update_posn_status(p_position_id);
2008
2009 end if;
2010 hr_utility.set_location('Exiting ' || l_proc,10);
2011 exception
2012 when mass_error then raise;
2013 when others then
2014 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2015 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2016 raise mass_error;
2017 end update_sel_flg;
2018
2019 --
2020 --
2021 --
2022
2023 FUNCTION check_eligibility(p_org_structure_id in varchar2,
2024 p_office_symbol in varchar2,
2025 p_personnel_office_id in varchar2,
2026 p_agency_sub_element_code in varchar2,
2027 p_duty_station_id in number,
2028 p_l_org_structure_id in varchar2,
2029 p_l_office_symbol in varchar2,
2030 p_l_personnel_office_id in varchar2,
2031 p_l_agency_sub_element_code in varchar2,
2032 p_l_duty_station_id in number,
2033 p_occ_series_code in varchar2,
2034 p_mass_transfer_id in number,
2035 p_action in varchar2,
2036 p_effective_date in date,
2037 p_person_id in number,
2038 p_assign_type in varchar2 default 'ASSIGNED')
2039 return boolean is
2040
2041 CURSOR occ_cur (tfr_id number,p_occ_series varchar2) IS
2042 select occ_code
2043 from ghr_mass_transfer_criteria
2044 where MASS_TRANSFER_ID = tfr_id
2045 and occ_code = p_occ_series;
2046
2047 CURSOR occ_cur_cnt (tfr_id number) is
2048 select count(*) COUNT
2049 from ghr_mass_transfer_criteria
2050 where MASS_TRANSFER_ID = tfr_id;
2051
2052 l_cnt number := 0;
2053 l_occ_code varchar2(30) := null;
2054 l_proc varchar2(72)
2055 := g_package || '.check_eligibility';
2056 BEGIN
2057 hr_utility.set_location('Entering ' || l_proc,5);
2058 g_proc := 'check_eligibility';
2059
2060 if p_org_structure_id is not null then
2061 if p_org_structure_id <> nvl(p_l_org_structure_id,'NULL!~') then
2062 return false;
2063 end if;
2064 end if;
2065
2066 if p_office_symbol is not null then
2067 if p_office_symbol <> nvl(p_l_office_symbol,'NULL!~') then
2068 return false;
2069 end if;
2070 end if;
2071
2072 if p_personnel_office_id is not null then
2073 if p_personnel_office_id <> nvl(p_l_personnel_office_id,'NULL!~') then
2074 return false;
2075 end if;
2076 end if;
2077
2078 -- VSM - p_agency_sub_element_code can have 2 or 4 chars.
2079 -- 2 char - Check for agency code only
2080 -- 4 char - Check for agency code and subelement
2081 if p_agency_sub_element_code is not null then
2082 if substr(p_agency_sub_element_code, 1, 2) <> nvl(substr(p_l_agency_sub_element_code, 1, 2), 'NULL!~') then
2083 return false;
2084 end if;
2085 end if;
2086
2087 if substr(p_agency_sub_element_code, 3, 2) is not null then
2088 if substr(p_agency_sub_element_code, 3, 2) <> nvl(substr(p_l_agency_sub_element_code, 3, 2), 'NULL!~') then
2089 return false;
2090 end if;
2091 end if;
2092
2093 if p_duty_station_id is not null then
2094 if p_duty_station_id <> nvl(p_l_duty_station_id,0) then
2095 return false;
2096 end if;
2097 end if;
2098
2099 --if p_assign_type = 'ASSIGNED' then
2100 for tfr_dtl_cnt in occ_cur_cnt (p_mass_transfer_id)
2101 loop
2102 l_cnt := tfr_dtl_cnt.count;
2103 exit;
2104 end loop;
2105
2106 for tfr_dtl in occ_cur (p_mass_transfer_id, p_occ_series_code)
2107 loop
2108 l_occ_code := tfr_dtl.occ_code;
2109 exit;
2110 end loop;
2111
2112 if l_cnt <> 0 then
2113 if l_occ_code is null then
2114 return false;
2115 end if;
2116 end if;
2117 --end if;
2118
2119 if p_action = 'CREATE' THEN
2120 if GHR_MRE_PKG.person_in_pa_req_1noa
2121 (p_person_id => p_person_id,
2122 p_effective_date => p_effective_date,
2123 p_first_noa_code => '352'
2124 ) then
2125 return false;
2126 end if;
2127 /*************
2128 if GHR_MRE_PKG.person_in_pa_req_2noa
2129 (p_person_id => p_person_id,
2130 p_effective_date => p_effective_date,
2131 p_second_noa_code => '352'
2132 ) then
2133 return false;
2134 end if;
2135 **************/
2136 end if;
2137
2138 pr('Eligible');
2139 return true;
2140
2141 exception
2142 when mass_error then raise;
2143 when others then
2144 pr('Error in '||l_proc);
2145 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2146 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2147 raise mass_error;
2148 END check_eligibility;
2149
2150 --
2151 --
2152 --
2153
2154 procedure get_pos_grp1_ddf (p_position_id in per_assignments_f.position_id%type,
2155 p_effective_date in date,
2156 p_personnel_office_id out NOCOPY varchar2,
2157 p_org_structure_id out NOCOPY varchar2,
2158 p_office_symbol out NOCOPY varchar2,
2159 p_position_organization out NOCOPY varchar2,
2160 p_pos_ei_data OUT NOCOPY per_position_extra_info%rowtype)
2161 IS
2162
2163 l_personnel_office_id per_position_Extra_info.poei_information3%type;
2164 l_org_structure_id per_position_Extra_info.poei_information5%type;
2165 l_office_symbol per_position_Extra_info.poei_information4%type;
2166 l_position_organization per_position_Extra_info.POEI_INFORMATION21%type;
2167
2168 l_pos_ei_data per_position_extra_info%rowtype;
2169
2170 l_proc varchar2(72)
2171 := g_package || '.get_pos_grp1_ddf';
2172 --l_pos_ei_data per_position_extra_info%type;
2173
2174 begin
2175 l_personnel_office_id := p_personnel_office_id;
2176 l_org_structure_id := p_org_structure_id;
2177 l_office_symbol := p_office_symbol;
2178 l_position_organization := p_position_organization;
2179 l_pos_ei_data := p_pos_ei_data;
2180
2181 hr_utility.set_location('Entering ' || l_proc,5);
2182 g_proc := 'get_pos_grp1_ddf';
2183 ghr_history_fetch.fetch_positionei
2184 (p_position_id => p_position_id
2185 ,p_information_type => 'GHR_US_POS_GRP1'
2186 ,p_date_effective => p_effective_date
2187 ,p_pos_ei_data => l_pos_ei_data
2188 );
2189 l_personnel_office_id := l_pos_ei_data.poei_information3;
2190 l_office_symbol := l_pos_ei_data.poei_information4;
2191 l_org_structure_id := l_pos_ei_data.poei_information5;
2192 l_position_organization := l_pos_ei_data.poei_information21;
2193
2194 --- NOCOPY Changes
2195 p_pos_ei_data := l_pos_ei_data;
2196 p_personnel_office_id := l_personnel_office_id;
2197 p_office_symbol := l_office_symbol;
2198 p_org_structure_id := l_org_structure_id;
2199 p_position_organization := l_position_organization;
2200
2201 hr_utility.set_location('Exiting ' || l_proc,10);
2202 exception
2203 when mass_error then raise;
2204 when others then
2205 -- NOCOPY Changes
2206 p_pos_ei_data := NULL;
2207 p_personnel_office_id := NULL;
2208 p_office_symbol := NULL;
2209 p_org_structure_id := NULL;
2210 p_position_organization := NULL;
2211 -- NOCOPY changes end
2212 pr('Error in '||l_proc);
2213 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2214 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2215 raise mass_error;
2216 END get_pos_grp1_ddf;
2217
2218 --
2219 --
2220 --
2221
2222 procedure get_pos_grp2_ddf (p_position_id in per_assignments_f.position_id%type,
2223 p_effective_date in date,
2224 p_org_func_code out NOCOPY varchar2,
2225 p_appropriation_code1 out NOCOPY varchar2,
2226 p_appropriation_code2 out NOCOPY varchar2,
2227 p_pos_ei_data OUT NOCOPY per_position_extra_info%rowtype)
2228 IS
2229
2230 l_proc varchar2(72)
2231 := g_package || '.get_pos_grp2_ddf';
2232
2233 l_org_func_code per_position_extra_info.POEI_INFORMATION4%type;
2234 l_appropriation_code1 per_position_extra_info.POEI_INFORMATION13%type;
2235 l_appropriation_code2 per_position_extra_info.POEI_INFORMATION13%type;
2236 l_pos_ei_data per_position_extra_info%rowtype;
2237
2238 begin
2239
2240 l_org_func_code := p_org_func_code;
2241 l_appropriation_code1 := p_appropriation_code1;
2242 l_appropriation_code2 := p_appropriation_code2;
2243 l_pos_ei_data := p_pos_ei_data;
2244
2245 hr_utility.set_location('Entering ' || l_proc,5);
2246 g_proc := 'get_pos_grp2_ddf';
2247 ghr_history_fetch.fetch_positionei
2248 (p_position_id => p_position_id
2249 ,p_information_type => 'GHR_US_POS_GRP2'
2250 ,p_date_effective => p_effective_date
2251 ,p_pos_ei_data => l_pos_ei_data
2252 );
2253 l_org_func_code := l_pos_ei_data.poei_information4;
2254 l_appropriation_code1 := l_pos_ei_data.poei_information13;
2255 l_appropriation_code2 := l_pos_ei_data.poei_information14;
2256
2257 ---NOCOPY Changes
2258 p_pos_ei_data := l_pos_ei_data;
2259 p_org_func_code := l_org_func_code;
2260 p_appropriation_code1 := l_appropriation_code1;
2261 p_appropriation_code2 := l_appropriation_code2;
2262 --- NOCOPY changes
2263
2264 hr_utility.set_location('Exiting ' || l_proc,10);
2265 exception
2266 when mass_error then raise;
2267 when others then
2268 ---NOCOPY Changes
2269 p_pos_ei_data := NULL;
2270 p_org_func_code := NULL;
2271 p_appropriation_code1 := NULL;
2272 p_appropriation_code2 := NULL;
2273 ---NOCOPY Changes END
2274
2275 pr('Error in '||l_proc);
2276 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2277 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2278 raise mass_error;
2279 END get_pos_grp2_ddf;
2280
2281 --
2282 --
2283 --
2284
2285 PROCEDURE get_extra_info_comments
2286 (p_position_id in number,
2287 p_effective_date in date,
2288 p_sel_flag in out NOCOPY varchar2,
2289 p_comments in out NOCOPY varchar2,
2290 p_mtfr_id in out NOCOPY number) IS
2291
2292 l_pos_ei_data per_position_extra_info%rowtype;
2293 l_proc varchar2(72) := g_package || '.get_extra_info_comments';
2294 l_eff_date date;
2295
2296 l_sel_flag varchar2(5);
2297 l_comments varchar2(4000);
2298 l_mtfr_id GHR_MASS_TRANSFERS.mass_transfer_id%type;
2299 begin
2300 g_proc := 'get_extra_info_comments';
2301
2302 -- NOCOPY Changes
2303 l_sel_flag := p_sel_flag;
2304 l_comments := p_comments;
2305 l_mtfr_id := p_mtfr_id;
2306 -- NOCOPY Changes
2307 hr_utility.set_location('Entering ' || l_proc,5);
2308
2309 l_eff_date := p_effective_date;
2310 ghr_history_fetch.fetch_positionei
2311 (p_position_id => p_position_id
2312 ,p_information_type => 'GHR_US_POS_MASS_ACTIONS'
2313 ,p_date_effective => l_eff_date
2314 ,p_pos_ei_data => l_pos_ei_data);
2315
2316 l_sel_flag := l_pos_ei_data.poei_information15;
2317 l_comments := l_pos_ei_data.poei_information16;
2318 l_mtfr_id := to_number(l_pos_ei_data.poei_information17);
2319
2320 -- NOCOPY Changes
2321 p_sel_flag := l_sel_flag;
2322 p_comments := l_comments;
2323 p_mtfr_id := l_mtfr_id;
2324 -- NOCOPY Changes
2325 pr('position ext id',to_char(l_pos_ei_data.position_extra_info_id),
2326 to_char(l_pos_ei_data.object_version_number));
2327 exception
2328 when mass_error then raise;
2329 when others then
2330 p_sel_flag := l_sel_flag;
2331 p_comments := l_comments;
2332 p_mtfr_id := l_mtfr_id;
2333 pr('Error in '||l_proc);
2334 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2335 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2336 raise mass_error;
2337 end;
2338
2339 --
2340 --
2341 --
2342
2343 procedure create_mass_act_prev (
2344 p_effective_date in date,
2345 p_date_of_birth in date,
2346 p_full_name in varchar2,
2347 p_national_identifier in varchar2,
2348 --Bug # 9329643
2349 p_employee_number in varchar2,
2350 p_duty_station_code in varchar2,
2351 p_duty_station_desc in varchar2,
2352 p_personnel_office_id in varchar2,
2353 p_position_id in per_assignments_f.position_id%type,
2354 p_position_title in varchar2,
2355 p_position_number in varchar2,
2356 p_position_seq_no in varchar2,
2357 p_org_structure_id in varchar2,
2358 p_agency_sub_element_code in varchar2,
2359 p_person_id in number,
2360 p_mass_transfer_id in number,
2361 p_sel_flg in varchar2,
2362 p_grade_or_level in varchar2,
2363 p_step_or_rate in varchar2,
2364 p_pay_plan in varchar2,
2365 p_occ_series in varchar2,
2366 p_office_symbol in varchar2,
2367 p_organization_id in number,
2368 p_organization_name in varchar2,
2369 p_positions_organization in varchar2 default null,
2370 t_personnel_office_id in varchar2 default null,
2371 t_sub_element_code in varchar2 default null,
2372 t_duty_station_id in number default null,
2373 t_duty_station_code in varchar2 default null,
2374 t_duty_station_desc in varchar2 default null,
2375 t_office_symbol in varchar2 default null,
2376 t_payroll_office_id in varchar2 default null,
2377 t_org_func_code in varchar2 default null,
2378 t_appropriation_code1 in varchar2 default null,
2379 t_appropriation_code2 in varchar2 default null,
2380 t_position_organization in varchar2 default null,
2381 p_to_agency_code in varchar2,
2382 p_tenure in varchar2,
2383 p_pay_rate_determinant in varchar2,
2384 p_action in varchar2,
2385 p_assignment_id in number)
2386 is
2387
2388 l_comb_rem varchar2(30);
2389 l_proc varchar2(72)
2390 := g_package || '.create_mass_act_prev';
2391
2392 l_agency_sub_elem_desc varchar2(80);
2393 t_sub_element_desc varchar2(80);
2394 t_appropriation_code1_desc varchar2(80);
2395 t_appropriation_code2_desc varchar2(80);
2396 l_pay_plan_desc varchar2(80);
2397 l_position_organization_name varchar2(240);
2398 l_poi_desc varchar2(80);
2399 t_poi_desc varchar2(80);
2400 t_position_organization_name varchar2(240);
2401 l_to_agency_code varchar2(10);
2402 l_agency_code varchar2(10);
2403
2404 l_cust_rec ghr_mass_act_custom.ghr_mass_custom_out_rec_type;
2405 l_cust_in_rec ghr_mass_act_custom.ghr_mass_custom_in_rec_type;
2406
2407 ----Temp Promo Changes.
2408 l_step_or_rate varchar2(30);
2409 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2410
2411 begin
2412 hr_utility.set_location('Entering ' || l_proc,5);
2413 g_proc := 'create_mass_act_prev';
2414
2415 pr('Inside ghr_cpdf_temp insert Transfer id ',to_char(p_mass_transfer_id),null);
2416 pr('t_pos_org is',t_position_organization);
2417
2418
2419 get_to_agency (p_person_id,
2420 p_effective_date,
2421 l_agency_code);
2422
2423 /*
2424 if l_agency_code is not null then
2425 l_to_agency_code := l_agency_code;
2426 else
2427 l_to_agency_code := p_to_agency_code;
2428 end if;
2429 */
2430 if (p_person_id is not null ) then
2431 if l_agency_code is not null then
2432 l_to_agency_code := l_agency_code;
2433 elsif p_to_agency_code is not null then
2434 l_to_agency_code := p_to_agency_code;
2435 else
2436 l_to_agency_code := p_agency_sub_element_code;
2437 end if;
2438 else
2439 l_to_agency_code := null;
2440 end if;
2441
2442
2443 ghr_mre_pkg.GET_FIELD_DESC (p_agency_sub_element_code,
2444 l_to_agency_code, ---t_sub_element_code,
2445 t_appropriation_code1,
2446 t_appropriation_code2,
2447 p_pay_plan,
2448 p_personnel_office_id,
2449 t_personnel_office_id,
2450 p_positions_organization,
2451 t_position_organization,
2452
2453 l_agency_sub_elem_desc,
2454 t_sub_element_desc,
2455 t_appropriation_code1_desc,
2456 t_appropriation_code2_desc,
2457 l_pay_plan_desc,
2458 l_poi_desc,
2459 t_poi_desc,
2460 l_position_organization_name,
2461 t_position_organization_name);
2462
2463 BEGIN
2464 l_cust_in_rec.person_id := p_person_id;
2465 l_cust_in_rec.position_id := p_position_id;
2466 l_cust_in_rec.assignment_id := p_assignment_id;
2467 l_cust_in_rec.national_identifier := p_national_identifier;
2468 l_cust_in_rec.mass_action_type := 'TRANSFER OUT';
2469 l_cust_in_rec.mass_action_id := p_mass_transfer_id;
2470 l_cust_in_rec.effective_date := p_effective_date;
2471
2472 GHR_MASS_ACT_CUSTOM.pre_insert (
2473 p_cust_in_rec => l_cust_in_rec,
2474 p_cust_rec => l_cust_rec);
2475
2476 exception
2477 when others then
2478 hr_utility.set_location('Error in Mass Act Custom '||
2479 'Err is '||sqlerrm(sqlcode),20);
2480 l_mass_errbuf := 'Error in Mass Act Custom '||
2481 'Err is '|| sqlerrm(sqlcode);
2482 raise mass_error;
2483 END;
2484
2485 l_step_or_rate := p_step_or_rate;
2486
2487 IF p_pay_rate_determinant in ('A','B','E','F') AND
2488 ghr_msl_pkg.check_grade_retention(p_pay_rate_determinant,p_person_id,p_effective_date) = 'REGULAR' THEN
2489 begin
2490 l_retained_grade_rec :=
2491 ghr_pc_basic_pay.get_retained_grade_details
2492 ( p_person_id,
2493 p_effective_date);
2494 if l_retained_grade_rec.temp_step is not null then
2495 l_step_or_rate := l_retained_grade_rec.temp_step;
2496 end if;
2497 exception
2498 when others then
2499 l_mass_errbuf := 'Preview - Others error in Get retained grade '||
2500 'Error is '||' Sql Err is '|| sqlerrm(sqlcode);
2501 ghr_mre_pkg.pr('Person ID '||to_char(p_person_id),'ERROR 2',l_mass_errbuf);
2502 raise mass_error;
2503 end;
2504 END IF;
2505
2506 insert into GHR_MASS_ACTIONS_PREVIEW
2507 (
2508 mass_action_type,
2509 --report_type,
2510 ui_type,
2511 session_id,
2512 effective_date,
2513 employee_date_of_birth,
2514 full_name,
2515 national_identifier,
2516 --Bug #9329643
2517 employee_number,
2518 duty_station_code,
2519 duty_station_desc,
2520 personnel_office_id,
2521 position_id,
2522 position_title,
2523 position_number,
2524 position_seq_no,
2525 org_structure_id,
2526 agency_code,
2527 person_id,
2528 select_flag,
2529 first_noa_code,
2530 grade_or_level,
2531 step_or_rate,
2532 pay_plan,
2533 office_symbol,
2534 organization_id,
2535 organization_name,
2536 occ_code,
2537 positions_organization,
2538 to_personnel_office_id,
2539 to_agency_code,
2540 to_duty_station_id,
2541 to_duty_station_code,
2542 to_duty_station_desc,
2543 to_office_symbol,
2544 to_payroll_office_id,
2545 to_org_func_code,
2546 to_appropriation_code1,
2547 to_appropriation_code2,
2548 to_positions_organization,
2549
2550 AGENCY_DESC,
2551 TO_AGENCY_DESC,
2552 TO_APPROPRIATION_CODE1_DESC,
2553 TO_APPROPRIATION_CODE2_DESC,
2554 PAY_PLAN_DESC,
2555 POI_DESC,
2556 TO_POI_DESC,
2557 POSITIONS_ORGANIZATION_NAME,
2558 TO_POSITIONS_ORG_NAME,
2559
2560 TENURE,
2561 PAY_RATE_DETERMINANT,
2562 USER_ATTRIBUTE1,
2563 USER_ATTRIBUTE2,
2564 USER_ATTRIBUTE3,
2565 USER_ATTRIBUTE4,
2566 USER_ATTRIBUTE5,
2567 USER_ATTRIBUTE6,
2568 USER_ATTRIBUTE7,
2569 USER_ATTRIBUTE8,
2570 USER_ATTRIBUTE9,
2571 USER_ATTRIBUTE10,
2572 USER_ATTRIBUTE11,
2573 USER_ATTRIBUTE12,
2574 USER_ATTRIBUTE13,
2575 USER_ATTRIBUTE14,
2576 USER_ATTRIBUTE15,
2577 USER_ATTRIBUTE16,
2578 USER_ATTRIBUTE17,
2579 USER_ATTRIBUTE18,
2580 USER_ATTRIBUTE19,
2581 USER_ATTRIBUTE20
2582 )
2583 values
2584 (
2585 'TRANSFER',
2586 /*--decode(p_action,'REPORT',userenv('SESSIONID'),p_mass_realignment_id),*/
2587 decode(p_action,'SHOW','FORM','REPORT'),
2588 userenv('SESSIONID'),
2589 p_effective_date,
2590 p_date_of_birth,
2591 p_full_name,
2592 p_national_identifier,
2593 --Bug #9329643
2594 p_employee_number,
2595 p_duty_station_code,
2596 p_duty_station_desc,
2597 p_personnel_office_id,
2598 p_position_id,
2599 p_position_title,
2600 p_position_number,
2601 to_number(p_position_seq_no),
2602 p_org_structure_id,
2603 p_agency_sub_element_code,
2604 p_person_id,
2605 p_sel_flg,
2606 '352',
2607 p_grade_or_level,
2608 l_step_or_rate,
2609 p_pay_plan,
2610 p_office_symbol,
2611 p_organization_id,
2612 p_organization_name,
2613 p_occ_series,
2614 p_positions_organization,
2615 t_personnel_office_id,
2616 decode(p_sel_flg,'N',NULL,l_to_agency_code), --- t_sub_element_code,
2617 t_duty_station_id,
2618 t_duty_station_code,
2619 t_duty_station_desc,
2620 t_office_symbol,
2621 t_payroll_office_id,
2622 t_org_func_code,
2623 t_appropriation_code1,
2624 t_appropriation_code2,
2625
2626 t_position_organization,
2627 l_agency_sub_elem_desc,
2628 decode(p_sel_flg,'N',NULL,t_sub_element_desc),
2629 t_appropriation_code1_desc,
2630 t_appropriation_code2_desc,
2631 l_pay_plan_desc,
2632 l_poi_desc,
2633 t_poi_desc,
2634 l_position_organization_name,
2635 t_position_organization_name,
2636
2637 p_tenure,
2638 p_pay_rate_determinant,
2639
2640 l_cust_rec.user_attribute1,
2641 l_cust_rec.user_attribute2,
2642 l_cust_rec.user_attribute3,
2643 l_cust_rec.user_attribute4,
2644 l_cust_rec.user_attribute5,
2645 l_cust_rec.user_attribute6,
2646 l_cust_rec.user_attribute7,
2647 l_cust_rec.user_attribute8,
2648 l_cust_rec.user_attribute9,
2649 l_cust_rec.user_attribute10,
2650 l_cust_rec.user_attribute11,
2651 l_cust_rec.user_attribute12,
2652 l_cust_rec.user_attribute13,
2653 l_cust_rec.user_attribute14,
2654 l_cust_rec.user_attribute15,
2655 l_cust_rec.user_attribute16,
2656 l_cust_rec.user_attribute17,
2657 l_cust_rec.user_attribute18,
2658 l_cust_rec.user_attribute19,
2659 l_cust_rec.user_attribute20
2660 );
2661
2662 hr_utility.set_location('Exiting ' || l_proc,10);
2663 exception
2664 when mass_error then raise;
2665 when others then
2666 pr('Error in '||l_proc);
2667 pr('Position title is '||p_position_title||' Length is '||to_char(length(p_position_title)));
2668 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2669 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2670 raise mass_error;
2671 end create_mass_act_prev;
2672
2673 --
2674 --
2675 --
2676
2677 function get_mto_name(p_mto_id in number) return varchar2 is
2678
2679 CURSOR mto_cur is
2680 SELECT NAME
2681 FROM GHR_MASS_TRANSFERS
2682 WHERE MASS_TRANSFER_ID = p_mto_id;
2683
2684 l_mto_name varchar2(150);
2685 l_proc varchar2(72) := g_package || '.get_mre_name';
2686 begin
2687 hr_utility.set_location('Entering ' || l_proc,5);
2688 g_proc := 'get_mto_name';
2689 FOR mto_REC IN mto_cur
2690 LOOP
2691 l_mto_name := mto_rec.name;
2692 exit;
2693 END LOOP;
2694 return (l_mto_name);
2695 end;
2696
2697 --
2698 --
2699
2700 PROCEDURE assign_to_sf52_rec(
2701 p_person_id in number,
2702 p_first_name in varchar2,
2703 p_last_name in varchar2,
2704 p_middle_names in varchar2,
2705 p_national_identifier in varchar2,
2706 p_date_of_birth in date,
2707 p_effective_date in date,
2708 p_assignment_id in number,
2709 p_tenure in varchar2,
2710 p_step_or_rate in varchar2,
2711 p_annuitant_indicator in varchar2,
2712 p_pay_rate_determinant in varchar2,
2713 p_work_schedule in varchar2,
2714 p_part_time_hour in varchar2,
2715 p_flsa_category in varchar2,
2716 p_bargaining_unit_status in varchar2,
2717 p_functional_class in varchar2,
2718 p_supervisory_status in varchar2,
2719 p_personnel_office_id in varchar2,
2720 p_sub_element_code in varchar2,
2721 p_duty_station_id in number,
2722 p_duty_station_code in ghr_pa_requests.duty_station_code%type,
2723 p_duty_station_desc in ghr_pa_requests.duty_station_desc%type,
2724 p_office_symbol in varchar2,
2725 p_payroll_office_id in varchar2,
2726 p_org_func_code in varchar2,
2727 p_appropriation_code1 in varchar2,
2728 p_appropriation_code2 in varchar2,
2729 p_position_organization in varchar2,
2730 p_first_noa_information1 in varchar2,
2731 p_to_position_org_line1 in varchar2, -- AVR
2732 p_lac_sf52_rec in ghr_pa_requests%rowtype,
2733 p_sf52_rec out NOCOPY ghr_pa_requests%rowtype) IS
2734
2735 l_proc varchar2(72)
2736 := g_package || '.assign_to_sf52_rec';
2737 l_sf52_rec ghr_pa_requests%rowtype;
2738 begin
2739
2740 l_sf52_rec := p_sf52_rec;
2741
2742 hr_utility.set_location('Entering ' || l_proc,5);
2743 g_proc := 'assign_to_sf52_rec';
2744
2745 l_sf52_rec.person_id := p_person_id;
2746 l_sf52_rec.employee_first_name := p_first_name;
2747 l_sf52_rec.employee_last_name := p_last_name;
2748 l_sf52_rec.employee_middle_names := p_middle_names;
2749 l_sf52_rec.employee_national_identifier := p_national_identifier;
2750 l_sf52_rec.employee_date_of_birth := p_date_of_birth;
2751 l_sf52_rec.effective_date := p_effective_date;
2752 l_sf52_rec.employee_assignment_id := p_assignment_id;
2753 l_sf52_rec.tenure := p_tenure;
2754 l_sf52_rec.to_step_or_rate := p_step_or_rate;
2755 l_sf52_rec.annuitant_indicator := p_annuitant_indicator;
2756 l_sf52_rec.pay_rate_determinant := p_pay_rate_determinant;
2757 l_sf52_rec.work_schedule := p_work_schedule;
2758 l_sf52_rec.part_time_hours := p_part_time_hour;
2759 l_sf52_rec.flsa_category := p_flsa_category;
2760 l_sf52_rec.bargaining_unit_status := p_bargaining_unit_status;
2761 l_sf52_rec.functional_class := p_functional_class;
2762 l_sf52_rec.supervisory_status := p_supervisory_status;
2763 l_sf52_rec.personnel_office_id := p_personnel_office_id;
2764 l_sf52_rec.agency_code := p_sub_element_code;
2765 l_sf52_rec.duty_station_id := p_duty_station_id;
2766 l_sf52_rec.duty_station_code := p_duty_station_code;
2767 l_sf52_rec.duty_station_desc := p_duty_station_desc;
2768 l_sf52_rec.to_office_symbol := p_office_symbol;
2769 l_sf52_rec.appropriation_code1 := p_appropriation_code1;
2770 l_sf52_rec.appropriation_code2 := p_appropriation_code2;
2771 l_sf52_rec.first_noa_information1 := p_first_noa_information1;
2772 l_sf52_rec.to_position_org_line1 := p_to_position_org_line1; -- AVR
2773
2774 l_sf52_rec.FIRST_LAC1_INFORMATION1 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION1;
2775 l_sf52_rec.FIRST_LAC1_INFORMATION2 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION2;
2776 l_sf52_rec.FIRST_LAC1_INFORMATION3 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION3;
2777 l_sf52_rec.FIRST_LAC1_INFORMATION4 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION4;
2778 l_sf52_rec.FIRST_LAC1_INFORMATION5 := p_lac_sf52_rec.FIRST_LAC1_INFORMATION5;
2779 l_sf52_rec.SECOND_LAC1_INFORMATION1 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION1;
2780 l_sf52_rec.SECOND_LAC1_INFORMATION2 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION2;
2781 l_sf52_rec.SECOND_LAC1_INFORMATION3 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION3;
2782 l_sf52_rec.SECOND_LAC1_INFORMATION4 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION4;
2783 l_sf52_rec.SECOND_LAC1_INFORMATION5 := p_lac_sf52_rec.SECOND_LAC1_INFORMATION5;
2784 l_sf52_rec.FIRST_ACTION_LA_CODE1 := p_lac_sf52_rec.FIRST_ACTION_LA_CODE1;
2785 l_sf52_rec.FIRST_ACTION_LA_CODE2 := p_lac_sf52_rec.FIRST_ACTION_LA_CODE2;
2786 l_sf52_rec.FIRST_ACTION_LA_DESC1 := p_lac_sf52_rec.FIRST_ACTION_LA_DESC1;
2787 l_sf52_rec.FIRST_ACTION_LA_DESC2 := p_lac_sf52_rec.FIRST_ACTION_LA_DESC2;
2788
2789 hr_utility.set_location('Exiting ' || l_proc,10);
2790
2791 p_sf52_rec := l_sf52_rec;
2792
2793 exception
2794 when mass_error then raise;
2795 when others then
2796 --NOCOPY changes
2797 p_sf52_rec := l_sf52_rec;
2798 -- NOCOPY Changes
2799 pr('Error in '||l_proc);
2800 hr_utility.set_location('Error in '||l_proc||' Err is '||sqlerrm(sqlcode),20);
2801 l_mass_errbuf := 'Error in '||l_proc||' Sql Err is '|| sqlerrm(sqlcode);
2802 raise mass_error;
2803 end assign_to_sf52_rec;
2804
2805 --
2806 --
2807 --
2808
2809 procedure upd_ext_info_to_null(p_position_id in number) is
2810
2811 CURSOR POSITION_EXT_CUR (p_position number) IS
2812 select position_extra_info_id, object_version_number
2813 from per_position_extra_info
2814 where position_id = (p_position)
2815 and INFORMATION_TYPE = 'GHR_US_POS_MASS_ACTIONS';
2816
2817 l_Position_EXTRA_INFO_ID NUMBER;
2818 l_OBJECT_VERSION_NUMBER NUMBER;
2819
2820 l_pos_ei_data per_position_extra_info%rowtype;
2821 l_proc varchar2(72) := g_package || '.upd_ext_info_api';
2822 begin
2823
2824 g_proc := 'upd_ext_info_to_null';
2825 ghr_history_fetch.fetch_positionei
2826 (p_position_id => p_position_id
2827 ,p_information_type => 'GHR_US_POS_MASS_ACTIONS'
2828 ,p_date_effective => trunc(sysdate)
2829 ,p_pos_ei_data => l_pos_ei_data);
2830
2831 l_position_extra_info_id := l_pos_ei_data.position_extra_info_id;
2832 l_object_version_number := l_pos_ei_data.object_version_number;
2833
2834 if l_position_extra_info_id is not null then
2835 ----- Set the global variable not to fire the trigger
2836 ghr_api.g_api_dml := TRUE;
2837 ghr_position_extra_info_api.update_position_extra_info
2838 (P_POSITION_EXTRA_INFO_ID => l_position_extra_info_id
2839 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
2840 ,P_POEI_INFORMATION_CATEGORY => 'GHR_US_POS_MASS_ACTIONS'
2841 ,P_EFFECTIVE_DATE => trunc(sysdate)
2842 ,P_POEI_INFORMATION15 => null
2843 ,P_POEI_INFORMATION16 => null
2844 ,P_POEI_INFORMATION17 => null);
2845 ----- Reset the global variable
2846 ghr_api.g_api_dml := FALSE;
2847
2848
2849 --- Commented the following two lines to remove Validation functionality on Position.
2850 -- ghr_validate_perwsdpo.validate_perwsdpo(p_position_id);
2851 -- ghr_validate_perwsdpo.update_posn_status(p_position_id);
2852
2853 end if;
2854 end;
2855
2856 --
2857 --
2858 --
2859
2860 PROCEDURE get_to_agency (p_person_id in number,
2861 p_effective_date in date,
2862 p_agency_code out NOCOPY varchar2) is
2863
2864 l_per_ei_data per_people_extra_info%rowtype;
2865 l_proc varchar2(72) := g_package || '.upd_ext_info_api';
2866 l_eff_date date;
2867 l_agency_code varchar2(5);
2868 begin
2869 hr_utility.set_location('Entering ' || l_proc,5);
2870
2871 -- NOCOPY changes
2872 l_agency_code := p_agency_code;
2873 -- NOCOPY changes
2874
2875 g_proc := 'get_to_agency';
2876 if p_effective_date > sysdate then
2877 l_eff_date := sysdate;
2878 else
2879 l_eff_date := p_effective_date;
2880 end if;
2881 -- VSM - Changes Bug # 752015 changed p_information type from
2882 -- GHR_US_PER_SEPARATE_RETIRE to GHR_US_PER_MASS_ACTIONS
2883 ghr_history_fetch.fetch_peopleei
2884 (p_person_id => p_person_id
2885 ,p_information_type => 'GHR_US_PER_MASS_ACTIONS'
2886 ,p_date_effective => l_eff_date
2887 ,p_per_ei_data => l_per_ei_data);
2888
2889 l_agency_code := l_per_ei_data.PEI_INFORMATION9;
2890
2891 -- NOCOPY changes
2892 p_agency_code := l_agency_code;
2893 -- NOCOPY changes
2894 hr_utility.set_location('Agency Code ' || p_agency_code,10);
2895 hr_utility.set_location('Leaving ' || l_proc,15);
2896 end;
2897
2898 PROCEDURE upd_ext_info_api (p_person_id in number,
2899 p_agency_code in varchar2,
2900 p_effective_date in date) IS
2901
2902 -- VSM - Changes Bug # 752015 changed p_information type from
2903 -- GHR_US_PER_SEPARATE_RETIRE to GHR_US_PER_MASS_ACTIONS
2904 CURSOR PERSON_EXT_CUR (p_person number) IS
2905 select person_extra_info_id, object_version_number
2906 from per_people_extra_info
2907 where person_id = (p_person)
2908 and INFORMATION_TYPE = 'GHR_US_PER_MASS_ACTIONS';
2909
2910 l_cnt number;
2911 l_person_id number;
2912 l_person_EXTRA_INFO_ID NUMBER;
2913 l_OBJECT_VERSION_NUMBER NUMBER;
2914 l_object_version_no number;
2915
2916 l_per_ei_data per_people_extra_info%rowtype;
2917 l_proc varchar2(72) := g_package || '.upd_ext_info_api';
2918 l_eff_date date;
2919
2920 BEGIN
2921 hr_utility.set_location('Entering ' || l_proc,5);
2922 g_proc := 'upd_ext_info_api';
2923 if p_effective_date > sysdate then
2924 l_eff_date := sysdate;
2925 else
2926 l_eff_date := p_effective_date;
2927 end if;
2928
2929 -- VSM - Changes Bug # 752015 changed p_information type from
2930 -- GHR_US_PER_SEPARATE_RETIRE to GHR_US_PER_MASS_ACTIONS
2931 ghr_history_fetch.fetch_peopleei
2932 (p_person_id => p_person_id
2933 ,p_information_type => 'GHR_US_PER_MASS_ACTIONS'
2934 ,p_date_effective => trunc(l_eff_date)
2935 ,p_per_ei_data => l_per_ei_data);
2936
2937
2938 pr ('Person id ',to_char(p_person_id));
2939 l_person_extra_info_id := l_per_ei_data.person_extra_info_id;
2940 l_object_version_number := l_per_ei_data.object_version_number;
2941
2942 pr ('Person ext info id ',to_char(l_person_extra_info_id));
2943
2944 if l_person_extra_info_id is null then
2945 for per_ext_rec in person_ext_cur(p_person_id)
2946 loop
2947 l_person_extra_info_id := per_ext_rec.person_extra_info_id;
2948 l_object_version_number := per_ext_rec.object_version_number;
2949 end loop;
2950 end if;
2951
2952 pr ('Person ext info id ',to_char(l_person_extra_info_id),to_char(l_object_version_number));
2953
2954 if l_person_extra_info_id is null then
2955 pr('Bef create pers ext info');
2956 -- VSM - Changes Bug # 752015 changed p_information type from
2957 -- GHR_US_PER_SEPARATE_RETIRE to GHR_US_PER_MASS_ACTIONS
2958 ghr_person_extra_info_api.create_person_extra_info
2959 (p_person_id => p_person_id
2960 ,p_information_type => 'GHR_US_PER_MASS_ACTIONS'
2961 ,P_EFFECTIVE_DATE => trunc(l_eff_date)
2962 ,P_PEI_information_category => 'GHR_US_PER_MASS_ACTIONS'
2963 ,P_PEI_INFORMATION9 => p_agency_code
2964 ,p_PERSON_EXTRA_INFO_ID => l_PERSON_EXTRA_INFO_ID
2965 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
2966
2967 else
2968 pr('Bef update pers ext info');
2969 -- VSM - Changes Bug # 752015 changed p_information type from
2970 -- GHR_US_PER_SEPARATE_RETIRE to GHR_US_PER_MASS_ACTIONS
2971 ghr_person_extra_info_api.update_person_extra_info
2972 (P_PERSON_EXTRA_INFO_ID => l_PERSON_extra_info_id
2973 ,P_EFFECTIVE_DATE => trunc(l_eff_date)
2974 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
2975 ,P_PEI_information_category => 'GHR_US_PER_MASS_ACTIONS'
2976 ,P_PEI_INFORMATION9 => p_agency_code);
2977
2978 end if;
2979 ---Commented the following two lines to remove Validation functionality on Person.
2980 -- ghr_validate_perwsepi.validate_perwsepi(p_person_id);
2981 -- ghr_validate_perwsepi.update_person_user_type(p_person_id);
2982 END;
2983
2984 --
2985 --
2986 --
2987
2988 procedure pr (msg varchar2,par1 in varchar2 default null,
2989 par2 in varchar2 default null) is
2990 begin
2991 g_no := g_no +1;
2992 -- insert into l_tmp values (g_no,substr(msg||'-'||par1||' -'||par2||'-',1,199));
2993 -- DBMS_OUTPUT.PUT_LINE(msg||'-'||par1||' -'||par2||'-');
2994 exception
2995 when others then
2996 pr('Error in '||'pr');
2997 hr_utility.set_location('Error in pr '||' Err is '||sqlerrm(sqlcode),20);
2998 l_mass_errbuf := 'Error in pr Sql Err is '|| sqlerrm(sqlcode);
2999 raise mass_error;
3000 end;
3001
3002 Procedure update_position_info
3003 (p_position_data_rec in ghr_sf52_pos_update.position_data_rec_type) is
3004 l_proc varchar2(30):='update_position_info';
3005 Begin
3006 hr_utility.set_location('Entering ' || l_proc, 10);
3007 g_proc := 'update_position_info';
3008 ghr_session.set_session_var_for_core( p_position_data_rec.effective_end_date );
3009 ghr_sf52_pos_update.update_position_info
3010 ( p_pos_data_rec => p_position_data_rec);
3011 hr_utility.set_location('Calling Pust_update_process ' || l_proc, 50);
3012 g_proc := 'post_update_process';
3013 ghr_history_api.post_update_process;
3014 hr_utility.set_location('Leaving ' || l_proc, 100);
3015
3016 end;
3017
3018 Procedure upd_per_extra_info_to_null(p_person_id in number) is
3019
3020 CURSOR PER_EXT_CUR (p_person number) IS
3021 select person_extra_info_id, object_version_number
3022 from per_people_extra_info
3023 where person_id = (p_person)
3024 and INFORMATION_TYPE = 'GHR_US_PER_MASS_ACTIONS';
3025
3026 l_Person_EXTRA_INFO_ID NUMBER;
3027 l_OBJECT_VERSION_NUMBER NUMBER;
3028 l_per_ei_data per_people_extra_info%rowtype;
3029 l_proc varchar2(72) := g_package || '.upd_per_extra_info_to_null';
3030 begin
3031
3032 g_proc := 'upd_per_extra_info_to_null';
3033 ghr_history_fetch.fetch_peopleei
3034 (p_person_id => p_person_id
3035 ,p_information_type => 'GHR_US_PER_MASS_ACTIONS'
3036 ,p_date_effective => trunc(sysdate)
3037 ,p_per_ei_data => l_per_ei_data);
3038
3039 l_person_extra_info_id := l_per_ei_data.person_extra_info_id;
3040
3041 l_object_version_number := l_per_ei_data.object_version_number;
3042
3043 if l_person_extra_info_id is not null then
3044 ----- Set the global variable not to fire the trigger
3045 ghr_api.g_api_dml := TRUE;
3046 ghr_person_extra_info_api.update_person_extra_info
3047 (P_PERSON_EXTRA_INFO_ID => l_PERSON_extra_info_id
3048 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
3049 ,P_PEI_INFORMATION_CATEGORY => 'GHR_US_PER_MASS_ACTIONS'
3050 ,P_EFFECTIVE_DATE => trunc(sysdate)
3051 ,P_PEI_INFORMATION9 => null
3052 );
3053 -- ,P_PEI_INFORMATION16 => null
3054 -- ,P_PEI_INFORMATION17 => null);
3055 ----- Reset the global variable
3056 ghr_api.g_api_dml := FALSE;
3057 end if;
3058
3059 End;
3060 END GHR_MTO_PKG;