1 PACKAGE BODY ghr_pa_requests_pkg2 AS
2 /* $Header: ghparqs2.pkb 120.7 2011/12/30 10:50:00 vmididho ship $ */
3
4 -- This function checks if there are any pending PA Request actions for a given person (not including the
5 -- given PA Request)
6 -- And returns a list of thoses that are pending.
7 -- The definition of pending is its current routing status is not 'CANCELED' or 'UPDATE_HR_COMPLETE'
8 -- To prevent listing those that got put in the 'black hole' (i.e. were saved but not routed) make sure
9 -- the routing history has a date notification sent (except for 'FUTURE_ACTIONS' as they may not have
10 -- been routed but are still pending)
11
12 FUNCTION check_pending_pars (p_person_id IN NUMBER
13 ,p_pa_request_id IN NUMBER)
14 RETURN VARCHAR2 IS
15
16 l_pending_list VARCHAR2(2000) := NULL;
17 l_new_line VARCHAR2(1) := substr('
18 ',1,1);
19 --
20 CURSOR c_par IS
21 SELECT 'Request Number:'||par.request_number||
22 ', 1st NOA Code:'||par.first_noa_code||
23 DECODE(par.second_noa_code,NULL,NULL, ', 2nd NOA Code:'||par.second_noa_code)||
24 ------ ', Effective Date:'||TO_CHAR(par.effective_date,'DD-MON-YYYY')||
25 ', Effective Date:'||fnd_date.date_to_chardate(par.effective_date)||
26 DECODE(prh.action_taken,'FUTURE_ACTION', ', APPROVED',
27 ', Routed To '||DECODE(gbx.name,null, 'User:'||prh.user_name, 'Groupbox:'||gbx.name)) list_info
28 FROM ghr_groupboxes gbx
29 ,ghr_pa_routing_history prh
30 ,ghr_pa_requests par
31 WHERE gbx.groupbox_id(+) = prh.groupbox_id
32 AND par.person_id = p_person_id
33 AND par.pa_request_id <> NVL(p_pa_request_id,-999)
34 AND prh.pa_request_id = par.pa_request_id
35 AND prh.pa_routing_history_id = (SELECT MAX(prh2.pa_routing_history_id)
36 FROM ghr_pa_routing_history prh2
37 WHERE prh2.pa_request_id = par.pa_request_id)
38 AND NVL(prh.action_taken,'==@@==') NOT IN ('CANCELED','UPDATE_HR_COMPLETE')
39 AND (prh.date_notification_sent is not null
40 OR prh.action_taken = 'FUTURE_ACTION')
41 ORDER BY par.pa_request_id;
42
43 BEGIN
44 -- loop arounfd them all to build up a list
45 FOR c_par_rec IN c_par LOOP
46
47 l_pending_list := SUBSTR(l_pending_list||l_new_line || l_new_line ||c_par%ROWCOUNT||'.'||c_par_rec.list_info,1,2000);
48
49 END LOOP;
50
51 RETURN(l_pending_list);
52
53 END check_pending_pars;
54
55 -- This function checks if there are any processed or approved PA Requests for the given person
56 -- at the given date. The definition of 'Processed' is the lasting Routing history record is 'UPDATE_HR_COMPLETE'
57 -- and the definition of 'Approved' is the lasting Routing history record is 'FUTURE_ACTION'
58 FUNCTION check_proc_future_pars (p_person_id IN NUMBER
59 ,p_effective_date IN DATE)
60 RETURN VARCHAR2 IS
61
62 l_proc_future_list VARCHAR2(2000) := NULL;
63 l_new_line VARCHAR2(1) := substr('
64 ',1,1);
65 --
66 CURSOR c_par IS
67 SELECT DECODE(prh.action_taken,'UPDATE_HR_COMPLETE', 'Processed:','FUTURE_ACTION', 'Pending:')||
68 'Request Number:'||par.request_number||
69 ', 1st NOA Code:'||par.first_noa_code||
70 DECODE(par.first_noa_cancel_or_correct,'CANCEL','(CANCELED)')||
71 DECODE(par.second_noa_code,NULL,NULL, ', 2nd NOA Code:'||par.second_noa_code)||
72 DECODE(par.second_noa_cancel_or_correct,'CANCEL','(CANCELED)')||
73 --- ', Effective Date:'||TO_CHAR(par.effective_date,'DD-MON-YYYY') list_info
74 ', Effective Date:'||fnd_date.date_to_chardate(par.effective_date) list_info
75 FROM ghr_pa_routing_history prh
76 ,ghr_pa_requests par
77 WHERE par.person_id = p_person_id
78 AND par.effective_date >= p_effective_date
79 AND prh.pa_request_id = par.pa_request_id
80 AND prh.pa_routing_history_id = (SELECT MAX(prh2.pa_routing_history_id)
81 FROM ghr_pa_routing_history prh2
82 WHERE prh2.pa_request_id = par.pa_request_id)
83 AND prh.action_taken IN ('FUTURE_ACTION','UPDATE_HR_COMPLETE')
84 AND par.NOA_FAMILY_CODE <> 'CANCEL'
85 AND ( ( par.second_noa_code IS NULL
86 AND NVL(par.first_noa_cancel_or_correct,'X') <> 'CANCEL'
87 )
88 OR ( par.second_noa_code IS NOT NULL
89 AND par.NOA_FAMILY_CODE <> 'CORRECT'
90 AND ( NVL(par.first_noa_cancel_or_correct,'X') <> 'CANCEL'
91 OR NVL(par.second_noa_cancel_or_correct,'X') <> 'CANCEL'
92 )
93 )
94 OR ( par.second_noa_code IS NOT NULL
95 AND par.NOA_FAMILY_CODE = 'CORRECT'
96 AND NVL(par.second_noa_cancel_or_correct,'X') <> 'CANCEL'
97 )
98 )
99 ORDER BY par.effective_date, par.pa_request_id;
100
101
102 BEGIN
103 -- loop around them all to build up a list
104 FOR c_par_rec IN c_par LOOP
105
106 l_proc_future_list := SUBSTR(l_proc_future_list|| l_new_line || l_new_line ||c_par%ROWCOUNT||'.'||c_par_rec.list_info,1,2000);
107
108 END LOOP;
109
110 RETURN(l_proc_future_list);
111
112 END check_proc_future_pars;
113 --
114 -- This procedure is called from GHRWSREI form and was only written because when we did just
115 -- these 2 calls in the form it call an Error 306... bad 'BIND_I' in the pacakage body
116 -- ghr_non_sf52_extra_info!!! (Well I couldn't explain it!)
117 --
118 PROCEDURE refresh_par_extra_info (p_pa_request_id IN NUMBER
119 ,p_first_noa_id IN NUMBER
120 ,p_second_noa_id IN NUMBER
121 ,p_person_id IN NUMBER
122 ,p_assignment_id IN NUMBER
123 ,p_position_id IN NUMBER
124 ,p_effective_date IN DATE) IS
125 BEGIN
126 ghr_non_sf52_extra_info.populate_noa_spec_extra_info(
127 p_pa_request_id => p_pa_request_id
128 ,p_first_noa_id => p_first_noa_id
129 ,p_second_noa_id => p_second_noa_id
130 ,p_person_id => p_person_id
131 ,p_assignment_id => p_assignment_id
132 ,p_position_id => p_position_id
133 ,p_effective_date => p_effective_date
134 ,p_refresh_flag => 'Y' );
135
136 ghr_non_sf52_extra_info.fetch_generic_extra_info(
137 p_pa_request_id => p_pa_request_id
138 ,p_person_id => p_person_id
139 ,p_assignment_id => p_assignment_id
140 ,p_effective_date => p_effective_date
141 ,p_refresh_flag => 'Y' );
142
143 END;
144 --
145 -- This function is passed an altered Pa request id to check that it is not a request id that
146 -- is also a correction.
147 -- Returns TRUE if the pa request id passed is not a correction
148 --
149 FUNCTION check_first_correction (p_altered_pa_request_id IN NUMBER)
150 RETURN BOOLEAN IS
151 --
152 CURSOR c_par IS
153 SELECT par.noa_family_code
154 FROM ghr_pa_requests par
155 WHERE par.pa_request_id = p_altered_pa_request_id;
156
157 BEGIN
158 -- get the noa_family code of the altered pa request id
159 FOR c_par_rec IN c_par LOOP
160 IF c_par_rec.noa_family_code <> 'CORRECT' THEN
161 RETURN(TRUE);
162 ELSE
163 RETURN(FALSE);
164 END IF;
165 END LOOP;
166
167 -- Shouldn't really get here as that means the PAR id passed in was invalid!!
168 RETURN(FALSE);
169
170 END check_first_correction;
171 --
172 -- this function takes in a pa request id and gives back the 'Agency code Transfer from'
173 -- that is in the PAR EI (should only be called for Appointment transfers, since all these NOACs
174 -- are with APP PM family we will actually call it in the form for ALL NOAC's in the APP family)
175 -- it should then be used to go into field #14
176 FUNCTION get_agency_code_from (p_pa_request_id IN NUMBER
177 ,p_noa_id IN NUMBER)
178 RETURN VARCHAR2 IS
179 CURSOR c_rei IS
180 SELECT rei.rei_information3 agency_code
181 FROM ghr_pa_request_extra_info rei
182 WHERE rei.pa_request_id = p_pa_request_id
183 AND rei.information_type = 'GHR_US_PAR_APPT_TRANSFER'
184 AND EXISTS (SELECT 1
185 FROM ghr_noa_families naf
186 ,ghr_pa_request_info_types rit
187 WHERE rei.information_type = rit.information_type
188 AND rit.noa_family_code = naf.noa_family_code
189 AND naf.nature_of_action_id = p_noa_id);
190 --
191 BEGIN
192 FOR c_rei_rec IN c_rei LOOP
193 RETURN(c_rei_rec.agency_code);
194 END LOOP;
195 --
196 -- Shouldn't really get here as that means the PAR id passed in was invalid!!
197 RETURN(NULL);
198 --
199 END get_agency_code_from;
200 --
201 -- this function takes in a pa request id and gives back the 'Agency code Transfer to'
202 -- that is in the PAR EI (should only be called for NOA 352)
203 -- it should then be used to go into field #22
204 FUNCTION get_agency_code_to (p_pa_request_id IN NUMBER
205 ,p_noa_id IN NUMBER)
206 RETURN VARCHAR2 IS
207 CURSOR c_rei IS
208 SELECT rei.rei_information3 agency_code
209 FROM ghr_pa_request_extra_info rei
210 WHERE rei.pa_request_id = p_pa_request_id
211 AND rei.information_type = 'GHR_US_PAR_MASS_TERM'
212 AND EXISTS (SELECT 1
213 FROM ghr_noa_families naf
214 ,ghr_pa_request_info_types rit
215 WHERE rei.information_type = rit.information_type
216 AND rit.noa_family_code = naf.noa_family_code
217 AND naf.nature_of_action_id = p_noa_id);
218 --
219 BEGIN
220 FOR c_rei_rec IN c_rei LOOP
221 RETURN(c_rei_rec.agency_code);
222 END LOOP;
223 --
224 -- Shouldn't really get here as that means the PAR id passed in was invalid!!
225 RETURN(NULL);
226 --
227 END get_agency_code_to;
228 --
229 FUNCTION get_position_nfc_agency_code(p_position_id IN NUMBER,
230 p_effective_date IN DATE)
231 RETURN VARCHAR2 IS
232 CURSOR cur_pp IS
233 --
234 SELECT pdf.segment3 nfc_agency
235 FROM per_position_definitions pdf, hr_all_positions_f pos
236 WHERE pos.position_id = p_position_id
237 AND p_effective_date between pos.effective_start_date
238 and pos.effective_end_date
239 AND pos.position_definition_id = pdf.position_definition_id;
240 BEGIN
241 FOR cur_pp_rec IN cur_pp LOOP
242 RETURN(cur_pp_rec.nfc_agency);
243 END LOOP;
244
245 RETURN (NULL);
246 END get_position_nfc_agency_code;
247
248 --
249
250 -- This function to be used only for NFC
251 FUNCTION get_poi (p_position_id IN NUMBER,p_effective_date IN DATE)
252 RETURN VARCHAR2 IS
253 CURSOR cur_pp(c_position_id IN NUMBER,c_effective_date IN DATE) IS
254 --
255 SELECT pdf.segment4 poi
256 FROM per_position_definitions pdf, hr_all_positions_f pos
257 WHERE pos.position_id = c_position_id
258 AND c_effective_date between pos.effective_start_date
259 and pos.effective_end_date
260 AND pos.position_definition_id = pdf.position_definition_id;
261
262 BEGIN
263 FOR cur_pp_rec IN cur_pp(p_position_id,p_effective_date) LOOP
264 RETURN(cur_pp_rec.poi);
265 END LOOP;
266
267 RETURN (NULL);
268 END get_poi;
269
270 --
271 FUNCTION get_poi_eit (p_position_id IN NUMBER,
272 p_effective_date in date,
273 p_bg_id in number)
274 RETURN VARCHAR2 IS
275 CURSOR cur_pp IS
276 --
277 select segment4 poi
278 from per_position_definitions ppd,hr_all_positions_f pos
279 where ppd.position_definition_id = pos.position_definition_id
280 and pos.position_id = p_position_id
281 and p_effective_date between pos.effective_start_date and
282 pos.effective_end_date;
283 CURSOR cur_nfc IS
284 SELECT hoi.org_information_context
285 , hoi.org_information6
286 FROM hr_organization_information hoi
287 WHERE hoi.org_information_context = 'GHR_US_ORG_INFORMATION'
288 AND hoi.organization_id = p_bg_id
289 AND hoi.org_information6 = 'Y';
290 BEGIN
291 FOR cur_nfc_rec in cur_nfc LOOP
292 FOR cur_pp_rec IN cur_pp LOOP
293 RETURN(cur_pp_rec.poi);
294 END LOOP;
295 END LOOP;
296
297 RETURN (NULL);
298 END get_poi_eit;
299
300 --
301 FUNCTION get_nfc_agency_eit (p_position_id IN NUMBER,
302 p_effective_date in date,
303 p_bg_id in number)
304 RETURN VARCHAR2 IS
305 CURSOR cur_pp IS
306 --
307 select segment3 nfc_agency
308 from per_position_definitions ppd,hr_all_positions_f pos
309 where ppd.position_definition_id = pos.position_definition_id
310 and pos.position_id = p_position_id
311 and p_effective_date between pos.effective_start_date
312 and pos.effective_end_date;
313 CURSOR cur_nfc IS
314 SELECT hoi.org_information_context
315 , hoi.org_information6
316 FROM hr_organization_information hoi
317 WHERE hoi.org_information_context = 'GHR_US_ORG_INFORMATION'
318 AND hoi.organization_id = p_bg_id
319 AND hoi.org_information6 = 'Y';
320 BEGIN
321 FOR cur_nfc_rec IN cur_nfc LOOP
322 FOR cur_pp_rec IN cur_pp LOOP
323 RETURN(cur_pp_rec.nfc_agency);
324 END LOOP;
325 END LOOP;
326
327 RETURN (NULL);
328 END get_nfc_agency_eit;
329
330 --
331 -- This function has to be called only for NFC
332 FUNCTION get_pay_plan_grade (p_position_id IN NUMBER
333 ,p_effective_date in date)
334 RETURN VARCHAR2 IS
335 --
336 CURSOR cur_pp(c_position_id IN NUMBER
337 ,c_effective_date in date) IS
338 select segment7 grade_id
339 from per_position_definitions ppd,hr_all_positions_f pos
340 where ppd.position_definition_id = pos.position_definition_id
341 and pos.position_id = p_position_id
342 and p_effective_date between pos.effective_start_date
343 and pos.effective_end_date;
344
345 CURSOR get_pay_plan(c_grade_id per_grades.grade_id%type) IS
346 select name grade_name
347 from per_grades
348 where grade_id = c_grade_id;
349
350 l_grade_id per_grades.grade_id%type;
351
352 BEGIN
353 FOR cur_pp_rec IN cur_pp(p_position_id
354 ,p_effective_date) LOOP
355 l_grade_id := cur_pp_rec.grade_id;
356 END LOOP;
357
358 FOR cur_get_pay_plan IN get_pay_plan(l_grade_id) LOOP
359 RETURN(cur_get_pay_plan.grade_name);
360 END LOOP;
361
362 RETURN (NULL);
363
364 END get_pay_plan_grade;
365 --
366 FUNCTION get_pay_plan_grade_eit (p_position_id IN NUMBER,
367 p_effective_date in date,
368 p_bg_id in number)
369 RETURN VARCHAR2 IS
370 CURSOR cur_pp IS
371 --
372 SELECT gdf.segment1 || '-' || gdf.segment2 pay_plan
373 FROM per_grade_definitions gdf
374 ,per_grades grd
375 WHERE grd.grade_id in
376 (select segment7
377 from per_position_definitions ppd,hr_all_positions_f pos
378 where ppd.position_definition_id = pos.position_definition_id
379 and pos.position_id = p_position_id
380 and p_effective_date between pos.effective_start_date and
381 pos.effective_end_date)
382 AND grd.grade_definition_id = gdf.grade_definition_id;
383 CURSOR cur_nfc IS
384 SELECT hoi.org_information_context
385 , hoi.org_information6
386 FROM hr_organization_information hoi
387 WHERE hoi.org_information_context = 'GHR_US_ORG_INFORMATION'
388 AND hoi.organization_id = p_bg_id
389 AND hoi.org_information6 = 'Y';
390 BEGIN
391 FOR cur_nfc_rec IN cur_nfc LOOP
392 FOR cur_pp_rec IN cur_pp LOOP
393 RETURN(cur_pp_rec.pay_plan);
394 END LOOP;
395 END LOOP;
396
397 RETURN (NULL);
398
399 END get_pay_plan_grade_eit;
400 --
401 FUNCTION get_pay_plan (p_position_id IN NUMBER)
402 RETURN VARCHAR2 IS
403 CURSOR cur_pp IS
404 SELECT gdf.segment1 pay_plan
405 FROM per_grade_definitions gdf
406 ,per_grades grd
407 ,per_position_extra_info poi
408 WHERE poi.position_id = p_position_id
409 AND poi.information_type = 'GHR_US_POS_VALID_GRADE'
410 AND grd.grade_id = poi.poei_information3
411 AND grd.grade_definition_id = gdf.grade_definition_id;
412 BEGIN
413 FOR cur_pp_rec IN cur_pp LOOP
414 RETURN(cur_pp_rec.pay_plan);
415 END LOOP;
416
417 RETURN (NULL);
418
419 END get_pay_plan;
420 --
421 FUNCTION get_grade_or_level (p_position_id IN NUMBER)
422 RETURN VARCHAR2 IS
423 --
424 CURSOR cur_pp IS
425 SELECT gdf.segment2 grade_or_level
426 FROM per_grade_definitions gdf
427 ,per_grades grd
428 ,per_position_extra_info poi
429 WHERE poi.position_id = p_position_id
430 AND poi.information_type = 'GHR_US_POS_VALID_GRADE'
431 AND grd.grade_id = poi.poei_information3
432 AND grd.grade_definition_id = gdf.grade_definition_id;
433 BEGIN
434 FOR cur_pp_rec IN cur_pp LOOP
435 RETURN(cur_pp_rec.grade_or_level);
436 END LOOP;
437
438 RETURN (NULL);
439
440 END get_grade_or_level;
441 --
442 FUNCTION get_pos_title_segment(p_business_group_id IN NUMBER)
443 RETURN VARCHAR2 IS
444 --
445 CURSOR cur_org IS
446 SELECT oi.org_information2
447 FROM hr_organization_information oi
448 WHERE oi.organization_id = p_business_group_id
449 AND oi.org_information_context = 'GHR_US_ORG_INFORMATION';
450 BEGIN
451 FOR cur_org_rec IN cur_org LOOP
452 RETURN(cur_org_rec.org_information2 );
453 END LOOP;
454 --
455 RETURN (NULL);
456 --
457 END get_pos_title_segment;
458
459 --
460 FUNCTION chk_position_obligated (p_position_id in number
461 ,p_date in date)
462 RETURN BOOLEAN IS
463 --
464 l_chk_position_obligated boolean :=false;
465 l_expire_date PER_POSITION_EXTRA_INFO.POEI_INFORMATION3%type;
466 l_obligate_type PER_POSITION_EXTRA_INFO.POEI_INFORMATION4%type;
467 l_pos_ei_data PER_POSITION_EXTRA_INFO%ROWTYPE;
468
469 BEGIN
470
471 ghr_history_fetch.fetch_positionei (p_position_id => p_position_id
472 ,p_information_type => 'GHR_US_POS_OBLIG'
473 ,p_date_effective => p_date
474 ,p_pos_ei_data => l_pos_ei_data);
475
476
477 l_expire_date := l_pos_ei_data.POEI_INFORMATION3;
478 l_obligate_type := l_pos_ei_data.POEI_INFORMATION4;
479 if (l_expire_date IS NULL
480 -- OR to_date(l_expire_date,'DD-MON-YYYY') >= p_date )
481 OR fnd_date.canonical_to_date(l_expire_date) >= p_date )
482 and NVL(l_obligate_type,'U') <> 'U' then
483 l_chk_position_obligated :=true;
484 else
485 l_chk_position_obligated :=false;
486 end if;
487
488 return l_chk_position_obligated;
489
490 end chk_position_obligated;
491
492 FUNCTION opm_mandated_duty_stations
493 (p_duty_station_code in ghr_duty_stations_f.duty_station_code%TYPE)
494 RETURN BOOLEAN IS
495 l_ret_val boolean := FALSE;
496 l_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
497 BEGIN
498 l_duty_station_code := p_duty_station_code;
499 if l_duty_station_code in
500 ('040355019', '060920071', '181788003', '181789003', '195549095',
501 '204891103', '211257115', '211758081', '211758187', '213397003',
502 '220376047', '222431059', '240414031', '240931047', '241371003',
503 '265260085', '296675179', '330043017', '343478025', '398961099',
504 '421172125', '424275109', '424676109', '471348157',
505 '484208013', '484209153', '485936303', '511566069', '530171061',
506 '530533025', '541475079', '542325035', '542334035', '542857045',
507 'UV0000000', 'CF0000000', 'CG0000000', 'PS0000000', 'TC0000000',
508 'TC1000000', 'TC1030000', 'TC1040000', 'TC1050000', 'TC1200000',
509 'TC1300000', 'TC1500000', 'WS0000000', '422760045')
510 then
511 l_ret_val := TRUE;
512 hr_utility.set_location('duty station code is OPM Mandated change ' ,20);
513 else
514 l_ret_val := FALSE;
515 hr_utility.set_location('duty station code is not OPM Mandated change ' ,20);
516 end if;
517
518 return l_ret_val;
519 END opm_mandated_duty_stations;
520
521 PROCEDURE duty_station_warn (p_first_noa_id IN NUMBER
522 ,p_second_noa_id IN NUMBER
523 ,p_person_id IN NUMBER
524 ,p_form_ds_code IN ghr_duty_stations_f.duty_station_code%TYPE
525 ,p_effective_date IN DATE
526 ,p_message_set OUT NOCOPY BOOLEAN) IS
527
528 l_proc varchar2(30) := 'duty_station_warn';
529
530 l_noa_family_code ghr_families.noa_family_code%TYPE;
531 l_location_id hr_locations.location_id%TYPE;
532 l_duty_station_id VARCHAR2(150);
533 l_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
534 l_duty_station_desc VARCHAR2(200);
535 l_exists BOOLEAN := FALSE;
536 l_message_set BOOLEAN := FALSE;
537 duty_sta_exp EXCEPTION;
538
539 cursor cur_loc is
540 select paf.location_id location_id
541 from per_assignments_f paf,
542 per_assignment_status_types ast
543 where paf.person_id = p_person_id
544 and p_effective_date
545 between paf.effective_start_date and paf.effective_end_date
546 and ast.assignment_status_type_id = paf.assignment_status_type_id
547 and ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN','TERM_ASSIGN');
548
549 cursor cur_asg is
550 select 1 from per_assignments_f
551 Where person_id = p_person_id
552 and effective_start_date = to_date('19'||'99/01/01','YYYY/MM/DD');
553
554
555 BEGIN
556 hr_utility.set_location('Entering ...' || l_proc,5);
557
558 l_location_id := null;
559 l_noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family(p_first_noa_id);
560
561 for cur_loc_rec in cur_loc
562 loop
563 l_location_id := cur_loc_rec.location_id;
564 end loop;
565
566 IF l_noa_family_code = 'CORRECT' then
567 l_noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family(p_second_noa_id);
568 END IF;
569
570 hr_utility.set_location('Location_id ...' || l_location_id,10);
571 hr_utility.set_location('noa_family_code ...' || l_noa_family_code,10);
572 hr_utility.set_location('form ds code ...' || p_form_ds_code,10);
573 IF l_noa_family_code in
574 ('APP','CHG_DUTY_STATION','CONV_APP','POS_CHG','POS_ESTABLISH',
575 'REALIGNMENT','REASSIGNMENT','RECRUIT_FILL',
576 'RETURN_TO_DUTY','SALARY_CHG') then
577 for cur_asg_rec in cur_asg
578 loop
579 l_exists := TRUE;
580 end loop;
581 if l_location_id is not null then
582 ghr_pa_requests_pkg.get_SF52_loc_ddf_details
583 (p_location_id => l_location_id
584 ,p_duty_station_id => l_duty_station_id);
585
586 hr_utility.set_location('duty station id ...' || l_duty_station_id,15);
587
588 ghr_pa_requests_pkg.get_duty_station_details
589 (p_duty_station_id => l_duty_station_id
590 ,p_effective_date => p_effective_date
591 ,p_duty_station_code => l_duty_station_code
592 ,p_duty_station_desc => l_duty_station_desc);
593
594 hr_utility.set_location('duty station code..' || l_duty_station_code,20);
595 else
596 l_duty_station_code := null;
597 hr_utility.set_location('duty station code..is null ' ,20);
598 end if;
599
600 if p_effective_date < to_date('19'||'99/01/01','YYYY/MM/DD') then
601 if nvl(p_form_ds_code, '123456789') <> nvl(l_duty_station_code, '123456789') then
602 if l_exists then
603 if l_noa_family_code in
604 ('CHG_DUTY_STATION','CONV_APP','POS_CHG','POS_ESTABLISH',
605 'REALIGNMENT','REASSIGNMENT','RECRUIT_FILL',
606 'RETURN_TO_DUTY','SALARY_CHG') then
607 hr_utility.set_location('GHR_38147_NOAC_RPA_900' ,25);
608 hr_utility.set_message(8301,'GHR_38147_NOAC_RPA_900');
609 raise duty_sta_exp;
610 else
611 if l_noa_family_code = 'APP' then
612 if opm_mandated_duty_stations(p_form_ds_code) then
613 hr_utility.set_location('GHR_38148_RERUN_DUTYSTN_CONV' ,25);
614 hr_utility.set_message(8301,'GHR_38148_RERUN_DUTYSTN_CONV');
615 raise duty_sta_exp;
616 end if;
617 end if;
618 end if;
619 else
620 if l_noa_family_code in
621 ('CHG_DUTY_STATION','CONV_APP','POS_CHG','POS_ESTABLISH',
622 'REALIGNMENT','REASSIGNMENT','RECRUIT_FILL',
623 'RETURN_TO_DUTY','SALARY_CHG') then
624 if opm_mandated_duty_stations(p_form_ds_code) then
625 hr_utility.set_location('GHR_38148_RERUN_DUTYSTN_CONV' ,25);
626 hr_utility.set_message(8301,'GHR_38148_RERUN_DUTYSTN_CONV');
627 raise duty_sta_exp;
628 end if;
629 else
630 if l_noa_family_code = 'APP' then
631 if opm_mandated_duty_stations(p_form_ds_code) then
632 hr_utility.set_location('GHR_38149_RERUN_CHECK_DUTYSTN' ,25);
633 hr_utility.set_message(8301,'GHR_38149_RERUN_CHECK_DUTYSTN');
634 raise duty_sta_exp;
635 end if;
636 end if;
637 end if;
638 end if;
639 else
640 if l_noa_family_code in
641 ('CHG_DUTY_STATION','CONV_APP','POS_CHG','POS_ESTABLISH',
642 'REALIGNMENT','REASSIGNMENT','RECRUIT_FILL',
643 'RETURN_TO_DUTY','SALARY_CHG') then
644 if l_exists then
645 hr_utility.set_location('GHR_38147_NOAC_RPA_900' ,25);
646 hr_utility.set_message(8301,'GHR_38147_NOAC_RPA_900');
647 raise duty_sta_exp;
648 else
649 if opm_mandated_duty_stations(p_form_ds_code) then
650 hr_utility.set_location('GHR_38148_RERUN_DUTYSTN_CONV' ,25);
651 hr_utility.set_message(8301,'GHR_38148_RERUN_DUTYSTN_CONV');
652 raise duty_sta_exp;
653 end if;
654 end if;
655 end if;
656
657 end if;
658 end if;
659 END IF;
660 hr_utility.set_location('Leaving ...' || l_proc,30);
661 p_message_set := l_message_set;
662 exception when duty_sta_exp then
663 l_message_set := TRUE;
664 p_message_set := l_message_set;
665 END duty_station_warn;
666
667 --
668
669 FUNCTION get_corr_cop (p_altered_pa_request_id
670 IN ghr_pa_requests.altered_pa_request_id%type)
671 RETURN NUMBER IS
672
673 cursor cur_other is
674 select EMPLOYEE_ASSIGNMENT_ID,
675 EFFECTIVE_DATE
676 from ghr_pa_requests
677 where pa_request_id = p_altered_pa_request_id;
678
679 l_assignment_id NUMBER(15);
680 l_effective_date DATE;
681 l_multiple_error_flag BOOLEAN;
682 l_capped_other_pay NUMBER;
683 begin
684
685 for cur_other_rec in cur_other
686 loop
687 l_assignment_id := cur_other_rec.employee_assignment_id;
688 l_effective_date := cur_other_rec.effective_date;
689 end loop;
690
691 ghr_api.retrieve_element_entry_value
692 (p_element_name => 'Other Pay'
693 ,p_input_value_name => 'Capped Other Pay'
694 ,p_assignment_id => l_assignment_id
695 ,p_effective_date => l_effective_date
696 ,p_value => l_capped_other_pay
697 ,p_multiple_error_flag => l_multiple_error_flag);
698
699
700 RETURN (l_capped_other_pay);
701
702 END get_corr_cop;
703
704 FUNCTION get_cop ( p_assignment_id IN per_assignments_f.assignment_id%type
705 ,p_effective_date IN date)
706
707 RETURN NUMBER IS
708 l_capped_other_pay NUMBER;
709 l_multiple_error_flag BOOLEAN;
710 begin
711
712
713 ghr_api.retrieve_element_entry_value
714 (p_element_name => 'Other Pay'
715 ,p_input_value_name => 'Capped Other Pay'
716 ,p_assignment_id => p_assignment_id
717 ,p_effective_date => nvl(p_effective_date,trunc(sysdate))
718 ,p_value => l_capped_other_pay
719 ,p_multiple_error_flag => l_multiple_error_flag);
720
721 RETURN (l_capped_other_pay);
722
723 END get_cop;
724
725
726 --
727 PROCEDURE chk_position_end_date (p_position_id IN NUMBER
728 ,p_business_group_id IN NUMBER
729 ,p_effective_date IN DATE
730 ,p_message_set OUT NOCOPY BOOLEAN) IS
731
732 l_proc varchar2(30) := 'chk_position_end_date';
733
734 l_effective_start_date date;
735 l_effective_end_date date;
736 l_status VARCHAR2(30);
737 l_message_set BOOLEAN := FALSE;
738
739 cursor cur_pos is
740 select pos.effective_start_date,pos.effective_end_date,typ.system_type_cd status
741 from hr_all_positions_f pos, per_shared_types typ
742 where p_effective_date
743 between pos.effective_start_date and pos.effective_end_date
744 and pos.business_group_id = p_business_group_id
745 and pos.position_id = p_position_id
746 and pos.availability_status_id = typ.shared_type_id
747 union
748 select pos1.effective_start_date,pos1.effective_end_date,typ1.system_type_cd status
749 from hr_all_positions_f pos1, per_shared_types typ1
750 where p_effective_date <= pos1.effective_start_date
751 and pos1.business_group_id = p_business_group_id
752 and pos1.position_id = p_position_id
753 and pos1.availability_status_id = typ1.shared_type_id
754 order by 1;
755
756 BEGIN
757 hr_utility.set_location('Entering ...' || l_proc,5);
758
759 for cur_pos_rec in cur_pos
760 loop
761 l_effective_start_date := cur_pos_rec.effective_start_date;
762 l_effective_end_date := cur_pos_rec.effective_end_date;
763 l_status := cur_pos_rec.status;
764
765 if l_effective_end_date = to_date('4712/12/31','YYYY/MM/DD') then
766 if l_status = 'ACTIVE' then
767 p_message_set := l_message_set;
768 else
769 l_message_set := TRUE;
770 p_message_set := l_message_set;
771 end if;
772 else
773 if l_status = 'ACTIVE' then
774 p_message_set := l_message_set;
775 else
776 l_message_set := TRUE;
777 p_message_set := l_message_set;
778 end if;
779 end if;
780 end loop;
781 p_message_set := l_message_set;
782
783 hr_utility.set_location('Leaving ...' || l_proc,30);
784
785 EXCEPTION
786 WHEN OTHERS THEN
787 p_message_set := NULL;
788
789 END chk_position_end_date;
790
791
792 --
793 PROCEDURE chk_position_hire_status (p_position_id IN NUMBER
794 ,p_business_group_id IN NUMBER
795 ,p_effective_date IN DATE
796 ,p_message_set OUT NOCOPY BOOLEAN) IS
797
798 l_proc varchar2(30) := 'chk_position_hire_status';
799
800 l_effective_start_date date;
801 l_effective_end_date date;
802 l_status VARCHAR2(30);
803 l_message_set BOOLEAN := FALSE;
804
805 cursor cur_pos is
806 select effective_start_date,effective_end_date,system_type_cd status
807 from hr_all_positions_f pos, per_shared_types typ
808 where p_effective_date
809 between effective_start_date and effective_end_date
810 and position_id = p_position_id
811 and pos.business_group_id = p_business_group_id
812 and pos.availability_status_id = typ.shared_type_id
813 order by 1;
814
815 BEGIN
816 hr_utility.set_location('Entering ...' || l_proc,5);
817
818 for cur_pos_rec in cur_pos
819 loop
820 l_effective_start_date := cur_pos_rec.effective_start_date;
821 l_effective_end_date := cur_pos_rec.effective_end_date;
822 l_status := cur_pos_rec.status;
823
824 if l_status in ('FROZEN','PROPOSED') then
825 l_message_set := TRUE;
826 p_message_set := l_message_set;
827 end if;
828
829 end loop;
830 p_message_set := l_message_set;
831
832 hr_utility.set_location('Leaving ...' || l_proc,30);
833 EXCEPTION
834 WHEN OTHERS THEN
835 p_message_set := NULL;
836 END chk_position_hire_status;
837 --
838 -- This function is to display a warning message while processing 850 action
839 -- whenever the sum of individual components versus the total value of mddds pay is
840 -- having difference.
841 --
842 FUNCTION check_mddds_pay (p_pa_request_id IN NUMBER)
843 RETURN BOOLEAN IS
844 --
845 CURSOR c_par_mddds IS
846 select (nvl(REI_INFORMATION9,0) +
847 nvl(REI_INFORMATION10,0) +
848 nvl(REI_INFORMATION3,0) +
849 nvl(REI_INFORMATION4,0) +
850 nvl(REI_INFORMATION5,0) +
851 nvl(REI_INFORMATION6,0) +
852 nvl(REI_INFORMATION7,0) +
853 nvl(REI_INFORMATION8,0)) cal_amt,
854 nvl(REI_INFORMATION11,0) tot_amt
855 from ghr_pa_request_extra_info
856 where pa_request_id = p_pa_request_id
857 and information_type = 'GHR_US_PAR_MD_DDS_PAY';
858
859 BEGIN
860 -- get the sum of all components values and the total value.
861 FOR c_par_rec IN c_par_mddds LOOP
862 IF c_par_rec.cal_amt <> c_par_rec.tot_amt THEN
863 RETURN(TRUE);
864 ELSE
865 RETURN(FALSE);
866 END IF;
867 END LOOP;
868
869 RETURN(FALSE);
870 --
871 END check_mddds_pay;
872 --
873 --
874
875 --Bug # 9329643
876 FUNCTION get_employee_number(p_person_id IN NUMBER,p_effective_date IN DATE)
877 RETURN VARCHAR2 IS
878
879 CURSOR c_per IS
880 SELECT per.employee_number
881 FROM per_people_f per
882 WHERE per.person_id = p_person_id
883 AND NVL(p_effective_date, TRUNC(sysdate)) BETWEEN per.effective_start_date
884 AND per.effective_end_date;
885
886 l_employee_number per_people_f.employee_number%type;
887 BEGIN
888 FOR rec_per in c_per LOOP
889 l_employee_number := rec_per.employee_number;
890 exit;
891 END LOOP;
892
893 return(l_employee_number);
894 END;
895
896 FUNCTION primary_key_chk(p_table_name IN VARCHAR2,
897 p_var_col1 IN VARCHAR2 DEFAULT NULL,
898 p_value1 IN VARCHAR2 DEFAULT NULL,
899 p_var_col2 IN VARCHAR2 DEFAULT NULL,
900 p_value2 IN VARCHAR2 DEFAULT NULL,
901 p_var_col3 IN VARCHAR2 DEFAULT NULL,
902 p_value3 IN VARCHAR2 DEFAULT NULL,
903 p_num_col1 IN VARCHAR2 DEFAULT NULL,
904 p_num_value1 IN NUMBER DEFAULT NULL,
905 p_num_col2 IN VARCHAR2 DEFAULT NULL,
906 p_num_value2 IN NUMBER DEFAULT NULL,
907 p_num_col3 IN VARCHAR2 DEFAULT NULL,
908 p_num_value3 IN NUMBER DEFAULT NULL,
909 p_dt_col1 IN VARCHAR2 DEFAULT NULL,
910 p_dt_value1 IN DATE DEFAULT NULL,
911 p_dt_col2 IN VARCHAR2 DEFAULT NULL,
912 p_dt_value2 IN DATE DEFAULT NULL,
913 p_dt_col3 IN VARCHAR2 DEFAULT NULL,
914 p_dt_value3 IN DATE DEFAULT NULL) RETURN BOOLEAN IS
915
916 p_exists varchar2(4);
917 p_condition varchar2(2000);
918 p_dyn_query varchar2(2000);
919
920 BEGIN
921 --p_condition := 'SELECT 1 FROM '||p_table_name||' WHERE ';
922 p_condition := NULL;
923 p_dyn_query := NULL;
924 IF p_var_col1 is NOT NULL AND p_value1 IS NOT NULL THEN
925 IF p_condition is NOT NULL THEN
926 p_condition := p_condition||' AND '||p_var_col1||' = '''||p_value1||'''';
927 ELSE
928 p_condition := p_var_col1||' = '''||p_value1||'''';
929 END IF;
930 END IF;
931
932 IF p_var_col2 is NOT NULL AND p_value2 IS NOT NULL THEN
933 IF p_condition is NOT NULL THEN
934 p_condition := p_condition||' AND '||p_var_col2||' = '''||p_value2||'''';
935 ELSE
936 p_condition := p_var_col2||' = '''||p_value2||'''';
937 END IF;
938 END IF;
939
940 IF p_var_col3 is NOT NULL AND p_value3 IS NOT NULL THEN
941 IF p_condition is NOT NULL THEN
942 p_condition := p_condition||' AND '||p_var_col3||' = '''||p_value3||'''';
943 ELSE
944 p_condition := p_var_col3||' = '''||p_value3||'''';
945 END IF;
946 END IF;
947
948 IF p_num_col1 is NOT NULL AND p_num_value1 IS NOT NULL THEN
949 IF p_condition is NOT NULL THEN
950 p_condition := p_condition||' AND '||p_num_col1||' = '||p_num_value1;
951 ELSE
952 p_condition := p_num_col1||' = '||p_num_value1;
953 END IF;
954 END IF;
955
956 IF p_num_col2 is NOT NULL AND p_num_value2 IS NOT NULL THEN
957 IF p_condition is NOT NULL THEN
958 p_condition := p_condition||' AND '||p_num_col2||' = '||p_num_value2;
959 ELSE
960 p_condition := p_num_col2||' = '||p_num_value2;
961 END IF;
962 END IF;
963
964 IF p_num_col3 is NOT NULL AND p_num_value3 IS NOT NULL THEN
965 IF p_condition is NOT NULL THEN
966 p_condition := p_condition||' AND '||p_num_col3||' = '||p_num_value3;
967 ELSE
968 p_condition := p_num_col3||' = '||p_num_value3;
969 END IF;
970 END IF;
971
972 IF p_dt_col1 is NOT NULL AND p_dt_value1 IS NOT NULL THEN
973 IF p_condition is NOT NULL THEN
974 p_condition := p_condition||' AND '||' TO_DATE('||p_dt_col1||',''DD/MM/RRRR'') = TO_DATE('''||p_dt_value1||''',''DD/MM/RRRR'')';
975 ELSE
976 p_condition := ' TO_DATE('||p_dt_col1||',''DD/MM/RRRR'') = TO_DATE('''||p_dt_value1||''',''DD/MM/RRRR'')';
977 END IF;
978 END IF;
979
980 IF p_dt_col2 is NOT NULL AND p_dt_value2 IS NOT NULL THEN
981 IF p_condition is NOT NULL THEN
982 p_condition := p_condition||' AND '||' TO_DATE('||p_dt_col2||',''DD/MM/RRRR'') = TO_DATE('''||p_dt_value2||''',''DD/MM/RRRR'')';
983 ELSE
984 p_condition := ' TO_DATE('||p_dt_col2||',''DD/MM/RRRR'') = TO_DATE('''||p_dt_value2||''',''DD/MM/RRRR'')';
985 END IF;
986 END IF;
987
988 IF p_dt_col3 is NOT NULL AND p_dt_value3 IS NOT NULL THEN
989 IF p_condition is NOT NULL THEN
990 p_condition := p_condition||' AND '||' TO_DATE('||p_dt_col3||',''DD/MM/RRRR'') = TO_DATE('''||p_dt_value3||''',''DD/MM/RRRR'')';
991 ELSE
992 p_condition := ' TO_DATE('||p_dt_col3||',''DD/MM/RRRR'') = TO_DATE('''||p_dt_value3||''',''DD/MM/RRRR'')';
993 END IF;
994 END IF;
995
996 p_dyn_query := 'SELECT 1 FROM '||p_table_name||' WHERE '||p_condition;
997
998 BEGIN
999 EXECUTE IMMEDIATE p_dyn_query into p_exists;
1000 EXCEPTION
1001 WHEN NO_DATA_FOUND THEN
1002 return(FALSE);
1003 END;
1004 IF p_exists IS NULL THEN
1005 return(FALSE);
1006 ELSE
1007 return(TRUE);
1008 END IF;
1009 END;
1010
1011
1012 END ghr_pa_requests_pkg2;
[Home] [Help]