[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_REFRESH_PKG
Source
1 package body BEN_CWB_REFRESH_PKG as
2 /* $Header: bencwbrf.pkb 120.8.12010000.1 2008/07/29 12:07:42 appldev ship $ */
3 --
4 -- --------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- --------------------------------------------------------------------------
7 --
8 g_package varchar2(33):=' ben_cwb_refresh_pkg.'; --Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 -- --------------------------------------------------------------------------
12 -- |------------------------------< refresh >-------------------------------|
13 -- --------------------------------------------------------------------------
14 -- Description
15 -- This procedure contains calls for refreshing person_info, pl_dsgn,
16 -- summary and consolidation of summary. This will be called by a concurent
17 -- process.
18 procedure refresh(errbuf out nocopy varchar2
19 ,retcode out nocopy number
20 ,p_group_pl_id number
21 ,p_lf_evt_ocrd_dt varchar2
22 ,p_effective_date varchar2 default null
23 ,p_refresh_summary_flag varchar2
24 ,p_refresh_person_info_flag varchar2
25 ,p_refresh_pl_dsgn_flag varchar2
26 ,p_consolidate_summary_flag varchar2
27 ,p_init_rank varchar2
28 ,p_refresh_xchg varchar2
29 ,p_refresh_rate_from_rule varchar2 default 'N')
30 is
31
32 cursor c_formula_inputs(p_group_pl_id number,p_lf_evt_ocrd_dt date)
33 is
34 select
35 dsgn.pl_id
36 ,dsgn.oipl_id
37 ,ws_abr_id
38 ,rates.group_per_in_ler_id
39 ,enrt.rt_strt_dt_rl
40 ,enrt.rt_strt_dt_cd
41 ,rates.person_id
42 ,enrt.business_group_id
43 ,rates.object_version_number
44 from
45 ben_cwb_pl_dsgn dsgn
46 ,ben_cwb_person_rates rates
47 ,ben_popl_enrt_typ_cycl_f popl
48 ,ben_cwb_person_info info
49 ,ben_enrt_perd enrt
50 where dsgn.group_pl_id = p_group_pl_id
51 and dsgn.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
52 and info.group_per_in_ler_id = rates.group_per_in_ler_id
53 and nvl(info.post_process_stat_cd,'N') <> 'PR'
54 and dsgn.ws_abr_id is not null
55 and rates.group_pl_id = dsgn.group_pl_id
56 and rates.group_oipl_id = dsgn.group_oipl_id
57 and rates.pl_id = dsgn.pl_id
58 and rates.oipl_id = dsgn.oipl_id
59 and rates.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
60 and popl.pl_id = dsgn.pl_id
61 and enrt.asnd_lf_evt_dt = dsgn.lf_evt_ocrd_dt
62 and popl.popl_enrt_typ_cycl_id = enrt.popl_enrt_typ_cycl_id;
63 --and enrt.rt_strt_dt_rl is not null;
64
65 cursor c_ranking_info
66 is
67 select assignment_extra_info_id
68 ,object_version_number
69 from per_assignment_extra_info xtra_info
70 where xtra_info.information_type = 'CWBRANK'
71 and xtra_info.aei_information3 IS NULL;
72
73 cursor csr_person_ids(p_group_pl_id number
74 ,p_lf_evt_ocrd_dt date) is
75 select distinct(person_id) person_id
76 from ben_cwb_summary
77 where status = 'P'
78 and group_pl_id = p_group_pl_id
79 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
80 --
81 l_effective_date date;
82 l_lf_evt_ocrd_dt date;
83 --
84 l_proc varchar2(72) := g_package||'refresh';
85 l_ranks_initialised number;
86 --
87 l_returned_date date;
88 l_commit number;
89 l_object_version_number number;
90 --
91 begin
92 --
93 if g_debug then
94 hr_utility.set_location('Entering:'|| l_proc, 10);
95 end if;
96 --
97 l_effective_date :=trunc(to_date(p_effective_date,'yyyy/mm/dd hh24:mi:ss'));
98 l_lf_evt_ocrd_dt :=trunc(to_date(p_lf_evt_ocrd_dt,'yyyy/mm/dd hh24:mi:ss'));
99 l_ranks_initialised := 0;
100
101 if g_debug then
102 hr_utility.set_location('Group Pl Id :'||p_group_pl_id, 20);
103 hr_utility.set_location('Lf Evt Date :'||l_lf_evt_ocrd_dt, 20);
104 hr_utility.set_location('Eff Date :'||l_effective_date, 20);
105 hr_utility.set_location('Refresh Summary :'||p_refresh_summary_flag, 20);
106 hr_utility.set_location('Refresh Person Info :'||
107 p_refresh_person_info_flag, 20);
108 hr_utility.set_location('Refresh Pl Dsgn :'||p_refresh_pl_dsgn_flag, 20);
109 hr_utility.set_location('Consolidate Summary:'||
110 p_consolidate_summary_flag, 20);
111 hr_utility.set_location('Initialising Rankings:'||
112 p_init_rank, 20);
113 hr_utility.set_location('Refresh Exchange Rate:'||
114 p_refresh_xchg, 20);
115 end if;
116 --
117 --
118 if p_init_rank = 'Y' then
119 ben_batch_utils.WRITE('Initialising Rankings');
120 if g_debug then
121 hr_utility.set_location(l_proc, 21);
122 end if;
123 for l_ranking_info in c_ranking_info loop
124 hr_assignment_extra_info_api.delete_assignment_extra_info
125 (p_validate => false
126 ,p_assignment_extra_info_id => l_ranking_info.assignment_extra_info_id
127 ,p_object_version_number => l_ranking_info.object_version_number);
128 l_ranks_initialised := l_ranks_initialised + 1;
129 end loop;
130 commit;
131 ben_batch_utils.WRITE ('Number of Employees with Rankings Initialized: '||l_ranks_initialised);
132 end if;
133 --
134 if p_refresh_pl_dsgn_flag = 'Y' then
135 --
136 ben_batch_utils.WRITE('Refreshing Plan Design');
137 if g_debug then
138 hr_utility.set_location(l_proc, 30);
139 end if;
140 --
141 ben_cwb_pl_dsgn_pkg.refresh_pl_dsgn
142 (p_group_pl_id => p_group_pl_id
143 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
144 ,p_effective_date => l_effective_date
145 ,p_refresh_always => 'Y');
146 commit;
147 --
148 if g_debug then
149 hr_utility.set_location(l_proc, 40);
150 end if;
151 --
152 end if;
153 --
154 begin
155 savepoint before_refresh_xchg;
156 if p_refresh_xchg = 'Y' then
157 ben_batch_utils.WRITE('Initialising Refresh');
158 if g_debug then
159 hr_utility.set_location(l_proc, 21);
160 end if;
161 ben_cwb_xchg_pkg.insert_into_ben_cwb_xchg(p_group_pl_id
162 ,l_lf_evt_ocrd_dt
163 ,l_effective_date
164 ,'Y'
165 );
166 commit;
167 ben_batch_utils.WRITE('Completed Refresh');
168 end if;
169 exception
170 when others then
171 ben_batch_utils.WRITE(SQLERRM);
172 ben_batch_utils.WRITE('Exchange Rate refresh failed');
173 rollback to before_refresh_xchg;
174 end;
175 --
176 if g_debug then
177 hr_utility.set_location(l_proc, 50);
178 end if;
179 --
180 if p_refresh_person_info_flag = 'Y' then
181 --
182 ben_batch_utils.WRITE('Refreshing Person Info');
183 if g_debug then
184 hr_utility.set_location(l_proc, 60);
185 end if;
186 --
187 ben_cwb_person_info_pkg.refresh_person_info_group_pl
188 (p_group_pl_id => p_group_pl_id
189 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
190 ,p_effective_date => l_effective_date);
191 --
192 commit;
193 --
194 if g_debug then
195 hr_utility.set_location(l_proc, 70);
196 end if;
197 --
198 end if;
199 --
200 if g_debug then
201 hr_utility.set_location(l_proc, 80);
202 end if;
203 --
204 if p_refresh_summary_flag = 'Y' then
205 --
206 ben_batch_utils.WRITE('Refreshing Summary');
207 if g_debug then
208 hr_utility.set_location(l_proc, 90);
209 end if;
210 --
211 ben_cwb_summary_pkg.refresh_summary_group_pl
212 (p_group_pl_id => p_group_pl_id
213 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt);
214 --
215 commit;
216 --
217 if g_debug then
218 hr_utility.set_location(l_proc, 100);
219 end if;
220 --
221 end if;
222 --
223 if g_debug then
224 hr_utility.set_location(l_proc, 110);
225 end if;
226 --
227 -- if the summary is refreshed then there is no need to consolidate the
228 -- summary
229 if p_consolidate_summary_flag = 'Y' and p_refresh_summary_flag <> 'Y' then
230
231 ben_batch_utils.WRITE('Consolidating Summary');
232 --
233 if g_debug then
234 hr_utility.set_location(l_proc, 120);
235 end if;
236 --
237 for rec in csr_person_ids(p_group_pl_id,l_lf_evt_ocrd_dt) loop
238 --
239 if g_debug then
240 hr_utility.set_location(l_proc, 130);
241 end if;
242 --
243 ben_cwb_summary_pkg.consolidate_summary_rec
244 (p_person_id => rec.person_id);
245 end loop;
246 commit;
247 --
248 if g_debug then
249 hr_utility.set_location(l_proc, 140);
250 end if;
251 --
252 end if;
253 --
254 if p_refresh_rate_from_rule = 'Y' then
255 ben_batch_utils.WRITE('Refreshing worksheet rate start date');
256 --
257 -- Put row in fnd_sessions
258 --
259 dt_fndate.change_ses_date
260 (p_ses_date => l_effective_date,
261 p_commit => l_commit);
262 ben_batch_utils.WRITE ('Changing Session Date: '||l_effective_date);
263 ben_batch_utils.WRITE ('Commit on date : '||l_commit);
264
265 for rec in c_formula_inputs(p_group_pl_id,l_lf_evt_ocrd_dt) loop
266
267 l_object_version_number := rec.object_version_number;
268
269 if rec.rt_strt_dt_cd = 'RL' AND rec.rt_strt_dt_rl is not null then
270 BEN_DETERMINE_DATE.main
271 (
272 p_date_cd => 'RL'
273 ,p_per_in_ler_id => rec.group_per_in_ler_id
274 ,p_person_id => rec.person_id
275 ,p_pl_id => rec.pl_id
276 ,p_oipl_id => rec.oipl_id
277 ,p_business_group_id => rec.business_group_id
278 ,p_formula_id => rec.rt_strt_dt_rl
279 ,p_acty_base_rt_id => rec.ws_abr_id
280 ,p_effective_date => l_effective_date
281 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
282 ,p_returned_date => l_returned_date
283 );
284 -- ben_batch_utils.WRITE(rec.group_per_in_ler_id||' '||l_returned_date||' '||rec.oipl_id);
285 if g_debug then
286 hr_utility.set_location(' p_returned_date:'|| l_returned_date, 11);
287 hr_utility.set_location(' p_per_in_ler_id:'|| rec.group_per_in_ler_id, 22);
288 end if;
289
290 BEN_CWB_PERSON_RATES_API.update_person_rate(
291 p_group_per_in_ler_id => rec.group_per_in_ler_id
292 ,p_pl_id => rec.pl_id
293 ,p_oipl_id => rec.oipl_id
294 ,p_ws_rt_start_date => l_returned_date
295 ,p_object_version_number => l_object_version_number
296 );
297
298 elsif REC.RT_STRT_DT_CD = 'ENTRBL' then
299
300 BEN_CWB_PERSON_RATES_API.update_person_rate(
301 p_group_per_in_ler_id => rec.group_per_in_ler_id
302 ,p_pl_id => rec.pl_id
303 ,p_oipl_id => rec.oipl_id
304 ,p_ws_rt_start_date => null
305 ,p_object_version_number => l_object_version_number
306 );
307 end if;
308
309 end loop;
310
311 --
312 commit;
313 --
314 end if;
315
316 if g_debug then
317 hr_utility.set_location(' Leaving:'|| l_proc, 999);
318 end if;
319 --
320 exception
321 when others then
322 ben_batch_utils.WRITE(SQLERRM);
323 ben_batch_utils.WRITE('Process Errored : Rolled Back');
324 ROLLBACK;
325
326 end refresh;
327
328 end BEN_CWB_REFRESH_PKG;
329