[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.1.12010000.2 2008/08/05 14:08:59 ubhat ship $ */
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
52 ,p_bpl_attribute24 in varchar2 default null
53 ,p_bpl_attribute25 in varchar2 default null
54 ,p_bpl_attribute26 in varchar2 default null
55 ,p_bpl_attribute27 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 --
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
180 (p_cash_recd_val is not null and p_cmcd_cash_recd_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,
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,
283 p_ann_frftd_val => l_ann_frftd_val,
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'
297 -- End of API User Hook for the before hook of create_Benefit_Prvdd_Ledger
294 ,p_hook_type => 'BP'
295 );
296 --
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,
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,
395 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
396 p_cmcd_frftd_val => l_cmcd_frftd_val,
397 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
401 p_ann_frftd_val => l_ann_frftd_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,
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,
493 p_ann_frftd_val => l_ann_frftd_val,
494 p_ann_prvdd_val => l_ann_prvdd_val,
498 );
495 p_ann_rld_up_val => l_ann_rld_up_val,
496 p_ann_used_val => l_ann_used_val,
497 p_ann_cash_recd_val => l_ann_cash_recd_val
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 --
620 then
617 hr_utility.set_location(l_proc, 20);
618 --
619 if upper(p_validate) = 'TRUE'
621 l_validate := TRUE;
622 else
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
725 ,p_bpl_attribute19 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
726 ,p_bpl_attribute20 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
730 ,p_bpl_attribute24 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
830
827 savepoint update_Benefit_Prvdd_Ledger;
828
829 hr_utility.set_location(l_proc, 20);
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 --
850 -- The primary key is invalid therefore we must error
851 --
852 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
853 fnd_message.raise_error;
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 open c1;
884 fetch c1 into l_dup_object_version_number;
885 close c1;
886 --
887 if (l_dup_object_version_number IS NOT NULL
888 AND l_object_version_number <> l_dup_object_version_number) then
889 l_bnft_prvdd_ldgr_updated := TRUE;
890 l_object_version_number := l_dup_object_version_number;
891 end if;
892 -- Bug: 3611160/4169180 Changes end...
893 end if;
894
895 if (p_frftd_val is not null and p_frftd_val <> l_hr_api_g_number and
896 (p_cmcd_frftd_val is null or p_cmcd_frftd_val = l_hr_api_g_number)) or
897 (p_used_val is not null and p_used_val <> l_hr_api_g_number and
898 (p_cmcd_used_val is null or p_cmcd_used_val = l_hr_api_g_number)) or
899 (p_prvdd_val is not null and p_prvdd_val <> l_hr_api_g_number and
900 (p_cmcd_prvdd_val is null or p_cmcd_prvdd_val = l_hr_api_g_number)) or
901 (p_cash_recd_val is not null and p_cash_recd_val <> l_hr_api_g_number and
902 (p_cmcd_cash_recd_val is null or p_cmcd_cash_recd_val = l_hr_api_g_number)) or
903 (p_rld_up_val is not null and p_rld_up_val <> l_hr_api_g_number and
904 (p_cmcd_rld_up_val is null or p_cmcd_rld_up_val = l_hr_api_g_number)) then
905 -- get the communicated and annual values
906 ben_update_ledgers.get_cmcd_ann_values
907 (p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id,
908 p_acty_base_rt_id => p_acty_base_rt_id,
909 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
910 p_business_group_id => p_business_group_id,
911 p_effective_start_date => p_effective_date,
912 p_per_in_ler_id => p_per_in_ler_id,
913 p_frftd_val => p_frftd_val,
914 p_used_val => p_used_val,
915 p_prvdd_val => p_prvdd_val,
916 p_cash_recd_val => p_cash_recd_val,
917 p_rld_up_val => p_rld_up_val,
918 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
919 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
920 p_cmcd_frftd_val => l_cmcd_frftd_val,
921 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
922 p_cmcd_rld_up_val => l_cmcd_rld_up_val,
923 p_cmcd_used_val => l_cmcd_used_val,
924 p_cmcd_cash_recd_val => l_cmcd_cash_recd_val,
925 p_ann_frftd_val => l_ann_frftd_val,
926 p_ann_prvdd_val => l_ann_prvdd_val,
927 p_ann_rld_up_val => l_ann_rld_up_val,
928 p_ann_used_val => l_ann_used_val,
929 p_ann_cash_recd_val => l_ann_cash_recd_val);
930 else
931 l_acty_ref_perd_cd := p_acty_ref_perd_cd;
932 l_cmcd_ref_perd_cd := p_cmcd_ref_perd_cd;
933 l_cmcd_frftd_val := p_cmcd_frftd_val;
934 l_cmcd_prvdd_val := p_cmcd_prvdd_val ;
935 l_cmcd_rld_up_val := p_cmcd_rld_up_val;
936 l_cmcd_used_val := p_cmcd_used_val;
937 l_cmcd_cash_recd_val := p_cmcd_cash_recd_val;
938 l_ann_frftd_val := p_ann_frftd_val;
939 l_ann_prvdd_val := p_ann_prvdd_val;
940 l_ann_rld_up_val := p_ann_rld_up_val;
941 l_ann_used_val := p_ann_used_val;
942 l_ann_cash_recd_val := p_ann_cash_recd_val;
943 end if;
944 --
948 -- Start of API User Hook for the before hook of update_Benefit_Prvdd_Ledger
945 if (NOT l_bnft_prvdd_ldgr_updated) then -- Bug: 3611160/4169180 Added this condition
946 --
947 begin
949 --
950 ben_Benefit_Prvdd_Ledger_bk2.update_Benefit_Prvdd_Ledger_b
951 (
952 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
953 ,p_prtt_ro_of_unusd_amt_flag => p_prtt_ro_of_unusd_amt_flag
954 ,p_frftd_val => p_frftd_val
955 ,p_prvdd_val => p_prvdd_val
956 ,p_used_val => p_used_val
957 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
958 ,p_acty_base_rt_id => p_acty_base_rt_id
959 ,p_per_in_ler_id => p_per_in_ler_id
960 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
961 ,p_business_group_id => p_business_group_id
962 ,p_bpl_attribute_category => p_bpl_attribute_category
963 ,p_bpl_attribute1 => p_bpl_attribute1
964 ,p_bpl_attribute2 => p_bpl_attribute2
965 ,p_bpl_attribute3 => p_bpl_attribute3
966 ,p_bpl_attribute4 => p_bpl_attribute4
967 ,p_bpl_attribute5 => p_bpl_attribute5
968 ,p_bpl_attribute6 => p_bpl_attribute6
969 ,p_bpl_attribute7 => p_bpl_attribute7
970 ,p_bpl_attribute8 => p_bpl_attribute8
971 ,p_bpl_attribute9 => p_bpl_attribute9
972 ,p_bpl_attribute10 => p_bpl_attribute10
973 ,p_bpl_attribute11 => p_bpl_attribute11
974 ,p_bpl_attribute12 => p_bpl_attribute12
975 ,p_bpl_attribute13 => p_bpl_attribute13
976 ,p_bpl_attribute14 => p_bpl_attribute14
977 ,p_bpl_attribute15 => p_bpl_attribute15
978 ,p_bpl_attribute16 => p_bpl_attribute16
979 ,p_bpl_attribute17 => p_bpl_attribute17
980 ,p_bpl_attribute18 => p_bpl_attribute18
981 ,p_bpl_attribute19 => p_bpl_attribute19
982 ,p_bpl_attribute20 => p_bpl_attribute20
983 ,p_bpl_attribute21 => p_bpl_attribute21
984 ,p_bpl_attribute22 => p_bpl_attribute22
985 ,p_bpl_attribute23 => p_bpl_attribute23
986 ,p_bpl_attribute24 => p_bpl_attribute24
987 ,p_bpl_attribute25 => p_bpl_attribute25
988 ,p_bpl_attribute26 => p_bpl_attribute26
989 ,p_bpl_attribute27 => p_bpl_attribute27
990 ,p_bpl_attribute28 => p_bpl_attribute28
991 ,p_bpl_attribute29 => p_bpl_attribute29
992 ,p_bpl_attribute30 => p_bpl_attribute30
993 ,p_object_version_number => p_object_version_number
994 ,p_cash_recd_val => p_cash_recd_val
995 ,p_rld_up_val => p_rld_up_val
996 ,p_effective_date => trunc(p_effective_date)
997 ,p_datetrack_mode => p_datetrack_mode,
998 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
999 p_cmcd_frftd_val => l_cmcd_frftd_val,
1000 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
1001 p_cmcd_rld_up_val => l_cmcd_rld_up_val,
1002 p_cmcd_used_val => l_cmcd_used_val,
1003 p_cmcd_cash_recd_val => l_cmcd_cash_recd_val,
1004 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
1005 p_ann_frftd_val => l_ann_frftd_val,
1006 p_ann_prvdd_val => l_ann_prvdd_val,
1007 p_ann_rld_up_val => l_ann_rld_up_val,
1008 p_ann_used_val => l_ann_used_val,
1009 p_ann_cash_recd_val => l_ann_cash_recd_val
1010 );
1011 exception
1012 when hr_api.cannot_find_prog_unit then
1013 hr_api.cannot_find_prog_unit_error
1014 (p_module_name => 'UPDATE_Benefit_Prvdd_Ledger'
1015 ,p_hook_type => 'BP'
1016 );
1017 --
1018 -- End of API User Hook for the before hook of update_Benefit_Prvdd_Ledger
1019 --
1020 end;
1021 --
1022 ben_bpl_upd.upd
1023 (
1024 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1025 ,p_effective_start_date => l_effective_start_date
1026 ,p_effective_end_date => l_effective_end_date
1027 ,p_prtt_ro_of_unusd_amt_flag => p_prtt_ro_of_unusd_amt_flag
1028 ,p_frftd_val => p_frftd_val
1029 ,p_prvdd_val => p_prvdd_val
1030 ,p_used_val => p_used_val
1031 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
1032 ,p_acty_base_rt_id => p_acty_base_rt_id
1033 ,p_per_in_ler_id => p_per_in_ler_id
1034 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1035 ,p_business_group_id => p_business_group_id
1036 ,p_bpl_attribute_category => p_bpl_attribute_category
1037 ,p_bpl_attribute1 => p_bpl_attribute1
1038 ,p_bpl_attribute2 => p_bpl_attribute2
1039 ,p_bpl_attribute3 => p_bpl_attribute3
1040 ,p_bpl_attribute4 => p_bpl_attribute4
1041 ,p_bpl_attribute5 => p_bpl_attribute5
1042 ,p_bpl_attribute6 => p_bpl_attribute6
1043 ,p_bpl_attribute7 => p_bpl_attribute7
1047 ,p_bpl_attribute11 => p_bpl_attribute11
1044 ,p_bpl_attribute8 => p_bpl_attribute8
1045 ,p_bpl_attribute9 => p_bpl_attribute9
1046 ,p_bpl_attribute10 => p_bpl_attribute10
1048 ,p_bpl_attribute12 => p_bpl_attribute12
1049 ,p_bpl_attribute13 => p_bpl_attribute13
1050 ,p_bpl_attribute14 => p_bpl_attribute14
1051 ,p_bpl_attribute15 => p_bpl_attribute15
1052 ,p_bpl_attribute16 => p_bpl_attribute16
1053 ,p_bpl_attribute17 => p_bpl_attribute17
1054 ,p_bpl_attribute18 => p_bpl_attribute18
1055 ,p_bpl_attribute19 => p_bpl_attribute19
1056 ,p_bpl_attribute20 => p_bpl_attribute20
1057 ,p_bpl_attribute21 => p_bpl_attribute21
1058 ,p_bpl_attribute22 => p_bpl_attribute22
1059 ,p_bpl_attribute23 => p_bpl_attribute23
1060 ,p_bpl_attribute24 => p_bpl_attribute24
1061 ,p_bpl_attribute25 => p_bpl_attribute25
1062 ,p_bpl_attribute26 => p_bpl_attribute26
1063 ,p_bpl_attribute27 => p_bpl_attribute27
1064 ,p_bpl_attribute28 => p_bpl_attribute28
1065 ,p_bpl_attribute29 => p_bpl_attribute29
1066 ,p_bpl_attribute30 => p_bpl_attribute30
1067 ,p_object_version_number => l_object_version_number
1068 ,p_cash_recd_val => p_cash_recd_val
1069 ,p_rld_up_val => p_rld_up_val
1070 ,p_effective_date => trunc(p_effective_date)
1071 ,p_datetrack_mode => p_datetrack_mode,
1072 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
1073 p_cmcd_frftd_val => l_cmcd_frftd_val,
1074 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
1075 p_cmcd_rld_up_val => l_cmcd_rld_up_val,
1076 p_cmcd_used_val => l_cmcd_used_val,
1077 p_cmcd_cash_recd_val => l_cmcd_cash_recd_val,
1078 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
1079 p_ann_frftd_val => l_ann_frftd_val,
1080 p_ann_prvdd_val => l_ann_prvdd_val,
1081 p_ann_rld_up_val => l_ann_rld_up_val,
1082 p_ann_used_val => l_ann_used_val,
1083 p_ann_cash_recd_val => l_ann_cash_recd_val
1084 );
1085 --
1086 begin
1087 --
1088 -- Start of API User Hook for the after hook of update_Benefit_Prvdd_Ledger
1089 --
1090 ben_Benefit_Prvdd_Ledger_bk2.update_Benefit_Prvdd_Ledger_a
1091 (
1092 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1093 ,p_effective_start_date => l_effective_start_date
1094 ,p_effective_end_date => l_effective_end_date
1095 ,p_prtt_ro_of_unusd_amt_flag => p_prtt_ro_of_unusd_amt_flag
1096 ,p_frftd_val => p_frftd_val
1097 ,p_prvdd_val => p_prvdd_val
1098 ,p_used_val => p_used_val
1099 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
1100 ,p_acty_base_rt_id => p_acty_base_rt_id
1101 ,p_per_in_ler_id => p_per_in_ler_id
1102 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1103 ,p_business_group_id => p_business_group_id
1104 ,p_bpl_attribute_category => p_bpl_attribute_category
1105 ,p_bpl_attribute1 => p_bpl_attribute1
1106 ,p_bpl_attribute2 => p_bpl_attribute2
1107 ,p_bpl_attribute3 => p_bpl_attribute3
1108 ,p_bpl_attribute4 => p_bpl_attribute4
1109 ,p_bpl_attribute5 => p_bpl_attribute5
1110 ,p_bpl_attribute6 => p_bpl_attribute6
1111 ,p_bpl_attribute7 => p_bpl_attribute7
1112 ,p_bpl_attribute8 => p_bpl_attribute8
1113 ,p_bpl_attribute9 => p_bpl_attribute9
1114 ,p_bpl_attribute10 => p_bpl_attribute10
1115 ,p_bpl_attribute11 => p_bpl_attribute11
1116 ,p_bpl_attribute12 => p_bpl_attribute12
1117 ,p_bpl_attribute13 => p_bpl_attribute13
1118 ,p_bpl_attribute14 => p_bpl_attribute14
1119 ,p_bpl_attribute15 => p_bpl_attribute15
1120 ,p_bpl_attribute16 => p_bpl_attribute16
1121 ,p_bpl_attribute17 => p_bpl_attribute17
1122 ,p_bpl_attribute18 => p_bpl_attribute18
1123 ,p_bpl_attribute19 => p_bpl_attribute19
1124 ,p_bpl_attribute20 => p_bpl_attribute20
1125 ,p_bpl_attribute21 => p_bpl_attribute21
1126 ,p_bpl_attribute22 => p_bpl_attribute22
1127 ,p_bpl_attribute23 => p_bpl_attribute23
1128 ,p_bpl_attribute24 => p_bpl_attribute24
1129 ,p_bpl_attribute25 => p_bpl_attribute25
1130 ,p_bpl_attribute26 => p_bpl_attribute26
1131 ,p_bpl_attribute27 => p_bpl_attribute27
1132 ,p_bpl_attribute28 => p_bpl_attribute28
1133 ,p_bpl_attribute29 => p_bpl_attribute29
1134 ,p_bpl_attribute30 => p_bpl_attribute30
1135 ,p_object_version_number => l_object_version_number
1136 ,p_cash_recd_val => p_cash_recd_val
1137 ,p_rld_up_val => p_rld_up_val
1141 p_cmcd_frftd_val => l_cmcd_frftd_val,
1138 ,p_effective_date => trunc(p_effective_date)
1139 ,p_datetrack_mode => p_datetrack_mode,
1140 p_acty_ref_perd_cd => p_acty_ref_perd_cd,
1142 p_cmcd_prvdd_val => l_cmcd_prvdd_val,
1143 p_cmcd_rld_up_val => l_cmcd_rld_up_val,
1144 p_cmcd_used_val => l_cmcd_used_val,
1145 p_cmcd_cash_recd_val => l_cmcd_cash_recd_val,
1146 p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd,
1147 p_ann_frftd_val => l_ann_frftd_val,
1148 p_ann_prvdd_val => l_ann_prvdd_val,
1149 p_ann_rld_up_val => l_ann_rld_up_val,
1150 p_ann_used_val => l_ann_used_val,
1151 p_ann_cash_recd_val => l_ann_cash_recd_val
1152 );
1153 exception
1154 when hr_api.cannot_find_prog_unit then
1155 hr_api.cannot_find_prog_unit_error
1156 (p_module_name => 'UPDATE_Benefit_Prvdd_Ledger'
1157 ,p_hook_type => 'AP'
1158 );
1159 --
1160 -- End of API User Hook for the after hook of update_Benefit_Prvdd_Ledger
1161 --
1162 end;
1163 --
1164 end if; -- Bug: 3611160/4169180.
1165 --
1166 hr_utility.set_location(l_proc, 60);
1167 --
1168 -- When in validation only mode raise the Validate_Enabled exception
1169 --
1170 if p_validate then
1171 raise hr_api.validate_enabled;
1172 end if;
1173 --
1174 -- Set all output arguments
1175 --
1176 p_object_version_number := l_object_version_number;
1177 p_effective_start_date := l_effective_start_date;
1178 p_effective_end_date := l_effective_end_date;
1179 --
1180 hr_utility.set_location(' Leaving:'||l_proc, 70);
1181 --
1182 exception
1183 --
1184 when hr_api.validate_enabled then
1185 --
1186 -- As the Validate_Enabled exception has been raised
1187 -- we must rollback to the savepoint
1188 --
1189 ROLLBACK TO update_Benefit_Prvdd_Ledger;
1190 --
1191 -- Only set output warning arguments
1192 -- (Any key or derived arguments must be set to null
1193 -- when validation only mode is being used.)
1194 --
1195 hr_utility.set_location(' Leaving:'||l_proc, 80);
1196 --
1197 when others then
1198 --
1199 -- A validation or unexpected error has occured
1200 --
1201 ROLLBACK TO update_Benefit_Prvdd_Ledger;
1202 p_effective_start_date := null;
1203 p_effective_end_date := null;
1204 hr_utility.set_location(' Leaving:'||l_proc, 90);
1205 raise;
1206 --
1207 end update_Benefit_Prvdd_Ledger;
1208 --
1209 -- ----------------------------------------------------------------------------
1210 -- |------------------------< update_Benefit_Prvdd_Ledger_w > ----------------|
1211 -- ----------------------------------------------------------------------------
1212 --
1213 procedure update_Benefit_Prvdd_Ledger_w
1214 (p_validate in varchar2 default 'FALSE'
1215 ,p_bnft_prvdd_ldgr_id in number
1216 ,p_effective_start_date out nocopy date
1217 ,p_effective_end_date out nocopy date
1218 ,p_prtt_ro_of_unusd_amt_flag in varchar2 default hr_api.g_varchar2
1219 ,p_frftd_val in number default hr_api.g_number
1220 ,p_prvdd_val in number default hr_api.g_number
1221 ,p_used_val in number default hr_api.g_number
1222 ,p_bnft_prvdr_pool_id in number default hr_api.g_number
1223 ,p_acty_base_rt_id in number default hr_api.g_number
1224 ,p_per_in_ler_id in number default hr_api.g_number
1225 ,p_prtt_enrt_rslt_id in number default hr_api.g_number
1226 ,p_business_group_id in number default hr_api.g_number
1227 ,p_cash_recd_val in number default hr_api.g_number
1228 ,p_rld_up_val in number default hr_api.g_number
1229 ,p_effective_date in date
1230 ,p_datetrack_mode in varchar2
1231 ,p_process_enrt_flag in varchar2 default 'Y'
1232 ,p_from_reinstate_enrt_flag in varchar2 default 'N',
1233 p_acty_ref_perd_cd in varchar2 default hr_api.g_varchar2,
1234 p_cmcd_frftd_val in number default hr_api.g_number,
1235 p_cmcd_prvdd_val in number default hr_api.g_number,
1236 p_cmcd_rld_up_val in number default hr_api.g_number,
1237 p_cmcd_used_val in number default hr_api.g_number,
1238 p_cmcd_cash_recd_val in number default hr_api.g_number,
1239 p_cmcd_ref_perd_cd in varchar2 default hr_api.g_varchar2,
1240 p_ann_frftd_val in number default hr_api.g_number,
1241 p_ann_prvdd_val in number default hr_api.g_number,
1242 p_ann_rld_up_val in number default hr_api.g_number,
1243 p_ann_used_val in number default hr_api.g_number,
1244 p_ann_cash_recd_val in number default hr_api.g_number,
1245 p_object_version_number in out nocopy number
1246 ) is
1247 --
1248 -- Declare cursors and local variables
1249 --
1250 l_object_version_number ben_bnft_prvdd_ldgr_f.object_version_number%TYPE;
1251 l_effective_start_date ben_bnft_prvdd_ldgr_f.effective_start_date%TYPE;
1255 l_proc varchar2(72) := g_package||'update_Benefit_Prvdd_Ledger_w';
1252 l_effective_end_date ben_bnft_prvdd_ldgr_f.effective_end_date%TYPE;
1253 l_validate BOOLEAN;
1254 --
1256 --
1257 begin
1258 --
1259 hr_utility.set_location('Entering:'|| l_proc, 10);
1260 --
1261 l_object_version_number := p_object_version_number;
1262 --
1263 if upper(p_validate) = 'TRUE'
1264 then
1265 l_validate := TRUE;
1266 else
1267 l_validate := FALSE;
1268 end if;
1269 --
1270 hr_utility.set_location(l_proc, 20);
1271 --
1272 ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger
1273 (
1274 p_validate => l_validate
1275 ,p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1276 ,p_effective_start_date => l_effective_start_date
1277 ,p_effective_end_date => l_effective_end_date
1278 ,p_prtt_ro_of_unusd_amt_flag => p_prtt_ro_of_unusd_amt_flag
1279 ,p_frftd_val => p_frftd_val
1280 ,p_prvdd_val => p_prvdd_val
1281 ,p_used_val => p_used_val
1282 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
1283 ,p_acty_base_rt_id => p_acty_base_rt_id
1284 ,p_per_in_ler_id => p_per_in_ler_id
1285 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1286 ,p_business_group_id => p_business_group_id
1287 ,p_object_version_number => l_object_version_number
1288 ,p_cash_recd_val => p_cash_recd_val
1289 ,p_rld_up_val => p_rld_up_val
1290 ,p_effective_date => trunc(p_effective_date)
1291 ,p_datetrack_mode => p_datetrack_mode
1292 ,p_process_enrt_flag => p_process_enrt_flag
1293 ,p_from_reinstate_enrt_flag => p_from_reinstate_enrt_flag,
1294 p_acty_ref_perd_cd => p_acty_ref_perd_cd,
1295 p_cmcd_frftd_val => p_cmcd_frftd_val,
1296 p_cmcd_prvdd_val => p_cmcd_prvdd_val,
1297 p_cmcd_rld_up_val => p_cmcd_rld_up_val,
1298 p_cmcd_used_val => p_cmcd_used_val,
1299 p_cmcd_cash_recd_val => p_cmcd_cash_recd_val,
1300 p_cmcd_ref_perd_cd => p_cmcd_ref_perd_cd,
1301 p_ann_frftd_val => p_ann_frftd_val,
1302 p_ann_prvdd_val => p_ann_prvdd_val,
1303 p_ann_rld_up_val => p_ann_rld_up_val,
1304 p_ann_used_val => p_ann_used_val,
1305 p_ann_cash_recd_val => p_ann_cash_recd_val
1306 );
1307 --
1308 hr_utility.set_location(l_proc, 30);
1309 --
1310 -- Set all output arguments
1311 --
1312 p_object_version_number := l_object_version_number;
1313 p_effective_start_date := l_effective_start_date;
1314 p_effective_end_date := l_effective_end_date;
1315 --
1316 hr_utility.set_location(' Leaving:'||l_proc, 40);
1317 --
1318 exception
1319 --
1320 when others then
1321 p_effective_start_date := null;
1322 p_effective_end_date := null;
1323 hr_utility.set_location(' Leaving:'||l_proc, 50);
1324 fnd_msg_pub.add;
1325 --
1326 end update_Benefit_Prvdd_Ledger_w;
1327 --
1328 -- ----------------------------------------------------------------------------
1329 -- |------------------------< delete_Benefit_Prvdd_Ledger >----------------------|
1330 -- ----------------------------------------------------------------------------
1331 --
1332 procedure delete_Benefit_Prvdd_Ledger
1333 (p_validate in boolean default false
1334 ,p_bnft_prvdd_ldgr_id in number
1335 ,p_effective_start_date out nocopy date
1336 ,p_effective_end_date out nocopy date
1337 ,p_object_version_number in out nocopy number
1338 ,p_effective_date in date
1339 ,p_datetrack_mode in varchar2
1340 ,p_business_group_id in number
1341 ,p_process_enrt_flag in varchar2 default 'Y'
1342 ,p_from_reinstate_enrt_flag in varchar2 default 'N'
1343 ) is
1344 --
1345 -- Declare cursors and local variables
1346 --
1347 l_proc varchar2(72) := g_package||'delete_Benefit_Prvdd_Ledger';
1348 l_object_version_number ben_bnft_prvdd_ldgr_f.object_version_number%TYPE;
1349 l_effective_start_date ben_bnft_prvdd_ldgr_f.effective_start_date%TYPE;
1350 l_effective_end_date ben_bnft_prvdd_ldgr_f.effective_end_date%TYPE;
1351 l_bnft_prvdr_pool_id number;
1352 l_person_id number;
1353 l_acty_base_rt_id number;
1354 l_per_in_ler_id number;
1355 l_enrt_mthd_cd varchar2(30);
1356 l_prtt_ro_of_unusd_amt_flag varchar2(30);
1357 l_old_used_val number;
1358 --
1359 -- cursor to get the ledger info
1360 --
1361 cursor c_ledger_info is
1362 select bpl.bnft_prvdr_pool_id,
1363 pen.person_id,
1364 bpl.acty_base_rt_id,
1365 bpl.per_in_ler_id,
1366 bpl.used_val,
1367 pen.enrt_mthd_cd,
1368 bpl.prtt_ro_of_unusd_amt_flag
1369 from ben_bnft_prvdd_ldgr_f bpl,
1370 ben_prtt_enrt_rslt_f pen,
1371 -- UK Changes.
1372 ben_per_in_ler pil
1373 where bpl.bnft_prvdd_ldgr_id=p_bnft_prvdd_ldgr_id and
1374 bpl.object_version_number=p_object_version_number and
1375 pen.prtt_enrt_rslt_id=bpl.prtt_enrt_rslt_id and
1376 pen.business_group_id=bpl.business_group_id and
1377 pen.prtt_enrt_rslt_stat_cd is null and
1381 pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') and
1378 -- UK Changes.
1379 pil.per_in_ler_id=bpl.per_in_ler_id and
1380 pil.business_group_id=bpl.business_group_id and
1382 p_effective_date between pen.effective_start_date and pen.effective_end_date
1383 ;
1384
1385 -- 5612091 - This is used to fetch current pil, if any.
1386 -- Unable to use a parameter as this procedure is called from lot of places. hence queried again.
1387 --
1388 cursor c_cur_pil IS
1389 SELECT pil.per_in_ler_id
1390 FROM ben_per_in_ler pil, ben_ler_f ler
1391 WHERE pil.person_id = l_person_id
1392 and pil.ler_id = ler.ler_id
1393 AND pil.per_in_ler_stat_cd = 'STRTD'
1394 and p_effective_date between ler.effective_start_date
1395 and ler.effective_end_date
1396 and ler.typ_cd not in ('GSP','COMP','ABS','IREC', 'CHECKLIST');
1397 --
1398 l_curr_pil_id NUMBER;
1399 begin
1400 --
1401 hr_utility.set_location('Entering:'|| l_proc, 10);
1402 --
1403 -- Issue a savepoint if operating in validation only mode
1404 --
1405 savepoint delete_Benefit_Prvdd_Ledger;
1406 --
1407 hr_utility.set_location(l_proc, 20);
1408 --
1409 -- Process Logic
1410 --
1411 l_object_version_number := p_object_version_number;
1412 --
1413 --
1414 begin
1415 --
1416 -- Start of API User Hook for the before hook of delete_Benefit_Prvdd_Ledger
1417 --
1418 ben_Benefit_Prvdd_Ledger_bk3.delete_Benefit_Prvdd_Ledger_b
1419 (
1420 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1421 ,p_object_version_number => p_object_version_number
1422 ,p_effective_date => trunc(p_effective_date)
1423 ,p_datetrack_mode => p_datetrack_mode
1424 );
1425 exception
1426 when hr_api.cannot_find_prog_unit then
1427 hr_api.cannot_find_prog_unit_error
1428 (p_module_name => 'DELETE_Benefit_Prvdd_Ledger'
1429 ,p_hook_type => 'BP'
1430 );
1431 --
1432 -- End of API User Hook for the before hook of delete_Benefit_Prvdd_Ledger
1433 --
1434 end;
1435 --
1436 -- Get the info to do the result adjustment
1437 -- have to get it before the delete takes place
1438 --
1439 if p_process_enrt_flag = 'Y' then
1440 --
1441 open c_ledger_info;
1442 fetch c_ledger_info into
1443 l_bnft_prvdr_pool_id,
1444 l_person_id,
1445 l_acty_base_rt_id,
1446 l_per_in_ler_id,
1447 l_old_used_val,
1448 l_enrt_mthd_cd,
1449 l_prtt_ro_of_unusd_amt_flag
1450 ;
1451 if c_ledger_info%notfound then
1452 --
1453 -- The primary key is invalid therefore we must error
1454 --
1455 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
1456 fnd_message.raise_error;
1457 end if;
1458 close c_ledger_info;
1459 --
1460 end if;
1461 --
1462 -- do the real delete
1463 --
1464 hr_utility.set_location('Delete Ledger Row ' || p_bnft_prvdd_ldgr_id, 10);
1465 hr_utility.set_location('Ledger created in PIL_ID ' || l_per_in_ler_id, 10);
1466 --
1467 ben_bpl_del.del
1468 (
1469 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1470 ,p_effective_start_date => l_effective_start_date
1471 ,p_effective_end_date => l_effective_end_date
1472 ,p_object_version_number => l_object_version_number
1473 ,p_effective_date => p_effective_date
1474 ,p_datetrack_mode => p_datetrack_mode
1475 );
1476 --
1477 --
1478 -- Create/update the enrollment to include the rollover amount.
1479 --
1480 -- 5612091 Use latest pil, if avlb
1481 hr_utility.set_location('Previous l_per_in_ler_id' || l_per_in_ler_id, 10);
1482
1483 l_curr_pil_id := null;
1484 open c_cur_pil;
1485 fetch c_cur_pil into l_curr_pil_id;
1486 close c_cur_pil;
1487 l_per_in_ler_id := NVL(l_curr_pil_id, l_per_in_ler_id);
1488 --
1489
1490 hr_utility.set_location('Create Rollover PIL_ID' || l_per_in_ler_id, 10);
1491
1492 /*
1493 Bug 5645366 : Commeneted the following ben_provider_pools.create_rollover_enrollment.
1494
1495 This call seems to be un-necessary. This call is made also through
1496 ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger
1497 which would take care of creating the rollover enrollment.
1498
1499 */
1500
1501 /*
1502 if l_prtt_ro_of_unusd_amt_flag='Y' and p_process_enrt_flag = 'Y'
1503 and p_from_reinstate_enrt_flag = 'N'
1504 and nvl(ben_newly_ineligible.g_denroling_from_pgm, 'N') <> 'Y' --Bug 5642702
1505 then
1506 ben_provider_pools.create_rollover_enrollment(
1507 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
1508 p_person_id => l_person_id,
1509 p_per_in_ler_id => l_per_in_ler_id,
1510 p_effective_date => p_effective_date,
1511 p_datetrack_mode => p_datetrack_mode,
1512 p_acty_base_rt_id => l_acty_base_rt_id,
1513 p_rlovr_amt => 0,
1514 p_old_rlovr_amt => l_old_used_val,
1515 p_business_group_id => p_business_group_id,
1516 p_enrt_mthd_cd => l_enrt_mthd_cd
1517 );
1521 --
1518 end if;
1519 */
1520
1522 begin
1523 --
1524 -- Start of API User Hook for the after hook of delete_Benefit_Prvdd_Ledger
1525 --
1526 ben_Benefit_Prvdd_Ledger_bk3.delete_Benefit_Prvdd_Ledger_a
1527 (
1528 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1529 ,p_effective_start_date => l_effective_start_date
1530 ,p_effective_end_date => l_effective_end_date
1531 ,p_object_version_number => l_object_version_number
1532 ,p_effective_date => trunc(p_effective_date)
1533 ,p_datetrack_mode => p_datetrack_mode
1534 );
1535 exception
1536 when hr_api.cannot_find_prog_unit then
1537 hr_api.cannot_find_prog_unit_error
1538 (p_module_name => 'DELETE_Benefit_Prvdd_Ledger'
1539 ,p_hook_type => 'AP'
1540 );
1541 --
1542 -- End of API User Hook for the after hook of delete_Benefit_Prvdd_Ledger
1543 --
1544 end;
1545 --
1546 hr_utility.set_location(l_proc, 60);
1547 --
1548 -- When in validation only mode raise the Validate_Enabled exception
1549 --
1550 if p_validate then
1551 raise hr_api.validate_enabled;
1552 end if;
1553 --
1554 hr_utility.set_location(' Leaving:'||l_proc, 70);
1555 --
1556 exception
1557 --
1558 when hr_api.validate_enabled then
1559 --
1560 -- As the Validate_Enabled exception has been raised
1561 -- we must rollback to the savepoint
1562 --
1563 ROLLBACK TO delete_Benefit_Prvdd_Ledger;
1564 --
1565 -- Only set output warning arguments
1566 -- (Any key or derived arguments must be set to null
1567 -- when validation only mode is being used.)
1568 --
1569 p_effective_start_date := null;
1570 p_effective_end_date := null;
1571 --
1572 when others then
1573 --
1574 -- A validation or unexpected error has occured
1575 --
1576 ROLLBACK TO delete_Benefit_Prvdd_Ledger;
1577 p_effective_start_date := null;
1578 p_effective_end_date := null;
1579 hr_utility.set_location(' Leaving:'||l_proc, 80);
1580 raise;
1581 --
1582 end delete_Benefit_Prvdd_Ledger;
1583 --
1584 -- ----------------------------------------------------------------------------
1585 -- |------------------------< delete_Benefit_Prvdd_Ledger_w >-----------------|
1586 -- ----------------------------------------------------------------------------
1587 --
1588 procedure delete_Benefit_Prvdd_Ledger_w
1589 (p_validate in varchar2 default 'FALSE'
1590 ,p_bnft_prvdd_ldgr_id in number
1591 ,p_effective_start_date out nocopy date
1592 ,p_effective_end_date out nocopy date
1593 ,p_object_version_number in out nocopy number
1594 ,p_effective_date in date
1595 ,p_datetrack_mode in varchar2
1596 ,p_business_group_id in number
1597 ,p_process_enrt_flag in varchar2 default 'Y'
1598 ,p_from_reinstate_enrt_flag in varchar2 default 'N'
1599 ) is
1600 --
1601 -- Declare cursors and local variables
1602 --
1603 l_object_version_number ben_bnft_prvdd_ldgr_f.object_version_number%TYPE;
1604 l_effective_start_date ben_bnft_prvdd_ldgr_f.effective_start_date%TYPE;
1605 l_effective_end_date ben_bnft_prvdd_ldgr_f.effective_end_date%TYPE;
1606 l_validate BOOLEAN;
1607 --
1608 l_proc varchar2(72) := g_package||'delete_Benefit_Prvdd_Ledger';
1609 --
1610 begin
1611 --
1612 hr_utility.set_location('Entering:'|| l_proc, 10);
1613 --
1614 l_object_version_number := p_object_version_number;
1615 --
1616 if upper(p_validate) = 'TRUE'
1617 then
1618 l_validate := TRUE;
1619 else
1620 l_validate := FALSE;
1621 end if;
1622 --
1623 hr_utility.set_location(l_proc, 20);
1624 --
1625 ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger
1626 (
1627 p_validate => l_validate
1628 ,p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1629 ,p_effective_start_date => l_effective_start_date
1630 ,p_effective_end_date => l_effective_end_date
1631 ,p_object_version_number => l_object_version_number
1632 ,p_effective_date => p_effective_date
1633 ,p_datetrack_mode => p_datetrack_mode
1634 ,p_business_group_id => p_business_group_id
1635 ,p_process_enrt_flag => p_process_enrt_flag
1636 ,p_from_reinstate_enrt_flag => p_from_reinstate_enrt_flag
1637 );
1638 --
1639 hr_utility.set_location(l_proc, 30);
1640 --
1641 -- Set all output arguments
1642 --
1643 p_object_version_number := l_object_version_number;
1644 p_effective_start_date := l_effective_start_date;
1645 p_effective_end_date := l_effective_end_date;
1646 --
1647 hr_utility.set_location(' Leaving:'||l_proc, 40);
1648 --
1649 exception
1650 --
1651 when others then
1652 p_effective_start_date := null;
1653 p_effective_end_date := null;
1654 hr_utility.set_location(' Leaving:'||l_proc, 50);
1655 fnd_msg_pub.add;
1656 --
1657 end delete_Benefit_Prvdd_Ledger_w;
1658 --
1659 -- ----------------------------------------------------------------------------
1660 -- |-------------------------------< lck >------------------------------------|
1664 (
1661 -- ----------------------------------------------------------------------------
1662 --
1663 procedure lck
1665 p_bnft_prvdd_ldgr_id in number
1666 ,p_object_version_number in number
1667 ,p_effective_date in date
1668 ,p_datetrack_mode in varchar2
1669 ,p_validation_start_date out nocopy date
1670 ,p_validation_end_date out nocopy date
1671 ) is
1672 --
1673 --
1674 -- Declare cursors and local variables
1675 --
1676 l_proc varchar2(72) := g_package||'lck';
1677 l_validation_start_date date;
1678 l_validation_end_date date;
1679 --
1680 begin
1681 --
1682 hr_utility.set_location('Entering:'|| l_proc, 10);
1683 --
1684 ben_bpl_shd.lck
1685 (
1686 p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1687 ,p_validation_start_date => l_validation_start_date
1688 ,p_validation_end_date => l_validation_end_date
1689 ,p_object_version_number => p_object_version_number
1690 ,p_effective_date => p_effective_date
1691 ,p_datetrack_mode => p_datetrack_mode
1692 );
1693 --
1694 hr_utility.set_location(' Leaving:'||l_proc, 70);
1695 --
1696 end lck;
1697 --
1698 end ben_Benefit_Prvdd_Ledger_api;