1 package body BEN_CWB_XCHG_PKG as
2 /* $Header: bencwbxchg.pkb 120.8.12000000.1 2007/01/19 15:39:41 appldev noship $ */
3 --
4 -- --------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- --------------------------------------------------------------------------
7 --
8 g_package varchar2(33):=' ben_cwb_xchg_pkb.'; --Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10 g_xchg_not_found varchar2(1) := 'N'; --global var to chk xchg rate
11 -- --------------------------------------------------------------------------
12 -- |----------------------< insert_into_ben_cwb_xchg >----------------------|
13 -- --------------------------------------------------------------------------
14 -- Description
15 -- This procedure inserts currency records in the the ben_cwb_xchg table on
16 -- participation process run as well on refresh.
17 -- Input parameters
18 -- p_group_pl_id : Group Plan Id
19 -- p_lf_evt_ocrd_dt : Life Event Occured Date
20 -- p_effective_date : Effective Date
21 -- p_refresh_always : Refresh Always flag
22 procedure insert_into_ben_cwb_xchg(p_group_pl_id IN number,
23 p_lf_evt_ocrd_dt IN date,
24 p_effective_date IN date,
25 p_refresh_always IN varchar2 default 'N',
26 p_currency IN varchar2 default null,
27 p_xchg_rate IN number default null) IS
28 --
29 -- cursor for fetching Bg_ID, Effective_Date and Pl_UOM for exchange rate Calculation
30 cursor csr_pl_dsgn_recs(p_group_pl_id number,
31 p_lf_evt_ocrd_dt date,
32 p_effective_date date) is
33 select pl_uom
34 ,nvl(p_effective_date, nvl(data_freeze_date, lf_evt_ocrd_dt))
35 ,business_group_id
36 from ben_cwb_pl_dsgn
37 where pl_id = p_group_pl_id
38 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
39 and oipl_id = -1;
40
41 --
42 -- cursor to fetch the records from person_info and rates where personId is -1
43 cursor csr_xchg_recs_bm(p_group_pl_id number, p_lf_evt_ocrd_dt date) is
44 select distinct base_salary_currency lCurrency
45 from ben_cwb_person_info
46 where group_pl_id = p_group_pl_id
47 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
48 and person_id = -1
49 and base_salary_currency is not null
50
51 UNION
52 select distinct salary_1_year_ago_currency lCurrency
53 from ben_cwb_person_info
54 where group_pl_id = p_group_pl_id
55 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
56 and person_id = -1
57 and salary_1_year_ago_currency is not null
58
59 UNION
60 select distinct salary_2_year_ago_currency lCurrency
61 from ben_cwb_person_info
62 where group_pl_id = p_group_pl_id
63 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
64 and person_id = -1
65 and salary_2_year_ago_currency is not null
66
67 UNION
68 select distinct salary_3_year_ago_currency lCurrency
69 from ben_cwb_person_info
70 where group_pl_id = p_group_pl_id
71 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
72 and person_id = -1
73 and salary_3_year_ago_currency is not null
74
75 UNION
76 select distinct salary_4_year_ago_currency lCurrency
77 from ben_cwb_person_info
78 where group_pl_id = p_group_pl_id
79 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
80 and person_id = -1
81 and salary_4_year_ago_currency is not null
82
83 UNION
84 select distinct salary_5_year_ago_currency lCurrency
85 from ben_cwb_person_info
86 where group_pl_id = p_group_pl_id
87 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
88 and person_id = -1
89 and salary_5_year_ago_currency is not null
90
91 UNION
92 select distinct mkt_currency lCurrency
93 from ben_cwb_person_info
94 where group_pl_id = p_group_pl_id
95 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
96 and person_id = -1
97 and mkt_currency is not null
98
99 UNION
100 select distinct prev_sal_currency lCurrency
101 from ben_cwb_person_info
102 where group_pl_id = p_group_pl_id
103 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
104 and person_id = -1
105 and prev_sal_currency is not null
106
107 UNION
108
109 select distinct cpr.currency lCurrency
110 from ben_cwb_person_rates cpr
111 ,ben_cwb_person_info cpi
112 where cpr.group_per_in_ler_id = cpi.group_per_in_ler_id
113 and cpi.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
114 and cpi.group_pl_id = p_group_pl_id
115 and cpi.person_id = -1
116 and cpr.currency is not null;
117
118 --
119 -- cursor to fetch the records from person_info and rates
120 -- for refresh
121 cursor csr_xchg_recs_rf(p_group_pl_id number, p_lf_evt_ocrd_dt date) is
122 select distinct base_salary_currency lCurrency
123 from ben_cwb_person_info
124 where group_pl_id = p_group_pl_id
125 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
126 and base_salary_currency is not null
127
128 UNION
129
130 select distinct cpr.currency lCurrency
131 from ben_cwb_person_rates cpr
132 where cpr.group_pl_id = p_group_pl_id
133 and cpr.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
134 and cpr.currency is not null
135
136 UNION
137 select distinct salary_1_year_ago_currency lCurrency
138 from ben_cwb_person_info
139 where group_pl_id = p_group_pl_id
140 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
141 and salary_1_year_ago_currency is not null
142
143 UNION
144 select distinct salary_2_year_ago_currency lCurrency
145 from ben_cwb_person_info
146 where group_pl_id = p_group_pl_id
147 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
148 and salary_2_year_ago_currency is not null
149
150 UNION
151 select distinct salary_3_year_ago_currency lCurrency
152 from ben_cwb_person_info
153 where group_pl_id = p_group_pl_id
154 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
155 and salary_3_year_ago_currency is not null
156
157 UNION
158 select distinct salary_4_year_ago_currency lCurrency
159 from ben_cwb_person_info
160 where group_pl_id = p_group_pl_id
161 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
162 and salary_4_year_ago_currency is not null
163
164 UNION
165 select distinct salary_5_year_ago_currency lCurrency
166 from ben_cwb_person_info
167 where group_pl_id = p_group_pl_id
168 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
169 and salary_5_year_ago_currency is not null
170
171 UNION
172 select distinct mkt_currency lCurrency
173 from ben_cwb_person_info
174 where group_pl_id = p_group_pl_id
175 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
176 and mkt_currency is not null
177
178 UNION
179 select distinct prev_sal_currency lCurrency
180 from ben_cwb_person_info
181 where group_pl_id = p_group_pl_id
182 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
183 and prev_sal_currency is not null;
184
185 --
186 l_pl_uom varchar2(30);
187 --
188 l_effective_date date;
189 --
190 l_bg_id number(15, 0);
191 --
192 l_currency varchar2(30);
193 --
194 l_xchg_rate number;
195 --
196 l_xchg_rec ben_cwb_xchg%rowtype;
197 --
198 l_proc varchar2(72) := g_package||'insert_into_ben_cwb_xchg';
199 --
200 begin
201 --
202 if g_debug then
203 hr_utility.set_location('Entering:'|| l_proc, 10);
204 end if;
205 --
206 -- for the group_plan record
207
208 open csr_pl_dsgn_recs(p_group_pl_id, p_lf_evt_ocrd_dt, p_effective_date);
209 fetch csr_pl_dsgn_recs into l_pl_uom, l_effective_date, l_bg_id;
210 close csr_pl_dsgn_recs;
211
212 --
213 -- check for refresh
214 if p_refresh_always = 'Y' then
215 --
216 if g_debug then
217 hr_utility.set_location('l_proc'|| l_proc, 20);
218 end if;
219 --
220 delete from ben_cwb_xchg
221 where group_pl_id = p_group_pl_id
222 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
223 --
224 for xchg_rec in csr_xchg_recs_rf(p_group_pl_id, p_lf_evt_ocrd_dt) loop
225 --
226 if g_debug then
227 hr_utility.set_location(l_proc, 25);
228 end if;
229 --
230 l_xchg_rec.currency := xchg_rec.lCurrency;
231 l_xchg_rec.group_pl_id := p_group_pl_id;
232 l_xchg_rec.lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
233 l_xchg_rec.xchg_rate := ben_cwb_pl_dsgn_pkg.get_exchg_rate(l_pl_uom
234 ,xchg_rec.lCurrency
235 ,l_effective_date
236 ,l_bg_id);
237 --check for rate to be null
238 if l_xchg_rec.xchg_rate is null then
239 g_xchg_not_found := 'Y';
240 end if;
241
242 --for each new currency records
243 insert into ben_cwb_xchg(
244 group_pl_id
245 ,lf_evt_ocrd_dt
246 ,currency
247 ,xchg_rate)
248 values(
249 l_xchg_rec.group_pl_id
250 ,l_xchg_rec.lf_evt_ocrd_dt
251 ,l_xchg_rec.currency
252 ,l_xchg_rec.xchg_rate
253 );
254 end loop;
255 elsif p_refresh_always = 'N' then
256 --when refresh is N
257 if g_debug then
258 hr_utility.set_location('l_proc'|| l_proc, 30);
259 end if;
260 --
261 ben_manage_cwb_life_events.g_error_log_rec.calling_proc :=
262 'Before xchg cursor';
263 ben_manage_cwb_life_events.g_error_log_rec.step_number := 61;
264 --
265 for xchg_rec in csr_xchg_recs_bm(p_group_pl_id, p_lf_evt_ocrd_dt) loop
266 --
267 if g_debug then
268 hr_utility.set_location(l_proc, 35);
269 end if;
270 --
271 l_xchg_rec.currency := xchg_rec.lCurrency;
272 l_xchg_rec.group_pl_id := p_group_pl_id;
273 l_xchg_rec.lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
274 l_xchg_rec.xchg_rate := ben_cwb_pl_dsgn_pkg.get_exchg_rate(l_pl_uom
275 ,xchg_rec.lCurrency
276 ,l_effective_date
277 ,l_bg_id);
278 --
279 --for each new currency records
280 begin
281 insert into ben_cwb_xchg(
282 group_pl_id
283 ,lf_evt_ocrd_dt
284 ,currency
285 ,xchg_rate)
286 values(
287 l_xchg_rec.group_pl_id
288 ,l_xchg_rec.lf_evt_ocrd_dt
289 ,l_xchg_rec.currency
290 ,l_xchg_rec.xchg_rate
291 );
292 exception
293 when others then
294 null;
295 end;
296 end loop;
297 ben_manage_cwb_life_events.g_error_log_rec.calling_proc :=
298 'After xchg cursor';
299 ben_manage_cwb_life_events.g_error_log_rec.step_number := 62;
300 --
301 elsif p_currency is not null then
302 --
303 --Insert a new exchg rate defined through plan Admin
304 l_currency := p_currency;
305 l_xchg_rate := p_xchg_rate;
306 --
307 if g_debug then
308 hr_utility.set_location('l_proc'|| l_proc, 40);
309 end if;
310
311 --
312 --if currency already exists
313 begin
314 delete from ben_cwb_xchg
315 where group_pl_id = p_group_pl_id
316 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
317 and currency = l_currency;
318 exception
319 when others then
320 null;
321 end;
322
323 --
324 --for each new currency record
325 begin
326 insert into ben_cwb_xchg(
327 group_pl_id
328 ,lf_evt_ocrd_dt
329 ,currency
330 ,xchg_rate)
331 values(
332 p_group_pl_id
333 ,p_lf_evt_ocrd_dt
334 ,l_currency
335 ,l_xchg_rate
336 );
337 exception
338 when others then
339 null;
340 end;
341
342 end if;
343 --
344 if g_debug then
345 hr_utility.set_location(' Leaving:'|| l_proc, 99);
346 end if;
347 --
348 end; -- of procedure insert_into_ben_cwb_xchg
349 --
350 --
351 -- --------------------------------------------------------------------------
352 -- |---------------------------< refresh_xchg_rates >-----------------------|
353 -- --------------------------------------------------------------------------
354 -- Description
355 -- This procedure will refresh the exchange rates in ben_cwb_xchg table on
356 -- a given effective date, and, return p_all_xchg_rt_exists as 'N' for any
357 -- rates found to be null.
358 -- Input parameters
359 -- p_group_pl_id : Group Plan Id
360 -- p_lf_evt_ocrd_dt : Life Event Occured Date
361 -- p_effective_date : Effective Date
362 -- p_refresh_always : Refresh Always flag
363 -- p_all_xchg_rt_exists : All Exchange Rates Exists
364 --
365 procedure refresh_xchg_rates(p_group_pl_id IN number,
366 p_lf_evt_ocrd_dt IN date,
367 p_effective_date IN date,
368 p_refresh_always IN varchar2 default 'N',
369 p_all_xchg_rt_exists IN OUT NOCOPY varchar2) IS
370 l_proc varchar2(72) := g_package||'refresh_xchg_rates';
371 begin
372 --
373 if g_debug then
374 hr_utility.set_location('Entering:'|| l_proc, 10);
375 end if;
376 --
377
378 --resetting value of global var
379 g_xchg_not_found := 'N';
380 --
381
382 --Calling procedure to refresh exchg rates
383 insert_into_ben_cwb_xchg(p_group_pl_id ,
384 p_lf_evt_ocrd_dt,
385 p_effective_date,
386 p_refresh_always,
387 null ,
388 null);
389
390 --
391 if g_debug then
392 hr_utility.set_location('l_proc'|| l_proc, 20);
393 end if;
394 --
395
396 --Check for any null exchg rate returned
397 if g_xchg_not_found = 'Y' then
398 p_all_xchg_rt_exists := 'N';
399 end if;
400 --
401
402 --
403 if g_debug then
404 hr_utility.set_location('Leaving:'|| l_proc, 99);
405 end if;
406 --
407 end; -- of procedure refresh_xchg_rates
408 end BEN_CWB_XCHG_PKG;
409
[Home] [Help]