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