[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_APPROVALS
Source
1 package body BEN_CWB_APPROVALS as
2 /* $Header: bencwbap.pkb 120.1 2005/12/23 02:40 aupadhya noship $ */
3
4
5
6
7 g_package varchar2(80) := 'BEN_CWB_APPROVALS';
8
9
10
11 -- ----------------------------------------------------------------------------
12 -- |-------------------------< approve_all_managers >---------------------|
13 -- ----------------------------------------------------------------------------
14
15
16
17 procedure approve_all_managers
18 (
19 p_group_per_in_ler_id in number,
20 p_group_pl_id in number,
21 p_group_oipl_id in number,
22 p_task_id in number,
23 p_effective_date date,
24 p_login_person_id in number
25 )
26 is
27 l_group_per_in_ler_id number;
28 l_group_pl_id number;
29 l_group_oipl_id number;
30 l_access_cd varchar2(15);
31 l_object_version_number number;
32 l_task_id number;
33 l_object_version_number_task number;
34 l_error varchar2(500);
35 l_task_status varchar2(15);
36 l_package varchar2(80) := g_package||'.approve_all_managers';
37
38 cursor getManagers is
39 select
40 group_per_in_ler_id,
41 group_pl_id,
42 group_oipl_id,
43 grp.approval_cd,
44 access_cd,
45 grp.object_version_number
46 from
47 ben_cwb_person_groups grp,
48 ben_cwb_group_hrchy hrchy,
49 ben_cwb_group_hrchy hrchy_mgr
50 where
51 hrchy.mgr_per_in_ler_id = p_group_per_in_ler_id
52 and hrchy.lvl_num>0
53 and hrchy.emp_per_in_ler_id=hrchy_mgr.mgr_per_in_ler_id
54 and hrchy_mgr.lvl_num=0
55 and grp.group_per_in_ler_id=hrchy_mgr.mgr_per_in_ler_id
56 and grp.group_pl_id = p_group_pl_id
57 and grp.group_oipl_id = p_group_oipl_id
58 and ((nvl(grp.approval_cd,'NULL') not in ('AP','PR')));
59
60 cursor getTaskObjVerNum(c_group_per_in_ler_id in number,c_task_id in number) is
61 select
62 object_version_number ovn,
63 status_cd status
64 from ben_cwb_person_tasks
65 where group_per_in_ler_id = c_group_per_in_ler_id
66 and task_id = c_task_id
67 and group_pl_id=p_group_pl_id;
68
69 cursor getApprTaskId(c_group_per_in_ler_id in number) is
70 SELECT cwb_wksht_grp_id
71 from
72 ben_cwb_wksht_grp grp,
73 ben_cwb_person_tasks tsk
74 where grp.wksht_grp_cd = 'APPR'
75 and tsk.group_per_in_ler_id= c_group_per_in_ler_id
76 and tsk.task_id= grp.cwb_wksht_grp_id;
77
78
79 begin
80 --hr_utility.trace_on (null, 'ORACLE');
81
82 hr_utility.set_location('Entering '||l_package ,10);
83
84 --hr_utility.set_location('p_group_per_in_ler_id '||p_group_per_in_ler_id ,20);
85 --hr_utility.set_location('p_group_pl_id '||p_group_pl_id ,30);
86 --hr_utility.set_location('p_group_oipl_id '||p_group_oipl_id ,40);
87 --hr_utility.set_location('p_effective_date '||p_effective_date ,60);
88 --hr_utility.set_location('p_login_person_id '||p_login_person_id ,70);
89
90
91 for i in getManagers loop
92
93 l_group_per_in_ler_id := i.group_per_in_ler_id;
94 l_group_pl_id := i.group_pl_id;
95 l_group_oipl_id := i.group_oipl_id;
96 l_access_cd:= i.access_cd;
97 l_object_version_number := i.object_version_number;
98
99 --hr_utility.set_location('p_group_per_in_ler_id '||l_group_per_in_ler_id ,20);
100 --hr_utility.set_location('p_group_pl_id '||l_group_pl_id ,30);
101 --hr_utility.set_location('p_group_oipl_id '||l_group_oipl_id ,40);
102 --hr_utility.set_location('l_access_cd '||l_access_cd ,50);
103
104
105 if ('NA' <> l_access_cd) then
106
107 BEN_CWB_PERSON_GROUPS_API.update_group_budget
108 (
109 p_validate => false
110 ,p_group_per_in_ler_id => l_group_per_in_ler_id
111 ,p_group_pl_id => l_group_pl_id
112 ,p_group_oipl_id => l_group_oipl_id
113 ,p_access_cd => 'RO'
114 ,p_approval_cd => 'AP'
115 ,p_approval_date => p_effective_date
116 ,p_approval_comments => ' '
117 ,p_object_version_number => l_object_version_number
118 );
119 else
120 BEN_CWB_PERSON_GROUPS_API.update_group_budget
121 (
122 p_validate => false
123 ,p_group_per_in_ler_id => l_group_per_in_ler_id
124 ,p_group_pl_id => l_group_pl_id
125 ,p_group_oipl_id => l_group_oipl_id
126 ,p_approval_cd => 'AP'
127 ,p_approval_date => p_effective_date
128 ,p_approval_comments => ' '
129 ,p_object_version_number => l_object_version_number
130 );
131
132
133 end if;
134
135 -- Update task status to complete for all lower managers
136
137
138 open getApprTaskId(l_group_per_in_ler_id);
139 fetch getApprTaskId into l_task_id;
140 close getApprTaskId;
141
142 open getTaskObjVerNum(l_group_per_in_ler_id,l_task_id);
143 fetch getTaskObjVerNum into l_object_version_number_task , l_task_status;
144 --fetch getTaskObjVerNum.status into l_task_status;
145 close getTaskObjVerNum;
146
147 if(l_task_status <> 'CO') then
148
149 --hr_utility.set_location('l_task_id '||l_task_id ,60);
150 --hr_utility.set_location('p_login_person_id '||p_login_person_id ,70);
151
152 BEN_CWB_PERSON_TASKS_API.update_person_task
153 (
154 p_validate => false
155 ,p_group_per_in_ler_id => l_group_per_in_ler_id
156 ,p_group_pl_id => l_group_pl_id
157 ,p_task_id => l_task_id
158 ,p_status_cd => 'CO'
159 ,p_object_version_number => l_object_version_number_task
160 ,p_task_last_update_date => p_effective_date
161 ,p_task_last_update_by => p_login_person_id
162 );
163 end if;
164
165 end loop;
166
167
168 hr_utility.set_location('Leaving '||l_package ,30);
169 --hr_utility.trace_off;
170
171 EXCEPTION
172 when others then
173 l_error:=fnd_message.get;
174 hr_utility.set_location ('exception is'||l_error , 300);
175
176 end approve_all_managers;
177
178
179 -- ----------------------------------------------------------------------------
180 -- |-------------------------< getNextApprover >---------------------|
181 -- ----------------------------------------------------------------------------
182
183 -- Next Approve Name used in self-service
184
185
186 procedure getNextApprover(p_per_in_ler_id in number,
187 p_ben_cwb_profile_disp_name in varchar2,
188 p_approver_name out nocopy varchar2,
189 p_approver_id out nocopy number,
190 p_last_approver_name out nocopy varchar2) is
191
192 cursor get_approval_status is
193 select approval_cd
194 from ben_cwb_person_groups
195 where group_per_in_ler_id= p_per_in_ler_id;
196
197 cursor get_name(c_per_in_ler_id in number) is
198 select
199 decode(p_ben_cwb_profile_disp_name,'FN',full_name,'CN',custom_name,brief_name) manager_name
200 from ben_cwb_person_info
201 where ben_cwb_person_info.group_per_in_ler_id = c_per_in_ler_id;
202
203 cursor get_manager_name(c_per_in_ler_id in number) is
204 select
205 decode(p_ben_cwb_profile_disp_name,'FN',full_name,'CN',custom_name,brief_name) manager_name ,
206 person_id
207 from ben_cwb_group_hrchy,
208 ben_cwb_person_info
209 where ben_cwb_group_hrchy.emp_per_in_ler_id = c_per_in_ler_id
210 and lvl_num=1
211 and ben_cwb_person_info.group_per_in_ler_id = ben_cwb_group_hrchy.mgr_per_in_ler_id;
212
213 cursor get_next_approver is
214 select mgr_per_in_ler_id
215 from
216 ben_cwb_group_hrchy,
217 ben_cwb_person_groups
218 where
219 ben_cwb_group_hrchy.emp_per_in_ler_id = p_per_in_ler_id
220 and ben_cwb_group_hrchy.mgr_per_in_ler_id = ben_cwb_person_groups.group_per_in_ler_id
221 and group_oipl_id=-1
222 and ((approval_cd is null ) or (approval_cd = 'RJ' ))
223 and lvl_num <> -1
224 order by lvl_num;
225
226 l_next_approver_name varchar2(480):=null;
227 l_last_approver_name varchar2(480):=null;
228 l_next_approver_id number:=-1;
229 l_approval_cd varchar2(5);
230 l_per_in_ler_id number := -1 ;
231 l_last_appr varchar2(100);
232
233
234 begin
235
236 -- fetch highest approver
237
238 fnd_message.set_name('BEN','BEN_92969_CWB_LAST_APPR');
239 l_last_appr:=fnd_message.get;
240
241 -- First check approval status of manager, if it is null or rejected
242 -- then supervisor is next approver
243
244 open get_approval_status;
245 fetch get_approval_status into l_approval_cd;
246 close get_approval_status;
247
248 if ((l_approval_cd is null ) or (l_approval_cd = 'RJ')) then
249 open get_manager_name(p_per_in_ler_id);
250 fetch get_manager_name into l_next_approver_name,l_next_approver_id;
251 close get_manager_name;
252 if l_next_approver_name is null then
253 p_approver_name:=l_last_appr;
254 p_approver_id := -1;
255 else
256 p_approver_name:=l_next_approver_name;
257 p_approver_id := l_next_approver_id;
258 end if;
259 return;
260 end if;
261
262 -- Find for a manager in hrchy whose status is null or rejected , that managers supervisor will
263 -- be the approver.
264
265 open get_next_approver;
266 fetch get_next_approver into l_per_in_ler_id;
267 close get_next_approver;
268
269 if (l_per_in_ler_id <> -1 ) then
270
271 open get_manager_name(l_per_in_ler_id);
272 fetch get_manager_name into l_next_approver_name,l_next_approver_id;
273 close get_manager_name;
274
275 open get_name(l_per_in_ler_id);
276 fetch get_name into l_last_approver_name;
277 close get_name;
278
279 p_last_approver_name := l_last_approver_name;
280
281 if l_next_approver_name is null then
282 p_approver_name:=l_last_appr;
283 p_approver_id := -1;
284 else
285 p_approver_name:=l_next_approver_name;
286 p_approver_id := l_next_approver_id;
287 end if;
288
289 end if;
290
291 end getNextApprover;
292
293
294
295 END BEN_CWB_APPROVALS;