[Home] [Help]
PACKAGE BODY: APPS.BEN_PRC_BUS
Source
1 Package Body ben_prc_bus as
2 /* $Header: beprcrhi.pkb 120.7.12010000.2 2008/08/05 15:19:06 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_prc_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_prtt_reimbmt_rqst_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- prtt_reimbmt_rqst_id PK of record being inserted or updated.
24 -- effective_date Effective Date of session
25 -- object_version_number Object version number of record being
26 -- inserted or updated.
27 --
28 -- Post Success
29 -- Processing continues
30 --
31 -- Post Failure
32 -- Errors handled by the procedure
33 --
34 -- Access Status
35 -- Internal table handler use only.
36 --
37 Procedure chk_prtt_reimbmt_rqst_id
38 (p_prtt_reimbmt_rqst_id in number,
39 p_effective_date in date,
40 p_object_version_number in number) is
41 --
42 l_proc varchar2(72) := g_package||'chk_prtt_reimbmt_rqst_id';
43 l_api_updating boolean;
44 --
45 Begin
46 --
47 hr_utility.set_location('Entering:'||l_proc, 5);
48 --
49 l_api_updating := ben_prc_shd.api_updating
50 (p_effective_date => p_effective_date,
51 p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id,
52 p_object_version_number => p_object_version_number);
53 --
54 if (l_api_updating
55 and nvl(p_prtt_reimbmt_rqst_id,hr_api.g_number)
56 <> ben_prc_shd.g_old_rec.prtt_reimbmt_rqst_id) then
57 --
58 -- raise error as PK has changed
59 --
60 ben_prc_shd.constraint_error('BEN_PRTT_REIMBMT_RQST_F_PK');
61 --
62 elsif not l_api_updating then
63 --
64 -- check if PK is null
65 --
66 if p_prtt_reimbmt_rqst_id is not null then
67 --
68 -- raise error as PK is not null
69 --
70 ben_prc_shd.constraint_error('BEN_PRTT_REIMBMT_RQST_F_PK');
71 --
72 end if;
73 --
74 end if;
75 --
76 hr_utility.set_location('Leaving:'||l_proc, 10);
77 --
78 End chk_prtt_reimbmt_rqst_id;
79 --
80
81 function get_year_balance (
82 p_person_id in number
83 ,p_pgm_id in number
84 ,p_pl_id in number
85 ,p_business_group_id in number
86 ,p_per_in_ler_id in number
87 ,p_prtt_enrt_rslt_id in number
88 ,p_effective_date in date
89 -- ,p_incrd_from_dt in date -- 2272862
90 ,p_exp_incurd_dt in date
91 ) return number is
92
93
94
95 cursor get_epe is
96 select pil.lf_evt_ocrd_dt,
97 epe.pgm_id,
98 epe.pl_id,
99 epe.oipl_id,
100 epe.per_in_ler_id,
101 epe.yr_perd_id,
102 pel.enrt_perd_id,
103 pel.lee_rsn_id,
104 pil.business_group_id
105 from ben_elig_per_elctbl_chc epe,
106 ben_pil_elctbl_chc_popl pel,
107 ben_per_in_ler pil
108 where pil.per_in_ler_id = p_per_in_ler_id
109 and pil.business_group_id = p_business_group_id
110 and epe.per_in_ler_id = pil.per_in_ler_id
111 and epe.pgm_id = p_pgm_id
112 and epe.pl_id = p_pl_id
113 and epe.per_in_ler_id = p_per_in_ler_id
114 and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id ;
115
116 --
117 l_get_epe get_epe%rowtype;
118 --
119 cursor c_yrp is
120 select yrp.start_date , yrp.end_date
121 from ben_yr_perd yrp
122 where yrp.yr_perd_id = l_get_epe.yr_perd_id
123 and yrp.business_group_id = p_business_group_id;
124 --
125 cursor c_prv is
126 select acty_base_rt_id
127 from ben_prtt_rt_val
128 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
129 and prtt_reimbmt_rqst_id is null ;
130
131 l_acty_base_rt_id ben_acty_base_rt_f.acty_base_rt_id%type ;
132 --
133 cursor c_abr_name is
134 select name
135 from ben_acty_base_rt_f
136 where acty_base_rt_id = l_acty_base_rt_id
137 and p_effective_date between effective_start_date and effective_end_date ;
138 l_abr_name ben_acty_base_rt_f.name%type ;
139 --
140 cursor abr_balance is
141 select abr.ptd_comp_lvl_fctr_id,
142 abr.clm_comp_lvl_fctr_id,
143 abr.det_pl_ytd_cntrs_cd,
144 abr.acty_base_rt_id
145 from ben_acty_base_rt_f abr
146 where acty_base_rt_id = l_acty_base_rt_id
147 and p_effective_date between
148 abr.effective_start_date and
149 abr.effective_end_date;
150
151 --
152 --
153 cursor c_abr_prv is
154 select distinct prv.acty_base_rt_id prv_rate,
155 abr.name abr_name,
156 clf.*
157 from ben_prtt_rt_val prv,
158 ben_acty_base_rt_f abr,
159 ben_comp_lvl_fctr clf
160 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
161 and prv.acty_base_rt_id = abr.acty_base_rt_id
162 and abr.acty_typ_cd not like 'PRD%'
163 and abr.acty_typ_cd <> 'PRFRFS'
164 and abr.ttl_comp_lvl_fctr_id = clf.comp_lvl_fctr_id (+)
165 and prv.prtt_rt_val_stat_cd is null
166 -- and p_incrd_from_dt between --2272862
167 -- and p_exp_incurd_dt between
168 -- prv.rt_strt_dt and prv.rt_end_dt
169 and p_effective_date between
170 abr.effective_start_date and abr.effective_end_date;
171 --
172 cursor c_asg(p_assignment_type varchar2,p_person_id number ) is
173 select paf.assignment_id
174 from per_all_assignments_f paf
175 where paf.person_id = p_person_id
176 and paf.business_group_id = p_business_group_id
177 and paf.primary_flag = 'Y'
178 and paf.assignment_type <> 'C'
179 and paf.assignment_type = p_assignment_type
180 and p_effective_date
181 between paf.effective_start_date
182 and paf.effective_end_date;
183 --
184 cursor c_yr_perd is
185 select yrp.end_date
186 from ben_yr_perd yrp,
187 ben_popl_yr_perd cyp
188 where yrp.yr_perd_id = cyp.yr_perd_id
189 -- and p_incrd_from_dt between yrp.start_date and yrp.end_date -- 2278262
190 and p_exp_incurd_dt between yrp.start_date and yrp.end_date
191 and cyp.pl_id = p_pl_id;
192 --
193 cursor c_bnft_bal(p_bnfts_bal_id number, p_person_id number) is
194 select bnb.val
195 from ben_per_bnfts_bal_f bnb
196 where bnb.bnfts_bal_id = p_bnfts_bal_id
197 and bnb.person_id = p_person_id
198 and bnb.business_group_id = p_business_group_id
199 and p_effective_date
200 between bnb.effective_start_date
201 and bnb.effective_end_date;
202 --
203 l_abr_prv c_abr_prv%rowtype;
204 l_abr_balance abr_balance%rowtype;
205 l_proc varchar2(100) := 'get_year_balance' ;
206 l_ptd_balance ben_prtt_reimbmt_rqst_f.rqst_amt%type ;
207 l_yr_start_date date;
208 l_yr_end_date date;
209 l_assignment_id number;
210 l_assignment_action_id number;
211 begin
212 hr_utility.set_location('Entering:'||l_proc,5);
213 hr_utility.set_location ('Effective date'||p_effective_date,111);
214 l_assignment_id := null;
215 open c_asg('E',p_person_id );
216 fetch c_asg into l_assignment_id;
217 close c_asg;
218 IF l_assignment_id IS NULL THEN
219 --
220 hr_utility.set_location (' employee failed ' || l_assignment_id , 30) ;
221 open c_asg('B',p_person_id);
222 fetch c_asg into l_assignment_id;
223 close c_asg;
224 --
225 -- 9999 Error out if assignment is not found for person.
226 --
227 END IF;
228 --
229 open c_yr_perd;
230 fetch c_yr_perd into l_yr_end_date;
231 close c_yr_perd;
232 --
233 open c_abr_prv;
234 fetch c_abr_prv into l_abr_prv;
235 close c_abr_prv;
236
237 hr_utility.set_location(' result id ' || p_prtt_enrt_rslt_id ,293);
238 hr_utility.set_location(' acty_base_rt ' || l_acty_base_rt_id,293);
239 hr_utility.set_location(' ytd cntr cd ' || l_abr_balance.det_pl_ytd_cntrs_cd,293);
240 hr_utility.set_location(' ptd level ' || l_abr_balance.ptd_comp_lvl_fctr_id,293);
241 hr_utility.set_location(' acty_base_rt ' || l_abr_balance.acty_base_rt_id,293);
242 hr_utility.set_location(' p_effective_date ' || p_effective_date ,293);
243 hr_utility.set_location(' yr_perd_id ' || l_get_epe.yr_perd_id ,293);
244 hr_utility.set_location(' bnfts_bal_id ' || l_abr_prv.bnfts_bal_id ,293);
245 hr_utility.set_location(' bnfts_bal_id ' || l_abr_prv.bnfts_bal_id ,293);
246 hr_utility.set_location(' person_id ' || p_person_id ,293);
247
248 if l_abr_prv.comp_src_cd is not null then
249 --
250 if l_abr_prv.comp_src_cd = 'BALTYP' THEN
251
252 ben_derive_part_and_rate_facts.set_taxunit_context
253 (p_person_id => p_person_id
254 ,p_business_group_id => p_business_group_id
255 ,p_effective_date => least(p_effective_date,l_yr_end_date)
256 ) ;
257 --
258 -- Bug 3818453. Pass assignment_action_id to get_value() to
259 -- improve performance
260 --
261 l_assignment_action_id :=
262 ben_derive_part_and_rate_facts.get_latest_paa_id
263 (p_person_id => p_person_id
264 ,p_business_group_id => p_business_group_id
265 ,p_effective_date => least(p_effective_date,l_yr_end_date));
266
267 if l_assignment_action_id is not null then
268 --
269 begin
270 l_ptd_balance :=
271 pay_balance_pkg.get_value(l_abr_prv.defined_balance_id
272 ,l_assignment_action_id);
273 exception
274 when others then
275 l_ptd_balance := null ;
276 end ;
277 --
278 --
279 end if ;
280 --
281 -- old code prior to 3818453
282 --
283 /*
284 l_ptd_balance :=
285 pay_balance_pkg.get_value(l_abr_prv.defined_balance_id
286 ,l_assignment_id
287 ,least(p_effective_date,l_yr_end_date));
288 */
289 elsif l_abr_prv.comp_src_cd = 'BNFTBALTYP' then
290 hr_utility.set_location(' bnfts_bal_id ' || l_abr_prv.bnfts_bal_id ,293);
291 hr_utility.set_location(' person_id ' || p_person_id ,293);
292 open c_bnft_bal(l_abr_prv.bnfts_bal_id, p_person_id);
293 fetch c_bnft_bal into l_ptd_balance;
294 close c_bnft_bal;
295 end if;
296 --
297 else
298 fnd_message.set_name('BEN', 'BEN_92668_CONTR_BAL_NOT_EXIST');
299 fnd_message.set_token('STD_RATE_NAME ', l_abr_prv.name);
300 fnd_message.raise_error;
301
302 end if;
303 hr_utility.set_location('Leaving:'||l_proc, 10);
304 hr_utility.set_location('ptd_balance '||l_ptd_balance , 293);
305 return nvl(l_ptd_balance,0);
306 end get_year_balance ;
307
308 --
309 -- ----------------------------------------------------------------------------
310 -- |---------------------------< plan_year_claim >---------------------------|
311 -- this finction to calcualte the reimbursement for the plan year
312 -- ----------------------------------------------------------------------------
313 Procedure plan_year_claim(
314 p_pl_id in number
315 ,p_person_id in number
316 ,p_business_group_id in number
317 ,p_prtt_reimbmt_rqst_id in number
318 ,p_effective_date in date
319 ,p_exp_incurd_dt in date
320 ,p_popl_yr_perd_id_1 in number
321 ,p_popl_yr_perd_id_2 in number
322 ,p_amt_1 out nocopy number
323 ,p_amt_2 out nocopy number
324 ) is
325 -- future created entry to be taken for calc so p_effective_Date is not validated with
326 -- start date and end date . thi may lead into a problem of entry is date tracked
327 -- curent entry is not taken for calc tath is added in calling proc this helps while updating
328 /*cursor c1 is select sum(nvl(prc.aprvd_for_pymt_amt,0))
329 from ben_prtt_reimbmt_rqst_f prc,
330 ben_pl_f pl,
331 ben_popl_yr_perd pyr,
332 ben_yr_perd yr
333 where prc.submitter_person_id = p_person_id
334 and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
335 and pl.pl_id = p_pl_id
336 and pl.pl_id = prc.pl_id
337 and p_effective_date between pl.effective_start_date and pl.effective_end_date
338 and prc.effective_end_date = hr_api.g_eot --future created entry to be taken for calc
339 and (p_prtt_reimbmt_rqst_id is null
340 or prc.prtt_reimbmt_rqst_id <> p_prtt_reimbmt_rqst_id)
341 and pl.pl_id = pyr.pl_id
342 and pyr.yr_perd_id = yr.yr_perd_id
343 -- if the reimp belong to the current year then the
344 -- both condition has to match
345 and p_exp_incurd_dt between yr.start_date and yr.end_date
346 and prc.exp_incurd_dt between yr.start_date and yr.end_date
347 -- and p_incrd_from_dt between yr.start_date and yr.end_date -- 2272862
348 -- and prc.incrd_from_dt between yr.start_date and yr.end_date
349 and prc.business_group_id = p_business_group_id
350 and pl.business_group_id = p_business_group_id
351 and pyr.business_group_id = p_business_group_id
352 and yr.business_group_id = p_business_group_id;
353 */
354 cursor c_year_claim_amt1 (p_popl_yr_perd number) is
355 select sum(nvl(prc.amt_year1,0))
356 from ben_prtt_reimbmt_rqst_f prc
357 where prc.submitter_person_id = p_person_id
358 and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
359 and prc.pl_id = p_pl_id
360 and prc.effective_end_date = hr_api.g_eot
361 and (p_prtt_reimbmt_rqst_id is null
362 or prc.prtt_reimbmt_rqst_id <> p_prtt_reimbmt_rqst_id)
363 and prc.popl_yr_perd_id_1 = p_popl_yr_perd;
364 --
365 cursor c_year_claim_amt2 (p_popl_yr_perd number) is
366 select sum(nvl(prc.amt_year2,0)) --+ sum(nvl(prc.amt_year2,0))
367 from ben_prtt_reimbmt_rqst_f prc
368 where prc.submitter_person_id = p_person_id
369 and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
370 and prc.pl_id = p_pl_id
371 and prc.effective_end_date = hr_api.g_eot
372 and (p_prtt_reimbmt_rqst_id is null
373 or prc.prtt_reimbmt_rqst_id <> p_prtt_reimbmt_rqst_id)
374 and prc.popl_yr_perd_id_2 = p_popl_yr_perd;
375
376
377 l_year_bal ben_prtt_reimbmt_rqst_f.rqst_amt%type ;
378 l_proc varchar2(100) := ' plan_year_claim' ;
379
380
381 begin
382 hr_utility.set_location('Entering:'||l_proc,5);
383 /*
384 hr_utility.set_location('pl' ||p_pl_id ,5);
385 hr_utility.set_location('reimbq '||p_prtt_reimbmt_rqst_id ,5);
386 hr_utility.set_location('person '||p_person_id ,5);
387 hr_utility.set_location('b group'||p_business_group_id ,5);
388 hr_utility.set_location('eff dt '||p_effective_date ,5);
389 open c1;
390 fetch c1 into l_year_bal ;
391 close c1 ;
392 */
393 l_year_bal := 0;
394 open c_year_claim_amt1 (p_popl_yr_perd_id_1);
395 fetch c_year_claim_amt1 into l_year_bal;
396 close c_year_claim_amt1;
397 p_amt_1 := nvl(l_year_bal,0);
398 --
399 open c_year_claim_amt2 (p_popl_yr_perd_id_1);
400 fetch c_year_claim_amt2 into l_year_bal;
401 close c_year_claim_amt2;
402 p_amt_1 := p_amt_1 + nvl(l_year_bal,0);
403
404 l_year_bal := 0;
405 --
406 if p_popl_yr_perd_id_2 is not null then
407 --
408 open c_year_claim_amt1 (p_popl_yr_perd_id_2);
409 fetch c_year_claim_amt1 into l_year_bal;
410 close c_year_claim_amt1;
411 --
412 p_amt_2 := nvl(l_year_bal,0);
413 --
414 open c_year_claim_amt2 (p_popl_yr_perd_id_2);
415 fetch c_year_claim_amt2 into l_year_bal;
416 close c_year_claim_amt2;
417 --
418 p_amt_2 := p_amt_2 + nvl(l_year_bal,0);
419 --
420 end if;
421 hr_utility.set_location('Leaving:'||l_proc, 10);
422 end plan_year_claim ;
423
424 --
425 -- ----------------------------------------------------------------------------
426 -- |------< chk_gd_or_svc_typ_id >------|
427 -- ----------------------------------------------------------------------------
428 --
429 -- Description
430 -- This procedure checks that a referenced foreign key actually exists
431 -- in the referenced table.
432 --
433 -- Pre-Conditions
434 -- None.
435 --
436 -- In Parameters
437 -- p_prtt_reimbmt_rqst_id PK
438 -- p_gd_or_svc_typ_id ID of FK column
439 -- p_effective_date session date
440 -- p_object_version_number object version number
441 --
442 -- Post Success
443 -- Processing continues
444 --
445 -- Post Failure
446 -- Error raised.
447 --
448 -- Access Status
449 -- Internal table handler use only.
450 --
451 Procedure chk_gd_or_svc_typ_id (p_prtt_reimbmt_rqst_id in number,
452 p_gd_or_svc_typ_id in number,
453 p_effective_date in date,
454 p_object_version_number in number) is
455 --
456 l_proc varchar2(72) := g_package||'chk_gd_or_svc_typ_id';
457 l_api_updating boolean;
458 l_dummy varchar2(1);
459 --
460 cursor c1 is
461 select null
462 from ben_gd_or_svc_typ a
463 where a.gd_or_svc_typ_id = p_gd_or_svc_typ_id;
464 --
465 Begin
466 --
467 hr_utility.set_location('Entering:'||l_proc,5);
468 --
469 l_api_updating := ben_prc_shd.api_updating
470 (p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id,
471 p_effective_date => p_effective_date,
472 p_object_version_number => p_object_version_number);
473 --
474 if (l_api_updating
475 and nvl(p_gd_or_svc_typ_id,hr_api.g_number)
476 <> nvl(ben_prc_shd.g_old_rec.gd_or_svc_typ_id,hr_api.g_number)
477 or not l_api_updating) and
478 p_gd_or_svc_typ_id is not null then
479 --
480 -- check if gd_or_svc_typ_id value exists in ben_gd_or_svc_typ table
481 --
482 open c1;
483 --
484 fetch c1 into l_dummy;
485 if c1%notfound then
486 --
487 close c1;
488 --
489 -- raise error as FK does not relate to PK in ben_gd_or_svc_typ
490 -- table.
491 --
492 ben_prc_shd.constraint_error('BEN_PRTT_REIMBMT_RQST_F_FK1');
493 --
494 end if;
495 --
496 close c1;
497 --
498 end if;
499 --
500 hr_utility.set_location('Leaving:'||l_proc,10);
501 --
502 End chk_gd_or_svc_typ_id;
503 --
504 -- ----------------------------------------------------------------------------
505 -- |------< chk_provider_person_id >------|
506 -- ----------------------------------------------------------------------------
507 --
508 -- Description
509 -- This procedure checks that a referenced foreign key actually exists
510 -- in the referenced table.
511 -- The Provider Person Id can be either a person in PER_ALL_PEOPLE_F
512 -- or an Org Unit ifrom HR_ALL_ORGANIZATION_UNITS. both places must
513 -- be checked.
514 --
515 -- Pre-Conditions
516 -- None.
517 --
518 -- In Parameters
519 -- p_prtt_reimbmt_rqst_id PK
520 -- p_provider_person_id ID of FK column
521 -- p_effective_date session date
522 -- p_object_version_number object version number
523 --
524 -- Post Success
525 -- Processing continues
526 --
527 -- Post Failure
528 -- Error raised.
529 --
530 -- Access Status
531 -- Internal table handler use only.
532 --
533 Procedure chk_provider_person_id
534 (p_prtt_reimbmt_rqst_id in number,
535 p_provider_person_id in number,
536 p_effective_date in date,
537 p_object_version_number in number) is
538 --
539 l_proc varchar2(72) := g_package||'chk_provider_person_id';
540 l_api_updating boolean;
541 l_dummy varchar2(1);
542 --
543 cursor c1 is
544 select null
545 from hr_all_organization_units a
546 where a.organization_id = p_provider_person_id;
547 --
548 cursor c2 is
549 select null
550 from per_all_people_f
551 where person_id = p_provider_person_id;
552 --
553 Begin
554 --
555 hr_utility.set_location('Entering:'||l_proc,5);
556 --
557 l_api_updating := ben_prc_shd.api_updating
558 (p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id,
559 p_effective_date => p_effective_date,
560 p_object_version_number => p_object_version_number);
561 --
562 if (l_api_updating
563 and nvl(p_provider_person_id,hr_api.g_number)
564 <> nvl(ben_prc_shd.g_old_rec.provider_person_id,hr_api.g_number)
565 or not l_api_updating) and
566 p_provider_person_id is not null then
567 --
568 -- check if provider_person_id value exists in hr_all_organization_units table
569 --
570 open c1;
571 fetch c1 into l_dummy;
572 if c1%notfound then
573 --
574 -- not exist in HR_ALL_ORGANIZATION_UNITS so will check PER_ALL_PEOPLE_F
575 --
576 open c2;
577 fetch c2 into l_dummy;
578 if c2%notfound then
579 --
580 close c1;
581 close c2;
582 --
583 -- raise error as FK does not relate to PK in hr_all_organization_units
584 -- table or PER_ALL_PEOPLE_F table.
585 --
586 ben_prc_shd.constraint_error('BEN_PRTT_REIMBMT_RQST_F_DT6');
587 --
588 end if;
589 --
590 close c2;
591 --
592 end if;
593 --
594 close c1;
595 --
596 end if;
597 --
598 hr_utility.set_location('Leaving:'||l_proc,10);
599 --
600 End chk_provider_person_id;
601 --
602 -- ----------------------------------------------------------------------------
603 -- |------< chk_rcrrg_cd >------|
604 -- ----------------------------------------------------------------------------
605 --
606 -- Description
607 -- This procedure is used to check that the lookup value is valid.
608 --
609 -- Pre Conditions
610 -- None.
611 --
612 -- In Parameters
613 -- prtt_reimbmt_rqst_id PK of record being inserted or updated.
614 -- rcrrg_cd Value of lookup code.
615 -- effective_date effective date
616 -- object_version_number Object version number of record being
617 -- inserted or updated.
618 --
619 -- Post Success
620 -- Processing continues
621 --
622 -- Post Failure
623 -- Error handled by procedure
624 --
625 -- Access Status
626 -- Internal table handler use only.
627 --
628 Procedure chk_rcrrg_cd(p_prtt_reimbmt_rqst_id in number,
629 p_rcrrg_cd in varchar2,
630 p_effective_date in date,
631 p_object_version_number in number) is
632 --
633 l_proc varchar2(72) := g_package||'chk_rcrrg_cd';
634 l_api_updating boolean;
635 --
636 Begin
637 --
638 hr_utility.set_location('Entering:'||l_proc, 5);
639 --
640 l_api_updating := ben_prc_shd.api_updating
641 (p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id,
642 p_effective_date => p_effective_date,
643 p_object_version_number => p_object_version_number);
644 --
645 if (l_api_updating
646 and p_rcrrg_cd
647 <> nvl(ben_prc_shd.g_old_rec.rcrrg_cd,hr_api.g_varchar2)
648 or not l_api_updating)
649 and p_rcrrg_cd is not null then
650 --
651 -- check if value of lookup falls within lookup type.
652 --
653 if hr_api.not_exists_in_hr_lookups
654 (p_lookup_type => 'BEN_REIMBMT_RQST_RCRG',
655 p_lookup_code => p_rcrrg_cd,
656 p_effective_date => p_effective_date) then
657 --
658 -- raise error as does not exist as lookup
659 --
660 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
661 fnd_message.set_token('FIELD','p_rcrrg_cd');
662 fnd_message.set_token('VALUE', p_rcrrg_cd);
663 fnd_message.set_token('TYPE','BEN_REIMBMT_RQST_RCRG');
664 fnd_message.raise_error;
665 --
666 end if;
667 --
668 end if;
669 --
670 hr_utility.set_location('Leaving:'||l_proc,10);
671 --
672 end chk_rcrrg_cd;
673 --
674 -- ----------------------------------------------------------------------------
675 -- |------< chk_reimbmt_ctfn_typ_prvdd_cd >------|
676 -- ----------------------------------------------------------------------------
677 --
678 -- Description
679 -- This procedure is used to check that the lookup value is valid.
680 --
681 -- Pre Conditions
682 -- None.
683 --
684 -- In Parameters
685 -- prtt_reimbmt_rqst_id PK of record being inserted or updated.
686 -- reimbmt_ctfn_typ_prvdd_cd Value of lookup code.
687 -- effective_date effective date
688 -- object_version_number Object version number of record being
689 -- inserted or updated.
690 --
691 -- Post Success
692 -- Processing continues
693 --
694 -- Post Failure
695 -- Error handled by procedure
696 --
697 -- Access Status
698 -- Internal table handler use only.
699 --
700 Procedure chk_reimbmt_ctfn_typ_prvdd_cd
701 (p_prtt_reimbmt_rqst_id in number,
702 p_reimbmt_ctfn_typ_prvdd_cd in varchar2,
703 p_effective_date in date,
704 p_object_version_number in number) is
705 --
706 l_proc varchar2(72) := g_package||'chk_reimbmt_ctfn_typ_prvdd_cd';
707 l_api_updating boolean;
708 --
709 Begin
710 --
711 hr_utility.set_location('Entering:'||l_proc, 5);
712 --
713 l_api_updating := ben_prc_shd.api_updating
714 (p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id,
715 p_effective_date => p_effective_date,
716 p_object_version_number => p_object_version_number);
717 --
718 if (l_api_updating
719 and p_reimbmt_ctfn_typ_prvdd_cd
720 <> nvl(ben_prc_shd.g_old_rec.reimbmt_ctfn_typ_prvdd_cd,hr_api.g_varchar2)
721 or not l_api_updating)
722 and p_reimbmt_ctfn_typ_prvdd_cd is not null then
723 --
724 -- check if value of lookup falls within lookup type.
725 --
726 if hr_api.not_exists_in_hr_lookups
727 (p_lookup_type => 'BEN_RMBMT_CTFN_TYP',
728 p_lookup_code => p_reimbmt_ctfn_typ_prvdd_cd,
729 p_effective_date => p_effective_date) then
730 --
731 -- raise error as does not exist as lookup
732 --
733 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
734 fnd_message.set_token('FIELD','p_reimbmt_ctfn_typ_prvdd_cd');
735 fnd_message.set_token('VALUE', p_reimbmt_ctfn_typ_prvdd_cd);
736 fnd_message.set_token('TYPE','BEN_RMBMT_CTFN_TYP');
737 fnd_message.raise_error;
738 --
739 end if;
740 --
741 end if;
742 --
743 hr_utility.set_location('Leaving:'||l_proc,10);
744 --
745 end chk_reimbmt_ctfn_typ_prvdd_cd;
746
747
748
749 -- ----------------------------------------------------------------------------
750 -- |------< chk_prtt_reimbmt_stat_apprvd >------|
751 -- ----------------------------------------------------------------------------
752
753 Procedure chk_prtt_reimbmt_stat_apprvd
754 (p_prtt_reimbmt_rqst_id in number,
755 p_aprvd_for_pymt_amt in number ,
756 p_prtt_reimbmt_rqst_stat_cd in out nocopy varchar2,
757 p_stat_rsn_cd in out nocopy varchar2,
758 p_effective_date in date
759 ) is
760 --
761 l_proc varchar2(72) := g_package||'chk_prtt_reimbmt_stat_apprvd';
762 l_api_updating boolean;
763
764 cursor c_pcg is
765 select 'x' from
766 ben_prtt_clm_gd_or_svc_typ pcg
767 where prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id ;
768 --
769 l_ctfn_pending_flag varchar2(1) ;
770 l_dummy_var varchar2(1) ;
771 Begin
772
773 hr_utility.set_location('Entering:'||l_proc, 5);
774
775 if p_prtt_reimbmt_rqst_stat_cd in ('APPRVD','PDINFL','PRTLYPD') then
776
777 -- Check the approved amount is entered
778 if nvl(p_aprvd_for_pymt_amt,0) = 0 then
779 fnd_message.set_name('BEN','BEN_92715_APRVD_AMT_IS_NULL');
780 fnd_message.raise_error ;
781 end if ;
782
783 -- Check the goods/service are defiend if not
784 -- changes the status to pending
785 open c_pcg ;
786 fetch c_pcg into l_dummy_var ;
787 close c_pcg ;
788
789 if l_dummy_var is null then
790 p_prtt_reimbmt_rqst_stat_cd := 'PNDNG' ;
791 p_stat_rsn_cd := 'RMBGRVREQ' ;
792 else
793
794 ----Certification is required validate
795 ben_PRTT_CLM_GD_R_SVC_TYP_api.check_remb_rqst_ctfn_prvdd
796 (p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
797 ,p_effective_date => p_effective_date
798 ,p_ctfn_pending_flag => l_ctfn_pending_flag ) ;
799
800 if l_ctfn_pending_flag = 'Y' then
801 --fnd_message.set_name('BEN','BEN_92706_REIMB_CTFN_NOT_PRVDD');
802 --fnd_message.show ;
803 p_prtt_reimbmt_rqst_stat_cd := 'PNDNG' ;
804 p_stat_rsn_cd := 'RMBCTFNRQD' ;
805 end if ;
806 end if ;
807
808
809 end if ;
810 --
811 hr_utility.set_location('Leaving:'||l_proc,10);
812 --
813
814 end chk_prtt_reimbmt_stat_apprvd ;
815 --
816
817
818 --
819 -- ----------------------------------------------------------------------------
820 -- |------< chk_stat_rsn_cd >------|
821 -- ----------------------------------------------------------------------------
822 --
823 Procedure chk_stat_rsn_cd
824 (p_prtt_reimbmt_rqst_id in number,
825 p_stat_rsn_cd in varchar2,
826 p_effective_date in date,
827 p_object_version_number in number) is
828 --
829 l_proc varchar2(72) := g_package||'chk_stat_rsn_cd';
830 l_api_updating boolean;
831 --
832 Begin
833 --
834 hr_utility.set_location('Entering:'||l_proc, 5);
835 --
836
837 l_api_updating := ben_prc_shd.api_updating
838 (p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id,
839 p_effective_date => p_effective_date,
840 p_object_version_number => p_object_version_number);
841 --
842 if (l_api_updating
843 and p_stat_rsn_cd
844 <> nvl(ben_prc_shd.g_old_rec.stat_rsn_cd,hr_api.g_varchar2)
845 or not l_api_updating)
846 and p_stat_rsn_cd is not null then
847 --
848 -- check if value of lookup falls within lookup type.
849 --
850 if hr_api.not_exists_in_hr_lookups
851 (p_lookup_type => 'BEN_STAT_RSN',
852 p_lookup_code => p_stat_rsn_cd,
853 p_effective_date => p_effective_date) then
854 --
855 -- raise error as does not exist as lookup
856 --
857 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
858 fnd_message.set_token('FIELD','p_stat_rsn_cd');
859 fnd_message.set_token('VALUE', p_stat_rsn_cd);
860 fnd_message.set_token('TYPE','BEN_STAT_RSN');
861 fnd_message.raise_error;
862 --
863 end if;
864
865 --
866 end if;
867 --
868 hr_utility.set_location('Leaving:'||l_proc,10);
869 --
870 end chk_stat_rsn_cd;
871
872
873
874 -- ----------------------------------------------------------------------------
875 -- |------< chk_pymt_stat_rsn_cd >------|
876 -- ----------------------------------------------------------------------------
877 --
878 Procedure chk_pymt_stat_rsn_cd
879 (p_prtt_reimbmt_rqst_id in number,
880 p_pymt_stat_rsn_cd in varchar2,
881 p_effective_date in date,
882 p_object_version_number in number) is
883 --
884 l_proc varchar2(72) := g_package||'chk_pymt_stat_rsn_cd';
885 l_api_updating boolean;
886 --
887 Begin
888 --
889 hr_utility.set_location('Entering:'||l_proc, 5);
890 --
891
892 l_api_updating := ben_prc_shd.api_updating
893 (p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id,
894 p_effective_date => p_effective_date,
895 p_object_version_number => p_object_version_number);
896 --
897 if (l_api_updating
898 and p_pymt_stat_rsn_cd
899 <> nvl(ben_prc_shd.g_old_rec.pymt_stat_rsn_cd,hr_api.g_varchar2)
900 or not l_api_updating)
901 and p_pymt_stat_rsn_cd is not null then
902 --
903 -- check if value of lookup falls within lookup type.
904 --
905 if hr_api.not_exists_in_hr_lookups
906 (p_lookup_type => 'BEN_PYMT_STAT_RSN',
907 p_lookup_code => p_pymt_stat_rsn_cd,
908 p_effective_date => p_effective_date) then
909 --
910 -- raise error as does not exist as lookup
911 --
912 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
913 fnd_message.set_token('FIELD','p_pymt_stat_rsn_cd');
914 fnd_message.set_token('VALUE', p_pymt_stat_rsn_cd);
915 fnd_message.set_token('TYPE','BEN_PYMT_STAT_RSN');
916 fnd_message.raise_error;
917 --
918 end if;
919
920 --
921 end if;
922 --
923 hr_utility.set_location('Leaving:'||l_proc,10);
924 --
925 end chk_pymt_stat_rsn_cd;
926
927
928 -- ----------------------------------------------------------------------------
929 -- |------< chk_pymt_stat_cd >------|
930 -- ----------------------------------------------------------------------------
931 --
932 Procedure chk_pymt_stat_cd
933 (p_prtt_reimbmt_rqst_id in number,
934 p_pymt_stat_cd in varchar2,
935 p_effective_date in date,
936 p_object_version_number in number) is
937 --
938 l_proc varchar2(72) := g_package||'chk_pymt_stat_cd';
939 l_api_updating boolean;
940 --
941 Begin
942 --
943 hr_utility.set_location('Entering:'||l_proc, 5);
944 --
945
946 l_api_updating := ben_prc_shd.api_updating
947 (p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id,
948 p_effective_date => p_effective_date,
949 p_object_version_number => p_object_version_number);
950 --
951 if (l_api_updating
952 and p_pymt_stat_cd
953 <> nvl(ben_prc_shd.g_old_rec.pymt_stat_cd,hr_api.g_varchar2)
954 or not l_api_updating)
955 and p_pymt_stat_cd is not null then
956 --
957 -- check if value of lookup falls within lookup type.
958 --
959 if hr_api.not_exists_in_hr_lookups
960 (p_lookup_type => 'BEN_PYMT_STAT',
961 p_lookup_code => p_pymt_stat_cd,
962 p_effective_date => p_effective_date) then
963 --
964 -- raise error as does not exist as lookup
965 --
966 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
967 fnd_message.set_token('FIELD','p_pymt_stat_cd');
968 fnd_message.set_token('VALUE', p_pymt_stat_cd);
969 fnd_message.set_token('TYPE','BEN_PYMT_STAT');
970 fnd_message.raise_error;
971 --
972 end if;
973
974 --
975 end if;
976 --
977 hr_utility.set_location('Leaving:'||l_proc,10);
978 --
979 end chk_pymt_stat_cd;
980
981
982 --
983 -- ----------------------------------------------------------------------------
984 -- |------< chk_prtt_reimbmt_rqst_stat_cd >------|
985 -- ----------------------------------------------------------------------------
986 --
987 -- Description
988 -- This procedure is used to check that the lookup value is valid.
989 --
990 -- Pre Conditions
991 -- None.
992 --
993 -- In Parameters
994 -- prtt_reimbmt_rqst_id PK of record being inserted or updated.
995 -- prtt_reimbmt_rqst_stat_cd Value of lookup code.
996 -- effective_date effective date
997 -- object_version_number Object version number of record being
998 -- inserted or updated.
999 --
1000 -- Post Success
1001 -- Processing continues
1002 --
1003 -- Post Failure
1004 -- Error handled by procedure
1005 --
1006 -- Access Status
1007 -- Internal table handler use only.
1008 --
1009 Procedure chk_prtt_reimbmt_rqst_stat_cd
1010 (p_prtt_reimbmt_rqst_id in number,
1011 p_prtt_reimbmt_rqst_stat_cd in varchar2,
1012 p_effective_date in date,
1013 p_object_version_number in number) is
1014 --
1015 l_proc varchar2(72) := g_package||'chk_prtt_reimbmt_rqst_stat_cd';
1016 l_api_updating boolean;
1017 --
1018 Begin
1019 --
1020 hr_utility.set_location('Entering:'||l_proc, 5);
1021 --
1022
1023 l_api_updating := ben_prc_shd.api_updating
1024 (p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id,
1025 p_effective_date => p_effective_date,
1026 p_object_version_number => p_object_version_number);
1027 --
1028
1029 ----If the Status is Approved or VOID dont allow to changed
1030 --- This has to be REMOVED or UPDATED whne the status logic change
1031
1032 -- hnarayan -- bug fix 2223214
1033 -- before this, no updates where allowed on a claim record once the
1034 -- status is approved.
1035 -- Now, there can be a change from any of the approved status to a non-approved status
1036 -- But change is not allowed between the three approved statuses once the claim is approved.
1037 --
1038 -- Now we should throw error only if user tries to change from one Approved status to another
1039 -- Approved status. Hence modified the if condn below.
1040 --
1041 if l_api_updating -- added l_api_updating to perform the check only when updating
1042 and ben_prc_shd.g_old_rec.prtt_reimbmt_rqst_stat_cd in ('APPRVD','PDINFL','PRTLYPD')
1043 and p_prtt_reimbmt_rqst_stat_cd in ('APPRVD','PDINFL','PRTLYPD') then
1044 --
1045 fnd_message.set_name('BEN','BEN_92705_REIMB_RQST_APPROVD');
1046 fnd_message.raise_error;
1047 --
1048 end if ;
1049
1050 -- hnarayan -- bug fix 2223214
1051 -- i am not changing the logic for update of Voided claims
1052 -- since it makes more sense for a voided claim to be just present for
1053 -- information sake and not for processing
1054 -- But, since no payment is made for a voided claim,
1055 -- presence of voided claims shud not prevent back-out of a life event.
1056 --
1057 ----Once voiced rquest is not allowd to be cahnged
1058 if l_api_updating and ben_prc_shd.g_old_rec.prtt_reimbmt_rqst_stat_cd = 'VOIDED' then
1059 fnd_message.set_name('BEN','BEN_92708_REIMB_RQST_VOIDED');
1060 fnd_message.raise_error;
1061 end if ;
1062
1063 if (l_api_updating
1064 and p_prtt_reimbmt_rqst_stat_cd
1065 <> nvl(ben_prc_shd.g_old_rec.prtt_reimbmt_rqst_stat_cd,hr_api.g_varchar2)
1066 or not l_api_updating)
1067 and p_prtt_reimbmt_rqst_stat_cd is not null then
1068 --
1069 -- check if value of lookup falls within lookup type.
1070 --
1071 if hr_api.not_exists_in_hr_lookups
1072 (p_lookup_type => 'BEN_REIMBMT_RQST_STAT',
1073 p_lookup_code => p_prtt_reimbmt_rqst_stat_cd,
1074 p_effective_date => p_effective_date) then
1075 --
1076 -- raise error as does not exist as lookup
1077 --
1078 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1079 fnd_message.set_token('FIELD','p_prtt_reimbmt_rqst_stat_cd');
1080 fnd_message.set_token('VALUE', p_prtt_reimbmt_rqst_stat_cd);
1081 fnd_message.set_token('TYPE','BEN_REIMBMT_RQST_STAT');
1082 fnd_message.raise_error;
1083 --
1084 end if;
1085 --
1086 end if;
1087 --
1088 hr_utility.set_location('Leaving:'||l_proc,10);
1089 --
1090 end chk_prtt_reimbmt_rqst_stat_cd;
1091 --
1092 -- ----------------------------------------------------------------------------
1093 -- |------< chk_pl_id_rqst_amt_and_uom >------|
1094 -- ----------------------------------------------------------------------------
1095 --
1096 -- Description
1097 -- This procedure is used to check that the lookup value is valid.
1098 --
1099 -- Pre Conditions
1100 -- None.
1101 --
1102 -- In Parameters
1103 -- prtt_reimbmt_rqst_id PK of record being inserted or updated.
1104 -- rqst_amt_uom Value of lookup code.
1105 -- effective_date effective date
1106 -- object_version_number Object version number of record being
1107 -- inserted or updated.
1108 --
1109 -- Post Success
1110 -- Processing continues
1111 --
1112 -- Post Failure
1113 -- Error handled by procedure
1114 --
1115 -- Access Status
1116 -- Internal table handler use only.
1117 --
1118 Procedure chk_pl_id_rqst_amt_and_uom(p_prtt_reimbmt_rqst_id in number,
1119 p_rqst_amt_uom in varchar2,
1120 p_rqst_amt in number,
1121 p_pl_id in number,
1122 p_submitter_person_id in number,
1123 p_business_group_id in number,
1124 p_effective_date in date,
1125 p_object_version_number in number,
1126 p_prtt_enrt_rslt_id in number,
1127 p_prtt_reimbmt_rqst_stat_cd in out nocopy varchar2,
1128 p_stat_rsn_cd in out nocopy varchar2,
1129 p_pymt_stat_cd in out nocopy varchar2,
1130 p_pymt_stat_rsn_cd in out nocopy varchar2,
1131 p_pymt_amount in out nocopy number ,
1132 p_aprvd_for_pymt_amt in out nocopy number ,
1133 p_popl_yr_perd_id_1 in number,
1134 p_popl_yr_perd_id_2 in number,
1135 p_amt_1 out nocopy number,
1136 p_amt_2 out nocopy number,
1137 -- p_incrd_from_dt in date ) is -- 2272862
1138 p_exp_incurd_dt in date ) is
1139 --
1140 l_proc varchar2(72) := g_package||'chk_pl_id_rqst_amt_and_uom';
1141 l_api_updating boolean;
1142
1143
1144 cursor c1 is
1145 select 'x'
1146 from fnd_currencies curr
1147 where curr.currency_code = p_rqst_amt_uom
1148 and curr.enabled_flag = 'Y'
1149 and p_effective_date
1150 between nvl(curr.start_date_active, p_effective_date)
1151 and nvl(curr.end_date_active, p_effective_date) ;
1152 l_test varchar2(1) := null;
1153
1154 ---- This cursor changed to pickup the latest coverage value
1155 --- from the plan year the EOT may not work if the
1156 --- coverage ended/changed in future date # 2469785
1157 --- changed to pickup the latest wihtin the plan year
1158 cursor c2 (p_popl_yr_perd_id number) is
1159 select pen.bnft_amt
1160 , pln.cmpr_clms_to_cvg_or_bal_cd
1161 ,pen.pgm_id
1162 ,per_in_ler_id
1163 from ben_prtt_enrt_rslt_f pen,
1164 ben_pl_f pln,
1165 ben_popl_yr_perd cpy,
1166 ben_yr_perd yrp
1167 where pln.pl_id = p_pl_id
1168 and pln.pl_id = pen.pl_id
1169 and pen.person_id = p_submitter_person_id
1170 and pln.business_group_id = p_business_group_id
1171 and cpy.pl_id = pln.pl_id
1172 and cpy.yr_perd_id = yrp.yr_perd_id
1173 and cpy.popl_yr_perd_id = p_popl_yr_perd_id
1174 and pen.enrt_cvg_strt_dt <= yrp.end_date
1175 and pen.enrt_cvg_thru_dt >= yrp.start_date
1176 and pen.prtt_enrt_rslt_stat_cd is null
1177 AND pen.enrt_cvg_thru_dt >= pen.effective_start_date /* Bug 5607655 : To remove invalid records */
1178 and pen.effective_start_date =
1179 (select max(pen_1.effective_start_date)
1180 from ben_prtt_enrt_rslt_f pen_1
1181 where pen_1.person_id = pen.person_id
1182 and pen_1.pl_id = pen.pl_id
1183 and pen_1.prtt_enrt_rslt_stat_cd is null
1184 and pen_1.enrt_cvg_strt_dt <= yrp.end_date
1185 and pen_1.enrt_cvg_thru_dt >= yrp.start_date ) ;
1186 --
1187 cursor c_pln (p_pl_id number) is
1188 select pln.cmpr_clms_to_cvg_or_bal_cd
1189 from ben_pl_f pln
1190 where pln.pl_id = p_pl_id
1191 and pln.business_group_id = p_business_group_id
1192 and p_effective_date between pln.effective_start_date
1193 and pln.effective_end_date;
1194 --
1195 l_cmpr_clms_to_cvg_or_bal_cd varchar2(300);
1196 l_c2_rec c2%rowtype;
1197 l_ptd_balance ben_prtt_reimbmt_rqst_f.rqst_amt%type ;
1198 l_amt_1 number;
1199 l_amt_2 number;
1200 prev_yr_cvg number;
1201 --
1202 Begin
1203 --
1204 hr_utility.set_location('Entering:'||l_proc, 5);
1205 --
1206 l_api_updating := ben_prc_shd.api_updating
1207 (p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id,
1208 p_effective_date => p_effective_date,
1209 p_object_version_number => p_object_version_number);
1210 --
1211
1212 hr_utility.set_location(' p_exp_incurd_dt ' || p_exp_incurd_dt ,192);
1213
1214 -- Check the UOM value
1215 if (l_api_updating
1216 and p_rqst_amt_uom
1217 <> nvl(ben_prc_shd.g_old_rec.rqst_amt_uom,hr_api.g_varchar2)
1218 or not l_api_updating)
1219 and p_rqst_amt_uom is not null then
1220 --
1221 -- check if value of lookup falls within lookup type.
1222 --
1223 open c1;
1224 fetch c1 into l_test;
1225 close c1;
1226 if l_test is null then
1227 --
1228 -- raise error as does not exist as lookup
1229 --
1230 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1231 fnd_message.set_token('FIELD','p_rqst_amt_uom');
1232 fnd_message.set_token('VALUE', p_rqst_amt_uom);
1233 fnd_message.set_token('TYPE','fnd_currencies');
1234 fnd_message.raise_error;
1235 --
1236 end if;
1237 end if;
1238
1239 -- Check the plan id
1240 open c2 (p_popl_yr_perd_id_1);
1241 fetch c2 into l_c2_rec;
1242 if c2%notfound and p_popl_yr_perd_id_2 is null then
1243 close c2;
1244 fnd_message.set_name('BEN','BEN_91452_PL_ENRT_MISSING');
1245 fnd_message.set_token('PERSON_ID', p_submitter_person_id);
1246 fnd_message.set_token('PLAN_ID', p_pl_id);
1247 fnd_message.raise_error;
1248 end if;
1249 close c2;
1250 --
1251 if l_c2_rec.cmpr_clms_to_cvg_or_bal_cd is null then
1252 --
1253 open c_pln(p_pl_id);
1254 fetch c_pln into l_cmpr_clms_to_cvg_or_bal_cd;
1255 close c_pln;
1256 --
1257 else
1258 --
1259 l_cmpr_clms_to_cvg_or_bal_cd := l_c2_rec.cmpr_clms_to_cvg_or_bal_cd;
1260 --
1261 end if;
1262
1263
1264 ---if the aproved amount is more than the requested amount then error
1265 if nvl(p_rqst_amt,0) < nvl(p_aprvd_for_pymt_amt,0) then
1266 fnd_message.set_name('BEN','BEN_92714_APRVD_MORE_THAN_RMT');
1267 fnd_message.raise_error;
1268 end if ;
1269
1270 -- check the rqst_amt
1271 ---tilak : this check removed ,approved amount is no stored
1272 --- so it is necessary to validates while approving
1273 --- approval can happend without any changes in amount
1274 --- When the amount is compered with coverage , if the coverage amount is
1275 -- not sufficient then the status is changed but in case of balance
1276 --- payment status is changed
1277 --if (l_api_updating
1278 -- and p_rqst_amt
1279 -- <> nvl(ben_prc_shd.g_old_rec.rqst_amt,hr_api.g_number)
1280 -- or not l_api_updating)
1281 -- and p_rqst_amt is not null then
1282
1283 -- if the plan's CMPR_CLMS_TO_CVG_OR_BAL_CD is cvg, the amt
1284 -- cannot exceed the result's coverage amount.
1285 l_amt_1 := 0;
1286 l_amt_2 := 0;
1287 plan_year_claim(
1288 p_pl_id => p_pl_id
1289 ,p_person_id => p_submitter_person_id
1290 ,p_business_group_id => p_business_group_id
1291 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
1292 ,p_effective_date => p_effective_date
1293 ,p_popl_yr_perd_id_1 => p_popl_yr_perd_id_1
1294 ,p_popl_yr_perd_id_2 => p_popl_yr_perd_id_2
1295 ,p_amt_1 => l_amt_1
1296 ,p_amt_2 => l_amt_2
1297 ,p_exp_incurd_dt => p_exp_incurd_dt );
1298
1299 hr_utility.set_location('claim amount I year' || l_amt_1 , 293 );
1300 hr_utility.set_location('claim amount II year ' || l_amt_2 , 294 );
1301
1302 if l_cmpr_clms_to_cvg_or_bal_cd in ('CVG', 'BAL') then
1303 -- if the expense is not in grace period
1304 if p_popl_yr_perd_id_2 is null then
1305 if nvl(l_c2_rec.bnft_amt,0) < l_amt_1+nvl(p_aprvd_for_pymt_amt,p_rqst_amt) then
1306 -- chek there is no fund or partial fund
1307 if (nvl(l_c2_rec.bnft_amt,0) - l_amt_1 ) <= 0 then
1308 p_pymt_amount := null ;
1309 p_aprvd_for_pymt_amt := null ;
1310 p_prtt_reimbmt_rqst_stat_cd := 'DND' ;
1311 p_stat_rsn_cd := 'RMBINCVG' ;
1312 Else
1313 P_PYMT_STAT_CD := 'RMBPRTPAID' ;
1314 p_stat_rsn_cd := 'RMBINCVG' ;
1315 p_pymt_amount := (nvl(l_c2_rec.bnft_amt,0) - l_amt_1 ) ;
1316 p_aprvd_for_pymt_amt := p_pymt_amount ;
1317 p_amt_1 := p_aprvd_for_pymt_amt;
1318 End if ;
1319 --
1320 Else
1321 --
1322 hr_utility.set_location ('Fully Paid'||p_aprvd_for_pymt_amt,11);
1323 P_PYMT_STAT_CD := 'RMBFLPAID' ;
1324 p_pymt_amount := nvl(p_aprvd_for_pymt_amt,p_rqst_amt) ;
1325 p_amt_1 := p_pymt_amount;
1326 --
1327 end if;
1328 --if the expense in grace period
1329 else
1330 --
1331 hr_utility.set_location ('Previos year coverage'||l_c2_rec.bnft_amt,12);
1332 prev_yr_cvg := nvl(l_c2_rec.bnft_amt,0);
1333 open c2 (p_popl_yr_perd_id_2);
1334 fetch c2 into l_c2_rec;
1335 if c2%notfound then
1336 close c2;
1337 fnd_message.set_name('BEN','BEN_91452_PL_ENRT_MISSING');
1338 fnd_message.set_token('PERSON_ID', p_submitter_person_id);
1339 fnd_message.set_token('PLAN_ID', p_pl_id);
1340 fnd_message.raise_error;
1341 end if;
1342 close c2;
1343 --
1344 if prev_yr_cvg <= l_amt_1 or prev_yr_cvg = 0 then
1345 -- the previous year benefit amount fully used so consider for this year
1346 -- there was no coverage in the previous year
1347 hr_utility.set_location ('Previous fully used or no coverage',100);
1348 hr_utility.set_location ('Benefit amont '||l_c2_rec.bnft_amt,101);
1349 hr_utility.set_location ('Approved amount '||p_aprvd_for_pymt_amt,102);
1350 hr_utility.set_location (l_amt_2 + nvl(p_aprvd_for_pymt_amt,p_rqst_amt),103);
1351 if nvl(l_c2_rec.bnft_amt,0) < (l_amt_2 + nvl(p_aprvd_for_pymt_amt,p_rqst_amt))
1352 then
1353 -- chek there is no fund or partial fund
1354 hr_utility.set_location ('Partial payment',103);
1355 if (nvl(l_c2_rec.bnft_amt,0) - l_amt_2 ) <= 0 then
1356 p_pymt_amount := null ;
1357 p_aprvd_for_pymt_amt := null ;
1358 p_prtt_reimbmt_rqst_stat_cd := 'DND' ;
1359 p_stat_rsn_cd := 'RMBINCVG' ;
1360 Else
1361 P_PYMT_STAT_CD := 'RMBPRTPAID' ;
1362 p_stat_rsn_cd := 'RMBINCVG' ;
1363 p_pymt_amount := (nvl(l_c2_rec.bnft_amt,0) - l_amt_2 )
1364 ;
1365 p_aprvd_for_pymt_amt := p_pymt_amount ;
1366 p_amt_2 := p_aprvd_for_pymt_amt;
1367 End if ;
1368 --
1369 else
1370 --
1371 hr_utility.set_location ('Fully Paid', 105);
1372 P_PYMT_STAT_CD := 'RMBFLPAID' ;
1373 p_pymt_amount := nvl(p_aprvd_for_pymt_amt,p_rqst_amt) ;
1374 p_amt_2 := p_pymt_amount;
1375 --
1376 end if;
1377 --
1378 else -- balance available in previous year
1379 --
1380 hr_utility.set_location ('Balance Available prev yr',13);
1381 if prev_yr_cvg >= l_amt_1 + nvl(p_aprvd_for_pymt_amt,p_rqst_amt) then
1382 --
1383 hr_utility.set_location ('previous year fully paid',14);
1384 P_PYMT_STAT_CD := 'RMBFLPAID' ;
1385 p_pymt_amount := nvl(p_aprvd_for_pymt_amt,p_rqst_amt) ;
1386 p_amt_1 := p_pymt_amount;
1387 --
1388 elsif (prev_yr_cvg + nvl(l_c2_rec.bnft_amt,0)) >= (l_amt_1 + l_amt_2 +
1389 nvl(p_aprvd_for_pymt_amt,p_rqst_amt)) then
1390 --
1391 hr_utility.set_location ('Claim falls on two year',15);
1392 P_PYMT_STAT_CD := 'RMBFLPAID' ;
1393 p_pymt_amount := nvl(p_aprvd_for_pymt_amt,p_rqst_amt) ;
1394 p_amt_1 := prev_yr_cvg - l_amt_1;
1395 p_amt_2 := p_pymt_amount - p_amt_1;
1396 elsif nvl(l_c2_rec.bnft_amt,0) = 0 then --no current coverage
1397 --
1398 hr_utility.set_location ('No current year coverage',16);
1399 p_pymt_amount := prev_yr_cvg - l_amt_1;
1400 p_amt_1 := p_pymt_amount;
1401 P_PYMT_STAT_CD := 'RMBPRTPAID' ;
1402 p_stat_rsn_cd := 'RMBINCVG' ;
1403 p_aprvd_for_pymt_amt := p_pymt_amount ;
1404 --
1405 elsif (prev_yr_cvg + nvl(l_c2_rec.bnft_amt,0)) < (l_amt_1 + l_amt_2 +
1406 nvl(p_aprvd_for_pymt_amt,p_rqst_amt)) then
1407 --
1408 hr_utility.set_location ('Partially paid for two years',17);
1409 p_amt_1 := prev_yr_cvg - l_amt_1;
1410 p_amt_2 := nvl(l_c2_rec.bnft_amt,0) - l_amt_2;
1411 p_pymt_amount := p_amt_1 + p_amt_2;
1412 P_PYMT_STAT_CD := 'RMBPRTPAID' ;
1413 p_stat_rsn_cd := 'RMBINCVG' ;
1414 p_aprvd_for_pymt_amt := p_pymt_amount ;
1415 --
1416 end if;
1417 end if;
1418 end if;
1419 end if;
1420 --
1421 /*
1422 If l_c2_rec.cmpr_clms_to_cvg_or_bal_cd = 'BAL' then
1423 -- compare amt to a balance.
1424 l_ptd_balance := get_year_balance (
1425 p_person_id => p_submitter_person_id
1426 ,p_pgm_id => l_c2_rec.pgm_id
1427 ,p_pl_id => p_pl_id
1428 ,p_business_group_id => p_business_group_id
1429 ,p_per_in_ler_id => l_c2_rec.per_in_ler_id
1430 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1431 ,p_effective_date => p_effective_date
1432 ,p_exp_incurd_dt => p_exp_incurd_dt
1433 ) ;
1434
1435
1436 if l_ptd_balance < l_amt_1+nvl(p_aprvd_for_pymt_amt,p_rqst_amt) then
1437 -- chek there is no fund or partial fund
1438 if (nvl(l_ptd_balance,0) - l_amt_1 ) <= 0 then
1439 P_PYMT_STAT_CD := 'RMBPNDNG' ;
1440 p_pymt_amount := null ;
1441 Else
1442 P_PYMT_STAT_CD := 'RMBPRTPAID' ;
1443 p_pymt_amount := (nvl(l_ptd_balance,0) - l_amt_1 );
1444 End if ;
1445 ----validate for coverage whether the amount is less then coveragwe
1446 if nvl(l_c2_rec.bnft_amt,0) < l_amt_1+nvl(p_aprvd_for_pymt_amt,p_rqst_amt) then
1447
1448 if (nvl(l_c2_rec.bnft_amt,0) - l_amt_1 ) <= 0 then
1449 p_pymt_amount := null ;
1450 p_aprvd_for_pymt_amt := null ;
1451 p_prtt_reimbmt_rqst_stat_cd := 'DND' ;
1452 p_stat_rsn_cd := 'RMBINCVG' ;
1453 Else
1454 p_stat_rsn_cd := 'RMBINCVG' ;
1455 p_pymt_amount := (nvl(l_c2_rec.bnft_amt,0) - l_amt_1 ) ;
1456 p_aprvd_for_pymt_amt := p_pymt_amount ;
1457 End if ;
1458
1459 end if;
1460
1461 Else
1462 P_PYMT_STAT_CD := 'RMBFLPAID' ;
1463 p_pymt_amount := nvl(p_aprvd_for_pymt_amt,p_rqst_amt) ;
1464 p_amt_1 := p_pymt_amount;
1465 end if;
1466 end if; */
1467 hr_utility.set_location(' pyment amount ' || p_pymt_amount ,110);
1468
1469 hr_utility.set_location('Leaving:'||l_proc,10);
1470 end chk_pl_id_rqst_amt_and_uom;
1471
1472 ------------------------------------------------------------------------------
1473 -- |------< chk_remb_status >-----|
1474 -- ----------------------------------------------------------------------------
1475
1476 procedure chk_remb_status(
1477 p_prtt_reimbmt_rqst_stat_cd in varchar2
1478 )is
1479 l_proc varchar2(72) := g_package||' chk_remb_status';
1480 begin
1481 hr_utility.set_location('Entering:'||l_proc, 5);
1482
1483 if p_prtt_reimbmt_rqst_stat_cd in ('APPRVD','PDINFL','PRTLYPD') then
1484 fnd_message.set_name('BEN','BEN_92705_REIMB_RQST_APPROVD');
1485 fnd_message.raise_error;
1486 end if ;
1487 if p_prtt_reimbmt_rqst_stat_cd = 'VOIDED' then
1488 fnd_message.set_name('BEN','BEN_92708_REIMB_RQST_VOIDED');
1489 fnd_message.raise_error;
1490 end if ;
1491
1492 hr_utility.set_location('Leaving:'||l_proc,10);
1493 end chk_remb_status;
1494
1495
1496 --
1497 ------------------------------------------------------------------------------
1498 -- |------< chk_future_dated >-----|
1499 -- ----------------------------------------------------------------------------
1500 ---- check future dated reimbursement requst or election exist
1501 procedure chk_future_dated(
1502 p_pl_id in number,
1503 p_submitter_person_id in number,
1504 p_prtt_reimbmt_rqst_id in number,
1505 p_business_group_id in number,
1506 p_effective_date in date
1507 ) is
1508
1509 cursor c_prc is
1510 select 'x' from ben_prtt_reimbmt_rqst_f
1511 where prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
1512 and effective_start_date > p_effective_date ;
1513
1514 cursor c_asg is
1515 select assignment_id
1516 ,payroll_id
1517 from per_all_assignments_f
1518 where person_id = p_submitter_person_id
1519 and assignment_type <> 'C'
1520 and p_effective_Date between
1521 effective_start_date and effective_end_date ;
1522
1523
1524 cursor c_prv is
1525 select acty_base_rt_id , rt_strt_dt
1526 from ben_prtt_rt_val
1527 where prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id ;
1528
1529
1530
1531
1532 cursor c_per is
1533 select 'x'
1534 from ben_prtt_enrt_rslt_f pen, ben_pl_f pln
1535 where pln.pl_id = p_pl_id
1536 and pln.pl_id = pen.pl_id
1537 and pen.person_id = p_submitter_person_id
1538 and pln.business_group_id = p_business_group_id
1539 and pen.effective_start_date > p_effective_date
1540 and p_effective_date between
1541 pln.effective_start_date and pln.effective_end_date
1542 and pen.prtt_enrt_rslt_stat_cd is null ;
1543
1544
1545 cursor c_get_end_dt(p_payroll_id in number
1546 ,p_acty_base_rt_id in number
1547 ,p_business_group_id in number
1548 ,p_assignment_id in number
1549 ,p_effective_date in date)
1550 is
1551 select /*+ leading(d) use_nl(d i e h a j g b) index(h PAY_RUN_RESULTS_N50) */
1552 max(g.end_date) end_date
1553 from
1554 pay_run_result_values a,
1555 pay_element_types_f b,
1556 pay_assignment_actions d,
1557 pay_payroll_actions e,
1558 per_time_periods g,
1559 pay_run_results h,
1560 ben_acty_base_rt_f i,
1561 pay_input_values_f j
1562 where d.assignment_id = p_assignment_id
1563 and d.payroll_action_id = e.payroll_action_id
1564 and i.input_value_id = j.input_value_id
1565 and i.element_type_id = b.element_type_id
1566 and i.acty_base_rt_id = p_acty_base_rt_id
1567 and p_effective_date
1568 between i.effective_start_date and i.effective_end_date
1569 and i.business_group_id = p_business_group_id
1570 and g.payroll_id = p_payroll_id
1571 and b.element_type_id = h.element_type_id
1572 and d.assignment_action_id = h.assignment_action_id
1573 and e.date_earned between
1574 g.start_date and g.end_date
1575 and a.input_value_id = j.input_value_id
1576 and a.run_result_id = h.run_result_id
1577 and j.element_type_id = b.element_type_id
1578 and p_effective_date between
1579 b.effective_start_date and b.effective_end_date
1580 and p_effective_date between
1581 j.effective_start_date and j.effective_end_date;
1582
1583 g_max_end_date date := null;
1584 l_dummy_var varchar2(1) ;
1585 l_proc varchar2(72) := g_package||'chk_future_date';
1586 l_payroll_id number ;
1587 l_assignment_id number ;
1588 l_acty_base_rt_id number ;
1589 l_rt_strt_dt date ;
1590 begin
1591
1592 hr_utility.set_location('Entering:'||l_proc,5);
1593 -- when future date tracked row in available for the reimbmt req
1594 /* thery may be status cahnges though there is futer dated entry there
1595 hr_utility.set_location('checking for futrue prc:'||l_proc,5);
1596 open c_prc ;
1597 fetch c_prc into l_dummy_var ;
1598 if c_prc%found then
1599 close c_prc ;
1600 fnd_message.set_name('BEN', 'BEN_92663_FUTURE_DTD_REIMB_REQ');
1601 fnd_message.raise_error;
1602 end if ;
1603 close c_prc ;
1604 */
1605 -- when the future date tracked election is avaialble
1606 hr_utility.set_location('checking for future election:'||l_proc,5);
1607 /*
1608 open c_per ;
1609 fetch c_per into l_dummy_var ;
1610 if c_per%found then
1611 close c_per ;
1612 fnd_message.set_name('BEN', 'BEN_92663_FUTURE_DTD_ELE_EXIST');
1613 fnd_message.raise_error;
1614 end if ;
1615 close c_per ;
1616 */
1617 --
1618 ---chek run result exist foe the element
1619 open c_asg;
1620 fetch c_asg into l_assignment_id, l_payroll_id ;
1621 close c_asg ;
1622
1623 open c_prv ;
1624 fetch c_prv into l_acty_base_rt_id,l_rt_strt_dt ;
1625 close c_prv ;
1626
1627
1628 hr_utility.set_location('checking for runresult:'||l_proc,5);
1629 g_max_end_date := null;
1630 open c_get_end_dt(p_payroll_id => l_payroll_id
1631 ,p_acty_base_rt_id => l_acty_base_rt_id
1632 ,p_business_group_id => p_business_group_id
1633 ,p_assignment_id => l_assignment_id
1634 ,p_effective_date => l_rt_strt_dt);
1635 fetch c_get_end_dt into g_max_end_date;
1636 close c_get_end_dt;
1637 if g_max_end_date is not null and (g_max_end_date +1) > l_rt_strt_dt then
1638 -- Issue a warning to the user. These will display on the enrt forms.
1639 fnd_message.set_name('BEN', 'BEN_92455_STRT_RUN_RESULTS');
1640 fnd_message.set_token('PARMA', l_rt_strt_dt);
1641 fnd_message.set_token('PARMB',g_max_end_date);
1642 fnd_message.raise_error;
1643
1644 end if;
1645 hr_utility.set_location('Leaving:' ||l_proc,5);
1646 end chk_future_dated ;
1647
1648 --
1649
1650
1651
1652
1653
1654
1655 -------------------------------------------------------------------------------
1656 -- |-------------------------< chk_incrd_dt >---------------------------------|
1657 -- ----------------------------------------------------------------------------
1658 --
1659 -- Description
1660 -- This procedure checks that incrd dt lies within plan year period
1661 -- and also within enrollment cvrg date range for the plan for the submitter.
1662 -- Pre-Conditions
1663 -- None.
1664 --
1665 -- In Parameters
1666 -- p_prtt_reimbmt_rqst_id
1667 -- p_submitter_person_id
1668 -- p_pl_id
1669 -- p_incrd_from_dt
1670 -- p_incrd_to_dt
1671 -- p_effective_date
1672 -- p_business_group_id
1673 -- p_object_version_number
1674 --
1675 -- Post Success
1676 -- Processing continues
1677 --
1678 -- Post Failure
1679 -- Error raised.
1680 --
1681 -- Access Status
1682 -- Internal table handler use only.
1683 --
1684 Procedure chk_incrd_dt(
1685 p_prtt_reimbmt_rqst_id in number,
1686 p_pl_id in number,
1687 p_submitter_person_id in number,
1688 p_incrd_from_dt in date,
1689 p_incrd_to_dt in date,
1690 p_effective_date in date,
1691 p_business_group_id in number,
1692 p_object_version_number in number,
1693 p_rqst_amt in number,
1694 p_prtt_reimbmt_rqst_stat_cd in out nocopy varchar2,
1695 p_stat_rsn_cd in out nocopy varchar2,
1696 p_exp_incurd_dt in date -- 2272862
1697 ) is
1698 --
1699 l_proc varchar2(72) := g_package||'chk_incrd_dt';
1700 l_api_updating boolean;
1701 --
1702 l_enrt_cvg_strt_dt date;
1703 l_enrt_cvg_thru_dt date;
1704 l_yrp_start_date date;
1705 l_yrp_end_date date;
1706 l_acpt_clm_rqsts_thru_dt date;
1707 l_py_clms_thru_dt date;
1708 --
1709 l_over_dated_gds_exists boolean := FALSE;
1710 l_tmp_str varchar2(2000);
1711 l_gds_date_str varchar2(2000);
1712 -- Coverage start date should lowest possible date with in year record
1713 -- cvg end date should be highest record end date with in year record
1714 -- This cursor is to check if the expense incurd dt is valid
1715 -- and to get other data to check if the incrd from and to dates are within the covrage period
1716 -- and to get acpt_clm_rqsts_thru_dt to check if the effective date is well within valid limits
1717 --
1718 --bug#4541750 - changed made to cursor to fetch details from multiple results
1719 --for the same plan year
1720 cursor c1 is
1721 select distinct
1722 pen_l.enrt_cvg_strt_dt enrt_cvg_strt_dt
1723 ,nvl(pen.enrt_cvg_thru_dt,pen_l.enrt_cvg_thru_dt) enrt_cvg_thru_dt
1724 ,yrp.start_date
1725 ,yrp.end_date
1726 ,nvl(cpy.acpt_clm_rqsts_thru_dt, pen.enrt_cvg_thru_dt) acpt_clm_rqsts_thru_dt
1727 ,nvl(cpy.PY_CLMS_THRU_DT, yrp.end_date) PY_CLMS_THRU_DT
1728 from ben_prtt_enrt_rslt_f pen,
1729 ben_prtt_enrt_rslt_f pen_l,
1730 ben_popl_yr_perd cpy,
1731 ben_yr_perd yrp
1732 where cpy.pl_id = p_pl_id
1733 and pen.pl_id = cpy.pl_id
1734 and pen.person_id = p_submitter_person_id
1735 and cpy.yr_perd_id = yrp.yr_perd_id
1736 and pen.prtt_enrt_rslt_stat_cd is null
1737 and pen_l.prtt_enrt_rslt_stat_cd is null
1738 and pen.effective_end_date = hr_api.g_eot
1739 and pen_l.effective_end_date = hr_api.g_eot
1740 and p_exp_incurd_dt >= yrp.start_date
1741 and p_exp_incurd_dt <= nvl(cpy.PY_CLMS_THRU_DT, yrp.end_date)
1742 and pen.pl_id = pen_l.pl_id
1743 and pen.person_id = pen_l.person_id
1744 and cpy.business_group_id = p_business_group_id
1745 and yrp.business_group_id = p_business_group_id
1746 and pen.business_group_id = p_business_group_id
1747 -- to find the highest possible record within the year #2469785
1748 --and pen.enrt_cvg_strt_dt <= yrp.end_date
1749 --and pen.enrt_cvg_thru_dt >= yrp.start_date
1750 --and pen_l.enrt_cvg_strt_dt <= yrp.end_date
1751 --and pen_l.enrt_cvg_thru_dt >= yrp.start_date
1752 --- effective date is not used to control
1753 --- there is poosibility of cvg may start
1754 --- much before effective date start
1755 --and pen.effective_start_date <= yrp.end_date
1756 --and pen.effective_end_date >= yrp.start_date
1757 --and pen_l.effective_start_date <= yrp.end_date
1758 --and pen_l.effective_end_date >= yrp.start_date
1759 ---
1760 and pen.prtt_enrt_rslt_id =
1761 (select max(pen2.prtt_enrt_rslt_id)
1762 from ben_prtt_enrt_rslt_f pen2
1763 where pen2.person_id = pen.person_id
1764 and pen2.pl_id = pen.pl_id
1765 and pen2.prtt_enrt_rslt_stat_cd is null
1766 and pen2.SSPNDD_FLAG = 'N'
1767 and pen2.enrt_cvg_strt_dt <= yrp.end_date
1768 and pen2.enrt_cvg_thru_dt >= yrp.start_date
1769 and pen2.effective_end_date = hr_api.g_eot
1770 )
1771 and pen_l.prtt_enrt_rslt_id =
1772 (select min(pen_l2.prtt_enrt_rslt_id)
1773 from ben_prtt_enrt_rslt_f pen_l2
1774 where pen_l2.person_id = pen_l.person_id
1775 and pen_l2.pl_id = pen_l.pl_id
1776 and pen_l2.SSPNDD_FLAG = 'N'
1777 and pen_l2.prtt_enrt_rslt_stat_cd is null
1778 and pen_l2.enrt_cvg_strt_dt <= yrp.end_date
1779 and pen_l2.enrt_cvg_thru_dt >= yrp.start_date
1780 and pen_l.effective_end_date = hr_api.g_eot
1781 )
1782 ;
1783 --
1784 -- This cursor will check if the incrd_to_dt is less than the earliest submission date for
1785 -- all the goods or services claimed as part of this reimbursement request.
1786 --
1787 cursor c2 is
1788 select gds.name,
1789 decode(pgs.GD_SVC_RECD_BASIS_CD, 'DATE', GD_SVC_RECD_BASIS_DT,
1790 decode(pgs.GD_SVC_RECD_BASIS_CD, 'MOINCRDT', add_months(p_exp_incurd_dt,pgs.GD_SVC_RECD_BASIS_MO),
1791 decode(pgs.GD_SVC_RECD_BASIS_CD, 'MOPLYRND', add_months(yrp.end_date,pgs.GD_SVC_RECD_BASIS_MO),yrp.end_date))) earliest_submit_date
1792 from ben_prtt_enrt_rslt_f pen,
1793 ben_popl_yr_perd cpy,
1794 ben_yr_perd yrp,
1795 ben_prtt_clm_gd_or_svc_typ pcg,
1796 ben_pl_gd_or_svc_f pgs,
1797 ben_gd_or_svc_typ gds
1798 where pcg.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
1799 and pcg.pl_gd_or_svc_id = pgs.pl_gd_or_svc_id
1800 and pgs.gd_or_svc_typ_id = gds.gd_or_svc_typ_id
1801 and cpy.pl_id = pgs.pl_id
1802 and pen.pl_id = cpy.pl_id
1803 and pen.person_id = p_submitter_person_id
1804 and cpy.yr_perd_id = yrp.yr_perd_id
1805 and pen.prtt_enrt_rslt_stat_cd is null
1806 and p_exp_incurd_dt >= yrp.start_date
1807 and p_exp_incurd_dt <= nvl(cpy.PY_CLMS_THRU_DT, yrp.end_date)
1808 and p_incrd_from_dt >= pen.enrt_cvg_strt_dt
1809 and p_incrd_to_dt <= pen.enrt_cvg_thru_dt
1810 and p_incrd_from_dt <= p_incrd_to_dt
1811 and p_incrd_from_dt >= yrp.start_date
1812 and p_incrd_to_dt > decode(pgs.GD_SVC_RECD_BASIS_CD, 'DATE', GD_SVC_RECD_BASIS_DT,
1813 decode(pgs.GD_SVC_RECD_BASIS_CD, 'MOINCRDT', add_months(p_exp_incurd_dt,pgs.GD_SVC_RECD_BASIS_MO),
1814 decode(pgs.GD_SVC_RECD_BASIS_CD, 'MOPLYRND', add_months(yrp.end_date,pgs.GD_SVC_RECD_BASIS_MO),yrp.end_date)))
1815 and p_effective_date between
1816 pen.effective_start_date and pen.effective_end_date
1817 and p_effective_date between
1818 pgs.effective_start_date and pgs.effective_end_date
1819 and cpy.business_group_id = p_business_group_id
1820 and yrp.business_group_id = p_business_group_id
1821 and pen.business_group_id = p_business_group_id
1822 and pcg.business_group_id = p_business_group_id
1823 and pgs.business_group_id = p_business_group_id
1824 and gds.business_group_id = p_business_group_id;
1825
1826
1827
1828 /* ************** bug 2272862
1829 -- This is to chek the claim happend on the year
1830 -- allow the futur date within the year
1831 cursor c1 is
1832 select nvl(cpy.acpt_clm_rqsts_thru_dt, pen.enrt_cvg_thru_dt)
1833 from ben_prtt_enrt_rslt_f pen,
1834 ben_popl_yr_perd cpy,
1835 ben_yr_perd yrp
1836 where cpy.pl_id = p_pl_id
1837 and pen.pl_id = p_pl_id
1838 and pen.person_id = p_submitter_person_id
1839 and cpy.yr_perd_id = yrp.yr_perd_id
1840 and pen.prtt_enrt_rslt_stat_cd is null
1841 and p_incrd_from_dt >= pen.enrt_cvg_strt_dt
1842 and p_incrd_to_dt <= pen.enrt_cvg_thru_dt
1843 and p_incrd_from_dt <= p_incrd_to_dt
1844 and p_incrd_from_dt >= yrp.start_date
1845 and p_incrd_to_dt <= yrp.end_date
1846 --and p_incrd_to_dt <= p_effective_date // allowing futur date with pndg status
1847 and p_effective_date between
1848 pen.effective_start_date and pen.effective_end_date
1849 --and p_effective_date >= yrp.start_date
1850 and cpy.business_group_id = p_business_group_id
1851 and yrp.business_group_id = p_business_group_id
1852 and pen.business_group_id = p_business_group_id;
1853 **************** */
1854 --
1855 --
1856 Begin
1857 --
1858 hr_utility.set_location('Entering:'||l_proc,5);
1859 --
1860 l_api_updating := ben_prc_shd.api_updating
1861 (p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id,
1862 p_effective_date => p_effective_date,
1863 p_object_version_number => p_object_version_number);
1864 --
1865 if ((l_api_updating
1866 and (nvl(p_incrd_from_dt, hr_api.g_date)
1867 <> nvl(ben_prc_shd.g_old_rec.incrd_from_dt, hr_api.g_date)
1868 or nvl(p_incrd_to_dt, hr_api.g_date)
1869 <> nvl(ben_prc_shd.g_old_rec.incrd_to_dt, hr_api.g_date)
1870 or nvl(p_exp_incurd_dt, hr_api.g_date)
1871 <> nvl(ben_prc_shd.g_old_rec.exp_incurd_dt, hr_api.g_date)
1872 or nvl(p_rqst_amt,0) <> nvl(ben_prc_shd.g_old_rec.rqst_amt,0)
1873 or nvl(p_prtt_reimbmt_rqst_stat_cd ,'-1')<>
1874 nvl(ben_prc_shd.g_old_rec.prtt_reimbmt_rqst_stat_cd ,'-1')
1875 or nvl(p_pl_id,-1) <> nvl(ben_prc_shd.g_old_rec.pl_id,-1) ))
1876 or not l_api_updating) then
1877
1878 hr_utility.set_location('validating'||l_proc,5);
1879
1880
1881 /* ************** bug 2272862
1882 --
1883 --
1884 -- check incrd_to_dt is not greater than effective date
1885 if p_incrd_to_dt > p_effective_date then
1886 --
1887 --fnd_message.set_name('BEN', 'BEN_92689_TO_DATE_EFF_DATE');
1888 --fnd_message.raise_error;
1889
1890 p_prtt_reimbmt_rqst_stat_cd := 'PNDNG' ;
1891 p_stat_rsn_cd := 'RMBFUTRDT' ;
1892
1893 --
1894 end if;
1895 **************** */
1896
1897 for i in c1 Loop
1898
1899 hr_utility.set_location( ' from st dt ' || i.enrt_cvg_strt_dt, 90) ;
1900 hr_utility.set_location( ' from en dt ' || i.enrt_cvg_thru_dt, 90) ;
1901 if l_enrt_cvg_strt_dt is null then
1902 l_enrt_cvg_strt_dt := i.enrt_cvg_strt_dt ;
1903 else
1904 l_enrt_cvg_strt_dt := least(i.enrt_cvg_strt_dt,l_enrt_cvg_strt_dt) ;
1905 end if ;
1906
1907 if l_enrt_cvg_thru_dt is null then
1908 l_enrt_cvg_thru_dt := i.enrt_cvg_thru_dt ;
1909 else
1910 l_enrt_cvg_thru_dt := greatest(i.enrt_cvg_thru_dt,l_enrt_cvg_thru_dt) ;
1911 end if ;
1912
1913 hr_utility.set_location( ' from st dt ' || l_enrt_cvg_strt_dt, 99) ;
1914 hr_utility.set_location( ' from en dt ' || l_enrt_cvg_thru_dt, 99) ;
1915 l_yrp_start_date := i.start_date ;
1916 l_yrp_end_date := i.end_date ;
1917 l_acpt_clm_rqsts_thru_dt := i.acpt_clm_rqsts_thru_dt ;
1918 l_py_clms_thru_dt := i.PY_CLMS_THRU_DT ;
1919 end Loop ;
1920
1921 if l_enrt_cvg_strt_dt is null or l_enrt_cvg_thru_dt is null then
1922 --
1923 fnd_message.set_name('PAY', 'HR_52965_COL_RANGE');
1924 fnd_message.set_token('COLUMN','Expense Incurred Date', TRUE);
1925 fnd_message.set_token('MINIMUM','Plan Year Start Date', TRUE);
1926 fnd_message.set_token('MAXIMUM','Expense Must Be Incurred On Or Before Date', TRUE);
1927 fnd_message.raise_error;
1928 --
1929 else
1930 --
1931 --
1932 if p_effective_date < l_yrp_start_date then
1933 --
1934 -- bug fix 2509297 - message change
1935 --
1936 -- fnd_message.set_name('PAY', 'HR_52965_COL_RANGE');
1937 -- fnd_message.set_token('COLUMN','Effective Date', TRUE);
1938 -- fnd_message.set_token('MINIMUM','Plan Year Start Date', TRUE);
1939 -- fnd_message.set_token('MAXIMUM','Request Must Be Received On Or Before Date', TRUE);
1940 --
1941 fnd_message.set_name('BEN', 'BEN_93189_EFFDT_NB_PLYR_RQRCV');
1942 --
1943 -- end fix 2509297
1944 --
1945 fnd_message.raise_error;
1946 --
1947 end if;
1948 --
1949 hr_utility.set_location(' p_effective_date ' || p_effective_date , 98 );
1950 hr_utility.set_location(' l_acpt_clm_rqsts_thru_dt ' || l_acpt_clm_rqsts_thru_dt , 98 );
1951 if p_effective_date > l_acpt_clm_rqsts_thru_dt then
1952 --
1953 -- No claim to be accepted beyond acpt_clm_rqsts_thru_dt.
1954 -- If acpt_clm_rqsts_thru_dt not specified, use enrt_cvg_thru_dt
1955 -- Effective beyond the date, so raise error.
1956 --
1957 fnd_message.set_name('BEN', 'BEN_92499_CLM_AFTR_ALWD_DT');
1958 fnd_message.set_token('RQST_THRU_DT', to_char(l_acpt_clm_rqsts_thru_dt,'DD-MON-RRRR'));
1959 fnd_message.set_token('EFFECTIVE_DATE', to_char(p_effective_date,'DD-MON-RRRR'));
1960 fnd_message.raise_error;
1961 --
1962 end if;
1963 --
1964 hr_utility.set_location(' p_incrd_from_dt ' || p_incrd_from_dt , 98 );
1965 hr_utility.set_location(' p_incrd_to_dt ' || p_incrd_to_dt , 98 );
1966 hr_utility.set_location(' l_enrt_cvg_strt_dt ' || l_enrt_cvg_strt_dt , 98 );
1967 hr_utility.set_location(' l_enrt_cvg_thru_dt ' || l_enrt_cvg_thru_dt , 98 );
1968 hr_utility.set_location(' l_yrp_start_date ' || l_yrp_start_date , 98 );
1969 hr_utility.set_location(' l_yrp_end_date ' || l_yrp_end_date , 98 );
1970 if not (p_incrd_from_dt >= l_enrt_cvg_strt_dt
1971 -- if a person last coverage beore the yr end allow him up to the year end
1972 and p_incrd_to_dt <= greatest(l_enrt_cvg_thru_dt,l_yrp_end_date)
1973 and p_incrd_from_dt <= p_incrd_to_dt
1974 and p_incrd_from_dt >= l_yrp_start_date ) then
1975 --
1976 -- The service "from" and "to" date is beyond the coverage range.
1977 -- So, raise error.
1978 --
1979 fnd_message.set_name('BEN', 'BEN_92498_RQST_BYND_CVG_DT');
1980 fnd_message.raise_error;
1981 --
1982 end if;
1983 --
1984 end if;
1985 --
1986
1987 if p_prtt_reimbmt_rqst_stat_cd in ('APPRVD','PDINFL','PRTLYPD') then
1988 --
1989 -- Check for presence of claimed goods or services whose earliest submit date
1990 -- is less than the claim date (incrd_to_dt)
1991 --
1992 for l_c2 in c2 loop
1993 --
1994 -- bug fix 2508246 -- added date formatting to l_c2.earliest_submit_date
1995 -- for showing in DD-MON-RRRR
1996 if not l_over_dated_gds_exists then
1997 l_tmp_str := l_c2.name || ' - ' || to_char(l_c2.earliest_submit_date, 'DD-MON-RRRR');
1998 else
1999 l_tmp_str := l_gds_date_str || ', ' || l_c2.name || ' - ' || to_char(l_c2.earliest_submit_date, 'DD-MON-RRRR');
2000 end if;
2001 --
2002 l_over_dated_gds_exists := TRUE;
2003 --
2004 if length(l_tmp_str) > 1700 then
2005 --commit;
2006 exit;
2007 else
2008 l_gds_date_str := l_tmp_str;
2009 end if;
2010 --
2011 end loop;
2012 --
2013 /*
2014 if l_over_dated_gds_exists then
2015 --
2016 fnd_message.set_name('BEN', 'BEN_93106_CLM_GD_SBMTDT_XCEED');
2017 fnd_message.set_token('GDS_DATE', l_gds_date_str);
2018 fnd_message.raise_error;
2019 --
2020 end if;
2021 --
2022 */
2023 end if;
2024 end if;
2025 --
2026 hr_utility.set_location('Leaving:'||l_proc,10);
2027 --
2028 End chk_incrd_dt;
2029 --
2030 --
2031 -- ----------------------------------------------------------------------------
2032 -- |--------------------------< dt_update_validate >--------------------------|
2033 -- ----------------------------------------------------------------------------
2034 -- {Start Of Comments}
2035 --
2036 -- Description:
2037 -- This procedure is used for referential integrity of datetracked
2038 -- parent entities when a datetrack update operation is taking place
2039 -- and where there is no cascading of update defined for this entity.
2040 --
2041 -- Prerequisites:
2042 -- This procedure is called from the update_validate.
2043 --
2044 -- In Parameters:
2045 --
2046 -- Post Success:
2047 -- Processing continues.
2048 --
2049 -- Post Failure:
2050 --
2051 -- Developer Implementation Notes:
2052 -- This procedure should not need maintenance unless the HR Schema model
2053 -- changes.
2054 --
2055 -- Access Status:
2056 -- Internal Row Handler Use Only.
2057 --
2058 -- {End Of Comments}
2059 -- ----------------------------------------------------------------------------
2060 Procedure dt_update_validate
2061 (p_pl_id in number ,
2062 p_datetrack_mode in varchar2,
2063 p_validation_start_date in date,
2064 p_validation_end_date in date) Is
2065 --
2066 l_proc varchar2(72) := g_package||'dt_update_validate';
2067 l_integrity_error Exception;
2068 l_table_name all_tables.table_name%TYPE;
2069 --
2070 Begin
2071 hr_utility.set_location('Entering:'||l_proc, 5);
2072 --
2073 -- Ensure that the p_datetrack_mode argument is not null
2074 --
2075 hr_api.mandatory_arg_error
2076 (p_api_name => l_proc,
2077 p_argument => 'datetrack_mode',
2078 p_argument_value => p_datetrack_mode);
2079 --
2080 -- Only perform the validation if the datetrack update mode is valid
2081 --
2082 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
2083 --
2084 --
2085 -- Ensure the arguments are not null
2086 --
2087 hr_api.mandatory_arg_error
2088 (p_api_name => l_proc,
2089 p_argument => 'validation_start_date',
2090 p_argument_value => p_validation_start_date);
2091 --
2092 hr_api.mandatory_arg_error
2093 (p_api_name => l_proc,
2094 p_argument => 'validation_end_date',
2095 p_argument_value => p_validation_end_date);
2096 --
2097 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
2098 NOT (dt_api.check_min_max_dates
2099 (p_base_table_name => 'ben_pl_f',
2100 p_base_key_column => 'pl_id',
2101 p_base_key_value => p_pl_id,
2102 p_from_date => p_validation_start_date,
2103 p_to_date => p_validation_end_date))) Then
2104 l_table_name := 'ben_pl_f';
2105 Raise l_integrity_error;
2106 End If;
2107 --
2108 End If;
2109 --
2110 hr_utility.set_location(' Leaving:'||l_proc, 10);
2111 Exception
2112 When l_integrity_error Then
2113 --
2114 -- A referential integrity check was violated therefore
2115 -- we must error
2116 --
2117 ben_utility.parent_integrity_error(p_table_name => l_table_name);
2118 When Others Then
2119 --
2120 -- An unhandled or unexpected error has occurred which
2121 -- we must report
2122 --
2123 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2124 fnd_message.set_token('PROCEDURE', l_proc);
2125 fnd_message.set_token('STEP','15');
2126 fnd_message.raise_error;
2127 End dt_update_validate;
2128 -- ----------------------------------------------------------------------------
2129 -- |--------------------------< prv_rows_exists >--------------------------|
2130 -- ----------------------------------------------------------------------------
2131
2132 Function prv_rows_exists (p_prtt_reimbmt_rqst_id in number ) Return Boolean is
2133
2134 l_proc varchar2(72) := g_package||'prv_rows_exists';
2135 l_dummy varchar2(1);
2136
2137 cursor c1 is
2138 select null
2139 from ben_prtt_rt_val a
2140 where a.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id;
2141
2142 Begin
2143 hr_utility.set_location('Entering:'||l_proc,5);
2144 --
2145 -- check if child rows exists in ben_prtt_rt_val.
2146 --
2147 open c1;
2148 fetch c1 into l_dummy;
2149 if c1%found then
2150
2151 close c1;
2152 --
2153 -- raise error as child rows exists.
2154 --
2155 Return(true);
2156 Else
2157 close c1;
2158 Return(false);
2159 end if;
2160 hr_utility.set_location('Leaving:'||l_proc,10);
2161 End prv_rows_exists;
2162
2163 --
2164 -- ----------------------------------------------------------------------------
2165 -- |--------------------------< dt_delete_validate >--------------------------|
2166 -- ----------------------------------------------------------------------------
2167 -- {Start Of Comments}
2168 --
2169 -- Description:
2170 -- This procedure is used for referential integrity of datetracked
2171 -- child entities when either a datetrack DELETE or ZAP is in operation
2172 -- and where there is no cascading of delete defined for this entity.
2173 -- For the datetrack mode of DELETE or ZAP we must ensure that no
2174 -- datetracked child rows exist between the validation start and end
2175 -- dates.
2176 --
2177 -- Prerequisites:
2178 -- This procedure is called from the delete_validate.
2179 --
2180 -- In Parameters:
2181 --
2182 -- Post Success:
2183 -- Processing continues.
2184 --
2185 -- Post Failure:
2186 -- If a row exists by determining the returning Boolean value from the
2187 -- generic dt_api.rows_exist function then we must supply an error via
2188 -- the use of the local exception handler l_rows_exist.
2189 --
2190 -- Developer Implementation Notes:
2191 -- This procedure should not need maintenance unless the HR Schema model
2192 -- changes.
2193 --
2194 -- Access Status:
2195 -- Internal Row Handler Use Only.
2196 --
2197 -- {End Of Comments}
2198 -- ----------------------------------------------------------------------------
2199 Procedure dt_delete_validate
2200 (p_prtt_reimbmt_rqst_id in number,
2201 p_datetrack_mode in varchar2,
2202 p_validation_start_date in date,
2203 p_validation_end_date in date) Is
2204 --
2205 l_proc varchar2(72) := g_package||'dt_delete_validate';
2206 l_rows_exist Exception;
2207 l_table_name all_tables.table_name%TYPE;
2208 --
2209 Begin
2210 hr_utility.set_location('Entering:'||l_proc, 5);
2211 --
2212 -- Ensure that the p_datetrack_mode argument is not null
2213 --
2214 hr_api.mandatory_arg_error
2215 (p_api_name => l_proc,
2216 p_argument => 'datetrack_mode',
2217 p_argument_value => p_datetrack_mode);
2218 --
2219 -- Only perform the validation if the datetrack mode is either
2220 -- DELETE or ZAP
2221 --
2222 If (p_datetrack_mode = 'DELETE' or
2223 p_datetrack_mode = 'ZAP') then
2224 --
2225 --
2226 -- Ensure the arguments are not null
2227 --
2228 hr_api.mandatory_arg_error
2229 (p_api_name => l_proc,
2230 p_argument => 'validation_start_date',
2231 p_argument_value => p_validation_start_date);
2232 --
2233 hr_api.mandatory_arg_error
2234 (p_api_name => l_proc,
2235 p_argument => 'validation_end_date',
2236 p_argument_value => p_validation_end_date);
2237 --
2238 hr_api.mandatory_arg_error
2239 (p_api_name => l_proc,
2240 p_argument => 'prtt_reimbmt_rqst_id',
2241 p_argument_value => p_prtt_reimbmt_rqst_id);
2242 --
2243 If (prv_rows_exists(p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id)) Then
2244 l_table_name := 'ben_prtt_rt_val';
2245 Raise l_rows_exist;
2246 End If;
2247 End If;
2248 --
2249 hr_utility.set_location(' Leaving:'||l_proc, 10);
2250 Exception
2251 When l_rows_exist Then
2252 --
2253 -- A referential integrity check was violated therefore we must error
2254 ben_utility.child_exists_error(p_table_name => l_table_name);
2255 When Others Then
2256 --
2257 -- An unhandled or unexpected error has occurred which
2258 -- we must report
2259 --
2260 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2261 fnd_message.set_token('PROCEDURE', l_proc);
2262 fnd_message.set_token('STEP','15');
2263 fnd_message.raise_error;
2264 End dt_delete_validate;
2265 --
2266 -- ----------------------------------------------------------------------------
2267 -- |---------------------------< insert_validate >----------------------------|
2268 -- ----------------------------------------------------------------------------
2269 Procedure insert_validate
2270 (p_rec in out nocopy ben_prc_shd.g_rec_type,
2271 p_effective_date in date,
2272 p_datetrack_mode in varchar2,
2273 p_validation_start_date in date,
2274 p_validation_end_date in date) is
2275 --
2276 l_proc varchar2(72) := g_package||'insert_validate';
2277 --
2278 Begin
2279 hr_utility.set_location('Entering:'||l_proc, 5);
2280 --
2281 -- Call all supporting business operations
2282 --
2283 --
2284 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2285 --
2286 chk_prtt_reimbmt_rqst_id
2287 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2288 p_effective_date => p_effective_date,
2289 p_object_version_number => p_rec.object_version_number);
2290 --
2291 chk_gd_or_svc_typ_id
2292 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2293 p_gd_or_svc_typ_id => p_rec.gd_or_svc_typ_id,
2294 p_effective_date => p_effective_date,
2295 p_object_version_number => p_rec.object_version_number);
2296 --
2297 chk_provider_person_id
2298 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2299 p_provider_person_id => p_rec.provider_person_id,
2300 p_effective_date => p_effective_date,
2301 p_object_version_number => p_rec.object_version_number);
2302 --
2303 chk_rcrrg_cd
2304 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2305 p_rcrrg_cd => p_rec.rcrrg_cd,
2306 p_effective_date => p_effective_date,
2307 p_object_version_number => p_rec.object_version_number);
2308 --
2309 chk_reimbmt_ctfn_typ_prvdd_cd
2310 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2311 p_reimbmt_ctfn_typ_prvdd_cd => p_rec.reimbmt_ctfn_typ_prvdd_cd,
2312 p_effective_date => p_effective_date,
2313 p_object_version_number => p_rec.object_version_number);
2314 --
2315 chk_prtt_reimbmt_rqst_stat_cd
2316 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2317 p_prtt_reimbmt_rqst_stat_cd => p_rec.prtt_reimbmt_rqst_stat_cd,
2318 p_effective_date => p_effective_date,
2319 p_object_version_number => p_rec.object_version_number);
2320 --
2321 chk_stat_rsn_cd
2322 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2323 p_stat_rsn_cd => p_rec.stat_rsn_cd,
2324 p_effective_date => p_effective_date,
2325 p_object_version_number => p_rec.object_version_number);
2326 --
2327 chk_pymt_stat_rsn_cd
2328 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2329 p_pymt_stat_rsn_cd => p_rec.pymt_stat_rsn_cd,
2330 p_effective_date => p_effective_date,
2331 p_object_version_number => p_rec.object_version_number);
2332 --
2333 --
2334 chk_pymt_stat_cd
2335 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2336 p_pymt_stat_cd => p_rec.pymt_stat_cd,
2337 p_effective_date => p_effective_date,
2338 p_object_version_number => p_rec.object_version_number);
2339
2340 chk_prtt_reimbmt_stat_apprvd
2341 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2342 p_aprvd_for_pymt_amt => p_rec.aprvd_for_pymt_amt ,
2343 p_prtt_reimbmt_rqst_stat_cd => p_rec.prtt_reimbmt_rqst_stat_cd ,
2344 p_stat_rsn_cd => p_rec.stat_rsn_cd ,
2345 p_effective_date => p_effective_date
2346 ) ;
2347
2348 chk_incrd_dt
2349 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2350 p_pl_id => p_rec.pl_id,
2351 p_submitter_person_id => p_rec.submitter_person_id,
2352 p_incrd_from_dt => p_rec.incrd_from_dt,
2353 p_incrd_to_dt => p_rec.incrd_to_dt,
2354 p_effective_date => p_effective_date,
2355 p_business_group_id => p_rec.business_group_id,
2356 p_object_version_number => p_rec.object_version_number,
2357 p_rqst_amt => p_rec.rqst_amt ,
2358 p_prtt_reimbmt_rqst_stat_cd => p_rec.prtt_reimbmt_rqst_stat_cd ,
2359 p_stat_rsn_cd => p_rec.stat_rsn_cd,
2360 p_exp_incurd_dt => p_rec.exp_incurd_dt -- 2272862
2361 );
2362
2363
2364 --
2365 chk_pl_id_rqst_amt_and_uom
2366 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2367 p_rqst_amt_uom => p_rec.rqst_amt_uom,
2368 p_rqst_amt => p_rec.rqst_amt,
2369 p_pl_id => p_rec.pl_id,
2370 p_submitter_person_id => p_rec.submitter_person_id,
2371 p_business_group_id => p_rec.business_group_id,
2372 p_effective_date => p_effective_date,
2373 p_object_version_number => p_rec.object_version_number,
2374 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id ,
2375 p_prtt_reimbmt_rqst_stat_cd=> p_rec.prtt_reimbmt_rqst_stat_cd,
2376 p_stat_rsn_cd => p_rec.stat_rsn_cd ,
2377 p_pymt_stat_cd => p_rec.pymt_stat_cd,
2378 p_pymt_stat_rsn_cd => p_rec.pymt_stat_rsn_cd,
2379 p_pymt_amount => p_rec.pymt_amount,
2380 p_aprvd_for_pymt_amt => p_rec.aprvd_for_pymt_amt,
2381 p_exp_incurd_dt => p_rec.exp_incurd_dt,
2382 p_popl_yr_perd_id_1 => p_rec.popl_yr_perd_id_1,
2383 p_popl_yr_perd_id_2 => p_rec.popl_yr_perd_id_2,
2384 p_amt_1 => p_rec.amt_year1,
2385 p_amt_2 => p_rec.amt_year2 );
2386
2387 hr_utility.set_location('after stat check ' || p_rec.prtt_reimbmt_rqst_stat_cd, 110);
2388 hr_utility.set_location('after stat check ' || p_rec.stat_rsn_cd, 110);
2389 hr_utility.set_location('after stat check ' || p_rec.Pymt_stat_cd, 110);
2390 hr_utility.set_location('after stat check ' || p_rec.pymt_stat_rsn_cd, 110);
2391
2392
2393 --
2394 hr_utility.set_location(' Leaving:'||l_proc, 10);
2395 End insert_validate;
2396 --
2397 -- ----------------------------------------------------------------------------
2398 -- |---------------------------< update_validate >----------------------------|
2399 -- ----------------------------------------------------------------------------
2400 Procedure update_validate
2401 (p_rec in out nocopy ben_prc_shd.g_rec_type,
2402 p_effective_date in date,
2403 p_datetrack_mode in varchar2,
2404 p_validation_start_date in date,
2405 p_validation_end_date in date) is
2406 --
2407 l_proc varchar2(72) := g_package||'update_validate';
2408 --
2409 Begin
2410 hr_utility.set_location('Entering:'||l_proc, 5);
2411 --
2412 -- Call all supporting business operations
2413 --
2414 --
2415
2416 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2417 --
2418 chk_prtt_reimbmt_rqst_id
2419 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2420 p_effective_date => p_effective_date,
2421 p_object_version_number => p_rec.object_version_number);
2422 --
2423 chk_gd_or_svc_typ_id
2424 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2425 p_gd_or_svc_typ_id => p_rec.gd_or_svc_typ_id,
2426 p_effective_date => p_effective_date,
2427 p_object_version_number => p_rec.object_version_number);
2428 --
2429 chk_provider_person_id
2430 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2431 p_provider_person_id => p_rec.provider_person_id,
2432 p_effective_date => p_effective_date,
2433 p_object_version_number => p_rec.object_version_number);
2434 --
2435 chk_rcrrg_cd
2436 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2437 p_rcrrg_cd => p_rec.rcrrg_cd,
2438 p_effective_date => p_effective_date,
2439 p_object_version_number => p_rec.object_version_number);
2440 --
2441 chk_reimbmt_ctfn_typ_prvdd_cd
2442 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2443 p_reimbmt_ctfn_typ_prvdd_cd => p_rec.reimbmt_ctfn_typ_prvdd_cd,
2444 p_effective_date => p_effective_date,
2445 p_object_version_number => p_rec.object_version_number);
2446 --
2447 chk_prtt_reimbmt_rqst_stat_cd
2448 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2449 p_prtt_reimbmt_rqst_stat_cd => p_rec.prtt_reimbmt_rqst_stat_cd,
2450 p_effective_date => p_effective_date,
2451 p_object_version_number => p_rec.object_version_number);
2452 --
2453 chk_stat_rsn_cd
2454 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2455 p_stat_rsn_cd => p_rec.stat_rsn_cd,
2456 p_effective_date => p_effective_date,
2457 p_object_version_number => p_rec.object_version_number);
2458 --
2459 chk_pymt_stat_rsn_cd
2460 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2461 p_pymt_stat_rsn_cd => p_rec.pymt_stat_rsn_cd,
2462 p_effective_date => p_effective_date,
2463 p_object_version_number => p_rec.object_version_number);
2464 --
2465 chk_pymt_stat_cd
2466 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2467 p_pymt_stat_cd => p_rec.pymt_stat_cd,
2468 p_effective_date => p_effective_date,
2469 p_object_version_number => p_rec.object_version_number);
2470
2471 chk_incrd_dt
2472 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2473 p_pl_id => p_rec.pl_id,
2474 p_submitter_person_id => p_rec.submitter_person_id,
2475 p_incrd_from_dt => p_rec.incrd_from_dt,
2476 p_incrd_to_dt => p_rec.incrd_to_dt,
2477 p_effective_date => p_effective_date,
2478 p_business_group_id => p_rec.business_group_id,
2479 p_object_version_number => p_rec.object_version_number,
2480 p_rqst_amt => p_rec.rqst_amt ,
2481 p_prtt_reimbmt_rqst_stat_cd => p_rec.prtt_reimbmt_rqst_stat_cd ,
2482 p_stat_rsn_cd => p_rec.stat_rsn_cd,
2483 p_exp_incurd_dt => p_rec.exp_incurd_dt -- 2272862
2484 );
2485
2486 hr_utility.set_location('after date check ' || p_rec.stat_rsn_cd, 110);
2487
2488 --
2489 chk_pl_id_rqst_amt_and_uom
2490 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2491 p_rqst_amt_uom => p_rec.rqst_amt_uom,
2492 p_rqst_amt => p_rec.rqst_amt,
2493 p_pl_id => p_rec.pl_id,
2494 p_submitter_person_id => p_rec.submitter_person_id,
2495 p_business_group_id => p_rec.business_group_id,
2496 p_effective_date => p_effective_date,
2497 p_object_version_number => p_rec.object_version_number,
2498 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2499 p_prtt_reimbmt_rqst_stat_cd=> p_rec.prtt_reimbmt_rqst_stat_cd,
2500 p_stat_rsn_cd => p_rec.stat_rsn_cd,
2501 p_pymt_stat_cd => p_rec.pymt_stat_cd,
2502 p_pymt_stat_rsn_cd => p_rec.pymt_stat_rsn_cd,
2503 p_pymt_amount => p_rec.pymt_amount,
2504 p_aprvd_for_pymt_amt => p_rec.aprvd_for_pymt_amt,
2505 p_exp_incurd_dt => p_rec.exp_incurd_dt,
2506 p_popl_yr_perd_id_1 => p_rec.popl_yr_perd_id_1,
2507 p_popl_yr_perd_id_2 => p_rec.popl_yr_perd_id_2,
2508 p_amt_1 => p_rec.amt_year1,
2509 p_amt_2 => p_rec.amt_year2 );
2510
2511
2512 hr_utility.set_location('after uom check ' || p_rec.stat_rsn_cd, 110);
2513 chk_prtt_reimbmt_stat_apprvd
2514 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2515 p_aprvd_for_pymt_amt => p_rec.aprvd_for_pymt_amt ,
2516 p_prtt_reimbmt_rqst_stat_cd => p_rec.prtt_reimbmt_rqst_stat_cd ,
2517 p_stat_rsn_cd => p_rec.stat_rsn_cd ,
2518 p_effective_date => p_effective_date
2519 ) ;
2520
2521
2522 hr_utility.set_location('after stat check ' || p_rec.prtt_reimbmt_rqst_stat_cd, 110);
2523 hr_utility.set_location('after sta check ' || p_rec.stat_rsn_cd, 110);
2524 hr_utility.set_location('after stat check ' || p_rec.Pymt_stat_cd, 110);
2525 hr_utility.set_location('after stat check ' || p_rec.pymt_stat_rsn_cd, 110);
2526
2527
2528
2529
2530 chk_future_dated(
2531 p_pl_id => p_rec.pl_id ,
2532 p_submitter_person_id => p_rec.submitter_person_id,
2533 p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2534 p_business_group_id => p_rec.business_group_id,
2535 p_effective_date => p_effective_date );
2536
2537 --
2538 -- Call the datetrack update integrity operation
2539 --
2540 dt_update_validate
2541 (p_pl_id => p_rec.pl_id,
2542 p_datetrack_mode => p_datetrack_mode,
2543 p_validation_start_date => p_validation_start_date,
2544 p_validation_end_date => p_validation_end_date);
2545 --
2546 hr_utility.set_location(' Leaving:'||l_proc, 10);
2547 End update_validate;
2548 --
2549 -- ----------------------------------------------------------------------------
2550 -- |---------------------------< delete_validate >----------------------------|
2551 -- ----------------------------------------------------------------------------
2552 Procedure delete_validate
2553 (p_rec in ben_prc_shd.g_rec_type,
2554 p_effective_date in date,
2555 p_datetrack_mode in varchar2,
2556 p_validation_start_date in date,
2557 p_validation_end_date in date) is
2558 --
2559 l_proc varchar2(72) := g_package||'delete_validate';
2560 --
2561 Begin
2562 hr_utility.set_location('Entering:'||l_proc, 5);
2563 --
2564 -- Call all supporting business operations
2565 --
2566 chk_remb_status (
2567 p_prtt_reimbmt_rqst_stat_cd => ben_prc_shd.g_old_rec.prtt_reimbmt_rqst_stat_cd );
2568
2569 chk_future_dated(
2570 p_pl_id => ben_prc_shd.g_old_rec.pl_id ,
2571 p_submitter_person_id => ben_prc_shd.g_old_rec.submitter_person_id,
2572 p_prtt_reimbmt_rqst_id => ben_prc_shd.g_old_rec.prtt_reimbmt_rqst_id,
2573 p_business_group_id => ben_prc_shd.g_old_rec.business_group_id,
2574 p_effective_date => p_effective_date );
2575
2576
2577 dt_delete_validate
2578 (p_datetrack_mode => p_datetrack_mode,
2579 p_validation_start_date => p_validation_start_date,
2580 p_validation_end_date => p_validation_end_date,
2581 p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id);
2582 --
2583 hr_utility.set_location(' Leaving:'||l_proc, 10);
2584 End delete_validate;
2585 --
2586 --
2587 -- ---------------------------------------------------------------------------
2588 -- |---------------------< return_legislation_code >-------------------------|
2589 -- ---------------------------------------------------------------------------
2590 --
2591 function return_legislation_code
2592 (p_prtt_reimbmt_rqst_id in number) return varchar2 is
2593 --
2594 -- Declare cursor
2595 --
2596 cursor csr_leg_code is
2597 select a.legislation_code
2598 from per_business_groups a,
2599 ben_prtt_reimbmt_rqst_f b
2600 where b.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
2601 and a.business_group_id = b.business_group_id;
2602 --
2603 -- Declare local variables
2604 --
2605 l_legislation_code per_business_groups.legislation_code%TYPE; --UTF8 varchar2(150);
2606 l_proc varchar2(72) := g_package||'return_legislation_code';
2607 --
2608 begin
2609 --
2610 hr_utility.set_location('Entering:'|| l_proc, 10);
2611 --
2612 -- Ensure that all the mandatory parameter are not null
2613 --
2614 hr_api.mandatory_arg_error(p_api_name => l_proc,
2615 p_argument => 'prtt_reimbmt_rqst_id',
2616 p_argument_value => p_prtt_reimbmt_rqst_id);
2617 --
2618 open csr_leg_code;
2619 --
2620 fetch csr_leg_code into l_legislation_code;
2621 --
2622 if csr_leg_code%notfound then
2623 --
2624 close csr_leg_code;
2625 --
2626 -- The primary key is invalid therefore we must error
2627 --
2628 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
2629 fnd_message.raise_error;
2630 --
2631 end if;
2632 --
2633 close csr_leg_code;
2634 --
2635 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2636 --
2637 return l_legislation_code;
2638 --
2639 end return_legislation_code;
2640 --
2641 end ben_prc_bus;