1 Package Body ben_update_ledgers as
2 /* $Header: benbplup.pkb 120.2.12010000.2 2008/08/05 14:33:56 ubhat ship $ */
3
4 -- Package Variables
5 g_package varchar2(33) := ' ben_update_ledgers.';
6
7 -- ---------------------------------------------------------------------
8 -- main
9 -- ---------------------------------------------------------------------
10 procedure main is
11
12 l_proc varchar2(72) := g_package||'main';
13
14 -- if acty-ref-perd-cd is not null, we ran this against the row.
15 -- added used-val in where to pick up rows where prv row has different value
16 -- than bpl row (like where rollovers went to two pools for one rslt)
17 cursor c_ldgr is
18 select bpl.bnft_prvdd_ldgr_id, bpl.acty_base_rt_id, bpl.prtt_enrt_rslt_id,
19 bpl.business_group_id, bpl.effective_start_date, bpl.effective_end_date,
20 bpl.object_version_number, bpl.per_in_ler_id,
21 bpl.frftd_val, bpl.used_val, bpl.prvdd_val, bpl.cash_recd_val, bpl.rld_up_val
22 from ben_bnft_prvdd_ldgr_f bpl,
23 ben_per_in_ler pil,
24 ben_prtt_enrt_rslt_f pen
25 where (bpl.acty_ref_perd_cd is null or
26 (bpl.used_val is not null and bpl.cmcd_used_val is null))
27 and bpl.per_in_ler_id = pil.per_in_ler_id
28 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
29 and bpl.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
30 and pen.prtt_enrt_rslt_stat_cd is null
31 and bpl.effective_start_date between pen.effective_start_date
32 and pen.effective_end_date ;
33 l_ldgr c_ldgr%rowtype;
34 l_object_version_number number ;
35 --
36
37 l_cmcd_frftd_val number := null;
38 l_cmcd_prvdd_val number := null;
39 l_cmcd_rld_up_val number := null;
40 l_cmcd_used_val number := null;
41 l_cmcd_cash_recd_val number := null;
42 l_ann_frftd_val number := null;
43 l_ann_prvdd_val number := null;
44 l_ann_rld_up_val number := null;
45 l_ann_used_val number := null;
46 l_ann_cash_recd_val number := null;
47 l_acty_ref_perd_cd varchar2(30) := null;
48 l_cmcd_ref_perd_cd varchar2(30) := null;
49
50 l_datetrack_mode varchar2(30);
51 test_ldgr_id number;
52 BEGIN
53 hr_utility.set_location('Entering '||l_proc, 10);
54
55 /* new columns being loaded:
56 ACTY_REF_PERD_CD VARCHAR2(30)
57 CMCD_FRFTD_VAL NUMBER
58 CMCD_PRVDD_VAL NUMBER
59 CMCD_RLD_UP_VAL NUMBER
60 CMCD_USED_VAL NUMBER
61 CMCD_CASH_RECD_VAL NUMBER
62 CMCD_REF_PERD_CD VARCHAR2(30)
63 ANN_FRFTD_VAL NUMBER
64 ANN_PRVDD_VAL NUMBER
65 ANN_RLD_UP_VAL NUMBER
66 ANN_USED_VAL NUMBER
67 ANN_CASH_RECD_VAL
68 */
69
70 for l_ldgr in c_ldgr loop
71 hr_utility.set_location('This result is: ', 20);
72
73 l_acty_ref_perd_cd := null;
74 l_cmcd_ref_perd_cd := null;
75 l_cmcd_frftd_val := null;
76 l_cmcd_prvdd_val := null;
77 l_cmcd_rld_up_val := null;
78 l_cmcd_used_val := null;
79 l_cmcd_cash_recd_val := null;
80 l_ann_frftd_val := null;
81 l_ann_prvdd_val := null;
82 l_ann_rld_up_val := null;
83 l_ann_used_val := null;
84 l_ann_cash_recd_val := null;
85
86 get_cmcd_ann_values
87 (p_bnft_prvdd_ldgr_id => l_ldgr.bnft_prvdd_ldgr_id,
88 p_acty_base_rt_id => l_ldgr.acty_base_rt_id,
89 p_prtt_enrt_rslt_id => l_ldgr.prtt_enrt_rslt_id,
90 p_business_group_id => l_ldgr.business_group_id,
91 p_effective_start_date => l_ldgr.effective_start_date,
92 p_per_in_ler_id => l_ldgr.per_in_ler_id,
93 p_frftd_val => l_ldgr.frftd_val,
94 p_used_val => l_ldgr.used_val,
95 p_prvdd_val => l_ldgr.prvdd_val,
96 p_cash_recd_val => l_ldgr.cash_recd_val,
97 p_rld_up_val => l_ldgr.rld_up_val,
98 p_acty_ref_perd_cd => l_acty_ref_perd_cd, -- beginning of out parms
99 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
100 p_cmcd_frftd_val => l_cmcd_frftd_val,
101 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
102 p_cmcd_rld_up_val => l_cmcd_rld_up_val,
103 p_cmcd_used_val => l_cmcd_used_val,
104 p_cmcd_cash_recd_val => l_cmcd_cash_recd_val,
105 p_ann_frftd_val => l_ann_frftd_val,
106 p_ann_prvdd_val => l_ann_prvdd_val,
107 p_ann_rld_up_val => l_ann_rld_up_val,
108 p_ann_used_val => l_ann_used_val,
109 p_ann_cash_recd_val => l_ann_cash_recd_val);
110
111 -- Now update the ledger columns if we found something to update them with:
112 if l_cmcd_frftd_val is not null or l_cmcd_prvdd_val is not null or
113 l_cmcd_rld_up_val is not null or l_cmcd_used_val is not null or
114 l_cmcd_cash_recd_val is not null then
115 /* get_dt_mode
116 (p_effective_date => l_ldgr.effective_start_date,
117 p_base_key_value => l_ldgr.bnft_prvdd_ldgr_id,
118 p_mode => l_datetrack_mode);
119 */
120 --
121 --
122 hr_utility.set_location('Updating ledger. Id='||to_char(l_ldgr.bnft_prvdd_ldgr_id),22);
123 begin
124 ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
125 p_bnft_prvdd_ldgr_id => l_ldgr.bnft_prvdd_ldgr_id
126 ,p_effective_start_date => l_ldgr.effective_start_date
127 ,p_effective_end_date => l_ldgr.effective_end_date
128 ,p_acty_ref_perd_cd => l_acty_ref_perd_cd
129 ,p_cmcd_frftd_val => l_cmcd_frftd_val
130 ,p_cmcd_prvdd_val => l_cmcd_prvdd_val
131 ,p_cmcd_rld_up_val => l_cmcd_rld_up_val
132 ,p_cmcd_used_val => l_cmcd_used_val
133 ,p_cmcd_cash_recd_val => l_cmcd_cash_recd_val
134 ,p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd
135 ,p_ann_frftd_val => l_ann_frftd_val
136 ,p_ann_prvdd_val => l_ann_prvdd_val
137 ,p_ann_rld_up_val => l_ann_rld_up_val
138 ,p_ann_used_val => l_ann_used_val
139 ,p_ann_cash_recd_val => l_ann_cash_recd_val
140 ,p_object_version_number => l_ldgr.object_version_number
141 ,p_effective_date => l_ldgr.effective_start_date
142 ,p_datetrack_mode => 'CORRECTION');
143 hr_utility.set_location('Updated Ledger. Id='||to_char(l_ldgr.bnft_prvdd_ldgr_id),24);
144 exception
145 when others then
146 --continue leaving the errored record
147 null;
148 end;
149 end if;
150
151 end loop;
152
153 hr_utility.set_location('Leaving '||l_proc, 999);
154
155 END; -- main
156 -- ---------------------------------------------------------------------
157 -- get_cmcd_ann_values
158 -- ---------------------------------------------------------------------
159 procedure get_cmcd_ann_values
160 (p_bnft_prvdd_ldgr_id in number default null,
161 p_acty_base_rt_id in number,
162 p_prtt_enrt_rslt_id in number,
163 p_business_group_id in number,
164 p_effective_start_date in date,
165 p_per_in_ler_id in number,
166 p_frftd_val in number,
167 p_used_val in number,
168 p_prvdd_val in number,
169 p_cash_recd_val in number,
170 p_rld_up_val in number,
171 p_acty_ref_perd_cd out nocopy varchar2,
172 p_cmcd_ref_perd_cd out nocopy varchar2,
173 p_cmcd_frftd_val out nocopy number,
174 p_cmcd_prvdd_val out nocopy number,
175 p_cmcd_rld_up_val out nocopy number,
176 p_cmcd_used_val out nocopy number,
177 p_cmcd_cash_recd_val out nocopy number,
178 p_ann_frftd_val out nocopy number,
179 p_ann_prvdd_val out nocopy number,
180 p_ann_rld_up_val out nocopy number,
181 p_ann_used_val out nocopy number,
182 p_ann_cash_recd_val out nocopy number) is
183
184 l_proc varchar2(72) := g_package||'get_cmcd_ann_values';
185 cursor c_ldgr(c_bnft_prvdd_ldgr_id in number,
186 c_effective_date in date) is
187 select bpl.acty_base_rt_id, bpl.prtt_enrt_rslt_id,
188 bpl.business_group_id, bpl.per_in_ler_id
189 from ben_bnft_prvdd_ldgr_f bpl
190 where bpl.bnft_prvdd_ldgr_id = c_bnft_prvdd_ldgr_id
191 and c_effective_date between
192 bpl.effective_start_date and bpl.effective_end_date;
193 l_ldgr c_ldgr%rowtype;
194 l_ldgr_parms c_ldgr%rowtype;
195
196 -- This is the DUMMY flex credit row. ALL ledgers hang off this.
197 cursor c_rslt (c_prtt_enrt_rslt_id in number,
198 c_effective_date in date) is
199 select distinct pen.person_id, pen.pgm_id,
200 pgm.acty_ref_perd_cd, pgm.enrt_info_rt_freq_cd
201 from ben_prtt_enrt_rslt_f pen, ben_pgm_f pgm
202 where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
203 and pen.pgm_id = pgm.pgm_id
204 and pen.prtt_enrt_rslt_stat_cd is null
205 and c_effective_date between
206 pen.effective_start_date and pen.effective_end_date
207 and c_effective_date between
208 pgm.effective_start_date and pgm.effective_end_date;
209 l_rslt c_rslt%rowtype;
210 /*
211 cursor c_person (c_person_id in number,
212 c_effective_date in date) is
213 select distinct asg.payroll_id
214 from per_all_assignments_f asg
215 where asg.person_id = c_person_id
216 and asg.assignment_type <> 'C'
217 and asg.primary_flag = 'Y'
218 and c_effective_date between
219 asg.effective_start_date and asg.effective_end_date;
220 l_person c_person%rowtype;
221 */
222
223 -- for used rates, there is no direct link from the ldgr to the person's result's
224 -- rate. We have to join on acty-base-rt-id to prtt-rt-val, then from prtt-rt-val
225 -- to check that we have the right person's result.
226 cursor c_used_rate (c_acty_base_rt_id in number,
227 c_person_id in number,
228 c_acty_ref_perd_cd in varchar2,
229 c_enrt_info_rt_freq_cd in varchar2,
230 c_effective_date in date,
231 c_used_val in number)is
232 select distinct ann_rt_val, cmcd_rt_val
233 from ben_prtt_rt_val prv, ben_prtt_enrt_rslt_f pen
234 where prv.acty_base_rt_id = c_acty_base_rt_id
235 -- make sure we're dealing with the exact same rate
236 and prv.rt_val = c_used_val
237 and prv.acty_ref_perd_cd = c_acty_ref_perd_cd
238 and prv.cmcd_ref_perd_cd = c_enrt_info_rt_freq_cd
239 -- make sure the rate is for our person
240 and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
241 and pen.person_id = c_person_id
242 and pen.prtt_enrt_rslt_stat_cd is null
243 and c_effective_date between
244 pen.effective_start_date and pen.effective_end_date;
245 l_used_rate c_used_rate%rowtype;
246
247 -- for providded rates, we can go right from the result-id on the ldgr to
248 -- that result for the person.
249 cursor c_prvdd_rate (c_prtt_enrt_rslt_id in number,
250 c_acty_ref_perd_cd in varchar2,
251 c_enrt_info_rt_freq_cd in varchar2,
252 c_effective_date in date,
253 c_prvdd_val in number)is
254 select distinct ann_rt_val, cmcd_rt_val
255 from ben_prtt_rt_val prv
256 where prv.rt_val = c_prvdd_val
257 and prv.acty_ref_perd_cd = c_acty_ref_perd_cd
258 and prv.cmcd_ref_perd_cd = c_enrt_info_rt_freq_cd
259 and prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id;
260 l_prvdd_rate c_prvdd_rate%rowtype;
261 -- and if we can't find the prvdd rate in the result table, we'll look in the
262 -- enrt-rt table.
263 cursor c_prvdd_rate2 (c_acty_base_rt_id in number,
264 c_person_id in number,
265 c_per_in_ler_id in number,
266 c_acty_ref_perd_cd in varchar2,
267 c_enrt_info_rt_freq_cd in varchar2,
268 c_effective_date in date,
269 c_prvdd_val in number)is
270 select distinct ann_val, cmcd_val
271 from ben_enrt_rt ecr, ben_elig_per_elctbl_chc epe, ben_per_in_ler pil,
272 ben_pil_elctbl_chc_popl pel
273 where ecr.acty_base_rt_id = c_acty_base_rt_id
274 -- make sure we're dealing with the exact same rate
275 and ecr.val = c_prvdd_val
276 and pel.acty_ref_perd_cd = c_acty_ref_perd_cd
277 and ecr.cmcd_acty_ref_perd_cd = c_enrt_info_rt_freq_cd
278 -- make sure the rate is for our person
279 and ecr.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
280 and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
281 and epe.per_in_ler_id = c_per_in_ler_id
282 and pel.per_in_ler_id = pil.per_in_ler_id
283 and pil.person_id = c_person_id;
284 l_prvdd_rate2 c_prvdd_rate2%rowtype;
285
286 -- for cash and forfeited rates, we calc the value from the enrt-rt for the
287 -- abr on the ldgr
288 cursor c_choice_data (c_acty_base_rt_id in number,
289 c_person_id in number,
290 c_per_in_ler_id in number,
291 c_acty_ref_perd_cd in varchar2,
292 c_enrt_info_rt_freq_cd in varchar2,
293 c_effective_date in date)is
294 select distinct epe.elig_per_elctbl_chc_id, ecr.enrt_rt_id, pil.lf_evt_ocrd_dt
295 from ben_enrt_rt ecr, ben_elig_per_elctbl_chc epe, ben_per_in_ler pil,
296 ben_pil_elctbl_chc_popl pel, ben_enrt_bnft enb
297 where ecr.acty_base_rt_id = c_acty_base_rt_id
298 -- make sure we're dealing with the exact same rate
299 and pel.acty_ref_perd_cd = c_acty_ref_perd_cd
300 and ecr.cmcd_acty_ref_perd_cd = c_enrt_info_rt_freq_cd
301 -- make sure the rate is for our person
302 and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
303 and (epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id or
304 enb.enrt_bnft_id = ecr.enrt_bnft_id)
305 and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
306 and epe.per_in_ler_id = c_per_in_ler_id
307 and pel.per_in_ler_id = pil.per_in_ler_id
308 and pil.person_id = c_person_id;
309 l_choice_data c_choice_data%rowtype;
310 --
311 -- Bug No 4538786 Added cursor to fetch the rate and element id
312 -- of the Flex Shell plan instead of the flex credits
313 --
314 cursor get_flex_shell_rt(c_per_in_ler_id in number,
315 c_pgm_id in number) is
316 select ecr.enrt_rt_id, abr.acty_base_rt_id, abr.element_type_id,
317 epe.elig_per_elctbl_chc_id , pil.lf_evt_ocrd_dt
318 from ben_enrt_rt ecr, ben_elig_per_elctbl_chc epe, ben_per_in_ler pil,
319 ben_acty_base_rt_f abr
320 where epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
321 and epe.pgm_id = c_pgm_id
322 and epe.comp_lvl_cd = 'PLANFC'
323 and pil.per_in_ler_id = epe.per_in_ler_id
324 and epe.per_in_ler_id = c_per_in_ler_id
325 and ecr.acty_base_rt_id = abr.acty_base_rt_id;
326 l_flex_shell_rt get_flex_shell_rt%rowtype;
327 --
328 -- End Bug No 4538786
329 --
330 --GEVITY
331 cursor c_abr(cv_acty_base_rt_id number)
332 is select rate_periodization_rl
333 from ben_acty_base_rt_f abr
334 where abr.acty_base_rt_id = cv_acty_base_rt_id
335 and p_effective_start_date between abr.effective_start_date
336 and abr.effective_end_date ;
337 --
338 l_rate_periodization_rl NUMBER;
339 --
340 l_dfnd_dummy number;
341 l_ann_dummy number;
342 l_cmcd_dummy number;
343 l_assignment_id per_all_assignments_f.assignment_id%type;
344 l_payroll_id per_all_assignments_f.payroll_id%type;
345 l_organization_id per_all_assignments_f.organization_id%type;
346 --END GEVITY
347 begin
348 hr_utility.set_location('Entering '||l_proc, 10);
349 -- init all the out parms
350 p_acty_ref_perd_cd := null;
351 p_cmcd_ref_perd_cd := null;
352 p_cmcd_frftd_val := null;
353 p_cmcd_prvdd_val := null;
354 p_cmcd_rld_up_val := null;
355 p_cmcd_used_val := null;
356 p_cmcd_cash_recd_val := null;
357 p_ann_frftd_val := null;
358 p_ann_prvdd_val := null;
359 p_ann_rld_up_val := null;
360 p_ann_used_val := null;
361 p_ann_cash_recd_val := null;
362 --
363 -- When updating ledgers, the api may not get all the parms passed in, hence
364 -- we might not either. Go get the ones we need.
365 if p_prtt_enrt_rslt_id is null or p_acty_base_rt_id is null or
366 p_per_in_ler_id is null or p_business_group_id is null then
367 hr_utility.set_location('Have to find out nocopy ledger parms:'||
368 to_char(p_bnft_prvdd_ldgr_id), 20);
369 open c_ldgr(c_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id,
370 c_effective_date => p_effective_start_date);
371 fetch c_ldgr into l_ldgr;
372 if c_ldgr%NOTFOUND or c_ldgr%NOTFOUND is null then
373 -- if we can't find this info, we can't find the data we need.
374 hr_utility.set_location('LEDGER INFO NOT FOUND. LEDGER ID='||
375 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
376 to_char(p_effective_start_date)||' RESULT ID='||
377 to_char(p_prtt_enrt_rslt_id)||' ',22);
378 close c_ldgr;
379 return; --<-------------------------------------
380 end if;
381 close c_ldgr;
382 hr_utility.set_location('Found ledger parms',24);
383 end if;
384 if p_prtt_enrt_rslt_id is null then
385 l_ldgr_parms.prtt_enrt_rslt_id := l_ldgr.prtt_enrt_rslt_id;
386 else
387 l_ldgr_parms.prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
388 end if;
389 if p_prtt_enrt_rslt_id is null then
390 l_ldgr_parms.acty_base_rt_id := l_ldgr.acty_base_rt_id;
391 else
392 l_ldgr_parms.acty_base_rt_id := p_acty_base_rt_id;
393 end if;
394 if p_per_in_ler_id is null then
395 l_ldgr_parms.per_in_ler_id := l_ldgr.per_in_ler_id;
396 else
397 l_ldgr_parms.per_in_ler_id := p_per_in_ler_id;
398 end if;
399 if p_business_group_id is null then
400 l_ldgr_parms.business_group_id := l_ldgr.business_group_id;
401 else
402 l_ldgr_parms.business_group_id := p_business_group_id;
403 end if;
404
405 /*
406 hr_utility.set_location('rslt:'||to_char(l_ldgr_parms.prtt_enrt_rslt_id), 26);
407 hr_utility.set_location('rt:'||to_char(l_ldgr_parms.acty_base_rt_id), 26);
408 hr_utility.set_location('pil:'||to_char(l_ldgr_parms.per_in_ler_id), 26);
409 hr_utility.set_location('bg:'||to_char(l_ldgr_parms.business_group_id), 26);
410 hr_utility.set_location('esd:'||to_char(p_effective_start_date), 26);
411 hr_utility.set_location('used:'||to_char(p_used_val), 26);
412 */
413
414 -- get the dummy flex credit result and it's programs ref-perd-cd's.
415 open c_rslt (c_prtt_enrt_rslt_id => l_ldgr_parms.prtt_enrt_rslt_id,
416 c_effective_date => p_effective_start_date);
417 fetch c_rslt into l_rslt;
418 if c_rslt%NOTFOUND or c_rslt%NOTFOUND is null then
419 close c_rslt;
420 hr_utility.set_location('RESULT INFO NOT FOUND. LEDGER ID='||
421 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
422 to_char(p_effective_start_date)||' RESULT ID='||
423 to_char(l_ldgr_parms.prtt_enrt_rslt_id)||' ',28);
424 else
425 close c_rslt;
426 /*
427 hr_utility.set_location('person:'||to_char(l_rslt.person_id), 30);
428 hr_utility.set_location('ref prd:'||l_rslt.acty_ref_perd_cd, 30);
429 hr_utility.set_location('cmcd:'||l_rslt.enrt_info_rt_freq_cd, 30);
430 */
431 --GEVITY
432 ben_element_entry.get_abr_assignment
433 (p_person_id => l_rslt.person_id
434 ,p_effective_date => p_effective_start_date
435 ,p_acty_base_rt_id => p_acty_base_rt_id
436 ,p_organization_id => l_organization_id
437 ,p_payroll_id => l_payroll_id
438 ,p_assignment_id => l_assignment_id
439 );
440 --
441 open c_abr(p_acty_base_rt_id) ;
442 fetch c_abr into l_rate_periodization_rl ;
443 close c_abr;
444 --END GEVITY
445 -- set two of the out parms.
446 p_acty_ref_perd_cd := l_rslt.acty_ref_perd_cd;
447 p_cmcd_ref_perd_cd := l_rslt.enrt_info_rt_freq_cd;
448
449 -- determine cmcd and ann values as needed.
450 -- ---------------------------------------------------------------------
451 -- Forfeited values
452 -- ---------------------------------------------------------------------
453 if p_frftd_val is not null then
454 hr_utility.set_location('Forfeited Row: '||l_ldgr_parms.prtt_enrt_rslt_id, 32);
455 if p_frftd_val = 0 then
456 p_ann_frftd_val := 0;
457 p_cmcd_frftd_val := 0;
458 else
459 -- Forfeited values are stored nowhere but the ledger. We have to call the
460 -- calculate routines to find the annual and communicated amounts. To
461 -- do this, an enrt-rt MUST exist for the providded flex credit choice row,
462 -- as that's the rate we're going to base the cal on.
463 open c_choice_data(c_acty_base_rt_id => l_ldgr_parms.acty_base_rt_id,
464 c_person_id => l_rslt.person_id,
465 c_per_in_ler_id => l_ldgr_parms.per_in_ler_id,
466 c_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
467 c_enrt_info_rt_freq_cd => l_rslt.enrt_info_rt_freq_cd,
468 c_effective_date => p_effective_start_date);
469 fetch c_choice_data into l_choice_data;
470 if c_choice_data%NOTFOUND or c_choice_data%NOTFOUND is null then
471 close c_choice_data;
472 hr_utility.set_location('FORFEITED RATE INFO NOT FOUND. LEDGER ID='||
473 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
474 to_char(p_effective_start_date)||' RESULT ID='||
475 to_char(l_ldgr_parms.prtt_enrt_rslt_id),34);
476 else
477 close c_choice_data;
478
479 -- to convert the rates, we need the payroll id.
480 /*
481 open c_person(c_person_id => l_rslt.person_id,
482 c_effective_date => p_effective_start_date);
483 fetch c_person into l_person;
484 if c_person%NOTFOUND or c_person%NOTFOUND is null then
485 close c_person;
486 hr_utility.set_location('FORFEITED PERSON INFO NOT FOUND. LEDGER ID='||
487 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
488 to_char(p_effective_start_date)||' RESULT ID='||
489 to_char(l_ldgr_parms.prtt_enrt_rslt_id),36);
490 else
491 close c_person;
492 */
493 if l_payroll_id IS NOT NULL THEN
494 IF l_rate_periodization_rl IS NOT NULL THEN
495 --
496 --
497 ben_distribute_rates.periodize_with_rule
498 (p_formula_id => l_rate_periodization_rl
499 ,p_effective_date => p_effective_start_date
500 ,p_assignment_id => l_assignment_id
501 ,p_convert_from_val => p_frftd_val
502 ,p_convert_from => 'DEFINED'
503 ,p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id
504 ,p_acty_base_rt_id => p_acty_base_rt_id
505 ,p_business_group_id => l_ldgr_parms.business_group_id
506 ,p_enrt_rt_id => l_choice_data.enrt_rt_id
507 ,p_ann_val => p_ann_frftd_val
508 ,p_cmcd_val => p_cmcd_frftd_val
509 ,p_val => l_dfnd_dummy
510 );
511 --
512 ELSE
513 hr_utility.set_location('Forfeited Row: Calling Distribute Rates', 38);
514 p_ann_frftd_val := ben_distribute_rates.period_to_annual(
515 p_amount => p_frftd_val,
516 p_enrt_rt_id => l_choice_data.enrt_rt_id,
517 p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id,
518 p_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
519 p_payroll_id => l_payroll_id,
520 p_business_group_id => l_ldgr_parms.business_group_id,
521 p_effective_date => p_effective_start_date,
522 p_lf_evt_ocrd_dt => l_choice_data.lf_evt_ocrd_dt,
523 p_complete_year_flag => 'Y');
524 --
525 -- Bug No 4538786
526 -- In this case, where rows are fetched from ecr(l_choice_data), pay periods
527 -- should be fetched from flex shell plan
528 if (l_rslt.enrt_info_rt_freq_cd = 'PPF') then
529 open get_flex_shell_rt(l_ldgr_parms.per_in_ler_id,
530 l_rslt.pgm_id);
531 fetch get_flex_shell_rt into l_flex_shell_rt;
532 if get_flex_shell_rt%FOUND then
533 close get_flex_shell_rt;
534 hr_utility.set_location('frftd l_flex_shell_rt.enrt_rt_id'||l_flex_shell_rt.enrt_rt_id,99);
535 hr_utility.set_location('frftd l_flex_shell_rt.element_type_id'||l_flex_shell_rt.element_type_id,99);
536 hr_utility.set_location('frftd l_flex_shell_rt.acty_base_rt_id'||l_flex_shell_rt.acty_base_rt_id,99);
537 p_cmcd_frftd_val := ben_distribute_rates.annual_to_period(
538 p_amount => p_ann_frftd_val,
539 p_enrt_rt_id => l_flex_shell_rt.enrt_rt_id,
540 p_elig_per_elctbl_chc_id => l_flex_shell_rt.elig_per_elctbl_chc_id,
541 p_acty_ref_perd_cd => l_rslt.enrt_info_rt_freq_cd,
542 p_payroll_id => l_payroll_id,
543 p_business_group_id => l_ldgr_parms.business_group_id,
544 p_effective_date => p_effective_start_date,
545 p_element_type_id => l_flex_shell_rt.element_type_id,
546 p_lf_evt_ocrd_dt => l_flex_shell_rt.lf_evt_ocrd_dt,
547 p_complete_year_flag => 'Y');
548 else
549 close get_flex_shell_rt; -- Bug 4604560, Close statement top of End If
550 end if;
551 else
552 p_cmcd_frftd_val := ben_distribute_rates.annual_to_period(
553 p_amount => p_ann_frftd_val,
554 p_enrt_rt_id => l_choice_data.enrt_rt_id,
555 p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id,
556 p_acty_ref_perd_cd => l_rslt.enrt_info_rt_freq_cd,
557 p_payroll_id => l_payroll_id,
558 p_business_group_id => l_ldgr_parms.business_group_id,
559 p_effective_date => p_effective_start_date,
560 p_lf_evt_ocrd_dt => l_choice_data.lf_evt_ocrd_dt,
561 p_complete_year_flag => 'Y');
562 end if;
563 -- End Bug No 4538786
564 END IF; --GEVITY
565 end if;
566 end if;
567 end if;
568 -- ---------------------------------------------------------------------
569 -- Used values
570 -- ---------------------------------------------------------------------
571 elsif p_used_val is not null then
572 hr_utility.set_location('Used Row: '||l_ldgr_parms.prtt_enrt_rslt_id, 40);
573 -- used val's are complex, must get communicated and annual values from
574 -- the prtt-rt-val table. They must have been put there by the enrollment
575 -- process, otherwise we can't get them. If rates are overridden, the user
576 -- must have provided the cmcd and ann vals to prtt-rt too.
577 if p_used_val = 0 then
578 p_ann_used_val := 0;
579 p_cmcd_used_val := 0;
580 else
581 -- try to get the used cmcd and annual values from the prtt-rt-val table
582 open c_used_rate(c_acty_base_rt_id => l_ldgr_parms.acty_base_rt_id,
583 c_person_id => l_rslt.person_id,
584 c_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
585 c_enrt_info_rt_freq_cd => l_rslt.enrt_info_rt_freq_cd,
586 c_effective_date => p_effective_start_date,
587 c_used_val => p_used_val);
588 fetch c_used_rate into l_used_rate;
589 if c_used_rate%NOTFOUND or c_used_rate%NOTFOUND is null then
590 close c_used_rate;
591 -- Cannot find the used rate in the prv table. Calculate it by calling the
592 -- convert routines. The only time (so far) that we need to do this is when
593 -- the prtt does a rollover of excess credits from two different benefit
594 -- pools into the same plan (creating one result row, but 2 ledger rows).
595 open c_choice_data(c_acty_base_rt_id => l_ldgr_parms.acty_base_rt_id,
596 c_person_id => l_rslt.person_id,
597 c_per_in_ler_id => l_ldgr_parms.per_in_ler_id,
598 c_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
599 c_enrt_info_rt_freq_cd => l_rslt.enrt_info_rt_freq_cd,
600 c_effective_date => p_effective_start_date);
601 fetch c_choice_data into l_choice_data;
602 if c_choice_data%NOTFOUND or c_choice_data%NOTFOUND is null then
603 close c_choice_data;
604 hr_utility.set_location('USED CHOICE INFO NOT FOUND. LEDGER ID='||
605 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
606 to_char(p_effective_start_date)||' RESULT ID='||
607 to_char(l_ldgr_parms.prtt_enrt_rslt_id),42);
608 else
609 close c_choice_data;
610 /*
611 -- to convert the rates, we need the payroll id.
612 open c_person(c_person_id => l_rslt.person_id,
613 c_effective_date => p_effective_start_date);
614 fetch c_person into l_person;
615 if c_person%NOTFOUND or c_person%NOTFOUND is null then
616 close c_person;
617 hr_utility.set_location('USED PERSON INFO NOT FOUND. LEDGER ID='||
618 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
619 to_char(p_effective_start_date)||' RESULT ID='||
620 to_char(l_ldgr_parms.prtt_enrt_rslt_id),44);
621 else
622 close c_person;
623 */
624 if l_payroll_id IS NOT NULL THEN
625 hr_utility.set_location('Forfeited Row: Calling Distribute Rates', 46);
626 IF l_rate_periodization_rl IS NOT NULL THEN
627 --
628 --
629 ben_distribute_rates.periodize_with_rule
630 (p_formula_id => l_rate_periodization_rl
631 ,p_effective_date => p_effective_start_date
632 ,p_assignment_id => l_assignment_id
633 ,p_convert_from_val => p_used_val
634 ,p_convert_from => 'DEFINED'
635 ,p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id
636 ,p_acty_base_rt_id => p_acty_base_rt_id
637 ,p_business_group_id => l_ldgr_parms.business_group_id
638 ,p_enrt_rt_id => l_choice_data.enrt_rt_id
639 ,p_ann_val => p_ann_used_val
640 ,p_cmcd_val => p_cmcd_used_val
641 ,p_val => l_dfnd_dummy
642 );
643 --
644 ELSE
645 p_ann_used_val := ben_distribute_rates.period_to_annual(
646 p_amount => p_used_val,
647 p_enrt_rt_id => l_choice_data.enrt_rt_id,
648 p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id,
649 p_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
650 p_payroll_id => l_payroll_id,
651 p_business_group_id => l_ldgr_parms.business_group_id,
652 p_effective_date => p_effective_start_date,
653 p_lf_evt_ocrd_dt => l_choice_data.lf_evt_ocrd_dt,
654 p_complete_year_flag => 'Y');
655
656 p_cmcd_used_val := ben_distribute_rates.annual_to_period(
657 p_amount => p_ann_used_val,
658 p_enrt_rt_id => l_choice_data.enrt_rt_id,
659 p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id,
660 p_acty_ref_perd_cd => l_rslt.enrt_info_rt_freq_cd,
661 p_payroll_id => l_payroll_id,
662 p_business_group_id => l_ldgr_parms.business_group_id,
663 p_effective_date => p_effective_start_date,
664 p_lf_evt_ocrd_dt => l_choice_data.lf_evt_ocrd_dt,
665 p_complete_year_flag => 'Y');
666 END IF; --GEVITY
667 --
668 if p_ann_used_val is null or p_cmcd_used_val is null then
669 hr_utility.set_location('USED RATE ANN OR CMCD NOT FOUND. LEDGER ID='||
670 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
671 to_char(p_effective_start_date)||' RESULT ID='||
672 to_char(l_ldgr_parms.prtt_enrt_rslt_id),48);
673 end if;
674 end if; -- end if person not found
675 end if; -- end if choice not found
676 else
677 -- used found in prv, use it.
678 close c_used_rate;
679 p_ann_used_val := l_used_rate.ann_rt_val;
680 p_cmcd_used_val := l_used_rate.cmcd_rt_val;
681 /*
682 hr_utility.set_location('ann:'||to_char(p_ann_used_val), 50);
683 hr_utility.set_location('cmcd:'||to_char(p_cmcd_used_val), 50);
684 */
685 if p_ann_used_val is null or p_cmcd_used_val is null then
686 hr_utility.set_location('USED RATE ANN OR CMCD NOT FOUND. LEDGER ID='||
687 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
688 to_char(p_effective_start_date)||' RESULT ID='||
689 to_char(l_ldgr_parms.prtt_enrt_rslt_id),52);
690 end if;
691 end if; -- end if used not found in prv
692 end if; -- end if used = 0
693 -- ---------------------------------------------------------------------
694 -- Provided values
695 -- ---------------------------------------------------------------------
696 elsif p_prvdd_val is not null then
697 hr_utility.set_location('Provided Row: '||l_ldgr_parms.prtt_enrt_rslt_id, 54);
698 if p_used_val = 0 then
699 p_ann_used_val := 0;
700 p_cmcd_used_val := 0;
701 else
702 -- try to get the provided cmcd and annual values from the prtt-rt-val table
703 open c_prvdd_rate(c_prtt_enrt_rslt_id => l_ldgr_parms.prtt_enrt_rslt_id,
704 c_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
705 c_enrt_info_rt_freq_cd => l_rslt.enrt_info_rt_freq_cd,
706 c_effective_date => p_effective_start_date,
707 c_prvdd_val => p_prvdd_val);
708 fetch c_prvdd_rate into l_prvdd_rate;
709 if c_prvdd_rate%NOTFOUND or c_prvdd_rate%NOTFOUND is null then
710 close c_prvdd_rate;
711 -- we couldn't find the prvdd in the prtt-rt-val table, probably because
712 -- it's a total there, rather than individual provided values. Look in
713 -- enrt-rt table instead.
714 open c_prvdd_rate2 (c_acty_base_rt_id => l_ldgr_parms.acty_base_rt_id,
715 c_person_id => l_rslt.person_id,
716 c_per_in_ler_id => l_ldgr_parms.per_in_ler_id,
717 c_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
718 c_enrt_info_rt_freq_cd => l_rslt.enrt_info_rt_freq_cd,
719 c_effective_date => p_effective_start_date,
720 c_prvdd_val => p_prvdd_val);
721 fetch c_prvdd_rate2 into l_prvdd_rate2;
722 if c_prvdd_rate2%NOTFOUND or c_prvdd_rate2%NOTFOUND is null then
723 close c_prvdd_rate2;
724 hr_utility.set_location('PRVDD RATE INFO NOT FOUND. LEDGER ID='||
725 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
726 to_char(p_effective_start_date)||' RESULT ID='||
727 to_char(l_ldgr_parms.prtt_enrt_rslt_id),56);
728 else
729 close c_prvdd_rate2;
730 p_ann_prvdd_val := l_prvdd_rate2.ann_val;
731 p_cmcd_prvdd_val := l_prvdd_rate2.cmcd_val;
732 end if;
733 else
734 close c_prvdd_rate;
735 p_ann_prvdd_val := l_prvdd_rate.ann_rt_val;
736 p_cmcd_prvdd_val := l_prvdd_rate.cmcd_rt_val;
737 end if;
738 end if;
739
740 -----------------------------------------------------------------------
741 -- Cash values
742 -- ---------------------------------------------------------------------
743 elsif p_cash_recd_val is not null then
744 hr_utility.set_location('Cash Row: '||l_ldgr_parms.prtt_enrt_rslt_id, 58);
745 if p_cash_recd_val = 0 then
746 p_ann_cash_recd_val := 0;
747 p_cmcd_cash_recd_val := 0;
748 else
749 -- Cash values are stored nowhere but the ledger. We have to call the
750 -- calculate routines to find the annual and communicated amounts. To
751 -- do this, an enrt-rt MUST exist for the associated providded flex credit
752 -- choice row, as that's the rate we're going to base the calc on.
753 open c_choice_data(c_acty_base_rt_id => l_ldgr_parms.acty_base_rt_id,
754 c_person_id => l_rslt.person_id,
755 c_per_in_ler_id => l_ldgr_parms.per_in_ler_id,
756 c_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
757 c_enrt_info_rt_freq_cd => l_rslt.enrt_info_rt_freq_cd,
758 c_effective_date => p_effective_start_date);
759 fetch c_choice_data into l_choice_data;
760 if c_choice_data%NOTFOUND or c_choice_data%NOTFOUND is null then
761 close c_choice_data;
762 hr_utility.set_location('CASH RATE INFO NOT FOUND. LEDGER ID='||
763 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
764 to_char(p_effective_start_date)||' RESULT ID='||
765 to_char(l_ldgr_parms.prtt_enrt_rslt_id),60);
766 else
767 close c_choice_data;
768 /*
769 -- to convert the rates, we need the payroll id.
770 open c_person(c_person_id => l_rslt.person_id,
771 c_effective_date => p_effective_start_date);
772 fetch c_person into l_person;
773 if c_person%NOTFOUND or c_person%NOTFOUND is null then
774 close c_person;
775 hr_utility.set_location('CASH PERSON INFO NOT FOUND. LEDGER ID='||
776 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
777 to_char(p_effective_start_date)||' RESULT ID='||
778 to_char(l_ldgr_parms.prtt_enrt_rslt_id),62);
779 else
780 close c_person;
781 */
782 if l_payroll_id IS NOT NULL THEN
783 hr_utility.set_location('Cash Row: Calling Distribute Rates', 64);
784 IF l_rate_periodization_rl IS NOT NULL THEN
785 --
786 --
787 ben_distribute_rates.periodize_with_rule
788 (p_formula_id => l_rate_periodization_rl
789 ,p_effective_date => p_effective_start_date
790 ,p_assignment_id => l_assignment_id
791 ,p_convert_from_val => p_cash_recd_val
792 ,p_convert_from => 'DEFINED'
793 ,p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id
794 ,p_acty_base_rt_id => p_acty_base_rt_id
795 ,p_business_group_id => l_ldgr_parms.business_group_id
796 ,p_enrt_rt_id => l_choice_data.enrt_rt_id
797 ,p_ann_val => p_ann_cash_recd_val
798 ,p_cmcd_val => p_cmcd_cash_recd_val
799 ,p_val => l_dfnd_dummy
800 );
801 --
802 ELSE
803 p_ann_cash_recd_val := ben_distribute_rates.period_to_annual(
804 p_amount => p_cash_recd_val,
805 p_enrt_rt_id => l_choice_data.enrt_rt_id,
806 p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id,
807 p_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
808 p_payroll_id => l_payroll_id,
809 p_business_group_id => l_ldgr_parms.business_group_id,
810 p_effective_date => p_effective_start_date,
811 p_lf_evt_ocrd_dt => l_choice_data.lf_evt_ocrd_dt,
812 p_complete_year_flag => 'Y');
813 --
814 -- Bug No 4538786
815 -- In this case, where rows are fetched from ecr(l_choice_data), pay periods
816 -- should be fetched from flex shell plan
817 if (l_rslt.enrt_info_rt_freq_cd = 'PPF') then
818 open get_flex_shell_rt(l_ldgr_parms.per_in_ler_id,
819 l_rslt.pgm_id);
820 fetch get_flex_shell_rt into l_flex_shell_rt;
821 if get_flex_shell_rt%FOUND then
822 close get_flex_shell_rt;
823 hr_utility.set_location('cash l_flex_shell_rt.enrt_rt_id'||l_flex_shell_rt.enrt_rt_id,99);
824 hr_utility.set_location('cash l_flex_shell_rt.element_type_id'||l_flex_shell_rt.element_type_id,99);
825 hr_utility.set_location('cash l_flex_shell_rt.acty_base_rt_id'||l_flex_shell_rt.acty_base_rt_id,99);
826 p_cmcd_cash_recd_val := ben_distribute_rates.annual_to_period(
827 p_amount => p_ann_cash_recd_val,
828 p_enrt_rt_id => l_flex_shell_rt.enrt_rt_id,
829 p_elig_per_elctbl_chc_id => l_flex_shell_rt.elig_per_elctbl_chc_id,
830 p_acty_ref_perd_cd => l_rslt.enrt_info_rt_freq_cd,
831 p_payroll_id => l_payroll_id,
832 p_business_group_id => l_ldgr_parms.business_group_id,
833 p_effective_date => p_effective_start_date,
834 p_element_type_id => l_flex_shell_rt.element_type_id,
835 p_lf_evt_ocrd_dt => l_flex_shell_rt.lf_evt_ocrd_dt,
836 p_complete_year_flag => 'Y');
837 else
838 --
839 close get_flex_shell_rt; -- Bug 4604560, Close statement top of End If
840 end if;
841 else
842 p_cmcd_cash_recd_val := ben_distribute_rates.annual_to_period(
843 p_amount => p_ann_cash_recd_val,
844 p_enrt_rt_id => l_choice_data.enrt_rt_id,
845 p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id,
846 p_acty_ref_perd_cd => l_rslt.enrt_info_rt_freq_cd,
847 p_payroll_id => l_payroll_id,
848 p_business_group_id => l_ldgr_parms.business_group_id,
849 p_effective_date => p_effective_start_date,
850 p_lf_evt_ocrd_dt => l_choice_data.lf_evt_ocrd_dt,
851 p_complete_year_flag => 'Y');
852 end if;
853 -- End Bug No 4538786
854 END IF; --GEVITY
855 end if;
856 end if;
857 end if;
858
859 -- ---------------------------------------------------------------------
860 -- Rolled Up values
861 -- ---------------------------------------------------------------------
862 else --if l_ldgr.rld_up_val is not null then
863 -- as of delivery of this module, we were not using the rld_up_val field.
864 null;
865 end if; -- end of 'if' for the various val columns
866 end if; -- end of 'if' for getting result table data
867
868 hr_utility.set_location('Leaving '||l_proc, 999);
869 end; -- get_cmcd_ann_values
870 -- ---------------------------------------------------------------------
871 -- get_dt_mode
872 -- ---------------------------------------------------------------------
873 procedure get_dt_mode
874 (p_effective_date in date,
875 p_base_key_value in number,
876 p_mode out nocopy varchar2) is
877
878 l_proc varchar2(72) := g_package||'get_dt_mode';
879 l_correction boolean := TRUE;
880 l_update boolean := FALSE;
881 l_update_override boolean := FALSE;
882 l_update_change_insert boolean := FALSE;
883 --
884 begin
885 hr_utility.set_location('Entering '||l_proc, 10);
886 --
887 -- Get the appropriate update mode.
888 --
889 DT_Api.Find_DT_Upd_Modes(p_effective_date => p_effective_date,
890 p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
891 p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
892 p_base_key_value => p_base_key_value,
893 p_correction => l_correction,
894 p_update => l_update,
895 p_update_override => l_update_override,
896 p_update_change_insert => l_update_change_insert);
897 --
898 if l_update_override or l_update_change_insert then
899 p_mode := 'UPDATE_OVERRIDE';
900 elsif l_correction then
901 p_mode := 'CORRECTION';
902 else
903 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
904 hr_utility.raise_error;
905 end if;
906 hr_utility.set_location('Leaving '||l_proc, 999);
907 end; -- get_dt_mode
908 end ben_update_ledgers;
[Home] [Help]