[Home] [Help]
PACKAGE BODY: APPS.BEN_PBN_BUS
Source
1 Package Body ben_pbn_bus as
2 /* $Header: bepbnrhi.pkb 120.1.12010000.3 2008/11/19 05:57:39 sagnanas ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_pbn_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_pl_bnf_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 -- pl_bnf_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_pl_bnf_id(p_pl_bnf_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_pl_bnf_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_pbn_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_pl_bnf_id => p_pl_bnf_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_pl_bnf_id,hr_api.g_number)
55 <> ben_pbn_shd.g_old_rec.pl_bnf_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_pbn_shd.constraint_error('BEN_PL_BNF_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_pl_bnf_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_pbn_shd.constraint_error('BEN_PL_BNF_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_pl_bnf_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_prmry_cntngnt_cd >------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that the lookup value is valid.
85 --
86 -- Pre Conditions
87 -- None.
88 --
89 -- In Parameters
90 -- pl_bnf_id PK of record being inserted or updated.
91 -- prmry_cntngnt_cd Value of lookup code.
92 -- effective_date effective date
93 -- object_version_number Object version number of record being
94 -- inserted or updated.
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Error handled by procedure
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 Procedure chk_prmry_cntngnt_cd(p_pl_bnf_id in number,
106 p_prmry_cntngnt_cd in varchar2,
107 p_effective_date in date,
108 p_object_version_number in number) is
109 --
110 l_proc varchar2(72) := g_package||'chk_prmry_cntngnt_cd';
111 l_api_updating boolean;
112 --
113 Begin
114 --
115 hr_utility.set_location('Entering:'||l_proc, 5);
116 --
117 l_api_updating := ben_pbn_shd.api_updating
118 (p_pl_bnf_id => p_pl_bnf_id,
119 p_effective_date => p_effective_date,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and p_prmry_cntngnt_cd
124 <> nvl(ben_pbn_shd.g_old_rec.prmry_cntngnt_cd,hr_api.g_varchar2)
125 or not l_api_updating) then
126 --
127 -- check if value of lookup falls within lookup type.
128 --
129 --
130 if hr_api.not_exists_in_hr_lookups
131 (p_lookup_type => 'BEN_PRMRY_CNTNGNT',
132 p_lookup_code => p_prmry_cntngnt_cd,
133 p_effective_date => p_effective_date) then
134 --
135 -- raise error as does not exist as lookup
136 --
137 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
138 fnd_message.raise_error;
139 --
140 end if;
141 --
142 end if;
143 --
144 hr_utility.set_location('Leaving:'||l_proc,10);
145 --
146 end chk_prmry_cntngnt_cd;
147 --
148 --
149 -- ----------------------------------------------------------------------------
150 -- |------< chk_all_pl_bnf_parameters >------|
151 -- ----------------------------------------------------------------------------
152 --
153 -- Description
154 -- This procedure validates all PL_BNF_F columns and business rules that
155 -- depend on the values of BNF parameters in PL_F table.
156 --
157 -- Pre-Conditions
158 -- None.
159 --
160 -- In Parameters
161 -- p_pl_bnf_id PK
162 --
163 -- p_effective_date session date
164 -- p_object_version_number object version number
165 --
166 -- Post Success
167 -- Processing continues
168 --
169 -- Post Failure
170 -- Error raised.
171 --
172 -- Access Status
173 -- Internal table handler use only.
174 --
175 Procedure chk_all_pl_bnf_parameters(p_pl_bnf_id in number,
176 p_prtt_enrt_rslt_id in number,
177 p_prmry_cntngnt_cd in varchar2,
178 p_organization_id in number,
179 p_addl_instrn_txt in varchar2,
180 p_amt_dsgd_val in number,
181 p_pct_dsgd_num in number,
182 p_validation_start_date in date,
183 p_validation_end_date in date,
184 p_effective_date in date,
185 p_business_group_id in number,
186 p_object_version_number in number) is
187 --
188 l_proc varchar2(72) := g_package||'chk_all_bnf_parameters';
189 l_bnf_dsgn_cd varchar2(30);
190 l_bnf_cntngt_bnfs_alwd_flag varchar2(1);
191 l_bnf_may_dsgt_org_flag varchar2(1);
192 l_bnf_addl_instn_txt_alwd_flag varchar2(1);
193 l_bnf_pct_amt_alwd_cd varchar2(30);
194 l_bnf_mn_dsgntbl_amt number(15);
195 l_bnf_incrmt_amt number(15);
196 l_bnf_mn_dsgntbl_pct_val number(15);
197 l_bnf_pct_incrmt_val number(15);
198 -- Added for Bug 2395217
199 l_bnf_enrt_oipl_id number(15);
200 l_bnf_enrt_option_name ben_opt_f.name%TYPE;
201 -- Added for bug no 1845251
202 l_pl_name ben_pl_f.name%type; -- UTF8 Change Bug 2254683
203 --
204 cursor pl1(l_lf_evt_ocrd_dt date) is
205 select a.bnf_dsgn_cd
206 -- added for bug no. 1845251
207 , a.name
208 , a.bnf_cntngt_bnfs_alwd_flag
209 , a.bnf_may_dsgt_org_flag
210 , a.bnf_addl_instn_txt_alwd_flag
211 , a.bnf_pct_amt_alwd_cd
212 , a.bnf_mn_dsgntbl_amt
213 , a.bnf_incrmt_amt
214 , a.bnf_mn_dsgntbl_pct_val
215 , a.bnf_pct_incrmt_val
216 , b.oipl_id
217 from ben_pl_f a
218 , ben_prtt_enrt_rslt_f b
219 where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
220 and b.prtt_enrt_rslt_stat_cd is null
221 and nvl(l_lf_evt_ocrd_dt,p_effective_date) between b.effective_start_date
222 and b.effective_end_date
223 and a.pl_id = b.pl_id
224 and nvl(l_lf_evt_ocrd_dt,p_effective_date) between a.effective_start_date
225 and a.effective_end_date
226 and a.business_group_id + 0 = p_business_group_id
227 ;
228
229 CURSOR csr_option_name(l_lf_evt_ocrd_dt date) is
230 SELECT
231 opt.NAME
232 FROM
233 ben_opt_f opt
234 ,ben_oipl_f oipl
235 WHERE
236 oipl.oipl_id=l_bnf_enrt_oipl_id
237 and nvl(l_lf_evt_ocrd_dt,p_effective_date) between oipl.effective_start_date and oipl.effective_end_date
238 and opt.opt_id=oipl.opt_id
239 and nvl(l_lf_evt_ocrd_dt,p_effective_date) between opt.effective_start_date and opt.effective_end_date;
240 --
241 CURSOR c_pil is
242 SELECT pil.lf_evt_ocrd_dt
243 FROM ben_prtt_enrt_rslt_f pen,
244 ben_per_in_ler pil
245 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
246 AND pen.prtt_enrt_rslt_stat_cd is NULL
247 AND p_effective_date BETWEEN pen.effective_start_date
248 AND pen.effective_end_date
249 AND pen.per_in_ler_id = pil.per_in_ler_id;
250 --
251 l_disp_param varchar2(30);
252 l_lf_evt_ocrd_dt date;
253 --
254 Begin
255 hr_utility.set_location('Entering:'||l_proc,5);
256
257 l_disp_param := null;
258 l_lf_evt_ocrd_dt := null;
259 l_disp_param := fnd_profile.value('BEN_DSPL_NAME_BASIS');
260 hr_utility.set_location('l_disp_param :' || l_disp_param, 12345);
261
262 if l_disp_param = 'LEOD' then
263 open c_pil;
264 fetch c_pil into l_lf_evt_ocrd_dt;
265 hr_utility.set_location('l_lf_evt_ocrd_dt :' || l_lf_evt_ocrd_dt, 12345);
266 close c_pil;
267 end if;
268
269 -- check if ben_pl_f bnf parameters do not contradict ben_pl_bnf_f values
270 open pl1(l_lf_evt_ocrd_dt);
271
272 fetch pl1 into
273 l_bnf_dsgn_cd
274 ,l_pl_name
275 ,l_bnf_cntngt_bnfs_alwd_flag
276 ,l_bnf_may_dsgt_org_flag
277 ,l_bnf_addl_instn_txt_alwd_flag
278 ,l_bnf_pct_amt_alwd_cd
279 ,l_bnf_mn_dsgntbl_amt
280 ,l_bnf_incrmt_amt
281 ,l_bnf_mn_dsgntbl_pct_val
282 ,l_bnf_pct_incrmt_val
283 ,l_bnf_enrt_oipl_id
284 ;
285 if pl1%notfound then
286 close pl1;
287 -- raise error as corresponding Plan does not exist in ben_pl_f
288 -- table.
289 fnd_message.set_name('BEN', 'BEN_91641_ENRT_RSLT_INVLD');
290 fnd_message.raise_error;
291 elsif l_bnf_dsgn_cd is null then
292 -- raise error as this plan does not allow to designate beneficiaries
293 fnd_message.set_name('BEN', 'BEN_91634_BNF_NOT_ALWD');
294 fnd_message.raise_error;
295 else
296 if p_organization_id is not null and
297 l_bnf_may_dsgt_org_flag = 'N' then
298 -- raise error as this plan does not allow to designate orgs
299 fnd_message.set_name('BEN', 'BEN_91635_ORGS_BNF_NOT_ALWD');
300 fnd_message.raise_error;
301 end if;
302 if p_prmry_cntngnt_cd = 'CNTNGNT' and
303 l_bnf_cntngt_bnfs_alwd_flag = 'N' then
304 -- raise error as this plan does not allow to designate contingent bnfs
305 fnd_message.set_name('BEN', 'BEN_91636_CNTNGNT_BNF_NOT_ALWD');
306 fnd_message.raise_error;
307 end if;
308 if p_addl_instrn_txt is not null and
309 l_bnf_addl_instn_txt_alwd_flag = 'N' then
310 -- raise error as this plan does not allow addl instructions
311 fnd_message.set_name('BEN', 'BEN_91637_ADDL_TXT_NOT_ALWD');
312 fnd_message.raise_error;
313 end if;
314 if l_bnf_pct_amt_alwd_cd = 'PCTA' then
315 if p_amt_dsgd_val is null and p_pct_dsgd_num is null then
316 fnd_message.set_name('BEN', 'BEN_92527_PCT_AMT_NULL');
317 fnd_message.raise_error;
318 end if;
319 end if;
320 if p_amt_dsgd_val is not null then
321 if l_bnf_pct_amt_alwd_cd = 'PCTO' then
322 -- raise error as this plan does not allow to designate amount
323 fnd_message.set_name('BEN', 'BEN_91638_BNF_AMT_NOT_ALWD');
324 fnd_message.raise_error;
325 elsif p_amt_dsgd_val = 0 then
326 -- raise error as this amt is invalid
327 fnd_message.set_name('BEN', 'BEN_92528_INV_AMT_VAL');
328 fnd_message.raise_error;
329 elsif l_bnf_mn_dsgntbl_amt is not null and
330 p_amt_dsgd_val < l_bnf_mn_dsgntbl_amt then
331 -- raise error as this amount is below minimum allowed
332 fnd_message.set_name('BEN', 'BEN_91639_BNF_AMT_MIN_ALWD');
333 fnd_message.set_token('MIN_AMT',l_bnf_mn_dsgntbl_amt);--4455819
334 fnd_message.raise_error;
335 elsif l_bnf_incrmt_amt is not null and
336 mod((p_amt_dsgd_val - nvl(l_bnf_mn_dsgntbl_amt, 0)), l_bnf_incrmt_amt) <> 0 then
337 -- raise error as this amount is not in increments allowed
338 fnd_message.set_name('BEN', 'BEN_91640_BNF_AMT_INCRMT_ALWD');
339 fnd_message.raise_error;
340 end if;
341 end if;
342 if p_pct_dsgd_num is not null then
343 if p_pct_dsgd_num > 100 or p_pct_dsgd_num <= 0 then
344 -- raise error as this pct num is invalid
345 fnd_message.set_name('BEN', 'BEN_91271_INV_PCT_VAL');
346 fnd_message.raise_error;
347 elsif l_bnf_mn_dsgntbl_pct_val is not null and
348 p_pct_dsgd_num < l_bnf_mn_dsgntbl_pct_val then
349 -- raise error as this pct is below minimum allowed
350 if (l_bnf_enrt_oipl_id is not null)
351 then
352 open csr_option_name(l_lf_evt_ocrd_dt);
353 fetch csr_option_name into l_bnf_enrt_option_name;
354 if csr_option_name%notfound then
355 close csr_option_name;
356 -- raise error as corresponding option does not exist in ben_opt_f
357 fnd_message.set_name('ben', 'ben_91641_enrt_rslt_invld');
358 fnd_message.raise_error;
359 end if;
360 close csr_option_name;
361 fnd_message.set_name('BEN', 'BEN_93263_BNF_PCT_MIN_ALWD');
362 fnd_message.set_token('MIN',l_bnf_mn_dsgntbl_pct_val);
366 fnd_message.set_name('BEN', 'BEN_91642_BNF_PCT_MIN_ALWD');
363 fnd_message.set_token('OPT',l_bnf_enrt_option_name);
364 fnd_message.set_token('PL',l_pl_name);
365 else
367
368 -- Added for Bug 1845251
369
370 fnd_message.set_token('MIN',l_bnf_mn_dsgntbl_pct_val);
371 fnd_message.set_token('PL',l_pl_name);
372 end if;
373 fnd_message.raise_error;
374 elsif l_bnf_pct_incrmt_val is not null and
375 mod((p_pct_dsgd_num - nvl(l_bnf_mn_dsgntbl_pct_val, 0)), l_bnf_pct_incrmt_val) <> 0 then
376 -- raise error as this pct is not in increments allowed
377 fnd_message.set_name('BEN', 'BEN_91643_BNF_PCT_INCRMT_ALWD');
378
379 fnd_message.set_token('INCR',l_bnf_pct_incrmt_val);
380 fnd_message.set_token('PL',l_pl_name);
381
382 fnd_message.raise_error;
383 end if;
384 elsif p_amt_dsgd_val is null and
385 l_bnf_mn_dsgntbl_pct_val is not null and
386 nvl(p_pct_dsgd_num,0) < l_bnf_mn_dsgntbl_pct_val then
387 -- raise error as this (null) pct is below minimum allowed
388 -- Bug 1096696
389 if (l_bnf_enrt_oipl_id is not null)
390 then
391 open csr_option_name(l_lf_evt_ocrd_dt);
392 fetch csr_option_name into l_bnf_enrt_option_name;
393 if csr_option_name%notfound then
394 close csr_option_name;
395 -- raise error as corresponding option does not exist in ben_opt_f
396 fnd_message.set_name('ben', 'ben_91641_enrt_rslt_invld');
397 fnd_message.raise_error;
398 end if;
399 close csr_option_name;
400 fnd_message.set_name('BEN', 'BEN_93263_BNF_PCT_MIN_ALWD');
401 fnd_message.set_token('MIN',l_bnf_mn_dsgntbl_pct_val);
402 fnd_message.set_token('OPT',l_bnf_enrt_option_name);
403 fnd_message.set_token('PL',l_pl_name);
404 else
405 fnd_message.set_name('BEN', 'BEN_91642_BNF_PCT_MIN_ALWD');
406
407 -- Added for Bug 1845251
408
409 fnd_message.set_token('MIN',l_bnf_mn_dsgntbl_pct_val);
410 fnd_message.set_token('PL',l_pl_name);
411
412 end if;
413 fnd_message.raise_error;
414 end if;
415 end if;
416 close pl1;
417 hr_utility.set_location('Leaving:'||l_proc,10);
418 End chk_all_pl_bnf_parameters;
419 --
420 --
421 -- ----------------------------------------------------------------------------
422 -- |------< chk_pct_dsgd_num >------|
423 -- ----------------------------------------------------------------------------
424 --
425 -- Description
426 -- This procedure checks that sum of designated % is no greater that 100
427 -- for each beneficiary type for the enrollment result.
428 --
429 -- Pre-Conditions
430 -- None.
431 --
432 -- In Parameters
433 -- p_pl_bnf_id PK
434 -- p_pct_dsgd_num column
435 -- p_effective_date session date
436 -- p_object_version_number object version number
437 --
438 -- Post Success
439 -- Processing continues
440 --
441 -- Post Failure
442 -- Error raised.
443 --
444 -- Access Status
445 -- Internal table handler use only.
446 --
447 Procedure chk_pct_dsgd_num (p_pl_bnf_id in number,
448 p_pct_dsgd_num in number,
449 p_prtt_enrt_rslt_id in number,
450 p_prmry_cntngnt_cd in varchar2,
451 p_validation_start_date in date,
452 p_validation_end_date in date,
453 p_effective_date in date,
454 p_business_group_id in number,
455 p_object_version_number in number) is
456 --
457 l_proc varchar2(72) := g_package||'chk_amt_dsgt_val';
458 l_api_updating boolean;
459 l_sum number(15,2);
460 --
461 --
462 --
463 cursor c1 is
464 select sum(pct_dsgd_num)
465 from ben_pl_bnf_f b
466 where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
467 and b.business_group_id + 0 = p_business_group_id
468 and b.prmry_cntngnt_cd = p_prmry_cntngnt_cd
469 and b.pl_bnf_id <> nvl(p_pl_bnf_id, hr_api.g_number)
470 and p_validation_start_date <= b.effective_end_date
471 and p_validation_end_date >= b.effective_start_date
472 ;
473 --
474 Begin
475 --
476 hr_utility.set_location('Entering:'||l_proc,5);
477 --
478 l_api_updating := ben_pbn_shd.api_updating
479 (p_pl_bnf_id => p_pl_bnf_id,
480 p_effective_date => p_effective_date,
481 p_object_version_number => p_object_version_number);
482 --
483 if p_pct_dsgd_num is not null then
484 if (l_api_updating
485 and nvl(p_pct_dsgd_num, hr_api.g_number)
486 <> nvl(ben_pbn_shd.g_old_rec.pct_dsgd_num, hr_api.g_number)
487 or not l_api_updating) then
488 --
492 --
489 -- check if sum of pct_dsgd_num is less or = 100
490 --
491 open c1;
493 fetch c1 into l_sum;
494 if (l_sum + p_pct_dsgd_num) > 100 then
495 --
496 fnd_message.set_name('BEN', 'BEN_91644_BNF_TTL_PCT_EXCEEDED');
497 fnd_message.raise_error;
498 --
499 --
500 end if;
501 --
502 close c1;
503 --
504 --
505 end if;
506 end if;
507 --
508 hr_utility.set_location('Leaving:'||l_proc,10);
509 --
510 End chk_pct_dsgd_num;
511 --
512 -- ----------------------------------------------------------------------------
513 -- |------< chk_amt_dsgd_val >------|
514 -- ----------------------------------------------------------------------------
515 --
516 -- Description
517 -- This procedure checks that specified amount is no greater that benefit amount
518 -- for the enrollment result.
519 --
520 -- Pre-Conditions
521 -- None.
522 --
523 -- In Parameters
524 -- p_pl_bnf_id PK
525 -- p_amt_dsgd_val column
526 -- p_effective_date session date
527 -- p_object_version_number object version number
528 --
529 -- Post Success
530 -- Processing continues
531 --
532 -- Post Failure
533 -- Error raised.
534 --
535 -- Access Status
536 -- Internal table handler use only.
537 --
538 Procedure chk_amt_dsgd_val (p_pl_bnf_id in number,
539 p_amt_dsgd_val in number,
540 p_prtt_enrt_rslt_id in number,
541 p_prmry_cntngnt_cd in varchar2,
542 p_validation_start_date in date,
543 p_validation_end_date in date,
544 p_effective_date in date,
545 p_business_group_id in number,
546 p_object_version_number in number) is
547 --
548 l_proc varchar2(72) := g_package||'chk_amt_dsgt_val';
549 l_api_updating boolean;
550 l_amt number(15,2);
551 l_sum number(15,2);
552 --
553 --
554 cursor c1 is
555 select bnft_amt
556 from ben_prtt_enrt_rslt_f a
557 where a.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
558 and a.prtt_enrt_rslt_stat_cd is null
559 and a.business_group_id + 0 = p_business_group_id
560 and p_validation_start_date <= effective_end_date
561 and p_validation_end_date >= effective_start_date
562 ;
563 --
564 cursor c2 is
565 select sum(amt_dsgd_val)
566 from ben_pl_bnf_f b
567 where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
568 and b.business_group_id + 0 = p_business_group_id
569 and b.prmry_cntngnt_cd = p_prmry_cntngnt_cd
570 and b.pl_bnf_id <> nvl(p_pl_bnf_id, hr_api.g_number)
571 and p_validation_start_date <=b.effective_end_date
572 and p_validation_end_date >= b.effective_start_date
573 ;
574 --
575 Begin
576 --
577 hr_utility.set_location('Entering:'||l_proc,5);
578 --
579 l_api_updating := ben_pbn_shd.api_updating
580 (p_pl_bnf_id => p_pl_bnf_id,
581 p_effective_date => p_effective_date,
582 p_object_version_number => p_object_version_number);
583 --
584 if p_amt_dsgd_val is not null then
585 if (l_api_updating
586 and nvl(p_amt_dsgd_val,hr_api.g_number)
587 <> nvl(ben_pbn_shd.g_old_rec.amt_dsgd_val, hr_api.g_number)
588 or not l_api_updating) then
589 --
590 -- check if sum of amt_dsgd_val is less or = bnft_amt value on ben_prtt_enrt_rslt_f table
591 --
592 open c1;
593 --
594 fetch c1 into l_amt;
595 if c1%notfound then
596 --
597 close c1;
598 --
599 -- raise error as corresponding rslt does not exist on ben_prtt_enrt_rslt_f
600 -- table.
601 --
602 ben_pbn_shd.constraint_error('BEN_PL_BNF_FK4');
603 else
604 --
605 open c2;
606 fetch c2 into l_sum;
607 if (l_sum + p_amt_dsgd_val) > nvl(l_amt, 0) then
608 --
609 fnd_message.set_name('BEN', 'BEN_91645_BNF_TTL_AMT_EXCEEDED');
610 fnd_message.raise_error;
611 --
612 close c2;
613 --
614 end if;
615 end if;
616 --
617 close c1;
618 --
619 --
620 end if;
621 end if;
622 --
623 hr_utility.set_location('Leaving:'||l_proc,10);
624 --
625 End chk_amt_dsgd_val;
626 --
627 -- ----------------------------------------------------------------------------
628 -- |------< chk_amt_dsgd_uom >------|
629 -- ----------------------------------------------------------------------------
630 --
631 -- Description
632 -- This procedure is used to check that the lookup value is valid.
633 --
634 -- Pre Conditions
635 -- None.
636 --
637 -- In Parameters
638 -- pl_bnf_id PK of record being inserted or updated.
642 -- inserted or updated.
639 -- amt_dsgd_uom Value of lookup code.
640 -- effective_date effective date
641 -- object_version_number Object version number of record being
643 --
644 -- Post Success
645 -- Processing continues
646 --
647 -- Post Failure
648 -- Error handled by procedure
649 --
650 -- Access Status
651 -- Internal table handler use only.
652 --
653 Procedure chk_amt_dsgd_uom(p_pl_bnf_id in number,
654 p_amt_dsgd_uom in varchar2,
655 p_prtt_enrt_rslt_id in number,
656 p_effective_date in date,
657 p_business_group_id in number,
658 p_object_version_number in number) is
659 --
660 l_proc varchar2(72) := g_package||'chk_amt_dsgd_uom';
661 l_api_updating boolean;
662 l_uom varchar2(30);
663 --
664 cursor c1 is
665 select uom
666 from ben_prtt_enrt_rslt_f a
667 where a.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
668 and a.prtt_enrt_rslt_stat_cd is null
669 and a.business_group_id + 0 = p_business_group_id
670 and p_effective_date between effective_start_date
671 and effective_end_date
672 ;
673 --
674 Begin
675 --
676 hr_utility.set_location('Entering:'||l_proc, 5);
677 --
678 l_api_updating := ben_pbn_shd.api_updating
679 (p_pl_bnf_id => p_pl_bnf_id,
680 p_effective_date => p_effective_date,
681 p_object_version_number => p_object_version_number);
682 --
683 if p_amt_dsgd_uom is not null then
684 if (l_api_updating
685 and p_amt_dsgd_uom
686 <> nvl(ben_pbn_shd.g_old_rec.amt_dsgd_uom,hr_api.g_varchar2)
687 or not l_api_updating)
688 and p_amt_dsgd_uom is not null then
689 --
690 -- check if value of lookup falls within lookup type.
691 --
692 --
693 open c1;
694 --
695 fetch c1 into l_uom;
696 if c1%notfound then
697 --
698 close c1;
699 --
700 -- raise error as corresponding rslt does not exist on ben_prtt_enrt_rslt_f
701 -- table.
702 --
703 ben_pbn_shd.constraint_error('BEN_PL_BNF_FK4');
704 elsif p_amt_dsgd_uom <> l_uom then
705 --
706 fnd_message.set_name('BEN', 'BEN_91647_BNF_AMT_UOM_INVALID');
707 fnd_message.raise_error;
708 --
709 --
710 end if;
711 --
712 close c1;
713 --
714 end if;
715 end if;
716 --
717 hr_utility.set_location('Leaving:'||l_proc,10);
718 --
719 end chk_amt_dsgd_uom;
720 --
721 --
722 -- ----------------------------------------------------------------------------
723 -- |------< chk_bnf_person_id >------|
724 -- ----------------------------------------------------------------------------
725 --
726 -- Description
727 -- This procedure checks that a referenced foreign key actually exists
728 -- in the referenced table.
729 --
730 -- Pre-Conditions
731 -- None.
732 --
733 -- In Parameters
734 -- p_pl_bnf_id PK
735 -- p_bnf_person_id ID of FK column
736 -- p_effective_date session date
737 -- p_object_version_number object version number
738 --
739 -- Post Success
740 -- Processing continues
741 --
742 -- Post Failure
743 -- Error raised.
744 --
745 -- Access Status
746 -- Internal table handler use only.
747 --
748 Procedure chk_bnf_person_id (p_pl_bnf_id in number,
749 p_bnf_person_id in number,
750 p_prtt_enrt_rslt_id in number,
751 p_validation_start_date in date,
752 p_validation_end_date in date,
753 p_effective_date in date,
754 p_business_group_id in number,
755 p_object_version_number in number) is
756 --
757 l_proc varchar2(72) := g_package||'chk_bnf_person_id';
758 l_api_updating boolean;
759 l_dummy varchar2(1);
760 l_exists varchar2(1);
761 --
762 -- Bug 1776842 : Do not consider the benficiary rowsattached to the
763 -- backed out per in ler.
764 --
765 cursor c3 is
766 select null
767 from ben_pl_bnf_f pbn,
768 ben_per_in_ler pil
769 where pbn.bnf_person_id = p_bnf_person_id
770 and pil.per_in_ler_id(+)=pbn.per_in_ler_id and
771 pil.business_group_id(+)=pbn.business_group_id
772 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
773 or pil.per_in_ler_stat_cd is null )
774 and pbn.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
775 and pbn.pl_bnf_id <> nvl(p_pl_bnf_id, hr_api.g_number)
776 and pbn.business_group_id + 0 = p_business_group_id
777 and p_validation_start_date <= pbn.effective_end_date
778 and p_validation_end_date >= pbn.effective_start_date
782 cursor c1 is
779 ;
780 --
781 --
783 select null
784 from per_all_people_f a
785 where a.person_id = p_bnf_person_id
786 and a.business_group_id + 0 = p_business_group_id
787 and p_validation_start_date <= effective_end_date
788 and p_validation_end_date >= effective_start_date
789 ;
790 --
791 Begin
792 --
793 hr_utility.set_location('Entering:'||l_proc,5);
794 --
795 l_api_updating := ben_pbn_shd.api_updating
796 (p_pl_bnf_id => p_pl_bnf_id,
797 p_effective_date => p_effective_date,
798 p_object_version_number => p_object_version_number);
799 --
800 if p_bnf_person_id is not null then
801 if (l_api_updating
802 and nvl(p_bnf_person_id,hr_api.g_number)
803 <> nvl(ben_pbn_shd.g_old_rec.bnf_person_id, hr_api.g_number)
804 or not l_api_updating) then
805 --
806 -- check if bnf_person_id value exists in per_all_people_f table
807 --
808 open c1;
809 --
810 fetch c1 into l_dummy;
811 if c1%notfound then
812 --
813 close c1;
814 --
815 -- raise error as FK does not relate to PK in per_all_people
816 -- table.
817 --
818 ben_pbn_shd.constraint_error('BEN_PL_BNF_FK1');
819 --
820 end if;
821 --
822 close c1;
823 --
824 open c3;
825 fetch c3 into l_exists;
826 if c3%found then
827 close c3;
828 --
829 -- raise error as this beneficiary already exists for this enrt rslt
830 --
831 fnd_message.set_name('BEN', 'BEN_91648_DUP_PL_BNF');
832 fnd_message.raise_error;
833 --
834 end if;
835 close c3;
836 --
837 end if;
838 end if;
839 --
840 hr_utility.set_location('Leaving:'||l_proc,10);
841 --
842 End chk_bnf_person_id;
843 --
844 --
845 -- ----------------------------------------------------------------------------
846 -- |------< chk_ttee_person_id >------|
847 -- ----------------------------------------------------------------------------
848 --
849 -- Description
850 -- This procedure checks that a referenced foreign key actually exists
851 -- in the referenced table.
852 --
853 -- Pre-Conditions
854 -- None.
855 --
856 -- In Parameters
857 -- p_pl_bnf_id PK
858 -- p_ttee_person_id ID of FK column
859 -- p_effective_date session date
860 -- p_object_version_number object version number
861 --
862 -- Post Success
863 -- Processing continues
864 --
865 -- Post Failure
866 -- Error raised.
867 --
868 -- Access Status
869 -- Internal table handler use only.
870 --
871 Procedure chk_ttee_person_id (p_pl_bnf_id in number,
872 p_ttee_person_id in number,
873 p_bnf_person_id in number,
874 p_validation_start_date in date,
875 p_validation_end_date in date,
876 p_effective_date in date,
877 p_business_group_id in number,
878 p_object_version_number in number) is
879 --
880 l_proc varchar2(72) := g_package||'chk_bnf_person_id';
881 l_api_updating boolean;
882 l_dummy varchar2(1);
883 --
884 --
885 --
886 cursor c1 is
887 select null
888 from per_all_people a, per_contact_relationships c
889 where a.person_id = p_ttee_person_id
890 and a.person_id = c.contact_person_id
891 and c.person_id = p_bnf_person_id
892 and p_validation_start_date <= nvl(c.date_end, p_validation_start_date)
893 and p_validation_end_date >= nvl(c.date_start, p_validation_start_date)
894 and a.business_group_id + 0 = p_business_group_id
895 and p_validation_start_date <= a.effective_end_date
896 and p_validation_end_date >= a.effective_start_date
897 ;
898 --
899 Begin
900 --
901 hr_utility.set_location('Entering:'||l_proc,5);
902 --
903 l_api_updating := ben_pbn_shd.api_updating
904 (p_pl_bnf_id => p_pl_bnf_id,
905 p_effective_date => p_effective_date,
906 p_object_version_number => p_object_version_number);
907 --
908 if p_ttee_person_id is not null then
909 if (l_api_updating
910 and nvl(p_ttee_person_id,hr_api.g_number)
911 <> nvl(ben_pbn_shd.g_old_rec.ttee_person_id, hr_api.g_number)
912 or not l_api_updating) then
913 --
914 -- check if ttee_person_id value exists in per_all_people_f table
915 --
916 open c1;
917 --
918 fetch c1 into l_dummy;
919 if c1%notfound then
920 --
921 close c1;
922 --
923 -- raise error as FK does not relate to PK in per_all_people
924 -- table.
925 --
926 ben_pbn_shd.constraint_error('BEN_PL_BNF_FK2');
927 --
928 end if;
929 --
930 close c1;
931 --
932 --
933 end if;
937 --
934 end if;
935 --
936 hr_utility.set_location('Leaving:'||l_proc,10);
938 End chk_ttee_person_id;
939 --
940 --
941 -- ----------------------------------------------------------------------------
942 -- |------------------< chk_bnf_dsgn_rqmt_relnshp_typ >-----------------------|
943 -- ----------------------------------------------------------------------------
944 --
945 -- Description
946 -- This procedure checks that the designated beneficary's relationship is valid
947 -- as per the designation requirements given at Option / option in Plan / Plan levels
948 -- in that order.
949 -- This procedure replaces the following procedure with the same name
950 -- This check procedure has been added to fix bugs 2493806 and 2367632 .
951 -- This is called from insert_validate and update_validate procedures.
952 --
953 -- Pre-Conditions
954 -- None.
955 --
956 -- In Parameters
957 -- p_pl_bnf_id PK
958 -- p_bnf_person_id ID of contact perosn who has been designated as beneficiary
959 -- p_per_in_ler_id per_in_ler_id
960 -- p_prtt_enrt_rslt_id participant enrollment result ID used to get PL /OIPL / OPT details
961 -- p_business_group_id business_group_id
962 -- p_effective_date session date
963 -- p_object_version_number object version number
964 --
965 -- Post Success
966 -- Processing continues
967 --
968 -- Post Failure
969 -- Error raised.
970 --
971 -- Access Status
972 -- Internal table handler use only.
973 --
974 Procedure chk_bnf_dsgn_rqmt_relnshp_typ (p_pl_bnf_id in number,
975 p_bnf_person_id in number,
976 p_per_in_ler_id in number,
977 p_prtt_enrt_rslt_id in number,
978 p_business_group_id in number,
979 p_effective_date in date,
980 p_object_version_number in number) is
981 --
982 l_proc varchar2(72) := g_package||'chk_bnf_dsgn_rqmt_relnshp_typ';
983 l_api_updating boolean;
984 l_dummy varchar2(1);
985 l_rel_typ varchar2(30);
986 l_rlshp_typ_cd varchar2(30);
987 l_lkup_meaning_reln_type varchar2(80);
988 l_pl_id number;
989 l_oipl_id number;
990 l_opt_id number;
991 --
992 --
993 cursor c_opt_dsgn_rqmt is
994 select distinct drt.rlshp_typ_cd
995 from ben_dsgn_rqmt_rlshp_typ drt
996 , ben_dsgn_rqmt_f drm
997 , ben_opt_f opt
998 , ben_oipl_f oipl
999 , ben_prtt_enrt_rslt_f pen
1000 where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
1001 and drt.business_group_id = p_business_group_id
1002 and drm.dsgn_typ_cd = 'BNF'
1003 and drm.business_group_id = p_business_group_id
1004 and pen.prtt_enrt_rslt_stat_cd is null
1005 and p_effective_date between drm.effective_start_date and drm.effective_end_date
1006 and opt.opt_id = nvl(drm.opt_id, -1)
1007 and opt.business_group_id = p_business_group_id
1008 and p_effective_date between opt.effective_start_date and opt.effective_end_date
1009 and oipl.opt_id = opt.opt_id
1010 and oipl.business_group_id = p_business_group_id
1011 and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
1012 and pen.oipl_id = oipl.oipl_id
1013 and pen.business_group_id = p_business_group_id
1014 and p_effective_date between pen.effective_start_date and pen.effective_end_date
1015 and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id ;
1016 --
1017 cursor c_oipl_dsgn_rqmt is
1018 select distinct drt.rlshp_typ_cd
1019 from ben_dsgn_rqmt_rlshp_typ drt
1020 , ben_dsgn_rqmt_f drm
1021 , ben_oipl_f oipl
1022 , ben_prtt_enrt_rslt_f pen
1023 where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
1024 and drt.business_group_id = p_business_group_id
1025 and drm.dsgn_typ_cd = 'BNF'
1026 and drm.business_group_id = p_business_group_id
1027 and p_effective_date between drm.effective_start_date and drm.effective_end_date
1028 and oipl.oipl_id = nvl(drm.oipl_id, -1)
1029 and oipl.business_group_id = p_business_group_id
1030 and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
1031 and pen.oipl_id = oipl.oipl_id
1032 and pen.business_group_id = p_business_group_id
1033 and p_effective_date between pen.effective_start_date and pen.effective_end_date
1034 and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1035 and pen.prtt_enrt_rslt_stat_cd is null;
1036 --
1037 cursor c_pl_dsgn_rqmt is
1038 select distinct drt.rlshp_typ_cd
1039 from ben_dsgn_rqmt_rlshp_typ drt
1040 , ben_dsgn_rqmt_f drm
1041 , ben_pl_f pln
1042 , ben_prtt_enrt_rslt_f pen
1043 where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
1044 and drt.business_group_id = p_business_group_id
1045 and drm.dsgn_typ_cd = 'BNF'
1046 and drm.business_group_id = p_business_group_id
1047 and p_effective_date between drm.effective_start_date and drm.effective_end_date
1048 and pln.pl_id = nvl(drm.pl_id, -1)
1049 and pln.business_group_id = p_business_group_id
1050 and p_effective_date between pln.effective_start_date and pln.effective_end_date
1051 and pen.pl_id = pln.pl_id
1052 and pen.business_group_id = p_business_group_id
1053 and p_effective_date between pen.effective_start_date and pen.effective_end_date
1057 cursor c_bnf_person_self is
1054 and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1055 and pen.prtt_enrt_rslt_stat_cd is null;
1056 --
1058 select null
1059 from ben_per_in_ler pil
1060 where pil.per_in_ler_id = p_per_in_ler_id
1061 and pil.business_group_id = p_business_group_id
1062 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1063 or pil.per_in_ler_stat_cd is null )
1064 and pil.person_id = p_bnf_person_id ;
1065 --
1066 --- # 3212439 parametrer added to find the relationship
1067 cursor c_bnf_person_rel_typ (c_contact_type varchar2) is
1068 select pcr.contact_type,
1069 hll.meaning
1070 from per_contact_relationships pcr
1071 , ben_per_in_ler pil
1072 , hr_leg_lookups hll
1073 where pil.per_in_ler_id = p_per_in_ler_id
1074 and pil.business_group_id = p_business_group_id
1075 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1076 or pil.per_in_ler_stat_cd is null )
1077 and pcr.business_group_id = p_business_group_id
1078 and pcr.person_id = pil.person_id
1079 and pcr.contact_person_id = p_bnf_person_id
1080 and pcr.contact_type = c_contact_type
1081 and p_effective_date between nvl(pcr.date_start,p_effective_date) and nvl(pcr.date_end,p_effective_date)
1082 and hll.lookup_type = 'CONTACT'
1083 and p_effective_date
1084 between nvl(hll.start_date_active,p_effective_date) and nvl(hll.end_date_active,p_effective_date)
1085 and pcr.contact_type = hll.lookup_code ;
1086 --
1087 cursor c_pl_oipl is
1088 select pl_id, oipl_id
1089 from ben_prtt_enrt_rslt_f pen
1090 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1091 and pen.prtt_enrt_rslt_stat_cd is null
1092 and pen.business_group_id = p_business_group_id
1093 and p_effective_date between pen.effective_start_date and pen.effective_end_date ;
1094 --
1095 cursor c_opt (p_oipl_id number) is
1096 select oipl.opt_id
1097 from ben_oipl_f oipl
1098 where oipl.oipl_id = p_oipl_id
1099 and oipl.business_group_id = p_business_group_id
1100 and p_effective_date between oipl.effective_start_date and oipl.effective_end_date ;
1101
1102 Procedure raise_error is
1103 --
1104 cursor c_pl_name (p_pl_id number) is
1105 select name
1106 from ben_pl_f
1107 where pl_id=p_pl_id
1108 and p_effective_date between effective_start_date and effective_end_date ;
1109 --
1110 cursor c_opt_name (p_opt_id number) is
1111 select name
1112 from ben_opt_f
1113 where opt_id=p_opt_id
1114 and p_effective_date between effective_start_date and effective_end_date ;
1115 --
1116 l_pl_name ben_pl_f.name%TYPE;
1117 l_opt_name ben_opt_f.name%TYPE;
1118 Begin
1119
1120 open c_pl_oipl;
1121 fetch c_pl_oipl into l_pl_id, l_oipl_id;
1122 close c_pl_oipl;
1123 --
1124 if l_oipl_id is not null then
1125 open c_opt(l_oipl_id);
1126 fetch c_opt into l_opt_id;
1127 close c_opt;
1128 end if;
1129 --
1130 --Bug 2869639: we will display Plan and option names instead of Id's
1131 --
1132 open c_pl_name (l_pl_id);
1133 fetch c_pl_name into l_pl_name;
1134 close c_pl_name;
1135 if l_opt_id is not NULL then
1136 open c_opt_name (l_opt_id);
1137 fetch c_opt_name into l_opt_name;
1138 close c_opt_name;
1139 end if;
1140 --
1141 -- Bug 2869639 Added separate message when option id is null
1142 if l_opt_id is not NULL then
1143 fnd_message.set_name('BEN', 'BEN_93049_INVLD_BNF_CNTCT_TYPE');
1144 fnd_message.set_token('RLTYP', nvl(l_lkup_meaning_reln_type,''));
1145 fnd_message.set_token('PL_ID', l_pl_name );
1146 -- fnd_message.set_token('OIPL_ID', to_char(l_oipl_id) );
1147 fnd_message.set_token('OPT_ID', l_opt_name );
1148 else
1149 fnd_message.set_name('BEN', 'BEN_93904_INVLD_BNF_CNTCT_T_PL');
1150 fnd_message.set_token('RLTYP', nvl(l_lkup_meaning_reln_type,''));
1151 fnd_message.set_token('PL_ID', l_pl_name );
1152 end if;
1153 --
1154 fnd_message.raise_error;
1155
1156 End;
1157
1158 Begin
1159 --
1160 hr_utility.set_location('Entering:'||l_proc,5);
1161 --
1162 l_api_updating := ben_pbn_shd.api_updating
1163 (p_pl_bnf_id => p_pl_bnf_id,
1164 p_effective_date => p_effective_date,
1165 p_object_version_number => p_object_version_number);
1166
1167 --
1168 -- Check if beneficiary is person or organisation, if not person then return
1169 --
1170 if p_bnf_person_id is null then
1171 --
1172 return ;
1173 --
1174 end if;
1175 --
1176 --
1177 -- Check if beneficiary is the person (employee) himself
1178 --
1179 open c_bnf_person_self;
1180 --
1181 fetch c_bnf_person_self into l_dummy;
1182 if c_bnf_person_self%found then
1183 --
1184 close c_bnf_person_self;
1185 --
1186 -- person (employee) himself is the beneficiary
1187 -- which is a valid case and no further validation reqd
1188 --
1189 return;
1190 --
1191 end if;
1192 --
1193 close c_bnf_person_self;
1194 --
1195 -- get contact relationship type of the person
1199 -- fetch c_bnf_person_rel_typ into l_rel_typ,l_lkup_meaning_reln_type ;
1196 --
1197 --- # 3212439 this cursor moved to inside loop to find each relation in rqmt
1198 -- open c_bnf_person_rel_typ;
1200 -- close c_bnf_person_rel_typ;
1201 --
1202 -- check if the designated beneficiary has a contact type provided
1203 -- at option level designation requirement.
1204 --
1205 hr_utility.set_location('l_rel_typ' || l_rel_typ ,07);
1206 hr_utility.set_location('l_lkup_meaning_reln_type' || l_lkup_meaning_reln_type ,07);
1207 open c_opt_dsgn_rqmt;
1208 loop
1209 fetch c_opt_dsgn_rqmt into l_rlshp_typ_cd;
1210 if c_opt_dsgn_rqmt%notfound then
1211 if c_opt_dsgn_rqmt%rowcount > 0 then
1212 close c_opt_dsgn_rqmt ;
1213 raise_error;
1214 end if;
1215 exit;
1216 end if;
1217 -- 3212439
1218 hr_utility.set_location('l_rlshp_typ_cd' || l_rlshp_typ_cd ,07);
1219 open c_bnf_person_rel_typ (l_rlshp_typ_cd);
1220 fetch c_bnf_person_rel_typ into l_rel_typ,l_lkup_meaning_reln_type ;
1221 hr_utility.set_location('l_rel_typ' || l_rel_typ ,07);
1222 hr_utility.set_location('l_lkup_meaning_reln_type' || l_lkup_meaning_reln_type ,07);
1223 if c_bnf_person_rel_typ%found then
1224 close c_bnf_person_rel_typ ;
1225 close c_opt_dsgn_rqmt ;
1226 return;
1227 end if ;
1228 close c_bnf_person_rel_typ;
1229
1230
1231 end loop;
1232 close c_opt_dsgn_rqmt ;
1233 --
1234 -- check if the designated beneficiary has a contact type provided
1235 -- at option in plan level designation requirement.
1236 --
1237 hr_utility.set_location(l_proc,10);
1238 open c_oipl_dsgn_rqmt;
1239 loop
1240 fetch c_oipl_dsgn_rqmt into l_rlshp_typ_cd;
1241 if c_oipl_dsgn_rqmt%notfound then
1242 if c_oipl_dsgn_rqmt%rowcount > 0 then
1243 close c_oipl_dsgn_rqmt ;
1244 raise_error;
1245 end if;
1246 exit;
1247 end if;
1248
1249 -- 3212439
1250 hr_utility.set_location('l_rlshp_typ_cd' || l_rlshp_typ_cd ,08);
1251 open c_bnf_person_rel_typ (l_rlshp_typ_cd);
1252 fetch c_bnf_person_rel_typ into l_rel_typ,l_lkup_meaning_reln_type ;
1253 hr_utility.set_location('l_rel_typ' || l_rel_typ ,08);
1254 hr_utility.set_location('l_lkup_meaning_reln_type' || l_lkup_meaning_reln_type ,08);
1255 if c_bnf_person_rel_typ%found then
1256 close c_bnf_person_rel_typ ;
1257 close c_oipl_dsgn_rqmt ;
1258 return;
1259 end if ;
1260 close c_bnf_person_rel_typ;
1261
1262
1263 -- if l_rlshp_typ_cd = l_rel_typ then
1264 -- close c_oipl_dsgn_rqmt ;
1265 -- return;
1266 -- end if;
1267
1268 end loop;
1269 close c_oipl_dsgn_rqmt ;
1270 --
1271 -- check if the designated beneficiary has a contact type provided
1272 -- at plan level designation requirement.
1273 --
1274 hr_utility.set_location(l_proc,15);
1275 open c_pl_dsgn_rqmt;
1276 loop
1277 fetch c_pl_dsgn_rqmt into l_rlshp_typ_cd;
1278 if c_pl_dsgn_rqmt%notfound then
1279 if c_pl_dsgn_rqmt%rowcount > 0 then
1280 close c_pl_dsgn_rqmt ;
1281 raise_error;
1282 end if;
1283 exit;
1284 end if;
1285
1286 -- 3212439
1287 hr_utility.set_location('l_rlshp_typ_cd' || l_rlshp_typ_cd ,09);
1288 open c_bnf_person_rel_typ (l_rlshp_typ_cd);
1289 fetch c_bnf_person_rel_typ into l_rel_typ,l_lkup_meaning_reln_type ;
1290 hr_utility.set_location('l_rel_typ' || l_rel_typ ,09);
1291 hr_utility.set_location('l_lkup_meaning_reln_type' || l_lkup_meaning_reln_type ,09);
1292 if c_bnf_person_rel_typ%found then
1293 close c_bnf_person_rel_typ ;
1294 close c_pl_dsgn_rqmt ;
1295 return;
1296 end if ;
1297 close c_bnf_person_rel_typ;
1298
1299
1300 -- if l_rlshp_typ_cd = l_rel_typ then
1301 -- close c_pl_dsgn_rqmt ;
1302 -- return;
1303 -- end if;
1304
1305 end loop;
1306 close c_pl_dsgn_rqmt ;
1307 hr_utility.set_location('Leaving:'||l_proc,40);
1308 --
1309 End chk_bnf_dsgn_rqmt_relnshp_typ ;
1310 --
1311 --
1312 /*-- ----------------------------------------------------------------------------
1313 -- |------------------< chk_bnf_dsgn_rqmt_relnshp_typ >-----------------------|
1314 -- ----------------------------------------------------------------------------
1315 --
1316 -- Description
1317 -- This procedure checks that the designated beneficary's relationship is valid
1318 -- as per the designation requirements given at Option / option in Plan / Plan levels
1319 -- in that order.
1320 -- This check procedure has been added to fix bug 2367632 - - .
1321 -- This is called from insert_validate and update_validate procedures.
1322 --
1323 -- Pre-Conditions
1324 -- None.
1325 --
1326 -- In Parameters
1327 -- p_pl_bnf_id PK
1328 -- p_bnf_person_id ID of contact perosn who has been designated as beneficiary
1329 -- p_per_in_ler_id per_in_ler_id
1330 -- p_prtt_enrt_rslt_id participant enrollment result ID used to get PL /OIPL / OPT details
1331 -- p_business_group_id business_group_id
1332 -- p_effective_date session date
1333 -- p_object_version_number object version number
1334 --
1338 -- Post Failure
1335 -- Post Success
1336 -- Processing continues
1337 --
1339 -- Error raised.
1340 --
1341 -- Access Status
1342 -- Internal table handler use only.
1343 --
1344 Procedure chk_bnf_dsgn_rqmt_relnshp_typ (p_pl_bnf_id in number,
1345 p_bnf_person_id in number,
1346 p_per_in_ler_id in number,
1347 p_prtt_enrt_rslt_id in number,
1348 p_business_group_id in number,
1349 p_effective_date in date,
1350 p_object_version_number in number) is
1351 --
1352 l_proc varchar2(72) := g_package||'chk_bnf_dsgn_rqmt_relnshp_typ';
1353 l_api_updating boolean;
1354 l_dummy varchar2(1);
1355 l_lkup_meaning_reln_type varchar2(80);
1356 l_pl_id number;
1357 l_oipl_id number;
1358 l_opt_id number;
1359 --
1360 --
1361 cursor c_opt_dsgn_rqmt is
1362 select null
1363 from per_contact_relationships pcr
1364 , ben_per_in_ler pil
1365 where pil.per_in_ler_id = p_per_in_ler_id
1366 and pil.business_group_id = p_business_group_id
1367 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1368 or pil.per_in_ler_stat_cd is null )
1369 and pcr.business_group_id = p_business_group_id
1370 and pcr.person_id = pil.person_id
1371 and pcr.contact_person_id = p_bnf_person_id
1372 and p_effective_date between nvl(pcr.date_start,p_effective_date) and nvl(pcr.date_end,p_effective_date)
1373 and pcr.contact_type in
1374 (select distinct drt.rlshp_typ_cd
1375 from ben_dsgn_rqmt_rlshp_typ drt
1376 , ben_dsgn_rqmt_f drm
1377 , ben_opt_f opt
1378 , ben_oipl_f oipl
1379 , ben_prtt_enrt_rslt_f pen
1380 where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
1381 and drt.business_group_id = p_business_group_id
1382 and drm.dsgn_typ_cd = 'BNF'
1383 and drm.business_group_id = p_business_group_id
1384 and p_effective_date between drm.effective_start_date and drm.effective_end_date
1385 and opt.opt_id = nvl(drm.opt_id, -1)
1386 and opt.business_group_id = p_business_group_id
1387 and p_effective_date between opt.effective_start_date and opt.effective_end_date
1388 and oipl.opt_id = opt.opt_id
1389 and oipl.business_group_id = p_business_group_id
1390 and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
1391 and pen.oipl_id = oipl.oipl_id
1392 and pen.business_group_id = p_business_group_id
1393 and p_effective_date between pen.effective_start_date and pen.effective_end_date
1394 and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id ) ;
1395 --
1396 cursor c_oipl_dsgn_rqmt is
1397 select null
1398 from per_contact_relationships pcr
1399 , ben_per_in_ler pil
1400 where pil.per_in_ler_id = p_per_in_ler_id
1401 and pil.business_group_id = p_business_group_id
1402 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1403 or pil.per_in_ler_stat_cd is null )
1404 and pcr.business_group_id = p_business_group_id
1405 and pcr.person_id = pil.person_id
1406 and pcr.contact_person_id = p_bnf_person_id
1407 and p_effective_date between nvl(pcr.date_start,p_effective_date) and nvl(pcr.date_end,p_effective_date)
1408 and pcr.contact_type in
1409 (select distinct drt.rlshp_typ_cd
1410 from ben_dsgn_rqmt_rlshp_typ drt
1411 , ben_dsgn_rqmt_f drm
1412 , ben_oipl_f oipl
1413 , ben_prtt_enrt_rslt_f pen
1414 where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
1415 and drt.business_group_id = p_business_group_id
1416 and drm.dsgn_typ_cd = 'BNF'
1417 and drm.business_group_id = p_business_group_id
1418 and p_effective_date between drm.effective_start_date and drm.effective_end_date
1419 and oipl.oipl_id = nvl(drm.oipl_id, -1)
1420 and oipl.business_group_id = p_business_group_id
1421 and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
1422 and pen.oipl_id = oipl.oipl_id
1423 and pen.business_group_id = p_business_group_id
1424 and p_effective_date between pen.effective_start_date and pen.effective_end_date
1425 and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id ) ;
1426 --
1427 cursor c_pl_dsgn_rqmt is
1428 select null
1429 from per_contact_relationships pcr
1430 , ben_per_in_ler pil
1431 where pil.per_in_ler_id = p_per_in_ler_id
1432 and pil.business_group_id = p_business_group_id
1433 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1434 or pil.per_in_ler_stat_cd is null )
1435 and pcr.business_group_id = p_business_group_id
1436 and pcr.person_id = pil.person_id
1437 and pcr.contact_person_id = p_bnf_person_id
1438 and p_effective_date between nvl(pcr.date_start,p_effective_date) and nvl(pcr.date_end,p_effective_date)
1439 and pcr.contact_type in
1440 (select distinct drt.rlshp_typ_cd
1441 from ben_dsgn_rqmt_rlshp_typ drt
1442 , ben_dsgn_rqmt_f drm
1446 and drt.business_group_id = p_business_group_id
1443 , ben_pl_f pln
1444 , ben_prtt_enrt_rslt_f pen
1445 where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
1447 and drm.dsgn_typ_cd = 'BNF'
1448 and drm.business_group_id = p_business_group_id
1449 and p_effective_date between drm.effective_start_date and drm.effective_end_date
1450 and pln.pl_id = nvl(drm.pl_id, -1)
1451 and pln.business_group_id = p_business_group_id
1452 and p_effective_date between pln.effective_start_date and pln.effective_end_date
1453 and pen.pl_id = pln.pl_id
1454 and pen.business_group_id = p_business_group_id
1455 and p_effective_date between pen.effective_start_date and pen.effective_end_date
1456 and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id ) ;
1457 --
1458 cursor c_bnf_person_self is
1459 select null
1460 from ben_per_in_ler pil
1461 where pil.per_in_ler_id = p_per_in_ler_id
1462 and pil.business_group_id = p_business_group_id
1463 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1464 or pil.per_in_ler_stat_cd is null )
1465 and pil.person_id = p_bnf_person_id ;
1466 --
1467 cursor c_bnf_person_rel_typ is
1468 select hll.meaning
1469 from per_contact_relationships pcr
1470 , ben_per_in_ler pil
1471 , hr_leg_lookups hll
1472 where pil.per_in_ler_id = p_per_in_ler_id
1473 and pil.business_group_id = p_business_group_id
1474 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1475 or pil.per_in_ler_stat_cd is null )
1476 and pcr.business_group_id = p_business_group_id
1477 and pcr.person_id = pil.person_id
1478 and pcr.contact_person_id = p_bnf_person_id
1479 and p_effective_date between nvl(pcr.date_start,p_effective_date) and nvl(pcr.date_end,p_effective_date)
1480 and hll.lookup_type = 'CONTACT'
1481 and p_effective_date
1482 between nvl(hll.start_date_active,p_effective_date) and nvl(hll.end_date_active,p_effective_date)
1483 and pcr.contact_type = hll.lookup_code ;
1484 --
1485 cursor c_pl_oipl is
1486 select pl_id, oipl_id
1487 from ben_prtt_enrt_rslt_f pen
1488 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1489 and pen.business_group_id = p_business_group_id
1490 and p_effective_date between pen.effective_start_date and pen.effective_end_date ;
1491 --
1492 cursor c_opt (p_oipl_id number) is
1493 select oipl.opt_id
1494 from ben_oipl_f oipl
1495 where oipl.oipl_id = p_oipl_id
1496 and oipl.business_group_id = p_business_group_id
1497 and p_effective_date between oipl.effective_start_date and oipl.effective_end_date ;
1498
1499 Begin
1500 --
1501 hr_utility.set_location('Entering:'||l_proc,5);
1502 --
1503 l_api_updating := ben_pbn_shd.api_updating
1504 (p_pl_bnf_id => p_pl_bnf_id,
1505 p_effective_date => p_effective_date,
1506 p_object_version_number => p_object_version_number);
1507
1508 --
1509 -- Check if beneficiary is person or organisation, if not person then return
1510 --
1511 if p_bnf_person_id is null then
1512 --
1513 return ;
1514 --
1515 end if;
1516 --
1517
1518 --
1519 -- Check if beneficiary is the person (employee) himself
1520 --
1521 open c_bnf_person_self;
1522 --
1523 fetch c_bnf_person_self into l_dummy;
1524 if c_bnf_person_self%found then
1525 --
1526 close c_bnf_person_self;
1527 --
1528 -- person (employee) himself is the beneficiary
1529 -- which is a valid case and no further validation reqd
1530 --
1531 return;
1532 --
1533 end if;
1534 --
1535 close c_bnf_person_self;
1536 --
1537
1538 --
1539 -- check if the designated beneficiary has a contact type provided
1540 -- at option level designation requirement.
1541 --
1542 open c_opt_dsgn_rqmt;
1543 --
1544 fetch c_opt_dsgn_rqmt into l_dummy;
1545 if c_opt_dsgn_rqmt%found then
1546 close c_opt_dsgn_rqmt ;
1547 hr_utility.set_location(l_proc,07);
1548 elsif c_opt_dsgn_rqmt%notfound then
1549 --
1550 hr_utility.set_location(l_proc,10);
1551 close c_opt_dsgn_rqmt;
1552 --
1553 -- check if the designated beneficiary has a contact type provided
1554 -- at option in plan level designation requirement.
1555 --
1556 open c_oipl_dsgn_rqmt;
1557 --
1558 fetch c_oipl_dsgn_rqmt into l_dummy;
1559 if c_oipl_dsgn_rqmt%found then
1560 close c_oipl_dsgn_rqmt ;
1561 hr_utility.set_location(l_proc,15);
1562 elsif c_oipl_dsgn_rqmt%notfound then
1563 --
1564 hr_utility.set_location(l_proc,20);
1565 close c_oipl_dsgn_rqmt;
1566 --
1567 -- check if the designated beneficiary has a contact type provided
1568 -- at plan level designation requirement.
1569 --
1570 open c_pl_dsgn_rqmt;
1571 --
1572 fetch c_pl_dsgn_rqmt into l_dummy;
1573 if c_pl_dsgn_rqmt%notfound then
1577 --
1574 --
1575 hr_utility.set_location(l_proc,30);
1576 close c_pl_dsgn_rqmt;
1578 -- Since the contact type has not been provided as a designation requirement
1579 -- at option / option in plan / plan level, Raise an error that this
1580 -- person cannot be designated as a beneficiary for this plan + option.
1581 --
1582 --
1583 -- get contact relationship type of the person
1584 --
1585 open c_bnf_person_rel_typ;
1586 fetch c_bnf_person_rel_typ into l_lkup_meaning_reln_type ;
1587 if c_bnf_person_rel_typ%found then
1588 fnd_message.set_token('RLTYP', l_lkup_meaning_reln_type);
1589 end if;
1590 close c_bnf_person_rel_typ;
1591 --
1592 -- get plan and oipl ids, if oipl_id is not null then retrieve opt_id also
1593 --
1594 open c_pl_oipl;
1595 fetch c_pl_oipl into l_pl_id, l_oipl_id;
1596 close c_pl_oipl;
1597 --
1598 if l_oipl_id is not null then
1599 open c_opt(l_oipl_id);
1600 fetch c_opt into l_opt_id;
1601 close c_opt;
1602 end if;
1603 --
1604 fnd_message.set_name('BEN', 'BEN_93049_INVLD_BNF_CNTCT_TYPE');
1605 fnd_message.set_token('RLTYP', nvl(l_lkup_meaning_reln_type,''));
1606 fnd_message.set_token('PL_ID', to_char(l_pl_id) );
1607 fnd_message.set_token('OIPL_ID', to_char(l_oipl_id) );
1608 fnd_message.set_token('OPT_ID', to_char(l_opt_id) );
1609 fnd_message.raise_error;
1610 --
1611 end if;
1612 --
1613 close c_pl_dsgn_rqmt;
1614 end if;
1615 --
1616 -- close c_oipl_dsgn_rqmt;
1617 --
1618 end if;
1619 --
1620 -- close c_opt_dsgn_rqmt;
1621 --
1622 hr_utility.set_location('Leaving:'||l_proc,40);
1623 --
1624 End chk_bnf_dsgn_rqmt_relnshp_typ ;
1625 --*/
1626 --
1627 -- ----------------------------------------------------------------------------
1628 -- |------< chk_organization_id >------|
1629 -- ----------------------------------------------------------------------------
1630 --
1631 -- Description
1632 -- This procedure checks that a referenced foreign key actually exists
1633 -- in the referenced table.
1634 --
1635 -- Pre-Conditions
1636 -- None.
1637 --
1638 -- In Parameters
1639 -- p_pl_bnf_id PK
1640 -- p_organization_id ID of FK column
1641 -- p_effective_date session date
1642 -- p_object_version_number object version number
1643 --
1644 -- Post Success
1645 -- Processing continues
1646 --
1647 -- Post Failure
1648 -- Error raised.
1649 --
1650 -- Access Status
1651 -- Internal table handler use only.
1652 --
1653 Procedure chk_organization_id (p_pl_bnf_id in number,
1654 p_organization_id in number,
1655 p_effective_date in date,
1656 p_business_group_id in number,
1657 p_object_version_number in number) is
1658 --
1659 l_proc varchar2(72) := g_package||'chk_bnf_person_id';
1660 l_api_updating boolean;
1661 l_dummy varchar2(1);
1662 l_exists varchar2(1);
1663 --
1664 --
1665 --
1666 cursor c1 is
1667 select null
1668 from hr_all_organization_units a
1669 where a.organization_id = p_organization_id
1670 and a.business_group_id + 0 = p_business_group_id
1671 ;
1672 --
1673 Begin
1674 --
1675 hr_utility.set_location('Entering:'||l_proc,5);
1676 --
1677 l_api_updating := ben_pbn_shd.api_updating
1678 (p_pl_bnf_id => p_pl_bnf_id,
1679 p_effective_date => p_effective_date,
1680 p_object_version_number => p_object_version_number);
1681 --
1682 if p_organization_id is not null then
1683 if (l_api_updating
1684 and nvl(p_organization_id,hr_api.g_number)
1685 <> nvl(ben_pbn_shd.g_old_rec.organization_id, hr_api.g_number)
1686 or not l_api_updating) then
1687 --
1688 -- check if organization_id value exists in hr_all_organization_units table
1689 --
1690 open c1;
1691 --
1692 fetch c1 into l_dummy;
1693 if c1%notfound then
1694 --
1695 close c1;
1696 --
1697 -- raise error as FK does not relate to PK in per_all_people
1698 -- table.
1699 --
1700 ben_pbn_shd.constraint_error('BEN_PL_BNF_FK3');
1701 --
1702 end if;
1703 --
1704 close c1;
1705 --
1706 --
1707 end if;
1708 end if;
1709 --
1710 hr_utility.set_location('Leaving:'||l_proc,10);
1711 --
1712 End chk_organization_id;
1713
1714 -- Bug 2843162
1718 -- ----------------------------------------------------------------------------
1715 --
1716 -- ----------------------------------------------------------------------------
1717 -- |------< chk_bnf_primy_cntgnt_exist >------|
1719 --
1720 -- Description
1721 -- This procedure checks that the same beneficiary is not designated
1722 -- as both primary and contingent
1723 --
1724 -- Pre-Conditions
1725 -- None.
1726 --
1727 -- In Parameters
1728 -- p_pl_bnf_id PK
1729 -- p_organization_id ID of FK column
1730 -- p_effective_date session date
1731 -- p_object_version_number object version number
1732 --
1733 -- Post Success
1734 -- Processing continues
1735 --
1736 -- Post Failure
1737 -- Error raised.
1738 --
1739 -- Access Status
1740 -- Internal table handler use only.
1741 --
1742 Procedure chk_bnf_primy_cntgnt_exist (p_pl_bnf_id in number,
1743 p_bnf_person_id in number,
1744 p_organization_id in number,
1745 p_effective_date in date,
1746 p_business_group_id in number,
1747 p_prmry_cntngnt_cd in varchar2,
1748 p_prtt_enrt_rslt_id in number,
1749 p_object_version_number in number) is
1750 --
1751 l_proc varchar2(72) := g_package||'chk_bnf_primy_cntgnt_exist';
1752 l_api_updating boolean;
1753 l_dummy varchar2(1);
1754 l_exists varchar2(1);
1755 --
1756 --
1757 --
1758 cursor c1 is
1759 select null
1760 from ben_pl_bnf_f pbn,
1761 ben_per_in_ler pil
1762 where (pbn.bnf_person_id = p_bnf_person_id
1763 or pbn.organization_id = p_organization_id)
1764 and pil.per_in_ler_id (+) = pbn.per_in_ler_id
1765 and pil.business_group_id (+) = pbn.business_group_id
1766 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1767 or pil.per_in_ler_stat_cd is null )
1768 and pbn.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1769 and pbn.pl_bnf_id <> nvl(p_pl_bnf_id,hr_api.g_number)
1770 and pbn.business_group_id = p_business_group_id
1771 and pbn.prmry_cntngnt_cd <> p_prmry_cntngnt_cd
1772 and p_effective_date between pbn.effective_start_date and (pbn.effective_end_date -1);
1773
1774 --
1775 Begin
1776 --
1777 hr_utility.set_location('Entering:'||l_proc,5);
1778 --
1779 l_api_updating := ben_pbn_shd.api_updating
1780 (p_pl_bnf_id => p_pl_bnf_id,
1781 p_effective_date => p_effective_date,
1782 p_object_version_number => p_object_version_number);
1783 --
1784 if (l_api_updating
1785 and (nvl(p_organization_id,hr_api.g_number) <> nvl(ben_pbn_shd.g_old_rec.organization_id, hr_api.g_number)
1786 or nvl(p_bnf_person_id,hr_api.g_number) <> nvl(ben_pbn_shd.g_old_rec.bnf_person_id, hr_api.g_number)
1787 or nvl(p_prmry_cntngnt_cd,hr_api.g_varchar2) <> nvl(ben_pbn_shd.g_old_rec.prmry_cntngnt_cd, hr_api.g_varchar2))
1788 or not l_api_updating) then
1789 --
1790 -- check if person/organization has already been designated as a
1791 -- bnf with a different prmry_cntngnt_cd
1792 --
1793
1794 open c1;
1795 --
1796 fetch c1 into l_dummy;
1797 if c1%found then
1798 --
1799 close c1;
1800 --
1801 -- raise error as the person/organization has already been designated
1802 --
1803 --
1804 fnd_message.set_name('BEN', 'BEN_92619_PRIMY_AND_CNTGNT');
1805 fnd_message.raise_error;
1806 --
1807 end if;
1808 --
1809 close c1;
1810 --
1811 --
1812 end if;
1813 --
1814 hr_utility.set_location('Leaving:'||l_proc,10);
1815 --
1816 End chk_bnf_primy_cntgnt_exist;
1817
1818 -- end 2843162
1819
1820 --
1821 -- ----------------------------------------------------------------------------
1822 -- |--------------------------< dt_update_validate >--------------------------|
1823 -- ----------------------------------------------------------------------------
1824 -- {Start Of Comments}
1825 --
1826 -- Description:
1827 -- This procedure is used for referential integrity of datetracked
1828 -- parent entities when a datetrack update operation is taking place
1829 -- and where there is no cascading of update defined for this entity.
1830 --
1831 -- Prerequisites:
1832 -- This procedure is called from the update_validate.
1833 --
1834 -- In Parameters:
1835 --
1836 -- Post Success:
1837 -- Processing continues.
1838 --
1839 -- Post Failure:
1840 --
1841 -- Developer Implementation Notes:
1842 -- This procedure should not need maintenance unless the HR Schema model
1843 -- changes.
1844 --
1845 -- Access Status:
1846 -- Internal Row Handler Use Only.
1847 --
1848 -- {End Of Comments}
1849 -- ----------------------------------------------------------------------------
1850 Procedure dt_update_validate
1851 (p_per_in_ler_id in number default hr_api.g_number,
1852 p_prtt_enrt_rslt_id in number default hr_api.g_number,
1853 p_datetrack_mode in varchar2,
1854 p_validation_start_date in date,
1858 l_integrity_error Exception;
1855 p_validation_end_date in date) Is
1856 --
1857 l_proc varchar2(72) := g_package||'dt_update_validate';
1859 l_table_name all_tables.table_name%TYPE;
1860 --
1861 Begin
1862 hr_utility.set_location('Entering:'||l_proc, 5);
1863 --
1864 -- Ensure that the p_datetrack_mode argument is not null
1865 --
1866 hr_api.mandatory_arg_error
1867 (p_api_name => l_proc,
1868 p_argument => 'datetrack_mode',
1869 p_argument_value => p_datetrack_mode);
1870 --
1871 -- Only perform the validation if the datetrack update mode is valid
1872 --
1873 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
1874 --
1875 --
1876 -- Ensure the arguments are not null
1877 --
1878 hr_api.mandatory_arg_error
1879 (p_api_name => l_proc,
1880 p_argument => 'validation_start_date',
1881 p_argument_value => p_validation_start_date);
1882 --
1883 hr_api.mandatory_arg_error
1884 (p_api_name => l_proc,
1885 p_argument => 'validation_end_date',
1886 p_argument_value => p_validation_end_date);
1887 --
1888 If ((nvl(p_prtt_enrt_rslt_id, hr_api.g_number) <> hr_api.g_number) and
1889 NOT (dt_api.check_min_max_dates
1890 (p_base_table_name => 'ben_prtt_enrt_rslt_f',
1891 p_base_key_column => 'prtt_enrt_rslt_id',
1892 p_base_key_value => p_prtt_enrt_rslt_id,
1893 p_from_date => p_validation_start_date,
1894 p_to_date => p_validation_end_date))) Then
1895 l_table_name := 'ben_prtt_enrt_rslt_f';
1896 Raise l_integrity_error;
1897 End If;
1898 --
1899 --
1900 --
1901 End If;
1902 --
1903 hr_utility.set_location(' Leaving:'||l_proc, 10);
1904 Exception
1905 When l_integrity_error Then
1906 --
1907 -- A referential integrity check was violated therefore
1908 -- we must error
1909 --
1910 -- ben_utility.parent_integrity_error(p_table_name => l_table_name);
1911 --
1912 ben_utility.parent_integrity_error(p_table_name=> l_table_name);
1913 When Others Then
1914 --
1915 -- An unhandled or unexpected error has occurred which
1916 -- we must report
1917 --
1918 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1919 fnd_message.set_token('PROCEDURE', l_proc);
1920 fnd_message.set_token('STEP','15');
1921 fnd_message.raise_error;
1922 End dt_update_validate;
1923 --
1924 -- ----------------------------------------------------------------------------
1925 -- |--------------------------< dt_delete_validate >--------------------------|
1926 -- ----------------------------------------------------------------------------
1927 -- {Start Of Comments}
1928 --
1929 -- Description:
1930 -- This procedure is used for referential integrity of datetracked
1931 -- child entities when either a datetrack DELETE or ZAP is in operation
1932 -- and where there is no cascading of delete defined for this entity.
1933 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1934 -- datetracked child rows exist between the validation start and end
1935 -- dates.
1936 --
1937 -- Prerequisites:
1938 -- This procedure is called from the delete_validate.
1939 --
1940 -- In Parameters:
1941 --
1942 -- Post Success:
1943 -- Processing continues.
1944 --
1945 -- Post Failure:
1946 -- If a row exists by determining the returning Boolean value from the
1947 -- generic dt_api.rows_exist function then we must supply an error via
1948 -- the use of the local exception handler l_rows_exist.
1949 --
1950 -- Developer Implementation Notes:
1951 -- This procedure should not need maintenance unless the HR Schema model
1952 -- changes.
1953 --
1954 -- Access Status:
1955 -- Internal Row Handler Use Only.
1956 --
1957 -- {End Of Comments}
1958 -- ----------------------------------------------------------------------------
1959 Procedure dt_delete_validate
1960 (p_pl_bnf_id in number,
1961 p_datetrack_mode in varchar2,
1962 p_validation_start_date in date,
1963 p_validation_end_date in date) Is
1964 --
1965 l_proc varchar2(72) := g_package||'dt_delete_validate';
1966 l_rows_exist Exception;
1967 l_table_name all_tables.table_name%TYPE;
1968 --
1969 Begin
1970 hr_utility.set_location('Entering:'||l_proc, 5);
1971 --
1972 -- Ensure that the p_datetrack_mode argument is not null
1973 --
1974 hr_api.mandatory_arg_error
1975 (p_api_name => l_proc,
1976 p_argument => 'datetrack_mode',
1977 p_argument_value => p_datetrack_mode);
1978 --
1979 -- Only perform the validation if the datetrack mode is either
1980 -- DELETE or ZAP
1981 --
1982 If (p_datetrack_mode = 'DELETE' or
1983 p_datetrack_mode = 'ZAP') then
1984 --
1985 --
1986 -- Ensure the arguments are not null
1987 --
1988 hr_api.mandatory_arg_error
1989 (p_api_name => l_proc,
1990 p_argument => 'validation_start_date',
1991 p_argument_value => p_validation_start_date);
1992 --
1993 hr_api.mandatory_arg_error
1994 (p_api_name => l_proc,
1998 hr_api.mandatory_arg_error
1995 p_argument => 'validation_end_date',
1996 p_argument_value => p_validation_end_date);
1997 --
1999 (p_api_name => l_proc,
2000 p_argument => 'pl_bnf_id',
2001 p_argument_value => p_pl_bnf_id);
2002 --
2003 If (dt_api.rows_exist
2004 (p_base_table_name => 'ben_pl_bnf_ctfn_prvdd_f',
2005 p_base_key_column => 'pl_bnf_id',
2006 p_base_key_value => p_pl_bnf_id,
2007 p_from_date => p_validation_start_date,
2008 p_to_date => p_validation_end_date)) Then
2009 l_table_name := 'ben_pl_bnf_ctfn_prvdd_f';
2010 Raise l_rows_exist;
2011 End If;
2012 --
2013 End If;
2014 --
2015 hr_utility.set_location(' Leaving:'||l_proc, 10);
2016 Exception
2017 When l_rows_exist Then
2018 --
2019 -- A referential integrity check was violated therefore
2020 -- we must error
2021 --
2022 -- ben_utility.child_exists_error(p_table_name => l_table_name);
2023 --
2024 ben_utility.child_exists_error(p_table_name=> l_table_name);
2025 When Others Then
2026 --
2027 -- An unhandled or unexpected error has occurred which
2028 -- we must report
2029 --
2030 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2031 fnd_message.set_token('PROCEDURE', l_proc);
2032 fnd_message.set_token('STEP','15');
2033 fnd_message.raise_error;
2034 End dt_delete_validate;
2035 --
2036 -- ----------------------------------------------------------------------------
2037 -- |---------------------------< insert_validate >----------------------------|
2038 -- ----------------------------------------------------------------------------
2039 Procedure insert_validate
2040 (p_rec in ben_pbn_shd.g_rec_type,
2041 p_effective_date in date,
2042 p_datetrack_mode in varchar2,
2043 p_validation_start_date in date,
2044 p_validation_end_date in date) is
2045 --
2046 l_proc varchar2(72) := g_package||'insert_validate';
2047 --
2048 Begin
2049 hr_utility.set_location('Entering:'||l_proc, 5);
2050 --
2051 -- Call all supporting business operations
2052 --
2053 --
2054 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2055 --
2056 chk_pl_bnf_id
2057 (p_pl_bnf_id => p_rec.pl_bnf_id,
2058 p_effective_date => p_effective_date,
2059 p_object_version_number => p_rec.object_version_number);
2060 --
2061 -- Bug Fix 2367632
2062 --
2063 chk_bnf_dsgn_rqmt_relnshp_typ
2064 (p_pl_bnf_id => p_rec.pl_bnf_id,
2065 p_bnf_person_id => p_rec.bnf_person_id,
2066 p_per_in_ler_id => p_rec.per_in_ler_id,
2067 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2068 p_business_group_id => p_rec.business_group_id,
2069 p_effective_date => p_effective_date,
2070 p_object_version_number => p_rec.object_version_number);
2071 --
2072 --End fix 2367632
2073 --
2074 chk_prmry_cntngnt_cd
2075 (p_pl_bnf_id => p_rec.pl_bnf_id,
2076 p_prmry_cntngnt_cd => p_rec.prmry_cntngnt_cd,
2077 p_effective_date => p_effective_date,
2078 p_object_version_number => p_rec.object_version_number);
2079 --
2080 chk_all_pl_bnf_parameters
2081 (p_pl_bnf_id => p_rec.pl_bnf_id,
2082 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2083 p_prmry_cntngnt_cd => p_rec.prmry_cntngnt_cd,
2084 p_organization_id => p_rec.organization_id,
2085 p_addl_instrn_txt => p_rec.addl_instrn_txt,
2086 p_amt_dsgd_val => p_rec.amt_dsgd_val,
2087 p_pct_dsgd_num => p_rec.pct_dsgd_num,
2088 p_validation_start_date => p_validation_start_date,
2089 p_validation_end_date => p_validation_end_date,
2090 p_effective_date => p_effective_date,
2091 p_business_group_id => p_rec.business_group_id,
2092 p_object_version_number => p_rec.object_version_number);
2093 --
2094 chk_bnf_person_id
2095 (p_pl_bnf_id => p_rec.pl_bnf_id,
2096 p_bnf_person_id => p_rec.bnf_person_id,
2097 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2098 p_validation_start_date => p_validation_start_date,
2099 p_validation_end_date => p_validation_end_date,
2100 p_effective_date => p_effective_date,
2101 p_business_group_id => p_rec.business_group_id,
2102 p_object_version_number => p_rec.object_version_number);
2103 --
2104 chk_organization_id
2105 (p_pl_bnf_id => p_rec.pl_bnf_id,
2106 p_organization_id => p_rec.organization_id,
2107 p_effective_date => p_effective_date,
2108 p_business_group_id => p_rec.business_group_id,
2109 p_object_version_number => p_rec.object_version_number);
2110 --
2111 chk_ttee_person_id
2112 (p_pl_bnf_id => p_rec.pl_bnf_id,
2113 p_ttee_person_id => p_rec.ttee_person_id,
2114 p_bnf_person_id => p_rec.bnf_person_id,
2115 p_validation_start_date => p_validation_start_date,
2116 p_validation_end_date => p_validation_end_date,
2117 p_effective_date => p_effective_date,
2118 p_business_group_id => p_rec.business_group_id,
2119 p_object_version_number => p_rec.object_version_number);
2120 --
2121
2122 --
2123 -- Bug 2843162
2124 --
2125 chk_bnf_primy_cntgnt_exist
2126 (p_pl_bnf_id => p_rec.pl_bnf_id,
2130 p_business_group_id => p_rec.business_group_id,
2127 p_bnf_person_id => p_rec.bnf_person_id,
2128 p_organization_id => p_rec.organization_id,
2129 p_effective_date => p_effective_date,
2131 p_prmry_cntngnt_cd => p_rec.prmry_cntngnt_cd,
2132 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2133 p_object_version_number => p_rec.object_version_number);
2134 --
2135 -- End of Bug 2843162
2136 --
2137
2138 -- maagrawa Aug 05, 2000.
2139 -- The following two checks (chk_pct_dsgd_num) and (chk_amt_dsgd_val) have
2140 -- been moved to bnf_actn_items procedure in the api.
2141 -- This was done as the total checks should be done only after all records
2142 -- have been saved and not for individual records.
2143 -- The bnf_actn_items procedure is only called when multi_rows_actn is TRUE.
2144 -- (Bug 1368208).
2145 --
2146 -- chk_pct_dsgd_num
2147 -- (p_pl_bnf_id => p_rec.pl_bnf_id,
2148 -- p_pct_dsgd_num => p_rec.pct_dsgd_num,
2149 -- p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2150 -- p_prmry_cntngnt_cd => p_rec.prmry_cntngnt_cd,
2151 -- p_validation_start_date => p_validation_start_date,
2152 -- p_validation_end_date => p_validation_end_date,
2153 -- p_effective_date => p_effective_date,
2154 -- p_business_group_id => p_rec.business_group_id,
2155 -- p_object_version_number => p_rec.object_version_number);
2156 --
2157 -- chk_amt_dsgd_val
2158 -- (p_pl_bnf_id => p_rec.pl_bnf_id,
2159 -- p_amt_dsgd_val => p_rec.amt_dsgd_val,
2160 -- p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2161 -- p_prmry_cntngnt_cd => p_rec.prmry_cntngnt_cd,
2162 -- p_validation_start_date => p_validation_start_date,
2163 -- p_validation_end_date => p_validation_end_date,
2164 -- p_effective_date => p_effective_date,
2165 -- p_business_group_id => p_rec.business_group_id,
2166 -- p_object_version_number => p_rec.object_version_number);
2167 --
2168 chk_amt_dsgd_uom
2169 (p_pl_bnf_id => p_rec.pl_bnf_id,
2170 p_amt_dsgd_uom => p_rec.amt_dsgd_uom,
2171 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2172 p_effective_date => p_effective_date,
2173 p_business_group_id => p_rec.business_group_id,
2174 p_object_version_number => p_rec.object_version_number);
2175 --
2176 hr_utility.set_location(' Leaving:'||l_proc, 10);
2177 End insert_validate;
2178 --
2179 -- ----------------------------------------------------------------------------
2180 -- |---------------------------< update_validate >----------------------------|
2181 -- ----------------------------------------------------------------------------
2182 Procedure update_validate
2183 (p_rec in ben_pbn_shd.g_rec_type,
2184 p_effective_date in date,
2185 p_datetrack_mode in varchar2,
2186 p_validation_start_date in date,
2187 p_validation_end_date in date) is
2188 --
2189 l_proc varchar2(72) := g_package||'update_validate';
2190 --
2191 Begin
2192 hr_utility.set_location('Entering:'||l_proc, 5);
2193 --
2194 -- Call all supporting business operations
2195 --
2196 --
2197 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2198 --
2199
2200 chk_pl_bnf_id
2201 (p_pl_bnf_id => p_rec.pl_bnf_id,
2202 p_effective_date => p_effective_date,
2203 p_object_version_number => p_rec.object_version_number);
2204 --
2205 -- Bug Fix 2367632
2206 --
2207 chk_bnf_dsgn_rqmt_relnshp_typ
2208 (p_pl_bnf_id => p_rec.pl_bnf_id,
2209 p_bnf_person_id => p_rec.bnf_person_id,
2210 p_per_in_ler_id => p_rec.per_in_ler_id,
2211 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2212 p_business_group_id => p_rec.business_group_id,
2213 p_effective_date => p_effective_date,
2214 p_object_version_number => p_rec.object_version_number);
2215 --
2216 --End fix 2367632
2217 --
2218 chk_prmry_cntngnt_cd
2219 (p_pl_bnf_id => p_rec.pl_bnf_id,
2220 p_prmry_cntngnt_cd => p_rec.prmry_cntngnt_cd,
2221 p_effective_date => p_effective_date,
2222 p_object_version_number => p_rec.object_version_number);
2223 --
2224 chk_all_pl_bnf_parameters
2225 (p_pl_bnf_id => p_rec.pl_bnf_id,
2226 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2227 p_prmry_cntngnt_cd => p_rec.prmry_cntngnt_cd,
2228 p_organization_id => p_rec.organization_id,
2229 p_addl_instrn_txt => p_rec.addl_instrn_txt,
2230 p_amt_dsgd_val => p_rec.amt_dsgd_val,
2231 p_pct_dsgd_num => p_rec.pct_dsgd_num,
2232 p_validation_start_date => p_validation_start_date,
2233 p_validation_end_date => p_validation_end_date,
2234 p_effective_date => p_effective_date,
2235 p_business_group_id => p_rec.business_group_id,
2236 p_object_version_number => p_rec.object_version_number);
2237 --
2238 chk_bnf_person_id
2239 (p_pl_bnf_id => p_rec.pl_bnf_id,
2240 p_bnf_person_id => p_rec.bnf_person_id,
2241 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2242 p_validation_start_date => p_validation_start_date,
2243 p_validation_end_date => p_validation_end_date,
2244 p_effective_date => p_effective_date,
2245 p_business_group_id => p_rec.business_group_id,
2246 p_object_version_number => p_rec.object_version_number);
2247 --
2251 p_effective_date => p_effective_date,
2248 chk_organization_id
2249 (p_pl_bnf_id => p_rec.pl_bnf_id,
2250 p_organization_id => p_rec.organization_id,
2252 p_business_group_id => p_rec.business_group_id,
2253 p_object_version_number => p_rec.object_version_number);
2254 --
2255 chk_ttee_person_id
2256 (p_pl_bnf_id => p_rec.pl_bnf_id,
2257 p_ttee_person_id => p_rec.ttee_person_id,
2258 p_bnf_person_id => p_rec.bnf_person_id,
2259 p_validation_start_date => p_validation_start_date,
2260 p_validation_end_date => p_validation_end_date,
2261 p_effective_date => p_effective_date,
2262 p_business_group_id => p_rec.business_group_id,
2263 p_object_version_number => p_rec.object_version_number);
2264 --
2265
2266 --
2267 -- Bug 2843162
2268 --
2269 chk_bnf_primy_cntgnt_exist
2270 (p_pl_bnf_id => p_rec.pl_bnf_id,
2271 p_bnf_person_id => p_rec.bnf_person_id,
2272 p_organization_id => p_rec.organization_id,
2273 p_effective_date => p_effective_date,
2274 p_business_group_id => p_rec.business_group_id,
2275 p_prmry_cntngnt_cd => p_rec.prmry_cntngnt_cd,
2276 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2277 p_object_version_number => p_rec.object_version_number);
2278
2279 --
2280 -- End Bug 2843162
2281 --
2282
2283 --
2284 -- maagrawa Aug 05, 2000.
2285 -- The following two checks (chk_pct_dsgd_num) and (chk_amt_dsgd_val) have
2286 -- been moved to bnf_actn_items procedure in the api.
2287 -- This was done as the total checks should be done only after all records
2288 -- have been saved and not for individual records.
2289 -- The bnf_actn_items procedure is only called when multi_rows_actn is TRUE.
2290 -- (Bug 1368208).
2291 --
2292 -- chk_pct_dsgd_num
2293 -- (p_pl_bnf_id => p_rec.pl_bnf_id,
2294 -- p_pct_dsgd_num => p_rec.pct_dsgd_num,
2295 -- p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2296 -- p_prmry_cntngnt_cd => p_rec.prmry_cntngnt_cd,
2297 -- p_validation_start_date => p_validation_start_date,
2298 -- p_validation_end_date => p_validation_end_date,
2299 -- p_effective_date => p_effective_date,
2300 -- p_business_group_id => p_rec.business_group_id,
2301 -- p_object_version_number => p_rec.object_version_number);
2302 --
2303 -- chk_amt_dsgd_val
2304 -- (p_pl_bnf_id => p_rec.pl_bnf_id,
2305 -- p_amt_dsgd_val => p_rec.amt_dsgd_val,
2306 -- p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2307 -- p_prmry_cntngnt_cd => p_rec.prmry_cntngnt_cd,
2308 -- p_validation_start_date => p_validation_start_date,
2309 -- p_validation_end_date => p_validation_end_date,
2310 -- p_effective_date => p_effective_date,
2311 -- p_business_group_id => p_rec.business_group_id,
2312 -- p_object_version_number => p_rec.object_version_number);
2313 --
2314 chk_amt_dsgd_uom
2315 (p_pl_bnf_id => p_rec.pl_bnf_id,
2316 p_amt_dsgd_uom => p_rec.amt_dsgd_uom,
2317 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2318 p_effective_date => p_effective_date,
2319 p_business_group_id => p_rec.business_group_id,
2320 p_object_version_number => p_rec.object_version_number);
2321 --
2322 --
2323 -- Call the datetrack update integrity operation
2324 --
2325 dt_update_validate
2326 (p_per_in_ler_id => p_rec.per_in_ler_id,
2327 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2328 p_datetrack_mode => p_datetrack_mode,
2329 p_validation_start_date => p_validation_start_date,
2330 p_validation_end_date => p_validation_end_date);
2331 --
2332 hr_utility.set_location(' Leaving:'||l_proc, 10);
2333 End update_validate;
2334 --
2335 -- ----------------------------------------------------------------------------
2336 -- |---------------------------< delete_validate >----------------------------|
2337 -- ----------------------------------------------------------------------------
2338 Procedure delete_validate
2339 (p_rec in ben_pbn_shd.g_rec_type,
2340 p_effective_date in date,
2341 p_datetrack_mode in varchar2,
2342 p_validation_start_date in date,
2343 p_validation_end_date in date) is
2344 --
2345 l_proc varchar2(72) := g_package||'delete_validate';
2346 --
2347 Begin
2348 hr_utility.set_location('Entering:'||l_proc, 5);
2349 --
2350 -- Call all supporting business operations
2351 --
2352 dt_delete_validate
2353 (p_datetrack_mode => p_datetrack_mode,
2354 p_validation_start_date => p_validation_start_date,
2355 p_validation_end_date => p_validation_end_date,
2356 p_pl_bnf_id => p_rec.pl_bnf_id);
2357 --
2358 hr_utility.set_location(' Leaving:'||l_proc, 10);
2359 End delete_validate;
2360 --
2361 --
2362 -- ---------------------------------------------------------------------------
2363 -- |---------------------< return_legislation_code >-------------------------|
2364 -- ---------------------------------------------------------------------------
2365 --
2366 function return_legislation_code
2367 (p_pl_bnf_id in number) return varchar2 is
2368 --
2369 -- Declare cursor
2370 --
2371 cursor csr_leg_code is
2372 select a.legislation_code
2373 from per_business_groups a,
2374 ben_pl_bnf_f b
2375 where b.pl_bnf_id = p_pl_bnf_id
2376 and a.business_group_id = b.business_group_id;
2377 --
2378 -- Declare local variables
2379 --
2380 l_legislation_code varchar2(150);
2381 l_proc varchar2(72) := g_package||'return_legislation_code';
2382 --
2383 begin
2384 --
2385 hr_utility.set_location('Entering:'|| l_proc, 10);
2386 --
2387 -- Ensure that all the mandatory parameter are not null
2388 --
2389 hr_api.mandatory_arg_error(p_api_name => l_proc,
2390 p_argument => 'pl_bnf_id',
2391 p_argument_value => p_pl_bnf_id);
2392 --
2393 open csr_leg_code;
2394 --
2395 fetch csr_leg_code into l_legislation_code;
2396 --
2397 if csr_leg_code%notfound then
2398 --
2399 close csr_leg_code;
2400 --
2401 -- The primary key is invalid therefore we must error
2402 --
2403 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
2404 fnd_message.raise_error;
2405 --
2406 end if;
2407 --
2408 close csr_leg_code;
2409 --
2410 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2411 --
2412 return l_legislation_code;
2413 --
2414 end return_legislation_code;
2415 --
2416 end ben_pbn_bus;