1 PACKAGE BODY ghr_pdi_pkg AS
2 /* $Header: ghrwspdi.pkb 120.0.12010000.2 2009/05/26 10:57:38 vmididho noship $ */
3
4
5 PROCEDURE get_last_routing_list(p_position_description_id IN ghr_position_descriptions.position_description_id%TYPE
6 ,p_routing_list_id OUT NOCOPY ghr_routing_lists.routing_list_id%TYPE
7 ,p_routing_list_name OUT NOCOPY ghr_routing_lists.name%TYPE
8 ,p_next_seq_number OUT NOCOPY ghr_routing_list_members.seq_number%TYPE
9 ,p_next_user_name OUT NOCOPY ghr_routing_list_members.user_name%TYPE
10 ,p_next_groupbox_id OUT NOCOPY ghr_routing_list_members.groupbox_id%TYPE
11 ,p_broken IN OUT NOCOPY BOOLEAN) IS
12
13 CURSOR cur_pdh_last_rli IS
14 SELECT rli.routing_list_id
15 ,rli.name
16 ,pdh.routing_seq_number
17 ,pdh.pd_routing_history_id
18 FROM ghr_routing_lists rli
19 ,ghr_pd_routing_history pdh
20 WHERE pdh.position_description_id = p_position_description_id
21 AND pdh.routing_list_id = rli.routing_list_id
22 ORDER BY pdh.pd_routing_history_id DESC;
23
24 -- The order by makes sure the first one we get is the last in the history
25 -- By joing to routing_list forces us to have a routing_list (since we didn't doan outer join)
26
27
28 -- Just get the last record so we can see if the cursor above got us the last record
29
30
31 CURSOR cur_pdh_last IS
32 SELECT pdh.pd_routing_history_id
33 FROM ghr_pd_routing_history pdh
34 WHERE pdh.position_description_id = p_position_description_id
35 ORDER BY pdh.pd_routing_history_id DESC;
36 -- Again the order by saves us having to do a max
37 CURSOR cur_rlm (p_routing_list_id IN NUMBER
38 ,p_seq_number IN NUMBER) IS
39 SELECT rlm.seq_number
40 ,rlm.user_name
41 ,rlm.groupbox_id
42 FROM ghr_routing_list_members rlm
43 WHERE rlm.routing_list_id = p_routing_list_id
44 AND rlm.seq_number > p_seq_number
45 ORDER BY rlm.seq_number asc;
46 BEGIN
47 -- Go and get the last routing list to be used
48 FOR cur_pdh_last_rli_rec IN cur_pdh_last_rli LOOP
49 p_routing_list_id := cur_pdh_last_rli_rec.routing_list_id;
50 p_routing_list_name := cur_pdh_last_rli_rec.name;
51 -- See if the routing list has been broken
52 FOR cur_pdh_last_rec IN cur_pdh_last LOOP
53 IF cur_pdh_last_rec.pd_routing_history_id = cur_pdh_last_rli_rec.pd_routing_history_id THEN
54
55
56 p_broken := FALSE;
57 ELSE
58 p_broken := TRUE;
59 END IF;
60 EXIT; -- Only want the first record therfore exit after we have got it
61 END LOOP;
62 -- If it is not broken then get the next sequence in the routing list
63 --
64 IF NOT p_broken THEN
65 FOR cur_rlm_rec IN cur_rlm(cur_pdh_last_rli_rec.routing_list_id, cur_pdh_last_rli_rec.routing_seq_number) LOOP
66
67
68 p_next_seq_number := cur_rlm_rec.seq_number;
69 p_next_user_name := cur_rlm_rec.user_name;
70 p_next_groupbox_id := cur_rlm_rec.groupbox_id;
71 --
72 -- When we get the first one exit
73 EXIT;
74 END LOOP;
75 END IF;
76 EXIT; -- Only want the first record therfore exit after we have got it
77 END LOOP;
78 END get_last_routing_list;
79
80
81 PROCEDURE get_roles (p_position_description_id in number
82 ,p_routing_group_id in number
83 ,p_user_name in varchar2 default null
84 ,p_initiator_flag in out NOCOPY varchar2
85 ,p_requester_flag in out NOCOPY varchar2
86 ,p_authorizer_flag in out NOCOPY varchar2
87 ,p_personnelist_flag in out NOCOPY varchar2
88 ,p_approver_flag in out NOCOPY varchar2
89 ,p_reviewer_flag in out NOCOPY varchar2) IS
90
91 l_groupbox_id ghr_pd_routing_history.groupbox_id%TYPE;
92 l_user_name ghr_pd_routing_history.user_name%TYPE;
93
94 CURSOR cur_gp_user IS
95 select pdh.groupbox_id
96 ,pdh.user_name
97 from ghr_pd_routing_history pdh
98 where pdh.position_description_id = p_position_description_id
99 order by pdh.pd_routing_history_id desc;
100
101 CURSOR cur_first_user IS
102 select pdh.groupbox_id
103 from ghr_pd_routing_history pdh
104 where pdh.position_description_id = p_position_description_id
105 and pdh.user_name = l_user_name
106 and pdh.groupbox_id is not NULL
107 and not exists (select 1
108 from ghr_pd_routing_history pdh2
109 where pdh2.position_description_id = p_position_description_id
110 and pdh2.user_name <> l_user_name
111 and pdh2.pd_routing_history_id > pdh.pd_routing_history_id)
112 order by pdh.pd_routing_history_id asc;
113
114 CURSOR cur_user_roles IS
115 select pei.pei_information4 initiator_flag
116 ,pei.pei_information5 requester_flag
117 ,pei.pei_information6 authorizer_flag
118 ,pei.pei_information7 personnelist_flag
119 ,pei.pei_information8 approver_flag
120 ,pei.pei_information9 reviewer_flag
121 from per_people_extra_info pei
122 ,fnd_user usr
123 where usr.user_name = l_user_name
124 and pei.person_id = usr.employee_id
125 and pei.information_type = 'GHR_US_PER_WF_ROUTING_GROUPS'
126 and pei.pei_information3 = p_routing_group_id;
127
128 CURSOR cur_gpbox_user_roles IS
129 select gru.initiator_flag
130 ,gru.authorizer_flag
131 ,gru.personnelist_flag
132 ,gru.approver_flag
133 ,gru.reviewer_flag
134 ,gru.requester_flag
135 from ghr_groupbox_users gru
136 where gru.groupbox_id = l_groupbox_id
137 and gru.user_name = p_user_name;
138
139 BEGIN
140 -- First get the last history record for given position_description_id
141 FOR c_rec in cur_gp_user LOOP
142 l_groupbox_id := c_rec.groupbox_id;
143 l_user_name := c_rec.user_name;
144 EXIT;
145 END LOOP;
146
147 -- If it is for a group box then definitely use the group box roles and that is it!
148 IF l_groupbox_id is not null THEN
149 FOR C_rec in cur_gpbox_user_roles LOOP
150 p_initiator_flag := c_rec.initiator_flag;
151 p_authorizer_flag := c_rec.authorizer_flag;
152 p_personnelist_flag := c_rec.personnelist_flag;
153 p_approver_flag := c_rec.approver_flag;
154 p_reviewer_flag := c_rec.reviewer_flag;
155 p_requester_flag := c_rec.requester_flag;
156 EXIT;
157 END LOOP;
158
159 ELSE
160 -- otherwise still need to work out if we use the individual roles or it was initially
161 -- set to this user in a group box and they saved and held!
162 IF l_user_name is null THEN
163 l_user_name := p_user_name;
164 END IF;
165 FOR cur_first_user_rec in cur_first_user LOOP
166 l_groupbox_id := cur_first_user_rec.groupbox_id;
167 EXIT;
168 END LOOP;
169
170 -- Again if it is for a group box then definitely use the group box roles
171 -- Note: the l_groupbox_id will be null if the above cursor return no rows
172 IF l_groupbox_id is not null THEN
173 FOR C_rec in cur_gpbox_user_roles LOOP
174 p_initiator_flag := c_rec.initiator_flag;
175 p_authorizer_flag := c_rec.authorizer_flag;
176 p_personnelist_flag := c_rec.personnelist_flag;
177 p_approver_flag := c_rec.approver_flag;
178 p_reviewer_flag := c_rec.reviewer_flag;
179 p_requester_flag := c_rec.requester_flag;
180 EXIT;
181 END LOOP;
182 ELSE
183 -- definitely get the user roles
184 FOR c_rec in cur_user_roles LOOP
185 p_initiator_flag := c_rec.initiator_flag;
186 p_authorizer_flag := c_rec.authorizer_flag;
187 p_personnelist_flag := c_rec.personnelist_flag;
188 p_approver_flag := c_rec.approver_flag;
189 p_reviewer_flag := c_rec.reviewer_flag;
190 p_requester_flag := c_rec.requester_flag;
191 END LOOP;
192 END IF;
193 END IF;
194
195 END get_roles;
196
197 END ghr_pdi_pkg;
[Home] [Help]