1 PACKAGE BODY ghr_pa_requests_pkg AS
2 /* $Header: ghparqst.pkb 120.8.12020000.2 2012/07/05 14:40:31 amnaraya ship $ */
3
4 -- For a particuar NOA Family this function returns the procesing method for a given
5 -- item in a form
6 PROCEDURE get_process_method(
7 p_noa_family_code IN ghr_noa_fam_proc_methods.noa_family_code%TYPE
8 ,p_form_block_name IN ghr_pa_data_fields.form_block_name%TYPE
9 ,p_form_field_name IN ghr_pa_data_fields.form_field_name%TYPE
10 ,p_effective_date IN DATE
11 ,p_process_method_code OUT NOCOPY VARCHAR2
12 ,p_navigable_flag OUT NOCOPY VARCHAR2) IS
13
14 CURSOR cur_nfp IS
15 SELECT nfp.process_method_code pm_code
16 ,nfp.navigable_flag
17 FROM ghr_noa_fam_proc_methods nfp
18 ,ghr_pa_data_fields pdf
19 WHERE pdf.pa_data_field_id = nfp.pa_data_field_id
20 AND pdf.form_block_name = p_form_block_name
21 AND pdf.form_field_name = p_form_field_name
22 AND nfp.noa_family_code = p_noa_family_code
23 AND nfp.enabled_flag = 'Y'
24 AND NVL(p_effective_date,TRUNC(sysdate))
25 BETWEEN NVL(nfp.start_date_active,NVL(p_effective_date,TRUNC(sysdate)))
26 AND NVL(nfp.end_date_active,NVL(p_effective_date,TRUNC(sysdate)));
27
28 BEGIN
29 FOR cur_nfp_rec IN cur_nfp LOOP
30 p_process_method_code := cur_nfp_rec.pm_code;
31 p_navigable_flag := cur_nfp_rec.navigable_flag;
32 END LOOP;
33
34 EXCEPTION
35
36 -- Reset IN OUT parameters and set OUT parameters
37 WHEN others THEN
38 p_process_method_code := NULL;
39 p_navigable_flag := NULL;
40 RAISE;
41
42 END get_process_method;
43
44 FUNCTION get_data_field_name(
45 p_form_block_name IN ghr_pa_data_fields.form_block_name%TYPE
46 ,p_form_field_name IN ghr_pa_data_fields.form_field_name%TYPE)
47 RETURN VARCHAR2 IS
48 --
49 CURSOR cur_pdf IS
50 SELECT pdf.name
51 FROM ghr_pa_data_fields pdf
52 WHERE pdf.form_block_name = p_form_block_name
53 AND pdf.form_field_name = p_form_field_name;
54 --
55 BEGIN
56 FOR cur_pdf_rec IN cur_pdf LOOP
57 RETURN(cur_pdf_rec.name);
58 END LOOP;
59 --
60 RETURN (NULL);
61 --
62 END get_data_field_name;
63 --
64 PROCEDURE get_restricted_process_method(
65 p_restricted_form IN ghr_restricted_proc_methods.restricted_form%TYPE
66 ,p_form_block_name IN ghr_pa_data_fields.form_block_name%TYPE
67 ,p_form_field_name IN ghr_pa_data_fields.form_field_name%TYPE
68 ,p_restricted_proc_method OUT NOCOPY VARCHAR2) IS
69
70 -- there is no need to pass in an effective data since the restricted form is for a user and is not
71 -- relevant to the 'effective date' on the SF52 we will do the restricted form as of todays date
72 CURSOR cur_rpm IS
73 SELECT rpm.restricted_proc_method
74 FROM ghr_pa_data_fields pdf
75 ,ghr_restricted_proc_methods rpm
76 WHERE pdf.pa_data_field_id = rpm.pa_data_field_id
77 AND rpm.restricted_form = p_restricted_form
78 AND pdf.form_block_name = p_form_block_name
79 AND pdf.form_field_name = p_form_field_name
80 AND rpm.enabled_flag = 'Y'
81 AND TRUNC(sysdate)
82 BETWEEN NVL(rpm.start_date_active,TRUNC(sysdate))
83 AND NVL(rpm.end_date_active,TRUNC(sysdate));
84
85 BEGIN
86 p_restricted_proc_method := NULL;
87 FOR cur_rpm_rec IN cur_rpm LOOP
88 p_restricted_proc_method := cur_rpm_rec.restricted_proc_method;
89 END LOOP;
90
91 EXCEPTION
92 -- Reset IN OUT parameters and set OUT parameters
93 WHEN others THEN
94 p_restricted_proc_method := NULL;
95 RAISE;
96
97 END get_restricted_process_method;
98
99 FUNCTION get_lookup_meaning(
100 p_application_id NUMBER
101 ,p_lookup_type hr_lookups.lookup_type%TYPE
102 ,p_lookup_code hr_lookups.lookup_code%TYPE)
103 RETURN VARCHAR2 IS
104
105 CURSOR cur_loc IS
106 SELECT loc.meaning
107 FROM hr_lookups loc
108 WHERE loc.lookup_type = p_lookup_type
109 AND loc.lookup_code = p_lookup_code;
110
111 BEGIN
112 -- Previously this routine used to go directly in on FND_COMMON_LOOKUPS
113 -- now it assumes it was only used for types with application id 800 and hence
114 -- should use HR_LOOKUPS
115 IF p_application_id = 800 THEN
116 FOR cur_loc_rec IN cur_loc LOOP
117 RETURN(cur_loc_rec.meaning);
118 END LOOP;
119 ELSE
120 hr_utility.set_message(8301, 'GHR_38596_NOT_HR_LOOKUP');
121 hr_utility.raise_error;
122 END IF;
123
124 RETURN(NULL);
125
126 END get_lookup_meaning;
127
128 FUNCTION get_lookup_description(
129 p_application_id NUMBER
130 ,p_lookup_type hr_lookups.lookup_type%TYPE
131 ,p_lookup_code hr_lookups.lookup_code%TYPE)
132 RETURN VARCHAR2 IS
133
134 CURSOR cur_loc IS
135 SELECT loc.description
136 FROM hr_lookups loc
137 WHERE loc.lookup_type = p_lookup_type
138 AND loc.lookup_code = p_lookup_code;
139
140 BEGIN
141 -- Previously this routine used to go directly in on FND_COMMON_LOOKUPS
142 -- now it assumes it was only used for types with application id 800 and hence
143 -- should use HR_LOOKUPS
144 IF p_application_id = 800 THEN
145 FOR cur_loc_rec IN cur_loc LOOP
146 RETURN(cur_loc_rec.description);
147 END LOOP;
148 ELSE
149 -- cannot use hr_utility as it will violate the pragma we need therfore just return
150 -- error!! -- This shouldn't happen anyway as this procedure should only be called for
151 -- application id 800!
152 RETURN('Error: GHR_38596_NOT_HR_LOOKUP');
153 END IF;
154
155 RETURN(NULL);
156
157 END get_lookup_description;
158
159 FUNCTION get_noa_family_name(
160 p_noa_family_code ghr_families.noa_family_code%TYPE)
161 RETURN VARCHAR2 IS
162
163 l_ret_val ghr_families.name%TYPE := NULL;
164
165 CURSOR cur_fam IS
166 SELECT fam.name
167 FROM ghr_families fam
168 WHERE fam.noa_family_code = p_noa_family_code;
169
170 BEGIN
171 FOR cur_fam_rec IN cur_fam LOOP
172 l_ret_val := cur_fam_rec.name;
173 END LOOP;
174
175 RETURN(l_ret_val);
176
177 END get_noa_family_name;
178
179 FUNCTION get_routing_group_name(
180 p_routing_group_id ghr_routing_groups.routing_group_id%TYPE)
181 RETURN VARCHAR2 IS
182
183 l_ret_val ghr_routing_groups.name%TYPE := NULL;
184
185 CURSOR cur_rgr IS
186 SELECT rgr.name
187 FROM ghr_routing_groups rgr
188 WHERE rgr.routing_group_id = p_routing_group_id;
189
190 BEGIN
191 FOR cur_rgr_rec IN cur_rgr LOOP
192 l_ret_val := cur_rgr_rec.name;
193 END LOOP;
194
195 RETURN(l_ret_val);
196
197 END get_routing_group_name;
198
199 FUNCTION get_full_name(
200 p_person_id per_people_f.person_id%TYPE
201 ,p_effective_date date)
202 RETURN VARCHAR2 IS
203 l_ret_val VARCHAR2(240) := NULL;
204 -- last name is 40 long, first name is 20 long and middles names is 60 long
205 -- therfore plus ',' and ' ' max length is 122!
206
207 CURSOR cur_per IS
208 SELECT per.last_name||','|| per.first_name||' '|| per.middle_names full_name
209 FROM per_people_f per
210 WHERE per.person_id = p_person_id
211 AND NVL(p_effective_date,TRUNC(sysdate)) between per.effective_start_date and per.effective_end_date;
212
213 BEGIN
214 FOR cur_per_rec IN cur_per LOOP
215 l_ret_val := substr(cur_per_rec.full_name,1,240);
216 END LOOP;
217
218 RETURN(l_ret_val);
219
220 END get_full_name;
221
222
223 FUNCTION get_full_name_unsecure(
224 p_person_id per_people_f.person_id%TYPE
225 ,p_effective_date date )
226 RETURN VARCHAR2 IS
227 l_ret_val VARCHAR2(240):=NULL;
228
229 -- last name is 40 long, first name is 20 long and middles names is 60 long
230 -- therfore plus ',' and ' ' max length is 122!
231
232 CURSOR cur_per IS
233 SELECT per.last_name||','|| per.first_name||' '|| per.middle_names full_name
234 FROM per_all_people_f per
235 WHERE per.person_id = p_person_id
236 AND NVL(p_effective_date,TRUNC(sysdate)) BETWEEN per.effective_start_date AND per.effective_end_date;
237
238 BEGIN
239 FOR cur_per_rec IN cur_per LOOP
240 l_ret_val := substr(cur_per_rec.full_name,1,240);
241 END LOOP;
242
243 RETURN(l_ret_val);
244
245 END get_full_name_unsecure;
246
247
248 FUNCTION get_noa_descriptor(
249 p_nature_of_action_id IN ghr_nature_of_actions.nature_of_action_id%TYPE)
250 RETURN VARCHAR2 IS
251
252 l_ret_val ghr_nature_of_actions.description%TYPE := NULL;
253
254 CURSOR cur_noa IS
255 SELECT noa.description
256 FROM ghr_nature_of_actions noa
257 WHERE noa.nature_of_action_id= p_nature_of_action_id;
258
259 BEGIN
260 FOR cur_noa_rec IN cur_noa LOOP
261 l_ret_val := cur_noa_rec.description;
262 END LOOP;
263
264 RETURN(l_ret_val);
265
266 END get_noa_descriptor;
267
268 FUNCTION get_remark_descriptor(
269 p_remark_id IN ghr_remarks.remark_id%TYPE)
270 RETURN VARCHAR2 IS
271
272 l_ret_val ghr_remarks.description%TYPE := NULL;
273
274 CURSOR cur_rem IS
275 SELECT rem.description
276 FROM ghr_remarks rem
277 WHERE rem.remark_id = p_remark_id;
278
279 BEGIN
280 FOR cur_rem_rec IN cur_rem LOOP
281 l_ret_val := cur_rem_rec.description;
282 END LOOP;
283
284 RETURN(l_ret_val);
285
286 END get_remark_descriptor;
287
288 -- Bug#5482191 Added the function get_personnel_system_indicator
289 FUNCTION get_personnel_system_indicator(
290 p_position_id hr_all_positions_f.position_id%TYPE
291 ,p_effective_date date)
292 RETURN VARCHAR2 IS
293 l_pos_psi_data per_position_extra_info%rowtype;
294 l_personnel_system_indicator VARCHAR2(30);
295 BEGIN
296 hr_utility.set_location('Entering get_psi',0);
297 ghr_history_fetch.fetch_positionei(
298 p_position_id => p_position_id,
299 p_information_type => 'GHR_US_POS_AFHR_DATA',
300 p_date_effective => p_effective_date,
301 p_pos_ei_data => l_pos_psi_data);
302 IF l_pos_psi_data.position_extra_info_id is not null THEN
303 l_personnel_system_indicator := l_pos_psi_data.poei_information3;
304 ELSE
305 l_personnel_system_indicator := '00';
306 END IF;
307 hr_utility.set_location('Leaving get_psi',10);
308 RETURN l_personnel_system_indicator;
309 EXCEPTION
310 WHEN OTHERS THEN
311 hr_utility.set_location('Leaving get_psi',20);
312 RAISE;
313 END get_personnel_system_indicator;
314 --
315
316 PROCEDURE get_default_routing_group(p_user_name IN fnd_user.user_name%TYPE
317 ,p_routing_group_id IN OUT NOCOPY NUMBER
318 ,p_initiator_flag IN OUT NOCOPY VARCHAR2
319 ,p_requester_flag IN OUT NOCOPY VARCHAR2
320 ,p_authorizer_flag IN OUT NOCOPY VARCHAR2
321 ,p_personnelist_flag IN OUT NOCOPY VARCHAR2
322 ,p_approver_flag IN OUT NOCOPY VARCHAR2
323 ,p_reviewer_flag IN OUT NOCOPY VARCHAR2) IS
324
325 l_routing_group_id NUMBER;
326 l_initiator_flag VARCHAR2(150);
327 l_requester_flag VARCHAR2(150);
328 l_authorizer_flag VARCHAR2(150);
329 l_personnelist_flag VARCHAR2(150);
330 l_approver_flag VARCHAR2(150);
331 l_reviewer_flag VARCHAR2(150);
332
333 CURSOR cur_rgr IS
334 -- Note: pei_information10 is a flag that indicates which is the defaulting routing_group
335 SELECT pei.pei_information3 routing_group_id
336 ,pei.pei_information4 initiator_flag
337 ,pei.pei_information5 requester_flag
338 ,pei.pei_information6 authorizer_flag
339 ,pei.pei_information7 personnelist_flag
340 ,pei.pei_information8 approver_flag
341 ,pei.pei_information9 reviewer_flag
342 FROM per_people_extra_info pei
343 ,fnd_user use
344 WHERE use.user_name = p_user_name
345 AND pei.person_id = use.employee_id
346 AND pei.information_type = 'GHR_US_PER_WF_ROUTING_GROUPS'
347 AND pei.pei_information10 = 'Y';
348
349
350 BEGIN
351
352 --Initialisation for NOCOPY Changes
353 l_routing_group_id :=p_routing_group_id;
354 l_initiator_flag :=p_initiator_flag;
355 l_requester_flag :=p_requester_flag;
356 l_authorizer_flag :=p_authorizer_flag;
357 l_personnelist_flag :=p_personnelist_flag;
358 l_approver_flag :=p_approver_flag;
359 l_reviewer_flag :=p_reviewer_flag;
360
361 -- while we are here we may as well get the personal roles even though this maybe overwriten
362 -- by the group box roles later
363 FOR cur_rgr_rec IN cur_rgr LOOP
364 p_routing_group_id := cur_rgr_rec.routing_group_id;
365 p_initiator_flag := cur_rgr_rec.initiator_flag;
366 p_requester_flag := cur_rgr_rec.requester_flag;
367 p_authorizer_flag := cur_rgr_rec.authorizer_flag;
368 p_personnelist_flag := cur_rgr_rec.personnelist_flag;
369 p_approver_flag := cur_rgr_rec.approver_flag;
370 p_reviewer_flag := cur_rgr_rec.reviewer_flag;
371 END LOOP;
372 EXCEPTION
373 -- Reset IN OUT parameters and set OUT parameters
374 WHEN others THEN
375 p_routing_group_id :=l_routing_group_id;
376 p_initiator_flag :=l_initiator_flag;
377 p_requester_flag :=l_requester_flag;
378 p_authorizer_flag :=l_authorizer_flag;
379 p_personnelist_flag :=l_personnelist_flag;
380 p_approver_flag :=l_approver_flag;
381 p_reviewer_flag :=l_reviewer_flag;
382 RAISE;
383
384 END get_default_routing_group;
385
386 PROCEDURE get_last_routing_list(p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE
387 ,p_routing_list_id OUT NOCOPY ghr_routing_lists.routing_list_id%TYPE
388 ,p_routing_list_name OUT NOCOPY ghr_routing_lists.name%TYPE
389 ,p_next_seq_number OUT NOCOPY ghr_routing_list_members.seq_number%TYPE
390 ,p_next_user_name OUT NOCOPY ghr_routing_list_members.user_name%TYPE
391 ,p_next_groupbox_id OUT NOCOPY ghr_routing_list_members.groupbox_id%TYPE
392 ,p_broken IN OUT NOCOPY BOOLEAN) IS
393
394
395 l_broken BOOLEAN ;
396
397 -- need to select the last routing list used for the given pa request and determine
398 -- if that was the last record
399 CURSOR cur_prh_last_rli IS
400 SELECT rli.routing_list_id
401 ,rli.name
402 ,prh.routing_seq_number
403 ,prh.pa_routing_history_id
404 FROM ghr_routing_lists rli
405 ,ghr_pa_routing_history prh
406 WHERE prh.pa_request_id = p_pa_request_id
407 AND prh.routing_list_id = rli.routing_list_id
408 ORDER BY prh.pa_routing_history_id DESC;
409 -- The order by makes sure the first one we get is the last in the history
410 -- By joing to routing_list forces us to have a routing_list (since we didn't do an outer join)
411
412 -- Just get the last record so we can see if the cursor above got us the last record
413 CURSOR cur_prh_last IS
414 SELECT prh.pa_routing_history_id
415 FROM ghr_pa_routing_history prh
416 WHERE prh.pa_request_id = p_pa_request_id
417 ORDER BY prh.pa_routing_history_id DESC;
418 -- Again the order by saves us having to do a max
419
420 CURSOR cur_rlm (p_routing_list_id IN NUMBER
421 ,p_seq_number IN NUMBER) IS
422 SELECT rlm.seq_number
423 ,rlm.user_name
424 ,rlm.groupbox_id
425 FROM ghr_routing_list_members rlm
426 WHERE rlm.routing_list_id = p_routing_list_id
427 AND rlm.seq_number > p_seq_number
428 ORDER BY rlm.seq_number asc;
429
430 BEGIN
431
432 l_broken :=p_broken; --NOCOPY Changes
433
434 -- Go and get the last routing list to be used
435 FOR cur_prh_last_rli_rec IN cur_prh_last_rli LOOP
436 p_routing_list_id := cur_prh_last_rli_rec.routing_list_id;
437 p_routing_list_name := cur_prh_last_rli_rec.name;
438
439 -- See if the routing list has been broken
440 FOR cur_prh_last_rec IN cur_prh_last LOOP
441 IF cur_prh_last_rec.pa_routing_history_id = cur_prh_last_rli_rec.pa_routing_history_id THEN
442 p_broken := FALSE;
443 ELSE
444 p_broken := TRUE;
445 END IF;
446 EXIT; -- Only want the first record therfore exit after we have got it
447 END LOOP;
448
449 -- If it is not broken then get the next sequence in the routing list
450 --
451 IF NOT p_broken THEN
452 FOR cur_rlm_rec IN cur_rlm(cur_prh_last_rli_rec.routing_list_id, cur_prh_last_rli_rec.routing_seq_number) LOOP
453 p_next_seq_number := cur_rlm_rec.seq_number;
454 p_next_user_name := cur_rlm_rec.user_name;
455 p_next_groupbox_id := cur_rlm_rec.groupbox_id;
456 --
457 -- When we get the first one exit
458 EXIT;
459 END LOOP;
460 END IF;
461
462 EXIT; -- Only want the first record therfore exit after we have got it
463 END LOOP;
464 EXCEPTION
465 -- Reset IN OUT parameters and set OUT parameters
466 WHEN others THEN
467 p_routing_list_id := NULL;
468 p_routing_list_name := NULL;
469 p_next_seq_number := NULL;
470 p_next_user_name := NULL;
471 p_next_groupbox_id := NULL;
472 p_broken :=l_broken;
473 RAISE;
474
475 END get_last_routing_list;
476
477 PROCEDURE get_roles (p_pa_request_id in number
478 ,p_routing_group_id in number
479 ,p_user_name in varchar2 default null
480 ,p_initiator_flag in out nocopy varchar2
481 ,p_requester_flag in out nocopy varchar2
482 ,p_authorizer_flag in out nocopy varchar2
483 ,p_personnelist_flag in out nocopy varchar2
484 ,p_approver_flag in out nocopy varchar2
485 ,p_reviewer_flag in out nocopy varchar2) IS
486
487 l_initiator_flag varchar2(150);
488 l_requester_flag varchar2(150);
489 l_authorizer_flag varchar2(150);
490 l_personnelist_flag varchar2(150);
491 l_approver_flag varchar2(150);
492 l_reviewer_flag varchar2(150);
493
494 l_groupbox_id ghr_pa_routing_history.groupbox_id%TYPE;
495 l_user_name ghr_pa_routing_history.user_name%TYPE;
496
497 CURSOR cur_gp_user IS
498 select prh.groupbox_id
499 ,prh.user_name
500 from ghr_pa_routing_history prh
501 where prh.pa_request_id = p_pa_request_id
502 order by prh.pa_routing_history_id desc;
503
504 CURSOR cur_first_user IS
505 select prh.groupbox_id
506 from ghr_pa_routing_history prh
507 where prh.pa_request_id = p_pa_request_id
508 and prh.user_name = l_user_name
509 and prh.groupbox_id is not NULL
510 and not exists (select 1
511 from ghr_pa_routing_history prh2
512 where prh2.pa_request_id = p_pa_request_id
513 and prh2.user_name <> l_user_name
514 and prh2.pa_routing_history_id > prh.pa_routing_history_id)
515 order by prh.pa_routing_history_id asc;
516
517 CURSOR cur_user_roles IS
518 select pei.pei_information4 initiator_flag
519 ,pei.pei_information5 requester_flag
520 ,pei.pei_information6 authorizer_flag
521 ,pei.pei_information7 personnelist_flag
522 ,pei.pei_information8 approver_flag
523 ,pei.pei_information9 reviewer_flag
524 from per_people_extra_info pei
525 ,fnd_user usr
526 where usr.user_name = l_user_name
527 and pei.person_id = usr.employee_id
528 and pei.information_type = 'GHR_US_PER_WF_ROUTING_GROUPS'
529 and pei.pei_information3 = p_routing_group_id;
530
531 CURSOR cur_gpbox_user_roles IS
532 select gru.initiator_flag
533 ,gru.requester_flag
534 ,gru.authorizer_flag
535 ,gru.personnelist_flag
536 ,gru.approver_flag
537 ,gru.reviewer_flag
538 from ghr_groupbox_users gru
539 where gru.groupbox_id = l_groupbox_id
540 and gru.user_name = p_user_name;
541
542 BEGIN
543
544 -- Initialisation for NOCOPY Changes
545 l_initiator_flag :=p_initiator_flag;
546 l_requester_flag :=p_requester_flag;
547 l_authorizer_flag :=p_authorizer_flag;
548 l_personnelist_flag :=p_personnelist_flag;
549 l_approver_flag :=p_approver_flag;
550 l_reviewer_flag :=p_reviewer_flag;
551
552 -- First get the last history record for given pa_request_id
553 FOR c_rec in cur_gp_user LOOP
554 l_groupbox_id := c_rec.groupbox_id;
555 l_user_name := c_rec.user_name;
556 EXIT;
557 END LOOP;
558
559 -- If it is for a group box then definitely use the group box roles and that is it!
560 IF l_groupbox_id is not null THEN
561 FOR C_rec in cur_gpbox_user_roles LOOP
562 p_initiator_flag := c_rec.initiator_flag;
563 p_requester_flag := c_rec.requester_flag;
564 p_authorizer_flag := c_rec.authorizer_flag;
565 p_personnelist_flag := c_rec.personnelist_flag;
566 p_approver_flag := c_rec.approver_flag;
567 p_reviewer_flag := c_rec.reviewer_flag;
568 EXIT;
569 END LOOP;
570
571 ELSE
572 -- otherwise still need to work out if we use the individual roles or it was initially
573 -- set to this user in a group box and they saved and held!
574 IF l_user_name is null THEN
575 l_user_name := p_user_name;
576 END IF;
577 FOR cur_first_user_rec in cur_first_user LOOP
578 l_groupbox_id := cur_first_user_rec.groupbox_id;
579 EXIT;
580 END LOOP;
581
582 -- Again if it is for a group box then definitely use the group box roles
583 -- Note: the l_groupbox_id will be null if the above cursor return no rows
584 IF l_groupbox_id is not null THEN
585 FOR C_rec in cur_gpbox_user_roles LOOP
586 p_initiator_flag := c_rec.initiator_flag;
587 p_requester_flag := c_rec.requester_flag;
588 p_authorizer_flag := c_rec.authorizer_flag;
589 p_personnelist_flag := c_rec.personnelist_flag;
590 p_approver_flag := c_rec.approver_flag;
591 p_reviewer_flag := c_rec.reviewer_flag;
592 EXIT;
593 END LOOP;
594 ELSE
595 -- definitely get the user roles
596 FOR c_rec in cur_user_roles LOOP
597 p_initiator_flag := c_rec.initiator_flag;
598 p_requester_flag := c_rec.requester_flag;
599 p_authorizer_flag := c_rec.authorizer_flag;
600 p_personnelist_flag := c_rec.personnelist_flag;
601 p_approver_flag := c_rec.approver_flag;
602 p_reviewer_flag := c_rec.reviewer_flag;
603 END LOOP;
604 END IF;
605 END IF;
606
607 EXCEPTION
608 -- Reset IN OUT parameters and set OUT parameters
609 WHEN others THEN
610 p_initiator_flag :=l_initiator_flag;
611 p_requester_flag :=l_requester_flag;
612 p_authorizer_flag :=l_authorizer_flag;
613 p_personnelist_flag :=l_personnelist_flag;
614 p_approver_flag :=l_approver_flag;
615 p_reviewer_flag :=l_reviewer_flag;
616 RAISE;
617
618 END get_roles;
619
620 PROCEDURE get_person_details (p_person_id IN per_people_f.person_id%TYPE
621 ,p_effective_date IN DATE
622 ,p_national_identifier IN OUT NOCOPY per_people_f.national_identifier%TYPE
623 ,p_date_of_birth IN OUT NOCOPY per_people_f.date_of_birth%TYPE
624 ,p_last_name IN OUT NOCOPY per_people_f.last_name%TYPE
625 ,p_first_name IN OUT NOCOPY per_people_f.first_name%TYPE
626 ,p_middle_names IN OUT NOCOPY per_people_f.middle_names%TYPE) IS
627
628 l_national_identifier per_people_f.national_identifier%TYPE;
629 l_date_of_birth per_people_f.date_of_birth%TYPE;
630 l_last_name per_people_f.last_name%TYPE;
631 l_first_name per_people_f.first_name%TYPE;
632 l_middle_names per_people_f.middle_names%TYPE;
633
634 CURSOR cur_per IS
635 SELECT per.national_identifier
636 ,per.date_of_birth
637 ,per.last_name
638 ,per.first_name
639 ,per.middle_names
640 FROM per_people_f per
641 WHERE per.person_id = p_person_id
642 AND NVL(p_effective_date,TRUNC(sysdate)) between per.effective_start_date and per.effective_end_date;
643
644 BEGIN
645 l_national_identifier :=p_national_identifier;
646 l_date_of_birth :=p_date_of_birth;
647 l_last_name :=p_last_name;
648 l_first_name :=p_first_name;
649 l_middle_names :=p_middle_names;
650
651 FOR cur_per_rec IN cur_per LOOP
652 p_national_identifier := cur_per_rec.national_identifier;
653 p_date_of_birth := cur_per_rec.date_of_birth;
654 p_last_name := cur_per_rec.last_name;
655 p_first_name := cur_per_rec.first_name;
656 p_middle_names := cur_per_rec.middle_names;
657 END LOOP;
658
659 EXCEPTION
660 -- Reset IN OUT parameters and set OUT parameters
661 WHEN others THEN
662 p_national_identifier :=l_national_identifier;
663 p_date_of_birth :=l_date_of_birth;
664 p_last_name :=l_last_name;
665 p_first_name :=l_first_name;
666 p_middle_names :=l_middle_names;
667 RAISE;
668
669 END get_person_details;
670
671 PROCEDURE get_duty_station_details (p_duty_station_id IN ghr_duty_stations_v.duty_station_id%TYPE
672 ,p_effective_date IN DATE
673 ,p_duty_station_code IN OUT NOCOPY ghr_duty_stations_v.duty_station_code%TYPE
674 ,p_duty_station_desc IN OUT NOCOPY ghr_duty_stations_v.duty_station_desc%TYPE) IS
675
676 l_duty_station_code ghr_duty_stations_v.duty_station_code%TYPE;
677 l_duty_station_desc ghr_duty_stations_v.duty_station_desc%TYPE;
678
679 CURSOR cur_dstv IS
680 SELECT dstv.duty_station_code
681 ,dstv.duty_station_desc
682 FROM ghr_duty_stations_v dstv
683 WHERE dstv.duty_station_id = p_duty_station_id
684 AND NVL(p_effective_date,TRUNC(sysdate)) between dstv.effective_start_date and dstv.effective_end_date;
685 --
686 BEGIN
687
688 --Initialisation for NOCOPY Changes
689
690 l_duty_station_code := p_duty_station_code;
691 l_duty_station_desc := p_duty_station_desc;
692
693 p_duty_station_code := NULL;
694 p_duty_station_desc := NULL;
695 FOR cur_dstv_rec IN cur_dstv LOOP
696 p_duty_station_code := cur_dstv_rec.duty_station_code;
697 p_duty_station_desc := cur_dstv_rec.duty_station_desc;
698 END LOOP;
699
700 EXCEPTION
701 -- Reset IN OUT parameters and set OUT parameters
702 WHEN others THEN
703 p_duty_station_code := l_duty_station_code;
704 p_duty_station_desc := l_duty_station_desc;
705 RAISE;
706
707 END get_duty_station_details;
708 --
709 --
710 PROCEDURE get_SF52_person_ddf_details (p_person_id IN per_people_f.person_id%TYPE
711 ,p_date_effective IN date default sysdate
712 ,p_citizenship OUT NOCOPY varchar2
713 ,p_veterans_preference OUT NOCOPY varchar2
714 ,p_veterans_pref_for_rif OUT NOCOPY varchar2
715 ,p_veterans_status OUT NOCOPY varchar2
716 ,p_scd_leave OUT NOCOPY varchar2) IS
717
718
719 -- Bug No 550117 Need seperate variable to store what is returned by the second
720 -- call to ghr_history_fetch.fetch_peopleei
721
722 l_per_ei_data per_people_extra_info%rowtype;
723 l_per_ei_scd_data per_people_extra_info%rowtype;
724
725 BEGIN
726
727 ghr_history_fetch.fetch_peopleei(
728 p_person_id => p_person_id,
729 p_information_type => 'GHR_US_PER_SF52',
730 p_date_effective => p_date_effective,
731 p_per_ei_data => l_per_ei_data);
732
733 if l_per_ei_data.person_extra_info_id is not null then
734 p_citizenship := l_per_ei_data.pei_information3;
735 p_veterans_preference := l_per_ei_data.pei_information4;
736 p_veterans_pref_for_rif := l_per_ei_data.pei_information5;
737 p_veterans_status := l_per_ei_data.pei_information6;
738 end if;
739
740 ghr_history_fetch.fetch_peopleei(
741 p_person_id => p_person_id,
742 p_information_type => 'GHR_US_PER_SCD_INFORMATION',
743 p_date_effective => p_date_effective,
744 p_per_ei_data => l_per_ei_scd_data);
745
746 if l_per_ei_scd_data.person_extra_info_id is not null then
747 p_scd_leave := l_per_ei_scd_data.pei_information3;
748 end if;
749
750
751 EXCEPTION
752 -- Reset IN OUT parameters and set OUT parameters
753 WHEN others THEN
754 p_citizenship :=NULL;
755 p_veterans_preference :=NULL;
756 p_veterans_pref_for_rif :=NULL;
757 p_veterans_status :=NULL;
758 p_scd_leave :=NULL;
759 RAISE;
760
761 END get_SF52_person_ddf_details;
762
763 -- vsm
764 PROCEDURE get_SF52_asg_ddf_details (p_assignment_id IN per_assignments_f.assignment_id%TYPE
765 ,p_date_effective IN date default sysdate
766 ,p_tenure OUT NOCOPY varchar2
767 ,p_annuitant_indicator OUT NOCOPY varchar2
768 ,p_pay_rate_determinant OUT NOCOPY varchar2
769 ,p_work_schedule OUT NOCOPY varchar2
770 ,p_part_time_hours OUT NOCOPY varchar2) IS
771
772 l_asgei_data per_assignment_extra_info%rowtype;
773
774 BEGIN
775 ghr_history_fetch.fetch_asgei (
776 p_assignment_id => p_assignment_id,
777 p_information_type => 'GHR_US_ASG_SF52',
778 p_date_effective => p_date_effective,
779 p_asg_ei_data => l_asgei_data) ;
780
781 if l_asgei_data.assignment_extra_info_id is not null then
782 p_tenure := l_asgei_data.aei_information4;
783 p_annuitant_indicator := l_asgei_data.aei_information5;
784
785 -- bit weird this but if it the PRD is stored as a 5 on the database when we retrieve it for
786 -- future use we retrieve a 6 and if it is a 7 we retrieve a 0!!
787 if l_asgei_data.aei_information6 = '5' then
788 p_pay_rate_determinant := '6';
789 elsif l_asgei_data.aei_information6 = '7' then
790 p_pay_rate_determinant := '0';
791 else
792 p_pay_rate_determinant := l_asgei_data.aei_information6;
793 end if;
794
795 p_work_schedule := l_asgei_data.aei_information7;
796 p_part_time_hours := l_asgei_data.aei_information8;
797
798 end if;
799
800 EXCEPTION
801 -- Reset IN OUT parameters and set OUT parameters
802 WHEN others THEN
803 p_tenure :=NULL;
804 p_annuitant_indicator :=NULL;
805 p_pay_rate_determinant :=NULL;
806 p_work_schedule :=NULL;
807 p_part_time_hours :=NULL;
808 RAISE;
809
810 END get_SF52_asg_ddf_details;
811
812 --vsm
813 PROCEDURE get_SF52_pos_ddf_details (p_position_id IN hr_all_positions_f.position_id%TYPE
814 ,p_date_Effective IN date default sysdate
815 ,p_flsa_category OUT NOCOPY varchar2
816 ,p_bargaining_unit_status OUT NOCOPY varchar2
817 ,p_work_schedule OUT NOCOPY varchar2
818 ,p_functional_class OUT NOCOPY varchar2
819 ,p_supervisory_status OUT NOCOPY varchar2
820 ,p_position_occupied OUT NOCOPY varchar2
821 ,p_appropriation_code1 OUT NOCOPY varchar2
822 ,p_appropriation_code2 OUT NOCOPY varchar2
823 ,p_personnel_office_id OUT NOCOPY varchar2
824 ,p_office_symbol OUT NOCOPY varchar2
825 ,p_part_time_hours OUT NOCOPY number) IS
826
827 l_pos_ei_grp1_data per_position_extra_info%rowtype;
828 l_pos_ei_grp2_data per_position_extra_info%rowtype;
829 --l_dummy_posei per_position_extra_info%rowtype;
830
831 BEGIN
832
833 ghr_history_fetch.fetch_positionei(
834 p_position_id => p_position_id,
835 p_information_type => 'GHR_US_POS_GRP1',
836 p_date_effective => p_date_effective,
837 p_pos_ei_data => l_pos_ei_grp1_data);
838
839 if l_pos_ei_grp1_data.position_extra_info_id is not null then
840 p_personnel_office_id := l_pos_ei_grp1_data.poei_information3;
841 p_office_symbol := l_pos_ei_grp1_data.poei_information4;
842 p_flsa_category := l_pos_ei_grp1_data.poei_information7;
843 p_bargaining_unit_status := l_pos_ei_grp1_data.poei_information8;
844 p_work_schedule := l_pos_ei_grp1_data.poei_information10;
845 p_functional_class := l_pos_ei_grp1_data.poei_information11;
846 p_supervisory_status := l_pos_ei_grp1_data.poei_information16;
847 p_part_time_hours := l_pos_ei_grp1_data.poei_information23;
848 end if;
849
850 ghr_history_fetch.fetch_positionei(
851 p_position_id => p_position_id,
852 p_information_type => 'GHR_US_POS_GRP2',
853 p_date_effective => p_date_effective,
854 p_pos_ei_data => l_pos_ei_grp2_data);
855
856 if l_pos_ei_grp2_data.position_extra_info_id is not null then
857 p_position_occupied := l_pos_ei_grp2_data.poei_information3;
858 p_appropriation_code1 := l_pos_ei_grp2_data.poei_information13;
859 p_appropriation_code2 := l_pos_ei_grp2_data.poei_information14;
860 end if;
861
862 EXCEPTION
863 -- Reset IN OUT parameters and set OUT parameters
864 WHEN others THEN
865 p_flsa_category :=NULL;
866 p_bargaining_unit_status :=NULL;
867 p_functional_class :=NULL;
868 p_work_schedule :=NULL;
869 p_part_time_hours :=NULL;
870 p_supervisory_status :=NULL;
871 p_position_occupied :=NULL;
872 p_appropriation_code1 :=NULL;
873 p_appropriation_code2 :=NULL;
874 p_personnel_office_id :=NULL;
875 p_office_symbol :=NULL;
876 RAISE;
877
878 END get_SF52_pos_ddf_details;
879
880
881 PROCEDURE get_SF52_loc_ddf_details (p_location_id IN hr_locations.location_id%TYPE
882 ,p_duty_station_id OUT NOCOPY varchar2) IS
883
884 CURSOR cur_lei IS
885 SELECT lei.lei_information3 duty_station_id
886 FROM hr_location_extra_info lei
887 WHERE lei.location_id = p_location_id
888 AND lei.information_type = 'GHR_US_LOC_INFORMATION';
889
890 BEGIN
891 FOR cur_lei_rec IN cur_lei LOOP
892 p_duty_station_id := cur_lei_rec.duty_station_id;
893 END LOOP;
894
895 EXCEPTION
896 -- Reset IN OUT parameters and set OUT parameters
897 WHEN others THEN
898 p_duty_station_id :=NULL;
899 RAISE;
900 END get_SF52_loc_ddf_details;
901
902
903 --vms
904 PROCEDURE get_address_details (p_person_id IN per_addresses.person_id%TYPE
905 ,p_effective_date IN DATE
906 ,p_address_line1 OUT NOCOPY per_addresses.address_line1%TYPE
907 ,p_address_line2 OUT NOCOPY per_addresses.address_line2%TYPE
908 ,p_address_line3 OUT NOCOPY per_addresses.address_line3%TYPE
909 ,p_town_or_city OUT NOCOPY per_addresses.town_or_city%TYPE
910 ,p_region_2 OUT NOCOPY per_addresses.region_2%TYPE
911 ,p_postal_code OUT NOCOPY per_addresses.postal_code%TYPE
912 ,p_country OUT NOCOPY per_addresses.country%TYPE
913 ,p_territory_short_name OUT NOCOPY varchar2) IS
914 CURSOR cur_adr IS
915 SELECT adr.address_line1
916 ,adr.address_line2
917 ,adr.address_line3
918 ,adr.town_or_city
919 ,adr.region_2
920 ,adr.postal_code
921 ,adr.country
922 ,ter.territory_short_name
923 FROM fnd_territories_vl ter
924 ,per_addresses adr
925 WHERE adr.person_id = p_person_id
926 AND adr.primary_flag = 'Y'
927 AND NVL(p_effective_date, TRUNC(sysdate))
928 BETWEEN adr.date_from AND NVL(adr.date_to,NVL(p_effective_date,TRUNC(sysdate)))
929 AND adr.country = ter.territory_code;
930 BEGIN
931
932 FOR cur_adr_rec IN cur_adr LOOP
933 p_address_line1 := cur_adr_rec.address_line1;
934 p_address_line2 := cur_adr_rec.address_line2;
935 p_address_line3 := cur_adr_rec.address_line3;
936 p_town_or_city := cur_adr_rec.town_or_city;
937 p_region_2 := cur_adr_rec.region_2;
938 p_postal_code := cur_adr_rec.postal_code;
939 p_country := cur_adr_rec.country;
940 p_territory_short_name := cur_adr_rec.territory_short_name;
941 END LOOP;
942
943 EXCEPTION
944 -- Reset IN OUT parameters and set OUT parameters
945 WHEN others THEN
946 p_address_line1 := NULL;
947 p_address_line2 := NULL;
948 p_address_line3 := NULL;
949 p_town_or_city := NULL;
950 p_region_2 := NULL;
951 p_postal_code := NULL;
952 p_country := NULL;
953 p_territory_short_name := NULL;
954 RAISE;
955
956 END get_address_details;
957
958 PROCEDURE get_SF52_to_data_elements
959 (p_position_id IN hr_all_positions_f.position_id%TYPE
960 ,p_effective_date IN date default sysdate
961 ,p_prd IN ghr_pa_requests.pay_rate_determinant%TYPE
962 ,p_grade_id IN OUT NOCOPY number
963 ,p_job_id IN OUT NOCOPY number
964 ,p_organization_id IN OUT NOCOPY number
965 ,p_location_id IN OUT NOCOPY number
966 ,p_pay_plan OUT NOCOPY varchar2
967 ,p_occ_code OUT NOCOPY varchar2
968 ,p_grade_or_level OUT NOCOPY varchar2
969 ,p_pay_basis OUT NOCOPY varchar2
970 ,p_position_org_line1 OUT NOCOPY varchar2
971 ,p_position_org_line2 OUT NOCOPY varchar2
972 ,p_position_org_line3 OUT NOCOPY varchar2
973 ,p_position_org_line4 OUT NOCOPY varchar2
974 ,p_position_org_line5 OUT NOCOPY varchar2
975 ,p_position_org_line6 OUT NOCOPY varchar2
976 ,p_duty_station_id OUT NOCOPY number
977 ) IS
978 --
979 l_business_group_id hr_all_positions_f.business_group_id%type;
980 l_pos_ei_grade_data per_position_extra_info%rowtype;
981 l_pos_ei_grp1_data per_position_extra_info%rowtype;
982
983 l_pos_organization_id hr_organization_information.organization_id%TYPE;
984 l_assignment_id per_all_assignments_f.assignment_id%type;
985 l_retained_grade ghr_pay_calc.retained_grade_rec_type;
986 l_person_id per_all_assignments_f.person_id%type;
987 l_prd VARCHAR2(30);
988 l_dummy VARCHAR2(30);
989
990 l_grade_id number(15);
991 l_job_id number(15);
992 l_organization_id number(15);
993 l_location_id number(15);
994 --
995 CURSOR cur_ass_id IS
996 SELECT assignment_id, person_id
997 FROM per_all_assignments_f
998 WHERE position_id = p_position_id
999 AND assignment_type <> 'B'
1000 AND primary_flag = 'Y'
1001 AND p_effective_date
1002 between effective_start_date and effective_end_date;
1003 --
1004 --
1005 CURSOR cur_pos_ids IS
1006 SELECT pos.job_id
1007 ,pos.business_group_id
1008 ,pos.organization_id
1009 ,pos.location_id
1010 FROM hr_all_positions_f pos -- Venkat -- Position DT
1011 WHERE pos.position_id = p_position_id
1012 and p_effective_date between pos.effective_start_date
1013 and pos.effective_end_date ;
1014 --
1015 CURSOR cur_grd IS
1016 SELECT gdf.segment1 pay_plan
1017 ,gdf.segment2 grade_or_level
1018 FROM per_grade_definitions gdf
1019 ,per_grades grd
1020 WHERE grd.grade_id = p_grade_id
1021 AND grd.grade_definition_id = gdf.grade_definition_id;
1022 --
1023 CURSOR cur_org (p_org_id number) IS
1024 SELECT oi.org_information5 position_org_line1
1025 ,oi.org_information6 position_org_line2
1026 ,oi.org_information7 position_org_line3
1027 ,oi.org_information8 position_org_line4
1028 ,oi.org_information9 position_org_line5
1029 ,oi.org_information10 position_org_line6
1030 FROM hr_organization_information oi
1031 WHERE oi.organization_id = p_org_id
1032 AND oi.org_information_context = 'GHR_US_ORG_REPORTING_INFO';
1033
1034 BEGIN
1035
1036 --Initialisation for NOCOPY Changes
1037
1038 l_grade_id := p_grade_id;
1039 l_job_id :=p_job_id;
1040 l_organization_id :=p_organization_id;
1041 l_location_id :=p_location_id;
1042
1043 -- First lets get all the id's from the position passed in
1044 -- Note since we are ther already may as well get the pay basis also
1045 --
1046 FOR cur_ass_id_rec IN cur_ass_id LOOP
1047 l_assignment_id := cur_ass_id_rec.assignment_id;
1048 l_person_id := cur_ass_id_rec.person_id;
1049 EXIT;
1050 END LOOP;
1051 --
1052
1053 IF l_assignment_id IS NOT NULL THEN
1054 ghr_pa_requests_pkg.get_SF52_asg_ddf_details
1055 (p_assignment_id => l_assignment_id
1056 ,p_date_effective => p_effective_date
1057 ,p_tenure => l_dummy
1058 ,p_annuitant_indicator => l_dummy
1059 ,p_pay_rate_determinant => l_prd
1060 ,p_work_schedule => l_dummy
1061 ,p_part_time_hours => l_dummy);
1062 END IF;
1063 if p_prd is not null then
1064 hr_utility.set_location('PRD BEF TO_DATA' || l_prd,1);
1065 l_prd := p_prd;
1066 hr_utility.set_location('PRD AFT TO_DATA' || l_prd,2);
1067 end if;
1068
1069 FOR cur_pos_ids_rec IN cur_pos_ids LOOP
1070 p_job_id := cur_pos_ids_rec.job_id;
1071 l_business_group_id := cur_pos_ids_rec.business_group_id;
1072 p_organization_id := cur_pos_ids_rec.organization_id;
1073 p_location_id := cur_pos_ids_rec.location_id;
1074 END LOOP;
1075 --
1076 -- Retive the Grade info and pay basis from the POI history table
1077 ghr_history_fetch.fetch_positionei(
1078 p_position_id => p_position_id,
1079 p_information_type => 'GHR_US_POS_VALID_GRADE',
1080 p_date_effective => p_effective_date,
1081 p_pos_ei_data => l_pos_ei_grade_data);
1082
1083 IF l_pos_ei_grade_data.position_extra_info_id IS NOT NULL THEN
1084 p_grade_id := l_pos_ei_grade_data.poei_information3;
1085 p_pay_basis := l_pos_ei_grade_data.poei_information6;
1086 ELSE
1087 p_grade_id := null;
1088 p_pay_basis := null;
1089 END IF;
1090
1091 IF l_person_id is not null then
1092 IF l_prd IN ('A','B','E','F','U','V') THEN
1093 hr_utility.set_location('l_prd is ' || l_prd,1);
1094 hr_utility.set_location('l_person_id is ' || to_char(l_person_id),1);
1095 hr_utility.set_location('p_position_id is ' || to_char(p_position_id),1);
1096 hr_utility.set_location('p_effective_date is ' || to_char(p_effective_date,'YYYY/MM/DD'),2);
1097 p_pay_basis := get_upd34_pay_basis (p_person_id => l_person_id
1098 ,p_position_id => p_position_id
1099 ,p_prd => l_prd
1100 ,p_effective_date => p_effective_date);
1101 END IF;
1102 END IF;
1103
1104 --
1105 -- OK lets now get pay plan and grade or level for the grade id just retrieved this is in the
1106 -- Grade Key Flexfield
1107 --
1108 IF p_grade_id IS NOT NULL THEN
1109 FOR cur_grd_rec IN cur_grd LOOP
1110 p_pay_plan := cur_grd_rec.pay_plan;
1111 p_grade_or_level := cur_grd_rec.grade_or_level;
1112 END LOOP;
1113 END IF;
1114 --
1115 -- Use function in ghr_api package to get the occ_code, otherwise known as the job occupational_series
1116 -- It is found in the Job KFF and the Oragnization DDF tells us which segemnt of the KFF it is in
1117 IF p_job_id IS NOT NULL AND l_business_group_id IS NOT NULL THEN
1118 p_occ_code := ghr_api.get_job_occ_series_job(p_job_id
1119 ,l_business_group_id);
1120 END IF;
1121 --
1122 -- Retrieve the location details
1123 --
1124 --
1125 -- Retive the Grade info and pay basis from the POI history table
1126 ghr_history_fetch.fetch_positionei(
1127 p_position_id => p_position_id,
1128 p_information_type => 'GHR_US_POS_GRP1',
1129 p_date_effective => p_effective_date,
1130 p_pos_ei_data => l_pos_ei_grp1_data);
1131
1132 IF l_pos_ei_grp1_data.position_extra_info_id IS NOT NULL THEN
1133 l_pos_organization_id := TO_NUMBER(l_pos_ei_grp1_data.poei_information21);
1134 ELSE
1135 l_pos_organization_id := null;
1136 END IF;
1137 --
1138 IF l_pos_organization_id IS NOT NULL THEN
1139 FOR cur_org_rec IN cur_org (l_pos_organization_id) LOOP
1140 p_position_org_line1 := cur_org_rec.position_org_line1;
1141 p_position_org_line2 := cur_org_rec.position_org_line2;
1142 p_position_org_line3 := cur_org_rec.position_org_line3;
1143 p_position_org_line4 := cur_org_rec.position_org_line4;
1144 p_position_org_line5 := cur_org_rec.position_org_line5;
1145 p_position_org_line6 := cur_org_rec.position_org_line6;
1146 END LOOP;
1147 ELSE
1148 p_position_org_line1 := NULL;
1149 p_position_org_line2 := NULL;
1150 p_position_org_line3 := NULL;
1151 p_position_org_line4 := NULL;
1152 p_position_org_line5 := NULL;
1153 p_position_org_line6 := NULL;
1154 END IF;
1155 --
1156 -- Use the procedure already written to get the duty station id for the given location_id
1157 --
1158 IF p_location_id IS NOT NULL THEN
1159 get_SF52_loc_ddf_details (p_location_id
1160 ,p_duty_station_id);
1161 END IF;
1162
1163 EXCEPTION
1164 -- Reset IN OUT parameters and set OUT parameters
1165 WHEN others THEN
1166 p_grade_id :=l_grade_id;
1167 p_job_id :=l_job_id;
1168 p_organization_id :=l_organization_id;
1169 p_location_id :=l_location_id;
1170 p_pay_basis := null;
1171 p_pay_plan := NULL;
1172 p_grade_or_level := NULL;
1173 p_duty_station_id :=NULL;
1174 p_occ_code :=NULL;
1175 p_position_org_line1 := NULL;
1176 p_position_org_line2 := NULL;
1177 p_position_org_line3 := NULL;
1178 p_position_org_line4 := NULL;
1179 p_position_org_line5 := NULL;
1180 p_position_org_line6 := NULL;
1181 RAISE;
1182 --
1183 END get_SF52_to_data_elements;
1184
1185 -- This procedure only really needs to be called for realignment. For this NOA the 6 'address' lines seen
1186 -- on the to side should come from the 'position organization' on the PAR extra info (if given)
1187 --
1188 PROCEDURE get_rei_org_lines (p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE
1189 ,p_organization_id IN OUT NOCOPY VARCHAR2
1190 ,p_position_org_line1 OUT NOCOPY varchar2
1191 ,p_position_org_line2 OUT NOCOPY varchar2
1192 ,p_position_org_line3 OUT NOCOPY varchar2
1193 ,p_position_org_line4 OUT NOCOPY varchar2
1194 ,p_position_org_line5 OUT NOCOPY varchar2
1195 ,p_position_org_line6 OUT NOCOPY varchar2) IS
1196
1197 l_organization_id VARCHAR2(150);
1198
1199 CURSOR cur_rei_org IS
1200 SELECT rei.rei_information8 org_id -- Bug 2681726 Changed information9 to 8 as we need to consider position's org
1201 FROM ghr_pa_request_extra_info rei
1202 WHERE pa_request_id = p_pa_request_id
1203 AND rei.information_type = 'GHR_US_PAR_REALIGNMENT';
1204
1205 CURSOR cur_org (p_org_id number) IS
1206 SELECT oi.org_information5 position_org_line1
1207 ,oi.org_information6 position_org_line2
1208 ,oi.org_information7 position_org_line3
1209 ,oi.org_information8 position_org_line4
1210 ,oi.org_information9 position_org_line5
1211 ,oi.org_information10 position_org_line6
1212 FROM hr_organization_information oi
1213 WHERE oi.organization_id = p_org_id
1214 AND oi.org_information_context = 'GHR_US_ORG_REPORTING_INFO';
1215
1216 --l_pos_org_id NUMBER;
1217
1218 BEGIN
1219
1220 l_organization_id :=p_organization_id; --NOCOPY Changes
1221
1222 FOR cur_rei_org_rec IN cur_rei_org LOOP
1223 p_organization_id := cur_rei_org_rec.org_id;
1224 END LOOP;
1225
1226 IF p_organization_id IS NOT NULL THEN
1227 FOR cur_org_rec IN cur_org(p_organization_id) LOOP
1228 p_position_org_line1 := cur_org_rec.position_org_line1;
1229 p_position_org_line2 := cur_org_rec.position_org_line2;
1230 p_position_org_line3 := cur_org_rec.position_org_line3;
1231 p_position_org_line4 := cur_org_rec.position_org_line4;
1232 p_position_org_line5 := cur_org_rec.position_org_line5;
1233 p_position_org_line6 := cur_org_rec.position_org_line6;
1234 END LOOP;
1235 END IF;
1236
1237 EXCEPTION
1238 -- Reset IN OUT parameters and set OUT parameters
1239 WHEN others THEN
1240 p_organization_id :=l_organization_id;
1241 p_position_org_line1 := NULL;
1242 p_position_org_line2 := NULL;
1243 p_position_org_line3 := NULL;
1244 p_position_org_line4 := NULL;
1245 p_position_org_line5 := NULL;
1246 p_position_org_line6 := NULL;
1247 RAISE;
1248
1249 END get_rei_org_lines;
1250
1251 FUNCTION segments_defined (p_flexfield_name IN VARCHAR2
1252 ,p_context_code IN VARCHAR2)
1253 RETURN BOOLEAN IS
1254 --
1255 CURSOR c_dfc IS
1256 SELECT 1
1257 FROM fnd_descr_flex_contexts dfc
1258 WHERE dfc.application_id = 8301
1259 AND dfc.descriptive_flexfield_name = p_flexfield_name
1260 AND dfc.descriptive_flex_context_code = p_context_code
1261 AND dfc.enabled_flag = 'Y'; --to avoid insertion prompts for diabled contexts 5766626
1262
1263 BEGIN
1264 FOR c_dfc_rec IN c_dfc LOOP
1265 RETURN (TRUE);
1266 END LOOP;
1267
1268 RETURN(FALSE);
1269
1270 END segments_defined;
1271
1272 FUNCTION get_noac_remark_req (p_first_noa_id IN ghr_noac_remarks.nature_of_action_id%TYPE
1273 ,p_second_noa_id IN ghr_noac_remarks.nature_of_action_id%TYPE
1274 ,p_remark_id IN ghr_noac_remarks.nature_of_action_id%TYPE
1275 ,p_effective_date IN DATE)
1276 RETURN VARCHAR2 IS
1277
1278 CURSOR c_ncr IS
1279 SELECT 1
1280 FROM ghr_noac_remarks ncr
1281 WHERE (ncr.nature_of_action_id = p_first_noa_id
1282 OR ncr.nature_of_action_id = p_second_noa_id)
1283 AND ncr.remark_id = p_remark_id
1284 AND ncr.required_flag = 'Y'
1285 AND NVL(p_effective_date,TRUNC(sysdate))
1286 BETWEEN ncr.date_from AND NVL(ncr.date_to,NVL(p_effective_date,TRUNC(sysdate)));
1287
1288 BEGIN
1289 -- We need to know if it is required for either the first noa OR the second
1290 FOR c_ncr_rec IN c_ncr LOOP
1291 -- If we got in here then the required flag must be set for at least one of the NOA's given
1292 RETURN ('Y');
1293 END LOOP;
1294
1295 RETURN('N');
1296
1297 END get_noac_remark_req;
1298
1299 FUNCTION get_user_person_id (p_user_name IN VARCHAR2)
1300 RETURN NUMBER IS
1301 --
1302 l_ret_val NUMBER(9) := NULL;
1303 --
1304 CURSOR c_use IS
1305 SELECT use.employee_id
1306 FROM fnd_user use
1307 WHERE use.user_name = p_user_name;
1308
1309 BEGIN
1310 FOR c_use_rec IN c_use LOOP
1311 l_ret_val := c_use_rec.employee_id;
1312 END LOOP;
1313
1314 RETURN(l_ret_val);
1315
1316 END get_user_person_id;
1317
1318 PROCEDURE get_single_noac_for_fam (p_noa_family_code IN ghr_noa_families.noa_family_code%TYPE
1319 ,p_effective_date IN DATE
1320 ,p_nature_of_action_id IN OUT NOCOPY ghr_nature_of_actions.nature_of_action_id%TYPE
1321 ,p_code IN OUT NOCOPY ghr_nature_of_actions.code%TYPE
1322 ,p_description IN OUT NOCOPY ghr_nature_of_actions.description%TYPE) IS
1323
1324
1325 l_nature_of_action_id ghr_nature_of_actions.nature_of_action_id%TYPE;
1326 l_code ghr_nature_of_actions.code%TYPE;
1327 l_description ghr_nature_of_actions.description%TYPE;
1328
1329 l_record_found BOOLEAN := FALSE;
1330
1331 CURSOR cur_noa IS
1332 SELECT noa.nature_of_action_id
1333 ,noa.code
1334 ,noa.description
1335 FROM ghr_nature_of_actions noa
1336 ,ghr_noa_families naf
1337 WHERE naf.noa_family_code = p_noa_family_code
1338 AND naf.nature_of_action_id = noa.nature_of_action_id
1339 AND naf.enabled_flag = 'Y'
1340 AND NVL(p_effective_date,trunc(sysdate))
1341 BETWEEN NVL(naf.start_date_active,NVL(p_effective_date,trunc(sysdate)))
1342 AND NVL(naf.end_date_active,NVL(p_effective_date,trunc(sysdate))) ;
1343
1344 BEGIN
1345
1346 --Initialisation for NOCOPY Changes
1347
1348 l_nature_of_action_id := p_nature_of_action_id;
1349 l_code := p_code;
1350 l_description := p_description;
1351
1352 FOR cur_noa_rec IN cur_noa LOOP
1353 IF l_record_found THEN
1354 p_nature_of_action_id := null;
1355 p_code := null;
1356 p_description := null;
1357 EXIT;
1358 ELSE
1359 l_record_found := TRUE;
1360 p_nature_of_action_id := cur_noa_rec.nature_of_action_id;
1361 p_code := cur_noa_rec.code;
1362 p_description := cur_noa_rec.description;
1363 END IF;
1364 END LOOP;
1365
1366 EXCEPTION
1367 -- Reset IN OUT parameters and set OUT parameters
1368 WHEN others THEN
1369 p_nature_of_action_id := l_nature_of_action_id;
1370 p_code := l_code;
1371 p_description := l_description;
1372 RAISE;
1373
1374 END get_single_noac_for_fam;
1375
1376
1377 -- This procedure will return the Legal Authority Code and Description if there is only one for the given
1378 -- NOAC, otherwise it returns null
1379 PROCEDURE get_single_lac_for_noac (p_nature_of_action_id IN ghr_noac_las.nature_of_action_id%TYPE
1380 ,p_effective_date IN DATE
1381 ,p_lac_code IN OUT NOCOPY ghr_noac_las.lac_lookup_code%TYPE
1382 ,p_description IN OUT NOCOPY VARCHAR2) IS
1383
1384 l_lac_code ghr_noac_las.lac_lookup_code%TYPE;
1385 l_description VARCHAR2(240);
1386 --
1387 l_record_found BOOLEAN := FALSE;
1388
1389 CURSOR cur_nla IS
1390 SELECT hrl.lookup_code
1391 ,hrl.description
1392 FROM hr_lookups hrl
1393 ,ghr_noac_las nla
1394 WHERE nla.nature_of_action_id = p_nature_of_action_id
1395 AND nla.enabled_flag = 'Y'
1396 AND nla.valid_first_lac_flag = 'Y'
1397 AND NVL(p_effective_date,trunc(sysdate))
1398 BETWEEN nla.date_from
1399 AND NVL(nla.date_to,NVL(p_effective_date,trunc(sysdate)))
1400 AND hrl.lookup_code = nla.lac_lookup_code
1401 AND hrl.lookup_type = 'GHR_US_LEGAL_AUTHORITY'
1402 AND hrl.enabled_flag = 'Y'
1403 AND NVL(p_effective_date,trunc(sysdate))
1404 BETWEEN NVL(hrl.start_date_active,NVL(p_effective_date,trunc(sysdate)))
1405 AND NVL(hrl.end_date_active,NVL(p_effective_date,trunc(sysdate)));
1406
1407 BEGIN
1408
1409 --Initialisation for NOCOPY Changes
1410 l_lac_code := p_lac_code;
1411 l_description := p_description;
1412
1413 p_lac_code := null;
1414 p_description := null;
1415 --
1416 FOR cur_nla_rec IN cur_nla LOOP
1417 IF l_record_found THEN
1418 p_lac_code := null;
1419 p_description := null;
1420 EXIT;
1421 ELSE
1422 l_record_found := TRUE;
1423 p_lac_code := cur_nla_rec.lookup_code;
1424 p_description := cur_nla_rec.description;
1425 END IF;
1426 END LOOP;
1427
1428 EXCEPTION
1429 -- Reset IN OUT parameters and set OUT parameters
1430 WHEN others THEN
1431 p_lac_code := l_lac_code;
1432 p_description := l_description;
1433 RAISE;
1434
1435 END get_single_lac_for_noac;
1436 --
1437 --
1438 FUNCTION get_restricted_form (p_person_id IN NUMBER)
1439 RETURN VARCHAR2 IS
1440 --
1441 l_ret_val VARCHAR2(30) := NULL;
1442 --
1443 CURSOR c_pei IS
1444 SELECT pei.pei_information3 restricted_form
1445 FROM per_people_extra_info pei
1446 WHERE pei.information_type = 'GHR_US_PER_USER_INFO'
1447 AND pei.person_id = p_person_id;
1448
1449 BEGIN
1450 FOR c_pei_rec IN c_pei LOOP
1451 l_ret_val := c_pei_rec.restricted_form;
1452 END LOOP;
1453
1454 RETURN(l_ret_val);
1455
1456 END get_restricted_form;
1457
1458 --
1459 FUNCTION get_noa_pm_family (p_nature_of_action_id IN ghr_noa_families.nature_of_action_id%TYPE)
1460 RETURN VARCHAR2 IS
1461 --
1462 l_ret_val VARCHAR2(30) := NULL;
1463 --
1464 CURSOR c_naf IS
1465 SELECT naf.noa_family_code
1466 FROM ghr_families fam
1467 ,ghr_noa_families naf
1468 WHERE fam.noa_family_code = naf.noa_family_code
1469 AND naf.nature_of_action_id = p_nature_of_action_id
1470 AND fam.proc_method_flag = 'Y';
1471
1472 BEGIN
1473 FOR c_naf_rec IN c_naf LOOP
1474 l_ret_val := c_naf_rec.noa_family_code;
1475 END LOOP;
1476
1477 RETURN(l_ret_val);
1478
1479 END get_noa_pm_family;
1480
1481 --
1482 --
1483 -- Bug#3941541 Overloaded function with effective date as another parameter
1484 FUNCTION get_noa_pm_family (p_nature_of_action_id IN ghr_noa_families.nature_of_action_id%TYPE,
1485 p_effective_date IN DATE)
1486 RETURN VARCHAR2 IS
1487 --
1488 l_ret_val VARCHAR2(30) := NULL;
1489 --
1490 CURSOR c_naf IS
1491 SELECT naf.noa_family_code
1492 FROM ghr_families fam
1493 ,ghr_noa_families naf
1494 WHERE fam.noa_family_code = naf.noa_family_code
1495 AND naf.nature_of_action_id = p_nature_of_action_id
1496 AND fam.proc_method_flag = 'Y'
1497 AND p_effective_date between NVL(naf.start_date_active,p_effective_date)
1498 and NVL(naf.end_date_active,p_effective_date);
1499
1500
1501 BEGIN
1502 FOR c_naf_rec IN c_naf LOOP
1503 l_ret_val := c_naf_rec.noa_family_code;
1504 END LOOP;
1505
1506 RETURN(l_ret_val);
1507
1508 END get_noa_pm_family;
1509 --
1510
1511 -- As above except pass in a noa code and it returns the family it is in
1512 FUNCTION get_noa_pm_family (p_noa_code IN ghr_nature_of_actions.code%TYPE)
1513 RETURN VARCHAR2 IS
1514 --
1515 l_ret_val VARCHAR2(30) := NULL;
1516 --
1517 CURSOR c_naf IS
1518 SELECT naf.noa_family_code
1519 FROM ghr_families fam
1520 ,ghr_noa_families naf
1521 ,ghr_nature_of_actions noa
1522 WHERE fam.noa_family_code = naf.noa_family_code
1523 AND naf.nature_of_action_id = noa.nature_of_action_id
1524 AND noa.code = p_noa_code
1525 AND fam.proc_method_flag = 'Y';
1526
1527 BEGIN
1528 FOR c_naf_rec IN c_naf LOOP
1529 l_ret_val := c_naf_rec.noa_family_code;
1530 END LOOP;
1531
1532 RETURN(l_ret_val);
1533
1534 END get_noa_pm_family;
1535 --
1536 -- Given a position_id and a date check to see if anybody has been assigned
1537 -- that position at the date and return 'TRUE' if they have
1538 FUNCTION position_assigned(p_position_id IN NUMBER
1539 ,p_effective_date IN DATE)
1540 RETURN VARCHAR2 IS
1541 --
1542 l_ret_val VARCHAR2(5) := 'FALSE';
1543 --
1544 CURSOR c_asg IS
1545 SELECT 1
1546 FROM per_all_assignments_f asg
1547 WHERE asg.position_id = p_position_id
1548 AND NVL(p_effective_date,TRUNC(sysdate))
1549 BETWEEN asg.effective_start_date AND asg.effective_end_date
1550 AND asg.assignment_type NOT IN ('A', 'B');
1551
1552 BEGIN
1553 FOR c_asg_rec IN c_asg LOOP
1554 l_ret_val := 'TRUE';
1555 END LOOP;
1556
1557 RETURN(l_ret_val);
1558
1559 END position_assigned;
1560
1561 -- This function looks at the AOL table FND_CONCURRENT_PROGRAMS to return the defualt printer for the
1562 -- given concurrent program , Doesn't pass in application ID as 8301 is assumed
1563 FUNCTION get_default_printer (p_concurrent_program_name IN VARCHAR2)
1564 RETURN VARCHAR2 IS
1565 --
1566 CURSOR c_cop IS
1567 SELECT cop.printer_name
1568 FROM fnd_concurrent_programs cop
1569 WHERE cop.application_id = 8301
1570 AND cop.concurrent_program_name = p_concurrent_program_name;
1571 --
1572 --Note: There is a uinque index on application id and concurrent_program_name
1573 --
1574 BEGIN
1575 FOR c_cop_rec IN c_cop LOOP
1576 RETURN (c_cop_rec.printer_name);
1577 END LOOP;
1578 RETURN(NULL);
1579 END get_default_printer;
1580
1581 -- This function returns TRUE if the PA Request passed in has an SF50 produced
1582 FUNCTION SF50_produced (p_pa_request_id IN NUMBER)
1583 RETURN BOOLEAN IS
1584 --
1585 CURSOR c_par IS
1586 SELECT 1
1587 FROM ghr_pa_requests par
1588 WHERE par.pa_request_id = p_pa_request_id
1589 AND par.pa_notification_id IS NOT NULL;
1590 --
1591 BEGIN
1592 FOR c_par_rec IN c_par LOOP
1593 RETURN (TRUE);
1594 END LOOP;
1595 RETURN (FALSE);
1596 END SF50_produced;
1597 --
1598 -- This function returns TRUE if the person id passed in is valid for the given date
1599 -- The noa_family_code determines what is a valid person on the SF52, i.e for APP
1600 -- family they must be Applicant otherwise they must be Employees.
1601 -- The select statements need to be the same as on the SF52 as this is only
1602 -- checking the person is still valid in case the user alters the effective
1603 -- date after they used the LOV in the form to pick up a person!
1604 FUNCTION check_person_id_SF52 (p_person_id IN NUMBER
1605 ,p_effective_date IN DATE
1606 ,p_business_group_id IN NUMBER
1607 ,p_user_person_id IN NUMBER
1608 ,p_noa_family_code IN VARCHAR2
1609 ,p_second_noa_family_code IN VARCHAR2)
1610 RETURN BOOLEAN IS
1611 --
1612 --Bug# 6711759 Included the person type EX_EMP_APL
1613 CURSOR c_per_app IS
1614 SELECT 1
1615 FROM per_person_types pet
1616 ,per_people_f per
1617 WHERE nvl(p_effective_date,trunc(sysdate)) between per.effective_start_date and per.effective_end_date
1618 AND pet.person_type_id = per.person_type_id
1619 AND pet.system_person_type in ('APL','EX_EMP','EX_EMP_APL')
1620 AND per.business_group_id = p_business_group_id
1621 AND per.person_id <> p_user_person_id
1622 AND per.person_id = p_person_id;
1623 --
1624 -- Bug 4217510/ 4377361 added person type EMP_APL also
1625 CURSOR c_per_emp IS
1626 SELECT 1
1627 FROM per_person_types pet
1628 ,per_people_f per
1629 WHERE nvl(p_effective_date,trunc(sysdate)) between per.effective_start_date and per.effective_end_date
1630 AND pet.person_type_id = per.person_type_id
1631 AND (pet.system_person_type in ('EMP', 'EMP_APL')
1632 OR (pet.system_person_type = 'EX_EMP' and p_noa_family_code = 'CONV_APP')
1633 )
1634 AND per.business_group_id = p_business_group_id
1635 AND per.person_id <> p_user_person_id
1636 AND per.person_id = p_person_id;
1637 --
1638 BEGIN
1639 -- For cancel and correction families do not need to do the check so just return true
1640 IF p_noa_family_code IN ('CANCEL', 'CORRECT') THEN
1641 RETURN (TRUE);
1642 END IF;
1643
1644 IF p_noa_family_code = 'APP' THEN
1645 FOR c_per_app_rec IN c_per_app LOOP
1646 RETURN (TRUE);
1647 END LOOP;
1648 ELSE
1649 FOR c_per_emp_rec IN c_per_emp LOOP
1650 RETURN (TRUE);
1651 END LOOP;
1652 END IF;
1653 RETURN (FALSE);
1654
1655 END check_person_id_SF52;
1656 --
1657 FUNCTION check_valid_person_id (p_person_id IN NUMBER
1658 ,p_effective_date IN DATE
1659 ,p_business_group_id IN NUMBER
1660 ,p_user_person_id IN NUMBER
1661 ,p_noa_family_code IN VARCHAR2
1662 ,p_second_noa_family_code IN VARCHAR2)
1663 RETURN VARCHAR2 IS
1664 --
1665 l_proc varchar2(72) := 'check_valid_person_id';
1666 l_ret_val VARCHAR2(5) := 'FALSE';
1667 --
1668 CURSOR c_per_app IS
1669 SELECT 1
1670 FROM per_person_types pet
1671 ,per_people_f per
1672 WHERE nvl(p_effective_date,trunc(sysdate)) between per.effective_start_date and per.effective_end_date
1673 AND pet.person_type_id = per.person_type_id
1674 AND pet.system_person_type in ('APL','EX_EMP')
1675 AND per.business_group_id = p_business_group_id
1676 AND per.person_id <> p_user_person_id
1677 AND per.person_id = p_person_id;
1678 --
1679 -- Bug 4217510/4377361 added person type EMP_APL also
1680 CURSOR c_per_emp IS
1681 SELECT 1
1682 FROM per_person_types pet
1683 ,per_people_f per
1684 WHERE nvl(p_effective_date,trunc(sysdate)) between per.effective_start_date and per.effective_end_date
1685 AND pet.person_type_id = per.person_type_id
1686 AND (pet.system_person_type in ('EMP', 'EMP_APL')
1687 OR (pet.system_person_type = 'EX_EMP' and p_noa_family_code = 'CONV_APP')
1688 )
1689 AND per.business_group_id = p_business_group_id
1690 AND per.person_id <> p_user_person_id
1691 AND per.person_id = p_person_id;
1692 --
1693 BEGIN
1694 hr_utility.set_location('Entering ' || l_proc,5);
1695 -- For cancel and correction families do not need to do the check so just return true
1696 IF p_noa_family_code IN ('CANCEL', 'CORRECT') THEN
1697 l_ret_val := 'TRUE';
1698 hr_utility.set_location('Valid person for CORRECT or CANCEL ' ,6);
1699 END IF;
1700
1701 IF p_noa_family_code = 'APP' THEN
1702 --
1703
1704 hr_utility.set_location( ' Input parameters for check person ',7);
1705 hr_utility.set_location( ' p_person_id = '|| to_char(p_person_id),7);
1706 hr_utility.set_location( ' p_effective_date = '|| to_char(p_effective_date,'DD-MON-YYYY'),7);
1707 hr_utility.set_location( ' p_business_group_id = '|| to_char(p_business_group_id),7);
1708 hr_utility.set_location( ' p_user_person_id = '|| to_char(p_user_person_id),7);
1709 hr_utility.set_location( ' p_noa_family_code = '|| p_noa_family_code,7);
1710 hr_utility.set_location( ' p_second_noa_family_code = '|| p_second_noa_family_code,7);
1711 --
1712 FOR c_per_app_rec IN c_per_app LOOP
1713 l_ret_val := 'TRUE';
1714 hr_utility.set_location('Valid person from c_per_app ' ,8);
1715 END LOOP;
1716 ELSE
1717 FOR c_per_emp_rec IN c_per_emp LOOP
1718 l_ret_val := 'TRUE';
1719 hr_utility.set_location('Valid person from c_per_emp ' ,8);
1720 END LOOP;
1721 END IF;
1722 if l_ret_val = 'FALSE' then
1723 hr_utility.set_location('Invalid person ' ,9);
1724 end if;
1725 hr_utility.set_location('Leaving ' || l_proc,10);
1726 RETURN(l_ret_val);
1727
1728 END check_valid_person_id;
1729 --
1730
1731 PROCEDURE get_corr_other_pay(p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE
1732 ,p_noa_code IN ghr_nature_of_actions.code%TYPE
1733 ,p_to_basic_pay OUT NOCOPY NUMBER
1734 ,p_to_adj_basic_pay OUT NOCOPY NUMBER
1735 ,p_to_auo_ppi OUT NOCOPY VARCHAR2
1736 ,p_to_auo OUT NOCOPY NUMBER
1737 ,p_to_ap_ppi OUT NOCOPY VARCHAR2
1738 ,p_to_ap OUT NOCOPY NUMBER
1739 ,p_to_retention_allowance OUT NOCOPY NUMBER
1740 ,p_to_supervisory_differential OUT NOCOPY NUMBER
1741 ,p_to_staffing_differential OUT NOCOPY NUMBER
1742 ,p_to_pay_basis OUT NOCOPY VARCHAR2
1743 -- Corr Warn
1744 ,p_pay_rate_determinant OUT NOCOPY VARCHAR2
1745 ,p_pay_plan OUT NOCOPY VARCHAR2
1746 ,p_to_position_id OUT NOCOPY NUMBER
1747 ,p_person_id OUT NOCOPY NUMBER
1748 ,p_locality_adj OUT NOCOPY NUMBER
1749 -- Corr Warn
1750 ) IS
1751 --
1752 --- ghr_pay_caps.do_pay_caps_main
1753 --- (p_effective_date => l_effective_date
1754 --- ,p_pay_rate_determinant => :par.pay_rate_determinant --
1755 --- ,p_pay_plan => :par.to_pay_plan --
1756 --- ,p_to_position_id => :par.to_position_id --
1757 --- ,p_pay_basis => :par.to_pay_basis
1758 --- ,p_person_id => :par.person_id --
1759 --- ,p_basic_pay => :par.to_basic_pay
1760 --- ,p_locality_adj => :par.to_locality_adj --
1761 --- ,p_adj_basic_pay => :par.to_adj_basic_pay
1762 --- ,p_total_salary => :par.to_total_salary
1763 --- ,p_other_pay_amount => :par.to_other_pay_amount
1764 --- ,p_au_overtime => :par.to_au_overtime
1765 --- ,p_availability_pay => :par.to_availability_pay
1766 --- ,p_open_pay_fields => l_open_pay_fields_caps
1767 --- ,p_message_set => l_message_set_caps);
1768 ---
1769 ---
1770 l_ghr_pa_request_rec ghr_pa_requests%ROWTYPE;
1771 BEGIN
1772 ghr_corr_canc_SF52.build_corrected_SF52(p_pa_request_id => p_pa_request_id
1773 ,p_noa_code_correct => p_noa_code
1774 ,p_sf52_data_result => l_ghr_pa_request_rec);
1775 --
1776 p_to_basic_pay := l_ghr_pa_request_rec.to_basic_pay;
1777 p_to_adj_basic_pay := l_ghr_pa_request_rec.to_adj_basic_pay;
1778 p_to_auo_ppi := l_ghr_pa_request_rec.to_auo_premium_pay_indicator;
1779 p_to_auo := l_ghr_pa_request_rec.to_au_overtime;
1780 p_to_ap_ppi := l_ghr_pa_request_rec.to_ap_premium_pay_indicator;
1781 p_to_ap := l_ghr_pa_request_rec.to_availability_pay;
1782 p_to_retention_allowance := l_ghr_pa_request_rec.to_retention_allowance;
1783 p_to_supervisory_differential := l_ghr_pa_request_rec.to_supervisory_differential;
1784 p_to_staffing_differential := l_ghr_pa_request_rec.to_staffing_differential;
1785 p_to_pay_basis := l_ghr_pa_request_rec.to_pay_basis;
1786 -- Corr Warn
1787 p_pay_rate_determinant := l_ghr_pa_request_rec.pay_rate_determinant;
1788 p_pay_plan := l_ghr_pa_request_rec.to_pay_plan;
1789 p_to_position_id := l_ghr_pa_request_rec.to_position_id;
1790 p_person_id := l_ghr_pa_request_rec.person_id;
1791 p_locality_adj := l_ghr_pa_request_rec.to_locality_adj;
1792 -- Corr Warn
1793 EXCEPTION
1794
1795 -- Reset IN OUT parameters and set OUT parameters
1796
1797 WHEN others THEN
1798 p_to_basic_pay := NULL;
1799 p_to_adj_basic_pay := NULL;
1800 p_to_auo_ppi := NULL;
1801 p_to_auo := NULL;
1802 p_to_ap_ppi := NULL;
1803 p_to_ap := NULL;
1804 p_to_retention_allowance := NULL;
1805 p_to_supervisory_differential := NULL;
1806 p_to_staffing_differential := NULL;
1807 p_to_pay_basis := NULL;
1808 p_pay_rate_determinant := NULL;
1809 p_pay_plan := NULL;
1810 p_to_position_id := NULL;
1811 p_person_id := NULL;
1812 p_locality_adj := NULL;
1813 RAISE;
1814
1815
1816 END get_corr_other_pay;
1817
1818 PROCEDURE get_corr_rpa_other_pay(p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE
1819 ,p_noa_code IN ghr_nature_of_actions.code%TYPE
1820 ,p_from_basic_pay OUT NOCOPY NUMBER
1821 ,p_to_basic_pay OUT NOCOPY NUMBER
1822 ,p_to_adj_basic_pay OUT NOCOPY NUMBER
1823 ,p_to_auo_ppi OUT NOCOPY VARCHAR2
1824 ,p_to_auo OUT NOCOPY NUMBER
1825 ,p_to_ap_ppi OUT NOCOPY VARCHAR2
1826 ,p_to_ap OUT NOCOPY NUMBER
1827 ,p_to_retention_allowance OUT NOCOPY NUMBER
1828 ,p_to_supervisory_differential OUT NOCOPY NUMBER
1829 ,p_to_staffing_differential OUT NOCOPY NUMBER
1830 ,p_to_pay_basis OUT NOCOPY VARCHAR2
1831 -- Corr Warn
1832 ,p_pay_rate_determinant OUT NOCOPY VARCHAR2
1833 ,p_pay_plan OUT NOCOPY VARCHAR2
1834 ,p_to_position_id OUT NOCOPY NUMBER
1835 ,p_person_id OUT NOCOPY NUMBER
1836 ,p_locality_adj OUT NOCOPY NUMBER
1837 ,p_from_step_or_rate OUT NOCOPY VARCHAR2
1838 ,p_to_step_or_rate OUT NOCOPY VARCHAR2
1839 -- Corr Warn
1840 ) IS
1841 l_ghr_pa_request_rec ghr_pa_requests%ROWTYPE;
1842 BEGIN
1843 ghr_corr_canc_SF52.build_corrected_SF52(p_pa_request_id => p_pa_request_id
1844 ,p_noa_code_correct => p_noa_code
1845 ,p_sf52_data_result => l_ghr_pa_request_rec);
1846 --
1847 p_from_basic_pay := l_ghr_pa_request_rec.from_basic_pay;
1848 p_to_basic_pay := l_ghr_pa_request_rec.to_basic_pay;
1849 p_to_adj_basic_pay := l_ghr_pa_request_rec.to_adj_basic_pay;
1850 p_to_auo_ppi := l_ghr_pa_request_rec.to_auo_premium_pay_indicator;
1851 p_to_auo := l_ghr_pa_request_rec.to_au_overtime;
1852 p_to_ap_ppi := l_ghr_pa_request_rec.to_ap_premium_pay_indicator;
1853 p_to_ap := l_ghr_pa_request_rec.to_availability_pay;
1854 p_to_retention_allowance := l_ghr_pa_request_rec.to_retention_allowance;
1855 p_to_supervisory_differential := l_ghr_pa_request_rec.to_supervisory_differential;
1856 p_to_staffing_differential := l_ghr_pa_request_rec.to_staffing_differential;
1857 p_to_pay_basis := l_ghr_pa_request_rec.to_pay_basis;
1858 -- Corr Warn
1859 p_pay_rate_determinant := l_ghr_pa_request_rec.pay_rate_determinant;
1860 p_pay_plan := l_ghr_pa_request_rec.to_pay_plan;
1861 p_to_position_id := l_ghr_pa_request_rec.to_position_id;
1862 p_person_id := l_ghr_pa_request_rec.person_id;
1863 p_locality_adj := l_ghr_pa_request_rec.to_locality_adj;
1864 p_from_step_or_rate := l_ghr_pa_request_rec.from_step_or_rate;
1865 p_to_step_or_rate := l_ghr_pa_request_rec.to_step_or_rate;
1866 -- Corr Warn
1867 EXCEPTION
1868
1869 -- Reset IN OUT parameters and set OUT parameters
1870 WHEN others THEN
1871 p_from_basic_pay := NULL;
1872 p_to_basic_pay := NULL;
1873 p_to_adj_basic_pay := NULL;
1874 p_to_auo_ppi := NULL;
1875 p_to_auo := NULL;
1876 p_to_ap_ppi := NULL;
1877 p_to_ap := NULL;
1878 p_to_retention_allowance := NULL;
1879 p_to_supervisory_differential := NULL;
1880 p_to_staffing_differential := NULL;
1881 p_to_pay_basis := NULL;
1882 p_pay_rate_determinant := NULL;
1883 p_pay_plan := NULL;
1884 p_to_position_id := NULL;
1885 p_person_id := NULL;
1886 p_locality_adj := NULL;
1887 p_from_step_or_rate := NULL;
1888 p_to_step_or_rate := NULL;
1889 RAISE;
1890
1891 END get_corr_rpa_other_pay;
1892
1893 --
1894 -- This procedure gets the amounts that are not displayed in a correction form that
1895 -- are needed to do an award
1896 PROCEDURE get_corr_award (p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE
1897 ,p_noa_code IN ghr_nature_of_actions.code%TYPE
1898 ,p_from_basic_pay OUT NOCOPY NUMBER
1899 ,p_from_pay_basis OUT NOCOPY VARCHAR2
1900 ) IS
1901 --
1902 l_ghr_pa_request_rec ghr_pa_requests%ROWTYPE;
1903 BEGIN
1904 ghr_corr_canc_SF52.build_corrected_SF52(p_pa_request_id => p_pa_request_id
1905 ,p_noa_code_correct => p_noa_code
1906 ,p_sf52_data_result => l_ghr_pa_request_rec);
1907 --
1908 p_from_basic_pay := l_ghr_pa_request_rec.from_basic_pay;
1909 p_from_pay_basis := l_ghr_pa_request_rec.from_pay_basis;
1910
1911 EXCEPTION
1912
1913 -- Reset IN OUT parameters and set OUT parameters
1914
1915 WHEN others THEN
1916 p_from_basic_pay := NULL;
1917 p_from_pay_basis := NULL;
1918 RAISE;
1919
1920 END get_corr_award;
1921 --
1922 FUNCTION get_position_work_title(p_position_id in number,
1923 p_effective_date in date default trunc(sysdate)
1924 ) RETURN varchar2 IS
1925
1926
1927 l_proc varchar2(72) := 'get_position_work_title';
1928 l_pos_ei_data per_position_extra_info%rowtype;
1929 l_title varchar2(150);
1930
1931
1932 BEGIN
1933
1934 hr_utility.set_location('Entering ' || l_proc,5);
1935
1936 ghr_history_fetch.fetch_positionei
1937 (p_position_id => p_position_id ,
1938 p_information_type => 'GHR_US_POS_GRP1' ,
1939 p_date_effective => p_effective_date ,
1940 p_pos_ei_data => l_pos_ei_data
1941 );
1942 l_title := l_pos_ei_data.poei_information12;
1943
1944 return(l_title);
1945
1946 hr_utility.set_location('Leaving ' || l_proc,25);
1947 End get_position_work_title;
1948
1949
1950 FUNCTION get_position_work_title(p_person_id in varchar2,
1951 p_effective_date in date default trunc(sysdate)
1952 ) RETURN varchar2 IS
1953
1954
1955 l_proc varchar2(72) := 'get_position_work_title';
1956 l_position_id hr_all_positions_f.position_id%type;
1957 l_pos_ei_data per_position_extra_info%rowtype;
1958 l_title varchar2(150);
1959
1960 CURSOR c_per_pos is
1961 SELECT asg.position_id
1962 FROM Per_assignments_f asg
1963 WHERE asg.person_id = p_person_id
1964 AND trunc(nvl(p_effective_date,sysdate))
1965 between asg.effective_start_date and asg.effective_end_date
1966 AND asg.assignment_type <> 'B'
1967 AND asg.primary_flag = 'Y';
1968
1969 BEGIN
1970
1971 hr_utility.set_location('Entering ' || l_proc,5);
1972
1973 -- Get the person's Position (for his primary Assignment).
1974
1975 for per_pos_id in C_per_pos loop
1976 hr_utility.set_location(l_proc,10);
1977 l_position_id := per_pos_id.position_id;
1978 end loop;
1979
1980
1981 If l_position_id is not null then
1982 hr_utility.set_location(l_proc,15);
1983 ghr_history_fetch.fetch_positionei
1984 (p_position_id => l_position_id ,
1985 p_information_type => 'GHR_US_POS_GRP1' ,
1986 p_date_effective => p_effective_date ,
1987 p_pos_ei_data => l_pos_ei_data
1988 );
1989 l_title := l_pos_ei_data.poei_information12;
1990 End if;
1991 return(l_title);
1992 hr_utility.set_location('Leaving ' || l_proc,25);
1993 End get_position_work_title;
1994
1995
1996 -- Function that returns fullname in the format (fml) i.e <First_name> <Middle_name>.<Last Name>
1997 FUNCTION get_full_name_fml(p_person_id in varchar2,
1998 p_effective_date in date default trunc(sysdate)
1999 ) RETURN varchar2 IS
2000
2001 l_proc varchar2(72) := 'get_person_full_name';
2002 l_name per_people_f.full_name%type;
2003
2004 CURSOR c_full_name is
2005 SELECT per.first_name || decode(per.middle_names,null,null, ' ' ||substr(per.middle_names,1,1) || '.') || ' ' ||per.last_name full_name
2006 FROM per_people_f per
2007 WHERE per.person_id = p_person_id
2008 AND trunc(nvl(p_effective_date,sysdate))between per.effective_start_date and per.effective_end_date;
2009
2010
2011 BEGIN
2012 hr_utility.set_location('Entering ' || l_proc,5);
2013 for full_name in c_full_name loop
2014 hr_utility.set_location(l_proc,10);
2015 l_name := substr(full_name.full_name,1,240);
2016 End loop;
2017
2018 hr_utility.set_location('Leaving ' || l_proc,15);
2019 return(l_name);
2020 END get_full_name_fml;
2021
2022 FUNCTION get_upd34_pay_basis (p_person_id IN per_people_f.person_id%TYPE
2023 ,p_position_id IN per_positions.position_id%type
2024 ,p_prd IN ghr_pa_requests.pay_rate_determinant%TYPE
2025 ,p_noa_code IN varchar2 DEFAULT NULL
2026 ,p_pa_request_id IN NUMBER DEFAULT NULL
2027 ,p_effective_date IN DATE)
2028 RETURN VARCHAR2 IS
2029 l_retained_grade ghr_pay_calc.retained_grade_rec_type;
2030 l_update34_date DATE;
2031 l_pos_ei_grade_data per_position_extra_info%rowtype;
2032 BEGIN
2033 hr_utility.set_location('Entering get_upd34_pay_basis',10);
2034 begin
2035 l_retained_grade := ghr_pc_basic_pay.get_retained_grade_details (p_person_id
2036 ,p_effective_date
2037 ,p_pa_request_id);
2038 EXCEPTION
2039 when others then
2040 hr_utility.set_location('Exception raised ' || sqlerrm(sqlcode),15);
2041 hr_utility.set_message(8301,'GHR_38255_MISSING_RETAINED_DET');
2042 hr_utility.raise_error;
2043 end;
2044
2045 IF p_prd IN ('A','B','E','F')
2046 AND nvl(p_noa_code,'XXX') <> '740'
2047 AND l_retained_grade.temp_step IS NOT NULL THEN
2048 hr_utility.set_location('get from positionei ',1);
2049 ghr_history_fetch.fetch_positionei(
2050 p_position_id => p_position_id,
2051 p_information_type => 'GHR_US_POS_VALID_GRADE',
2052 p_date_effective => p_effective_date,
2053 p_pos_ei_data => l_pos_ei_grade_data);
2054
2055 IF l_pos_ei_grade_data.position_extra_info_id IS NOT NULL THEN
2056 RETURN(l_pos_ei_grade_data.poei_information6);
2057 END IF;
2058 END IF;
2059
2060 l_update34_date := ghr_pay_caps.update34_implemented_date(p_person_id);
2061 hr_utility.set_location('l_update34_date ' || l_update34_date,1);
2062 If l_update34_date is null then
2063 hr_utility.set_location('update 34 is null',1);
2064 RETURN(l_retained_grade.pay_basis);
2065 elsif p_effective_date >= l_update34_date then
2066 hr_utility.set_location('update 34 isnot null and effective_date is ',1);
2067 RETURN(l_retained_grade.pay_basis);
2068 else
2069 hr_utility.set_location('get from positionei ',1);
2070 ghr_history_fetch.fetch_positionei(
2071 p_position_id => p_position_id,
2072 p_information_type => 'GHR_US_POS_VALID_GRADE',
2073 p_date_effective => p_effective_date,
2074 p_pos_ei_data => l_pos_ei_grade_data);
2075
2076 IF l_pos_ei_grade_data.position_extra_info_id IS NOT NULL THEN
2077 RETURN(l_pos_ei_grade_data.poei_information6);
2078 ELSE
2079 RETURN(null);
2080 END IF;
2081 end if;
2082
2083 END get_upd34_pay_basis;
2084
2085 PROCEDURE update34_implement_cancel (p_person_id IN NUMBER
2086 ,p_assignment_id IN NUMBER
2087 ,p_date IN DATE
2088 ,p_altered_pa_request_id in NUMBER)
2089 IS
2090
2091 l_effective_date DATE;
2092 l_update34_date DATE;
2093 l_per_ei_data per_people_extra_info%rowtype;
2094 l_person_extra_info_id NUMBER;
2095 l_object_version_number NUMBER;
2096
2097 l_tenure VARCHAR2(150);
2098 l_annuitant_indicator VARCHAR2(150);
2099 l_pay_rate_determinant VARCHAR2(150);
2100 l_work_schedule VARCHAR2(150);
2101 l_part_time_hours VARCHAR2(150);
2102 l_altered_pa_request_id NUMBER;
2103 l_exists BOOLEAN := false;
2104 l_del_flag varchar2(1);
2105 l_upd_flag varchar2(1);
2106
2107 CURSOR c_par is
2108 select par.effective_date effective_date,
2109 par.pa_request_id
2110 from ghr_pa_requests par
2111 where par.person_id = p_person_id
2112 and par.effective_date >= p_date
2113 and par.pa_notification_id is not null
2114 and par.first_noa_code <> '001'
2115 and par.pa_request_id <> nvl(p_altered_pa_request_id,par.pa_request_id)
2116 and nvl(par.first_noa_cancel_or_correct,hr_api.g_varchar2) <> 'CANCEL'
2117 and ((par.second_noa_code is null)
2118 or (par.second_noa_code is not null
2119 and nvl(par.second_noa_cancel_or_correct,hr_api.g_varchar2) <> 'CANCEL'))
2120 order by par.effective_date ,par.pa_request_id ;
2121
2122
2123 /*CURSOR c_par IS
2124 SELECT par.effective_date effective_date
2125 ,par.altered_pa_request_id
2126 FROM ghr_pa_routing_history prh
2127 ,ghr_pa_requests par
2128 WHERE par.person_id = p_person_id
2129 AND par.effective_date >= p_date
2130 AND prh.pa_request_id = par.pa_request_id
2131 AND prh.pa_routing_history_id = (SELECT MAX(prh2.pa_routing_history_id)
2132 FROM ghr_pa_routing_history prh2
2133 WHERE prh2.pa_request_id = par.pa_request_id)
2134 AND prh.action_taken IN ('FUTURE_ACTION','UPDATE_HR_COMPLETE')
2135 AND par.NOA_FAMILY_CODE != 'CANCEL'
2136 AND ( ( par.second_noa_code IS NULL
2137 AND NVL(par.first_noa_cancel_or_correct,'X') != 'CANCEL'
2138 )
2139 OR ( par.second_noa_code IS NOT NULL
2140 AND par.NOA_FAMILY_CODE != 'CORRECT'
2141 AND ( NVL(par.first_noa_cancel_or_correct,'X') != 'CANCEL'
2142 OR NVL(par.second_noa_cancel_or_correct,'X') != 'CANCEL'
2143 )
2144 )
2145 OR ( par.second_noa_code IS NOT NULL
2146 AND par.NOA_FAMILY_CODE = 'CORRECT'
2147 AND NVL(par.second_noa_cancel_or_correct,'X') != 'CANCEL'
2148 )
2149 )
2150 ORDER BY par.effective_date, par.pa_request_id;
2151 */
2152
2153 BEGIN
2154
2155 for c_par_rec in c_par
2156 loop
2157 l_exists := TRUE;
2158 l_effective_date := c_par_rec.effective_date;
2159 hr_utility.set_location('p_rpa' || c_par_rec.pa_request_id,1);
2160 exit;
2161 end loop;
2162
2163 l_update34_date := ghr_pay_caps.update34_implemented_date(p_person_id);
2164
2165 If p_date = nvl(l_update34_date,hr_api.g_date) then
2166 If not l_exists then
2167 hr_utility.set_location('Not exists',1);
2168 l_effective_date := NULL;
2169 l_del_flag := 'Y';
2170 Else
2171 hr_utility.set_location('Exists',1);
2172 if l_effective_date <> p_date then
2173 l_upd_flag := 'Y';
2174 end if;
2175 End if;
2176
2177 ghr_history_fetch.fetch_peopleei
2178 (p_person_id => p_person_id
2179 ,p_information_type => 'GHR_US_PER_UPDATE34'
2180 ,p_date_effective => p_date
2181 ,p_per_ei_data => l_per_ei_data);
2182
2183 l_person_extra_info_id := l_per_ei_data.person_extra_info_id;
2184 l_object_version_number := l_per_ei_data.object_version_number;
2185 if l_person_extra_info_id is not null then
2186 hr_utility.set_location('PEID Exists',1);
2187 if l_del_flag = 'Y' then
2188 delete per_people_extra_info
2189 where person_extra_info_id = l_person_extra_info_id;
2190
2191 delete from ghr_pa_history
2192 where table_name = 'PER_PEOPLE_EXTRA_INFO'
2193 and information1 = l_person_extra_info_id;
2194
2195 end if;
2196
2197 if l_upd_flag = 'Y' then
2198 delete from ghr_pa_history
2199 where table_name = 'PER_PEOPLE_EXTRA_INFO'
2200 and to_number(information4) = p_person_id
2201 and information5 = 'GHR_US_PER_UPDATE34'
2202 and pa_request_id = l_altered_pa_request_id ;
2203
2204 ghr_person_extra_info_api.update_person_extra_info
2205 (P_PERSON_EXTRA_INFO_ID => l_person_extra_info_id
2206 ,P_EFFECTIVE_DATE => l_effective_date
2207 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
2208 ,P_PEI_ATTRIBUTE_CATEGORY => 'GHR_US_PER_UPDATE34'
2209 ,p_pei_INFORMATION3 => fnd_date.date_to_canonical(l_effective_date)
2210 ,P_PEI_INFORMATION_CATEGORY => 'GHR_US_PER_UPDATE34'
2211 );
2212 end if;
2213 end if;
2214 end if;
2215
2216 END update34_implement_cancel;
2217
2218 FUNCTION temp_step_true (p_pa_request_id IN ghr_pa_requests.pa_request_id%type)
2219 RETURN BOOLEAN IS
2220
2221 l_noa_code ghr_nature_of_actions.code%type;
2222 l_temp_step VARCHAR2(60);
2223
2224 CURSOR cur_par IS
2225 SELECT first_noa_code,second_noa_code
2226 FROM ghr_pa_requests
2227 WHERE pa_request_id = p_pa_request_id;
2228
2229 CURSOR cur_temp_step IS
2230 SELECT rei_information3 temp_step
2231 FROM ghr_pa_request_extra_info
2232 WHERE pa_request_id = p_pa_request_id
2233 AND information_type = 'GHR_US_PAR_RG_TEMP_PROMO';
2234
2235 BEGIN
2236 ------- Start Temp Promotion Code changes for 703 and 866 NOACs.
2237 l_noa_code := null;
2238 l_temp_step := null;
2239 IF p_pa_request_id is not null THEN
2240 FOR cur_par_rec IN cur_par LOOP
2241 if cur_par_rec.first_noa_code = '002' then
2242 l_noa_code := cur_par_rec.second_noa_code;
2243 else
2244 l_noa_code := cur_par_rec.first_noa_code;
2245 end if;
2246 EXIT;
2247 END LOOP;
2248 IF l_noa_code in ('703','866') THEN
2249 FOR cur_temp_step_rec IN cur_temp_step LOOP
2250 l_temp_step := cur_temp_step_rec.temp_step;
2251 END LOOP;
2252 END IF;
2253 END IF;
2254 -------End Temp Promotion Code changes for 703 and 866 NOACs.
2255 IF l_temp_step is not null THEN
2256 RETURN(TRUE);
2257 ELSE
2258 RETURN(FALSE);
2259 END IF;
2260 END temp_step_true;
2261
2262 --Begin bug# 8653508
2263 FUNCTION get_flsa_category(p_position_id in hr_all_positions_f.position_id%TYPE,
2264 p_effective_date in date default sysdate
2265 ) RETURN varchar2 IS
2266
2267
2268 l_proc varchar2(72) := 'get_flsa_category';
2269 l_pos_ei_data per_position_extra_info%rowtype;
2270 l_flsa varchar2(150);
2271
2272
2273 BEGIN
2274
2275 hr_utility.set_location('Entering ' || l_proc,5);
2276
2277 ghr_history_fetch.fetch_positionei
2278 (p_position_id => p_position_id ,
2279 p_information_type => 'GHR_US_POS_GRP1' ,
2280 p_date_effective => p_effective_date ,
2281 p_pos_ei_data => l_pos_ei_data
2282 );
2283 l_flsa := l_pos_ei_data.poei_information7;
2284 hr_utility.set_location('Leaving ' || l_proc,25);
2285 return(l_flsa);
2286
2287 End get_flsa_category;
2288
2289 FUNCTION get_bargaining_unit(p_position_id in hr_all_positions_f.position_id%TYPE,
2290 p_effective_date in date default sysdate
2291 ) RETURN varchar2 IS
2292
2293
2294 l_proc varchar2(72) := 'get_bargaining_unit';
2295 l_pos_ei_data per_position_extra_info%rowtype;
2296 l_bargaining_unit varchar2(150);
2297
2298
2299 BEGIN
2300
2301 hr_utility.set_location('Entering ' || l_proc,5);
2302
2303 ghr_history_fetch.fetch_positionei
2304 (p_position_id => p_position_id ,
2305 p_information_type => 'GHR_US_POS_GRP1' ,
2306 p_date_effective => p_effective_date ,
2307 p_pos_ei_data => l_pos_ei_data
2308 );
2309 l_bargaining_unit := l_pos_ei_data.poei_information8;
2310 hr_utility.set_location('Leaving ' || l_proc,25);
2311 return(l_bargaining_unit);
2312
2313 End get_bargaining_unit;
2314
2315 FUNCTION get_work_schedule(p_position_id in hr_all_positions_f.position_id%TYPE,
2316 p_effective_date in date default sysdate
2317 ) RETURN varchar2 IS
2318
2319
2320 l_proc varchar2(72) := 'get_work_schedule';
2321 l_pos_ei_data per_position_extra_info%rowtype;
2322 l_work_schedule varchar2(150);
2323
2324
2325 BEGIN
2326
2327 hr_utility.set_location('Entering ' || l_proc,5);
2328
2329 ghr_history_fetch.fetch_positionei
2330 (p_position_id => p_position_id ,
2331 p_information_type => 'GHR_US_POS_GRP1' ,
2332 p_date_effective => p_effective_date ,
2333 p_pos_ei_data => l_pos_ei_data
2334 );
2335 l_work_schedule := l_pos_ei_data.poei_information10;
2336 hr_utility.set_location('Leaving ' || l_proc,25);
2337 return(l_work_schedule);
2338
2339 End get_work_schedule;
2340
2341
2342 FUNCTION get_valid_grade(p_position_id in hr_all_positions_f.position_id%TYPE,
2343 p_effective_date in date default sysdate
2344 ) RETURN varchar2 IS
2345
2346
2347 l_proc varchar2(72) := 'get_valid_grade';
2348 l_pos_ei_data per_position_extra_info%rowtype;
2349 l_valid_grade varchar2(150);
2350 l_grade_id varchar2(150);
2351 CURSOR cur_grd IS
2352 SELECT grd.name grade_name
2353 FROM per_grades grd
2354 WHERE grd.grade_id = TO_NUMBER(l_grade_id);
2355
2356
2357 BEGIN
2358
2359 hr_utility.set_location('Entering ' || l_proc,5);
2360
2361 ghr_history_fetch.fetch_positionei
2362 (p_position_id => p_position_id ,
2363 p_information_type => 'GHR_US_POS_VALID_GRADE' ,
2364 p_date_effective => p_effective_date ,
2365 p_pos_ei_data => l_pos_ei_data
2366 );
2367 l_grade_id := l_pos_ei_data.poei_information3;
2368 FOR l_cur_grd IN cur_grd LOOP
2369 l_valid_grade:= l_cur_grd.grade_name;
2370 END LOOP;
2371 hr_utility.set_location('Leaving ' || l_proc,25);
2372 return(l_valid_grade);
2373
2374 End get_valid_grade;
2375
2376 FUNCTION get_duty_station(p_position_id in hr_all_positions_f.position_id%TYPE,
2377 p_effective_date in date default sysdate
2378 ) RETURN varchar2 IS
2379
2380
2381 l_proc varchar2(72) := 'get_duty_station';
2382 l_duty_station ghr_duty_stations_v.duty_station_code%type;
2383 l_location_id hr_all_positions_f.location_id%type;
2384
2385 CURSOR get_loc_id IS
2386 SELECT location_id
2387 FROM hr_all_positions_f
2388 WHERE position_id = p_position_id
2389 and p_effective_date between
2390 effective_start_date and effective_end_date;
2391
2392
2393 CURSOR get_duty_station_code IS
2394 select ds.duty_station_code
2395 FROM hr_location_extra_info lei, ghr_duty_stations_v ds
2396 WHERE lei.location_id = l_location_id
2397 AND lei.information_type = 'GHR_US_LOC_INFORMATION'
2398 and to_number(lei.lei_information3) = ds.duty_station_id
2399 and p_effective_date between ds.effective_start_date
2400 and ds.effective_end_date;
2401
2402
2403
2404 BEGIN
2405 hr_utility.set_location('Entering ' || l_proc,5);
2406
2407 FOR l_get_loc_id IN get_loc_id LOOP
2408 l_location_id := l_get_loc_id.location_id;
2409 END LOOP;
2410
2411 FOR l_get_duty_station_code IN get_duty_station_code LOOP
2412 l_duty_station := l_get_duty_station_code.duty_station_code;
2413 END LOOP;
2414
2415 hr_utility.set_location('Leaving ' || l_proc,25);
2416 return(l_duty_station);
2417
2418 End get_duty_station;
2419
2420 FUNCTION get_occ_series(p_position_id in hr_all_positions_f.position_id%TYPE,
2421 p_effective_date in date default sysdate,
2422 p_business_group_id in per_assignments_f.business_group_id%type
2423 ) RETURN varchar2 IS
2424
2425
2426 l_proc varchar2(72) := 'get_occ_series';
2427 l_occ_series varchar2(150);
2428 l_job_id hr_all_positions_f.location_id%type;
2429
2430 CURSOR get_job_id IS
2431 SELECT pos.job_id
2432 FROM hr_all_positions_f pos
2433 WHERE pos.position_id = p_position_id
2434 AND p_effective_date BETWEEN pos.effective_start_date and pos.effective_end_date;
2435
2436 BEGIN
2437 hr_utility.set_location('Entering ' || l_proc,5);
2438
2439 FOR l_get_job_id IN get_job_id LOOP
2440 l_job_id := l_get_job_id.job_id;
2441 END LOOP;
2442 l_occ_series := ghr_api.get_job_occ_series_job
2443 (p_job_id => l_job_id
2444 ,p_business_group_id => p_business_group_id);
2445
2446 hr_utility.set_location('Leaving ' || l_proc,25);
2447 return(l_occ_series);
2448
2449 End get_occ_series;
2450
2451 FUNCTION get_appropriation_code(
2452 p_position_id hr_all_positions_f.position_id%TYPE
2453 ,p_effective_date date default sysdate) RETURN VARCHAR2 IS
2454
2455
2456 l_proc varchar2(72) := 'get_appropriation_code';
2457 l_pos_ei_grp2_data per_position_extra_info%rowtype;
2458
2459 BEGIN
2460
2461 hr_utility.set_location('Entering ' || l_proc,35);
2462
2463 ghr_history_fetch.fetch_positionei(
2464 p_position_id => p_position_id,
2465 p_information_type => 'GHR_US_POS_GRP2',
2466 p_date_effective => p_effective_date,
2467 p_pos_ei_data => l_pos_ei_grp2_data);
2468
2469 hr_utility.set_location('Leaving ' || l_proc,35);
2470 return(l_pos_ei_grp2_data.poei_information13);
2471
2472 END get_appropriation_code;
2473 --Begin 9647383
2474 FUNCTION get_pos_organization( p_position_id hr_all_positions_f.position_id%TYPE
2475 ,p_effective_date date default sysdate
2476 ,p_business_group_id in per_assignments_f.business_group_id%type)
2477 RETURN VARCHAR2 IS
2478
2479 l_proc varchar2(72) := 'get_pos_organization';
2480
2481 CURSOR get_pos_org IS
2482 SELECT org.name from hr_positions_f pos, hr_organization_units org
2483 WHERE pos.organization_id = org.organization_id
2484 AND pos.business_group_id = p_business_group_id
2485 AND p_effective_date between pos.EFFECTIVE_START_DATE and pos.EFFECTIVE_END_DATE
2486 AND pos.position_id =p_position_id;
2487
2488 BEGIN
2489 hr_utility.set_location('Entering ' || l_proc,35);
2490 FOR get_pos_org_rec IN get_pos_org LOOP
2491 hr_utility.set_location('Leaving ' || l_proc,45);
2492 RETURN(get_pos_org_rec.name);
2493 END LOOP;
2494 hr_utility.set_location('Leaving without data ' || l_proc,45);
2495
2496 END get_pos_organization;
2497 --Begin 9647383
2498 --End bug# 8653508
2499
2500 --Begin Bug# 13684234
2501 function get_pos_availability_status (
2502 p_availability_status_id number) return varchar2 is
2503
2504 cursor c_pos_shared_types is
2505 select system_type_cd
2506 from per_shared_types_vl
2507 where shared_type_id = p_availability_status_id;
2508
2509 l_system_type_cd per_shared_types_vl.system_type_cd%TYPE := null;
2510
2511 begin
2512 if p_availability_status_id is not null then
2513 open c_pos_shared_types;
2514 fetch c_pos_shared_types into l_system_type_cd;
2515 close c_pos_shared_types;
2516 end if;
2517 return upper(l_system_type_cd);
2518 end get_pos_availability_status;
2519 --End Bug# 13684234
2520
2521 END ghr_pa_requests_pkg;
[Home] [Help]