[Home] [Help]
PACKAGE BODY: APPS.BEN_JRT_BUS
Source
1 Package Body ben_jrt_bus as
2 /* $Header: bejrtrhi.pkb 120.2 2006/03/30 23:48:52 gsehgal noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_jrt_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_job_rt_id number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |---------------------------------< chk_job_rt_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 -- job_rt_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_job_rt_id(p_job_rt_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_job_rt_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_jrt_shd.api_updating
55 (p_effective_date => p_effective_date,
56 p_job_rt_id => p_job_rt_id,
57 p_object_version_number => p_object_version_number);
58 --
59 if (l_api_updating
60 and nvl(p_job_rt_id,hr_api.g_number)
61 <> ben_jrt_shd.g_old_rec.job_rt_id) then
62 --
63 -- raise error as PK has changed
64 --
65 ben_jrt_shd.constraint_error('BEN_JOB_RT_F_PK');
66 --
67 elsif not l_api_updating then
68 --
69 -- check if PK is null
70 --
71 if p_job_rt_id is not null then
72 --
73 -- raise error as PK is not null
74 --
75 ben_jrt_shd.constraint_error('BEN_JOB_RT_F_PK');
76 --
77 end if;
78 --
79 end if;
80 --
81 hr_utility.set_location('Leaving:'||l_proc, 10);
82 --
83 End chk_job_rt_id;
84 --
85 -- ----------------------------------------------------------------------------
86 -- |------< chk_job_id >------|
87 -- ----------------------------------------------------------------------------
88 --
89 -- Description
90 -- This procedure checks that a referenced foreign key actually exists
91 -- in the referenced table.
92 -- Additionally this procedure will check that job_id is unique
93 -- within the Eligibility profile.
94 --
95 -- Pre-Conditions
96 -- None.
97 --
98 -- In Parameters
99 -- p_job_rt_id PK
100 -- p_job_id ID of FK column
101 -- p_effective_date session date
102 -- p_object_version_number object version number
103 --
104 -- Post Success
105 -- Processing continues
106 --
107 -- Post Failure
108 -- Error raised.
109 --
110 -- Access Status
111 -- Internal table handler use only.
112 --
113 Procedure chk_job_id (p_job_rt_id in number,
114 p_job_id in number,
115 p_vrbl_rt_prfl_id in number,
116 p_validation_start_date in date,
117 p_validation_end_date in date,
118 p_effective_date in date,
119 p_business_group_id in number,
120 p_object_version_number in number) is
121 --
122 l_proc varchar2(72) := g_package||'chk_job_id';
123 l_api_updating boolean;
124 l_dummy varchar2(1);
125 l_exists varchar2(1);
126 --
127 cursor c1 is
128 select null
129 from per_jobs a
130 where a.job_id = p_job_id
131 and a.business_group_id + 0 = p_business_group_id
132 and p_effective_date between a.date_from and
133 nvl(a.date_to, p_effective_date);
134 --
135 cursor c3 is
136 select null
137 from ben_job_rt_f
138 where job_id = p_job_id
139 and vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
140 and vrbl_rt_prfl_id <> nvl(p_job_rt_id,hr_api.g_number)
141 and business_group_id + 0 = p_business_group_id
142 and p_validation_start_date <= effective_end_date
143 and p_validation_end_date >= effective_start_date;
144 --
145 --
146 Begin
147 --
148 hr_utility.set_location('Entering:'||l_proc,5);
149 --
150 l_api_updating := ben_jrt_shd.api_updating
151 (p_job_rt_id => p_job_rt_id,
152 p_effective_date => p_effective_date,
153 p_object_version_number => p_object_version_number);
154 --
155 if (l_api_updating
156 and nvl(p_job_id,hr_api.g_number)
157 <> nvl(ben_jrt_shd.g_old_rec.job_id,hr_api.g_number)
158 or not l_api_updating) then
159 --
160 -- check if job_id value exists in per_jobs table
161 --
162 open c1;
163 --
164 fetch c1 into l_dummy;
165 if c1%notfound then
166 --
167 close c1;
168 --
169 -- raise error as FK does not relate to PK in per_jobs
170 -- table.
171 --
172 ben_jrt_shd.constraint_error('BEN_JOB_RT_FK2');
173 --
174 end if;
175 --
176 close c1;
177 --
178 open c3;
179 fetch c3 into l_exists;
180 if c3%found then
181 close c3;
182 --
183 -- raise error as this job already exists for this profile
184 --
185 fnd_message.set_name('BEN', 'BEN_92992_DUPS_ROW');
186 fnd_message.set_token('VAR1','Job criteria');
187 fnd_message.set_token('VAR2','Variable Rate Profile');
188 fnd_message.raise_error;
189 --
190 end if;
191 close c3;
192 --
193 --
194 end if;
195 --
196 hr_utility.set_location('Leaving:'||l_proc,10);
197 --
198 End chk_job_id;
199 --
200 -- ----------------------------------------------------------------------------
201 -- |------< chk_excld_flag >------|
202 -- ----------------------------------------------------------------------------
203 --
204 -- Description
205 -- This procedure is used to check that the lookup value is valid.
206 --
207 -- Pre Conditions
208 -- None.
209 --
210 -- In Parameters
211 -- job_rt_id PK of record being inserted or updated.
212 -- excld_flag Value of lookup code.
213 -- effective_date effective date
214 -- object_version_number Object version number of record being
215 -- inserted or updated.
216 --
217 -- Post Success
218 -- Processing continues
219 --
220 -- Post Failure
221 -- Error handled by procedure
222 --
223 -- Access Status
224 -- Internal table handler use only.
225 --
226 Procedure chk_excld_flag(p_job_rt_id in number,
227 p_excld_flag in varchar2,
228 p_effective_date in date,
229 p_object_version_number in number) is
230 --
231 l_proc varchar2(72) := g_package||'chk_excld_flag';
232 l_api_updating boolean;
233 --
234 Begin
235 --
236 hr_utility.set_location('Entering:'||l_proc, 5);
237 --
238 l_api_updating := ben_jrt_shd.api_updating
239 (p_job_rt_id => p_job_rt_id,
240 p_effective_date => p_effective_date,
241 p_object_version_number => p_object_version_number);
242 --
243 if (l_api_updating
244 and p_excld_flag
245 <> nvl(ben_jrt_shd.g_old_rec.excld_flag,hr_api.g_varchar2)
246 or not l_api_updating) then
247 --
248 -- check if value of lookup falls within lookup type.
249 --
250 --
251 if hr_api.not_exists_in_hr_lookups
252 (p_lookup_type => 'YES_NO',
253 p_lookup_code => p_excld_flag,
254 p_effective_date => p_effective_date) then
255 --
256 -- raise error as does not exist as lookup
257 --
258 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
259 fnd_message.raise_error;
260 --
261 end if;
262 --
263 end if;
264 --
265 hr_utility.set_location('Leaving:'||l_proc,10);
266 --
267 end chk_excld_flag;
268 --
269 -- ---------------------------------------------------------------------------
270 -- |----------------------< set_security_group_id >--------------------------|
271 -- ---------------------------------------------------------------------------
272 --
273 Procedure set_security_group_id
274 (p_job_rt_id in number
275 ,p_associated_column1 in varchar2 default null
276 ) is
277 --
278 -- Declare cursor
279 --
280 cursor csr_sec_grp is
281 select pbg.security_group_id
282 from per_business_groups pbg
283 , ben_job_rt_f jrt
284 where jrt.job_rt_id = p_job_rt_id
285 and pbg.business_group_id = jrt.business_group_id;
286 --
287 -- Declare local variables
288 --
289 l_security_group_id number;
290 l_proc varchar2(72) := g_package||'set_security_group_id';
291 --
292 begin
293 --
294 hr_utility.set_location('Entering:'|| l_proc, 10);
295 --
296 -- Ensure that all the mandatory parameter are not null
297 --
298 hr_api.mandatory_arg_error
299 (p_api_name => l_proc
300 ,p_argument => 'job_rt_id'
301 ,p_argument_value => p_job_rt_id
302 );
303 --
304 open csr_sec_grp;
305 fetch csr_sec_grp into l_security_group_id;
306 --
307 if csr_sec_grp%notfound then
308 --
309 close csr_sec_grp;
310 --
311 -- The primary key is invalid therefore we must error
312 --
313 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
314 hr_multi_message.add
315 (p_associated_column1
316 => nvl(p_associated_column1,'JOB_RT_ID')
317 );
318 --
319 else
320 close csr_sec_grp;
321 --
322 -- Set the security_group_id in CLIENT_INFO
323 --
324 hr_api.set_security_group_id
325 (p_security_group_id => l_security_group_id
326 );
327 end if;
328 --
329 hr_utility.set_location(' Leaving:'|| l_proc, 20);
330 --
331 end set_security_group_id;
332 --
333 -- ---------------------------------------------------------------------------
334 -- |---------------------< return_legislation_code >-------------------------|
335 -- ---------------------------------------------------------------------------
336 --
337 Function return_legislation_code
338 (p_job_rt_id in number
339 )
340 Return Varchar2 Is
341 --
342 -- Declare cursor
343 --
344 cursor csr_leg_code is
345 select pbg.legislation_code
346 from per_business_groups pbg
347 , ben_job_rt_f jrt
348 where jrt.job_rt_id = p_job_rt_id
349 and pbg.business_group_id = jrt.business_group_id;
350 --
351 -- Declare local variables
352 --
353 l_legislation_code varchar2(150);
354 l_proc varchar2(72) := g_package||'return_legislation_code';
355 --
356 Begin
357 --
358 hr_utility.set_location('Entering:'|| l_proc, 10);
359 --
360 -- Ensure that all the mandatory parameter are not null
361 --
362 hr_api.mandatory_arg_error
363 (p_api_name => l_proc
364 ,p_argument => 'job_rt_id'
365 ,p_argument_value => p_job_rt_id
366 );
367 --
368 if ( nvl(ben_jrt_bus.g_job_rt_id, hr_api.g_number)
369 = p_job_rt_id) then
370 --
371 -- The legislation code has already been found with a previous
372 -- call to this function. Just return the value in the global
373 -- variable.
374 --
375 l_legislation_code := ben_jrt_bus.g_legislation_code;
376 hr_utility.set_location(l_proc, 20);
377 else
378 --
379 -- The ID is different to the last call to this function
380 -- or this is the first call to this function.
381 --
382 open csr_leg_code;
383 fetch csr_leg_code into l_legislation_code;
384 --
385 if csr_leg_code%notfound then
386 --
387 -- The primary key is invalid therefore we must error
388 --
389 close csr_leg_code;
390 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
391 fnd_message.raise_error;
392 end if;
393 hr_utility.set_location(l_proc,30);
394 --
395 -- Set the global variables so the values are
396 -- available for the next call to this function.
397 --
398 close csr_leg_code;
399 ben_jrt_bus.g_job_rt_id := p_job_rt_id;
400 ben_jrt_bus.g_legislation_code := l_legislation_code;
401 end if;
402 hr_utility.set_location(' Leaving:'|| l_proc, 40);
403 return l_legislation_code;
404 end return_legislation_code;
405 --
406 -- ----------------------------------------------------------------------------
407 -- |------------------------------< chk_df >----------------------------------|
408 -- ----------------------------------------------------------------------------
409 --
410 -- Description:
411 -- Validates all the Descriptive Flexfield values.
412 --
413 -- Prerequisites:
414 -- All other columns have been validated. Must be called as the
415 -- last step from insert_validate and update_validate.
416 --
417 -- In Arguments:
418 -- p_rec
419 --
420 -- Post Success:
421 -- If the Descriptive Flexfield structure column and data values are
422 -- all valid this procedure will end normally and processing will
423 -- continue.
424 --
425 -- Post Failure:
426 -- If the Descriptive Flexfield structure column value or any of
427 -- the data values are invalid then an application error is raised as
428 -- a PL/SQL exception.
429 --
430 -- Access Status:
431 -- Internal Row Handler Use Only.
432 --
433 -- ----------------------------------------------------------------------------
434 procedure chk_df
435 (p_rec in ben_jrt_shd.g_rec_type
436 ) is
437 --
438 l_proc varchar2(72) := g_package || 'chk_df';
439 --
440 begin
441 hr_utility.set_location('Entering:'||l_proc,10);
442 --
443 if ((p_rec.job_rt_id is not null) and (
444 nvl(ben_jrt_shd.g_old_rec.jrt_attribute_category, hr_api.g_varchar2) <>
445 nvl(p_rec.jrt_attribute_category, hr_api.g_varchar2) or
446 nvl(ben_jrt_shd.g_old_rec.jrt_attribute1, hr_api.g_varchar2) <>
447 nvl(p_rec.jrt_attribute1, hr_api.g_varchar2) or
451 nvl(p_rec.jrt_attribute3, hr_api.g_varchar2) or
448 nvl(ben_jrt_shd.g_old_rec.jrt_attribute2, hr_api.g_varchar2) <>
449 nvl(p_rec.jrt_attribute2, hr_api.g_varchar2) or
450 nvl(ben_jrt_shd.g_old_rec.jrt_attribute3, hr_api.g_varchar2) <>
452 nvl(ben_jrt_shd.g_old_rec.jrt_attribute4, hr_api.g_varchar2) <>
453 nvl(p_rec.jrt_attribute4, hr_api.g_varchar2) or
454 nvl(ben_jrt_shd.g_old_rec.jrt_attribute5, hr_api.g_varchar2) <>
455 nvl(p_rec.jrt_attribute5, hr_api.g_varchar2) or
456 nvl(ben_jrt_shd.g_old_rec.jrt_attribute6, hr_api.g_varchar2) <>
457 nvl(p_rec.jrt_attribute6, hr_api.g_varchar2) or
458 nvl(ben_jrt_shd.g_old_rec.jrt_attribute7, hr_api.g_varchar2) <>
459 nvl(p_rec.jrt_attribute7, hr_api.g_varchar2) or
460 nvl(ben_jrt_shd.g_old_rec.jrt_attribute8, hr_api.g_varchar2) <>
461 nvl(p_rec.jrt_attribute8, hr_api.g_varchar2) or
462 nvl(ben_jrt_shd.g_old_rec.jrt_attribute9, hr_api.g_varchar2) <>
463 nvl(p_rec.jrt_attribute9, hr_api.g_varchar2) or
464 nvl(ben_jrt_shd.g_old_rec.jrt_attribute10, hr_api.g_varchar2) <>
465 nvl(p_rec.jrt_attribute10, hr_api.g_varchar2) or
466 nvl(ben_jrt_shd.g_old_rec.jrt_attribute11, hr_api.g_varchar2) <>
467 nvl(p_rec.jrt_attribute11, hr_api.g_varchar2) or
468 nvl(ben_jrt_shd.g_old_rec.jrt_attribute12, hr_api.g_varchar2) <>
469 nvl(p_rec.jrt_attribute12, hr_api.g_varchar2) or
470 nvl(ben_jrt_shd.g_old_rec.jrt_attribute13, hr_api.g_varchar2) <>
471 nvl(p_rec.jrt_attribute13, hr_api.g_varchar2) or
472 nvl(ben_jrt_shd.g_old_rec.jrt_attribute14, hr_api.g_varchar2) <>
473 nvl(p_rec.jrt_attribute14, hr_api.g_varchar2) or
474 nvl(ben_jrt_shd.g_old_rec.jrt_attribute15, hr_api.g_varchar2) <>
475 nvl(p_rec.jrt_attribute15, hr_api.g_varchar2) or
476 nvl(ben_jrt_shd.g_old_rec.jrt_attribute16, hr_api.g_varchar2) <>
477 nvl(p_rec.jrt_attribute16, hr_api.g_varchar2) or
478 nvl(ben_jrt_shd.g_old_rec.jrt_attribute17, hr_api.g_varchar2) <>
479 nvl(p_rec.jrt_attribute17, hr_api.g_varchar2) or
480 nvl(ben_jrt_shd.g_old_rec.jrt_attribute18, hr_api.g_varchar2) <>
481 nvl(p_rec.jrt_attribute18, hr_api.g_varchar2) or
482 nvl(ben_jrt_shd.g_old_rec.jrt_attribute19, hr_api.g_varchar2) <>
483 nvl(p_rec.jrt_attribute19, hr_api.g_varchar2) or
484 nvl(ben_jrt_shd.g_old_rec.jrt_attribute20, hr_api.g_varchar2) <>
485 nvl(p_rec.jrt_attribute20, hr_api.g_varchar2) or
486 nvl(ben_jrt_shd.g_old_rec.jrt_attribute21, hr_api.g_varchar2) <>
487 nvl(p_rec.jrt_attribute21, hr_api.g_varchar2) or
488 nvl(ben_jrt_shd.g_old_rec.jrt_attribute22, hr_api.g_varchar2) <>
489 nvl(p_rec.jrt_attribute22, hr_api.g_varchar2) or
490 nvl(ben_jrt_shd.g_old_rec.jrt_attribute23, hr_api.g_varchar2) <>
491 nvl(p_rec.jrt_attribute23, hr_api.g_varchar2) or
492 nvl(ben_jrt_shd.g_old_rec.jrt_attribute24, hr_api.g_varchar2) <>
493 nvl(p_rec.jrt_attribute24, hr_api.g_varchar2) or
494 nvl(ben_jrt_shd.g_old_rec.jrt_attribute25, hr_api.g_varchar2) <>
495 nvl(p_rec.jrt_attribute25, hr_api.g_varchar2) or
496 nvl(ben_jrt_shd.g_old_rec.jrt_attribute26, hr_api.g_varchar2) <>
497 nvl(p_rec.jrt_attribute26, hr_api.g_varchar2) or
498 nvl(ben_jrt_shd.g_old_rec.jrt_attribute27, hr_api.g_varchar2) <>
499 nvl(p_rec.jrt_attribute27, hr_api.g_varchar2) or
500 nvl(ben_jrt_shd.g_old_rec.jrt_attribute28, hr_api.g_varchar2) <>
501 nvl(p_rec.jrt_attribute28, hr_api.g_varchar2) or
502 nvl(ben_jrt_shd.g_old_rec.jrt_attribute29, hr_api.g_varchar2) <>
503 nvl(p_rec.jrt_attribute29, hr_api.g_varchar2) or
504 nvl(ben_jrt_shd.g_old_rec.jrt_attribute30, hr_api.g_varchar2) <>
505 nvl(p_rec.jrt_attribute30, hr_api.g_varchar2) ))
506 or (p_rec.job_rt_id is null) then
507 --
508 -- Only execute the validation if absolutely necessary:
509 -- a) During update, the structure column value or any
510 -- of the attribute values have actually changed.
511 -- b) During insert.
512 --
513 hr_dflex_utility.ins_or_upd_descflex_attribs
514 (p_appl_short_name => 'BEN'
515 ,p_descflex_name => 'BEN_JOB_RT_F'
516 ,p_attribute_category => 'JRT_ATTRIBUTE_CATEGORY'
517 ,p_attribute1_name => 'JRT_ATTRIBUTE1'
518 ,p_attribute1_value => p_rec.jrt_attribute1
519 ,p_attribute2_name => 'JRT_ATTRIBUTE2'
520 ,p_attribute2_value => p_rec.jrt_attribute2
521 ,p_attribute3_name => 'JRT_ATTRIBUTE3'
522 ,p_attribute3_value => p_rec.jrt_attribute3
523 ,p_attribute4_name => 'JRT_ATTRIBUTE4'
524 ,p_attribute4_value => p_rec.jrt_attribute4
525 ,p_attribute5_name => 'JRT_ATTRIBUTE5'
526 ,p_attribute5_value => p_rec.jrt_attribute5
527 ,p_attribute6_name => 'JRT_ATTRIBUTE6'
528 ,p_attribute6_value => p_rec.jrt_attribute6
529 ,p_attribute7_name => 'JRT_ATTRIBUTE7'
530 ,p_attribute7_value => p_rec.jrt_attribute7
531 ,p_attribute8_name => 'JRT_ATTRIBUTE8'
532 ,p_attribute8_value => p_rec.jrt_attribute8
533 ,p_attribute9_name => 'JRT_ATTRIBUTE9'
534 ,p_attribute9_value => p_rec.jrt_attribute9
535 ,p_attribute10_name => 'JRT_ATTRIBUTE10'
536 ,p_attribute10_value => p_rec.jrt_attribute10
537 ,p_attribute11_name => 'JRT_ATTRIBUTE11'
538 ,p_attribute11_value => p_rec.jrt_attribute11
539 ,p_attribute12_name => 'JRT_ATTRIBUTE12'
543 ,p_attribute14_name => 'JRT_ATTRIBUTE14'
540 ,p_attribute12_value => p_rec.jrt_attribute12
541 ,p_attribute13_name => 'JRT_ATTRIBUTE13'
542 ,p_attribute13_value => p_rec.jrt_attribute13
544 ,p_attribute14_value => p_rec.jrt_attribute14
545 ,p_attribute15_name => 'JRT_ATTRIBUTE15'
546 ,p_attribute15_value => p_rec.jrt_attribute15
547 ,p_attribute16_name => 'JRT_ATTRIBUTE16'
548 ,p_attribute16_value => p_rec.jrt_attribute16
549 ,p_attribute17_name => 'JRT_ATTRIBUTE17'
550 ,p_attribute17_value => p_rec.jrt_attribute17
551 ,p_attribute18_name => 'JRT_ATTRIBUTE18'
552 ,p_attribute18_value => p_rec.jrt_attribute18
553 ,p_attribute19_name => 'JRT_ATTRIBUTE19'
554 ,p_attribute19_value => p_rec.jrt_attribute19
555 ,p_attribute20_name => 'JRT_ATTRIBUTE20'
556 ,p_attribute20_value => p_rec.jrt_attribute20
557 ,p_attribute21_name => 'JRT_ATTRIBUTE21'
558 ,p_attribute21_value => p_rec.jrt_attribute21
559 ,p_attribute22_name => 'JRT_ATTRIBUTE22'
560 ,p_attribute22_value => p_rec.jrt_attribute22
561 ,p_attribute23_name => 'JRT_ATTRIBUTE23'
562 ,p_attribute23_value => p_rec.jrt_attribute23
563 ,p_attribute24_name => 'JRT_ATTRIBUTE24'
564 ,p_attribute24_value => p_rec.jrt_attribute24
565 ,p_attribute25_name => 'JRT_ATTRIBUTE25'
566 ,p_attribute25_value => p_rec.jrt_attribute25
567 ,p_attribute26_name => 'JRT_ATTRIBUTE26'
568 ,p_attribute26_value => p_rec.jrt_attribute26
569 ,p_attribute27_name => 'JRT_ATTRIBUTE27'
570 ,p_attribute27_value => p_rec.jrt_attribute27
571 ,p_attribute28_name => 'JRT_ATTRIBUTE28'
572 ,p_attribute28_value => p_rec.jrt_attribute28
573 ,p_attribute29_name => 'JRT_ATTRIBUTE29'
574 ,p_attribute29_value => p_rec.jrt_attribute29
575 ,p_attribute30_name => 'JRT_ATTRIBUTE30'
576 ,p_attribute30_value => p_rec.jrt_attribute30
577 );
578 end if;
579 --
580 hr_utility.set_location(' Leaving:'||l_proc,20);
581 end chk_df;
582 --
583 -- ----------------------------------------------------------------------------
584 -- |-----------------------< chk_non_updateable_args >------------------------|
585 -- ----------------------------------------------------------------------------
586 -- {Start Of Comments}
587 --
588 -- Description:
589 -- This procedure is used to ensure that non updateable attributes have
590 -- not been updated. If an attribute has been updated an error is generated.
591 --
592 -- Pre Conditions:
593 -- g_old_rec has been populated with details of the values currently in
594 -- the database.
595 --
596 -- In Arguments:
597 -- p_rec has been populated with the updated values the user would like the
598 -- record set to.
599 --
600 -- Post Success:
601 -- Processing continues if all the non updateable attributes have not
602 -- changed.
603 --
604 -- Post Failure:
605 -- An application error is raised if any of the non updatable attributes
606 -- have been altered.
607 --
608 -- {End Of Comments}
609 -- ----------------------------------------------------------------------------
610 Procedure chk_non_updateable_args
611 (p_effective_date in date
612 ,p_rec in ben_jrt_shd.g_rec_type
613 ) IS
614 --
615 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
616 --
617 Begin
618 --
619 -- Only proceed with the validation if a row exists for the current
620 -- record in the HR Schema.
621 --
622 IF NOT ben_jrt_shd.api_updating
623 (p_job_rt_id => p_rec.job_rt_id
624 ,p_effective_date => p_effective_date
625 ,p_object_version_number => p_rec.object_version_number
626 ) THEN
627 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
628 fnd_message.set_token('PROCEDURE ', l_proc);
629 fnd_message.set_token('STEP ', '5');
630 fnd_message.raise_error;
631 END IF;
632 --
633 -- EDIT_HERE: Add checks to ensure non-updateable args have
634 -- not been updated.
635 --
636 End chk_non_updateable_args;
637 --
638 -- added for Bug 5078478 .. add this procedure to check the duplicate seq no
639 -- |--------------------< chk_duplicate_ordr_num >----------------------------|
640 -- ----------------------------------------------------------------------------
641 --
642 -- Description
643 --
644 -- Pre Conditions
645 -- None.
646 --
647 -- In Parameters
648 -- p_job_rt_id
649 -- p_vrbl_rt_prfl_id
650 -- p_ordr_num
651 -- p_effective_date
652 -- p_business_group_id
653 --
654 -- Post Success
655 -- Processing continues
656 --
657 -- Post Failure
658 -- Errors handled by the procedure
662 --
659 --
660 -- Access Status
661 -- Internal table handler use only.
663 -- ----------------------------------------------------------------------------
664
665
666 procedure chk_duplicate_ordr_num
667 (p_vrbl_rt_prfl_id in number
668 ,p_job_rt_id in number
669 ,p_ordr_num in number
670 ,p_validation_start_date in date
671 ,p_validation_end_date in date
672 ,p_business_group_id in number)
673 is
674 l_proc varchar2(72) := g_package||' chk_duplicate_ordr_num ';
675 l_dummy char(1);
676 cursor c1 is select null
677 from ben_job_rt_f
678 where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
679 -- changed against bug: 5113011
680 and job_rt_id <> nvl(p_job_rt_id ,-1)
681 --and job_id <> nvl(p_job_id ,-1)
682 --and p_effective_date between effective_start_date
683 -- and effective_end_date
684 and p_validation_start_date <= effective_end_date
685 and p_validation_end_date >= effective_start_date
686 and business_group_id + 0 = p_business_group_id
687 and ordr_num = p_ordr_num;
688 --
689 Begin
690 hr_utility.set_location('Entering:'||l_proc, 5);
691
692 --
693 open c1;
694 fetch c1 into l_dummy;
695 --
696 if c1%found then
697 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
698 fnd_message.raise_error;
699 end if;
700 close c1;
701 --
702 hr_utility.set_location('Leaving:'||l_proc, 15);
703 End chk_duplicate_ordr_num;
704
705
706 --
707 -- ----------------------------------------------------------------------------
708 -- |--------------------------< dt_update_validate >--------------------------|
709 -- ----------------------------------------------------------------------------
710 -- {Start Of Comments}
711 --
712 -- Description:
713 -- This procedure is used for referential integrity of datetracked
714 -- parent entities when a datetrack update operation is taking place
715 -- and where there is no cascading of update defined for this entity.
716 --
717 -- Prerequisites:
718 -- This procedure is called from the update_validate.
719 --
720 -- In Parameters:
721 --
722 -- Post Success:
723 -- Processing continues.
724 --
725 -- Post Failure:
726 --
727 -- Developer Implementation Notes:
728 -- This procedure should not need maintenance unless the HR Schema model
729 -- changes.
730 --
731 -- Access Status:
732 -- Internal Row Handler Use Only.
733 --
734 -- {End Of Comments}
735 -- ----------------------------------------------------------------------------
736 Procedure dt_update_validate
737 (p_vrbl_rt_prfl_id in number default hr_api.g_number
738 ,p_datetrack_mode in varchar2
739 ,p_validation_start_date in date
740 ,p_validation_end_date in date
741 ) Is
742 --
743 l_proc varchar2(72) := g_package||'dt_update_validate';
744 --
745 Begin
746 --
747 -- Ensure that the p_datetrack_mode argument is not null
748 --
749 hr_api.mandatory_arg_error
750 (p_api_name => l_proc
751 ,p_argument => 'datetrack_mode'
752 ,p_argument_value => p_datetrack_mode
753 );
754 --
755 -- Mode will be valid, as this is checked at the start of the upd.
756 --
757 -- Ensure the arguments are not null
758 --
759 hr_api.mandatory_arg_error
760 (p_api_name => l_proc
761 ,p_argument => 'validation_start_date'
762 ,p_argument_value => p_validation_start_date
763 );
764 --
765 hr_api.mandatory_arg_error
766 (p_api_name => l_proc
767 ,p_argument => 'validation_end_date'
768 ,p_argument_value => p_validation_end_date
769 );
770 --
771 If ((nvl(p_vrbl_rt_prfl_id, hr_api.g_number) <> hr_api.g_number) and
772 NOT (dt_api.check_min_max_dates
773 (p_base_table_name => 'ben_vrbl_rt_prfl_f'
774 ,p_base_key_column => 'VRBL_RT_PRFL_ID'
775 ,p_base_key_value => p_vrbl_rt_prfl_id
776 ,p_from_date => p_validation_start_date
777 ,p_to_date => p_validation_end_date))) Then
778 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
779 fnd_message.set_token('TABLE_NAME','vrbl rt prfl');
780 hr_multi_message.add
781 (p_associated_column1 => ben_jrt_shd.g_tab_nam || '.VRBL_RT_PRFL_ID');
782 End If;
783 --
784 Exception
785 When Others Then
786 --
787 -- An unhandled or unexpected error has occurred which
788 -- we must report
789 --
790 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
791 fnd_message.set_token('PROCEDURE', l_proc);
792 fnd_message.set_token('STEP','15');
793 fnd_message.raise_error;
794 End dt_update_validate;
795 --
796 -- ----------------------------------------------------------------------------
797 -- |--------------------------< dt_delete_validate >--------------------------|
801 -- Description:
798 -- ----------------------------------------------------------------------------
799 -- {Start Of Comments}
800 --
802 -- This procedure is used for referential integrity of datetracked
803 -- child entities when either a datetrack DELETE or ZAP is in operation
804 -- and where there is no cascading of delete defined for this entity.
805 -- For the datetrack mode of DELETE or ZAP we must ensure that no
806 -- datetracked child rows exist between the validation start and end
807 -- dates.
808 --
809 -- Prerequisites:
810 -- This procedure is called from the delete_validate.
811 --
812 -- In Parameters:
813 --
814 -- Post Success:
815 -- Processing continues.
816 --
817 -- Post Failure:
818 -- If a row exists by determining the returning Boolean value from the
819 -- generic dt_api.rows_exist function then we must supply an error via
820 -- the use of the local exception handler l_rows_exist.
821 --
822 -- Developer Implementation Notes:
823 -- This procedure should not need maintenance unless the HR Schema model
824 -- changes.
825 --
826 -- Access Status:
827 -- Internal Row Handler Use Only.
828 --
829 -- {End Of Comments}
830 -- ----------------------------------------------------------------------------
831 Procedure dt_delete_validate
832 (p_job_rt_id in number
833 ,p_datetrack_mode in varchar2
834 ,p_validation_start_date in date
835 ,p_validation_end_date in date
836 ) Is
837 --
838 l_proc varchar2(72) := g_package||'dt_delete_validate';
839 --
840 Begin
841 --
842 -- Ensure that the p_datetrack_mode argument is not null
843 --
844 hr_api.mandatory_arg_error
845 (p_api_name => l_proc
846 ,p_argument => 'datetrack_mode'
847 ,p_argument_value => p_datetrack_mode
848 );
849 --
850 -- Only perform the validation if the datetrack mode is either
851 -- DELETE or ZAP
852 --
853 If (p_datetrack_mode = hr_api.g_delete or
854 p_datetrack_mode = hr_api.g_zap) then
855 --
856 --
857 -- Ensure the arguments are not null
858 --
859 hr_api.mandatory_arg_error
860 (p_api_name => l_proc
861 ,p_argument => 'validation_start_date'
862 ,p_argument_value => p_validation_start_date
863 );
864 --
865 hr_api.mandatory_arg_error
866 (p_api_name => l_proc
867 ,p_argument => 'validation_end_date'
868 ,p_argument_value => p_validation_end_date
869 );
870 --
871 hr_api.mandatory_arg_error
872 (p_api_name => l_proc
873 ,p_argument => 'job_rt_id'
874 ,p_argument_value => p_job_rt_id
875 );
876 --
877 --
878 --
879 End If;
880 --
881 Exception
882 When Others Then
883 --
884 -- An unhandled or unexpected error has occurred which
885 -- we must report
886 --
887 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
888 fnd_message.set_token('PROCEDURE', l_proc);
889 fnd_message.set_token('STEP','15');
890 fnd_message.raise_error;
891 --
892 End dt_delete_validate;
893 --
894 -- ----------------------------------------------------------------------------
895 -- |---------------------------< insert_validate >----------------------------|
896 -- ----------------------------------------------------------------------------
897 Procedure insert_validate
898 (p_rec in ben_jrt_shd.g_rec_type
899 ,p_effective_date in date
900 ,p_datetrack_mode in varchar2
901 ,p_validation_start_date in date
902 ,p_validation_end_date in date
903 ) is
904 --
905 l_proc varchar2(72) := g_package||'insert_validate';
906 --
907 Begin
908 hr_utility.set_location('Entering:'||l_proc, 5);
909 --
910 -- Call all supporting business operations
911 --
912 hr_api.validate_bus_grp_id
913 (p_business_group_id => p_rec.business_group_id
914 ,p_associated_column1 => ben_jrt_shd.g_tab_nam
915 || '.BUSINESS_GROUP_ID');
916 --
917
918 chk_job_rt_id
919 (p_job_rt_id => p_rec.job_rt_id,
920 p_effective_date => p_effective_date,
921 p_object_version_number => p_rec.object_version_number);
922 --
923 chk_job_id
924 (p_job_rt_id => p_rec.job_rt_id,
925 p_job_id => p_rec.job_id,
926 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
927 p_validation_start_date => p_validation_start_date,
928 p_validation_end_date => p_validation_end_date,
929 p_effective_date => p_effective_date,
930 p_business_group_id => p_rec.business_group_id,
931 p_object_version_number => p_rec.object_version_number);
932 --
933 chk_excld_flag
934 (p_job_rt_id => p_rec.job_rt_id,
935 p_excld_flag => p_rec.excld_flag,
936 p_effective_date => p_effective_date,
937 p_object_version_number => p_rec.object_version_number);
938 --
939 chk_duplicate_ordr_num
940 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
941 ,p_job_rt_id => p_rec.job_rt_id
942 ,p_ordr_num => p_rec.ordr_num
943 ,p_validation_start_date => p_validation_start_date
944 ,p_validation_end_date => p_validation_end_date
945 ,p_business_group_id => p_rec.business_group_id);
946
947 --
948 -- After validating the set of important attributes,
949 -- if Multiple Message detection is enabled and at least
950 -- one error has been found then abort further validation.
951 --
952 hr_multi_message.end_validation_set;
953 --
954 -- Validate Dependent Attributes
955 --
956 --
957 -- ben_jrt_bus.chk_df(p_rec);
958 --
959 hr_utility.set_location(' Leaving:'||l_proc, 10);
960 End insert_validate;
961 --
962 -- ----------------------------------------------------------------------------
963 -- |---------------------------< update_validate >----------------------------|
964 -- ----------------------------------------------------------------------------
965 Procedure update_validate
966 (p_rec in ben_jrt_shd.g_rec_type
970 ,p_validation_end_date in date
967 ,p_effective_date in date
968 ,p_datetrack_mode in varchar2
969 ,p_validation_start_date in date
971 ) is
972 --
973 l_proc varchar2(72) := g_package||'update_validate';
974 --
975 Begin
976 hr_utility.set_location('Entering:'||l_proc, 5);
977 --
978 -- Call all supporting business operations
979 --
980 hr_api.validate_bus_grp_id
981 (p_business_group_id => p_rec.business_group_id
982 ,p_associated_column1 => ben_jrt_shd.g_tab_nam
983 || '.BUSINESS_GROUP_ID');
984
985 chk_job_rt_id
986 (p_job_rt_id => p_rec.job_rt_id,
987 p_effective_date => p_effective_date,
988 p_object_version_number => p_rec.object_version_number);
989 --
990 chk_job_id
991 (p_job_rt_id => p_rec.job_rt_id,
992 p_job_id => p_rec.job_id,
993 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
994 p_validation_start_date => p_validation_start_date,
995 p_validation_end_date => p_validation_end_date,
996 p_effective_date => p_effective_date,
997 p_business_group_id => p_rec.business_group_id,
998 p_object_version_number => p_rec.object_version_number);
999 --
1000 chk_excld_flag
1001 (p_job_rt_id => p_rec.job_rt_id,
1002 p_excld_flag => p_rec.excld_flag,
1003 p_effective_date => p_effective_date,
1004 p_object_version_number => p_rec.object_version_number);
1005 --
1006 -- added for Bug 5078478 .. add this procedure to check the duplicate seq no
1007 chk_duplicate_ordr_num
1008 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
1009 ,p_job_rt_id => p_rec.job_rt_id
1010 ,p_ordr_num => p_rec.ordr_num
1011 ,p_validation_start_date => p_validation_start_date
1012 ,p_validation_end_date => p_validation_end_date
1013 ,p_business_group_id => p_rec.business_group_id);
1014 --
1015
1016 -- After validating the set of important attributes,
1017 -- if Multiple Message detection is enabled and at least
1018 -- one error has been found then abort further validation.
1019 --
1020 hr_multi_message.end_validation_set;
1021 --
1022 -- Validate Dependent Attributes
1023 --
1024 -- Call the datetrack update integrity operation
1025 --
1026 dt_update_validate
1027 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
1028 ,p_datetrack_mode => p_datetrack_mode
1029 ,p_validation_start_date => p_validation_start_date
1030 ,p_validation_end_date => p_validation_end_date
1031 );
1032 --
1033 chk_non_updateable_args
1034 (p_effective_date => p_effective_date
1035 ,p_rec => p_rec
1036 );
1037 --
1038 --
1039 -- ben_jrt_bus.chk_df(p_rec);
1040 --
1041 hr_utility.set_location(' Leaving:'||l_proc, 10);
1042 End update_validate;
1043 --
1044 -- ----------------------------------------------------------------------------
1045 -- |---------------------------< delete_validate >----------------------------|
1046 -- ----------------------------------------------------------------------------
1047 Procedure delete_validate
1048 (p_rec in ben_jrt_shd.g_rec_type
1049 ,p_effective_date in date
1050 ,p_datetrack_mode in varchar2
1051 ,p_validation_start_date in date
1052 ,p_validation_end_date in date
1053 ) is
1054 --
1055 l_proc varchar2(72) := g_package||'delete_validate';
1056 --
1057 Begin
1058 hr_utility.set_location('Entering:'||l_proc, 5);
1059 --
1060 -- Call all supporting business operations
1061 --
1062 dt_delete_validate
1063 (p_datetrack_mode => p_datetrack_mode
1064 ,p_validation_start_date => p_validation_start_date
1065 ,p_validation_end_date => p_validation_end_date
1066 ,p_job_rt_id => p_rec.job_rt_id
1067 );
1068 --
1069 hr_utility.set_location(' Leaving:'||l_proc, 10);
1070 End delete_validate;
1071 --
1072 end ben_jrt_bus;