[Home] [Help]
PACKAGE BODY: APPS.BEN_OTP_BUS
Source
1 Package Body ben_otp_bus as
2 /* $Header: beotprhi.pkb 115.3 2003/09/25 00:30:57 rpgupta noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_otp_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 --
14 g_legislation_code varchar2(150) default null;
15 g_optip_id number default null;
16 --
17 -- ----------------------------------------------------------------------------
18 -- |------< chk_optip_id >------|
19 -- ----------------------------------------------------------------------------
20 --
21 -- Description
22 -- This procedure is used to check that the primary key for the table
23 -- is created properly. It should be null on insert and
24 -- should not be able to be updated.
25 --
26 -- Pre Conditions
27 -- None.
28 --
29 -- In Parameters
30 -- optip_id PK of record being inserted or updated.
31 -- effective_date Effective Date of session
32 -- object_version_number Object version number of record being
33 -- inserted or updated.
34 --
35 -- Post Success
36 -- Processing continues
37 --
38 -- Post Failure
39 -- Errors handled by the procedure
40 --
41 -- Access Status
42 -- Internal table handler use only.
43 --
44 Procedure chk_optip_id(p_optip_id in number,
45 p_effective_date in date,
46 p_object_version_number in number) is
47 --
48 l_proc varchar2(72) := g_package||'chk_optip_id';
49 l_api_updating boolean;
50 --
51 Begin
52 --
53 hr_utility.set_location('Entering:'||l_proc, 5);
54 --
55 l_api_updating := ben_otp_shd.api_updating
56 (p_effective_date => p_effective_date,
57 p_optip_id => p_optip_id,
58 p_object_version_number => p_object_version_number);
59 --
63 -- raise error as PK has changed
60 if (l_api_updating
61 and nvl(p_optip_id,hr_api.g_number)
62 <> ben_otp_shd.g_old_rec.optip_id) then
64 --
65 ben_otp_shd.constraint_error('BEN_OPTIP_F_PK');
66 --
67 elsif not l_api_updating then
68 --
69 -- check if PK is null
70 --
71 if p_optip_id is not null then
72 --
73 -- raise error as PK is not null
74 --
75 ben_otp_shd.constraint_error('BEN_OPTIP_F_PK');
76 --
77 end if;
78 --
79 end if;
80 --
81 hr_utility.set_location('Leaving:'||l_proc, 10);
82 --
83 End chk_optip_id;
84 --
85 -- ----------------------------------------------------------------------------
86 -- |------< chk_uniq_optip >------|
87 -- ----------------------------------------------------------------------------
88 --
89 -- Description
90 -- This procedure is used to check that the the records is unique with the
91 -- pgm_id, pl_typ_id and opt_id for the effective_date
92 --
93 -- Pre Conditions
94 -- None.
95 --
96 -- In Parameters
97 -- pgm_id
98 -- pl_typ_id
99 -- opt_id
100 -- effective_date Effective Date of session
101 -- object_version_number Object version number of record being
102 -- inserted or updated.
103 --
104 -- Post Success
105 -- Processing continues
106 --
107 -- Post Failure
108 -- Errors handled by the procedure
109 --
110 -- Access Status
111 -- Internal table handler use only.
112 --
113 Procedure chk_uniq_optip( p_pgm_id in number,
114 p_pl_typ_id in number,
115 p_opt_id in number,
116 p_effective_date in date,
117 p_object_version_number in number) is
118 --
119 l_proc varchar2(72) := g_package||'chk_uniq_optip';
120 l_dummy varchar2(1) := null ;
121 --
122 cursor c_uniq_optip is
123 select null from
124 ben_optip_f optip
125 where optip.pgm_id = p_pgm_id
126 and optip.pl_typ_id = p_pl_typ_id
127 and optip.opt_id = p_opt_id
128 and optip.effective_start_date > p_effective_date ;
129 Begin
130 hr_utility.set_location('Entering:'||l_proc, 5);
131 --
132 -- raise error as the record already exists
133 open c_uniq_optip ;
134 fetch c_uniq_optip into l_dummy ;
135 --
136 if c_uniq_optip%found then
137 --
138 hr_utility.set_location('Future record exists.Cannot insert ', 8 ) ;
139 close c_uniq_optip ;
140 fnd_message.set_name('PER','HR_7211_DT_UPD_ROWS_IN_FUTURE');
141 fnd_message.raise_error;
142 --
143 end if;
144 close c_uniq_optip ;
145 --
146 hr_utility.set_location('Leaving:'||l_proc, 10);
147 --
148 End chk_uniq_optip;
149 --
150 -- ---------------------------------------------------------------------------
151 -- |----------------------< set_security_group_id >--------------------------|
152 -- ---------------------------------------------------------------------------
153 --
154 Procedure set_security_group_id
155 (p_optip_id in number
156 ) is
157 --
158 -- Declare cursor
159 --
160 cursor csr_sec_grp is
161 select pbg.security_group_id
162 from per_business_groups pbg
163 , ben_optip_f otp
164 where otp.optip_id = p_optip_id
165 and pbg.business_group_id = otp.business_group_id;
166 --
167 -- Declare local variables
168 --
169 l_security_group_id number;
170 l_proc varchar2(72) := g_package||'set_security_group_id';
171 --
172 begin
173 --
174 hr_utility.set_location('Entering:'|| l_proc, 10);
175 --
176 -- Ensure that all the mandatory parameter are not null
177 --
178 hr_api.mandatory_arg_error
179 (p_api_name => l_proc
180 ,p_argument => 'optip_id'
181 ,p_argument_value => p_optip_id
182 );
183 --
184 open csr_sec_grp;
185 fetch csr_sec_grp into l_security_group_id;
186 --
187 if csr_sec_grp%notfound then
188 --
189 close csr_sec_grp;
190 --
191 -- The primary key is invalid therefore we must error
192 --
193 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
194 fnd_message.raise_error;
195 --
196 end if;
197 close csr_sec_grp;
198 --
199 -- Set the security_group_id in CLIENT_INFO
200 --
201 hr_api.set_security_group_id
202 (p_security_group_id => l_security_group_id
203 );
204 --
205 hr_utility.set_location(' Leaving:'|| l_proc, 20);
206 --
207 end set_security_group_id;
208 --
209 -- ---------------------------------------------------------------------------
210 -- |---------------------< return_legislation_code >-------------------------|
211
212 -- ---------------------------------------------------------------------------
213 --
214 Function return_legislation_code
215
216 (p_optip_id in number
217 )
218 Return Varchar2 Is
219 --
220 -- Declare cursor
221 --
222 cursor csr_leg_code is
223 select pbg.legislation_code
224 from per_business_groups pbg
225 , ben_optip_f otp
226 where otp.optip_id = p_optip_id
230 --
227 and pbg.business_group_id = otp.business_group_id;
228 --
229 -- Declare local variables
231 l_legislation_code varchar2(150);
232 l_proc varchar2(72) := g_package||'return_legislation_code';
233 --
234 Begin
235 --
236 hr_utility.set_location('Entering:'|| l_proc, 10);
237 --
238 -- Ensure that all the mandatory parameter are not null
239 --
240 hr_api.mandatory_arg_error
241 (p_api_name => l_proc
242 ,p_argument => 'optip_id'
243 ,p_argument_value => p_optip_id
244 );
245 --
246 if ( nvl(ben_otp_bus.g_optip_id, hr_api.g_number)
247 = p_optip_id) then
248 --
249 -- The legislation code has already been found with a previous
250 -- call to this function. Just return the value in the global
251 -- variable.
252 --
253 l_legislation_code := ben_otp_bus.g_legislation_code;
254
255 hr_utility.set_location(l_proc, 20);
256 else
257 --
258 -- The ID is different to the last call to this function
259 -- or this is the first call to this function.
260 --
261 open csr_leg_code;
262 fetch csr_leg_code into l_legislation_code;
263
264 --
265 if csr_leg_code%notfound then
266 --
267 -- The primary key is invalid therefore we must error
268 --
269 close csr_leg_code;
270 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
271 fnd_message.raise_error;
272 end if;
273 hr_utility.set_location(l_proc,30);
274 --
275 -- Set the global variables so the values are
276 -- available for the next call to this function.
277 --
278 close csr_leg_code;
279 ben_otp_bus.g_optip_id := p_optip_id;
280 ben_otp_bus.g_legislation_code := l_legislation_code;
281
282 end if;
283 hr_utility.set_location(' Leaving:'|| l_proc, 40);
284 return l_legislation_code;
285
286 end return_legislation_code;
287
288 --
289 /*
290 -- ----------------------------------------------------------------------------
291 -- |------------------------------< chk_df >----------------------------------|
292 -- ----------------------------------------------------------------------------
293 --
294 -- Description:
295 -- Validates all the Descriptive Flexfield values.
296 --
297 -- Prerequisites:
298 -- All other columns have been validated. Must be called as the
299 -- last step from insert_validate and update_validate.
300 --
301 -- In Arguments:
302 -- p_rec
303 --
304 -- Post Success:
305 -- If the Descriptive Flexfield structure column and data values are
306 -- all valid this procedure will end normally and processing will
307 -- continue.
308 --
309 -- Post Failure:
310 -- If the Descriptive Flexfield structure column value or any of
311 -- the data values are invalid then an application error is raised as
312 -- a PL/SQL exception.
313 --
314 -- Access Status:
315 -- Internal Row Handler Use Only.
316 --
317 -- ----------------------------------------------------------------------------
318 procedure chk_df
319 (p_rec in ben_otp_shd.g_rec_type
320 ) is
321 --
322 l_proc varchar2(72) := g_package || 'chk_df';
323 --
324 begin
325 hr_utility.set_location('Entering:'||l_proc,10);
326 --
327 if ((p_rec.optip_id is not null) and (
328 nvl(ben_otp_shd.g_old_rec.legislation_code, hr_api.g_varchar2) <>
329 nvl(ben_otp_shd.g_old_rec.legislation_subgroup, hr_api.g_varchar2) <>
330 nvl(ben_otp_shd.g_old_rec.otp_attribute_category, hr_api.g_varchar2) <>
331 nvl(p_rec.legislation_code, hr_api.g_varchar2) or
332 nvl(p_rec.legislation_subgroup, hr_api.g_varchar2) or
333 nvl(p_rec.otp_attribute_category, hr_api.g_varchar2) or
334 nvl(ben_otp_shd.g_old_rec.otp_attribute1, hr_api.g_varchar2) <>
335 nvl(p_rec.otp_attribute1, hr_api.g_varchar2) or
336 nvl(ben_otp_shd.g_old_rec.otp_attribute2, hr_api.g_varchar2) <>
337 nvl(p_rec.otp_attribute2, hr_api.g_varchar2) or
338 nvl(ben_otp_shd.g_old_rec.otp_attribute3, hr_api.g_varchar2) <>
339 nvl(p_rec.otp_attribute3, hr_api.g_varchar2) or
340 nvl(ben_otp_shd.g_old_rec.otp_attribute4, hr_api.g_varchar2) <>
341 nvl(p_rec.otp_attribute4, hr_api.g_varchar2) or
342 nvl(ben_otp_shd.g_old_rec.otp_attribute5, hr_api.g_varchar2) <>
343 nvl(p_rec.otp_attribute5, hr_api.g_varchar2) or
344 nvl(ben_otp_shd.g_old_rec.otp_attribute6, hr_api.g_varchar2) <>
345 nvl(p_rec.otp_attribute6, hr_api.g_varchar2) or
346 nvl(ben_otp_shd.g_old_rec.otp_attribute7, hr_api.g_varchar2) <>
347 nvl(p_rec.otp_attribute7, hr_api.g_varchar2) or
348 nvl(ben_otp_shd.g_old_rec.otp_attribute8, hr_api.g_varchar2) <>
349 nvl(p_rec.otp_attribute8, hr_api.g_varchar2) or
350 nvl(ben_otp_shd.g_old_rec.otp_attribute9, hr_api.g_varchar2) <>
351 nvl(p_rec.otp_attribute9, hr_api.g_varchar2) or
352 nvl(ben_otp_shd.g_old_rec.otp_attribute10, hr_api.g_varchar2) <>
353 nvl(p_rec.otp_attribute10, hr_api.g_varchar2) or
354 nvl(ben_otp_shd.g_old_rec.otp_attribute11, hr_api.g_varchar2) <>
355 nvl(p_rec.otp_attribute11, hr_api.g_varchar2) or
356 nvl(ben_otp_shd.g_old_rec.otp_attribute12, hr_api.g_varchar2) <>
357 nvl(p_rec.otp_attribute12, hr_api.g_varchar2) or
358 nvl(ben_otp_shd.g_old_rec.otp_attribute13, hr_api.g_varchar2) <>
359 nvl(p_rec.otp_attribute13, hr_api.g_varchar2) or
360 nvl(ben_otp_shd.g_old_rec.otp_attribute14, hr_api.g_varchar2) <>
361 nvl(p_rec.otp_attribute14, hr_api.g_varchar2) or
365 nvl(p_rec.otp_attribute16, hr_api.g_varchar2) or
362 nvl(ben_otp_shd.g_old_rec.otp_attribute15, hr_api.g_varchar2) <>
363 nvl(p_rec.otp_attribute15, hr_api.g_varchar2) or
364 nvl(ben_otp_shd.g_old_rec.otp_attribute16, hr_api.g_varchar2) <>
366 nvl(ben_otp_shd.g_old_rec.otp_attribute17, hr_api.g_varchar2) <>
367 nvl(p_rec.otp_attribute17, hr_api.g_varchar2) or
368 nvl(ben_otp_shd.g_old_rec.otp_attribute18, hr_api.g_varchar2) <>
369 nvl(p_rec.otp_attribute18, hr_api.g_varchar2) or
370 nvl(ben_otp_shd.g_old_rec.otp_attribute19, hr_api.g_varchar2) <>
371 nvl(p_rec.otp_attribute19, hr_api.g_varchar2) or
372 nvl(ben_otp_shd.g_old_rec.otp_attribute20, hr_api.g_varchar2) <>
373 nvl(p_rec.otp_attribute20, hr_api.g_varchar2) or
374 nvl(ben_otp_shd.g_old_rec.otp_attribute21, hr_api.g_varchar2) <>
375 nvl(p_rec.otp_attribute21, hr_api.g_varchar2) or
376 nvl(ben_otp_shd.g_old_rec.otp_attribute22, hr_api.g_varchar2) <>
377 nvl(p_rec.otp_attribute22, hr_api.g_varchar2) or
378 nvl(ben_otp_shd.g_old_rec.otp_attribute23, hr_api.g_varchar2) <>
379 nvl(p_rec.otp_attribute23, hr_api.g_varchar2) or
380 nvl(ben_otp_shd.g_old_rec.otp_attribute24, hr_api.g_varchar2) <>
381 nvl(p_rec.otp_attribute24, hr_api.g_varchar2) or
382 nvl(ben_otp_shd.g_old_rec.otp_attribute25, hr_api.g_varchar2) <>
383 nvl(p_rec.otp_attribute25, hr_api.g_varchar2) or
384 nvl(ben_otp_shd.g_old_rec.otp_attribute26, hr_api.g_varchar2) <>
385 nvl(p_rec.otp_attribute26, hr_api.g_varchar2) or
386 nvl(ben_otp_shd.g_old_rec.otp_attribute27, hr_api.g_varchar2) <>
387 nvl(p_rec.otp_attribute27, hr_api.g_varchar2) or
388 nvl(ben_otp_shd.g_old_rec.otp_attribute28, hr_api.g_varchar2) <>
389 nvl(p_rec.otp_attribute28, hr_api.g_varchar2) or
390 nvl(ben_otp_shd.g_old_rec.otp_attribute29, hr_api.g_varchar2) <>
391 nvl(p_rec.otp_attribute29, hr_api.g_varchar2) or
392 nvl(ben_otp_shd.g_old_rec.otp_attribute30, hr_api.g_varchar2) <>
393 nvl(p_rec.otp_attribute30, hr_api.g_varchar2) ))
394 or (p_rec.optip_idis null) then
395 --
396 -- Only execute the validation if absolutely necessary:
397 -- a) During update, the structure column value or any
398 -- of the attribute values have actually changed.
399 -- b) During insert.
400 --
401 hr_dflex_utility.ins_or_upd_descflex_attribs
402 (p_appl_short_name => 'BEN'
403 ,p_descflex_name => 'EDIT_HERE: Enter descflex name'
404 ,p_attribute_category => 'legislation_code'
405 ,p_attribute_category => 'legislation_subgroup'
406 ,p_attribute_category => 'OTP_ATTRIBUTE_CATEGORY'
407 ,p_attribute1_name => 'OTP_ATTRIBUTE1'
408 ,p_attribute1_value => p_rec.otp_attribute1
409 ,p_attribute2_name => 'OTP_ATTRIBUTE2'
410 ,p_attribute2_value => p_rec.otp_attribute2
411 ,p_attribute3_name => 'OTP_ATTRIBUTE3'
412 ,p_attribute3_value => p_rec.otp_attribute3
413 ,p_attribute4_name => 'OTP_ATTRIBUTE4'
414 ,p_attribute4_value => p_rec.otp_attribute4
415 ,p_attribute5_name => 'OTP_ATTRIBUTE5'
416 ,p_attribute5_value => p_rec.otp_attribute5
417 ,p_attribute6_name => 'OTP_ATTRIBUTE6'
418 ,p_attribute6_value => p_rec.otp_attribute6
419 ,p_attribute7_name => 'OTP_ATTRIBUTE7'
420 ,p_attribute7_value => p_rec.otp_attribute7
421 ,p_attribute8_name => 'OTP_ATTRIBUTE8'
422 ,p_attribute8_value => p_rec.otp_attribute8
423 ,p_attribute9_name => 'OTP_ATTRIBUTE9'
424 ,p_attribute9_value => p_rec.otp_attribute9
425 ,p_attribute10_name => 'OTP_ATTRIBUTE10'
426 ,p_attribute10_value => p_rec.otp_attribute10
427 ,p_attribute11_name => 'OTP_ATTRIBUTE11'
428 ,p_attribute11_value => p_rec.otp_attribute11
429 ,p_attribute12_name => 'OTP_ATTRIBUTE12'
430 ,p_attribute12_value => p_rec.otp_attribute12
431 ,p_attribute13_name => 'OTP_ATTRIBUTE13'
432 ,p_attribute13_value => p_rec.otp_attribute13
433 ,p_attribute14_name => 'OTP_ATTRIBUTE14'
434 ,p_attribute14_value => p_rec.otp_attribute14
435 ,p_attribute15_name => 'OTP_ATTRIBUTE15'
436 ,p_attribute15_value => p_rec.otp_attribute15
437 ,p_attribute16_name => 'OTP_ATTRIBUTE16'
438 ,p_attribute16_value => p_rec.otp_attribute16
439 ,p_attribute17_name => 'OTP_ATTRIBUTE17'
440 ,p_attribute17_value => p_rec.otp_attribute17
441 ,p_attribute18_name => 'OTP_ATTRIBUTE18'
442 ,p_attribute18_value => p_rec.otp_attribute18
443 ,p_attribute19_name => 'OTP_ATTRIBUTE19'
444 ,p_attribute19_value => p_rec.otp_attribute19
445 ,p_attribute20_name => 'OTP_ATTRIBUTE20'
446 ,p_attribute20_value => p_rec.otp_attribute20
447 ,p_attribute21_name => 'OTP_ATTRIBUTE21'
448 ,p_attribute21_value => p_rec.otp_attribute21
449 ,p_attribute22_name => 'OTP_ATTRIBUTE22'
450 ,p_attribute22_value => p_rec.otp_attribute22
451 ,p_attribute23_name => 'OTP_ATTRIBUTE23'
452 ,p_attribute23_value => p_rec.otp_attribute23
453 ,p_attribute24_name => 'OTP_ATTRIBUTE24'
454 ,p_attribute24_value => p_rec.otp_attribute24
458 ,p_attribute26_value => p_rec.otp_attribute26
455 ,p_attribute25_name => 'OTP_ATTRIBUTE25'
456 ,p_attribute25_value => p_rec.otp_attribute25
457 ,p_attribute26_name => 'OTP_ATTRIBUTE26'
459 ,p_attribute27_name => 'OTP_ATTRIBUTE27'
460 ,p_attribute27_value => p_rec.otp_attribute27
461 ,p_attribute28_name => 'OTP_ATTRIBUTE28'
462 ,p_attribute28_value => p_rec.otp_attribute28
463 ,p_attribute29_name => 'OTP_ATTRIBUTE29'
464 ,p_attribute29_value => p_rec.otp_attribute29
465 ,p_attribute30_name => 'OTP_ATTRIBUTE30'
466 ,p_attribute30_value => p_rec.otp_attribute30
467 );
468 end if;
469 --
470 hr_utility.set_location(' Leaving:'||l_proc,20);
471 end chk_df;
472 */
473 --
474 /*
475 -- ----------------------------------------------------------------------------
476 -- |-----------------------< chk_non_updateable_args >------------------------|
477 -- ----------------------------------------------------------------------------
478 -- {Start Of Comments}
479 --
480 -- Description:
481 -- This procedure is used to ensure that non updateable attributes have
482 -- not been updated. If an attribute has been updated an error is generated.
483 --
484 -- Pre Conditions:
485 -- g_old_rec has been populated with details of the values currently in
486 -- the database.
487 --
488 -- In Arguments:
489 -- p_rec has been populated with the updated values the user would like the
490 -- record set to.
491 --
492 -- Post Success:
493 -- Processing continues if all the non updateable attributes have not
494 -- changed.
495 --
496 -- Post Failure:
497 -- An application error is raised if any of the non updatable attributes
498 -- have been altered.
499 --
500 -- {End Of Comments}
501 -- ----------------------------------------------------------------------------
502 Procedure chk_non_updateable_args
503 (p_effective_date in date
504 ,p_rec in ben_otp_shd.g_rec_type
505 ) IS
506 --
507 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
508 l_error EXCEPTION;
509 l_argument varchar2(30);
510 --
511 Begin
512 --
513 -- Only proceed with the validation if a row exists for the current
514 -- record in the HR Schema.
515 --
516 IF NOT ben_otp_shd.api_updating
517 (p_optip_id => p_rec.optip_id
518 ,p_effective_date => p_effective_date
519 ,p_object_version_number => p_rec.object_version_number
520 ) THEN
521 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
522 fnd_message.set_token('PROCEDURE ', l_proc);
523 fnd_message.set_token('STEP ', '5');
524 fnd_message.raise_error;
525 END IF;
526 --
527 -- EDIT_HERE: Add checks to ensure non-updateable args have
528 -- not been updated.
529 --
530 EXCEPTION
531 WHEN l_error THEN
532 hr_api.argument_changed_error
533 (p_api_name => l_proc
534 ,p_argument => l_argument);
535 WHEN OTHERS THEN
536 RAISE;
537 End chk_non_updateable_args;
538 */
539 --
540 -- ----------------------------------------------------------------------------
541 -- |--------------------------< dt_update_validate >--------------------------|
542 -- ----------------------------------------------------------------------------
543 -- {Start Of Comments}
544 --
545 -- Description:
546 -- This procedure is used for referential integrity of datetracked
547 -- parent entities when a datetrack update operation is taking place
548 -- and where there is no cascading of update defined for this entity.
549 --
550 -- Prerequisites:
551 -- This procedure is called from the update_validate.
552 --
553 -- In Parameters:
554 --
555 -- Post Success:
556 -- Processing continues.
557 --
558 -- Post Failure:
559 --
560 -- Developer Implementation Notes:
561 -- This procedure should not need maintenance unless the HR Schema model
562 -- changes.
563 --
564 -- Access Status:
565 -- Internal Row Handler Use Only.
566 --
567 -- {End Of Comments}
568 -- ----------------------------------------------------------------------------
569 Procedure dt_update_validate
570 (p_pgm_id in number default hr_api.g_number
571 ,p_ptip_id in number default hr_api.g_number
572 ,p_opt_id in number default hr_api.g_number
573 ,p_datetrack_mode in varchar2
574 ,p_validation_start_date in date
575 ,p_validation_end_date in date
576 ) Is
577 --
578 l_proc varchar2(72) := g_package||'dt_update_validate';
579 l_integrity_error Exception;
580 l_table_name all_tables.table_name%TYPE;
581 --
582 Begin
583 --
584 -- Ensure that the p_datetrack_mode argument is not null
585 --
586 hr_api.mandatory_arg_error
587 (p_api_name => l_proc
588 ,p_argument => 'datetrack_mode'
589 ,p_argument_value => p_datetrack_mode
590 );
591 --
592 -- Mode will be valid, as this is checked at the start of the upd.
593 --
594 -- Ensure the arguments are not null
595 --
596 hr_api.mandatory_arg_error
597 (p_api_name => l_proc
598 ,p_argument => 'validation_start_date'
599 ,p_argument_value => p_validation_start_date
600 );
601 --
602 hr_api.mandatory_arg_error
603 (p_api_name => l_proc
607 --
604 ,p_argument => 'validation_end_date'
605 ,p_argument_value => p_validation_end_date
606 );
608 If ((nvl(p_pgm_id, hr_api.g_number) <> hr_api.g_number) and
609 NOT (dt_api.check_min_max_dates
610 (p_base_table_name => 'ben_pgm_f'
611 ,p_base_key_column => 'PGM_ID'
612 ,p_base_key_value => p_pgm_id
613 ,p_from_date => p_validation_start_date
614 ,p_to_date => p_validation_end_date))) Then
615 l_table_name := 'pgm';
616 raise l_integrity_error;
617 End If;
618 If ((nvl(p_ptip_id, hr_api.g_number) <> hr_api.g_number) and
619 NOT (dt_api.check_min_max_dates
620 (p_base_table_name => 'ben_ptip_f'
621 ,p_base_key_column => 'PTIP_ID'
622 ,p_base_key_value => p_ptip_id
623 ,p_from_date => p_validation_start_date
624 ,p_to_date => p_validation_end_date))) Then
625 l_table_name := 'ptip';
626 raise l_integrity_error;
627 End If;
628 If ((nvl(p_opt_id, hr_api.g_number) <> hr_api.g_number) and
629 NOT (dt_api.check_min_max_dates
630 (p_base_table_name => 'ben_opt_f'
631 ,p_base_key_column => 'OPT_ID'
632 ,p_base_key_value => p_opt_id
633 ,p_from_date => p_validation_start_date
634 ,p_to_date => p_validation_end_date))) Then
635 l_table_name := 'opt';
636 raise l_integrity_error;
637 End If;
638 --
639 Exception
640 When l_integrity_error Then
641 --
642 -- A referential integrity check was violated therefore
643 -- we must error
644 --
645 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
646 fnd_message.set_token('TABLE_NAME', l_table_name);
647 fnd_message.raise_error;
648 When Others Then
649 --
650 -- An unhandled or unexpected error has occurred which
651 -- we must report
652 --
653 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
654 fnd_message.set_token('PROCEDURE', l_proc);
655 fnd_message.set_token('STEP','15');
656 fnd_message.raise_error;
657 End dt_update_validate;
658 --
659 -- ----------------------------------------------------------------------------
660 -- |--------------------------< dt_delete_validate >--------------------------|
661 -- ----------------------------------------------------------------------------
662 -- {Start Of Comments}
663 --
664 -- Description:
665 -- This procedure is used for referential integrity of datetracked
666 -- child entities when either a datetrack DELETE or ZAP is in operation
667 -- and where there is no cascading of delete defined for this entity.
668 -- For the datetrack mode of DELETE or ZAP we must ensure that no
669 -- datetracked child rows exist between the validation start and end
670 -- dates.
671 --
672 -- Prerequisites:
673 -- This procedure is called from the delete_validate.
674 --
675 -- In Parameters:
676 --
677 -- Post Success:
678 -- Processing continues.
679 --
680 -- Post Failure:
681 -- If a row exists by determining the returning Boolean value from the
682 -- generic dt_api.rows_exist function then we must supply an error via
683 -- the use of the local exception handler l_rows_exist.
684 --
685 -- Developer Implementation Notes:
686 -- This procedure should not need maintenance unless the HR Schema model
687 -- changes.
688 --
689 -- Access Status:
690 -- Internal Row Handler Use Only.
691 --
692 -- {End Of Comments}
693 -- ----------------------------------------------------------------------------
694 Procedure dt_delete_validate
695 (p_optip_id in number
696 ,p_datetrack_mode in varchar2
697 ,p_validation_start_date in date
698 ,p_validation_end_date in date
699 ) Is
700 --
701 l_proc varchar2(72) := g_package||'dt_delete_validate';
702 l_rows_exist Exception;
703 l_table_name all_tables.table_name%TYPE;
704 --
705 Begin
706 --
707 -- Ensure that the p_datetrack_mode argument is not null
708 --
709 hr_api.mandatory_arg_error
710 (p_api_name => l_proc
711 ,p_argument => 'datetrack_mode'
712 ,p_argument_value => p_datetrack_mode
713 );
714 --
715 -- Only perform the validation if the datetrack mode is either
716 -- DELETE or ZAP
717 --
718 If (p_datetrack_mode = hr_api.g_delete or
719 p_datetrack_mode = hr_api.g_zap) then
720 --
721 --
722 -- Ensure the arguments are not null
723 --
724 hr_api.mandatory_arg_error
725 (p_api_name => l_proc
726 ,p_argument => 'validation_start_date'
727 ,p_argument_value => p_validation_start_date
728 );
729 --
730 hr_api.mandatory_arg_error
731 (p_api_name => l_proc
732 ,p_argument => 'validation_end_date'
733 ,p_argument_value => p_validation_end_date
734 );
735 --
736 hr_api.mandatory_arg_error
737 (p_api_name => l_proc
738 ,p_argument => 'optip_id'
739 ,p_argument_value => p_optip_id
740 );
741 --
742 --
743 --
744 End If;
745 --
746 Exception
747 When l_rows_exist Then
748 --
749 -- A referential integrity check was violated therefore
750 -- we must error
751 --
752 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
753 fnd_message.set_token('TABLE_NAME', l_table_name);
754 fnd_message.raise_error;
755 When Others Then
756 --
757 -- An unhandled or unexpected error has occurred which
758 -- we must report
759 --
760 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
761 fnd_message.set_token('PROCEDURE', l_proc);
762 fnd_message.set_token('STEP','15');
763 fnd_message.raise_error;
764 --
765 End dt_delete_validate;
766 --
767 -- ----------------------------------------------------------------------------
768 -- |---------------------------< insert_validate >----------------------------|
769 -- ----------------------------------------------------------------------------
770 Procedure insert_validate
771 (p_rec in ben_otp_shd.g_rec_type
772 ,p_effective_date in date
773 ,p_datetrack_mode in varchar2
774 ,p_validation_start_date in date
775 ,p_validation_end_date in date
776 ) is
777 --
778 l_proc varchar2(72) := g_package||'insert_validate';
779 --
780 Begin
781 hr_utility.set_location('Entering:'||l_proc, 5);
782 --
783 -- Call all supporting business operations
784 --
785 if p_rec.business_group_id is not null and p_rec.legislation_code is null then
786 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
787 end if;
788 --
789 --
790 --ben_otp_bus.chk_df(p_rec);
791 chk_optip_id
792 (p_optip_id => p_rec.optip_id,
793 p_effective_date => p_effective_date,
794 p_object_version_number => p_rec.object_version_number);
795 -- Check the future rows for pgm/pl_typ/opt/effective_date combination
796 --
797 chk_uniq_optip
798 (p_pgm_id =>p_rec.pgm_id,
799 p_pl_typ_id =>p_rec.pl_typ_id,
800 p_opt_id =>p_rec.opt_id,
801 p_effective_date =>p_effective_date,
802 p_object_version_number => p_rec.object_version_number);
803 hr_utility.set_location(' Leaving:'||l_proc, 10);
804 End insert_validate;
805 --
806 -- ----------------------------------------------------------------------------
807 -- |---------------------------< update_validate >----------------------------|
808 -- ----------------------------------------------------------------------------
809 Procedure update_validate
810 (p_rec in ben_otp_shd.g_rec_type
811 ,p_effective_date in date
812 ,p_datetrack_mode in varchar2
813 ,p_validation_start_date in date
814 ,p_validation_end_date in date
815 ) is
816 --
817 l_proc varchar2(72) := g_package||'update_validate';
818 --
819 Begin
820 hr_utility.set_location('Entering:'||l_proc, 5);
821 --
822 -- Call all supporting business operations
823 --
824 if p_rec.business_group_id is not null and p_rec.legislation_code is null then
825 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
826 end if;
827 --
828 chk_optip_id
829 (p_optip_id => p_rec.optip_id,
830 p_effective_date => p_effective_date,
831 p_object_version_number => p_rec.object_version_number);
832
833 -- Call the datetrack update integrity operation
834 --
835 dt_update_validate
836 (p_pgm_id => p_rec.pgm_id
837 ,p_ptip_id => p_rec.ptip_id
838 ,p_opt_id => p_rec.opt_id
839 ,p_datetrack_mode => p_datetrack_mode
840 ,p_validation_start_date => p_validation_start_date
841 ,p_validation_end_date => p_validation_end_date
842 );
843 --
844 /*
845 chk_non_updateable_args
846 (p_effective_date => p_effective_date
847 ,p_rec => p_rec
848 );
849 */
850 --
851 --
852 --ben_otp_bus.chk_df(p_rec);
853 --
854 hr_utility.set_location(' Leaving:'||l_proc, 10);
855 End update_validate;
856 --
857 -- ----------------------------------------------------------------------------
858 -- |---------------------------< delete_validate >----------------------------|
859 -- ----------------------------------------------------------------------------
860 Procedure delete_validate
861 (p_rec in ben_otp_shd.g_rec_type
862 ,p_effective_date in date
863 ,p_datetrack_mode in varchar2
864 ,p_validation_start_date in date
865 ,p_validation_end_date in date
866 ) is
867 --
868 l_proc varchar2(72) := g_package||'delete_validate';
869 --
870 Begin
871 hr_utility.set_location('Entering:'||l_proc, 5);
872 --
873 -- Call all supporting business operations
874 --
875 dt_delete_validate
876 (p_datetrack_mode => p_datetrack_mode
877 ,p_validation_start_date => p_validation_start_date
878 ,p_validation_end_date => p_validation_end_date
879 ,p_optip_id => p_rec.optip_id
880 );
881 --
882 hr_utility.set_location(' Leaving:'||l_proc, 10);
883 End delete_validate;
884 --
885 end ben_otp_bus;