1 Package Body ben_ppr_bus as
2 /* $Header: bepprrhi.pkb 120.1 2008/02/05 09:49:35 rtagarra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
8 g_package varchar2(33) := ' ben_ppr_bus.'; -- Global package name
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_prmry_care_prvdr_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 -- prmry_care_prvdr_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_prmry_care_prvdr_id(p_prmry_care_prvdr_id in number,
38 p_effective_date in date,
39 p_object_version_number in number) is
40 --
41 l_proc varchar2(72) := g_package||'chk_prmry_care_prvdr_id';
42 l_api_updating boolean;
43 --
44 Begin
45 --
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 --
48 l_api_updating := ben_ppr_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_prmry_care_prvdr_id => p_prmry_care_prvdr_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_prmry_care_prvdr_id,hr_api.g_number)
55 <> ben_ppr_shd.g_old_rec.prmry_care_prvdr_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_ppr_shd.constraint_error('BEN_PRMRY_CARE_PRVDR_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_prmry_care_prvdr_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_ppr_shd.constraint_error('BEN_PRMRY_CARE_PRVDR_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_prmry_care_prvdr_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_pcp_name_spclty_not_null >------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that the Provider Name and Speciality
85 -- are not null
86 --
87 -- Pre Conditions
88 -- None.
89 --
90 -- In Parameters
91 -- name Provider Name
92 -- prmry_care_prvdr_typ_cd Value of lookup code.
93 --
94 -- Post Success
95 -- Processing continues
96 --
97 -- Post Failure
98 -- Error handled by procedure
99 --
100 -- Access Status
101 -- Internal table handler use only.
102 --
103 procedure chk_pcp_name_spclty_not_null(p_name in varchar2,
104 p_prmry_care_prvdr_typ_cd in varchar2) is
105 l_proc varchar2(72) := g_package||'chk_pcp_name_spclty_not_null';
106 --
107 begin
108 --
109 hr_utility.set_location('Entering:'||l_proc, 5);
110 --
111 if p_name is null or p_prmry_care_prvdr_typ_cd is null then
112 hr_utility.set_location('Error Provider Name or Speciality is null',99);
113 fnd_message.set_name('BEN','BEN_94126_PCP_NAME_SPL_REQD');
114 fnd_message.raise_error;
115 end if;
116 --
117 hr_utility.set_location('Leaving:'||l_proc,10);
118 --
119 end chk_pcp_name_spclty_not_null;
120
121 -- ----------------------------------------------------------------------------
122 -- |------< chk_prmry_care_prvdr_typ_cd >------|
123 -- ----------------------------------------------------------------------------
124 --
125 -- Description
126 -- This procedure is used to check that the lookup value is valid.
127 --
128 -- Pre Conditions
129 -- None.
130 --
131 -- In Parameters
132 -- prmry_care_prvdr_id PK of record being inserted or updated.
133 -- prmry_care_prvdr_typ_cd Value of lookup code.
134 -- effective_date effective date
135 -- object_version_number Object version number of record being
136 -- inserted or updated.
137 --
138 -- Post Success
139 -- Processing continues
140 --
141 -- Post Failure
142 -- Error handled by procedure
143 --
144 -- Access Status
145 -- Internal table handler use only.
146 --
147 procedure chk_prmry_care_prvdr_typ_cd(p_prmry_care_prvdr_id in number,
148 p_prmry_care_prvdr_typ_cd in varchar2,
149 p_prtt_enrt_rslt_id in number,
150 p_effective_date in date,
151 p_object_version_number in number) is
152 l_proc varchar2(72) := g_package||'chk_prmry_care_prvdr_typ_cd';
153 l_api_updating boolean;
154 l_pcp_rpstry_flag varchar2(1);
158 hr_utility.set_location('Entering:'||l_proc, 5);
155 --
156 Begin
157 --
159 --
160 l_api_updating := ben_ppr_shd.api_updating
161 (p_prmry_care_prvdr_id => p_prmry_care_prvdr_id,
162 p_effective_date => p_effective_date,
163 p_object_version_number => p_object_version_number);
164 --
165 if (l_api_updating
166 and p_prmry_care_prvdr_typ_cd
167 <> nvl(ben_ppr_shd.g_old_rec.prmry_care_prvdr_typ_cd,hr_api.g_varchar2)
168 or not l_api_updating) then
169 --
170 -- check if value of lookup falls within lookup type.
171 --
172 Begin
173 select bpp.pcp_rpstry_flag
174 into l_pcp_rpstry_flag
175 from ben_prtt_enrt_rslt_f bper,
176 ben_pl_pcp bpp
177 where bper.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
178 and bper.pl_id = bpp.pl_id
179 and p_effective_date between bper.effective_start_date and bper.effective_end_date;
180
181 If l_pcp_rpstry_flag = 'N' then
182 if hr_api.not_exists_in_hr_lookups
183 (p_lookup_type => 'BEN_PRMRY_CARE_PRVDR_TYP',
184 p_lookup_code => p_prmry_care_prvdr_typ_cd,
185 p_effective_date => p_effective_date) then
186 --
187 -- raise error as does not exist as lookup
188 --
189 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
190 fnd_message.set_token('FIELD','p_prmry_care_prvdr_typ_cd');
191 fnd_message.set_token('TYPE', 'BEN_PRMRY_CARE_PRVDR_TYP');
192 fnd_message.raise_error;
193 --
194 end if;
195 --
196
197 Elsif l_pcp_rpstry_flag = 'Y' then -- else condition changed to check for repository flag = 'Y' and not 'N'
198 if hr_api.not_exists_in_hr_lookups
199 (p_lookup_type => 'BEN_PCP_SPCLTY',
200 p_lookup_code => p_prmry_care_prvdr_typ_cd,
201 p_effective_date => p_effective_date) then
202 --
203 -- raise error as does not exist as lookup
204 --
205 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
206 fnd_message.set_token('FIELD','p_prmry_care_prvdr_typ_cd');
207 fnd_message.set_token('TYPE', 'BEN_PRMRY_CARE_PRVDR_TYP');
208 fnd_message.raise_error;
209 --
210 end if;
211 --
212 End if;
213 Exception
214 When no_data_found then
215 Null;
216 End;
217 End if;
218 --
219 hr_utility.set_location('Leaving:'||l_proc,10);
220 --
221 end chk_prmry_care_prvdr_typ_cd;
222
223
224 /* ************************************************************************************
225 Procedure chk_prmry_care_prvdr_typ_cd(p_prmry_care_prvdr_id in number,
226 p_prmry_care_prvdr_typ_cd in varchar2,
227 p_effective_date in date,
228 p_object_version_number in number) is
229 --
230 l_proc varchar2(72) := g_package||'chk_prmry_care_prvdr_typ_cd';
231 l_api_updating boolean;
232 --
233 Begin
234 --
235 hr_utility.set_location('Entering:'||l_proc, 5);
236 --
237 l_api_updating := ben_ppr_shd.api_updating
238 (p_prmry_care_prvdr_id => p_prmry_care_prvdr_id,
239 p_effective_date => p_effective_date,
240 p_object_version_number => p_object_version_number);
241 --
242 if (l_api_updating
243 and p_prmry_care_prvdr_typ_cd
244 <> nvl(ben_ppr_shd.g_old_rec.prmry_care_prvdr_typ_cd,hr_api.g_varchar2)
245 or not l_api_updating) then
246 --
247 -- check if value of lookup falls within lookup type.
248 --
249 --
250 if hr_api.not_exists_in_hr_lookups
251 (p_lookup_type => 'BEN_PRMRY_CARE_PRVDR_TYP',
252 p_lookup_code => p_prmry_care_prvdr_typ_cd,
253 p_effective_date => p_effective_date) then
254 --
255 -- raise error as does not exist as lookup
256 --
257 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
258 fnd_message.set_token('FIELD','p_prmry_care_prvdr_typ_cd');
259 fnd_message.set_token('TYPE', 'BEN_PRMRY_CARE_PRVDR_TYP');
260 fnd_message.raise_error;
261 --
262 end if;
263 --
264 end if;
265 --
266 hr_utility.set_location('Leaving:'||l_proc,10);
267 --
268 end chk_prmry_care_prvdr_typ_cd;
269
270 ************************************************************************************* */
271 -- ----------------------------------------------------------------------------
272 -- |------< chk_unique_type >------|
273 -- ----------------------------------------------------------------------------
274 --
275 -- Description
276 -- This procedure is used to check that the type of the PCP is unique for
277 -- this result or dpnt record (ie, for the plan for this person).
278 --
279 -- Pre Conditions
280 -- None.
281 --
282 --
283 -- Post Success
284 -- Processing continues
285 --
286 -- Post Failure
287 -- Errors handled by the procedure
288 --
289 -- Access Status
290 -- Internal table handler use only.
291 --
292 Procedure chk_unique_type(p_prmry_care_prvdr_id in number,
293 p_prmry_care_prvdr_typ_cd in varchar2,
294 p_prtt_enrt_rslt_id in number,
298 p_validation_end_date in date,
295 p_elig_cvrd_dpnt_id in number,
296 p_effective_date in date,
297 p_validation_start_date in date,
299 p_object_version_number in number) is
300
301 CURSOR c1 IS
302 SELECT name
303 FROM ben_prmry_care_prvdr_f
304 WHERE prmry_care_prvdr_id <> nvl(p_prmry_care_prvdr_id, hr_api.g_number)
305 AND prmry_care_prvdr_typ_cd = p_prmry_care_prvdr_typ_cd
306 AND prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
307 AND effective_end_date >= p_validation_start_date
308 AND effective_start_date <= p_validation_end_date ;
309
310 l_c1_row c1%rowtype;
311 l_proc varchar2(72) := g_package||'chk_unique_type';
312 l_api_updating boolean;
313
314 Begin
315 --
316 hr_utility.set_location('Entering:'||l_proc, 5);
317 --
318 l_api_updating := ben_ppr_shd.api_updating
319 (p_prmry_care_prvdr_id => p_prmry_care_prvdr_id,
320 p_effective_date => p_effective_date,
321 p_object_version_number => p_object_version_number);
322
323 if (l_api_updating
324 and nvl(p_prmry_care_prvdr_typ_cd,hr_api.g_varchar2)
325 <> ben_ppr_shd.g_old_rec.prmry_care_prvdr_typ_cd
326 or not l_api_updating) then
327
328 if p_prtt_enrt_rslt_id is not null then
329 open c1 ;
330 fetch c1 into l_c1_row;
331 if c1%found then
332 close c1;
333 -- raise error as there is another record for this result or dependent
334 -- that has the same pcp type cd.
335 fnd_message.set_name('BEN','BEN_91818_PPR_TYP_UNIQUE');
336 fnd_message.raise_error;
337 end if;
338 close c1;
339 end if;
340
341 end if;
342 --
343 hr_utility.set_location('Leaving:'||l_proc, 10);
344 --
345 End chk_unique_type;
346
347 -- ------------------------------------------------------------------------
348 -- ---------------------< chk_pln_alws_pcp_dsgn >--------------------------
349 -- ------------------------------------------------------------------------
350 --
351 -- Description
352 -- This procedure is used to validate that a plan allows PCP designation
353 -- Codes at the Option in Plan level override the plan.
354
355 -- Pre Conditions
356 -- None.
357 --
358 --
359 -- Post Success
360 -- Processing continues
361 --
362 -- Post Failure
363 -- Errors handled by the procedure
364 --
365 -- Access Status
366 -- Internal table handler use only.
367 --
368 Procedure chk_pln_alws_pcp_dsgn(p_elig_cvrd_dpnt_id in number,
369 p_prtt_enrt_rslt_id in number,
370 p_effective_date in date) is
371 CURSOR c1 IS
372 SELECT bper.pl_id
373 FROM ben_prtt_enrt_rslt_f bper,
374 ben_pl_pcp bpp
375 WHERE bper.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
376 AND bper.pl_id = bpp.pl_id
377 AND bpp.pcp_dsgn_cd in ('R','O')
378 AND p_effective_date BETWEEN bper.effective_start_date and bper.effective_end_date;
379
380
381 CURSOR c2 IS
382 SELECT bper.prtt_enrt_rslt_id
383 FROM ben_elig_cvrd_dpnt_f becd,
384 ben_prtt_enrt_rslt_f bper,
385 ben_pl_pcp bpp
386 WHERE becd.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
387 AND becd.prtt_enrt_rslt_id = bper.prtt_enrt_rslt_id
388 AND bper.pl_id = bpp.pl_id
389 AND bpp.pcp_dpnt_dsgn_cd in ('R','O')
390 AND p_effective_date BETWEEN becd.effective_start_date and becd.effective_end_date
391 AND p_effective_date BETWEEN bper.effective_start_date and bper.effective_end_date;
392
393 CURSOR c3 IS
394 SELECT oipl.oipl_id
395 FROM ben_prtt_enrt_rslt_f bper,
396 ben_oipl_f oipl
397 WHERE bper.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
398 AND oipl.oipl_id = bper.oipl_id
399 AND oipl.pcp_dsgn_cd in ('R','O')
400 AND p_effective_date BETWEEN oipl.effective_start_date and oipl.effective_end_date
401 AND p_effective_date BETWEEN bper.effective_start_date and bper.effective_end_date;
402
403 CURSOR c4 IS
404 SELECT bper.prtt_enrt_rslt_id
405 FROM ben_elig_cvrd_dpnt_f becd,
406 ben_prtt_enrt_rslt_f bper,
407 ben_oipl_f oipl
408 WHERE becd.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
409 AND becd.prtt_enrt_rslt_id = bper.prtt_enrt_rslt_id
410 AND bper.oipl_id = oipl.oipl_id
411 AND oipl.pcp_dpnt_dsgn_cd in ('R','O')
412 AND p_effective_date BETWEEN becd.effective_start_date and becd.effective_end_date
413 AND p_effective_date BETWEEN oipl.effective_start_date and oipl.effective_end_date
414 AND p_effective_date BETWEEN bper.effective_start_date and bper.effective_end_date;
415
416 l_c1_row c1%rowtype;
417 l_c2_row c2%rowtype;
418 l_c3_row c3%rowtype;
419 l_c4_row c4%rowtype;
420
421 Begin
422 if p_prtt_enrt_rslt_id is not null then
423 open c3;
424 fetch c3 into l_c3_row;
425 if c3%notfound then
426 close c3;
427 open c1;
428 fetch c1 into l_c1_row;
429 if c1%notfound then
430 close c1;
431 -- raise error as this plan does not allow selection of pcp
435 elsif c1%found then
432 fnd_message.set_name('BEN','BEN_92568_DSGN_NOT_ALWD');
433 fnd_message.raise_error;
434
436 close c1;
437 end if;
438 elsif c3%found then
439 close c3;
440 end if;
441 elsif p_elig_cvrd_dpnt_id is not null then
442 open c4;
443 fetch c4 into l_c4_row;
444 if c4%notfound then
445 close c4;
446 open c2;
447 fetch c2 into l_c2_row;
448 if c2%notfound then
449 close c2;
450 -- raise error as this plan does not allow dependent selection of pcp
451 fnd_message.set_name('BEN','BEN_92569_DPNT_DSGN_NOT_ALWD');
452 fnd_message.raise_error;
453 elsif c2%found then
454 close c2;
455 end if;
456 elsif c4%found then
457 close c4;
458 end if;
459 end if;
460
461 end chk_pln_alws_pcp_dsgn;
462
463
464
465
466 -- ----------------------------------------------------------------------------
467 -- |------< chk_rslt_dpnt_id >------|
468 -- ----------------------------------------------------------------------------
469 --
470 -- Description
471 -- This procedure is used to check that only the result id or the dependent
472 -- id is filled in, not both.
473 --
474 -- Pre Conditions
475 -- None.
476 --
477 --
478 -- Post Success
479 -- Processing continues
480 --
481 -- Post Failure
482 -- Errors handled by the procedure
483 --
484 -- Access Status
485 -- Internal table handler use only.
486 --
487 Procedure chk_rslt_dpnt_id(p_prtt_enrt_rslt_id in number,
488 p_elig_cvrd_dpnt_id in number) is
489
490 l_proc varchar2(72) := g_package||'chk_rslt_dpnt_id';
491 l_api_updating boolean;
492 --
493 Begin
494 --
495 hr_utility.set_location('Entering:'||l_proc, 5);
496 --
497 if p_prtt_enrt_rslt_id is null and p_elig_cvrd_dpnt_id is null then
498 fnd_message.set_name('BEN','BEN_91819_RSLT_DPNT_NULL');
499 fnd_message.raise_error;
500 elsif p_prtt_enrt_rslt_id is not null
501 and p_elig_cvrd_dpnt_id is not null then
502 fnd_message.set_name('BEN','BEN_91820_RSLT_DPNT_NOTNULL');
503 fnd_message.raise_error;
504 end if;
505 --
506 hr_utility.set_location('Leaving:'||l_proc, 10);
507 --
508 End chk_rslt_dpnt_id;
509 --
510 -- ----------------------------------------------------------------------------
511 -- |------< chk_age_gendr_ppr_record >------|
512 -- ----------------------------------------------------------------------------
513 --
514 -- Description
515 -- This procedure is used to check that the age and gender of the participant.
516 --
517 -- Pre Conditions
518 -- None.
519 --
520 -- In Parameters
521 -- prmry_care_prvdr_id Primary key for the record.
522 -- prtt_enrt_rslt_id
523 -- elig_cvrd_dpnt_id
524 -- effective_date
525 --
526 -- Post Success
527 -- Processing continues
528 --
529 -- Post Failure
530 -- Errors handled by the procedure
531 --
532 -- Access Status
533 -- Internal table handler use only.
534 --
535
536 Procedure chk_age_gendr_ppr_record
537 (p_prmry_care_prvdr_id in number,
538 p_prtt_enrt_rslt_id in number,
539 p_elig_cvrd_dpnt_id in number,
540 p_prmry_care_prvdr_typ_cd in varchar2,
541 p_effective_date in date) is
542
543 l_proc varchar2(72) := g_package|| ' chk_age_gendr_ppr_record' ;
544 l_min_age ben_pl_pcp_typ.min_age%type;
545 l_max_age ben_pl_pcp_typ.max_age%type;
546 l_min_msg varchar2(30) := 'n/a';
547 l_max_msg varchar2(30) := 'n/a';
548 l_sex varchar2(30);
549 l_gender varchar2(30);
550 l_age number;
551 l_dob date;
552 l_rslt_id number;
553 l_dpnt_person_id number;
554 l_person_id number;
555
556 cursor c_plan_design(p_rslt_id number) is
557 select pen.person_id, nvl(pct.min_age,-1),
558 nvl(pct.max_age,9999), pct.gndr_alwd_cd
559 from ben_pl_pcp_typ pct,
560 ben_pl_pcp pcp,
561 ben_prtt_enrt_rslt_f pen
562 where pen.prtt_enrt_rslt_id = p_rslt_id
563 and pen.pl_id = pcp.pl_id
564 and pcp.pl_pcp_id = pct.pl_pcp_id
565 and pct.pcp_typ_cd = p_prmry_care_prvdr_typ_cd
566 and p_effective_date between pen.effective_start_date
567 and pen.effective_end_date;
568
569 cursor c_dob(p_person_id number) is
570 select trunc(date_of_birth), sex
571 from per_all_people_f
572 where person_id = p_person_id
573 and p_effective_date between effective_start_date
574 and effective_end_date;
575
576 cursor c_rslt_id is
577 select distinct prtt_enrt_rslt_id, dpnt_person_id
578 from ben_elig_cvrd_dpnt_f
579 where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
580 and p_effective_date between effective_start_date
581 and effective_end_date;
582
583 --
584 Begin
585 hr_utility.set_location('Entering:'||l_proc, 5);
586 --
587 if p_elig_cvrd_dpnt_id is not null then
588 -- get result id, dpnt person id.
589 open c_rslt_id;
593 l_rslt_id := p_prtt_enrt_rslt_id;
590 fetch c_rslt_id into l_rslt_id, l_dpnt_person_id;
591 close c_rslt_id;
592 else
594 end if;
595
596 -- get the plan design limitations.
597 open c_plan_design(p_rslt_id => l_rslt_id);
598 fetch c_plan_design into l_person_id, l_min_age, l_max_age, l_gender;
599 close c_plan_design;
600
601 if l_person_id is not null then
602 -- we found a limitation....
603 -- get the data from person table to compare to plan design limits.
604 if p_elig_cvrd_dpnt_id is not null then
605 l_person_id := l_dpnt_person_id; -- reload for dpnt.
606 end if;
607 open c_dob(p_person_id => l_person_id);
608 fetch c_dob into l_dob, l_sex;
609 close c_dob;
610
611 if l_dob is not null then
612 -- calculation to see whether the age fall under min and max or not
613 l_age := (months_between(p_effective_date, l_dob))/12;
614 if (l_age > l_max_age or l_age < l_min_age ) then
615 if l_max_age <> 9999 then
616 l_max_msg := l_max_age;
617 end if;
618 if l_min_age <> -1 then
619 l_min_msg := l_min_age;
620 end if;
621 fnd_message.set_name('BEN','BEN_92579_AGE_GNDR_REQD');
622 fnd_message.set_token('MIN', l_min_msg);
623 fnd_message.set_token('MAX', l_max_msg);
624 fnd_message.set_token('GENDER', 'n/a');
625 fnd_message.raise_error;
626 end if;
627 end if;
628 if l_sex is not null and l_gender is not null then
629 if (l_sex <> l_gender ) then
630 fnd_message.set_name('BEN','BEN_92579_AGE_GNDR_REQD');
631 fnd_message.set_token('MIN', l_min_msg);
632 fnd_message.set_token('MAX', l_max_msg);
633 fnd_message.set_token('GENDER', l_gender);
634 fnd_message.raise_error;
635 end if;
636 end if;
637 end if;
638
639 hr_utility.set_location('Leaving:'||l_proc, 15);
640 End chk_age_gendr_ppr_record;
641 --
642 -- ----------------------------------------------------------------------------
643 -- |------< chk_max_chgs_ppr_record >------|
644 -- ----------------------------------------------------------------------------
645 --
646 -- Description
647 -- This procedure is used to check the max number of changes to PCP based data.
648 --
649 -- Pre Conditions
650 -- None.
651 --
652 -- In Parameters
653 -- prtt_enrt_rslt_id
654 -- elig_cvrd_dpnt_id
655 -- effective_date
656 --
657 -- Post Success
658 -- Processing continues
659 --
660 -- Post Failure
661 -- Errors handled by the procedure
662 --
663 -- Access Status
664 -- Internal table handler use only.
665 --
666
667 Procedure chk_max_chgs_ppr_record
668 (p_prtt_enrt_rslt_id in number,
669 p_elig_cvrd_dpnt_id in number,
670 p_effective_date in date) is
671
672 l_proc varchar2(72) := g_package|| ' chk_max_chgs_ppr_record ' ;
673 l_prtt_enrt_rslt_id number;
674 l_num_chgs_alwd number := 0;
675 l_first_day date;
676 l_last_day date;
677 l_num_of_chgs number := 0;
678
679 cursor c_prtt_enrt_rslt_id is
680 select distinct prtt_enrt_rslt_id
681 from ben_elig_cvrd_dpnt_f
682 where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id;
683
684 cursor c_pcp (p_prtt_enrt_rslt_id number) is
685 select pcp.pcp_num_chgs
686 from ben_prtt_enrt_rslt_f pen, ben_pl_pcp pcp
687 Where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
688 and pen.pl_id = pcp.pl_id
689 and p_effective_date between
690 pen.effective_start_date and pen.effective_end_date;
691
692 cursor c_count_rows(p_first_day date, p_last_day date) is
693 select count('x')
694 from ben_prmry_care_prvdr_f ppr
695 where (ppr.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
696 or ppr.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id)
697 and ppr.effective_start_date between p_first_day and p_last_day;
698
699 --
700 Begin
701 hr_utility.set_location('Entering:'||l_proc, 5);
702 --
703 if p_elig_cvrd_dpnt_id is not null then
704 -- get rslt-id from dpnt record.
705 open c_prtt_enrt_rslt_id;
706 fetch c_prtt_enrt_rslt_id into l_prtt_enrt_rslt_id;
707 close c_prtt_enrt_rslt_id;
708 else
709 l_prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
710 end if;
711
712 -- get the number of changes allowed for this plan.
713 open c_pcp (p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id);
714 fetch c_pcp into l_num_chgs_alwd;
715 close c_pcp;
716
717 -- for now, the number of changes always relates to within a calendar month.
718 -- so get the number of changes the person has made to this plan's
719 -- pcp selection for this calendar month.
720
721 l_first_day := to_date('01'||substr(to_char(p_effective_date, 'dd-mon-rrrr'), 4,9),
722 'dd-mon-rrrr');
723 l_last_day := last_day(p_effective_date);
724
725 open c_count_rows (p_first_day => l_first_day, p_last_day => l_last_day);
726 fetch c_count_rows into l_num_of_chgs;
727 close c_count_rows;
728
729 -- comparing number of changes
730 if l_num_of_chgs >= l_num_chgs_alwd then
734 end if;
731 fnd_message.set_name('BEN','BEN_92580_MAX_NUM_CHGS');
732 fnd_message.set_token('NDATE', (l_last_day + 1));
733 fnd_message.raise_error;
735 hr_utility.set_location('Leaving:'||l_proc, 15);
736 End chk_max_chgs_ppr_record ;
737 --
738 --
739 -- ----------------------------------------------------------------------------
740 -- |--------------------------< dt_update_validate >--------------------------|
741 -- ----------------------------------------------------------------------------
742 -- {Start Of Comments}
743 --
744 -- Description:
745 -- This procedure is used for referential integrity of datetracked
746 -- parent entities when a datetrack update operation is taking place
747 -- and where there is no cascading of update defined for this entity.
748 --
749 -- Prerequisites:
750 -- This procedure is called from the update_validate.
751 --
752 -- In Parameters:
753 --
754 -- Post Success:
755 -- Processing continues.
756 --
757 -- Post Failure:
758 --
759 -- Developer Implementation Notes:
760 -- This procedure should not need maintenance unless the HR Schema model
761 -- changes.
762 --
763 -- Access Status:
764 -- Internal Row Handler Use Only.
765 --
766 -- {End Of Comments}
767 -- ----------------------------------------------------------------------------
768 Procedure dt_update_validate
769 (p_elig_cvrd_dpnt_id in number default hr_api.g_number,
770 p_prtt_enrt_rslt_id in number default hr_api.g_number,
771 p_datetrack_mode in varchar2,
772 p_validation_start_date in date,
773 p_validation_end_date in date) Is
774 --
775 l_proc varchar2(72) := g_package||'dt_update_validate';
776 l_integrity_error Exception;
777 l_table_name all_tables.table_name%TYPE;
778 --
779 Begin
780 hr_utility.set_location('Entering:'||l_proc, 5);
781 --
782 -- Ensure that the p_datetrack_mode argument is not null
783 --
784 hr_api.mandatory_arg_error
785 (p_api_name => l_proc,
786 p_argument => 'datetrack_mode',
787 p_argument_value => p_datetrack_mode);
788 --
789 -- Only perform the validation if the datetrack update mode is valid
790 --
791 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
792 --
793 --
794 -- Ensure the arguments are not null
795 --
796 hr_api.mandatory_arg_error
797 (p_api_name => l_proc,
798 p_argument => 'validation_start_date',
799 p_argument_value => p_validation_start_date);
800 --
801 hr_api.mandatory_arg_error
802 (p_api_name => l_proc,
803 p_argument => 'validation_end_date',
804 p_argument_value => p_validation_end_date);
805 --
806 If ((nvl(p_elig_cvrd_dpnt_id, hr_api.g_number) <> hr_api.g_number) and
807 NOT (dt_api.check_min_max_dates
808 (p_base_table_name => 'ben_elig_cvrd_dpnt_f',
809 p_base_key_column => 'elig_cvrd_dpnt_id',
810 p_base_key_value => p_elig_cvrd_dpnt_id,
811 p_from_date => p_validation_start_date,
812 p_to_date => p_validation_end_date))) Then
813 l_table_name := 'ben_elig_cvrd_dpnt_f';
814 Raise l_integrity_error;
815 End If;
816 If ((nvl(p_prtt_enrt_rslt_id, hr_api.g_number) <> hr_api.g_number) and
817 NOT (dt_api.check_min_max_dates
818 (p_base_table_name => 'ben_prtt_enrt_rslt_f',
819 p_base_key_column => 'prtt_enrt_rslt_id',
820 p_base_key_value => p_prtt_enrt_rslt_id,
821 p_from_date => p_validation_start_date,
822 p_to_date => p_validation_end_date))) Then
823 l_table_name := 'ben_prtt_enrt_rslt_f';
824 Raise l_integrity_error;
825 End If;
826 --
827 End If;
828 --
829 hr_utility.set_location(' Leaving:'||l_proc, 10);
830 Exception
831 When l_integrity_error Then
832 --
833 -- A referential integrity check was violated therefore
834 -- we must error
835
836 ben_utility.parent_integrity_error(p_table_name => l_table_name);
837
838 When Others Then
839 --
840 -- An unhandled or unexpected error has occurred which
841 -- we must report
842 --
843 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
844 fnd_message.set_token('PROCEDURE', l_proc);
845 fnd_message.set_token('STEP','15');
846 fnd_message.raise_error;
847 End dt_update_validate;
848 --
849 -- ----------------------------------------------------------------------------
850 -- |--------------------------< dt_delete_validate >--------------------------|
851 -- ----------------------------------------------------------------------------
852 -- {Start Of Comments}
853 --
854 -- Description:
855 -- This procedure is used for referential integrity of datetracked
856 -- child entities when either a datetrack DELETE or ZAP is in operation
857 -- and where there is no cascading of delete defined for this entity.
858 -- For the datetrack mode of DELETE or ZAP we must ensure that no
859 -- datetracked child rows exist between the validation start and end
860 -- dates.
861 --
862 -- Prerequisites:
863 -- This procedure is called from the delete_validate.
864 --
865 -- In Parameters:
866 --
867 -- Post Success:
868 -- Processing continues.
869 --
870 -- Post Failure:
874 --
871 -- If a row exists by determining the returning Boolean value from the
872 -- generic dt_api.rows_exist function then we must supply an error via
873 -- the use of the local exception handler l_rows_exist.
875 -- Developer Implementation Notes:
876 -- This procedure should not need maintenance unless the HR Schema model
877 -- changes.
878 --
879 -- Access Status:
880 -- Internal Row Handler Use Only.
881 --
882 -- {End Of Comments}
883 -- ----------------------------------------------------------------------------
884 Procedure dt_delete_validate
885 (p_prmry_care_prvdr_id in number,
886 p_datetrack_mode in varchar2,
887 p_validation_start_date in date,
888 p_validation_end_date in date) Is
889 --
890 l_proc varchar2(72) := g_package||'dt_delete_validate';
891 l_rows_exist Exception;
892 l_table_name all_tables.table_name%TYPE;
893 --
894 Begin
895 hr_utility.set_location('Entering:'||l_proc, 5);
896 --
897 -- Ensure that the p_datetrack_mode argument is not null
898 --
899 hr_api.mandatory_arg_error
900 (p_api_name => l_proc,
901 p_argument => 'datetrack_mode',
902 p_argument_value => p_datetrack_mode);
903 --
904 -- Only perform the validation if the datetrack mode is either
905 -- DELETE or ZAP
906 --
907 If (p_datetrack_mode = 'DELETE' or
908 p_datetrack_mode = 'ZAP') then
909 --
910 --
911 -- Ensure the arguments are not null
912 --
913 hr_api.mandatory_arg_error
914 (p_api_name => l_proc,
915 p_argument => 'validation_start_date',
916 p_argument_value => p_validation_start_date);
917 --
918 hr_api.mandatory_arg_error
919 (p_api_name => l_proc,
920 p_argument => 'validation_end_date',
921 p_argument_value => p_validation_end_date);
922 --
923 hr_api.mandatory_arg_error
924 (p_api_name => l_proc,
925 p_argument => 'prmry_care_prvdr_id',
926 p_argument_value => p_prmry_care_prvdr_id);
927 --
928 --
929 --
930 End If;
931 --
932 hr_utility.set_location(' Leaving:'||l_proc, 10);
933 Exception
934 When l_rows_exist Then
935 --
936 -- A referential integrity check was violated therefore
937 -- we must error
938 --
939 ben_utility.child_exists_error(p_table_name => l_table_name);
940
941 When Others Then
942 --
943 -- An unhandled or unexpected error has occurred which
944 -- we must report
945 --
946 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
947 fnd_message.set_token('PROCEDURE', l_proc);
948 fnd_message.set_token('STEP','15');
949 fnd_message.raise_error;
950 End dt_delete_validate;
951 --
952 -- ----------------------------------------------------------------------------
953 -- |---------------------------< insert_validate >----------------------------|
954 -- ----------------------------------------------------------------------------
955 Procedure insert_validate
956 (p_rec in ben_ppr_shd.g_rec_type,
957 p_effective_date in date,
958 p_datetrack_mode in varchar2,
959 p_validation_start_date in date,
960 p_validation_end_date in date) is
961 --
962 l_proc varchar2(72) := g_package||'insert_validate';
963 --
964 Begin
965 hr_utility.set_location('Entering:'||l_proc, 5);
966 --
967 -- Call all supporting business operations
968 --
969 --
970 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
971 --
972 chk_prmry_care_prvdr_id
973 (p_prmry_care_prvdr_id => p_rec.prmry_care_prvdr_id,
974 p_effective_date => p_effective_date,
975 p_object_version_number => p_rec.object_version_number);
976 --
977 chk_pcp_name_spclty_not_null
978 (p_name => p_rec.name,
979 p_prmry_care_prvdr_typ_cd => p_rec.prmry_care_prvdr_typ_cd);
980 --
981 chk_prmry_care_prvdr_typ_cd
982 (p_prmry_care_prvdr_id => p_rec.prmry_care_prvdr_id,
983 p_prmry_care_prvdr_typ_cd => p_rec.prmry_care_prvdr_typ_cd,
984 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
985 p_effective_date => p_effective_date,
986 p_object_version_number => p_rec.object_version_number);
987
988 chk_unique_type
989 (p_prmry_care_prvdr_id => p_rec.prmry_care_prvdr_id,
990 p_prmry_care_prvdr_typ_cd => p_rec.prmry_care_prvdr_typ_cd,
991 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
992 p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
993 p_effective_date => p_effective_date,
994 p_validation_start_date => p_validation_start_date,
995 p_validation_end_date => p_validation_end_date,
996 p_object_version_number => p_rec.object_version_number);
997
998 chk_pln_alws_pcp_dsgn(p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
999 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1000 p_effective_date => p_effective_date);
1001
1002 chk_rslt_dpnt_id
1003 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1004 p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id);
1005
1006
1007 chk_age_gendr_ppr_record
1008 (p_prmry_care_prvdr_id => p_rec.prmry_care_prvdr_id,
1009 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1010 p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1014 chk_max_chgs_ppr_record
1011 p_prmry_care_prvdr_typ_cd => p_rec.prmry_care_prvdr_typ_cd,
1012 p_effective_date => p_effective_date);
1013
1015 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1016 p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1017 p_effective_date => p_effective_date);
1018
1019 --
1020 hr_utility.set_location(' Leaving:'||l_proc, 10);
1021 End insert_validate;
1022 --
1023 -- ----------------------------------------------------------------------------
1024 -- |---------------------------< update_validate >----------------------------|
1025 -- ----------------------------------------------------------------------------
1026 Procedure update_validate
1027 (p_rec in ben_ppr_shd.g_rec_type,
1028 p_effective_date in date,
1029 p_datetrack_mode in varchar2,
1030 p_validation_start_date in date,
1031 p_validation_end_date in date) is
1032 --
1033 l_proc varchar2(72) := g_package||'update_validate';
1034 --
1035 Begin
1036
1037 hr_utility.set_location('Entering:'||l_proc, 5);
1038 --
1039 -- Call all supporting business operations
1040 --
1041 --
1042 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1043 --
1044 chk_prmry_care_prvdr_id
1045 (p_prmry_care_prvdr_id => p_rec.prmry_care_prvdr_id,
1046 p_effective_date => p_effective_date,
1047 p_object_version_number => p_rec.object_version_number);
1048 --
1049 chk_pcp_name_spclty_not_null
1050 (p_name => p_rec.name,
1051 p_prmry_care_prvdr_typ_cd => p_rec.prmry_care_prvdr_typ_cd);
1052 --
1053 chk_prmry_care_prvdr_typ_cd
1054 (p_prmry_care_prvdr_id => p_rec.prmry_care_prvdr_id,
1055 p_prmry_care_prvdr_typ_cd => p_rec.prmry_care_prvdr_typ_cd,
1056 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1057 p_effective_date => p_effective_date,
1058 p_object_version_number => p_rec.object_version_number);
1059
1060 chk_unique_type
1061 (p_prmry_care_prvdr_id => p_rec.prmry_care_prvdr_id,
1062 p_prmry_care_prvdr_typ_cd => p_rec.prmry_care_prvdr_typ_cd,
1063 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1064 p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1065 p_effective_date => p_effective_date,
1066 p_validation_start_date => p_validation_start_date,
1067 p_validation_end_date => p_validation_end_date,
1068 p_object_version_number => p_rec.object_version_number);
1069
1070
1071 chk_pln_alws_pcp_dsgn(p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1072 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1073 p_effective_date => p_effective_date);
1074
1075
1076 chk_rslt_dpnt_id
1077 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1078 p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id);
1079
1080 chk_age_gendr_ppr_record
1081 (p_prmry_care_prvdr_id => p_rec.prmry_care_prvdr_id,
1082 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1083 p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1084 p_prmry_care_prvdr_typ_cd => p_rec.prmry_care_prvdr_typ_cd,
1085 p_effective_date => p_effective_date);
1086
1087 chk_max_chgs_ppr_record
1088 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1089 p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1090 p_effective_date => p_effective_date);
1091
1092 --
1093 -- Call the datetrack update integrity operation
1094 --
1095 dt_update_validate
1096 (p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1097 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1098 p_datetrack_mode => p_datetrack_mode,
1099 p_validation_start_date => p_validation_start_date,
1100 p_validation_end_date => p_validation_end_date);
1101 --
1102 hr_utility.set_location(' Leaving:'||l_proc, 10);
1103 End update_validate;
1104 --
1105 -- ----------------------------------------------------------------------------
1106 -- |---------------------------< delete_validate >----------------------------|
1107 -- ----------------------------------------------------------------------------
1108 Procedure delete_validate
1109 (p_rec in ben_ppr_shd.g_rec_type,
1110 p_effective_date in date,
1111 p_datetrack_mode in varchar2,
1112 p_validation_start_date in date,
1113 p_validation_end_date in date) is
1114 --
1115 l_proc varchar2(72) := g_package||'delete_validate';
1116 --
1117 Begin
1118 hr_utility.set_location('Entering:'||l_proc, 5);
1119 --
1120 -- Call all supporting business operations
1121 --
1122 dt_delete_validate
1123 (p_datetrack_mode => p_datetrack_mode,
1124 p_validation_start_date => p_validation_start_date,
1125 p_validation_end_date => p_validation_end_date,
1126 p_prmry_care_prvdr_id => p_rec.prmry_care_prvdr_id);
1127 --
1128 hr_utility.set_location(' Leaving:'||l_proc, 10);
1129 End delete_validate;
1130 --
1131 --
1132 -- ---------------------------------------------------------------------------
1133 -- |---------------------< return_legislation_code >-------------------------|
1134 -- ---------------------------------------------------------------------------
1135 --
1136 function return_legislation_code
1137 (p_prmry_care_prvdr_id in number) return varchar2 is
1138 --
1139 -- Declare cursor
1140 --
1141 cursor csr_leg_code is
1142 select a.legislation_code
1143 from per_business_groups a,
1144 ben_prmry_care_prvdr_f b
1145 where b.prmry_care_prvdr_id = p_prmry_care_prvdr_id
1146 and a.business_group_id = b.business_group_id;
1147 --
1148 -- Declare local variables
1149 --
1150 l_legislation_code varchar2(150);
1151 l_proc varchar2(72) := g_package||'return_legislation_code';
1152 --
1153 begin
1154 --
1155 hr_utility.set_location('Entering:'|| l_proc, 10);
1156 --
1157 -- Ensure that all the mandatory parameter are not null
1158 --
1159 hr_api.mandatory_arg_error(p_api_name => l_proc,
1160 p_argument => 'prmry_care_prvdr_id',
1161 p_argument_value => p_prmry_care_prvdr_id);
1162 --
1163 open csr_leg_code;
1164 --
1165 fetch csr_leg_code into l_legislation_code;
1166 --
1167 if csr_leg_code%notfound then
1168 --
1169 close csr_leg_code;
1170 --
1171 -- The primary key is invalid therefore we must error
1172 --
1173 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1174 fnd_message.raise_error;
1175 --
1176 end if;
1177 --
1178 close csr_leg_code;
1179 --
1180 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1181 --
1182 return l_legislation_code;
1183 --
1184 end return_legislation_code;
1185 --
1186 end ben_ppr_bus;
[Home] [Help]