1 Package Body ben_vep_bus as
2 /* $Header: beveprhi.pkb 120.1.12020000.2 2012/07/26 10:36:02 usaraswa ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_vep_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_vrbl_rt_elig_prfl_id number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |------< chk_vrbl_rt_elig_prfl_id >------|
18 -- ----------------------------------------------------------------------------
19 --
20 -- Description
21 -- This procedure is used to check that the primary key for the table
22 -- is created properly. It should be null on insert and
23 -- should not be able to be updated.
24 --
25 -- Pre Conditions
26 -- None.
27 --
28 -- In Parameters
29 -- vrbl_rt_elig_prfl_id PK of record being inserted or updated.
30 -- effective_date Effective Date of session
31 -- object_version_number Object version number of record being
32 -- inserted or updated.
33 --
34 -- Post Success
35 -- Processing continues
36 --
37 -- Post Failure
38 -- Errors handled by the procedure
39 --
40 -- Access Status
41 -- Internal table handler use only.
42 --
43 Procedure chk_vrbl_rt_elig_prfl_id(p_vrbl_rt_elig_prfl_id in number,
44 p_effective_date in date,
45 p_object_version_number in number) is
46 --
47 l_proc varchar2(72) := g_package||'chk_vrbl_rt_elig_prfl_id';
48 l_api_updating boolean;
49 --
50 Begin
51 --
52 hr_utility.set_location('Entering:'||l_proc, 5);
53 --
54 l_api_updating := ben_vep_shd.api_updating
55 (p_effective_date => p_effective_date,
56 p_vrbl_rt_elig_prfl_id => p_vrbl_rt_elig_prfl_id,
57 p_object_version_number => p_object_version_number);
58 --
59 if (l_api_updating
60 and nvl(p_vrbl_rt_elig_prfl_id,hr_api.g_number)
61 <> ben_vep_shd.g_old_rec.vrbl_rt_elig_prfl_id) then
62 -- raise error as PK has changed
63 --
64 ben_vep_shd.constraint_error('BEN_VRBL_RT_ELIG_PRFL_F_PK');
65 --
66 elsif not l_api_updating then
67 --
68 -- check if PK is null
69 --
70 if p_vrbl_rt_elig_prfl_id is not null then
71 --
72 -- raise error as PK is not null
73 --
74 ben_vep_shd.constraint_error('BEN_VRBL_RT_ELIG_PRFL_F_PK');
75 --
76 end if;
77 --
78 end if;
79 --
80 hr_utility.set_location('Leaving:'||l_proc, 10);
81 --
82 End chk_vrbl_rt_elig_prfl_id;
83
84
85
86 -- ----------------------------------------------------------------------------
87 -- |------< chk_mndtry_flag >------|
88 -- ----------------------------------------------------------------------------
89 --
90 -- Description
91 -- This procedure is used to check that the lookup value is valid.
92 --
93 -- Pre Conditions
94 -- None.
95 --
96 -- In Parameters
97 -- prtn_elig_prfl_id PK of record being inserted or updated.
98 -- mndtry_flag Value of lookup code.
99 -- effective_date effective date
100 -- object_version_number Object version number of record being
101 -- inserted or updated.
102 --
103 -- Post Success
104 -- Processing continues
105 --
106 -- Post Failure
107 -- Error handled by procedure
108 --
109 -- Access Status
110 -- Internal table handler use only.
111 --
112 Procedure chk_mndtry_flag(p_vrbl_rt_elig_prfl_id in number,
113 p_mndtry_flag in varchar2,
114 p_effective_date in date,
115 p_object_version_number in number) is
116 --
117 --
118 l_proc varchar2(72) := g_package||'chk_mndtry_flag';
119 l_api_updating boolean;
120 --
121 Begin
122 --
123 hr_utility.set_location('Entering:'||l_proc, 5);
124 --
125 l_api_updating := ben_vep_shd.api_updating
126 (p_vrbl_rt_elig_prfl_id => p_vrbl_rt_elig_prfl_id,
127 p_effective_date => p_effective_date,
128 p_object_version_number => p_object_version_number);
129 --
130 if (l_api_updating
131 and p_mndtry_flag
132 <> nvl(ben_vep_shd.g_old_rec.mndtry_flag,hr_api.g_varchar2)
133 or not l_api_updating)
134 and p_mndtry_flag is not null then
135 --
136 -- check if value of lookup falls within lookup type.
137 --
138 if hr_api.not_exists_in_hr_lookups
139 (p_lookup_type => 'YES_NO',
140 p_lookup_code => p_mndtry_flag,
141 p_effective_date => p_effective_date) then
142 --
143 -- raise error as does not exist as lookup
144 --
145 hr_utility.set_message(801,'MNDTRY_FLAG_NOT_EXIST');
146 hr_utility.raise_error;
147 --
148 end if;
149 end if;
150 --
151 hr_utility.set_location('Leaving:'||l_proc,10);
152 --
153 end chk_mndtry_flag;
154 -- ----------------------------------------------------------------------------
155 -- |------< chk_vrbl_rt_elig_prfl_count >------|
156 -- ----------------------------------------------------------------------------
157 --
158 -- Description
159 -- This procedure is used to check that there should be only one
160 -- Eligibility Profile record linked to VAPRO.
161 -- This procedure has been created as part of bug 3548872
162 -- Since PDW is not supporting multiple VAPROs, we have this check.
163 -- As soon as PDW start supporting multiple VAPROs, this procedure needs
164 -- to be removed. This would be called only from Insert_Validate
165 --
166 --
167 -- In Parameters
168 -- p_vrbl_rt_prfl_id
169 -- effective_date Effective Date of session
170 --
171 -- Post Success
172 -- Processing continues
173 --
174 -- Post Failure
175 -- Errors handled by the procedure
176 --
177 -- Access Status
178 -- Internal table handler use only.
179 --
180 Procedure chk_vrbl_rt_elig_prfl_count(
181 p_vrbl_rt_prfl_id in number,
182 p_effective_date in date
183 ) is
184 --
185 l_proc varchar2(72) := g_package||'chk_vrbl_rt_elig_prfl_count';
186 l_dummy varchar2(1) := null ;
187 --
188 cursor c_vrbl_rt_elig_prfl is
189 select null from
190 BEN_VRBL_RT_ELIG_PRFL_f vep
191 where
192 vep.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
193 and p_effective_date between effective_start_date
194 and effective_end_date;
195
196 Begin
197 hr_utility.set_location('Entering:'||l_proc, 5);
198 --
199 -- raise error as the record already exists
200 open c_vrbl_rt_elig_prfl ;
201 fetch c_vrbl_rt_elig_prfl into l_dummy ;
202 --
203 if c_vrbl_rt_elig_prfl%found then
204 --
205 hr_utility.set_location('Only one Profile is allowed ', 8 ) ;
206 close c_vrbl_rt_elig_prfl ;
207 fnd_message.set_name('BEN','BEN_93952_ONLY_ONE_ELPRO');
208 fnd_message.raise_error;
209 --
210 end if;
211 close c_vrbl_rt_elig_prfl ;
212 --
213 hr_utility.set_location('Leaving:'||l_proc, 10);
214 --
215 End chk_vrbl_rt_elig_prfl_count;
216 --
217 --
218 -- ----------------------------------------------------------------------------
219 -- |------< chk_uniq_vrbl_rt_elig_prfl >------|
220 -- ----------------------------------------------------------------------------
221 --
222 -- Description
223 -- This procedure is used to check that the the records is unique with the
224 --
225 -- Pre Conditions
226 -- None.
227 --
228 -- In Parameters
229 -- eligy_prfl_id
230 -- effective_date Effective Date of session
231 -- object_version_number Object version number of record being
232 -- inserted or updated.
233 --
234 -- Post Success
235 -- Processing continues
236 --
237 -- Post Failure
238 -- Errors handled by the procedure
239 --
240 -- Access Status
241 -- Internal table handler use only.
242 --
243 Procedure chk_uniq_vrbl_rt_elig_prfl(
244 p_vrbl_rt_elig_prfl_id in number,
245 p_eligy_prfl_id in number,
246 p_vrbl_rt_prfl_id in number,
247 p_effective_date in date,
248 p_object_version_number in number) is
249 --
250 l_proc varchar2(72) := g_package||'chk_uniq_vrbl_rt_elig_prfl';
251 l_dummy varchar2(1) := null ;
252 --
253 cursor c_uniq_vrbl_rt_elig_prfl is
254 select null from
255 BEN_VRBL_RT_ELIG_PRFL_f vep
256 where
257 vep.eligy_prfl_id = p_eligy_prfl_id
258 and vep.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
259 and vep.effective_start_date > p_effective_date
260 and nvl(vep.vrbl_rt_elig_prfl_id,-1) <> p_vrbl_rt_elig_prfl_id ;
261 Begin
262 hr_utility.set_location('Entering:'||l_proc, 5);
263 --
264 -- raise error as the record already exists
265 open c_uniq_vrbl_rt_elig_prfl ;
266 fetch c_uniq_vrbl_rt_elig_prfl into l_dummy ;
267 --
268 if c_uniq_vrbl_rt_elig_prfl%found then
269 --
270 hr_utility.set_location('Future record exists.Cannot insert ', 8 ) ;
271 close c_uniq_vrbl_rt_elig_prfl ;
272 fnd_message.set_name('PER','HR_7211_DT_UPD_ROWS_IN_FUTURE');
273 fnd_message.raise_error;
274 --
275 end if;
276 close c_uniq_vrbl_rt_elig_prfl ;
277 --
278 hr_utility.set_location('Leaving:'||l_proc, 10);
279 --
280 End chk_uniq_vrbl_rt_elig_prfl;
281 --
282 -- 2940151
283 -- ----------------------------------------------------------------------------
284 -- |------< chk_elig_prfl_criteria >------|
285 -- ----------------------------------------------------------------------------
286 --
287 -- Description
288 -- This procedure is used to ensure that a variable prfl has either an
289 -- elig profile or criteria attached, not both
290 --
291 -- Pre Conditions
292 -- None.
293 --
294 -- In Parameters
295 -- eligy_prfl_id
296 -- effective_date Effective Date of session
297 -- object_version_number Object version number of record being
298 -- inserted or updated.
299 --
300 -- Post Success
301 -- Processing continues
302 --
303 -- Post Failure
304 -- Errors handled by the procedure
305 --
306 -- Access Status
307 -- Internal table handler use only.
308 --
309 Procedure chk_elig_prfl_criteria(
310 p_vrbl_rt_elig_prfl_id in number,
311 p_eligy_prfl_id in number,
312 p_vrbl_rt_prfl_id in number,
313 p_effective_date in date,
314 p_object_version_number in number) is
315 --
316 l_proc varchar2(72) := g_package||'chk_elig_prfl_criteria';
317 l_dummy varchar2(1) := null ;
318 --
319 cursor c_vrbl_rt_flags is
320 select 'X' from
321 ben_vrbl_rt_prfl_f vpf
322 where
323 vpf.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
324 and p_effective_date between vpf.effective_start_date and vpf.effective_end_date
325 and (
326 -- Bug 14361059
327 (rt_hrly_slrd_flag = 'Y' and exists (select '1' from BEN_HRLY_SLRD_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
328 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
329
330 (rt_pstl_cd_flag = 'Y' and exists (select '1' from BEN_PSTL_ZIP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
331 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
332
333 (rt_lbr_mmbr_flag = 'Y' and exists (select '1' from BEN_LBR_MMBR_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
334 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
335
336 (rt_lgl_enty_flag = 'Y' and exists (select '1' from BEN_LGL_ENTY_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
337 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
338
339 (rt_benfts_grp_flag = 'Y' and exists (select '1' from BEN_BENFTS_GRP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
340 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
341
342 (rt_wk_loc_flag = 'Y' and exists (select '1' from BEN_WK_LOC_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
343 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
344
345 (rt_brgng_unit_flag = 'Y' and exists (select '1' from BEN_BRGNG_UNIT_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
346 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
347
348 (rt_age_flag = 'Y' and exists (select '1' from BEN_AGE_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
349 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
350
351 (rt_los_flag = 'Y' and exists (select '1' from BEN_LOS_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
352 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
353
354 (rt_per_typ_flag = 'Y' and exists (select '1' from BEN_PER_TYP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
355 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
356
357 (rt_fl_tm_pt_tm_flag = 'Y' and exists (select '1' from BEN_FL_TM_PT_TM_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
358 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
359
360 (rt_ee_stat_flag = 'Y' and exists (select '1' from BEN_EE_STAT_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
361 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
362
363 (rt_grd_flag = 'Y' and exists (select '1' from BEN_GRADE_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
364 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
365
366 (rt_pct_fl_tm_flag = 'Y' and exists (select '1' from BEN_PCT_FL_TM_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
367 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
368
369 (rt_asnt_set_flag = 'Y' and exists (select '1' from BEN_ASNT_SET_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
370 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
371
372 (rt_hrs_wkd_flag = 'Y' and exists (select '1' from BEN_HRS_WKD_IN_PERD_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
373 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
374
375 (rt_comp_lvl_flag = 'Y' and exists (select '1' from BEN_COMP_LVL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
376 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
377
378 (rt_org_unit_flag = 'Y' and exists (select '1' from BEN_ORG_UNIT_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
379 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
380
381 (rt_loa_rsn_flag = 'Y' and exists (select '1' from BEN_LOA_RSN_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
382 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
383
384 (rt_pyrl_flag = 'Y' and exists (select '1' from BEN_PYRL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
385 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
386
387 (rt_schedd_hrs_flag = 'Y' and exists (select '1' from BEN_SCHEDD_HRS_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
388 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
389
390 (rt_py_bss_flag = 'Y' and exists (select '1' from BEN_PY_BSS_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
391 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
392
393 (rt_prfl_rl_flag = 'Y' and exists (select '1' from BEN_VRBL_RT_PRFL_RL_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
394 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
395
396 (rt_cmbn_age_los_flag = 'Y' and exists (select '1' from BEN_CMBN_AGE_LOS_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
397 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
398
399 (rt_prtt_pl_flag = 'Y') or
400
401 (rt_svc_area_flag = 'Y' and exists (select '1' from BEN_SVC_AREA_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
402 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
403
404 (rt_ppl_grp_flag = 'Y' and exists (select '1' from BEN_PPL_GRP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
405 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
406
407 (rt_dsbld_flag = 'Y' and exists (select '1' from BEN_DSBLD_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
408 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
409
410 (rt_hlth_cvg_flag = 'Y') or
411
412 (rt_poe_flag = 'Y' and exists (select '1' from BEN_POE_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
413 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
414
415 (rt_ttl_cvg_vol_flag = 'Y' and exists (select '1' from BEN_TTL_CVG_VOL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
416 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
417
418 (rt_ttl_prtt_flag = 'Y' and exists (select '1' from BEN_TTL_PRTT_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
419 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
420
421 (rt_gndr_flag = 'Y' and exists (select '1' from BEN_GNDR_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
422 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
423
424 (rt_tbco_use_flag = 'Y' and exists (select '1' from BEN_TBCO_USE_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
425 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
426
427 (rt_cntng_prtn_prfl_flag = 'Y' and exists (select '1' from BEN_CNTNG_PRTN_PRFL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
428 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
429
430 (rt_cbr_quald_bnf_flag = 'Y' and exists (select '1' from BEN_CBR_QUALD_BNF_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
431 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
432
433 (rt_optd_mdcr_flag = 'Y' and exists (select '1' from BEN_OPTD_MDCR_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
434 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
435
436 (rt_lvg_rsn_flag = 'Y' and exists (select '1' from BEN_LVG_RSN_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
437 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
438
439 (rt_pstn_flag = 'Y' and exists (select '1' from BEN_PSTN_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
440 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
441
442 (rt_comptncy_flag = 'Y' and exists (select '1' from BEN_COMPTNCY_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
443 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
444
445 (rt_job_flag = 'Y' and exists (select '1' from BEN_JOB_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
446 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
447
448 (rt_qual_titl_flag = 'Y' and exists (select '1' from BEN_QUAL_TITL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
449 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
450
451 (rt_dpnt_cvrd_pl_flag = 'Y' and exists (select '1' from BEN_DPNT_CVRD_OTHR_PL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
452 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
453
454 (rt_dpnt_cvrd_plip_flag = 'Y' and exists (select '1' from BEN_DPNT_CVRD_PLIP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
455 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
456
457 (rt_dpnt_cvrd_ptip_flag = 'Y' and exists (select '1' from BEN_DPNT_CVRD_OTHR_PTIP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
458 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
459
460 (rt_dpnt_cvrd_pgm_flag = 'Y' and exists (select '1' from BEN_DPNT_CVRD_OTHR_PGM_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
461 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
462
463 (rt_enrld_oipl_flag = 'Y' and exists (select '1' from BEN_ENRLD_ANTHR_OIPL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
464 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
465
466 (rt_enrld_pl_flag = 'Y' and exists (select '1' from BEN_ENRLD_ANTHR_PL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
467 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
468
469 (rt_enrld_plip_flag = 'Y' and exists (select '1' from BEN_ENRLD_ANTHR_PLIP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
470 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
471
472 (rt_enrld_ptip_flag = 'Y' and exists (select '1' from BEN_ENRLD_ANTHR_PTIP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
473 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
474
475 (rt_enrld_pgm_flag = 'Y' and exists (select '1' from BEN_ENRLD_ANTHR_PGM_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
476 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
477
478 (rt_prtt_anthr_pl_flag = 'Y' and exists (select '1' from BEN_PRTT_ANTHR_PL_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
479 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
480
481 (rt_othr_ptip_flag = 'Y' and exists (select '1' from BEN_OTHR_PTIP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
482 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
483
484 (rt_no_othr_cvg_flag = 'Y' and exists (select '1' from BEN_NO_OTHR_CVG_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
485 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
486
487 (rt_dpnt_othr_ptip_flag = 'Y' and exists (select '1' from BEN_DPNT_OTHR_PTIP_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
488 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
489
490 (rt_qua_in_gr_flag = 'Y' and exists (select '1' from BEN_QUA_IN_GR_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
491 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)) or
492
493 (rt_perf_rtng_flag = 'Y' and exists (select '1' from BEN_PERF_RTNG_RT_F where VRBL_RT_PRFL_ID = p_vrbl_rt_prfl_id
494 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE))
495 --Bug 14361059
496 -- or rt_elig_prfl_flag = 'Y'
497 );
498
499 Begin
500 hr_utility.set_location('Entering:'||l_proc, 5);
501 --
502 -- raise error as the record already exists
503 open c_vrbl_rt_flags ;
504 fetch c_vrbl_rt_flags into l_dummy ;
505 --
506 if c_vrbl_rt_flags%found then
507 --
508
509 close c_vrbl_rt_flags ;
510 fnd_message.set_name('BEN','BEN_93550_VAPRO_ELIG_PRFL');
511 fnd_message.raise_error;
512 --
513 end if;
514 close c_vrbl_rt_flags ;
515 --
516 hr_utility.set_location('Leaving:'||l_proc, 10);
517 --
518 End chk_elig_prfl_criteria;
519 --
520
521 -- ---------------------------------------------------------------------------
522 -- |----------------------< set_security_group_id >--------------------------|
523 -- ---------------------------------------------------------------------------
524 --
525 Procedure set_security_group_id
526 (p_vrbl_rt_elig_prfl_id in number
527 ) is
528 --
529 -- Declare cursor
530 --
531 cursor csr_sec_grp is
532 select pbg.security_group_id
533 from per_business_groups pbg
534 , BEN_VRBL_RT_ELIG_PRFL_f vep
535 where vep.vrbl_rt_elig_prfl_id = p_vrbl_rt_elig_prfl_id
536 and pbg.business_group_id = vep.business_group_id;
537 --
538 -- Declare local variables
539 --
540 l_security_group_id number;
541 l_proc varchar2(72) := g_package||'set_security_group_id';
542 --
543 begin
544 --
545 hr_utility.set_location('Entering:'|| l_proc, 10);
546 --
547 -- Ensure that all the mandatory parameter are not null
548 --
549 hr_api.mandatory_arg_error
550 (p_api_name => l_proc
551 ,p_argument => 'VRBL_RT_ELIG_PRFL_ID'
552 ,p_argument_value => p_vrbl_rt_elig_prfl_id
553 );
554 --
555 open csr_sec_grp;
556 fetch csr_sec_grp into l_security_group_id;
557 --
558 if csr_sec_grp%notfound then
559 --
560 close csr_sec_grp;
561 --
562 -- The primary key is invalid therefore we must error
563 --
564 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
565 fnd_message.raise_error;
566 --
567 end if;
568 close csr_sec_grp;
569 --
570 -- Set the security_group_id in CLIENT_INFO
571 --
572 hr_api.set_security_group_id
573 (p_security_group_id => l_security_group_id
574 );
575 --
576 hr_utility.set_location(' Leaving:'|| l_proc, 20);
577 --
578 end set_security_group_id;
579 --
580 -- ---------------------------------------------------------------------------
581 -- |---------------------< return_legislation_code >-------------------------|
582 -- ---------------------------------------------------------------------------
583 --
584 Function return_legislation_code
585 (p_vrbl_rt_elig_prfl_id in number
586 )
587 Return Varchar2 Is
588 --
589 -- Declare cursor
590 --
591 cursor csr_leg_code is
592 select pbg.legislation_code
593 from per_business_groups pbg
594 , BEN_VRBL_RT_ELIG_PRFL_f vep
595 where vep.vrbl_rt_elig_prfl_id = p_vrbl_rt_elig_prfl_id
596 and pbg.business_group_id = vep.business_group_id;
597 --
598 -- Declare local variables
599 --
600 l_legislation_code varchar2(150);
601 l_proc varchar2(72) := g_package||'return_legislation_code';
602 --
603 Begin
604 --
605 hr_utility.set_location('Entering:'|| l_proc, 10);
606 --
607 -- Ensure that all the mandatory parameter are not null
608 --
609 hr_api.mandatory_arg_error
610 (p_api_name => l_proc
611 ,p_argument => 'VRBL_RT_ELIG_PRFL_ID'
612 ,p_argument_value => p_vrbl_rt_elig_prfl_id
613 );
614 --
615 if ( nvl(ben_vep_bus.g_vrbl_rt_elig_prfl_id, hr_api.g_number)
616 = p_vrbl_rt_elig_prfl_id) then
617 --
618 -- The legislation code has already been found with a previous
619 -- call to this function. Just return the value in the global
620 -- variable.
621 --
622 l_legislation_code := ben_vep_bus.g_legislation_code;
623 hr_utility.set_location(l_proc, 20);
624 else
625 --
626 -- The ID is different to the last call to this function
627 -- or this is the first call to this function.
628 --
629 open csr_leg_code;
630 fetch csr_leg_code into l_legislation_code;
631 --
632 if csr_leg_code%notfound then
633 --
634 -- The primary key is invalid therefore we must error
635 --
636 close csr_leg_code;
637 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
638 fnd_message.raise_error;
639 end if;
640 hr_utility.set_location(l_proc,30);
641 --
642 -- Set the global variables so the values are
643 -- available for the next call to this function.
644 --
645 close csr_leg_code;
646 ben_vep_bus.g_vrbl_rt_elig_prfl_id := p_vrbl_rt_elig_prfl_id;
647 ben_vep_bus.g_legislation_code := l_legislation_code;
648 end if;
649 hr_utility.set_location(' Leaving:'|| l_proc, 40);
650 return l_legislation_code;
651 end return_legislation_code;
652 --
653 /*
654 -- ----------------------------------------------------------------------------
655 -- |------------------------------< chk_df >----------------------------------|
656 -- ----------------------------------------------------------------------------
657 --
658 -- Description:
659 -- Validates all the Descriptive Flexfield values.
660 --
661 -- Prerequisites:
662 -- All other columns have been validated. Must be called as the
663 -- last step from insert_validate and update_validate.
664 --
665 -- In Arguments:
666 -- p_rec
667 --
668 -- Post Success:
669 -- If the Descriptive Flexfield structure column and data values are
670 -- all valid this procedure will end normally and processing will
671 -- continue.
672 --
673 -- Post Failure:
674 -- If the Descriptive Flexfield structure column value or any of
675 -- the data values are invalid then an application error is raised as
676 -- a PL/SQL exception.
677 --
678 -- Access Status:
679 -- Internal Row Handler Use Only.
680 --
681 -- ----------------------------------------------------------------------------
682 procedure chk_df
683 (p_rec in ben_vep_shd.g_rec_type
684 ) is
685 --
686 l_proc varchar2(72) := g_package || 'chk_df';
687 --
688 begin
689 hr_utility.set_location('Entering:'||l_proc,10);
690 --
691 if ((p_rec.vrbl_rt_elig_prfl_id is not null) and (
692 nvl(ben_vep_shd.g_old_rec.vep_attribute_category, hr_api.g_varchar2) <>
693 nvl(p_rec.vep_attribute_category, hr_api.g_varchar2) or
694 nvl(ben_vep_shd.g_old_rec.vep_attribute1, hr_api.g_varchar2) <>
695 nvl(p_rec.vep_attribute1, hr_api.g_varchar2) or
696 nvl(ben_vep_shd.g_old_rec.vep_attribute2, hr_api.g_varchar2) <>
697 nvl(p_rec.vep_attribute2, hr_api.g_varchar2) or
698 nvl(ben_vep_shd.g_old_rec.vep_attribute3, hr_api.g_varchar2) <>
699 nvl(p_rec.vep_attribute3, hr_api.g_varchar2) or
700 nvl(ben_vep_shd.g_old_rec.vep_attribute4, hr_api.g_varchar2) <>
701 nvl(p_rec.vep_attribute4, hr_api.g_varchar2) or
702 nvl(ben_vep_shd.g_old_rec.vep_attribute5, hr_api.g_varchar2) <>
703 nvl(p_rec.vep_attribute5, hr_api.g_varchar2) or
704 nvl(ben_vep_shd.g_old_rec.vep_attribute6, hr_api.g_varchar2) <>
705 nvl(p_rec.vep_attribute6, hr_api.g_varchar2) or
706 nvl(ben_vep_shd.g_old_rec.vep_attribute7, hr_api.g_varchar2) <>
707 nvl(p_rec.vep_attribute7, hr_api.g_varchar2) or
708 nvl(ben_vep_shd.g_old_rec.vep_attribute8, hr_api.g_varchar2) <>
709 nvl(p_rec.vep_attribute8, hr_api.g_varchar2) or
710 nvl(ben_vep_shd.g_old_rec.vep_attribute9, hr_api.g_varchar2) <>
711 nvl(p_rec.vep_attribute9, hr_api.g_varchar2) or
712 nvl(ben_vep_shd.g_old_rec.vep_attribute10, hr_api.g_varchar2) <>
713 nvl(p_rec.vep_attribute10, hr_api.g_varchar2) or
714 nvl(ben_vep_shd.g_old_rec.vep_attribute11, hr_api.g_varchar2) <>
715 nvl(p_rec.vep_attribute11, hr_api.g_varchar2) or
716 nvl(ben_vep_shd.g_old_rec.vep_attribute12, hr_api.g_varchar2) <>
717 nvl(p_rec.vep_attribute12, hr_api.g_varchar2) or
718 nvl(ben_vep_shd.g_old_rec.vep_attribute13, hr_api.g_varchar2) <>
719 nvl(p_rec.vep_attribute13, hr_api.g_varchar2) or
720 nvl(ben_vep_shd.g_old_rec.vep_attribute14, hr_api.g_varchar2) <>
721 nvl(p_rec.vep_attribute14, hr_api.g_varchar2) or
722 nvl(ben_vep_shd.g_old_rec.vep_attribute15, hr_api.g_varchar2) <>
723 nvl(p_rec.vep_attribute15, hr_api.g_varchar2) or
724 nvl(ben_vep_shd.g_old_rec.vep_attribute16, hr_api.g_varchar2) <>
725 nvl(p_rec.vep_attribute16, hr_api.g_varchar2) or
726 nvl(ben_vep_shd.g_old_rec.vep_attribute17, hr_api.g_varchar2) <>
727 nvl(p_rec.vep_attribute17, hr_api.g_varchar2) or
728 nvl(ben_vep_shd.g_old_rec.vep_attribute18, hr_api.g_varchar2) <>
729 nvl(p_rec.vep_attribute18, hr_api.g_varchar2) or
730 nvl(ben_vep_shd.g_old_rec.vep_attribute19, hr_api.g_varchar2) <>
731 nvl(p_rec.vep_attribute19, hr_api.g_varchar2) or
732 nvl(ben_vep_shd.g_old_rec.vep_attribute20, hr_api.g_varchar2) <>
733 nvl(p_rec.vep_attribute20, hr_api.g_varchar2) or
734 nvl(ben_vep_shd.g_old_rec.vep_attribute21, hr_api.g_varchar2) <>
735 nvl(p_rec.vep_attribute21, hr_api.g_varchar2) or
736 nvl(ben_vep_shd.g_old_rec.vep_attribute22, hr_api.g_varchar2) <>
737 nvl(p_rec.vep_attribute22, hr_api.g_varchar2) or
738 nvl(ben_vep_shd.g_old_rec.vep_attribute23, hr_api.g_varchar2) <>
739 nvl(p_rec.vep_attribute23, hr_api.g_varchar2) or
740 nvl(ben_vep_shd.g_old_rec.vep_attribute24, hr_api.g_varchar2) <>
741 nvl(p_rec.vep_attribute24, hr_api.g_varchar2) or
742 nvl(ben_vep_shd.g_old_rec.vep_attribute25, hr_api.g_varchar2) <>
743 nvl(p_rec.vep_attribute25, hr_api.g_varchar2) or
744 nvl(ben_vep_shd.g_old_rec.vep_attribute26, hr_api.g_varchar2) <>
745 nvl(p_rec.vep_attribute26, hr_api.g_varchar2) or
746 nvl(ben_vep_shd.g_old_rec.vep_attribute27, hr_api.g_varchar2) <>
747 nvl(p_rec.vep_attribute27, hr_api.g_varchar2) or
748 nvl(ben_vep_shd.g_old_rec.vep_attribute28, hr_api.g_varchar2) <>
749 nvl(p_rec.vep_attribute28, hr_api.g_varchar2) or
750 nvl(ben_vep_shd.g_old_rec.vep_attribute29, hr_api.g_varchar2) <>
751 nvl(p_rec.vep_attribute29, hr_api.g_varchar2) or
752 nvl(ben_vep_shd.g_old_rec.vep_attribute30, hr_api.g_varchar2) <>
753 nvl(p_rec.vep_attribute30, hr_api.g_varchar2) ))
754 or (p_rec.vrbl_rt_elig_prfl_idis null) then
755 --
756 -- Only execute the validation if absolutely necessary:
757 -- a) During update, the structure column value or any
758 -- of the attribute values have actually changed.
759 -- b) During insert.
760 --
761 hr_dflex_utility.ins_or_upd_descflex_attribs
762 (p_appl_short_name => 'BEN'
763 ,p_descflex_name => 'EDIT_HERE: Enter descflex name'
764 ,p_attribute_category => 'VEP_ATTRIBUTE_CATEGORY'
765 ,p_attribute1_name => 'VEP_ATTRIBUTE1'
766 ,p_attribute1_value => p_rec.vep_attribute1
767 ,p_attribute2_name => 'VEP_ATTRIBUTE2'
768 ,p_attribute2_value => p_rec.vep_attribute2
769 ,p_attribute3_name => 'VEP_ATTRIBUTE3'
770 ,p_attribute3_value => p_rec.vep_attribute3
771 ,p_attribute4_name => 'VEP_ATTRIBUTE4'
772 ,p_attribute4_value => p_rec.vep_attribute4
773 ,p_attribute5_name => 'VEP_ATTRIBUTE5'
774 ,p_attribute5_value => p_rec.vep_attribute5
775 ,p_attribute6_name => 'VEP_ATTRIBUTE6'
776 ,p_attribute6_value => p_rec.vep_attribute6
777 ,p_attribute7_name => 'VEP_ATTRIBUTE7'
778 ,p_attribute7_value => p_rec.vep_attribute7
779 ,p_attribute8_name => 'VEP_ATTRIBUTE8'
780 ,p_attribute8_value => p_rec.vep_attribute8
781 ,p_attribute9_name => 'VEP_ATTRIBUTE9'
782 ,p_attribute9_value => p_rec.vep_attribute9
783 ,p_attribute10_name => 'VEP_ATTRIBUTE10'
784 ,p_attribute10_value => p_rec.vep_attribute10
785 ,p_attribute11_name => 'VEP_ATTRIBUTE11'
786 ,p_attribute11_value => p_rec.vep_attribute11
787 ,p_attribute12_name => 'VEP_ATTRIBUTE12'
788 ,p_attribute12_value => p_rec.vep_attribute12
789 ,p_attribute13_name => 'VEP_ATTRIBUTE13'
790 ,p_attribute13_value => p_rec.vep_attribute13
791 ,p_attribute14_name => 'VEP_ATTRIBUTE14'
792 ,p_attribute14_value => p_rec.vep_attribute14
793 ,p_attribute15_name => 'VEP_ATTRIBUTE15'
794 ,p_attribute15_value => p_rec.vep_attribute15
795 ,p_attribute16_name => 'VEP_ATTRIBUTE16'
796 ,p_attribute16_value => p_rec.vep_attribute16
797 ,p_attribute17_name => 'VEP_ATTRIBUTE17'
798 ,p_attribute17_value => p_rec.vep_attribute17
799 ,p_attribute18_name => 'VEP_ATTRIBUTE18'
800 ,p_attribute18_value => p_rec.vep_attribute18
801 ,p_attribute19_name => 'VEP_ATTRIBUTE19'
802 ,p_attribute19_value => p_rec.vep_attribute19
803 ,p_attribute20_name => 'VEP_ATTRIBUTE20'
804 ,p_attribute20_value => p_rec.vep_attribute20
805 ,p_attribute21_name => 'VEP_ATTRIBUTE21'
806 ,p_attribute21_value => p_rec.vep_attribute21
807 ,p_attribute22_name => 'VEP_ATTRIBUTE22'
808 ,p_attribute22_value => p_rec.vep_attribute22
809 ,p_attribute23_name => 'VEP_ATTRIBUTE23'
810 ,p_attribute23_value => p_rec.vep_attribute23
811 ,p_attribute24_name => 'VEP_ATTRIBUTE24'
812 ,p_attribute24_value => p_rec.vep_attribute24
813 ,p_attribute25_name => 'VEP_ATTRIBUTE25'
814 ,p_attribute25_value => p_rec.vep_attribute25
815 ,p_attribute26_name => 'VEP_ATTRIBUTE26'
816 ,p_attribute26_value => p_rec.vep_attribute26
817 ,p_attribute27_name => 'VEP_ATTRIBUTE27'
818 ,p_attribute27_value => p_rec.vep_attribute27
819 ,p_attribute28_name => 'VEP_ATTRIBUTE28'
820 ,p_attribute28_value => p_rec.vep_attribute28
821 ,p_attribute29_name => 'VEP_ATTRIBUTE29'
822 ,p_attribute29_value => p_rec.vep_attribute29
823 ,p_attribute30_name => 'VEP_ATTRIBUTE30'
824 ,p_attribute30_value => p_rec.vep_attribute30
825 );
826 end if;
827 --
828 hr_utility.set_location(' Leaving:'||l_proc,20);
829 end chk_df;
830 */
831 --
832 /*
833 -- ----------------------------------------------------------------------------
834 -- |-----------------------< chk_non_updateable_args >------------------------|
835 -- ----------------------------------------------------------------------------
836 -- {Start Of Comments}
837 --
838 -- Description:
839 -- This procedure is used to ensure that non updateable attributes have
840 -- not been updated. If an attribute has been updated an error is generated.
841 --
842 -- Pre Conditions:
843 -- g_old_rec has been populated with details of the values currently in
844 -- the database.
845 --
846 -- In Arguments:
847 -- p_rec has been populated with the updated values the user would like the
848 -- record set to.
849 --
850 -- Post Success:
851 -- Processing continues if all the non updateable attributes have not
852 -- changed.
853 --
854 -- Post Failure:
855 -- An application error is raised if any of the non updatable attributes
856 -- have been altered.
857 --
858 -- {End Of Comments}
859 -- ----------------------------------------------------------------------------
860 Procedure chk_non_updateable_args
861 (p_effective_date in date
862 ,p_rec in ben_vep_shd.g_rec_type
863 ) IS
864 --
865 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
866 l_error EXCEPTION;
867 l_argument varchar2(30);
868 --
869 Begin
870 --
871 -- Only proceed with the validation if a row exists for the current
872 -- record in the HR Schema.
873 --
874 IF NOT ben_vep_shd.api_updating
875 (p_vrbl_rt_elig_prfl_id => p_rec.vrbl_rt_elig_prfl_id
876 ,p_effective_date => p_effective_date
877 ,p_object_version_number => p_rec.object_version_number
878 ) THEN
879 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
880 fnd_message.set_token('PROCEDURE ', l_proc);
881 fnd_message.set_token('STEP ', '5');
882 fnd_message.raise_error;
883 END IF;
884 --
885 -- EDIT_HERE: Add checks to ensure non-updateable args have
886 -- not been updated.
887 --
888 EXCEPTION
889 WHEN l_error THEN
890 hr_api.argument_changed_error
891 (p_api_name => l_proc
892 ,p_argument => l_argument);
893 WHEN OTHERS THEN
894 RAISE;
895 End chk_non_updateable_args;
896 */
897 --
898 --
899 -- ----------------------------------------------------------------------------
900 -- |----------------------< chk_ttlcov_ttlprtt_mutexcl >----------------------|
901 -- ----------------------------------------------------------------------------
902 -- Added for checks during usage of 'Eligbility Profiles' for defining
903 -- criteria for calculation of Variable Coverages and Variable Actual Premiums.
904 -- Bug : 3456400
905 --
906 --
907 Procedure chk_ttlcov_ttlprtt_mutexcl
908 (p_vrbl_rt_prfl_id in number
909 ,p_eligy_prfl_id in number
910 ,p_effective_date in date
911 ,p_business_group_id in number
912 ) is
913 --
914 l_dummy varchar2(1);
915 l_proc varchar2(72) := g_package||'chk_ttlcov_ttlprtt_mutexcl';
916 --
917 cursor c1 is
918 select null
919 from ben_elig_ttl_cvg_vol_prte_f
920 where eligy_prfl_id = p_eligy_prfl_id
921 and exists
922 (select null
923 from ben_elig_ttl_prtt_prte_f
924 where eligy_prfl_id in
925 (select eligy_prfl_id
926 from ben_vrbl_rt_elig_prfl_f
927 where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
928 and business_group_id = p_business_group_id
929 and p_effective_date between effective_start_date and effective_end_date)
930 or eligy_prfl_id = p_eligy_prfl_id);
931 --
932 cursor c2 is
933 select null
934 from ben_elig_ttl_prtt_prte_f
935 where eligy_prfl_id = p_eligy_prfl_id
936 and exists
937 (select null
938 from ben_elig_ttl_cvg_vol_prte_f
939 where eligy_prfl_id in
940 (select eligy_prfl_id
941 from ben_vrbl_rt_elig_prfl_f
942 where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
943 and business_group_id = p_business_group_id
944 and p_effective_date between effective_start_date and effective_end_date)
945 or eligy_prfl_id = p_eligy_prfl_id);
946
947 --
948 --
949 Begin
950 --
951 hr_utility.set_location('Entering:'||l_proc, 5);
952 --
953 open c1;
954 fetch c1 into l_dummy;
955 --
956 if c1%found then
957 close c1;
958 fnd_message.set_name('BEN','BEN_92258_TTLPRTT_CVGVOL_EXCL1');
959 fnd_message.raise_error;
960 end if;
961 --
962 close c1;
963 --
964 --
965 open c2;
966 fetch c2 into l_dummy;
967 --
968 if c2%found then
969 close c2;
970 fnd_message.set_name('BEN','BEN_92259_TTLPRTT_CVGVOL_EXCL2');
971 fnd_message.raise_error;
972 end if;
973 --
974 close c2;
975 --
976 --
977 hr_utility.set_location('Leaving:'||l_proc, 10);
978 End chk_ttlcov_ttlprtt_mutexcl;
979 --
980 --
981 -- ----------------------------------------------------------------------------
982 -- |----------------------< chk_ttlcov_ttlprtt_mlt_cd >-----------------------|
983 -- ----------------------------------------------------------------------------
984 -- Added for checks during usage of 'Eligbility Profiles' for defining
985 -- criteria for calculation of Variable Coverages and Variable Actual Premiums.
986 -- Bug : 3456400
987 --
988 Procedure chk_ttlcov_ttlprtt_mlt_cd
989 (p_vrbl_rt_prfl_id in number
990 ,p_eligy_prfl_id in number
991 ,p_effective_date in date
992 ,p_business_group_id in number
993 ) is
994 --
995 l_dummy varchar2(1);
996 l_proc varchar2(72) := g_package||'chk_ttlcov_ttlprtt_mlt_cd';
997 l_mlt_cd varchar2(30);
998 l_vrbl_usg_cd varchar2(30);
999 --
1000 cursor c1 is
1001 select mlt_cd, vrbl_usg_cd
1002 from ben_vrbl_rt_prfl_f
1003 where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
1004 and business_group_id = p_business_group_id
1005 and p_effective_date between effective_start_date and effective_end_date;
1006 --
1007 cursor c2 is
1008 select null
1009 from ben_elig_ttl_prtt_prte_f
1010 where eligy_prfl_id = p_eligy_prfl_id
1011 and business_group_id = p_business_group_id
1012 and p_effective_date between effective_start_date and effective_end_date;
1013 --
1014 cursor c3 is
1015 select null
1016 from ben_elig_ttl_cvg_vol_prte_f
1017 where eligy_prfl_id = p_eligy_prfl_id
1018 and business_group_id = p_business_group_id
1019 and p_effective_date between effective_start_date and effective_end_date;
1020 --
1021 Begin
1022 --
1023 hr_utility.set_location('Entering:'||l_proc, 5);
1024 --
1025 open c1;
1026 fetch c1 into l_mlt_cd, l_vrbl_usg_cd;
1027 close c1;
1028 --
1029 open c2;
1030 fetch c2 into l_dummy;
1031 if c2%found then
1032 if l_vrbl_usg_cd = 'ACP' and l_mlt_cd <> 'TTLPRTT' then
1033 close c2;
1034 fnd_message.set_name('BEN','BEN_92263_TTLPRTT_MLTCD');
1035 fnd_message.raise_error;
1036 end if;
1037 end if;
1038 close c2;
1039 --
1040 --
1041 open c3;
1042 fetch c3 into l_dummy;
1043 if c3%found then
1044 if l_vrbl_usg_cd = 'ACP' and l_mlt_cd <> 'TTLCVG' then
1045 close c3;
1046 fnd_message.set_name('BEN','BEN_92264_TTLCVG_MLTCD');
1047 fnd_message.raise_error;
1048 end if;
1049 end if;
1050 close c3;
1051 --
1052 hr_utility.set_location('Leaving:'||l_proc, 10);
1053 End chk_ttlcov_ttlprtt_mlt_cd;
1054 --
1055
1056 --
1057 -- ----------------------------------------------------------------------------
1058 -- |--------------------------< dt_update_validate >--------------------------|
1059 -- ----------------------------------------------------------------------------
1060 -- {Start Of Comments}
1061 --
1062 -- Description:
1063 -- This procedure is used for referential integrity of datetracked
1064 -- parent entities when a datetrack update operation is taking place
1065 -- and where there is no cascading of update defined for this entity.
1066 --
1067 -- Prerequisites:
1068 -- This procedure is called from the update_validate.
1069 --
1070 -- In Parameters:
1071 --
1072 -- Post Success:
1073 -- Processing continues.
1074 --
1075 -- Post Failure:
1076 --
1077 -- Developer Implementation Notes:
1078 -- This procedure should not need maintenance unless the HR Schema model
1079 -- changes.
1080 --
1081 -- Access Status:
1082 -- Internal Row Handler Use Only.
1083 --
1084 -- {End Of Comments}
1085 -- ----------------------------------------------------------------------------
1086 Procedure dt_update_validate
1087 (
1088 p_vrbl_rt_prfl_id in number default hr_api.g_number
1089 ,p_eligy_prfl_id in number default hr_api.g_number
1090 ,p_datetrack_mode in varchar2
1091 ,p_validation_start_date in date
1092 ,p_validation_end_date in date
1093 ) Is
1094 --
1095 l_proc varchar2(72) := g_package||'dt_update_validate';
1096 l_integrity_error Exception;
1097 l_table_name all_tables.table_name%TYPE;
1098 --
1099 Begin
1100 --
1101 -- Ensure that the p_datetrack_mode argument is not null
1102 --
1103 hr_api.mandatory_arg_error
1104 (p_api_name => l_proc
1105 ,p_argument => 'datetrack_mode'
1106 ,p_argument_value => p_datetrack_mode
1107 );
1108 --
1109 -- Mode will be valid, as this is checked at the start of the upd.
1110 --
1111 -- Ensure the arguments are not null
1112 --
1113 hr_api.mandatory_arg_error
1114 (p_api_name => l_proc
1115 ,p_argument => 'validation_start_date'
1116 ,p_argument_value => p_validation_start_date
1117 );
1118 --
1119 hr_api.mandatory_arg_error
1120 (p_api_name => l_proc
1121 ,p_argument => 'validation_end_date'
1122 ,p_argument_value => p_validation_end_date
1123 );
1124 --
1125 Exception
1126 When Others Then
1127 --
1128 -- An unhandled or unexpected error has occurred which
1129 -- we must report
1130 --
1131 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1132 fnd_message.set_token('PROCEDURE', l_proc);
1133 fnd_message.set_token('STEP','15');
1134 fnd_message.raise_error;
1135 End dt_update_validate;
1136 --
1137 -- ----------------------------------------------------------------------------
1138 -- |--------------------------< dt_delete_validate >--------------------------|
1139 -- ----------------------------------------------------------------------------
1140 -- {Start Of Comments}
1141 --
1142 -- Description:
1143 -- This procedure is used for referential integrity of datetracked
1144 -- child entities when either a datetrack DELETE or ZAP is in operation
1145 -- and where there is no cascading of delete defined for this entity.
1146 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1147 -- datetracked child rows exist between the validation start and end
1148 -- dates.
1149 --
1150 -- Prerequisites:
1151 -- This procedure is called from the delete_validate.
1152 --
1153 -- In Parameters:
1154 --
1155 -- Post Success:
1156 -- Processing continues.
1157 --
1158 -- Post Failure:
1159 -- If a row exists by determining the returning Boolean value from the
1160 -- generic dt_api.rows_exist function then we must supply an error via
1161 -- the use of the local exception handler l_rows_exist.
1162 --
1163 -- Developer Implementation Notes:
1164 -- This procedure should not need maintenance unless the HR Schema model
1165 -- changes.
1166 --
1167 -- Access Status:
1168 -- Internal Row Handler Use Only.
1169 --
1170 -- {End Of Comments}
1171 -- ----------------------------------------------------------------------------
1172 Procedure dt_delete_validate
1173 (p_vrbl_rt_elig_prfl_id in number
1174 ,p_datetrack_mode in varchar2
1175 ,p_validation_start_date in date
1176 ,p_validation_end_date in date
1177 ) Is
1178 --
1179 l_proc varchar2(72) := g_package||'dt_delete_validate';
1180 l_rows_exist Exception;
1181 l_table_name all_tables.table_name%TYPE;
1182 --
1183 Begin
1184 --
1185 -- Ensure that the p_datetrack_mode argument is not null
1186 --
1187 hr_api.mandatory_arg_error
1188 (p_api_name => l_proc
1189 ,p_argument => 'datetrack_mode'
1190 ,p_argument_value => p_datetrack_mode
1191 );
1192 --
1193 -- Only perform the validation if the datetrack mode is either
1194 -- DELETE or ZAP
1195 --
1196 If (p_datetrack_mode = hr_api.g_delete or
1197 p_datetrack_mode = hr_api.g_zap) then
1198 --
1199 --
1200 -- Ensure the arguments are not null
1201 --
1202 hr_api.mandatory_arg_error
1203 (p_api_name => l_proc
1204 ,p_argument => 'validation_start_date'
1205 ,p_argument_value => p_validation_start_date
1206 );
1207 --
1208 hr_api.mandatory_arg_error
1209 (p_api_name => l_proc
1210 ,p_argument => 'validation_end_date'
1211 ,p_argument_value => p_validation_end_date
1212 );
1213 --
1214 hr_api.mandatory_arg_error
1215 (p_api_name => l_proc
1216 ,p_argument => 'VRBL_RT_ELIG_PRFL_ID'
1217 ,p_argument_value => p_vrbl_rt_elig_prfl_id
1218 );
1219 --
1220 --
1221 --
1222 End If;
1223 --
1224 Exception
1225 When l_rows_exist Then
1226 --
1227 -- A referential integrity check was violated therefore
1228 -- we must error
1229 --
1230 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
1231 fnd_message.set_token('TABLE_NAME', l_table_name);
1232 fnd_message.raise_error;
1233 When Others Then
1234 --
1235 -- An unhandled or unexpected error has occurred which
1236 -- we must report
1237 --
1238 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1239 fnd_message.set_token('PROCEDURE', l_proc);
1240 fnd_message.set_token('STEP','15');
1241 fnd_message.raise_error;
1242 --
1243 End dt_delete_validate;
1244 --
1245 -- ----------------------------------------------------------------------------
1246 -- |---------------------------< insert_validate >----------------------------|
1247 -- ----------------------------------------------------------------------------
1248 Procedure insert_validate
1249 (p_rec in ben_vep_shd.g_rec_type
1250 ,p_effective_date in date
1251 ,p_datetrack_mode in varchar2
1252 ,p_validation_start_date in date
1253 ,p_validation_end_date in date
1254 ) is
1255 --
1256 l_proc varchar2(72) := g_package||'insert_validate';
1257 --
1258 Begin
1259 hr_utility.set_location('Entering:'||l_proc, 5);
1260 --
1261 -- Call all supporting business operations
1262 --
1263 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1264 --
1265 --
1266 --ben_vep_bus.chk_df(p_rec);
1267
1268 chk_vrbl_rt_elig_prfl_id
1269 (p_vrbl_rt_elig_prfl_id => p_rec.vrbl_rt_elig_prfl_id,
1270 p_effective_date => p_effective_date,
1271 p_object_version_number => p_rec.object_version_number);
1272 -- Check the future rows for eligy_prfl/effective_date combination
1273 --
1274 chk_mndtry_flag
1275 (p_vrbl_rt_elig_prfl_id => p_rec.vrbl_rt_elig_prfl_id,
1276 p_mndtry_flag => p_rec.mndtry_flag,
1277 p_effective_date => p_effective_date,
1278 p_object_version_number => p_rec.object_version_number);
1279
1280 chk_vrbl_rt_elig_prfl_count(
1281 p_vrbl_rt_prfl_id =>p_rec.vrbl_rt_prfl_id,
1282 p_effective_date => p_effective_date);
1283
1284 chk_uniq_vrbl_rt_elig_prfl
1285 (
1286 p_vrbl_rt_elig_prfl_id => p_rec.vrbl_rt_elig_prfl_id,
1287 p_vrbl_rt_prfl_id =>p_rec.vrbl_rt_prfl_id,
1288 p_eligy_prfl_id =>p_rec.eligy_prfl_id,
1289 p_effective_date =>p_effective_date,
1290 p_object_version_number => p_rec.object_version_number);
1291 chk_elig_prfl_criteria
1292 (
1293 p_vrbl_rt_elig_prfl_id => p_rec.vrbl_rt_elig_prfl_id,
1294 p_vrbl_rt_prfl_id =>p_rec.vrbl_rt_prfl_id,
1295 p_eligy_prfl_id =>p_rec.eligy_prfl_id,
1296 p_effective_date =>p_effective_date,
1297 p_object_version_number => p_rec.object_version_number);
1298 -- Bug : 3456400
1299 chk_ttlcov_ttlprtt_mutexcl
1300 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1301 p_eligy_prfl_id => p_rec.eligy_prfl_id,
1302 p_effective_date => p_effective_date,
1303 p_business_group_id => p_rec.business_group_id);
1304 chk_ttlcov_ttlprtt_mlt_cd
1305 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1306 p_eligy_prfl_id => p_rec.eligy_prfl_id,
1307 p_effective_date => p_effective_date,
1308 p_business_group_id => p_rec.business_group_id);
1309
1310
1311 hr_utility.set_location(' Leaving:'||l_proc, 10);
1312 End insert_validate;
1313 --
1314 -- ----------------------------------------------------------------------------
1315 -- |---------------------------< update_validate >----------------------------|
1316 -- ----------------------------------------------------------------------------
1317 Procedure update_validate
1318 (p_rec in ben_vep_shd.g_rec_type
1319 ,p_effective_date in date
1320 ,p_datetrack_mode in varchar2
1321 ,p_validation_start_date in date
1322 ,p_validation_end_date in date
1323 ) is
1324 --
1325 l_proc varchar2(72) := g_package||'update_validate';
1326 --
1327 Begin
1328 hr_utility.set_location('Entering:'||l_proc, 5);
1329 --
1330 -- Call all supporting business operations
1331 --
1332 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1333 --
1334 chk_vrbl_rt_elig_prfl_id
1335 (p_vrbl_rt_elig_prfl_id => p_rec.vrbl_rt_elig_prfl_id,
1336 p_effective_date => p_effective_date,
1337 p_object_version_number => p_rec.object_version_number);
1338
1339 chk_mndtry_flag
1340 (p_vrbl_rt_elig_prfl_id => p_rec.vrbl_rt_elig_prfl_id,
1341 p_mndtry_flag => p_rec.mndtry_flag,
1342 p_effective_date => p_effective_date,
1343 p_object_version_number => p_rec.object_version_number);
1344
1345 chk_uniq_vrbl_rt_elig_prfl
1346 (
1347 p_vrbl_rt_elig_prfl_id => p_rec.vrbl_rt_elig_prfl_id,
1348 p_vrbl_rt_prfl_id =>p_rec.vrbl_rt_prfl_id,
1349 p_eligy_prfl_id =>p_rec.eligy_prfl_id,
1350 p_effective_date =>p_effective_date,
1351 p_object_version_number => p_rec.object_version_number);
1352
1353 chk_elig_prfl_criteria
1354 (
1355 p_vrbl_rt_elig_prfl_id => p_rec.vrbl_rt_elig_prfl_id,
1356 p_vrbl_rt_prfl_id =>p_rec.vrbl_rt_prfl_id,
1357 p_eligy_prfl_id =>p_rec.eligy_prfl_id,
1358 p_effective_date =>p_effective_date,
1359 p_object_version_number => p_rec.object_version_number);
1360
1361 -- Bug : 3456400
1362 chk_ttlcov_ttlprtt_mutexcl
1363 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1364 p_eligy_prfl_id => p_rec.eligy_prfl_id,
1365 p_effective_date => p_effective_date,
1366 p_business_group_id => p_rec.business_group_id);
1367 chk_ttlcov_ttlprtt_mlt_cd
1368 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1369 p_eligy_prfl_id => p_rec.eligy_prfl_id,
1370 p_effective_date => p_effective_date,
1371 p_business_group_id => p_rec.business_group_id);
1372
1373 -- Call the datetrack update integrity operation
1374 --
1375 dt_update_validate
1376 (
1377 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
1378 ,p_eligy_prfl_id => p_rec.eligy_prfl_id
1379 ,p_datetrack_mode => p_datetrack_mode
1380 ,p_validation_start_date => p_validation_start_date
1381 ,p_validation_end_date => p_validation_end_date
1382 );
1383 --
1384 /*
1385 chk_non_updateable_args
1386 (p_effective_date => p_effective_date
1387 ,p_rec => p_rec
1388 );
1389 */
1390 --
1391 --
1392 --ben_vep_bus.chk_df(p_rec);
1393 --
1394 hr_utility.set_location(' Leaving:'||l_proc, 10);
1395 End update_validate;
1396 --
1397 -- ----------------------------------------------------------------------------
1398 -- |---------------------------< delete_validate >----------------------------|
1399 -- ----------------------------------------------------------------------------
1400 Procedure delete_validate
1401 (p_rec in ben_vep_shd.g_rec_type
1402 ,p_effective_date in date
1403 ,p_datetrack_mode in varchar2
1404 ,p_validation_start_date in date
1405 ,p_validation_end_date in date
1406 ) is
1407 --
1408 l_proc varchar2(72) := g_package||'delete_validate';
1409 --
1410 Begin
1411 hr_utility.set_location('Entering:'||l_proc, 5);
1412 --
1413 -- Call all supporting business operations
1414 --
1415 dt_delete_validate
1416 (p_datetrack_mode => p_datetrack_mode
1417 ,p_validation_start_date => p_validation_start_date
1418 ,p_validation_end_date => p_validation_end_date
1419 ,p_vrbl_rt_elig_prfl_id => p_rec.vrbl_rt_elig_prfl_id
1420 );
1421 --
1422 hr_utility.set_location(' Leaving:'||l_proc, 10);
1423 End delete_validate;
1424 end ben_vep_bus;
[Home] [Help]