1 package body ghr_validate_perwsepi AS
2 /* $Header: ghrwsepi.pkb 115.3 1999/11/09 22:52:15 pkm ship $ */
3
4 -- Created g_new_line to use instead of CHR(10)
5 g_new_line varchar2(1) := substr('
6 ',1,1);
7
8 --------------------------------------------------------------------------------------------------------
9 ---------------------------------- get_person_type ----------------------------------------------------
10 --------------------------------------------------------------------------------------------------------
11 Function get_person_type (p_business_group_id IN NUMBER,p_person_id IN number,p_effective_date IN DATE)
12 RETURN VARCHAR2 IS
13
14 -- This cursor gets the Person Type ID for Employee
15 CURSOR cur_chk_pst(p_business_group_id IN NUMBER,p_person_id IN NUMBER, p_effective_date IN DATE) IS
16 SELECT ppf.person_id
17 FROM per_people_f ppf, per_person_types pty
18 WHERE ppf.person_id = p_person_id
19 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
20 AND ppf.person_type_id = pty.person_type_id
21 AND pty.system_person_type = 'EMP'
22 AND pty.business_group_id = p_business_group_id
23 AND pty.active_flag = 'Y';
24
25 BEGIN
26 FOR cur_chk_pst_rec in cur_chk_pst(p_business_group_id,p_person_id,p_effective_date) LOOP
27 RETURN('TRUE');
28 END LOOP;
29
30 RETURN('FALSE');
31
32 END get_person_type;
33
34 -- This function checks if there are any future PA Request actions for a given person between 2 dates
35 -- that have been completed.
36 FUNCTION check_pend_future_pars (p_person_id IN NUMBER
37 ,p_from_date IN DATE
38 ,p_to_date IN DATE)
39 RETURN VARCHAR2 IS
40
41 l_pend_future_list VARCHAR2(2000) := NULL;
42 --
43 CURSOR c_par IS
44 SELECT 'Request Number:'||par.request_number||
45 ', 1st NOA Code:'||par.first_noa_code||
46 DECODE(par.second_noa_code,NULL,NULL, ', 2nd NOA Code:'||par.second_noa_code)||
47 ', Effective Date:'||par.effective_date||
48 ', Updater:'||prh.user_name list_info
49 FROM ghr_pa_routing_history prh
50 ,ghr_pa_requests par
51 WHERE par.person_id = p_person_id
52 AND par.effective_date BETWEEN NVL(p_from_date,par.effective_date) AND NVL(p_to_date,par.effective_date)
53 AND prh.pa_request_id = par.pa_request_id
54 AND prh.pa_routing_history_id = (SELECT MAX(prh2.pa_routing_history_id)
55 FROM ghr_pa_routing_history prh2
56 WHERE prh2.pa_request_id = par.pa_request_id)
57 AND prh.action_taken IN ('FUTURE_ACTION')
58 ORDER BY par.effective_date, par.pa_request_id;
59
60
61 BEGIN
62 -- loop around them all to build up a list
63 FOR c_par_rec IN c_par LOOP
64 l_pend_future_list := SUBSTR(l_pend_future_list||g_new_line||g_new_line||c_par%ROWCOUNT||'.'||c_par_rec.list_info,1,2000);
65 END LOOP;
66
67 RETURN(l_pend_future_list);
68
69 END check_pend_future_pars ;
70
71 END ghr_validate_perwsepi;
[Home] [Help]