1 Package Body ben_update_ledgers as
2 /* $Header: benbplup.pkb 120.3.12020000.2 2013/01/11 00:29:05 stee 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 hr_utility.set_location('stee p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id, 10);
364 hr_utility.set_location('stee p_acty_base_rt_id '||p_acty_base_rt_id, 10);
365 hr_utility.set_location('stee p_per_in_ler_id '||p_per_in_ler_id, 10);
366 --
367 -- When updating ledgers, the api may not get all the parms passed in, hence
368 -- we might not either. Go get the ones we need.
369 if p_prtt_enrt_rslt_id is null or p_acty_base_rt_id is null or
370 p_per_in_ler_id is null or p_business_group_id is null then
371 hr_utility.set_location('Have to find out nocopy ledger parms:'||
372 to_char(p_bnft_prvdd_ldgr_id), 20);
373 open c_ldgr(c_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id,
374 c_effective_date => p_effective_start_date);
375 fetch c_ldgr into l_ldgr;
376 if c_ldgr%NOTFOUND or c_ldgr%NOTFOUND is null then
377 -- if we can't find this info, we can't find the data we need.
378 hr_utility.set_location('LEDGER INFO NOT FOUND. LEDGER ID='||
379 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
380 to_char(p_effective_start_date)||' RESULT ID='||
381 to_char(p_prtt_enrt_rslt_id)||' ',22);
382 close c_ldgr;
383 return; --<-------------------------------------
384 end if;
385 close c_ldgr;
386 hr_utility.set_location('Found ledger parms',24);
387 hr_utility.set_location('stee l_ldgr.acty_base_rt_id '||l_ldgr.acty_base_rt_id,24);
388 end if;
389 if p_prtt_enrt_rslt_id is null then
390 l_ldgr_parms.prtt_enrt_rslt_id := l_ldgr.prtt_enrt_rslt_id;
391 else
392 l_ldgr_parms.prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
393 end if;
394 if p_acty_base_rt_id is null then -- 14776776
395 l_ldgr_parms.acty_base_rt_id := l_ldgr.acty_base_rt_id;
396 else
397 l_ldgr_parms.acty_base_rt_id := p_acty_base_rt_id;
398 end if;
399 if p_per_in_ler_id is null then
400 l_ldgr_parms.per_in_ler_id := l_ldgr.per_in_ler_id;
401 else
402 l_ldgr_parms.per_in_ler_id := p_per_in_ler_id;
403 end if;
404 if p_business_group_id is null then
405 l_ldgr_parms.business_group_id := l_ldgr.business_group_id;
406 else
407 l_ldgr_parms.business_group_id := p_business_group_id;
408 end if;
409
410 hr_utility.set_location('rslt:'||to_char(l_ldgr_parms.prtt_enrt_rslt_id), 26);
411 hr_utility.set_location('rt:'||to_char(l_ldgr_parms.acty_base_rt_id), 26);
412 hr_utility.set_location('pil:'||to_char(l_ldgr_parms.per_in_ler_id), 26);
413 hr_utility.set_location('bg:'||to_char(l_ldgr_parms.business_group_id), 26);
414 hr_utility.set_location('esd:'||to_char(p_effective_start_date), 26);
415 hr_utility.set_location('used:'||to_char(p_used_val), 26);
416
417
418 -- get the dummy flex credit result and it's programs ref-perd-cd's.
419 open c_rslt (c_prtt_enrt_rslt_id => l_ldgr_parms.prtt_enrt_rslt_id,
420 c_effective_date => p_effective_start_date);
421 fetch c_rslt into l_rslt;
422 if c_rslt%NOTFOUND or c_rslt%NOTFOUND is null then
423 close c_rslt;
424 hr_utility.set_location('RESULT INFO NOT FOUND. LEDGER ID='||
425 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
426 to_char(p_effective_start_date)||' RESULT ID='||
427 to_char(l_ldgr_parms.prtt_enrt_rslt_id)||' ',28);
428 else
429 close c_rslt;
430
431 hr_utility.set_location('person:'||to_char(l_rslt.person_id), 30);
432 hr_utility.set_location('ref prd:'||l_rslt.acty_ref_perd_cd, 30);
433 hr_utility.set_location('cmcd:'||l_rslt.enrt_info_rt_freq_cd, 30);
434
435 --GEVITY
436 ben_element_entry.get_abr_assignment
437 (p_person_id => l_rslt.person_id
438 ,p_effective_date => p_effective_start_date
439 ,p_acty_base_rt_id => l_ldgr_parms.acty_base_rt_id -- 14776776
440 ,p_organization_id => l_organization_id
441 ,p_payroll_id => l_payroll_id
442 ,p_assignment_id => l_assignment_id
443 );
444 --
445 open c_abr(l_ldgr_parms.acty_base_rt_id) ; -- 14776776
446 fetch c_abr into l_rate_periodization_rl ;
447 close c_abr;
448 --END GEVITY
449 -- set two of the out parms.
450 p_acty_ref_perd_cd := l_rslt.acty_ref_perd_cd;
451 p_cmcd_ref_perd_cd := l_rslt.enrt_info_rt_freq_cd;
452
453 -- determine cmcd and ann values as needed.
454 -- ---------------------------------------------------------------------
455 -- Forfeited values
456 -- ---------------------------------------------------------------------
457 if p_frftd_val is not null then
458 hr_utility.set_location('Forfeited Row: '||l_ldgr_parms.prtt_enrt_rslt_id, 32);
459 if p_frftd_val = 0 then
460 p_ann_frftd_val := 0;
461 p_cmcd_frftd_val := 0;
462 else
463 -- Forfeited values are stored nowhere but the ledger. We have to call the
464 -- calculate routines to find the annual and communicated amounts. To
465 -- do this, an enrt-rt MUST exist for the providded flex credit choice row,
466 -- as that's the rate we're going to base the cal on.
467 open c_choice_data(c_acty_base_rt_id => l_ldgr_parms.acty_base_rt_id,
468 c_person_id => l_rslt.person_id,
469 c_per_in_ler_id => l_ldgr_parms.per_in_ler_id,
470 c_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
471 c_enrt_info_rt_freq_cd => l_rslt.enrt_info_rt_freq_cd,
472 c_effective_date => p_effective_start_date);
473 fetch c_choice_data into l_choice_data;
474 if c_choice_data%NOTFOUND or c_choice_data%NOTFOUND is null then
475 close c_choice_data;
476 hr_utility.set_location('FORFEITED RATE INFO NOT FOUND. LEDGER ID='||
477 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
478 to_char(p_effective_start_date)||' RESULT ID='||
479 to_char(l_ldgr_parms.prtt_enrt_rslt_id),34);
480 else
481 close c_choice_data;
482
483 -- to convert the rates, we need the payroll id.
484 /*
485 open c_person(c_person_id => l_rslt.person_id,
486 c_effective_date => p_effective_start_date);
487 fetch c_person into l_person;
488 if c_person%NOTFOUND or c_person%NOTFOUND is null then
489 close c_person;
490 hr_utility.set_location('FORFEITED PERSON INFO NOT FOUND. LEDGER ID='||
491 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
492 to_char(p_effective_start_date)||' RESULT ID='||
493 to_char(l_ldgr_parms.prtt_enrt_rslt_id),36);
494 else
495 close c_person;
496 */
497 if l_payroll_id IS NOT NULL THEN
498 IF l_rate_periodization_rl IS NOT NULL THEN
499 --
500 --
501 ben_distribute_rates.periodize_with_rule
502 (p_formula_id => l_rate_periodization_rl
503 ,p_effective_date => p_effective_start_date
504 ,p_assignment_id => l_assignment_id
505 ,p_convert_from_val => p_frftd_val
506 ,p_convert_from => 'DEFINED'
507 ,p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id
508 ,p_acty_base_rt_id => l_ldgr_parms.acty_base_rt_id -- 14776776
509 ,p_business_group_id => l_ldgr_parms.business_group_id
510 ,p_enrt_rt_id => l_choice_data.enrt_rt_id
511 ,p_ann_val => p_ann_frftd_val
512 ,p_cmcd_val => p_cmcd_frftd_val
513 ,p_val => l_dfnd_dummy
514 );
515 --
516 ELSE
517 hr_utility.set_location('Forfeited Row: Calling Distribute Rates', 38);
518 p_ann_frftd_val := ben_distribute_rates.period_to_annual(
519 p_amount => p_frftd_val,
520 p_enrt_rt_id => l_choice_data.enrt_rt_id,
521 p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id,
522 p_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
523 p_payroll_id => l_payroll_id,
524 p_business_group_id => l_ldgr_parms.business_group_id,
525 p_effective_date => p_effective_start_date,
526 p_lf_evt_ocrd_dt => l_choice_data.lf_evt_ocrd_dt,
527 p_complete_year_flag => 'Y');
528 --
529 -- Bug No 4538786
530 -- In this case, where rows are fetched from ecr(l_choice_data), pay periods
531 -- should be fetched from flex shell plan
532 if (l_rslt.enrt_info_rt_freq_cd = 'PPF') then
533 open get_flex_shell_rt(l_ldgr_parms.per_in_ler_id,
534 l_rslt.pgm_id);
535 fetch get_flex_shell_rt into l_flex_shell_rt;
536 if get_flex_shell_rt%FOUND then
537 close get_flex_shell_rt;
538 hr_utility.set_location('frftd l_flex_shell_rt.enrt_rt_id'||l_flex_shell_rt.enrt_rt_id,99);
539 hr_utility.set_location('frftd l_flex_shell_rt.element_type_id'||l_flex_shell_rt.element_type_id,99);
540 hr_utility.set_location('frftd l_flex_shell_rt.acty_base_rt_id'||l_flex_shell_rt.acty_base_rt_id,99);
541 p_cmcd_frftd_val := ben_distribute_rates.annual_to_period(
542 p_amount => p_ann_frftd_val,
543 p_enrt_rt_id => l_flex_shell_rt.enrt_rt_id,
544 p_elig_per_elctbl_chc_id => l_flex_shell_rt.elig_per_elctbl_chc_id,
545 p_acty_ref_perd_cd => l_rslt.enrt_info_rt_freq_cd,
546 p_payroll_id => l_payroll_id,
547 p_business_group_id => l_ldgr_parms.business_group_id,
548 p_effective_date => p_effective_start_date,
549 p_element_type_id => l_flex_shell_rt.element_type_id,
550 p_lf_evt_ocrd_dt => l_flex_shell_rt.lf_evt_ocrd_dt,
551 p_complete_year_flag => 'Y');
552 else
553 close get_flex_shell_rt; -- Bug 4604560, Close statement top of End If
554 end if;
555 else
556 p_cmcd_frftd_val := ben_distribute_rates.annual_to_period(
557 p_amount => p_ann_frftd_val,
558 p_enrt_rt_id => l_choice_data.enrt_rt_id,
559 p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id,
560 p_acty_ref_perd_cd => l_rslt.enrt_info_rt_freq_cd,
561 p_payroll_id => l_payroll_id,
562 p_business_group_id => l_ldgr_parms.business_group_id,
563 p_effective_date => p_effective_start_date,
564 p_lf_evt_ocrd_dt => l_choice_data.lf_evt_ocrd_dt,
565 p_complete_year_flag => 'Y');
566 end if;
567 -- End Bug No 4538786
568 END IF; --GEVITY
569 end if;
570 end if;
571 end if;
572 -- ---------------------------------------------------------------------
573 -- Used values
574 -- ---------------------------------------------------------------------
575 elsif p_used_val is not null then
576 hr_utility.set_location('Used Row: '||l_ldgr_parms.prtt_enrt_rslt_id, 40);
577 -- used val's are complex, must get communicated and annual values from
578 -- the prtt-rt-val table. They must have been put there by the enrollment
579 -- process, otherwise we can't get them. If rates are overridden, the user
580 -- must have provided the cmcd and ann vals to prtt-rt too.
581 if p_used_val = 0 then
582 p_ann_used_val := 0;
583 p_cmcd_used_val := 0;
584 else
585 -- try to get the used cmcd and annual values from the prtt-rt-val table
586 open c_used_rate(c_acty_base_rt_id => l_ldgr_parms.acty_base_rt_id,
587 c_person_id => l_rslt.person_id,
588 c_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
589 c_enrt_info_rt_freq_cd => l_rslt.enrt_info_rt_freq_cd,
590 c_effective_date => p_effective_start_date,
591 c_used_val => p_used_val);
592 fetch c_used_rate into l_used_rate;
593 if c_used_rate%NOTFOUND or c_used_rate%NOTFOUND is null then
594 close c_used_rate;
595 -- Cannot find the used rate in the prv table. Calculate it by calling the
596 -- convert routines. The only time (so far) that we need to do this is when
597 -- the prtt does a rollover of excess credits from two different benefit
598 -- pools into the same plan (creating one result row, but 2 ledger rows).
599 open c_choice_data(c_acty_base_rt_id => l_ldgr_parms.acty_base_rt_id,
600 c_person_id => l_rslt.person_id,
601 c_per_in_ler_id => l_ldgr_parms.per_in_ler_id,
602 c_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
603 c_enrt_info_rt_freq_cd => l_rslt.enrt_info_rt_freq_cd,
604 c_effective_date => p_effective_start_date);
605 fetch c_choice_data into l_choice_data;
606 if c_choice_data%NOTFOUND or c_choice_data%NOTFOUND is null then
607 close c_choice_data;
608 hr_utility.set_location('USED CHOICE INFO NOT FOUND. LEDGER ID='||
609 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
610 to_char(p_effective_start_date)||' RESULT ID='||
611 to_char(l_ldgr_parms.prtt_enrt_rslt_id),42);
612 else
613 close c_choice_data;
614 /*
615 -- to convert the rates, we need the payroll id.
616 open c_person(c_person_id => l_rslt.person_id,
617 c_effective_date => p_effective_start_date);
618 fetch c_person into l_person;
619 if c_person%NOTFOUND or c_person%NOTFOUND is null then
620 close c_person;
621 hr_utility.set_location('USED PERSON INFO NOT FOUND. LEDGER ID='||
622 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
623 to_char(p_effective_start_date)||' RESULT ID='||
624 to_char(l_ldgr_parms.prtt_enrt_rslt_id),44);
625 else
626 close c_person;
627 */
628 if l_payroll_id IS NOT NULL THEN
629 hr_utility.set_location('Forfeited Row: Calling Distribute Rates', 46);
630 IF l_rate_periodization_rl IS NOT NULL THEN
631 --
632 --
633 ben_distribute_rates.periodize_with_rule
634 (p_formula_id => l_rate_periodization_rl
635 ,p_effective_date => p_effective_start_date
636 ,p_assignment_id => l_assignment_id
637 ,p_convert_from_val => p_used_val
638 ,p_convert_from => 'DEFINED'
639 ,p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id
640 ,p_acty_base_rt_id => l_ldgr_parms.acty_base_rt_id -- 14776776
641 ,p_business_group_id => l_ldgr_parms.business_group_id
642 ,p_enrt_rt_id => l_choice_data.enrt_rt_id
643 ,p_ann_val => p_ann_used_val
644 ,p_cmcd_val => p_cmcd_used_val
645 ,p_val => l_dfnd_dummy
646 );
647 --
648 ELSE
649 p_ann_used_val := ben_distribute_rates.period_to_annual(
650 p_amount => p_used_val,
651 p_enrt_rt_id => l_choice_data.enrt_rt_id,
652 p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id,
653 p_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
654 p_payroll_id => l_payroll_id,
655 p_business_group_id => l_ldgr_parms.business_group_id,
656 p_effective_date => p_effective_start_date,
657 p_lf_evt_ocrd_dt => l_choice_data.lf_evt_ocrd_dt,
658 p_complete_year_flag => 'Y');
659
660 p_cmcd_used_val := ben_distribute_rates.annual_to_period(
661 p_amount => p_ann_used_val,
662 p_enrt_rt_id => l_choice_data.enrt_rt_id,
663 p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id,
664 p_acty_ref_perd_cd => l_rslt.enrt_info_rt_freq_cd,
665 p_payroll_id => l_payroll_id,
666 p_business_group_id => l_ldgr_parms.business_group_id,
667 p_effective_date => p_effective_start_date,
668 p_lf_evt_ocrd_dt => l_choice_data.lf_evt_ocrd_dt,
669 p_complete_year_flag => 'Y');
670 END IF; --GEVITY
671 --
672 if p_ann_used_val is null or p_cmcd_used_val is null then
673 hr_utility.set_location('USED RATE ANN OR CMCD NOT FOUND. LEDGER ID='||
674 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
675 to_char(p_effective_start_date)||' RESULT ID='||
676 to_char(l_ldgr_parms.prtt_enrt_rslt_id),48);
677 end if;
678 end if; -- end if person not found
679 end if; -- end if choice not found
680 else
681 -- used found in prv, use it.
682 close c_used_rate;
683 p_ann_used_val := l_used_rate.ann_rt_val;
684 p_cmcd_used_val := l_used_rate.cmcd_rt_val;
685 /*
686 hr_utility.set_location('ann:'||to_char(p_ann_used_val), 50);
687 hr_utility.set_location('cmcd:'||to_char(p_cmcd_used_val), 50);
688 */
689 if p_ann_used_val is null or p_cmcd_used_val is null then
690 hr_utility.set_location('USED RATE ANN OR CMCD NOT FOUND. LEDGER ID='||
691 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
692 to_char(p_effective_start_date)||' RESULT ID='||
693 to_char(l_ldgr_parms.prtt_enrt_rslt_id),52);
694 end if;
695 end if; -- end if used not found in prv
696 end if; -- end if used = 0
697 -- ---------------------------------------------------------------------
698 -- Provided values
699 -- ---------------------------------------------------------------------
700 elsif p_prvdd_val is not null then
701 hr_utility.set_location('Provided Row: '||l_ldgr_parms.prtt_enrt_rslt_id, 54);
702 if p_used_val = 0 then
703 p_ann_used_val := 0;
704 p_cmcd_used_val := 0;
705 else
706 -- try to get the provided cmcd and annual values from the prtt-rt-val table
707 open c_prvdd_rate(c_prtt_enrt_rslt_id => l_ldgr_parms.prtt_enrt_rslt_id,
708 c_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
709 c_enrt_info_rt_freq_cd => l_rslt.enrt_info_rt_freq_cd,
710 c_effective_date => p_effective_start_date,
711 c_prvdd_val => p_prvdd_val);
712 fetch c_prvdd_rate into l_prvdd_rate;
713 if c_prvdd_rate%NOTFOUND or c_prvdd_rate%NOTFOUND is null then
714 close c_prvdd_rate;
715 -- we couldn't find the prvdd in the prtt-rt-val table, probably because
716 -- it's a total there, rather than individual provided values. Look in
717 -- enrt-rt table instead.
718 open c_prvdd_rate2 (c_acty_base_rt_id => l_ldgr_parms.acty_base_rt_id,
719 c_person_id => l_rslt.person_id,
720 c_per_in_ler_id => l_ldgr_parms.per_in_ler_id,
721 c_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
722 c_enrt_info_rt_freq_cd => l_rslt.enrt_info_rt_freq_cd,
723 c_effective_date => p_effective_start_date,
724 c_prvdd_val => p_prvdd_val);
725 fetch c_prvdd_rate2 into l_prvdd_rate2;
726 if c_prvdd_rate2%NOTFOUND or c_prvdd_rate2%NOTFOUND is null then
727 close c_prvdd_rate2;
728 hr_utility.set_location('PRVDD RATE INFO NOT FOUND. LEDGER ID='||
729 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
730 to_char(p_effective_start_date)||' RESULT ID='||
731 to_char(l_ldgr_parms.prtt_enrt_rslt_id),56);
732 else
733 close c_prvdd_rate2;
734 p_ann_prvdd_val := l_prvdd_rate2.ann_val;
735 p_cmcd_prvdd_val := l_prvdd_rate2.cmcd_val;
736 end if;
737 else
738 close c_prvdd_rate;
739 p_ann_prvdd_val := l_prvdd_rate.ann_rt_val;
740 p_cmcd_prvdd_val := l_prvdd_rate.cmcd_rt_val;
741 end if;
742 end if;
743
744 -----------------------------------------------------------------------
745 -- Cash values
746 -- ---------------------------------------------------------------------
747 elsif p_cash_recd_val is not null then
748 hr_utility.set_location('Cash Row: '||l_ldgr_parms.prtt_enrt_rslt_id, 58);
749 if p_cash_recd_val = 0 then
750 p_ann_cash_recd_val := 0;
751 p_cmcd_cash_recd_val := 0;
752 else
753 -- Cash values are stored nowhere but the ledger. We have to call the
754 -- calculate routines to find the annual and communicated amounts. To
755 -- do this, an enrt-rt MUST exist for the associated providded flex credit
756 -- choice row, as that's the rate we're going to base the calc on.
757 open c_choice_data(c_acty_base_rt_id => l_ldgr_parms.acty_base_rt_id,
758 c_person_id => l_rslt.person_id,
759 c_per_in_ler_id => l_ldgr_parms.per_in_ler_id,
760 c_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
761 c_enrt_info_rt_freq_cd => l_rslt.enrt_info_rt_freq_cd,
762 c_effective_date => p_effective_start_date);
763 fetch c_choice_data into l_choice_data;
764 if c_choice_data%NOTFOUND or c_choice_data%NOTFOUND is null then
765 close c_choice_data;
766 hr_utility.set_location('CASH RATE INFO NOT FOUND. LEDGER ID='||
767 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
768 to_char(p_effective_start_date)||' RESULT ID='||
769 to_char(l_ldgr_parms.prtt_enrt_rslt_id),60);
770 else
771 close c_choice_data;
772 /*
773 -- to convert the rates, we need the payroll id.
774 open c_person(c_person_id => l_rslt.person_id,
775 c_effective_date => p_effective_start_date);
776 fetch c_person into l_person;
777 if c_person%NOTFOUND or c_person%NOTFOUND is null then
778 close c_person;
779 hr_utility.set_location('CASH PERSON INFO NOT FOUND. LEDGER ID='||
780 to_char(p_bnft_prvdd_ldgr_id)||' DATE='||
781 to_char(p_effective_start_date)||' RESULT ID='||
782 to_char(l_ldgr_parms.prtt_enrt_rslt_id),62);
783 else
784 close c_person;
785 */
786 if l_payroll_id IS NOT NULL THEN
787 hr_utility.set_location('Cash Row: Calling Distribute Rates', 64);
788 IF l_rate_periodization_rl IS NOT NULL THEN
789 --
790 --
791 ben_distribute_rates.periodize_with_rule
792 (p_formula_id => l_rate_periodization_rl
793 ,p_effective_date => p_effective_start_date
794 ,p_assignment_id => l_assignment_id
795 ,p_convert_from_val => p_cash_recd_val
796 ,p_convert_from => 'DEFINED'
797 ,p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id
798 ,p_acty_base_rt_id => l_ldgr_parms.acty_base_rt_id -- 14776776
799 ,p_business_group_id => l_ldgr_parms.business_group_id
800 ,p_enrt_rt_id => l_choice_data.enrt_rt_id
801 ,p_ann_val => p_ann_cash_recd_val
802 ,p_cmcd_val => p_cmcd_cash_recd_val
803 ,p_val => l_dfnd_dummy
804 );
805 --
806 ELSE
807 p_ann_cash_recd_val := ben_distribute_rates.period_to_annual(
808 p_amount => p_cash_recd_val,
809 p_enrt_rt_id => l_choice_data.enrt_rt_id,
810 p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id,
811 p_acty_ref_perd_cd => l_rslt.acty_ref_perd_cd,
812 p_payroll_id => l_payroll_id,
813 p_business_group_id => l_ldgr_parms.business_group_id,
814 p_effective_date => p_effective_start_date,
815 p_lf_evt_ocrd_dt => l_choice_data.lf_evt_ocrd_dt,
816 p_complete_year_flag => 'Y');
817 --
818 -- Bug No 4538786
819 -- In this case, where rows are fetched from ecr(l_choice_data), pay periods
820 -- should be fetched from flex shell plan
821 if (l_rslt.enrt_info_rt_freq_cd = 'PPF') then
822 open get_flex_shell_rt(l_ldgr_parms.per_in_ler_id,
823 l_rslt.pgm_id);
824 fetch get_flex_shell_rt into l_flex_shell_rt;
825 if get_flex_shell_rt%FOUND then
826 close get_flex_shell_rt;
827 hr_utility.set_location('cash l_flex_shell_rt.enrt_rt_id'||l_flex_shell_rt.enrt_rt_id,99);
828 hr_utility.set_location('cash l_flex_shell_rt.element_type_id'||l_flex_shell_rt.element_type_id,99);
829 hr_utility.set_location('cash l_flex_shell_rt.acty_base_rt_id'||l_flex_shell_rt.acty_base_rt_id,99);
830 p_cmcd_cash_recd_val := ben_distribute_rates.annual_to_period(
831 p_amount => p_ann_cash_recd_val,
832 p_enrt_rt_id => l_flex_shell_rt.enrt_rt_id,
833 p_elig_per_elctbl_chc_id => l_flex_shell_rt.elig_per_elctbl_chc_id,
834 p_acty_ref_perd_cd => l_rslt.enrt_info_rt_freq_cd,
835 p_payroll_id => l_payroll_id,
836 p_business_group_id => l_ldgr_parms.business_group_id,
837 p_effective_date => p_effective_start_date,
838 p_element_type_id => l_flex_shell_rt.element_type_id,
839 p_lf_evt_ocrd_dt => l_flex_shell_rt.lf_evt_ocrd_dt,
840 p_complete_year_flag => 'Y');
841 else
842 --
843 close get_flex_shell_rt; -- Bug 4604560, Close statement top of End If
844 end if;
845 else
846 p_cmcd_cash_recd_val := ben_distribute_rates.annual_to_period(
847 p_amount => p_ann_cash_recd_val,
848 p_enrt_rt_id => l_choice_data.enrt_rt_id,
849 p_elig_per_elctbl_chc_id => l_choice_data.elig_per_elctbl_chc_id,
850 p_acty_ref_perd_cd => l_rslt.enrt_info_rt_freq_cd,
851 p_payroll_id => l_payroll_id,
852 p_business_group_id => l_ldgr_parms.business_group_id,
853 p_effective_date => p_effective_start_date,
854 p_lf_evt_ocrd_dt => l_choice_data.lf_evt_ocrd_dt,
855 p_complete_year_flag => 'Y');
856 end if;
857 -- End Bug No 4538786
858 END IF; --GEVITY
859 end if;
860 end if;
861 end if;
862
863 -- ---------------------------------------------------------------------
864 -- Rolled Up values
865 -- ---------------------------------------------------------------------
866 else --if l_ldgr.rld_up_val is not null then
867 -- as of delivery of this module, we were not using the rld_up_val field.
868 null;
869 end if; -- end of 'if' for the various val columns
870 end if; -- end of 'if' for getting result table data
871
872 hr_utility.set_location('Leaving '||l_proc, 999);
873 end; -- get_cmcd_ann_values
874 -- ---------------------------------------------------------------------
875 -- get_dt_mode
876 -- ---------------------------------------------------------------------
877 procedure get_dt_mode
878 (p_effective_date in date,
879 p_base_key_value in number,
880 p_mode out nocopy varchar2) is
881
882 l_proc varchar2(72) := g_package||'get_dt_mode';
883 l_correction boolean := TRUE;
884 l_update boolean := FALSE;
885 l_update_override boolean := FALSE;
886 l_update_change_insert boolean := FALSE;
887 --
888 begin
889 hr_utility.set_location('Entering '||l_proc, 10);
890 --
891 -- Get the appropriate update mode.
892 --
893 DT_Api.Find_DT_Upd_Modes(p_effective_date => p_effective_date,
894 p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
895 p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
896 p_base_key_value => p_base_key_value,
897 p_correction => l_correction,
898 p_update => l_update,
899 p_update_override => l_update_override,
900 p_update_change_insert => l_update_change_insert);
901 --
902 if l_update_override or l_update_change_insert then
903 p_mode := 'UPDATE_OVERRIDE';
904 elsif l_correction then
905 p_mode := 'CORRECTION';
906 else
907 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
908 hr_utility.raise_error;
909 end if;
910 hr_utility.set_location('Leaving '||l_proc, 999);
911 end; -- get_dt_mode
912 end ben_update_ledgers;
[Home] [Help]