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