1 Package Body ben_pry_bus as
2 /* $Header: bepryrhi.pkb 120.7 2008/04/29 07:14:34 sagnanas noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_pry_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_prtt_rmt_aprvd_fr_pymt_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_prtt_rmt_aprvd_fr_pymt_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , ben_prtt_rmt_aprvd_fr_pymt_f pry
30 where pry.prtt_rmt_aprvd_fr_pymt_id = p_prtt_rmt_aprvd_fr_pymt_id
31 and pbg.business_group_id = pry.business_group_id;
32 --
33 -- Declare local variables
34 --
35 l_security_group_id number;
36 l_proc varchar2(72) := g_package||'set_security_group_id';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'prtt_rmt_aprvd_fr_pymt_id'
47 ,p_argument_value => p_prtt_rmt_aprvd_fr_pymt_id
48 );
49 --
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 --
53 if csr_sec_grp%notfound then
54 --
55 close csr_sec_grp;
56 --
57 -- The primary key is invalid therefore we must error
58 --
59 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
60 fnd_message.raise_error;
61 --
62 end if;
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 20);
72 --
73 end set_security_group_id;
74 --
75 -- ---------------------------------------------------------------------------
76 -- |---------------------< return_legislation_code >-------------------------|
77 -- ---------------------------------------------------------------------------
78 --
79 Function return_legislation_code
80 (p_prtt_rmt_aprvd_fr_pymt_id in number
81 )
82 Return Varchar2 Is
83 --
84 -- Declare cursor
85 --
86 cursor csr_leg_code is
87 select pbg.legislation_code
88 from per_business_groups pbg
89 , ben_prtt_rmt_aprvd_fr_pymt_f pry
90 where pry.prtt_rmt_aprvd_fr_pymt_id = p_prtt_rmt_aprvd_fr_pymt_id
91 and pbg.business_group_id (+) = pry.business_group_id;
92 --
93 -- Declare local variables
94 --
95 l_legislation_code varchar2(150);
96 l_proc varchar2(72) := g_package||'return_legislation_code';
97 --
98 Begin
99 --
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_api.mandatory_arg_error
105 (p_api_name => l_proc
106 ,p_argument => 'prtt_rmt_aprvd_fr_pymt_id'
107 ,p_argument_value => p_prtt_rmt_aprvd_fr_pymt_id
108 );
109 --
110 if ( nvl(ben_pry_bus.g_prtt_rmt_aprvd_fr_pymt_id, hr_api.g_number)
111 = p_prtt_rmt_aprvd_fr_pymt_id) then
112 --
113 -- The legislation code has already been found with a previous
114 -- call to this function. Just return the value in the global
115 -- variable.
116 --
117 l_legislation_code := ben_pry_bus.g_legislation_code;
118 hr_utility.set_location(l_proc, 20);
119 else
120 --
121 -- The ID is different to the last call to this function
122 -- or this is the first call to this function.
123 --
124 open csr_leg_code;
125 fetch csr_leg_code into l_legislation_code;
126 --
127 if csr_leg_code%notfound then
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 close csr_leg_code;
132 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
133 fnd_message.raise_error;
134 end if;
135 hr_utility.set_location(l_proc,30);
136 --
137 -- Set the global variables so the values are
138 -- available for the next call to this function.
139 --
140 close csr_leg_code;
141 ben_pry_bus.g_prtt_rmt_aprvd_fr_pymt_id:= p_prtt_rmt_aprvd_fr_pymt_id;
142 ben_pry_bus.g_legislation_code := l_legislation_code;
143 end if;
144 hr_utility.set_location(' Leaving:'|| l_proc, 40);
145 return l_legislation_code;
146 end return_legislation_code;
147
148
149 --
150 -- ----------------------------------------------------------------------------
151 -- |------------------------------< chk_pymt_amt >----------------------------------|
152 -- ----------------------------------------------------------------------------
153 --
154 -- ----------------------------------------------------------------------------
155 procedure chk_pymt_amt
156 (p_rec in ben_pry_shd.g_rec_type
157 ,p_effective_date in date
158 ) is
159 --
160 l_proc varchar2(72) := g_package || 'chk_pymt_amt';
161 -- future created entry to be taken for calc so p_effective_Date is not validated with
162 -- start date and end date . thi may lead into a problem of entry is date tracked
163 -- curent entry is not taken for calc tath is added in calling proc this helps while updating
164
165 cursor c_pry is
166 select sum(nvl(pry.aprvd_fr_pymt_amt,0))
167 from ben_prtt_rmt_aprvd_fr_pymt_f pry
168 where pry.effective_end_date = hr_api.g_eot --future created entry to be taken for calc
169 and p_rec.prtt_reimbmt_rqst_id = pry.prtt_reimbmt_rqst_id
170 and (p_rec.prtt_rmt_aprvd_fr_pymt_id is null
171 or (p_rec.prtt_rmt_aprvd_fr_pymt_id <> pry.prtt_rmt_aprvd_fr_pymt_id ) )
172 and pry.business_group_id = p_rec.business_group_id ;
173
174
175
176 cursor c_prc is
177 select nvl(prc.aprvd_for_pymt_amt,prc.rqst_amt),
178 prc.popl_yr_perd_id_1,
179 prc.popl_yr_perd_id_2,
180 prc.amt_year1,
181 prc.amt_year2
182 from ben_prtt_reimbmt_rqst_f prc
183 where p_rec.prtt_reimbmt_rqst_id = prc.prtt_reimbmt_rqst_id
184 and p_rec.business_group_id = prc.business_group_id
185 and p_effective_Date between prc.effective_start_date
186 and prc.effective_end_date ;
187
188 l_rqst_amt number ;
189 l_total_pymt_amt number ;
190 l_popl_yr_perd_id_1 number;
191 l_popl_yr_perd_id_2 number;
192 --
193
194 cursor c_pl_info is
195 select pl.pl_id
196 ,pl.cmpr_clms_to_cvg_or_bal_cd
197 ,prc.SUBMITTER_PERSON_ID
198 ,prc.PRTT_ENRT_RSLT_ID
199 ,prc.EXP_INCURD_DT
200 from ben_prtt_reimbmt_rqst_f prc,
201 ben_pl_f pl
202 where prc.prtt_reimbmt_rqst_id = p_rec.prtt_reimbmt_rqst_id
203 and pl.pl_id = prc.pl_id
204 and prc.EXP_INCURD_DT between pl.effective_start_date
205 and pl.effective_end_date ;
206
207
208 cursor c_yr_amount (p_pl_id number ,
209 p_person_id number,
210 p_popl_yr_perd number ) is
211 select sum(nvl(pry.APRVD_FR_PYMT_AMT,0))
212 from ben_prtt_reimbmt_rqst_f prc,
213 ben_prtt_rmt_aprvd_fr_pymt_f pry
214 where prc.submitter_person_id = p_person_id
215 and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
216 and p_pl_id = prc.pl_id
217 and ((prc.popl_yr_perd_id_1 = p_popl_yr_perd and
218 prc.amt_year2 is null) or
219 (prc.popl_yr_perd_id_2 = p_popl_yr_perd
220 and prc.amt_year1 is null))
221 and prc.effective_end_date = hr_api.g_eot --future created entry to be taken for calc
222 and prc.prtt_reimbmt_rqst_id = pry.prtt_reimbmt_rqst_id
223 ;
224 cursor c_prc_overlap(p_pl_id number ,
225 p_person_id number,
226 p_popl_yr_perd number ) is
227 select prtt_reimbmt_rqst_id,
228 popl_yr_perd_id_1,
229 popl_yr_perd_id_2,
230 amt_year1,
231 amt_year2
232 from ben_prtt_reimbmt_rqst_f prc
233 where prc.submitter_person_id = p_person_id
234 and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
235 and p_pl_id = prc.pl_id
236 and (( prc.popl_yr_perd_id_1 = p_popl_yr_perd and prc.amt_year2 is not null)
237 or (prc.popl_yr_perd_id_2 = p_popl_yr_perd and prc.amt_year1 is not null))
238 ;
239 l_prc_overlap c_prc_overlap%rowtype;
240 --
241 cursor c_paid_amt (p_prtt_reimbmt_rqst_id number) is
242 select sum(nvl(pry.APRVD_FR_PYMT_AMT,0))
243 from ben_prtt_rmt_aprvd_fr_pymt_f pry
244 where pry.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id;
245 --
246 l_paid_amt number;
247 l_pl_info c_pl_info%rowtype ;
248 --
249 cursor c_yr_perd(p_popl_yr_perd_id number) is
250 select END_DATE
251 from ben_yr_perd yrp,
252 ben_popl_yr_perd cpy
253 where cpy.popl_yr_perd_id = p_popl_yr_perd_id
254 and cpy.yr_perd_id = yrp.yr_perd_id;
255 --
256 l_yr_end date;
257 l_amt_year1 number;
258 l_amt_year2 number;
259 l_rqst_amt_1 number;
260
261 begin
262 hr_utility.set_location('Entering:'||l_proc,10);
263 --
264 open c_prc ;
265 fetch c_prc into l_rqst_amt,l_popl_yr_perd_id_1,l_popl_yr_perd_id_2,
266 l_amt_year1,l_amt_year2 ;
267 close c_prc ;
268
269 open c_pry ;
270 fetch c_pry into l_total_pymt_amt ;
271 close c_pry ;
272
273 hr_utility.set_location( 'reimburs ' || p_rec.prtt_reimbmt_rqst_id , 110);
274 hr_utility.set_location( 'payment ' || p_rec.prtt_rmt_aprvd_fr_pymt_id , 110);
275 hr_utility.set_location( 'rqst amount ' || l_rqst_amt , 110);
276 hr_utility.set_location( 'to payment amount ' || l_total_pymt_amt , 110);
277 hr_utility.set_location( 'payment amount ' || p_rec.aprvd_fr_pymt_amt , 110);
278
279
280 --- whne the total submited more than requst amounnt throw errors
281 if nvl(l_total_pymt_amt,0) + p_rec.aprvd_fr_pymt_amt > nvl(l_rqst_amt,0) then
282 fnd_message.set_name('BEN', 'BEN_92713_PYMT_MORE_THAN_RMT');
283 fnd_message.raise_error;
284 end if ;
285 --
286
287
288 -- chek the total payment against accrual
289 -- chekc only for comparison code is 'BA'
290 -- for coverage approval takes care of the validation
291 open c_pl_info ;
292 fetch c_pl_info into l_pl_info ;
293 close c_pl_info ;
294 hr_utility.set_location(' bal type '|| l_pl_info.cmpr_clms_to_cvg_or_bal_cd ,20);
295 if l_pl_info.cmpr_clms_to_cvg_or_bal_cd = 'BAL' then
296 -- not in grace period or claim is in only for current year
297 if l_popl_yr_perd_id_2 is null or (l_popl_yr_perd_id_2 is not null and
298 l_amt_year1 is null) then
299 --
300 l_total_pymt_amt := ben_prc_bus.get_year_balance (
301 p_person_id => l_pl_info.submitter_person_id
302 ,p_pgm_id => null
303 ,p_pl_id => l_pl_info.pl_id
304 ,p_business_group_id => p_rec.business_group_id
305 ,p_per_in_ler_id => null
306 ,p_prtt_enrt_rslt_id => l_pl_info.prtt_enrt_rslt_id
307 ,p_effective_date => p_effective_date
308 ,p_exp_incurd_dt => l_pl_info.exp_incurd_dt
309 ) ;
310 hr_utility.set_location(' balance '|| l_total_pymt_amt ,20);
311 --
312 if l_popl_yr_perd_id_2 is null then
313 open c_yr_amount(l_pl_info.pl_id ,
314 l_pl_info.submitter_person_id ,
315 l_popl_yr_perd_id_1 ) ;
316 fetch c_yr_amount into l_rqst_amt ;
317 close c_yr_amount ;
318 --
319 open c_prc_overlap(l_pl_info.pl_id ,
320 l_pl_info.submitter_person_id ,
321 l_popl_yr_perd_id_1 ) ;
322 fetch c_prc_overlap into l_prc_overlap;
323 close c_prc_overlap;
324 --
325 --
326 if l_prc_overlap.prtt_reimbmt_rqst_id is not null then
327 open c_paid_amt(l_prc_overlap.prtt_reimbmt_rqst_id);
328 fetch c_paid_amt into l_paid_amt;
329 close c_paid_amt;
330 end if;
331 if l_paid_amt > 0 then
332 hr_utility.set_location ('Paid Amount '||l_paid_amt,10);
333 if l_prc_overlap.popl_yr_perd_id_1 = l_popl_yr_perd_id_1 then
334 --
335 l_rqst_amt := l_rqst_amt + least(l_paid_amt,l_prc_overlap.amt_year1);
336 elsif
337 --
338 l_prc_overlap.popl_yr_perd_id_2 = l_popl_yr_perd_id_1 then
339 --
340 l_rqst_amt := l_rqst_amt + least((l_paid_amt - l_prc_overlap.amt_year1),l_prc_overlap.amt_year2);
341 end if;
342 --
343 end if;
344 else
345 --
346 open c_yr_amount(l_pl_info.pl_id ,
347 l_pl_info.submitter_person_id ,
348 l_popl_yr_perd_id_2 ) ;
349 fetch c_yr_amount into l_rqst_amt ;
350 close c_yr_amount ;
351 --
352 open c_prc_overlap(l_pl_info.pl_id ,
353 l_pl_info.submitter_person_id ,
354 l_popl_yr_perd_id_2 ) ;
355 fetch c_prc_overlap into l_prc_overlap;
356 close c_prc_overlap;
357 --
358 --l_rqst_amt_1 := l_rqst_amt;
359 --
360 if l_prc_overlap.prtt_reimbmt_rqst_id is not null then
361 open c_paid_amt(l_prc_overlap.prtt_reimbmt_rqst_id);
362 fetch c_paid_amt into l_paid_amt;
363 close c_paid_amt;
364 end if;
365 --
366 if l_paid_amt > 0 then
367 --
368 hr_utility.set_location ('Paid Amount '||l_paid_amt,11);
369 if l_prc_overlap.popl_yr_perd_id_1 = l_popl_yr_perd_id_2 then
370 --
371 l_rqst_amt := l_rqst_amt + least(l_paid_amt,l_prc_overlap.amt_year1);
372 elsif
373 --
374 l_prc_overlap.popl_yr_perd_id_2 = l_popl_yr_perd_id_1 then
375 --
376 l_rqst_amt := l_rqst_amt + least((l_paid_amt - l_prc_overlap.amt_year1),l_prc_overlap.amt_year2);
377 end if;
378 --
379 end if;
380 end if;
381
382 hr_utility.set_location(' total for the yr '|| l_rqst_amt ,20);
383 hr_utility.set_location(' old mount '|| nvl(ben_pry_shd.g_old_rec.aprvd_fr_pymt_amt,0) ,20);
384
385 else
386 --
387 open c_yr_perd (l_popl_yr_perd_id_1);
388 fetch c_yr_perd into l_yr_end;
389 close c_yr_perd;
390 --
391 --
392 l_total_pymt_amt := ben_prc_bus.get_year_balance (
393 p_person_id => l_pl_info.submitter_person_id
394 ,p_pgm_id => null
395 ,p_pl_id => l_pl_info.pl_id
396 ,p_business_group_id => p_rec.business_group_id
397 ,p_per_in_ler_id => null
398 ,p_prtt_enrt_rslt_id => l_pl_info.prtt_enrt_rslt_id
399 ,p_effective_date => l_yr_end
400 ,p_exp_incurd_dt => l_yr_end
401 ) ;
402 hr_utility.set_location(' prevbalance '|| l_total_pymt_amt ,21);
403 open c_yr_amount(l_pl_info.pl_id ,
404 l_pl_info.submitter_person_id ,
405 l_popl_yr_perd_id_1) ;
406 fetch c_yr_amount into l_rqst_amt ;
407 close c_yr_amount ;
408 --
409 open c_prc_overlap(l_pl_info.pl_id ,
410 l_pl_info.submitter_person_id ,
411 l_popl_yr_perd_id_1 ) ;
412 fetch c_prc_overlap into l_prc_overlap;
413 close c_prc_overlap;
414 --
415 if l_prc_overlap.prtt_reimbmt_rqst_id is not null then
416 open c_paid_amt(l_prc_overlap.prtt_reimbmt_rqst_id);
417 fetch c_paid_amt into l_paid_amt;
418 close c_paid_amt;
419 end if;
420 --
421 if l_paid_amt > 0 then
422 hr_utility.set_location ('Paid Amount '||l_paid_amt,12);
423 if l_prc_overlap.popl_yr_perd_id_1 = l_popl_yr_perd_id_1 then
424 --
425 l_rqst_amt := l_rqst_amt + least(l_paid_amt,l_prc_overlap.amt_year1);
426 elsif
427 --
428 l_prc_overlap.popl_yr_perd_id_2 = l_popl_yr_perd_id_1 then
429 --
430 l_rqst_amt := l_rqst_amt + least((l_paid_amt - l_prc_overlap.amt_year1),l_prc_overlap.amt_year2);
431 end if;
432 --
433 end if;
434 hr_utility.set_location(' total for the yr '|| l_rqst_amt ,21);
435 hr_utility.set_location(' old mount '|| nvl(ben_pry_shd.g_old_rec.aprvd_fr_pymt_amt,0) ,22);
436 --
437 if l_amt_year2 is not null then
438 --
439 l_total_pymt_amt := l_total_pymt_amt +
440 ben_prc_bus.get_year_balance (
441 p_person_id => l_pl_info.submitter_person_id
442 ,p_pgm_id => null
443 ,p_pl_id => l_pl_info.pl_id
444 ,p_business_group_id => p_rec.business_group_id
445 ,p_per_in_ler_id => null
446 ,p_prtt_enrt_rslt_id => l_pl_info.prtt_enrt_rslt_id
447 ,p_effective_date => p_effective_date
448 ,p_exp_incurd_dt => l_pl_info.exp_incurd_dt
449 ) ;
450 open c_yr_amount(l_pl_info.pl_id ,
451 l_pl_info.submitter_person_id ,
452 l_popl_yr_perd_id_2) ;
453 fetch c_yr_amount into l_rqst_amt ;
454 close c_yr_amount ;
455 --
456 open c_prc_overlap(l_pl_info.pl_id ,
457 l_pl_info.submitter_person_id ,
458 l_popl_yr_perd_id_1 ) ;
459 fetch c_prc_overlap into l_prc_overlap;
460 close c_prc_overlap;
461 --
462 if l_prc_overlap.prtt_reimbmt_rqst_id is not null then
463 open c_paid_amt(l_prc_overlap.prtt_reimbmt_rqst_id);
464 fetch c_paid_amt into l_paid_amt;
465 close c_paid_amt;
466 end if;
467 --
468 if l_paid_amt > 0 then
469 hr_utility.set_location ('Paid Amount '||l_paid_amt,13);
470 if l_prc_overlap.popl_yr_perd_id_1 = l_popl_yr_perd_id_1 then
471 --
472 l_rqst_amt := l_rqst_amt + least(l_paid_amt,l_prc_overlap.amt_year1);
473 elsif
474 --
475 l_prc_overlap.popl_yr_perd_id_2 = l_popl_yr_perd_id_1 then
476 --
477 l_rqst_amt := l_rqst_amt + least((l_paid_amt - l_prc_overlap.amt_year1),l_prc_overlap.amt_year2);
478 end if;
479 --
480 end if;
481 --
482 end if;
483
484
485 end if;
486 --
487
488 if nvl(l_rqst_amt,0) + nvl(p_rec.aprvd_fr_pymt_amt,0)- nvl(ben_pry_shd.g_old_rec.aprvd_fr_pymt_amt,0)
489 > l_total_pymt_amt then
490
491 fnd_message.set_name('BEN','BEN_92662_AMT_EXCEEDS_BAL');
492 fnd_message.set_token('BAL', l_total_pymt_amt );
493 fnd_message.raise_error;
494 end if ;
495
496
497 end if ;
498
499 --
500 hr_utility.set_location(' Leaving:'||l_proc,20);
501 end chk_pymt_amt;
502
503
504
505 --
506 -- ----------------------------------------------------------------------------
507 -- |------------------------------< chk_df >----------------------------------|
508 -- ----------------------------------------------------------------------------
509 --
510 -- Description:
511 -- Validates all the Descriptive Flexfield values.
512 --
513 -- Prerequisites:
514 -- All other columns have been validated. Must be called as the
515 -- last step from insert_validate and update_validate.
516 --
517 -- In Arguments:
518 -- p_rec
519 --
520 -- Post Success:
521 -- If the Descriptive Flexfield structure column and data values are
522 -- all valid this procedure will end normally and processing will
523 -- continue.
524 --
525 -- Post Failure:
526 -- If the Descriptive Flexfield structure column value or any of
527 -- the data values are invalid then an application error is raised as
528 -- a PL/SQL exception.
529 --
530 -- Access Status:
531 -- Internal Row Handler Use Only.
532 --
533 -- ----------------------------------------------------------------------------
534 procedure chk_df
535 (p_rec in ben_pry_shd.g_rec_type
536 ) is
537 --
538 l_proc varchar2(72) := g_package || 'chk_df';
539 --
540 begin
541 hr_utility.set_location('Entering:'||l_proc,10);
542 --
543 if ((p_rec.prtt_rmt_aprvd_fr_pymt_id is not null) and (
544 nvl(ben_pry_shd.g_old_rec.pry_attribute_category, hr_api.g_varchar2) <>
545 nvl(p_rec.pry_attribute_category, hr_api.g_varchar2) or
546 nvl(ben_pry_shd.g_old_rec.pry_attribute1, hr_api.g_varchar2) <>
547 nvl(p_rec.pry_attribute1, hr_api.g_varchar2) or
548 nvl(ben_pry_shd.g_old_rec.pry_attribute2, hr_api.g_varchar2) <>
549 nvl(p_rec.pry_attribute2, hr_api.g_varchar2) or
550 nvl(ben_pry_shd.g_old_rec.pry_attribute3, hr_api.g_varchar2) <>
551 nvl(p_rec.pry_attribute3, hr_api.g_varchar2) or
552 nvl(ben_pry_shd.g_old_rec.pry_attribute4, hr_api.g_varchar2) <>
553 nvl(p_rec.pry_attribute4, hr_api.g_varchar2) or
554 nvl(ben_pry_shd.g_old_rec.pry_attribute5, hr_api.g_varchar2) <>
555 nvl(p_rec.pry_attribute5, hr_api.g_varchar2) or
556 nvl(ben_pry_shd.g_old_rec.pry_attribute6, hr_api.g_varchar2) <>
557 nvl(p_rec.pry_attribute6, hr_api.g_varchar2) or
558 nvl(ben_pry_shd.g_old_rec.pry_attribute7, hr_api.g_varchar2) <>
559 nvl(p_rec.pry_attribute7, hr_api.g_varchar2) or
560 nvl(ben_pry_shd.g_old_rec.pry_attribute8, hr_api.g_varchar2) <>
561 nvl(p_rec.pry_attribute8, hr_api.g_varchar2) or
562 nvl(ben_pry_shd.g_old_rec.pry_attribute9, hr_api.g_varchar2) <>
563 nvl(p_rec.pry_attribute9, hr_api.g_varchar2) or
564 nvl(ben_pry_shd.g_old_rec.pry_attribute10, hr_api.g_varchar2) <>
565 nvl(p_rec.pry_attribute10, hr_api.g_varchar2) or
566 nvl(ben_pry_shd.g_old_rec.pry_attribute11, hr_api.g_varchar2) <>
567 nvl(p_rec.pry_attribute11, hr_api.g_varchar2) or
568 nvl(ben_pry_shd.g_old_rec.pry_attribute12, hr_api.g_varchar2) <>
569 nvl(p_rec.pry_attribute12, hr_api.g_varchar2) or
570 nvl(ben_pry_shd.g_old_rec.pry_attribute13, hr_api.g_varchar2) <>
571 nvl(p_rec.pry_attribute13, hr_api.g_varchar2) or
572 nvl(ben_pry_shd.g_old_rec.pry_attribute14, hr_api.g_varchar2) <>
573 nvl(p_rec.pry_attribute14, hr_api.g_varchar2) or
574 nvl(ben_pry_shd.g_old_rec.pry_attribute15, hr_api.g_varchar2) <>
575 nvl(p_rec.pry_attribute15, hr_api.g_varchar2) or
576 nvl(ben_pry_shd.g_old_rec.pry_attribute16, hr_api.g_varchar2) <>
577 nvl(p_rec.pry_attribute16, hr_api.g_varchar2) or
578 nvl(ben_pry_shd.g_old_rec.pry_attribute17, hr_api.g_varchar2) <>
579 nvl(p_rec.pry_attribute17, hr_api.g_varchar2) or
580 nvl(ben_pry_shd.g_old_rec.pry_attribute18, hr_api.g_varchar2) <>
581 nvl(p_rec.pry_attribute18, hr_api.g_varchar2) or
582 nvl(ben_pry_shd.g_old_rec.pry_attribute19, hr_api.g_varchar2) <>
583 nvl(p_rec.pry_attribute19, hr_api.g_varchar2) or
584 nvl(ben_pry_shd.g_old_rec.pry_attribute20, hr_api.g_varchar2) <>
585 nvl(p_rec.pry_attribute20, hr_api.g_varchar2) or
586 nvl(ben_pry_shd.g_old_rec.pry_attribute21, hr_api.g_varchar2) <>
587 nvl(p_rec.pry_attribute21, hr_api.g_varchar2) or
588 nvl(ben_pry_shd.g_old_rec.pry_attribute22, hr_api.g_varchar2) <>
589 nvl(p_rec.pry_attribute22, hr_api.g_varchar2) or
590 nvl(ben_pry_shd.g_old_rec.pry_attribute23, hr_api.g_varchar2) <>
591 nvl(p_rec.pry_attribute23, hr_api.g_varchar2) or
592 nvl(ben_pry_shd.g_old_rec.pry_attribute24, hr_api.g_varchar2) <>
593 nvl(p_rec.pry_attribute24, hr_api.g_varchar2) or
594 nvl(ben_pry_shd.g_old_rec.pry_attribute25, hr_api.g_varchar2) <>
595 nvl(p_rec.pry_attribute25, hr_api.g_varchar2) or
596 nvl(ben_pry_shd.g_old_rec.pry_attribute26, hr_api.g_varchar2) <>
597 nvl(p_rec.pry_attribute26, hr_api.g_varchar2) or
598 nvl(ben_pry_shd.g_old_rec.pry_attribute27, hr_api.g_varchar2) <>
599 nvl(p_rec.pry_attribute27, hr_api.g_varchar2) or
600 nvl(ben_pry_shd.g_old_rec.pry_attribute28, hr_api.g_varchar2) <>
601 nvl(p_rec.pry_attribute28, hr_api.g_varchar2) or
602 nvl(ben_pry_shd.g_old_rec.pry_attribute29, hr_api.g_varchar2) <>
603 nvl(p_rec.pry_attribute29, hr_api.g_varchar2) or
604 nvl(ben_pry_shd.g_old_rec.pry_attribute30, hr_api.g_varchar2) <>
605 nvl(p_rec.pry_attribute30, hr_api.g_varchar2) ))
606 or (p_rec.prtt_rmt_aprvd_fr_pymt_id is null) then
607 --
608 -- Only execute the validation if absolutely necessary:
609 -- a) During update, the structure column value or any
610 -- of the attribute values have actually changed.
611 -- b) During insert.
612 --
613 hr_dflex_utility.ins_or_upd_descflex_attribs
614 (p_appl_short_name => 'BEN'
615 ,p_descflex_name => 'EDIT_HERE: Enter descflex name'
616 ,p_attribute_category => 'PRY_ATTRIBUTE_CATEGORY'
617 ,p_attribute1_name => 'PRY_ATTRIBUTE1'
618 ,p_attribute1_value => p_rec.pry_attribute1
619 ,p_attribute2_name => 'PRY_ATTRIBUTE2'
620 ,p_attribute2_value => p_rec.pry_attribute2
621 ,p_attribute3_name => 'PRY_ATTRIBUTE3'
622 ,p_attribute3_value => p_rec.pry_attribute3
623 ,p_attribute4_name => 'PRY_ATTRIBUTE4'
624 ,p_attribute4_value => p_rec.pry_attribute4
625 ,p_attribute5_name => 'PRY_ATTRIBUTE5'
626 ,p_attribute5_value => p_rec.pry_attribute5
627 ,p_attribute6_name => 'PRY_ATTRIBUTE6'
628 ,p_attribute6_value => p_rec.pry_attribute6
629 ,p_attribute7_name => 'PRY_ATTRIBUTE7'
630 ,p_attribute7_value => p_rec.pry_attribute7
631 ,p_attribute8_name => 'PRY_ATTRIBUTE8'
632 ,p_attribute8_value => p_rec.pry_attribute8
633 ,p_attribute9_name => 'PRY_ATTRIBUTE9'
634 ,p_attribute9_value => p_rec.pry_attribute9
635 ,p_attribute10_name => 'PRY_ATTRIBUTE10'
636 ,p_attribute10_value => p_rec.pry_attribute10
637 ,p_attribute11_name => 'PRY_ATTRIBUTE11'
638 ,p_attribute11_value => p_rec.pry_attribute11
639 ,p_attribute12_name => 'PRY_ATTRIBUTE12'
640 ,p_attribute12_value => p_rec.pry_attribute12
641 ,p_attribute13_name => 'PRY_ATTRIBUTE13'
642 ,p_attribute13_value => p_rec.pry_attribute13
643 ,p_attribute14_name => 'PRY_ATTRIBUTE14'
644 ,p_attribute14_value => p_rec.pry_attribute14
645 ,p_attribute15_name => 'PRY_ATTRIBUTE15'
646 ,p_attribute15_value => p_rec.pry_attribute15
647 ,p_attribute16_name => 'PRY_ATTRIBUTE16'
648 ,p_attribute16_value => p_rec.pry_attribute16
649 ,p_attribute17_name => 'PRY_ATTRIBUTE17'
650 ,p_attribute17_value => p_rec.pry_attribute17
651 ,p_attribute18_name => 'PRY_ATTRIBUTE18'
652 ,p_attribute18_value => p_rec.pry_attribute18
653 ,p_attribute19_name => 'PRY_ATTRIBUTE19'
654 ,p_attribute19_value => p_rec.pry_attribute19
655 ,p_attribute20_name => 'PRY_ATTRIBUTE20'
656 ,p_attribute20_value => p_rec.pry_attribute20
657 ,p_attribute21_name => 'PRY_ATTRIBUTE21'
658 ,p_attribute21_value => p_rec.pry_attribute21
659 ,p_attribute22_name => 'PRY_ATTRIBUTE22'
660 ,p_attribute22_value => p_rec.pry_attribute22
661 ,p_attribute23_name => 'PRY_ATTRIBUTE23'
662 ,p_attribute23_value => p_rec.pry_attribute23
663 ,p_attribute24_name => 'PRY_ATTRIBUTE24'
664 ,p_attribute24_value => p_rec.pry_attribute24
665 ,p_attribute25_name => 'PRY_ATTRIBUTE25'
666 ,p_attribute25_value => p_rec.pry_attribute25
667 ,p_attribute26_name => 'PRY_ATTRIBUTE26'
668 ,p_attribute26_value => p_rec.pry_attribute26
669 ,p_attribute27_name => 'PRY_ATTRIBUTE27'
670 ,p_attribute27_value => p_rec.pry_attribute27
671 ,p_attribute28_name => 'PRY_ATTRIBUTE28'
672 ,p_attribute28_value => p_rec.pry_attribute28
673 ,p_attribute29_name => 'PRY_ATTRIBUTE29'
674 ,p_attribute29_value => p_rec.pry_attribute29
675 ,p_attribute30_name => 'PRY_ATTRIBUTE30'
676 ,p_attribute30_value => p_rec.pry_attribute30
677 );
678 end if;
679 --
680 hr_utility.set_location(' Leaving:'||l_proc,20);
681 end chk_df;
682 --
683 -- ----------------------------------------------------------------------------
684 -- |-----------------------< chk_non_updateable_args >------------------------|
685 -- ----------------------------------------------------------------------------
686 -- {Start Of Comments}
687 --
688 -- Description:
689 -- This procedure is used to ensure that non updateable attributes have
690 -- not been updated. If an attribute has been updated an error is generated.
691 --
692 -- Pre Conditions:
693 -- g_old_rec has been populated with details of the values currently in
694 -- the database.
695 --
696 -- In Arguments:
697 -- p_rec has been populated with the updated values the user would like the
698 -- record set to.
699 --
700 -- Post Success:
701 -- Processing continues if all the non updateable attributes have not
702 -- changed.
703 --
704 -- Post Failure:
705 -- An application error is raised if any of the non updatable attributes
706 -- have been altered.
707 --
708 -- {End Of Comments}
709 -- ----------------------------------------------------------------------------
710 Procedure chk_non_updateable_args
711 (p_effective_date in date
712 ,p_rec in ben_pry_shd.g_rec_type
713 ) IS
714 --
715 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
716 l_error EXCEPTION;
717 l_argument varchar2(30);
718 --
719 Begin
720 --
721 -- Only proceed with the validation if a row exists for the current
722 -- record in the HR Schema.
723 --
724 IF NOT ben_pry_shd.api_updating
725 (p_prtt_rmt_aprvd_fr_pymt_id => p_rec.prtt_rmt_aprvd_fr_pymt_id
726 ,p_effective_date => p_effective_date
727 ,p_object_version_number => p_rec.object_version_number
728 ) THEN
729 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
730 fnd_message.set_token('PROCEDURE ', l_proc);
731 fnd_message.set_token('STEP ', '5');
732 fnd_message.raise_error;
733 END IF;
734 --
735 -- EDIT_HERE: Add checks to ensure non-updateable args have
736 -- not been updated.
737 --
738 EXCEPTION
739 WHEN l_error THEN
740 hr_api.argument_changed_error
741 (p_api_name => l_proc
742 ,p_argument => l_argument);
743 WHEN OTHERS THEN
744 RAISE;
745 End chk_non_updateable_args;
746 --
747 -- ----------------------------------------------------------------------------
748 -- |--------------------------< dt_update_validate >--------------------------|
749 -- ----------------------------------------------------------------------------
750 -- {Start Of Comments}
751 --
752 -- Description:
753 -- This procedure is used for referential integrity of datetracked
754 -- parent entities when a datetrack update operation is taking place
755 -- and where there is no cascading of update defined for this entity.
756 --
757 -- Prerequisites:
758 -- This procedure is called from the update_validate.
759 --
760 -- In Parameters:
761 --
762 -- Post Success:
763 -- Processing continues.
764 --
765 -- Post Failure:
766 --
767 -- Developer Implementation Notes:
768 -- This procedure should not need maintenance unless the HR Schema model
769 -- changes.
770 --
771 -- Access Status:
772 -- Internal Row Handler Use Only.
773 --
774 -- {End Of Comments}
775 -- ----------------------------------------------------------------------------
776 Procedure dt_update_validate
777 (p_prtt_reimbmt_rqst_id in number default hr_api.g_number
778 ,p_datetrack_mode in varchar2
779 ,p_validation_start_date in date
780 ,p_validation_end_date in date
781 ) Is
782 --
783 l_proc varchar2(72) := g_package||'dt_update_validate';
784 l_integrity_error Exception;
785 l_table_name all_tables.table_name%TYPE;
786 --
787 Begin
788 --
789 -- Ensure that the p_datetrack_mode argument is not null
790 --
791 hr_api.mandatory_arg_error
792 (p_api_name => l_proc
793 ,p_argument => 'datetrack_mode'
794 ,p_argument_value => p_datetrack_mode
795 );
796 --
797 -- Mode will be valid, as this is checked at the start of the upd.
798 --
799 -- Ensure the arguments are not null
800 --
801 hr_api.mandatory_arg_error
802 (p_api_name => l_proc
803 ,p_argument => 'validation_start_date'
804 ,p_argument_value => p_validation_start_date
805 );
806 --
807 hr_api.mandatory_arg_error
808 (p_api_name => l_proc
809 ,p_argument => 'validation_end_date'
810 ,p_argument_value => p_validation_end_date
811 );
812 --
813 If ((nvl(p_prtt_reimbmt_rqst_id, hr_api.g_number) <> hr_api.g_number) and
814 NOT (dt_api.check_min_max_dates
815 (p_base_table_name => 'ben_prtt_reimbmt_rqst_f'
816 ,p_base_key_column => 'prtt_reimbmt_rqst_id'
817 ,p_base_key_value => p_prtt_reimbmt_rqst_id
818 ,p_from_date => p_validation_start_date
819 ,p_to_date => p_validation_end_date))) Then
820 l_table_name := 'prtt reimbmt rqst';
821 raise l_integrity_error;
822 End If;
823 --
824 Exception
825 When l_integrity_error Then
826 --
827 -- A referential integrity check was violated therefore
828 -- we must error
829 --
830 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
831 fnd_message.set_token('TABLE_NAME', l_table_name);
832 fnd_message.raise_error;
833 When Others Then
834 --
835 -- An unhandled or unexpected error has occurred which
836 -- we must report
837 --
838 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
839 fnd_message.set_token('PROCEDURE', l_proc);
840 fnd_message.set_token('STEP','15');
841 fnd_message.raise_error;
842 End dt_update_validate;
843 --
844 -- ----------------------------------------------------------------------------
845 -- |--------------------------< dt_delete_validate >--------------------------|
846 -- ----------------------------------------------------------------------------
847 -- {Start Of Comments}
848 --
849 -- Description:
850 -- This procedure is used for referential integrity of datetracked
851 -- child entities when either a datetrack DELETE or ZAP is in operation
852 -- and where there is no cascading of delete defined for this entity.
853 -- For the datetrack mode of DELETE or ZAP we must ensure that no
854 -- datetracked child rows exist between the validation start and end
855 -- dates.
856 --
857 -- Prerequisites:
858 -- This procedure is called from the delete_validate.
859 --
860 -- In Parameters:
861 --
862 -- Post Success:
863 -- Processing continues.
864 --
865 -- Post Failure:
866 -- If a row exists by determining the returning Boolean value from the
867 -- generic dt_api.rows_exist function then we must supply an error via
868 -- the use of the local exception handler l_rows_exist.
869 --
870 -- Developer Implementation Notes:
871 -- This procedure should not need maintenance unless the HR Schema model
872 -- changes.
873 --
874 -- Access Status:
875 -- Internal Row Handler Use Only.
876 --
877 -- {End Of Comments}
878 -- ----------------------------------------------------------------------------
879 Procedure dt_delete_validate
880 (p_prtt_rmt_aprvd_fr_pymt_id in number
881 ,p_datetrack_mode in varchar2
882 ,p_validation_start_date in date
883 ,p_validation_end_date in date
884 ) Is
885 --
886 l_proc varchar2(72) := g_package||'dt_delete_validate';
887 l_rows_exist Exception;
888 l_table_name all_tables.table_name%TYPE;
889 --
890 Begin
891 --
892 -- Ensure that the p_datetrack_mode argument is not null
893 --
894 hr_api.mandatory_arg_error
895 (p_api_name => l_proc
896 ,p_argument => 'datetrack_mode'
897 ,p_argument_value => p_datetrack_mode
898 );
899 --
900 -- Only perform the validation if the datetrack mode is either
901 -- DELETE or ZAP
902 --
903 If (p_datetrack_mode = hr_api.g_delete or
904 p_datetrack_mode = hr_api.g_zap) then
905 --
906 --
907 -- Ensure the arguments are not null
908 --
909 hr_api.mandatory_arg_error
910 (p_api_name => l_proc
911 ,p_argument => 'validation_start_date'
912 ,p_argument_value => p_validation_start_date
913 );
914 --
915 hr_api.mandatory_arg_error
916 (p_api_name => l_proc
917 ,p_argument => 'validation_end_date'
918 ,p_argument_value => p_validation_end_date
919 );
920 --
921 hr_api.mandatory_arg_error
922 (p_api_name => l_proc
923 ,p_argument => 'prtt_rmt_aprvd_fr_pymt_id'
924 ,p_argument_value => p_prtt_rmt_aprvd_fr_pymt_id
925 );
926 --
927 --
928 --
929 End If;
930 --
931 Exception
932 When l_rows_exist Then
933 --
934 -- A referential integrity check was violated therefore
935 -- we must error
936 --
937 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
938 fnd_message.set_token('TABLE_NAME', l_table_name);
939 fnd_message.raise_error;
940 When Others Then
941 --
942 -- An unhandled or unexpected error has occurred which
943 -- we must report
944 --
945 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
946 fnd_message.set_token('PROCEDURE', l_proc);
947 fnd_message.set_token('STEP','15');
948 fnd_message.raise_error;
949 --
950 End dt_delete_validate;
951 --
952 -- ----------------------------------------------------------------------------
953 -- |---------------------------< insert_validate >----------------------------|
954 -- ----------------------------------------------------------------------------
955 Procedure insert_validate
956 (p_rec in ben_pry_shd.g_rec_type
957 ,p_effective_date in date
958 ,p_datetrack_mode in varchar2
959 ,p_validation_start_date in date
960 ,p_validation_end_date in date
961 ) is
962 --
963 l_proc varchar2(72) := g_package||'insert_validate';
964 --
965 Begin
966 hr_utility.set_location('Entering:'||l_proc, 5);
967 --
968 -- Call all supporting business operations
969 --
970 -- EDIT_HERE: The following call to hr_api.validate_bus_grp_id
971 -- will only be valid when the business_group_id is not null.
972 -- As this column is defined as optional on the table then
973 -- different logic will be required to handle the null case.
974 -- If this is a start-up data entity then:
975 -- a) add code to stop null values being processed by this
976 -- row handler
977 -- If this is not a start-up data entity then either:
978 -- b) ignore the security_group_id value held in
979 -- client_info. This includes performing lookup
980 -- validation against the HR_STANDARD_LOOKUPS view.
981 -- or c) (less likely) ensure the correct security_group_id
982 -- value is set in client_info.
983 -- Remove this comment when the edit has been completed.
984 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
985 --
986 chk_pymt_amt
987 (p_rec =>p_rec
988 ,p_effective_date => p_effective_date
989 ) ;
990
991
992 --
993 -------------------ben_pry_bus.chk_df(p_rec);
994 --
995 hr_utility.set_location(' Leaving:'||l_proc, 10);
996 End insert_validate;
997 --
998 -- ----------------------------------------------------------------------------
999 -- |---------------------------< update_validate >----------------------------|
1000 -- ----------------------------------------------------------------------------
1001 Procedure update_validate
1002 (p_rec in ben_pry_shd.g_rec_type
1003 ,p_effective_date in date
1004 ,p_datetrack_mode in varchar2
1005 ,p_validation_start_date in date
1006 ,p_validation_end_date in date
1007 ) is
1008 --
1009 l_proc varchar2(72) := g_package||'update_validate';
1010 --
1011 Begin
1012 hr_utility.set_location('Entering:'||l_proc, 5);
1013 --
1014 -- Call all supporting business operations
1015 --
1016 -- EDIT_HERE: The following call to hr_api.validate_bus_grp_id
1017 -- will only be valid when the business_group_id is not null.
1018 -- As this column is defined as optional on the table then
1019 -- different logic will be required to handle the null case.
1020 -- If this is a start-up data entity then:
1021 -- a) add code to stop null values being processed by this
1022 -- row handler
1023 -- If this is not a start-up data entity then either:
1024 -- b) ignore the security_group_id value held in
1025 -- client_info. This includes performing lookup
1026 -- validation against the HR_STANDARD_LOOKUPS view.
1027 -- or c) (less likely) ensure the correct security_group_id
1028 -- value is set in client_info.
1029 -- Remove this comment when the edit has been completed.
1030 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1031 --
1032 -- Call the datetrack update integrity operation
1033 --
1034 dt_update_validate
1035 (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id
1036 ,p_datetrack_mode => p_datetrack_mode
1037 ,p_validation_start_date => p_validation_start_date
1038 ,p_validation_end_date => p_validation_end_date
1039 );
1040 --
1041 chk_non_updateable_args
1042 (p_effective_date => p_effective_date
1043 ,p_rec => p_rec
1044 );
1045 --
1046 chk_pymt_amt
1047 (p_rec =>p_rec
1048 ,p_effective_date => p_effective_date
1049 ) ;
1050
1051 --
1052 --------------ben_pry_bus.chk_df(p_rec);
1053 --
1054 hr_utility.set_location(' Leaving:'||l_proc, 10);
1055 End update_validate;
1056 --
1057 -- ----------------------------------------------------------------------------
1058 -- |---------------------------< delete_validate >----------------------------|
1059 -- ----------------------------------------------------------------------------
1060 Procedure delete_validate
1061 (p_rec in ben_pry_shd.g_rec_type
1062 ,p_effective_date in date
1063 ,p_datetrack_mode in varchar2
1064 ,p_validation_start_date in date
1065 ,p_validation_end_date in date
1066 ) is
1067 --
1068 l_proc varchar2(72) := g_package||'delete_validate';
1069 --
1070 Begin
1071 hr_utility.set_location('Entering:'||l_proc, 5);
1072 --
1073 -- Call all supporting business operations
1074 --
1075 dt_delete_validate
1076 (p_datetrack_mode => p_datetrack_mode
1077 ,p_validation_start_date => p_validation_start_date
1078 ,p_validation_end_date => p_validation_end_date
1079 ,p_prtt_rmt_aprvd_fr_pymt_id => p_rec.prtt_rmt_aprvd_fr_pymt_id
1080 );
1081 --
1082 hr_utility.set_location(' Leaving:'||l_proc, 10);
1083 End delete_validate;
1084 --
1085 end ben_pry_bus;
[Home] [Help]