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