[Home] [Help]
PACKAGE BODY: APPS.BEN_BENEFIT_PRVDD_LEDGER_API
Source
1 Package Body ben_benefit_prvdd_ledger_api as
2 /* $Header: bebplapi.pkb 120.3 2011/01/25 07:05:50 stee noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_Benefit_Prvdd_Ledger_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_Benefit_Prvdd_Ledger >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_Benefit_Prvdd_Ledger
13 (p_validate in boolean default false
14 ,p_bnft_prvdd_ldgr_id out nocopy number
15 ,p_effective_start_date out nocopy date
16 ,p_effective_end_date out nocopy date
17 ,p_prtt_ro_of_unusd_amt_flag in varchar2 default null
18 ,p_frftd_val in number default null
19 ,p_prvdd_val in number default null
20 ,p_used_val in number default null
21 ,p_person_id in number default null
22 ,p_enrt_mthd_cd in varchar2 default null
23 ,p_bnft_prvdr_pool_id in number default null
24 ,p_acty_base_rt_id in number default null
25 ,p_per_in_ler_id in number default null
26 ,p_prtt_enrt_rslt_id in number default null
27 ,p_business_group_id in number default null
28 ,p_bpl_attribute_category in varchar2 default null
29 ,p_bpl_attribute1 in varchar2 default null
30 ,p_bpl_attribute2 in varchar2 default null
31 ,p_bpl_attribute3 in varchar2 default null
32 ,p_bpl_attribute4 in varchar2 default null
33 ,p_bpl_attribute5 in varchar2 default null
34 ,p_bpl_attribute6 in varchar2 default null
35 ,p_bpl_attribute7 in varchar2 default null
36 ,p_bpl_attribute8 in varchar2 default null
37 ,p_bpl_attribute9 in varchar2 default null
38 ,p_bpl_attribute10 in varchar2 default null
39 ,p_bpl_attribute11 in varchar2 default null
40 ,p_bpl_attribute12 in varchar2 default null
41 ,p_bpl_attribute13 in varchar2 default null
42 ,p_bpl_attribute14 in varchar2 default null
43 ,p_bpl_attribute15 in varchar2 default null
44 ,p_bpl_attribute16 in varchar2 default null
45 ,p_bpl_attribute17 in varchar2 default null
46 ,p_bpl_attribute18 in varchar2 default null
47 ,p_bpl_attribute19 in varchar2 default null
48 ,p_bpl_attribute20 in varchar2 default null
49 ,p_bpl_attribute21 in varchar2 default null
50 ,p_bpl_attribute22 in varchar2 default null
51 ,p_bpl_attribute23 in varchar2 default null
55 ,p_bpl_attribute27 in varchar2 default null
52 ,p_bpl_attribute24 in varchar2 default null
53 ,p_bpl_attribute25 in varchar2 default null
54 ,p_bpl_attribute26 in varchar2 default null
56 ,p_bpl_attribute28 in varchar2 default null
57 ,p_bpl_attribute29 in varchar2 default null
58 ,p_bpl_attribute30 in varchar2 default null
59 ,p_cash_recd_val in number default null
60 ,p_rld_up_val in number default null
61 ,p_effective_date in date
62 ,p_process_enrt_flag in varchar2 default 'Y'
63 ,p_from_reinstate_enrt_flag in varchar2 default 'N',
64 p_acty_ref_perd_cd in varchar2 default null,
65 p_cmcd_frftd_val in number default null,
66 p_cmcd_prvdd_val in number default null,
67 p_cmcd_rld_up_val in number default null,
68 p_cmcd_used_val in number default null,
69 p_cmcd_cash_recd_val in number default null,
70 p_cmcd_ref_perd_cd in varchar2 default null,
71 p_ann_frftd_val in number default null,
72 p_ann_prvdd_val in number default null,
73 p_ann_rld_up_val in number default null,
74 p_ann_used_val in number default null,
75 p_ann_cash_recd_val in number default null,
76 p_object_version_number out nocopy number
77 ) is
78 --
79 -- Declare cursors and local variables
80 --
81 l_prtt_enrt_rslt_id ben_bnft_prvdd_ldgr_f.prtt_enrt_rslt_id%TYPE;
82 l_bnft_prvdd_ldgr_id ben_bnft_prvdd_ldgr_f.bnft_prvdd_ldgr_id%TYPE;
83 l_effective_start_date ben_bnft_prvdd_ldgr_f.effective_start_date%TYPE;
84 l_effective_end_date ben_bnft_prvdd_ldgr_f.effective_end_date%TYPE;
85 l_proc varchar2(72) := g_package||'create_Benefit_Prvdd_Ledger';
86 l_object_version_number ben_bnft_prvdd_ldgr_f.object_version_number%TYPE;
87 l_prtt_rt_val_id number;
88
89 l_cmcd_frftd_val number := null;
90 l_cmcd_prvdd_val number := null;
91 l_cmcd_rld_up_val number := null;
92 l_cmcd_used_val number := null;
93 l_cmcd_cash_recd_val number := null;
94 l_ann_frftd_val number := null;
95 l_ann_prvdd_val number := null;
96 l_ann_rld_up_val number := null;
97 l_ann_used_val number := null;
98 l_ann_cash_recd_val number := null;
99 l_acty_ref_perd_cd varchar2(30) := null;
100 l_cmcd_ref_perd_cd varchar2(30) := null;
101 cursor c1 is
102 select object_version_number
103 from ben_bnft_prvdd_ldgr_f bpl
104 where bnft_prvdd_ldgr_id = l_bnft_prvdd_ldgr_id
105 and p_effective_date between bpl.effective_start_date
106 and bpl.effective_end_date;
107 --
108 cursor c_bnft_pool is
109 select bpp.pgm_id
110 from ben_bnft_prvdr_pool_f bpp
111 where bpp.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id
112 and p_effective_date between bpp.effective_start_date
113 and bpp.effective_end_date;
114 --
115 l_pgm_id number;
116
117
118 --
119 begin
120 --
121 hr_utility.set_location('Entering:'|| l_proc, 10);
122 hr_utility.set_location('p_acty_base_rt_id:'||to_char(p_acty_base_rt_id), 11);
123 hr_utility.set_location('p_person_id:'||to_char(p_person_id), 11);
124 hr_utility.set_location('p_business_group_id:'||to_char(p_business_group_id), 11);
125
126 --
127 -- Issue a savepoint if operating in validation only mode
128 --
129 savepoint create_Benefit_Prvdd_Ledger;
130 --
131 hr_utility.set_location(l_proc, 20);
132 --
133 -- Process Logic
134 --
135 -- if dummy flex credit result id is passed as null then go get it.
136 if p_prtt_enrt_rslt_id is null and p_process_enrt_flag = 'Y' then
137 --
138 open c_bnft_pool;
139 fetch c_bnft_pool into l_pgm_id;
140 close c_bnft_pool;
141 --
142 ben_provider_pools.create_flex_credit_enrolment(
143 p_person_id => p_person_id,
144 p_enrt_mthd_cd => p_enrt_mthd_cd,
145 p_business_group_id => p_business_group_id,
146 p_effective_date => p_effective_date,
147 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
148 p_prtt_rt_val_id => l_prtt_rt_val_id,
149 p_per_in_ler_id => p_per_in_ler_id,
150 p_rt_val => null,
151 p_pgm_id => l_pgm_id );
152 else
153 l_prtt_enrt_rslt_id:=p_prtt_enrt_rslt_id;
154 end if;
155
156 --
157 -- Bug#2278267 - for rollover plans the communicated values calculated after the result is
158 -- created
159 if p_prtt_ro_of_unusd_amt_flag = 'Y' and p_process_enrt_flag = 'Y'
160 and p_from_reinstate_enrt_flag = 'N' then
161 --
162 l_acty_ref_perd_cd := p_acty_ref_perd_cd;
163 l_cmcd_ref_perd_cd := p_cmcd_ref_perd_cd;
164 l_cmcd_frftd_val := p_cmcd_frftd_val;
165 l_cmcd_prvdd_val := p_cmcd_prvdd_val ;
166 l_cmcd_rld_up_val := p_cmcd_rld_up_val;
167 l_cmcd_used_val := p_cmcd_used_val;
168 l_cmcd_cash_recd_val := p_cmcd_cash_recd_val;
169 l_ann_frftd_val := p_ann_frftd_val;
170 l_ann_prvdd_val := p_ann_prvdd_val;
171 l_ann_rld_up_val := p_ann_rld_up_val;
172 l_ann_used_val := p_ann_used_val;
173 l_ann_cash_recd_val := p_ann_cash_recd_val;
174
175 else
176 --
180 (p_cash_recd_val is not null and p_cmcd_cash_recd_val is null) or
177 if (p_frftd_val is not null and p_cmcd_frftd_val is null) or
178 (p_used_val is not null and p_cmcd_used_val is null) or
179 (p_prvdd_val is not null and p_cmcd_prvdd_val is null) or
181 (p_rld_up_val is not null and p_cmcd_rld_up_val is null) then
182
183 -- get the communicated and annual values
184 ben_update_ledgers.get_cmcd_ann_values
185 (p_bnft_prvdd_ldgr_id => null,
186 p_acty_base_rt_id => p_acty_base_rt_id,
187 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
188 p_business_group_id => p_business_group_id,
189 p_effective_start_date => p_effective_date,
190 p_per_in_ler_id => p_per_in_ler_id,
191 p_frftd_val => p_frftd_val,
192 p_used_val => p_used_val,
193 p_prvdd_val => p_prvdd_val,
194 p_cash_recd_val => p_cash_recd_val,
195 p_rld_up_val => p_rld_up_val,
196 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
197 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
198 p_cmcd_frftd_val => l_cmcd_frftd_val,
199 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
200 p_cmcd_rld_up_val => l_cmcd_rld_up_val,
201 p_cmcd_used_val => l_cmcd_used_val,
202 p_cmcd_cash_recd_val => l_cmcd_cash_recd_val,
203 p_ann_frftd_val => l_ann_frftd_val,
204 p_ann_prvdd_val => l_ann_prvdd_val,
205 p_ann_rld_up_val => l_ann_rld_up_val,
206 p_ann_used_val => l_ann_used_val,
207 p_ann_cash_recd_val => l_ann_cash_recd_val);
208 else
209 l_acty_ref_perd_cd := p_acty_ref_perd_cd;
210 l_cmcd_ref_perd_cd := p_cmcd_ref_perd_cd;
211 l_cmcd_frftd_val := p_cmcd_frftd_val;
212 l_cmcd_prvdd_val := p_cmcd_prvdd_val ;
213 l_cmcd_rld_up_val := p_cmcd_rld_up_val;
214 l_cmcd_used_val := p_cmcd_used_val;
215 l_cmcd_cash_recd_val := p_cmcd_cash_recd_val;
216 l_ann_frftd_val := p_ann_frftd_val;
217 l_ann_prvdd_val := p_ann_prvdd_val;
218 l_ann_rld_up_val := p_ann_rld_up_val;
219 l_ann_used_val := p_ann_used_val;
220 l_ann_cash_recd_val := p_ann_cash_recd_val;
221 end if;
222 --
223 end if;
224
225
226 --
227 begin
228 --
229 -- Start of API User Hook for the before hook of create_Benefit_Prvdd_Ledger
230 --
231 ben_Benefit_Prvdd_Ledger_bk1.create_Benefit_Prvdd_Ledger_b
232 (
233 p_prtt_ro_of_unusd_amt_flag => p_prtt_ro_of_unusd_amt_flag
234 ,p_frftd_val => p_frftd_val
235 ,p_prvdd_val => p_prvdd_val
236 ,p_used_val => p_used_val
237 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
238 ,p_acty_base_rt_id => p_acty_base_rt_id
239 ,p_per_in_ler_id => p_per_in_ler_id
240 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
241 ,p_business_group_id => p_business_group_id
242 ,p_bpl_attribute_category => p_bpl_attribute_category
243 ,p_bpl_attribute1 => p_bpl_attribute1
244 ,p_bpl_attribute2 => p_bpl_attribute2
245 ,p_bpl_attribute3 => p_bpl_attribute3
246 ,p_bpl_attribute4 => p_bpl_attribute4
247 ,p_bpl_attribute5 => p_bpl_attribute5
248 ,p_bpl_attribute6 => p_bpl_attribute6
249 ,p_bpl_attribute7 => p_bpl_attribute7
250 ,p_bpl_attribute8 => p_bpl_attribute8
251 ,p_bpl_attribute9 => p_bpl_attribute9
252 ,p_bpl_attribute10 => p_bpl_attribute10
253 ,p_bpl_attribute11 => p_bpl_attribute11
254 ,p_bpl_attribute12 => p_bpl_attribute12
255 ,p_bpl_attribute13 => p_bpl_attribute13
256 ,p_bpl_attribute14 => p_bpl_attribute14
257 ,p_bpl_attribute15 => p_bpl_attribute15
258 ,p_bpl_attribute16 => p_bpl_attribute16
259 ,p_bpl_attribute17 => p_bpl_attribute17
260 ,p_bpl_attribute18 => p_bpl_attribute18
261 ,p_bpl_attribute19 => p_bpl_attribute19
262 ,p_bpl_attribute20 => p_bpl_attribute20
263 ,p_bpl_attribute21 => p_bpl_attribute21
264 ,p_bpl_attribute22 => p_bpl_attribute22
265 ,p_bpl_attribute23 => p_bpl_attribute23
266 ,p_bpl_attribute24 => p_bpl_attribute24
267 ,p_bpl_attribute25 => p_bpl_attribute25
268 ,p_bpl_attribute26 => p_bpl_attribute26
269 ,p_bpl_attribute27 => p_bpl_attribute27
270 ,p_bpl_attribute28 => p_bpl_attribute28
271 ,p_bpl_attribute29 => p_bpl_attribute29
272 ,p_bpl_attribute30 => p_bpl_attribute30
273 ,p_cash_recd_val => p_cash_recd_val
274 ,p_rld_up_val => p_rld_up_val
275 ,p_effective_date => trunc(p_effective_date),
276 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
277 p_cmcd_frftd_val => l_cmcd_frftd_val,
278 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
279 p_cmcd_rld_up_val => l_cmcd_rld_up_val,
283 p_ann_frftd_val => l_ann_frftd_val,
280 p_cmcd_used_val => l_cmcd_used_val,
281 p_cmcd_cash_recd_val => l_cmcd_cash_recd_val,
282 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
284 p_ann_prvdd_val => l_ann_prvdd_val,
285 p_ann_rld_up_val => l_ann_rld_up_val,
286 p_ann_used_val => l_ann_used_val,
287 p_ann_cash_recd_val => l_ann_cash_recd_val
288 );
289 exception
290 when hr_api.cannot_find_prog_unit then
291 hr_api.cannot_find_prog_unit_error
292 (
293 p_module_name => 'CREATE_Benefit_Prvdd_Ledger'
294 ,p_hook_type => 'BP'
295 );
296 --
297 -- End of API User Hook for the before hook of create_Benefit_Prvdd_Ledger
298 --
299 end;
300 --
301 ben_bpl_ins.ins
302 (
303 p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
304 ,p_effective_start_date => l_effective_start_date
305 ,p_effective_end_date => l_effective_end_date
306 ,p_prtt_ro_of_unusd_amt_flag => p_prtt_ro_of_unusd_amt_flag
307 ,p_frftd_val => p_frftd_val
308 ,p_prvdd_val => p_prvdd_val
309 ,p_used_val => p_used_val
310 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
311 ,p_acty_base_rt_id => p_acty_base_rt_id
312 ,p_per_in_ler_id => p_per_in_ler_id
313 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
314 ,p_business_group_id => p_business_group_id
315 ,p_bpl_attribute_category => p_bpl_attribute_category
316 ,p_bpl_attribute1 => p_bpl_attribute1
317 ,p_bpl_attribute2 => p_bpl_attribute2
318 ,p_bpl_attribute3 => p_bpl_attribute3
319 ,p_bpl_attribute4 => p_bpl_attribute4
320 ,p_bpl_attribute5 => p_bpl_attribute5
321 ,p_bpl_attribute6 => p_bpl_attribute6
322 ,p_bpl_attribute7 => p_bpl_attribute7
323 ,p_bpl_attribute8 => p_bpl_attribute8
324 ,p_bpl_attribute9 => p_bpl_attribute9
325 ,p_bpl_attribute10 => p_bpl_attribute10
326 ,p_bpl_attribute11 => p_bpl_attribute11
327 ,p_bpl_attribute12 => p_bpl_attribute12
328 ,p_bpl_attribute13 => p_bpl_attribute13
329 ,p_bpl_attribute14 => p_bpl_attribute14
330 ,p_bpl_attribute15 => p_bpl_attribute15
331 ,p_bpl_attribute16 => p_bpl_attribute16
332 ,p_bpl_attribute17 => p_bpl_attribute17
333 ,p_bpl_attribute18 => p_bpl_attribute18
334 ,p_bpl_attribute19 => p_bpl_attribute19
335 ,p_bpl_attribute20 => p_bpl_attribute20
336 ,p_bpl_attribute21 => p_bpl_attribute21
337 ,p_bpl_attribute22 => p_bpl_attribute22
338 ,p_bpl_attribute23 => p_bpl_attribute23
339 ,p_bpl_attribute24 => p_bpl_attribute24
340 ,p_bpl_attribute25 => p_bpl_attribute25
341 ,p_bpl_attribute26 => p_bpl_attribute26
342 ,p_bpl_attribute27 => p_bpl_attribute27
343 ,p_bpl_attribute28 => p_bpl_attribute28
344 ,p_bpl_attribute29 => p_bpl_attribute29
345 ,p_bpl_attribute30 => p_bpl_attribute30
346 ,p_object_version_number => l_object_version_number
347 ,p_cash_recd_val => p_cash_recd_val
348 ,p_rld_up_val => p_rld_up_val
349 ,p_effective_date => trunc(p_effective_date),
350 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
351 p_cmcd_frftd_val => l_cmcd_frftd_val,
352 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
353 p_cmcd_rld_up_val => l_cmcd_rld_up_val,
354 p_cmcd_used_val => l_cmcd_used_val,
355 p_cmcd_cash_recd_val => l_cmcd_cash_recd_val,
356 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
357 p_ann_frftd_val => l_ann_frftd_val,
358 p_ann_prvdd_val => l_ann_prvdd_val,
359 p_ann_rld_up_val => l_ann_rld_up_val,
360 p_ann_used_val => l_ann_used_val,
361 p_ann_cash_recd_val => l_ann_cash_recd_val
362 );
363 --
364 -- Create/update the enrollment to include the rollover amount.
365 --
366 if p_prtt_ro_of_unusd_amt_flag='Y' and p_process_enrt_flag = 'Y'
367 and p_from_reinstate_enrt_flag = 'N'
368 then
369 ben_provider_pools.create_rollover_enrollment(
370 p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id,
371 p_person_id => p_person_id,
372 p_per_in_ler_id => p_per_in_ler_id,
373 p_effective_date => p_effective_date,
374 p_datetrack_mode => hr_api.g_update,
375 p_acty_base_rt_id => p_acty_base_rt_id,
376 p_rlovr_amt => p_used_val,
377 p_old_rlovr_amt => 0, -- Creation so old value is 0.
378 p_business_group_id => p_business_group_id,
379 p_enrt_mthd_cd => p_enrt_mthd_cd
380 );
381 --
382 ben_update_ledgers.get_cmcd_ann_values
383 (p_bnft_prvdd_ldgr_id => null,
384 p_acty_base_rt_id => p_acty_base_rt_id,
385 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
386 p_business_group_id => p_business_group_id,
387 p_effective_start_date => p_effective_date,
388 p_per_in_ler_id => p_per_in_ler_id,
389 p_frftd_val => p_frftd_val,
390 p_used_val => p_used_val,
391 p_prvdd_val => p_prvdd_val,
395 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
392 p_cash_recd_val => p_cash_recd_val,
393 p_rld_up_val => p_rld_up_val,
394 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
396 p_cmcd_frftd_val => l_cmcd_frftd_val,
397 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
398 p_cmcd_rld_up_val => l_cmcd_rld_up_val,
399 p_cmcd_used_val => l_cmcd_used_val,
400 p_cmcd_cash_recd_val => l_cmcd_cash_recd_val,
401 p_ann_frftd_val => l_ann_frftd_val,
402 p_ann_prvdd_val => l_ann_prvdd_val,
403 p_ann_rld_up_val => l_ann_rld_up_val,
404 p_ann_used_val => l_ann_used_val,
405 p_ann_cash_recd_val => l_ann_cash_recd_val);
406 --
407 open c1;
408 fetch c1 into l_object_version_number;
409 close c1;
410 --
411 ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
412 p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
413 ,p_effective_start_date => l_effective_start_date
414 ,p_effective_end_date => l_effective_end_date
415 ,p_acty_ref_perd_cd => l_acty_ref_perd_cd
416 ,p_cmcd_frftd_val => l_cmcd_frftd_val
417 ,p_cmcd_prvdd_val => l_cmcd_prvdd_val
418 ,p_cmcd_rld_up_val => l_cmcd_rld_up_val
419 ,p_cmcd_used_val => l_cmcd_used_val
420 ,p_cmcd_cash_recd_val => l_cmcd_cash_recd_val
421 ,p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd
422 ,p_ann_frftd_val => l_ann_frftd_val
423 ,p_ann_prvdd_val => l_ann_prvdd_val
424 ,p_ann_rld_up_val => l_ann_rld_up_val
425 ,p_ann_used_val => l_ann_used_val
426 ,p_ann_cash_recd_val => l_ann_cash_recd_val
427 ,p_object_version_number => l_object_version_number
428 ,p_effective_date => trunc(p_effective_date)
429 ,p_datetrack_mode => 'CORRECTION');
430 --
431 end if;
432 --
433 begin
434 --
435 -- Start of API User Hook for the after hook of create_Benefit_Prvdd_Ledger
436 --
437 ben_Benefit_Prvdd_Ledger_bk1.create_Benefit_Prvdd_Ledger_a
438 (
439 p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
440 ,p_effective_start_date => l_effective_start_date
441 ,p_effective_end_date => l_effective_end_date
442 ,p_prtt_ro_of_unusd_amt_flag => p_prtt_ro_of_unusd_amt_flag
443 ,p_frftd_val => p_frftd_val
444 ,p_prvdd_val => p_prvdd_val
445 ,p_used_val => p_used_val
446 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
447 ,p_acty_base_rt_id => p_acty_base_rt_id
448 ,p_per_in_ler_id => p_per_in_ler_id
449 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
450 ,p_business_group_id => p_business_group_id
451 ,p_bpl_attribute_category => p_bpl_attribute_category
452 ,p_bpl_attribute1 => p_bpl_attribute1
453 ,p_bpl_attribute2 => p_bpl_attribute2
454 ,p_bpl_attribute3 => p_bpl_attribute3
455 ,p_bpl_attribute4 => p_bpl_attribute4
456 ,p_bpl_attribute5 => p_bpl_attribute5
457 ,p_bpl_attribute6 => p_bpl_attribute6
458 ,p_bpl_attribute7 => p_bpl_attribute7
459 ,p_bpl_attribute8 => p_bpl_attribute8
460 ,p_bpl_attribute9 => p_bpl_attribute9
461 ,p_bpl_attribute10 => p_bpl_attribute10
462 ,p_bpl_attribute11 => p_bpl_attribute11
463 ,p_bpl_attribute12 => p_bpl_attribute12
464 ,p_bpl_attribute13 => p_bpl_attribute13
465 ,p_bpl_attribute14 => p_bpl_attribute14
466 ,p_bpl_attribute15 => p_bpl_attribute15
467 ,p_bpl_attribute16 => p_bpl_attribute16
468 ,p_bpl_attribute17 => p_bpl_attribute17
469 ,p_bpl_attribute18 => p_bpl_attribute18
470 ,p_bpl_attribute19 => p_bpl_attribute19
471 ,p_bpl_attribute20 => p_bpl_attribute20
472 ,p_bpl_attribute21 => p_bpl_attribute21
473 ,p_bpl_attribute22 => p_bpl_attribute22
474 ,p_bpl_attribute23 => p_bpl_attribute23
475 ,p_bpl_attribute24 => p_bpl_attribute24
476 ,p_bpl_attribute25 => p_bpl_attribute25
477 ,p_bpl_attribute26 => p_bpl_attribute26
478 ,p_bpl_attribute27 => p_bpl_attribute27
479 ,p_bpl_attribute28 => p_bpl_attribute28
480 ,p_bpl_attribute29 => p_bpl_attribute29
481 ,p_bpl_attribute30 => p_bpl_attribute30
482 ,p_object_version_number => l_object_version_number
483 ,p_cash_recd_val => p_cash_recd_val
484 ,p_rld_up_val => p_rld_up_val
485 ,p_effective_date => trunc(p_effective_date),
486 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
487 p_cmcd_frftd_val => l_cmcd_frftd_val,
488 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
489 p_cmcd_rld_up_val => l_cmcd_rld_up_val,
490 p_cmcd_used_val => l_cmcd_used_val,
491 p_cmcd_cash_recd_val => l_cmcd_cash_recd_val,
492 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
496 p_ann_used_val => l_ann_used_val,
493 p_ann_frftd_val => l_ann_frftd_val,
494 p_ann_prvdd_val => l_ann_prvdd_val,
495 p_ann_rld_up_val => l_ann_rld_up_val,
497 p_ann_cash_recd_val => l_ann_cash_recd_val
498 );
499 exception
500 when hr_api.cannot_find_prog_unit then
501 hr_api.cannot_find_prog_unit_error
502 (p_module_name => 'CREATE_Benefit_Prvdd_Ledger'
503 ,p_hook_type => 'AP'
504 );
505 --
506 -- End of API User Hook for the after hook of create_Benefit_Prvdd_Ledger
507 --
508 end;
509 --
510 hr_utility.set_location(l_proc, 60);
511 --
512 -- When in validation only mode raise the Validate_Enabled exception
513 --
514 if p_validate then
515 raise hr_api.validate_enabled;
516 end if;
517 --
518 -- Set all output arguments
519 --
520 p_bnft_prvdd_ldgr_id := l_bnft_prvdd_ldgr_id;
521 p_effective_start_date := l_effective_start_date;
522 p_effective_end_date := l_effective_end_date;
523 p_object_version_number := l_object_version_number;
524 --
525 hr_utility.set_location(' Leaving:'||l_proc, 70);
526 --
527 exception
528 --
529 when hr_api.validate_enabled then
530 --
531 -- As the Validate_Enabled exception has been raised
532 -- we must rollback to the savepoint
533 --
534 ROLLBACK TO create_Benefit_Prvdd_Ledger;
535 --
536 -- Only set output warning arguments
537 -- (Any key or derived arguments must be set to null
538 -- when validation only mode is being used.)
539 --
540 p_bnft_prvdd_ldgr_id := null;
541 p_effective_start_date := null;
542 p_effective_end_date := null;
543 p_object_version_number := null;
544 hr_utility.set_location(' Leaving:'||l_proc, 80);
545 --
546 when others then
547 --
548 -- A validation or unexpected error has occured
549 --
550 ROLLBACK TO create_Benefit_Prvdd_Ledger;
551 p_bnft_prvdd_ldgr_id := null;
552 p_effective_start_date := null;
553 p_effective_end_date := null;
554 p_object_version_number := null;
555 hr_utility.set_location(' Leaving:'||l_proc, 90);
556 raise;
557 --
558 end create_Benefit_Prvdd_Ledger;
559 --
560 -- ----------------------------------------------------------------------------
561 -- |------------------------< create_Benefit_Prvdd_Ledger_w >-----------------|
562 -- ----------------------------------------------------------------------------
563 --
564 procedure create_Benefit_Prvdd_Ledger_w
565 (p_validate in varchar2 default 'FALSE'
566 ,p_bnft_prvdd_ldgr_id out nocopy number
567 ,p_effective_start_date out nocopy date
568 ,p_effective_end_date out nocopy date
569 ,p_prtt_ro_of_unusd_amt_flag in varchar2 default null
570 ,p_frftd_val in number default null
571 ,p_prvdd_val in number default null
572 ,p_used_val in number default null
573 ,p_person_id in number default null
574 ,p_enrt_mthd_cd in varchar2 default null
575 ,p_bnft_prvdr_pool_id in number default null
576 ,p_acty_base_rt_id in number default null
577 ,p_per_in_ler_id in number default null
578 ,p_prtt_enrt_rslt_id in number default null
579 ,p_business_group_id in number default null
580 ,p_cash_recd_val in number default null
581 ,p_rld_up_val in number default null
582 ,p_effective_date in date
583 ,p_process_enrt_flag in varchar2 default 'Y'
584 ,p_from_reinstate_enrt_flag in varchar2 default 'N',
585 p_acty_ref_perd_cd in varchar2 default null,
586 p_cmcd_frftd_val in number default null,
587 p_cmcd_prvdd_val in number default null,
588 p_cmcd_rld_up_val in number default null,
589 p_cmcd_used_val in number default null,
590 p_cmcd_cash_recd_val in number default null,
591 p_cmcd_ref_perd_cd in varchar2 default null,
592 p_ann_frftd_val in number default null,
593 p_ann_prvdd_val in number default null,
594 p_ann_rld_up_val in number default null,
595 p_ann_used_val in number default null,
596 p_ann_cash_recd_val in number default null,
597 p_object_version_number out nocopy number
598 )is
599 --
600 -- Declare cursors and local variables
601 --
602 l_bnft_prvdd_ldgr_id ben_bnft_prvdd_ldgr_f.bnft_prvdd_ldgr_id%TYPE;
603 l_effective_start_date ben_bnft_prvdd_ldgr_f.effective_start_date%TYPE;
604 l_effective_end_date ben_bnft_prvdd_ldgr_f.effective_end_date%TYPE;
605 l_object_version_number ben_bnft_prvdd_ldgr_f.object_version_number%TYPE;
606 l_validate BOOLEAN;
607
608 l_proc varchar2(72) := g_package||'create_Benefit_Prvdd_Ledger_w';
609 --
610 begin
611 --
612 hr_utility.set_location('Entering:'|| l_proc, 10);
613 hr_utility.set_location('p_acty_base_rt_id:'||to_char(p_acty_base_rt_id), 11);
614 hr_utility.set_location('p_person_id:'||to_char(p_person_id), 11);
615 hr_utility.set_location('p_business_group_id:'||to_char(p_business_group_id), 11);
616 --
617 hr_utility.set_location(l_proc, 20);
618 --
622 else
619 if upper(p_validate) = 'TRUE'
620 then
621 l_validate := TRUE;
623 l_validate := FALSE;
624 end if;
625 --
626 hr_utility.set_location(l_proc, 30);
627 --
628 ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger
629 (
630 p_validate => l_validate
631 ,p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
632 ,p_effective_start_date => l_effective_start_date
633 ,p_effective_end_date => l_effective_end_date
634 ,p_prtt_ro_of_unusd_amt_flag => p_prtt_ro_of_unusd_amt_flag
635 ,p_frftd_val => p_frftd_val
636 ,p_prvdd_val => p_prvdd_val
637 ,p_used_val => p_used_val
638 ,p_person_id => p_person_id
639 ,p_enrt_mthd_cd => p_enrt_mthd_cd
640 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
641 ,p_acty_base_rt_id => p_acty_base_rt_id
642 ,p_per_in_ler_id => p_per_in_ler_id
643 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
644 ,p_business_group_id => p_business_group_id
645 ,p_object_version_number => l_object_version_number
646 ,p_cash_recd_val => p_cash_recd_val
647 ,p_rld_up_val => p_rld_up_val
648 ,p_effective_date => trunc(p_effective_date)
649 ,p_process_enrt_flag => p_process_enrt_flag
650 ,p_from_reinstate_enrt_flag => p_from_reinstate_enrt_flag,
651 p_acty_ref_perd_cd => p_acty_ref_perd_cd,
652 p_cmcd_frftd_val => p_cmcd_frftd_val,
653 p_cmcd_prvdd_val => p_cmcd_prvdd_val,
654 p_cmcd_rld_up_val => p_cmcd_rld_up_val,
655 p_cmcd_used_val => p_cmcd_used_val,
656 p_cmcd_cash_recd_val => p_cmcd_cash_recd_val,
657 p_cmcd_ref_perd_cd => p_cmcd_ref_perd_cd,
658 p_ann_frftd_val => p_ann_frftd_val,
659 p_ann_prvdd_val => p_ann_prvdd_val,
660 p_ann_rld_up_val => p_ann_rld_up_val,
661 p_ann_used_val => p_ann_used_val,
662 p_ann_cash_recd_val => p_ann_cash_recd_val
663 );
664 --
665 hr_utility.set_location(l_proc, 40);
666 --
667 -- Set all output arguments
668 --
669 p_bnft_prvdd_ldgr_id := l_bnft_prvdd_ldgr_id;
670 p_effective_start_date := l_effective_start_date;
671 p_effective_end_date := l_effective_end_date;
672 p_object_version_number := l_object_version_number;
673 --
674 hr_utility.set_location(' Leaving:'||l_proc, 50);
675 --
676 exception
677 --
678 when others then
679 p_bnft_prvdd_ldgr_id := null;
680 p_effective_start_date := null;
681 p_effective_end_date := null;
682 p_object_version_number := null;
683 hr_utility.set_location(' Leaving:'||l_proc, 60);
684 fnd_msg_pub.add;
685 --
686 end create_Benefit_Prvdd_Ledger_w;
687 --
688 -- ----------------------------------------------------------------------------
689 -- |------------------------< update_Benefit_Prvdd_Ledger >--- ------------------|
690 -- ----------------------------------------------------------------------------
691 --
692 procedure update_Benefit_Prvdd_Ledger
693 (p_validate in boolean default false
694 ,p_bnft_prvdd_ldgr_id in number
695 ,p_effective_start_date out nocopy date
696 ,p_effective_end_date out nocopy date
697 ,p_prtt_ro_of_unusd_amt_flag in varchar2 default hr_api.g_varchar2
698 ,p_frftd_val in number default hr_api.g_number
699 ,p_prvdd_val in number default hr_api.g_number
700 ,p_used_val in number default hr_api.g_number
701 ,p_bnft_prvdr_pool_id in number default hr_api.g_number
702 ,p_acty_base_rt_id in number default hr_api.g_number
703 ,p_per_in_ler_id in number default hr_api.g_number
704 ,p_prtt_enrt_rslt_id in number default hr_api.g_number
705 ,p_business_group_id in number default hr_api.g_number
706 ,p_bpl_attribute_category in varchar2 default hr_api.g_varchar2
707 ,p_bpl_attribute1 in varchar2 default hr_api.g_varchar2
708 ,p_bpl_attribute2 in varchar2 default hr_api.g_varchar2
709 ,p_bpl_attribute3 in varchar2 default hr_api.g_varchar2
710 ,p_bpl_attribute4 in varchar2 default hr_api.g_varchar2
711 ,p_bpl_attribute5 in varchar2 default hr_api.g_varchar2
712 ,p_bpl_attribute6 in varchar2 default hr_api.g_varchar2
713 ,p_bpl_attribute7 in varchar2 default hr_api.g_varchar2
714 ,p_bpl_attribute8 in varchar2 default hr_api.g_varchar2
715 ,p_bpl_attribute9 in varchar2 default hr_api.g_varchar2
716 ,p_bpl_attribute10 in varchar2 default hr_api.g_varchar2
717 ,p_bpl_attribute11 in varchar2 default hr_api.g_varchar2
718 ,p_bpl_attribute12 in varchar2 default hr_api.g_varchar2
719 ,p_bpl_attribute13 in varchar2 default hr_api.g_varchar2
720 ,p_bpl_attribute14 in varchar2 default hr_api.g_varchar2
721 ,p_bpl_attribute15 in varchar2 default hr_api.g_varchar2
722 ,p_bpl_attribute16 in varchar2 default hr_api.g_varchar2
723 ,p_bpl_attribute17 in varchar2 default hr_api.g_varchar2
724 ,p_bpl_attribute18 in varchar2 default hr_api.g_varchar2
725 ,p_bpl_attribute19 in varchar2 default hr_api.g_varchar2
726 ,p_bpl_attribute20 in varchar2 default hr_api.g_varchar2
730 ,p_bpl_attribute24 in varchar2 default hr_api.g_varchar2
727 ,p_bpl_attribute21 in varchar2 default hr_api.g_varchar2
728 ,p_bpl_attribute22 in varchar2 default hr_api.g_varchar2
729 ,p_bpl_attribute23 in varchar2 default hr_api.g_varchar2
731 ,p_bpl_attribute25 in varchar2 default hr_api.g_varchar2
732 ,p_bpl_attribute26 in varchar2 default hr_api.g_varchar2
733 ,p_bpl_attribute27 in varchar2 default hr_api.g_varchar2
734 ,p_bpl_attribute28 in varchar2 default hr_api.g_varchar2
735 ,p_bpl_attribute29 in varchar2 default hr_api.g_varchar2
736 ,p_bpl_attribute30 in varchar2 default hr_api.g_varchar2
737 ,p_cash_recd_val in number default hr_api.g_number
738 ,p_rld_up_val in number default hr_api.g_number
739 ,p_effective_date in date
740 ,p_datetrack_mode in varchar2
741 ,p_process_enrt_flag in varchar2 default 'Y'
742 ,p_from_reinstate_enrt_flag in varchar2 default 'N',
743 p_acty_ref_perd_cd in varchar2 default hr_api.g_varchar2,
744 p_cmcd_frftd_val in number default hr_api.g_number,
745 p_cmcd_prvdd_val in number default hr_api.g_number,
746 p_cmcd_rld_up_val in number default hr_api.g_number,
747 p_cmcd_used_val in number default hr_api.g_number,
748 p_cmcd_cash_recd_val in number default hr_api.g_number,
749 p_cmcd_ref_perd_cd in varchar2 default hr_api.g_varchar2,
750 p_ann_frftd_val in number default hr_api.g_number,
751 p_ann_prvdd_val in number default hr_api.g_number,
752 p_ann_rld_up_val in number default hr_api.g_number,
753 p_ann_used_val in number default hr_api.g_number,
754 p_ann_cash_recd_val in number default hr_api.g_number,
755 p_object_version_number in out nocopy number
756 ) is
757 --
758 -- Declare cursors and local variables
759 --
760 l_proc varchar2(72) := g_package||'update_Benefit_Prvdd_Ledger';
761 l_object_version_number ben_bnft_prvdd_ldgr_f.object_version_number%TYPE;
762 l_dup_object_version_number ben_bnft_prvdd_ldgr_f.object_version_number%TYPE;
763 l_effective_start_date ben_bnft_prvdd_ldgr_f.effective_start_date%TYPE;
764 l_effective_end_date ben_bnft_prvdd_ldgr_f.effective_end_date%TYPE;
765 l_bnft_prvdr_pool_id number;
766 l_person_id number;
767 l_acty_base_rt_id number;
768 l_per_in_ler_id number;
769 l_enrt_mthd_cd varchar2(30);
770 l_old_used_val number;
771 --
772 -- cursor to get the ledger info
773 --
774 cursor c_ledger_info is
775 select bpl.bnft_prvdr_pool_id,
776 pen.person_id,
777 bpl.acty_base_rt_id,
778 bpl.per_in_ler_id,
779 bpl.used_val,
780 pen.enrt_mthd_cd
781 from ben_bnft_prvdd_ldgr_f bpl,
782 ben_prtt_enrt_rslt_f pen,
783 -- Bug : 1634870 : UK changes
784 ben_per_in_ler pil
785 where bpl.bnft_prvdd_ldgr_id=p_bnft_prvdd_ldgr_id and
786 bpl.object_version_number=p_object_version_number and
787 bpl.business_group_id=p_business_group_id and
788 pen.prtt_enrt_rslt_id=bpl.prtt_enrt_rslt_id and
789 pen.business_group_id=p_business_group_id and
790 pen.prtt_enrt_rslt_stat_cd is null and
791 -- Bug : 1634870 : UK changes
792 pil.per_in_ler_id=bpl.per_in_ler_id and
793 pil.business_group_id=bpl.business_group_id and
794 pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') and
795 p_effective_date between
796 pen.effective_start_date and pen.effective_end_date;
797 --
798 -- Bug: 3611160/4169180: Added this cursor.
799 cursor c1 is
800 select object_version_number
801 from ben_bnft_prvdd_ldgr_f bpl
802 where bnft_prvdd_ldgr_id = p_bnft_prvdd_ldgr_id
803 and p_effective_date between bpl.effective_start_date
804 and bpl.effective_end_date;
805 --
806
807 l_cmcd_frftd_val number := null;
808 l_cmcd_prvdd_val number := null;
809 l_cmcd_rld_up_val number := null;
810 l_cmcd_used_val number := null;
811 l_cmcd_cash_recd_val number := null;
812 l_ann_frftd_val number := null;
813 l_ann_prvdd_val number := null;
814 l_ann_rld_up_val number := null;
815 l_ann_used_val number := null;
816 l_ann_cash_recd_val number := null;
817 l_acty_ref_perd_cd varchar2(30) := null;
818 l_cmcd_ref_perd_cd varchar2(30) := null;
819 l_hr_api_g_number number := hr_api.g_number;
820 l_bnft_prvdd_ldgr_updated boolean := FALSE;
821
822 begin
823 --
824 hr_utility.set_location('Entering:'|| l_proc, 10);
825
826 -- Issue a savepoint if operating in validation only mode
827 savepoint update_Benefit_Prvdd_Ledger;
828
829 hr_utility.set_location(l_proc, 20);
830
831 -- Process Logic
832 l_object_version_number := p_object_version_number;
833
834 --
835 -- Get the info to do the result adjustment
836 -- have to get it before the delete takes place
837 --
838 if p_prtt_ro_of_unusd_amt_flag='Y' and p_process_enrt_flag = 'Y' then
839 open c_ledger_info;
840 fetch c_ledger_info into
841 l_bnft_prvdr_pool_id,
842 l_person_id,
843 l_acty_base_rt_id,
844 l_per_in_ler_id,
845 l_old_used_val,
846 l_enrt_mthd_cd
847 ;
848 if c_ledger_info%notfound then
849 --
853 fnd_message.raise_error;
850 -- The primary key is invalid therefore we must error
851 --
852 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
854 end if;
855 close c_ledger_info;
856 end if;
857
858 --
859 -- Create/update the enrollment to include the rollover amount.
860 -- (lmcdonal 20-Feb-02: moved this to here from after the update of the ldgr
861 -- because we need the prtt-rt to have been created to go get the cmcd and annual
862 -- values to put onto the ldgr)
863 if p_prtt_ro_of_unusd_amt_flag='Y' and p_process_enrt_flag = 'Y'
864 and p_from_reinstate_enrt_flag = 'N'
865 then
866 ben_provider_pools.create_rollover_enrollment(
867 p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id,
868 p_person_id => l_person_id,
869 p_per_in_ler_id => p_per_in_ler_id,
870 p_effective_date => p_effective_date,
871 p_datetrack_mode => p_datetrack_mode,
872 p_acty_base_rt_id => p_acty_base_rt_id,
873 p_rlovr_amt => p_used_val,
874 p_old_rlovr_amt => l_old_used_val,
875 p_business_group_id => p_business_group_id,
876 p_enrt_mthd_cd => l_enrt_mthd_cd);
877 --
878 -- Bug: 3611160/4169180:
879 -- If create_rollover_enrollment calls update_Benefit_Prvdd_Ledger internally,
880 -- then fetch the new object_version_number.
881 -- If the bnft_prvdd_ldgr row is updated, then avoid calling the ben_bpl_upd.upd.
882 --
883 hr_utility.set_location('p_bnft_prvdd_ldgr_id :'||p_bnft_prvdd_ldgr_id, 20);
884 open c1;
885 fetch c1 into l_dup_object_version_number;
886 close c1;
887 --
888 hr_utility.set_location('l_dup_object_version_number :'||l_dup_object_version_number, 20);
889 --
890 -- bug 10649653. Only call update the ledger if it is found.
891 --
892 if (l_dup_object_version_number IS NOT NULL
893 AND l_object_version_number <> l_dup_object_version_number)
894 or l_dup_object_version_number is NULL then -- bug 10649653
895 l_bnft_prvdd_ldgr_updated := TRUE;
896 l_object_version_number := l_dup_object_version_number;
897 end if;
898 -- Bug: 3611160/4169180 Changes end...
899 end if;
900
901 if (p_frftd_val is not null and p_frftd_val <> l_hr_api_g_number and
902 (p_cmcd_frftd_val is null or p_cmcd_frftd_val = l_hr_api_g_number)) or
903 (p_used_val is not null and p_used_val <> l_hr_api_g_number and
904 (p_cmcd_used_val is null or p_cmcd_used_val = l_hr_api_g_number)) or
905 (p_prvdd_val is not null and p_prvdd_val <> l_hr_api_g_number and
906 (p_cmcd_prvdd_val is null or p_cmcd_prvdd_val = l_hr_api_g_number)) or
907 (p_cash_recd_val is not null and p_cash_recd_val <> l_hr_api_g_number and
908 (p_cmcd_cash_recd_val is null or p_cmcd_cash_recd_val = l_hr_api_g_number)) or
909 (p_rld_up_val is not null and p_rld_up_val <> l_hr_api_g_number and
910 (p_cmcd_rld_up_val is null or p_cmcd_rld_up_val = l_hr_api_g_number)) then
911 -- get the communicated and annual values
912 ben_update_ledgers.get_cmcd_ann_values
913 (p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id,
914 p_acty_base_rt_id => p_acty_base_rt_id,
915 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
916 p_business_group_id => p_business_group_id,
917 p_effective_start_date => p_effective_date,
918 p_per_in_ler_id => p_per_in_ler_id,
919 p_frftd_val => p_frftd_val,
920 p_used_val => p_used_val,
921 p_prvdd_val => p_prvdd_val,
922 p_cash_recd_val => p_cash_recd_val,
923 p_rld_up_val => p_rld_up_val,
924 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
925 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
926 p_cmcd_frftd_val => l_cmcd_frftd_val,
927 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
928 p_cmcd_rld_up_val => l_cmcd_rld_up_val,
929 p_cmcd_used_val => l_cmcd_used_val,
930 p_cmcd_cash_recd_val => l_cmcd_cash_recd_val,
931 p_ann_frftd_val => l_ann_frftd_val,
932 p_ann_prvdd_val => l_ann_prvdd_val,
933 p_ann_rld_up_val => l_ann_rld_up_val,
934 p_ann_used_val => l_ann_used_val,
935 p_ann_cash_recd_val => l_ann_cash_recd_val);
936 else
937 l_acty_ref_perd_cd := p_acty_ref_perd_cd;
938 l_cmcd_ref_perd_cd := p_cmcd_ref_perd_cd;
939 l_cmcd_frftd_val := p_cmcd_frftd_val;
940 l_cmcd_prvdd_val := p_cmcd_prvdd_val ;
941 l_cmcd_rld_up_val := p_cmcd_rld_up_val;
942 l_cmcd_used_val := p_cmcd_used_val;
943 l_cmcd_cash_recd_val := p_cmcd_cash_recd_val;
944 l_ann_frftd_val := p_ann_frftd_val;
945 l_ann_prvdd_val := p_ann_prvdd_val;
946 l_ann_rld_up_val := p_ann_rld_up_val;
947 l_ann_used_val := p_ann_used_val;
948 l_ann_cash_recd_val := p_ann_cash_recd_val;
949 end if;
950 --
951 if (NOT l_bnft_prvdd_ldgr_updated) then -- Bug: 3611160/4169180 Added this condition
952 --
953 begin
954 -- Start of API User Hook for the before hook of update_Benefit_Prvdd_Ledger
955 --
956 ben_Benefit_Prvdd_Ledger_bk2.update_Benefit_Prvdd_Ledger_b
957 (
958 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
959 ,p_prtt_ro_of_unusd_amt_flag => p_prtt_ro_of_unusd_amt_flag
960 ,p_frftd_val => p_frftd_val
961 ,p_prvdd_val => p_prvdd_val
965 ,p_per_in_ler_id => p_per_in_ler_id
962 ,p_used_val => p_used_val
963 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
964 ,p_acty_base_rt_id => p_acty_base_rt_id
966 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
967 ,p_business_group_id => p_business_group_id
968 ,p_bpl_attribute_category => p_bpl_attribute_category
969 ,p_bpl_attribute1 => p_bpl_attribute1
970 ,p_bpl_attribute2 => p_bpl_attribute2
971 ,p_bpl_attribute3 => p_bpl_attribute3
972 ,p_bpl_attribute4 => p_bpl_attribute4
973 ,p_bpl_attribute5 => p_bpl_attribute5
974 ,p_bpl_attribute6 => p_bpl_attribute6
975 ,p_bpl_attribute7 => p_bpl_attribute7
976 ,p_bpl_attribute8 => p_bpl_attribute8
977 ,p_bpl_attribute9 => p_bpl_attribute9
978 ,p_bpl_attribute10 => p_bpl_attribute10
979 ,p_bpl_attribute11 => p_bpl_attribute11
980 ,p_bpl_attribute12 => p_bpl_attribute12
981 ,p_bpl_attribute13 => p_bpl_attribute13
982 ,p_bpl_attribute14 => p_bpl_attribute14
983 ,p_bpl_attribute15 => p_bpl_attribute15
984 ,p_bpl_attribute16 => p_bpl_attribute16
985 ,p_bpl_attribute17 => p_bpl_attribute17
986 ,p_bpl_attribute18 => p_bpl_attribute18
987 ,p_bpl_attribute19 => p_bpl_attribute19
988 ,p_bpl_attribute20 => p_bpl_attribute20
989 ,p_bpl_attribute21 => p_bpl_attribute21
990 ,p_bpl_attribute22 => p_bpl_attribute22
991 ,p_bpl_attribute23 => p_bpl_attribute23
992 ,p_bpl_attribute24 => p_bpl_attribute24
993 ,p_bpl_attribute25 => p_bpl_attribute25
994 ,p_bpl_attribute26 => p_bpl_attribute26
995 ,p_bpl_attribute27 => p_bpl_attribute27
996 ,p_bpl_attribute28 => p_bpl_attribute28
997 ,p_bpl_attribute29 => p_bpl_attribute29
998 ,p_bpl_attribute30 => p_bpl_attribute30
999 ,p_object_version_number => p_object_version_number
1000 ,p_cash_recd_val => p_cash_recd_val
1001 ,p_rld_up_val => p_rld_up_val
1002 ,p_effective_date => trunc(p_effective_date)
1003 ,p_datetrack_mode => p_datetrack_mode,
1004 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
1005 p_cmcd_frftd_val => l_cmcd_frftd_val,
1006 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
1007 p_cmcd_rld_up_val => l_cmcd_rld_up_val,
1008 p_cmcd_used_val => l_cmcd_used_val,
1009 p_cmcd_cash_recd_val => l_cmcd_cash_recd_val,
1010 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
1011 p_ann_frftd_val => l_ann_frftd_val,
1012 p_ann_prvdd_val => l_ann_prvdd_val,
1013 p_ann_rld_up_val => l_ann_rld_up_val,
1014 p_ann_used_val => l_ann_used_val,
1015 p_ann_cash_recd_val => l_ann_cash_recd_val
1016 );
1017 exception
1018 when hr_api.cannot_find_prog_unit then
1019 hr_api.cannot_find_prog_unit_error
1020 (p_module_name => 'UPDATE_Benefit_Prvdd_Ledger'
1021 ,p_hook_type => 'BP'
1022 );
1023 --
1024 -- End of API User Hook for the before hook of update_Benefit_Prvdd_Ledger
1025 --
1026 end;
1027 --
1028 ben_bpl_upd.upd
1029 (
1030 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1031 ,p_effective_start_date => l_effective_start_date
1032 ,p_effective_end_date => l_effective_end_date
1033 ,p_prtt_ro_of_unusd_amt_flag => p_prtt_ro_of_unusd_amt_flag
1034 ,p_frftd_val => p_frftd_val
1035 ,p_prvdd_val => p_prvdd_val
1036 ,p_used_val => p_used_val
1037 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
1038 ,p_acty_base_rt_id => p_acty_base_rt_id
1039 ,p_per_in_ler_id => p_per_in_ler_id
1040 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1041 ,p_business_group_id => p_business_group_id
1042 ,p_bpl_attribute_category => p_bpl_attribute_category
1043 ,p_bpl_attribute1 => p_bpl_attribute1
1044 ,p_bpl_attribute2 => p_bpl_attribute2
1045 ,p_bpl_attribute3 => p_bpl_attribute3
1046 ,p_bpl_attribute4 => p_bpl_attribute4
1047 ,p_bpl_attribute5 => p_bpl_attribute5
1048 ,p_bpl_attribute6 => p_bpl_attribute6
1049 ,p_bpl_attribute7 => p_bpl_attribute7
1050 ,p_bpl_attribute8 => p_bpl_attribute8
1051 ,p_bpl_attribute9 => p_bpl_attribute9
1052 ,p_bpl_attribute10 => p_bpl_attribute10
1053 ,p_bpl_attribute11 => p_bpl_attribute11
1054 ,p_bpl_attribute12 => p_bpl_attribute12
1055 ,p_bpl_attribute13 => p_bpl_attribute13
1056 ,p_bpl_attribute14 => p_bpl_attribute14
1057 ,p_bpl_attribute15 => p_bpl_attribute15
1058 ,p_bpl_attribute16 => p_bpl_attribute16
1059 ,p_bpl_attribute17 => p_bpl_attribute17
1060 ,p_bpl_attribute18 => p_bpl_attribute18
1061 ,p_bpl_attribute19 => p_bpl_attribute19
1062 ,p_bpl_attribute20 => p_bpl_attribute20
1066 ,p_bpl_attribute24 => p_bpl_attribute24
1063 ,p_bpl_attribute21 => p_bpl_attribute21
1064 ,p_bpl_attribute22 => p_bpl_attribute22
1065 ,p_bpl_attribute23 => p_bpl_attribute23
1067 ,p_bpl_attribute25 => p_bpl_attribute25
1068 ,p_bpl_attribute26 => p_bpl_attribute26
1069 ,p_bpl_attribute27 => p_bpl_attribute27
1070 ,p_bpl_attribute28 => p_bpl_attribute28
1071 ,p_bpl_attribute29 => p_bpl_attribute29
1072 ,p_bpl_attribute30 => p_bpl_attribute30
1073 ,p_object_version_number => l_object_version_number
1074 ,p_cash_recd_val => p_cash_recd_val
1075 ,p_rld_up_val => p_rld_up_val
1076 ,p_effective_date => trunc(p_effective_date)
1077 ,p_datetrack_mode => p_datetrack_mode,
1078 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
1079 p_cmcd_frftd_val => l_cmcd_frftd_val,
1080 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
1081 p_cmcd_rld_up_val => l_cmcd_rld_up_val,
1082 p_cmcd_used_val => l_cmcd_used_val,
1083 p_cmcd_cash_recd_val => l_cmcd_cash_recd_val,
1084 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
1085 p_ann_frftd_val => l_ann_frftd_val,
1086 p_ann_prvdd_val => l_ann_prvdd_val,
1087 p_ann_rld_up_val => l_ann_rld_up_val,
1088 p_ann_used_val => l_ann_used_val,
1089 p_ann_cash_recd_val => l_ann_cash_recd_val
1090 );
1091 --
1092 begin
1093 --
1094 -- Start of API User Hook for the after hook of update_Benefit_Prvdd_Ledger
1095 --
1096 ben_Benefit_Prvdd_Ledger_bk2.update_Benefit_Prvdd_Ledger_a
1097 (
1098 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1099 ,p_effective_start_date => l_effective_start_date
1100 ,p_effective_end_date => l_effective_end_date
1101 ,p_prtt_ro_of_unusd_amt_flag => p_prtt_ro_of_unusd_amt_flag
1102 ,p_frftd_val => p_frftd_val
1103 ,p_prvdd_val => p_prvdd_val
1104 ,p_used_val => p_used_val
1105 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
1106 ,p_acty_base_rt_id => p_acty_base_rt_id
1107 ,p_per_in_ler_id => p_per_in_ler_id
1108 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1109 ,p_business_group_id => p_business_group_id
1110 ,p_bpl_attribute_category => p_bpl_attribute_category
1111 ,p_bpl_attribute1 => p_bpl_attribute1
1112 ,p_bpl_attribute2 => p_bpl_attribute2
1113 ,p_bpl_attribute3 => p_bpl_attribute3
1114 ,p_bpl_attribute4 => p_bpl_attribute4
1115 ,p_bpl_attribute5 => p_bpl_attribute5
1116 ,p_bpl_attribute6 => p_bpl_attribute6
1117 ,p_bpl_attribute7 => p_bpl_attribute7
1118 ,p_bpl_attribute8 => p_bpl_attribute8
1119 ,p_bpl_attribute9 => p_bpl_attribute9
1120 ,p_bpl_attribute10 => p_bpl_attribute10
1121 ,p_bpl_attribute11 => p_bpl_attribute11
1122 ,p_bpl_attribute12 => p_bpl_attribute12
1123 ,p_bpl_attribute13 => p_bpl_attribute13
1124 ,p_bpl_attribute14 => p_bpl_attribute14
1125 ,p_bpl_attribute15 => p_bpl_attribute15
1126 ,p_bpl_attribute16 => p_bpl_attribute16
1127 ,p_bpl_attribute17 => p_bpl_attribute17
1128 ,p_bpl_attribute18 => p_bpl_attribute18
1129 ,p_bpl_attribute19 => p_bpl_attribute19
1130 ,p_bpl_attribute20 => p_bpl_attribute20
1131 ,p_bpl_attribute21 => p_bpl_attribute21
1132 ,p_bpl_attribute22 => p_bpl_attribute22
1133 ,p_bpl_attribute23 => p_bpl_attribute23
1134 ,p_bpl_attribute24 => p_bpl_attribute24
1135 ,p_bpl_attribute25 => p_bpl_attribute25
1136 ,p_bpl_attribute26 => p_bpl_attribute26
1137 ,p_bpl_attribute27 => p_bpl_attribute27
1138 ,p_bpl_attribute28 => p_bpl_attribute28
1139 ,p_bpl_attribute29 => p_bpl_attribute29
1140 ,p_bpl_attribute30 => p_bpl_attribute30
1141 ,p_object_version_number => l_object_version_number
1142 ,p_cash_recd_val => p_cash_recd_val
1143 ,p_rld_up_val => p_rld_up_val
1144 ,p_effective_date => trunc(p_effective_date)
1145 ,p_datetrack_mode => p_datetrack_mode,
1146 p_acty_ref_perd_cd => p_acty_ref_perd_cd,
1147 p_cmcd_frftd_val => l_cmcd_frftd_val,
1148 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
1149 p_cmcd_rld_up_val => l_cmcd_rld_up_val,
1150 p_cmcd_used_val => l_cmcd_used_val,
1151 p_cmcd_cash_recd_val => l_cmcd_cash_recd_val,
1152 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
1153 p_ann_frftd_val => l_ann_frftd_val,
1154 p_ann_prvdd_val => l_ann_prvdd_val,
1155 p_ann_rld_up_val => l_ann_rld_up_val,
1156 p_ann_used_val => l_ann_used_val,
1157 p_ann_cash_recd_val => l_ann_cash_recd_val
1158 );
1159 exception
1160 when hr_api.cannot_find_prog_unit then
1161 hr_api.cannot_find_prog_unit_error
1162 (p_module_name => 'UPDATE_Benefit_Prvdd_Ledger'
1163 ,p_hook_type => 'AP'
1164 );
1165 --
1166 -- End of API User Hook for the after hook of update_Benefit_Prvdd_Ledger
1167 --
1168 end;
1169 --
1170 end if; -- Bug: 3611160/4169180.
1171 --
1172 hr_utility.set_location(l_proc, 60);
1173 --
1174 -- When in validation only mode raise the Validate_Enabled exception
1175 --
1176 if p_validate then
1177 raise hr_api.validate_enabled;
1178 end if;
1179 --
1180 -- Set all output arguments
1181 --
1182 p_object_version_number := l_object_version_number;
1183 p_effective_start_date := l_effective_start_date;
1184 p_effective_end_date := l_effective_end_date;
1185 --
1186 hr_utility.set_location(' Leaving:'||l_proc, 70);
1187 --
1188 exception
1189 --
1190 when hr_api.validate_enabled then
1191 --
1192 -- As the Validate_Enabled exception has been raised
1193 -- we must rollback to the savepoint
1194 --
1195 ROLLBACK TO update_Benefit_Prvdd_Ledger;
1196 --
1197 -- Only set output warning arguments
1198 -- (Any key or derived arguments must be set to null
1199 -- when validation only mode is being used.)
1200 --
1201 hr_utility.set_location(' Leaving:'||l_proc, 80);
1202 --
1203 when others then
1204 --
1205 -- A validation or unexpected error has occured
1206 --
1207 ROLLBACK TO update_Benefit_Prvdd_Ledger;
1208 p_effective_start_date := null;
1209 p_effective_end_date := null;
1210 hr_utility.set_location(' Leaving:'||l_proc, 90);
1211 raise;
1212 --
1213 end update_Benefit_Prvdd_Ledger;
1214 --
1215 -- ----------------------------------------------------------------------------
1216 -- |------------------------< update_Benefit_Prvdd_Ledger_w > ----------------|
1217 -- ----------------------------------------------------------------------------
1218 --
1219 procedure update_Benefit_Prvdd_Ledger_w
1220 (p_validate in varchar2 default 'FALSE'
1221 ,p_bnft_prvdd_ldgr_id in number
1222 ,p_effective_start_date out nocopy date
1223 ,p_effective_end_date out nocopy date
1224 ,p_prtt_ro_of_unusd_amt_flag in varchar2 default hr_api.g_varchar2
1225 ,p_frftd_val in number default hr_api.g_number
1226 ,p_prvdd_val in number default hr_api.g_number
1227 ,p_used_val in number default hr_api.g_number
1228 ,p_bnft_prvdr_pool_id in number default hr_api.g_number
1229 ,p_acty_base_rt_id in number default hr_api.g_number
1230 ,p_per_in_ler_id in number default hr_api.g_number
1231 ,p_prtt_enrt_rslt_id in number default hr_api.g_number
1232 ,p_business_group_id in number default hr_api.g_number
1233 ,p_cash_recd_val in number default hr_api.g_number
1234 ,p_rld_up_val in number default hr_api.g_number
1235 ,p_effective_date in date
1236 ,p_datetrack_mode in varchar2
1237 ,p_process_enrt_flag in varchar2 default 'Y'
1238 ,p_from_reinstate_enrt_flag in varchar2 default 'N',
1239 p_acty_ref_perd_cd in varchar2 default hr_api.g_varchar2,
1240 p_cmcd_frftd_val in number default hr_api.g_number,
1241 p_cmcd_prvdd_val in number default hr_api.g_number,
1242 p_cmcd_rld_up_val in number default hr_api.g_number,
1243 p_cmcd_used_val in number default hr_api.g_number,
1244 p_cmcd_cash_recd_val in number default hr_api.g_number,
1245 p_cmcd_ref_perd_cd in varchar2 default hr_api.g_varchar2,
1246 p_ann_frftd_val in number default hr_api.g_number,
1247 p_ann_prvdd_val in number default hr_api.g_number,
1248 p_ann_rld_up_val in number default hr_api.g_number,
1249 p_ann_used_val in number default hr_api.g_number,
1250 p_ann_cash_recd_val in number default hr_api.g_number,
1251 p_object_version_number in out nocopy number
1252 ) is
1253 --
1254 -- Declare cursors and local variables
1255 --
1256 l_object_version_number ben_bnft_prvdd_ldgr_f.object_version_number%TYPE;
1257 l_effective_start_date ben_bnft_prvdd_ldgr_f.effective_start_date%TYPE;
1258 l_effective_end_date ben_bnft_prvdd_ldgr_f.effective_end_date%TYPE;
1259 l_validate BOOLEAN;
1260 --
1261 l_proc varchar2(72) := g_package||'update_Benefit_Prvdd_Ledger_w';
1262 --
1263 begin
1264 --
1265 hr_utility.set_location('Entering:'|| l_proc, 10);
1266 --
1267 l_object_version_number := p_object_version_number;
1268 --
1269 if upper(p_validate) = 'TRUE'
1270 then
1271 l_validate := TRUE;
1272 else
1273 l_validate := FALSE;
1274 end if;
1275 --
1276 hr_utility.set_location(l_proc, 20);
1277 --
1278 ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger
1279 (
1280 p_validate => l_validate
1281 ,p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1282 ,p_effective_start_date => l_effective_start_date
1283 ,p_effective_end_date => l_effective_end_date
1284 ,p_prtt_ro_of_unusd_amt_flag => p_prtt_ro_of_unusd_amt_flag
1285 ,p_frftd_val => p_frftd_val
1286 ,p_prvdd_val => p_prvdd_val
1287 ,p_used_val => p_used_val
1288 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
1289 ,p_acty_base_rt_id => p_acty_base_rt_id
1290 ,p_per_in_ler_id => p_per_in_ler_id
1291 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1292 ,p_business_group_id => p_business_group_id
1293 ,p_object_version_number => l_object_version_number
1294 ,p_cash_recd_val => p_cash_recd_val
1295 ,p_rld_up_val => p_rld_up_val
1296 ,p_effective_date => trunc(p_effective_date)
1297 ,p_datetrack_mode => p_datetrack_mode
1298 ,p_process_enrt_flag => p_process_enrt_flag
1299 ,p_from_reinstate_enrt_flag => p_from_reinstate_enrt_flag,
1300 p_acty_ref_perd_cd => p_acty_ref_perd_cd,
1301 p_cmcd_frftd_val => p_cmcd_frftd_val,
1302 p_cmcd_prvdd_val => p_cmcd_prvdd_val,
1303 p_cmcd_rld_up_val => p_cmcd_rld_up_val,
1304 p_cmcd_used_val => p_cmcd_used_val,
1305 p_cmcd_cash_recd_val => p_cmcd_cash_recd_val,
1306 p_cmcd_ref_perd_cd => p_cmcd_ref_perd_cd,
1307 p_ann_frftd_val => p_ann_frftd_val,
1308 p_ann_prvdd_val => p_ann_prvdd_val,
1309 p_ann_rld_up_val => p_ann_rld_up_val,
1310 p_ann_used_val => p_ann_used_val,
1311 p_ann_cash_recd_val => p_ann_cash_recd_val
1312 );
1313 --
1314 hr_utility.set_location(l_proc, 30);
1315 --
1316 -- Set all output arguments
1317 --
1318 p_object_version_number := l_object_version_number;
1319 p_effective_start_date := l_effective_start_date;
1320 p_effective_end_date := l_effective_end_date;
1321 --
1322 hr_utility.set_location(' Leaving:'||l_proc, 40);
1323 --
1324 exception
1325 --
1326 when others then
1327 p_effective_start_date := null;
1328 p_effective_end_date := null;
1329 hr_utility.set_location(' Leaving:'||l_proc, 50);
1330 fnd_msg_pub.add;
1331 --
1332 end update_Benefit_Prvdd_Ledger_w;
1333 --
1334 -- ----------------------------------------------------------------------------
1335 -- |------------------------< delete_Benefit_Prvdd_Ledger >----------------------|
1336 -- ----------------------------------------------------------------------------
1337 --
1338 procedure delete_Benefit_Prvdd_Ledger
1339 (p_validate in boolean default false
1340 ,p_bnft_prvdd_ldgr_id in number
1341 ,p_effective_start_date out nocopy date
1342 ,p_effective_end_date out nocopy date
1343 ,p_object_version_number in out nocopy number
1344 ,p_effective_date in date
1345 ,p_datetrack_mode in varchar2
1346 ,p_business_group_id in number
1347 ,p_process_enrt_flag in varchar2 default 'Y'
1348 ,p_from_reinstate_enrt_flag in varchar2 default 'N'
1349 ) is
1350 --
1351 -- Declare cursors and local variables
1352 --
1353 l_proc varchar2(72) := g_package||'delete_Benefit_Prvdd_Ledger';
1354 l_object_version_number ben_bnft_prvdd_ldgr_f.object_version_number%TYPE;
1355 l_effective_start_date ben_bnft_prvdd_ldgr_f.effective_start_date%TYPE;
1356 l_effective_end_date ben_bnft_prvdd_ldgr_f.effective_end_date%TYPE;
1357 l_bnft_prvdr_pool_id number;
1358 l_person_id number;
1359 l_acty_base_rt_id number;
1360 l_per_in_ler_id number;
1361 l_enrt_mthd_cd varchar2(30);
1362 l_prtt_ro_of_unusd_amt_flag varchar2(30);
1363 l_old_used_val number;
1364 --
1365 -- cursor to get the ledger info
1366 --
1367 cursor c_ledger_info is
1368 select bpl.bnft_prvdr_pool_id,
1369 pen.person_id,
1370 bpl.acty_base_rt_id,
1371 bpl.per_in_ler_id,
1372 bpl.used_val,
1373 pen.enrt_mthd_cd,
1374 bpl.prtt_ro_of_unusd_amt_flag
1375 from ben_bnft_prvdd_ldgr_f bpl,
1376 ben_prtt_enrt_rslt_f pen,
1377 -- UK Changes.
1378 ben_per_in_ler pil
1379 where bpl.bnft_prvdd_ldgr_id=p_bnft_prvdd_ldgr_id and
1380 bpl.object_version_number=p_object_version_number and
1381 pen.prtt_enrt_rslt_id=bpl.prtt_enrt_rslt_id and
1382 pen.business_group_id=bpl.business_group_id and
1383 pen.prtt_enrt_rslt_stat_cd is null and
1384 -- UK Changes.
1385 pil.per_in_ler_id=bpl.per_in_ler_id and
1386 pil.business_group_id=bpl.business_group_id and
1387 pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') and
1388 p_effective_date between pen.effective_start_date and pen.effective_end_date
1389 ;
1390
1391 -- 5612091 - This is used to fetch current pil, if any.
1392 -- Unable to use a parameter as this procedure is called from lot of places. hence queried again.
1393 --
1394 cursor c_cur_pil IS
1395 SELECT pil.per_in_ler_id
1396 FROM ben_per_in_ler pil, ben_ler_f ler
1397 WHERE pil.person_id = l_person_id
1398 and pil.ler_id = ler.ler_id
1399 AND pil.per_in_ler_stat_cd = 'STRTD'
1400 and p_effective_date between ler.effective_start_date
1401 and ler.effective_end_date
1402 and ler.typ_cd not in ('GSP','COMP','ABS','IREC', 'CHECKLIST');
1403 --
1404 l_curr_pil_id NUMBER;
1405 begin
1406 --
1407 hr_utility.set_location('Entering:'|| l_proc, 10);
1408 --
1409 -- Issue a savepoint if operating in validation only mode
1410 --
1411 savepoint delete_Benefit_Prvdd_Ledger;
1412 --
1413 hr_utility.set_location(l_proc, 20);
1414 --
1415 -- Process Logic
1416 --
1417 l_object_version_number := p_object_version_number;
1418 --
1419 --
1420 begin
1421 --
1422 -- Start of API User Hook for the before hook of delete_Benefit_Prvdd_Ledger
1423 --
1424 ben_Benefit_Prvdd_Ledger_bk3.delete_Benefit_Prvdd_Ledger_b
1425 (
1426 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1427 ,p_object_version_number => p_object_version_number
1428 ,p_effective_date => trunc(p_effective_date)
1429 ,p_datetrack_mode => p_datetrack_mode
1430 );
1431 exception
1432 when hr_api.cannot_find_prog_unit then
1433 hr_api.cannot_find_prog_unit_error
1434 (p_module_name => 'DELETE_Benefit_Prvdd_Ledger'
1435 ,p_hook_type => 'BP'
1436 );
1437 --
1438 -- End of API User Hook for the before hook of delete_Benefit_Prvdd_Ledger
1439 --
1440 end;
1441 --
1442 -- Get the info to do the result adjustment
1443 -- have to get it before the delete takes place
1444 --
1445 if p_process_enrt_flag = 'Y' then
1446 --
1447 open c_ledger_info;
1448 fetch c_ledger_info into
1449 l_bnft_prvdr_pool_id,
1450 l_person_id,
1451 l_acty_base_rt_id,
1452 l_per_in_ler_id,
1453 l_old_used_val,
1454 l_enrt_mthd_cd,
1455 l_prtt_ro_of_unusd_amt_flag
1456 ;
1457 if c_ledger_info%notfound then
1458 --
1459 -- The primary key is invalid therefore we must error
1460 --
1461 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
1462 fnd_message.raise_error;
1463 end if;
1464 close c_ledger_info;
1465 --
1466 end if;
1467 --
1468 -- do the real delete
1469 --
1470 hr_utility.set_location('Delete Ledger Row ' || p_bnft_prvdd_ldgr_id, 10);
1471 hr_utility.set_location('Ledger created in PIL_ID ' || l_per_in_ler_id, 10);
1472 --
1473 ben_bpl_del.del
1474 (
1475 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1476 ,p_effective_start_date => l_effective_start_date
1477 ,p_effective_end_date => l_effective_end_date
1478 ,p_object_version_number => l_object_version_number
1479 ,p_effective_date => p_effective_date
1480 ,p_datetrack_mode => p_datetrack_mode
1481 );
1482 --
1483 --
1484 -- Create/update the enrollment to include the rollover amount.
1485 --
1486 -- 5612091 Use latest pil, if avlb
1487 hr_utility.set_location('Previous l_per_in_ler_id' || l_per_in_ler_id, 10);
1488
1489 l_curr_pil_id := null;
1490 open c_cur_pil;
1491 fetch c_cur_pil into l_curr_pil_id;
1492 close c_cur_pil;
1493 l_per_in_ler_id := NVL(l_curr_pil_id, l_per_in_ler_id);
1494 --
1495
1496 hr_utility.set_location('Create Rollover PIL_ID' || l_per_in_ler_id, 10);
1497
1498 /*
1499 Bug 5645366 : Commeneted the following ben_provider_pools.create_rollover_enrollment.
1500
1501 This call seems to be un-necessary. This call is made also through
1502 ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger
1503 which would take care of creating the rollover enrollment.
1504
1505 */
1506
1507 /*
1508 if l_prtt_ro_of_unusd_amt_flag='Y' and p_process_enrt_flag = 'Y'
1509 and p_from_reinstate_enrt_flag = 'N'
1510 and nvl(ben_newly_ineligible.g_denroling_from_pgm, 'N') <> 'Y' --Bug 5642702
1511 then
1512 ben_provider_pools.create_rollover_enrollment(
1513 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
1514 p_person_id => l_person_id,
1515 p_per_in_ler_id => l_per_in_ler_id,
1516 p_effective_date => p_effective_date,
1517 p_datetrack_mode => p_datetrack_mode,
1518 p_acty_base_rt_id => l_acty_base_rt_id,
1519 p_rlovr_amt => 0,
1520 p_old_rlovr_amt => l_old_used_val,
1521 p_business_group_id => p_business_group_id,
1522 p_enrt_mthd_cd => l_enrt_mthd_cd
1523 );
1524 end if;
1525 */
1526
1527 --
1528 begin
1529 --
1530 -- Start of API User Hook for the after hook of delete_Benefit_Prvdd_Ledger
1531 --
1532 ben_Benefit_Prvdd_Ledger_bk3.delete_Benefit_Prvdd_Ledger_a
1533 (
1534 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1535 ,p_effective_start_date => l_effective_start_date
1536 ,p_effective_end_date => l_effective_end_date
1537 ,p_object_version_number => l_object_version_number
1538 ,p_effective_date => trunc(p_effective_date)
1539 ,p_datetrack_mode => p_datetrack_mode
1540 );
1541 exception
1542 when hr_api.cannot_find_prog_unit then
1543 hr_api.cannot_find_prog_unit_error
1544 (p_module_name => 'DELETE_Benefit_Prvdd_Ledger'
1545 ,p_hook_type => 'AP'
1546 );
1547 --
1548 -- End of API User Hook for the after hook of delete_Benefit_Prvdd_Ledger
1549 --
1550 end;
1551 --
1552 hr_utility.set_location(l_proc, 60);
1553 --
1554 -- When in validation only mode raise the Validate_Enabled exception
1555 --
1556 if p_validate then
1557 raise hr_api.validate_enabled;
1558 end if;
1559 --
1560 hr_utility.set_location(' Leaving:'||l_proc, 70);
1561 --
1562 exception
1563 --
1564 when hr_api.validate_enabled then
1565 --
1566 -- As the Validate_Enabled exception has been raised
1567 -- we must rollback to the savepoint
1568 --
1569 ROLLBACK TO delete_Benefit_Prvdd_Ledger;
1570 --
1571 -- Only set output warning arguments
1572 -- (Any key or derived arguments must be set to null
1573 -- when validation only mode is being used.)
1574 --
1575 p_effective_start_date := null;
1576 p_effective_end_date := null;
1577 --
1578 when others then
1579 --
1580 -- A validation or unexpected error has occured
1581 --
1582 ROLLBACK TO delete_Benefit_Prvdd_Ledger;
1583 p_effective_start_date := null;
1584 p_effective_end_date := null;
1585 hr_utility.set_location(' Leaving:'||l_proc, 80);
1586 raise;
1587 --
1588 end delete_Benefit_Prvdd_Ledger;
1589 --
1590 -- ----------------------------------------------------------------------------
1591 -- |------------------------< delete_Benefit_Prvdd_Ledger_w >-----------------|
1592 -- ----------------------------------------------------------------------------
1593 --
1594 procedure delete_Benefit_Prvdd_Ledger_w
1595 (p_validate in varchar2 default 'FALSE'
1596 ,p_bnft_prvdd_ldgr_id in number
1597 ,p_effective_start_date out nocopy date
1598 ,p_effective_end_date out nocopy date
1599 ,p_object_version_number in out nocopy number
1600 ,p_effective_date in date
1601 ,p_datetrack_mode in varchar2
1602 ,p_business_group_id in number
1603 ,p_process_enrt_flag in varchar2 default 'Y'
1604 ,p_from_reinstate_enrt_flag in varchar2 default 'N'
1605 ) is
1606 --
1607 -- Declare cursors and local variables
1608 --
1609 l_object_version_number ben_bnft_prvdd_ldgr_f.object_version_number%TYPE;
1610 l_effective_start_date ben_bnft_prvdd_ldgr_f.effective_start_date%TYPE;
1611 l_effective_end_date ben_bnft_prvdd_ldgr_f.effective_end_date%TYPE;
1612 l_validate BOOLEAN;
1613 --
1614 l_proc varchar2(72) := g_package||'delete_Benefit_Prvdd_Ledger';
1615 --
1616 begin
1617 --
1618 hr_utility.set_location('Entering:'|| l_proc, 10);
1619 --
1620 l_object_version_number := p_object_version_number;
1621 --
1622 if upper(p_validate) = 'TRUE'
1623 then
1624 l_validate := TRUE;
1625 else
1626 l_validate := FALSE;
1627 end if;
1628 --
1629 hr_utility.set_location(l_proc, 20);
1630 --
1631 ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger
1632 (
1633 p_validate => l_validate
1634 ,p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1635 ,p_effective_start_date => l_effective_start_date
1636 ,p_effective_end_date => l_effective_end_date
1637 ,p_object_version_number => l_object_version_number
1638 ,p_effective_date => p_effective_date
1639 ,p_datetrack_mode => p_datetrack_mode
1640 ,p_business_group_id => p_business_group_id
1641 ,p_process_enrt_flag => p_process_enrt_flag
1642 ,p_from_reinstate_enrt_flag => p_from_reinstate_enrt_flag
1643 );
1644 --
1645 hr_utility.set_location(l_proc, 30);
1646 --
1647 -- Set all output arguments
1648 --
1649 p_object_version_number := l_object_version_number;
1650 p_effective_start_date := l_effective_start_date;
1651 p_effective_end_date := l_effective_end_date;
1652 --
1653 hr_utility.set_location(' Leaving:'||l_proc, 40);
1654 --
1655 exception
1656 --
1657 when others then
1658 p_effective_start_date := null;
1659 p_effective_end_date := null;
1660 hr_utility.set_location(' Leaving:'||l_proc, 50);
1661 fnd_msg_pub.add;
1662 --
1663 end delete_Benefit_Prvdd_Ledger_w;
1664 --
1665 -- ----------------------------------------------------------------------------
1666 -- |-------------------------------< lck >------------------------------------|
1667 -- ----------------------------------------------------------------------------
1668 --
1669 procedure lck
1670 (
1671 p_bnft_prvdd_ldgr_id in number
1672 ,p_object_version_number in number
1673 ,p_effective_date in date
1674 ,p_datetrack_mode in varchar2
1675 ,p_validation_start_date out nocopy date
1676 ,p_validation_end_date out nocopy date
1677 ) is
1678 --
1679 --
1680 -- Declare cursors and local variables
1681 --
1682 l_proc varchar2(72) := g_package||'lck';
1683 l_validation_start_date date;
1684 l_validation_end_date date;
1685 --
1686 begin
1687 --
1688 hr_utility.set_location('Entering:'|| l_proc, 10);
1689 --
1690 ben_bpl_shd.lck
1691 (
1692 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1693 ,p_validation_start_date => l_validation_start_date
1694 ,p_validation_end_date => l_validation_end_date
1695 ,p_object_version_number => p_object_version_number
1696 ,p_effective_date => p_effective_date
1697 ,p_datetrack_mode => p_datetrack_mode
1698 );
1699 --
1700 hr_utility.set_location(' Leaving:'||l_proc, 70);
1701 --
1702 end lck;
1703 --
1704 end ben_Benefit_Prvdd_Ledger_api;