[Home] [Help]
PACKAGE BODY: APPS.HXC_HAV_BUS
Source
1 Package Body hxc_hav_bus as
2 /* $Header: hxchavrhi.pkb 120.2 2005/09/23 10:41:41 sechandr noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_hav_bus.'; -- Global package name
9 g_debug boolean:=hr_utility.debug_enabled;
10 --
11 -- The following two global variables are only to be
12 -- used by the return_legislation_code function.
13 --
14 g_legislation_code varchar2(150) default null;
15 g_alias_value_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_alias_value_id in number
23 ) is
24 --
25 -- Declare cursor
26 --
27 -- EDIT_HERE In the following cursor statement add join(s) between
28 -- hxc_alias_values and PER_BUSINESS_GROUPS
29 -- so that the security_group_id for
30 -- the current business group context can be derived.
31 -- Remove this comment when the edit has been completed.
32 cursor csr_sec_grp is
33 select pbg.security_group_id
34 from per_business_groups pbg
35 , hxc_alias_values hav
36 -- , EDIT_HERE table_name(s) 333
37 where hav.alias_value_id = p_alias_value_id;
38 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
39 --
40 -- Declare local variables
41 --
42 l_security_group_id number;
43 l_proc varchar2(72);
44 --
45 begin
46 --
47 g_debug:=hr_utility.debug_enabled;
48 if g_debug then
49 l_proc := g_package||'set_security_group_id';
50 hr_utility.set_location('Entering:'|| l_proc, 10);
51 end if;
52 --
53 -- Ensure that all the mandatory parameter are not null
54 --
55 hr_api.mandatory_arg_error
56 (p_api_name => l_proc
57 ,p_argument => 'alias_value_id'
58 ,p_argument_value => p_alias_value_id
59 );
60 --
61 open csr_sec_grp;
62 fetch csr_sec_grp into l_security_group_id;
63 --
64 if csr_sec_grp%notfound then
65 --
66 close csr_sec_grp;
67 --
68 -- The primary key is invalid therefore we must error
69 --
70 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
71 fnd_message.raise_error;
72 --
73 end if;
74 close csr_sec_grp;
75 --
76 -- Set the security_group_id in CLIENT_INFO
77 --
78 hr_api.set_security_group_id
79 (p_security_group_id => l_security_group_id
80 );
81 --
82 if g_debug then
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 end if;
85 --
86 end set_security_group_id;
87 --
88 -- ---------------------------------------------------------------------------
89 -- |---------------------< return_legislation_code >-------------------------|
90 -- ---------------------------------------------------------------------------
91 --
92 Function return_legislation_code
93 (p_alias_value_id in number
94 )
95 Return Varchar2 Is
96 --
97 -- Declare cursor
98 --
99 -- EDIT_HERE In the following cursor statement add join(s) between
100 -- hxc_alias_values and PER_BUSINESS_GROUPS
101 -- so that the legislation_code for
102 -- the current business group context can be derived.
103 -- Remove this comment when the edit has been completed.
104 cursor csr_leg_code is
105 select pbg.legislation_code
106 from per_business_groups pbg
107 , hxc_alias_values hav
108 -- , EDIT_HERE table_name(s) 333
109 where hav.alias_value_id = p_alias_value_id;
110 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
111 --
112 -- Declare local variables
113 --
114 l_legislation_code varchar2(150);
115 l_proc varchar2(72);
116 --
117 Begin
118 --
119 g_debug:=hr_utility.debug_enabled;
120 if g_debug then
121 l_proc := g_package||'return_legislation_code';
122 hr_utility.set_location('Entering:'|| l_proc, 10);
123 end if;
124 --
125 -- Ensure that all the mandatory parameter are not null
126 --
127 hr_api.mandatory_arg_error
128 (p_api_name => l_proc
129 ,p_argument => 'alias_value_id'
130 ,p_argument_value => p_alias_value_id
131 );
132 --
133 if ( nvl(hxc_hav_bus.g_alias_value_id, hr_api.g_number)
134 = p_alias_value_id) then
135 --
136 -- The legislation code has already been found with a previous
137 -- call to this function. Just return the value in the global
138 -- variable.
139 --
140 l_legislation_code := hxc_hav_bus.g_legislation_code;
141 if g_debug then
142 hr_utility.set_location(l_proc, 20);
143 end if;
144 else
145 --
146 -- The ID is different to the last call to this function
147 -- or this is the first call to this function.
148 --
149 open csr_leg_code;
150 fetch csr_leg_code into l_legislation_code;
151 --
152 if csr_leg_code%notfound then
153 --
154 -- The primary key is invalid therefore we must error
155 --
156 close csr_leg_code;
157 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
158 fnd_message.raise_error;
159 end if;
160 if g_debug then
161 hr_utility.set_location(l_proc,30);
162 end if;
163 --
164 -- Set the global variables so the values are
165 -- available for the next call to this function.
166 --
167 close csr_leg_code;
168 hxc_hav_bus.g_alias_value_id := p_alias_value_id;
169 hxc_hav_bus.g_legislation_code := l_legislation_code;
170 end if;
171 if g_debug then
172 hr_utility.set_location(' Leaving:'|| l_proc, 40);
173 end if;
174 return l_legislation_code;
175 end return_legislation_code;
176 --
177 -- ----------------------------------------------------------------------------
178 -- |------------------------------< chk_df >----------------------------------|
179 -- ----------------------------------------------------------------------------
180 --
181 -- Description:
182 -- Validates all the Descriptive Flexfield values.
183 --
184 -- Prerequisites:
185 -- All other columns have been validated. Must be called as the
186 -- last step from insert_validate and update_validate.
187 --
188 -- In Arguments:
189 -- p_rec
190 --
191 -- Post Success:
192 -- If the Descriptive Flexfield structure column and data values are
193 -- all valid this procedure will end normally and processing will
194 -- continue.
195 --
196 -- Post Failure:
197 -- If the Descriptive Flexfield structure column value or any of
198 -- the data values are invalid then an application error is raised as
199 -- a PL/SQL exception.
200 --
201 -- Access Status:
202 -- Internal Row Handler Use Only.
203 --
204 -- ----------------------------------------------------------------------------
205 procedure chk_df
206 (p_rec in hxc_hav_shd.g_rec_type
207 ) is
208 --
209 l_proc varchar2(72);
210 --
211 begin
212 g_debug:=hr_utility.debug_enabled;
213 if g_debug then
214 l_proc := g_package || 'chk_df';
215 hr_utility.set_location('Entering:'||l_proc,10);
216 end if;
217 --
218 if ((p_rec.alias_value_id is not null) and (
219 nvl(hxc_hav_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
220 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
221 nvl(hxc_hav_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
222 nvl(p_rec.attribute1, hr_api.g_varchar2) or
223 nvl(hxc_hav_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
224 nvl(p_rec.attribute2, hr_api.g_varchar2) or
225 nvl(hxc_hav_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
226 nvl(p_rec.attribute3, hr_api.g_varchar2) or
227 nvl(hxc_hav_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
228 nvl(p_rec.attribute4, hr_api.g_varchar2) or
229 nvl(hxc_hav_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
230 nvl(p_rec.attribute5, hr_api.g_varchar2) or
231 nvl(hxc_hav_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
232 nvl(p_rec.attribute6, hr_api.g_varchar2) or
233 nvl(hxc_hav_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
234 nvl(p_rec.attribute7, hr_api.g_varchar2) or
235 nvl(hxc_hav_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
236 nvl(p_rec.attribute8, hr_api.g_varchar2) or
237 nvl(hxc_hav_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
238 nvl(p_rec.attribute9, hr_api.g_varchar2) or
239 nvl(hxc_hav_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
240 nvl(p_rec.attribute10, hr_api.g_varchar2) or
241 nvl(hxc_hav_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
242 nvl(p_rec.attribute11, hr_api.g_varchar2) or
243 nvl(hxc_hav_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
244 nvl(p_rec.attribute12, hr_api.g_varchar2) or
245 nvl(hxc_hav_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
246 nvl(p_rec.attribute13, hr_api.g_varchar2) or
247 nvl(hxc_hav_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
248 nvl(p_rec.attribute14, hr_api.g_varchar2) or
249 nvl(hxc_hav_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
250 nvl(p_rec.attribute15, hr_api.g_varchar2) or
251 nvl(hxc_hav_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
252 nvl(p_rec.attribute16, hr_api.g_varchar2) or
253 nvl(hxc_hav_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
254 nvl(p_rec.attribute17, hr_api.g_varchar2) or
255 nvl(hxc_hav_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
256 nvl(p_rec.attribute18, hr_api.g_varchar2) or
257 nvl(hxc_hav_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
258 nvl(p_rec.attribute19, hr_api.g_varchar2) or
259 nvl(hxc_hav_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
260 nvl(p_rec.attribute20, hr_api.g_varchar2) or
261 nvl(hxc_hav_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
262 nvl(p_rec.attribute21, hr_api.g_varchar2) or
263 nvl(hxc_hav_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
264 nvl(p_rec.attribute22, hr_api.g_varchar2) or
265 nvl(hxc_hav_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
266 nvl(p_rec.attribute23, hr_api.g_varchar2) or
267 nvl(hxc_hav_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
268 nvl(p_rec.attribute24, hr_api.g_varchar2) or
269 nvl(hxc_hav_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
270 nvl(p_rec.attribute25, hr_api.g_varchar2) or
271 nvl(hxc_hav_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
272 nvl(p_rec.attribute26, hr_api.g_varchar2) or
273 nvl(hxc_hav_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
274 nvl(p_rec.attribute27, hr_api.g_varchar2) or
275 nvl(hxc_hav_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
276 nvl(p_rec.attribute28, hr_api.g_varchar2) or
277 nvl(hxc_hav_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
278 nvl(p_rec.attribute29, hr_api.g_varchar2) or
279 nvl(hxc_hav_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
280 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
281 or (p_rec.alias_value_id is null) then
282 --
283 -- Only execute the validation if absolutely necessary:
284 -- a) During update, the structure column value or any
285 -- of the attribute values have actually changed.
286 -- b) During insert.
287 --
288 hr_dflex_utility.ins_or_upd_descflex_attribs
289 (p_appl_short_name => 'HXC'
290 ,p_descflex_name => 'OTC Aliases'
291 ,p_attribute_category => p_rec.attribute_category
292 ,p_attribute1_name => 'ATTRIBUTE1'
293 ,p_attribute1_value => p_rec.attribute1
294 ,p_attribute2_name => 'ATTRIBUTE2'
295 ,p_attribute2_value => p_rec.attribute2
296 ,p_attribute3_name => 'ATTRIBUTE3'
297 ,p_attribute3_value => p_rec.attribute3
298 ,p_attribute4_name => 'ATTRIBUTE4'
299 ,p_attribute4_value => p_rec.attribute4
300 ,p_attribute5_name => 'ATTRIBUTE5'
301 ,p_attribute5_value => p_rec.attribute5
302 ,p_attribute6_name => 'ATTRIBUTE6'
303 ,p_attribute6_value => p_rec.attribute6
304 ,p_attribute7_name => 'ATTRIBUTE7'
305 ,p_attribute7_value => p_rec.attribute7
306 ,p_attribute8_name => 'ATTRIBUTE8'
307 ,p_attribute8_value => p_rec.attribute8
308 ,p_attribute9_name => 'ATTRIBUTE9'
309 ,p_attribute9_value => p_rec.attribute9
310 ,p_attribute10_name => 'ATTRIBUTE10'
311 ,p_attribute10_value => p_rec.attribute10
312 ,p_attribute11_name => 'ATTRIBUTE11'
313 ,p_attribute11_value => p_rec.attribute11
314 ,p_attribute12_name => 'ATTRIBUTE12'
315 ,p_attribute12_value => p_rec.attribute12
316 ,p_attribute13_name => 'ATTRIBUTE13'
317 ,p_attribute13_value => p_rec.attribute13
318 ,p_attribute14_name => 'ATTRIBUTE14'
319 ,p_attribute14_value => p_rec.attribute14
320 ,p_attribute15_name => 'ATTRIBUTE15'
321 ,p_attribute15_value => p_rec.attribute15
322 ,p_attribute16_name => 'ATTRIBUTE16'
323 ,p_attribute16_value => p_rec.attribute16
324 ,p_attribute17_name => 'ATTRIBUTE17'
325 ,p_attribute17_value => p_rec.attribute17
326 ,p_attribute18_name => 'ATTRIBUTE18'
327 ,p_attribute18_value => p_rec.attribute18
328 ,p_attribute19_name => 'ATTRIBUTE19'
329 ,p_attribute19_value => p_rec.attribute19
330 ,p_attribute20_name => 'ATTRIBUTE20'
331 ,p_attribute20_value => p_rec.attribute20
332 ,p_attribute21_name => 'ATTRIBUTE21'
333 ,p_attribute21_value => p_rec.attribute21
334 ,p_attribute22_name => 'ATTRIBUTE22'
335 ,p_attribute22_value => p_rec.attribute22
336 ,p_attribute23_name => 'ATTRIBUTE23'
337 ,p_attribute23_value => p_rec.attribute23
338 ,p_attribute24_name => 'ATTRIBUTE24'
339 ,p_attribute24_value => p_rec.attribute24
340 ,p_attribute25_name => 'ATTRIBUTE25'
341 ,p_attribute25_value => p_rec.attribute25
342 ,p_attribute26_name => 'ATTRIBUTE26'
343 ,p_attribute26_value => p_rec.attribute26
344 ,p_attribute27_name => 'ATTRIBUTE27'
345 ,p_attribute27_value => p_rec.attribute27
346 ,p_attribute28_name => 'ATTRIBUTE28'
347 ,p_attribute28_value => p_rec.attribute28
348 ,p_attribute29_name => 'ATTRIBUTE29'
349 ,p_attribute29_value => p_rec.attribute29
350 ,p_attribute30_name => 'ATTRIBUTE30'
351 ,p_attribute30_value => p_rec.attribute30
352 );
353 end if;
354 --
355 if g_debug then
356 hr_utility.set_location(' Leaving:'||l_proc,20);
357 end if;
358 end chk_df;
359 --
360 -- ----------------------------------------------------------------------------
361 -- |-----------------------< chk_non_updateable_args >------------------------|
362 -- ----------------------------------------------------------------------------
363 -- {Start Of Comments}
364 --
365 -- Description:
366 -- This procedure is used to ensure that non updateable attributes have
370 -- g_old_rec has been populated with details of the values currently in
367 -- not been updated. If an attribute has been updated an error is generated.
368 --
369 -- Pre Conditions:
371 -- the database.
372 --
373 -- In Arguments:
374 -- p_rec has been populated with the updated values the user would like the
375 -- record set to.
376 --
377 -- Post Success:
378 -- Processing continues if all the non updateable attributes have not
379 -- changed.
380 --
381 -- Post Failure:
382 -- An application error is raised if any of the non updatable attributes
383 -- have been altered.
384 --
385 -- {End Of Comments}
386 -- ----------------------------------------------------------------------------
387 Procedure chk_non_updateable_args
388 (p_rec in hxc_hav_shd.g_rec_type
389 ) IS
390 --
391 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
392 l_error EXCEPTION;
393 l_argument varchar2(30);
394 --
395 Begin
396 --
397 -- Only proceed with the validation if a row exists for the current
398 -- record in the HR Schema.
399 --
400 IF NOT hxc_hav_shd.api_updating
401 (p_alias_value_id => p_rec.alias_value_id
402 ,p_object_version_number => p_rec.object_version_number
403 ) THEN
404 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
405 fnd_message.set_token('PROCEDURE ', l_proc);
406 fnd_message.set_token('STEP ', '5');
407 fnd_message.raise_error;
408 END IF;
409 --
410 -- EDIT_HERE: Add checks to ensure non-updateable args have
411 -- not been updated.
412 --
413 EXCEPTION
414 WHEN l_error THEN
415 hr_api.argument_changed_error
416 (p_api_name => l_proc
417 ,p_argument => l_argument);
418 WHEN OTHERS THEN
419 RAISE;
420 End chk_non_updateable_args;
421 --
422 -- ----------------------------------------------------------------------------
423 -- |-----------------------< chk_name >---------------------------------------|
424 -- ----------------------------------------------------------------------------
425 -- {Start Of Comments}
426 --
427 -- Description:
428 -- This procedure insures a valid alias value name
429 --
430 -- Pre Conditions:
431 -- None
432 --
433 -- In Arguments:
434 -- p_name
435 -- p_ovn - object_version_number
436 -- p_date_from
437 -- p_date_to
438 -- p_alias_definition_id
439 --
440 -- Post Success:
441 -- Processing continues if the name business rules have not been violated
442 --
443 -- Post Failure:
444 -- An application error is raised if the name is not valid
445 --
446 -- {End Of Comments}
447 -- ----------------------------------------------------------------------------
448 Procedure chk_name
449 (
450 p_name in hxc_alias_values.alias_value_name%TYPE
451 ,p_ovn in hxc_alias_values.object_version_number%TYPE
452 ,p_date_from in hxc_alias_values.date_from%TYPE
453 ,p_date_to in hxc_alias_values.date_to%TYPE
454 ,p_alias_definition_id in hxc_alias_values.alias_definition_id%TYPE
455 ,p_alias_value_id in hxc_alias_values.alias_definition_id%TYPE DEFAULT NULL
456 ) IS
457 --
458 l_proc varchar2(72);
459 --
460 -- cursor to check alias value name does not overlap
461 --
462 CURSOR csr_chk_name IS
463 SELECT 'error'
464 FROM hxc_alias_values_tl havt,
465 hxc_alias_values hav
466 WHERE havt.alias_value_id = hav.alias_value_id
467 AND havt.alias_value_name = p_name
468 AND hav.object_version_number <> NVL(p_ovn, -1)
469 AND hav.alias_definition_id = p_alias_definition_id
470 and havt.language = USERENV('LANG')
471 AND hav.alias_value_id <> nvl(p_alias_value_id,9.99E125)
472 AND ((p_date_from BETWEEN hav.date_from
473 AND NVL(hav.date_to, HR_GENERAL.END_OF_TIME))
474 OR
475 (NVL(p_date_to, HR_GENERAL.END_OF_TIME) BETWEEN
476 hav.date_from AND NVL(hav.date_to, HR_GENERAL.END_OF_TIME))
477 OR
478 (hav.date_from BETWEEN p_date_from
479 AND NVL(p_date_to, HR_GENERAL.END_OF_TIME))
480 OR
481 (NVL(hav.date_to, HR_GENERAL.END_OF_TIME) BETWEEN
482 p_date_from AND NVL(p_date_to, HR_GENERAL.END_OF_TIME)));
483 --
484 l_dup_name varchar2(5) := NULL;
485 --
486 BEGIN
487 g_debug:=hr_utility.debug_enabled;
488 if g_debug then
489 l_proc := g_package||'chk_name';
490 hr_utility.set_location('Entering:'||l_proc, 5);
491 end if;
492 --
493 -- check that the name has been entered
494 --
495 IF p_name IS NULL
496 THEN
497 --
498 hr_utility.set_message(809, 'HXC_ALIAS_NAME_VALUE_MAND');
499 hr_utility.raise_error;
500 --
501 END IF;
502 --
503 IF p_date_from IS NULL
504 THEN
505 --
506 hr_utility.set_message(809, 'HXC_0056_DAR_START_DATE_MAND');
507 hr_utility.raise_error;
508 --
509 END IF;
510 --
511 IF p_date_to is not null AND p_date_from > p_date_to THEN
515 --
512 hr_utility.set_message(809,'HXC_0059_DAR_TO_LESS_THAN_FROM');
513 hr_utility.raise_error;
514 END IF;
516 if g_debug then
517 hr_utility.set_location('Processing:'||l_proc, 10);
518 end if;
519 --
520 -- check that the name is unique
521 --
522 /*
523 fnd_log.string(1
524 ,'hxc_hav_shd.chk_duplicate_values'
525 ,'p_ovn '||p_ovn||
526 ' p_date_from '||p_date_from ||
527 ' p_date_to '||p_date_to ||
528 ' p_alias_definition_id '||p_alias_definition_id||
529 ' p_alias_value_id '||p_alias_value_id||
530 ' p_name '||p_name);
531 */
532 OPEN csr_chk_name;
533 FETCH csr_chk_name INTO l_dup_name;
534 CLOSE csr_chk_name;
535 --
536 IF l_dup_name IS NOT NULL
537 THEN
538 --
539 hr_utility.set_message(809, 'HXC_ALIAS_NAME_VALUE_UNIQUE');
540 hr_utility.raise_error;
541 --
542 END IF;
543 --
544 if g_debug then
545 hr_utility.set_location('Leaving:'||l_proc, 20);
546 end if;
547 --
548 END chk_name;
549 --
550 -- ----------------------------------------------------------------------------
551 -- |-----------------------< chk_duplicate_values >---------------------------------------|
552 -- ----------------------------------------------------------------------------
553 -- {Start Of Comments}
554 --
555 -- Description:
556 -- This procedure insures a valid alias values.
557 --
558 -- Pre Conditions:
559 -- None
560 --
561 -- In Arguments:
562 -- p_name
563 -- p_ovn - object_version_number
564 -- p_date_from
565 -- p_date_to
566 -- p_alias_definition_id
567 --
568 -- Post Success:
569 -- Processing continues if the name business rules have not been violated
570 --
571 -- Post Failure:
572 -- An application error is raised if the name is not valid
573 --
574 -- {End Of Comments}
575 -- ----------------------------------------------------------------------------
576 Procedure chk_duplicate_values
577 (
578 p_attribute_category in hxc_alias_values.attribute_category%TYPE
579 ,p_attribute1 in hxc_alias_values.attribute1%TYPE DEFAULT NULL
580 ,p_attribute2 in hxc_alias_values.attribute2%TYPE DEFAULT NULL
581 ,p_attribute3 in hxc_alias_values.attribute3%TYPE DEFAULT NULL
582 ,p_attribute4 in hxc_alias_values.attribute4%TYPE DEFAULT NULL
583 ,p_attribute5 in hxc_alias_values.attribute5%TYPE DEFAULT NULL
584 ,p_attribute6 in hxc_alias_values.attribute6%TYPE DEFAULT NULL
585 ,p_attribute7 in hxc_alias_values.attribute7%TYPE DEFAULT NULL
586 ,p_attribute8 in hxc_alias_values.attribute8%TYPE DEFAULT NULL
587 ,p_attribute9 in hxc_alias_values.attribute9%TYPE DEFAULT NULL
588 ,p_attribute10 in hxc_alias_values.attribute10%TYPE DEFAULT NULL
589 ,p_attribute11 in hxc_alias_values.attribute11%TYPE DEFAULT NULL
590 ,p_attribute12 in hxc_alias_values.attribute12%TYPE DEFAULT NULL
591 ,p_attribute13 in hxc_alias_values.attribute13%TYPE DEFAULT NULL
592 ,p_attribute14 in hxc_alias_values.attribute14%TYPE DEFAULT NULL
593 ,p_attribute15 in hxc_alias_values.attribute15%TYPE DEFAULT NULL
594 ,p_attribute16 in hxc_alias_values.attribute16%TYPE DEFAULT NULL
595 ,p_attribute17 in hxc_alias_values.attribute17%TYPE DEFAULT NULL
596 ,p_attribute18 in hxc_alias_values.attribute18%TYPE DEFAULT NULL
597 ,p_attribute19 in hxc_alias_values.attribute19%TYPE DEFAULT NULL
598 ,p_attribute20 in hxc_alias_values.attribute20%TYPE DEFAULT NULL
599 ,p_attribute21 in hxc_alias_values.attribute21%TYPE DEFAULT NULL
600 ,p_attribute22 in hxc_alias_values.attribute22%TYPE DEFAULT NULL
601 ,p_attribute23 in hxc_alias_values.attribute23%TYPE DEFAULT NULL
602 ,p_attribute24 in hxc_alias_values.attribute24%TYPE DEFAULT NULL
603 ,p_attribute25 in hxc_alias_values.attribute25%TYPE DEFAULT NULL
604 ,p_attribute26 in hxc_alias_values.attribute26%TYPE DEFAULT NULL
605 ,p_attribute27 in hxc_alias_values.attribute27%TYPE DEFAULT NULL
606 ,p_attribute28 in hxc_alias_values.attribute28%TYPE DEFAULT NULL
607 ,p_attribute29 in hxc_alias_values.attribute29%TYPE DEFAULT NULL
608 ,p_attribute30 in hxc_alias_values.attribute30%TYPE DEFAULT NULL
609 ,p_ovn in hxc_alias_values.object_version_number%TYPE
610 ,p_date_from in hxc_alias_values.date_from%TYPE
611 ,p_date_to in hxc_alias_values.date_to%TYPE
612 ,p_alias_definition_id in hxc_alias_values.alias_definition_id%TYPE
613 ,p_alias_value_id in hxc_alias_values.alias_value_id%TYPE DEFAULT NULL
614 ) IS
615 --
616 l_proc varchar2(72);
617 --
618 -- cursor to check alias value name does not overlap
619 --
620 CURSOR csr_chk_duplicate IS
621 SELECT 'error'
622 FROM hxc_alias_values hav
623 WHERE hav.alias_definition_id = p_alias_definition_id
624 AND hav.object_version_number <> NVL(p_ovn, -1)
625 AND ((p_date_from BETWEEN hav.date_from
626 AND NVL(hav.date_to, HR_GENERAL.END_OF_TIME))
627 OR
628 (NVL(p_date_to, HR_GENERAL.END_OF_TIME) BETWEEN
629 hav.date_from AND NVL(hav.date_to, HR_GENERAL.END_OF_TIME))
630 OR
631 (hav.date_from BETWEEN p_date_from
635 p_date_from AND NVL(p_date_to, HR_GENERAL.END_OF_TIME)))
632 AND NVL(p_date_to, HR_GENERAL.END_OF_TIME))
633 OR
634 (NVL(hav.date_to, HR_GENERAL.END_OF_TIME) BETWEEN
636 AND hav.alias_value_id <> nvl(p_alias_value_id,9.99E125)
637 AND hav.attribute_category = p_attribute_category
638 AND nvl(hav.attribute1,chr(0)) = nvl(p_attribute1,chr(0))
639 AND nvl(hav.attribute2,chr(0)) = nvl(p_attribute2,chr(0))
640 AND nvl(hav.attribute3,chr(0)) = nvl(p_attribute3,chr(0))
641 AND nvl(hav.attribute4,chr(0)) = nvl(p_attribute4,chr(0))
642 AND nvl(hav.attribute5,chr(0)) = nvl(p_attribute5,chr(0))
643 AND nvl(hav.attribute6,chr(0)) = nvl(p_attribute6,chr(0))
644 AND nvl(hav.attribute7,chr(0)) = nvl(p_attribute7,chr(0))
645 AND nvl(hav.attribute8,chr(0)) = nvl(p_attribute8,chr(0))
646 AND nvl(hav.attribute9,chr(0)) = nvl(p_attribute9,chr(0))
647 AND nvl(hav.attribute10,chr(0)) = nvl(p_attribute10,chr(0))
648 AND nvl(hav.attribute11,chr(0)) = nvl(p_attribute11,chr(0))
649 AND nvl(hav.attribute12,chr(0)) = nvl(p_attribute12,chr(0))
650 AND nvl(hav.attribute13,chr(0)) = nvl(p_attribute13,chr(0))
651 AND nvl(hav.attribute14,chr(0)) = nvl(p_attribute14,chr(0))
652 AND nvl(hav.attribute15,chr(0)) = nvl(p_attribute15,chr(0))
653 AND nvl(hav.attribute16,chr(0)) = nvl(p_attribute16,chr(0))
654 AND nvl(hav.attribute17,chr(0)) = nvl(p_attribute17,chr(0))
655 AND nvl(hav.attribute18,chr(0)) = nvl(p_attribute18,chr(0))
656 AND nvl(hav.attribute19,chr(0)) = nvl(p_attribute19,chr(0))
657 AND nvl(hav.attribute20,chr(0)) = nvl(p_attribute20,chr(0))
658 AND nvl(hav.attribute21,chr(0)) = nvl(p_attribute21,chr(0))
659 AND nvl(hav.attribute22,chr(0)) = nvl(p_attribute22,chr(0))
660 AND nvl(hav.attribute23,chr(0)) = nvl(p_attribute23,chr(0))
661 AND nvl(hav.attribute24,chr(0)) = nvl(p_attribute24,chr(0))
662 AND nvl(hav.attribute25,chr(0)) = nvl(p_attribute25,chr(0))
663 AND nvl(hav.attribute26,chr(0)) = nvl(p_attribute26,chr(0))
664 AND nvl(hav.attribute27,chr(0)) = nvl(p_attribute27,chr(0))
665 AND nvl(hav.attribute28,chr(0)) = nvl(p_attribute28,chr(0))
666 AND nvl(hav.attribute29,chr(0)) = nvl(p_attribute29,chr(0))
667 AND nvl(hav.attribute30,chr(0)) = nvl(p_attribute30,chr(0));
668 --
669 l_dup_value varchar2(5) := NULL;
670 --
671 BEGIN
672 g_debug:=hr_utility.debug_enabled;
673 if g_debug then
674 l_proc := g_package||'chk_duplicate_values';
675 hr_utility.set_location('Entering:'||l_proc, 5);
676 end if;
677 --
678 IF p_date_from IS NULL
679 THEN
680 --
681 hr_utility.set_message(809, 'HXC_0056_DAR_START_DATE_MAND');
682 hr_utility.raise_error;
683 --
684 END IF;
685 --
686 IF p_date_to is not null AND p_date_from > p_date_to THEN
687 hr_utility.set_message(809,'HXC_0059_DAR_TO_LESS_THAN_FROM');
688 hr_utility.raise_error;
689 END IF;
690 --
691 if g_debug then
692 hr_utility.set_location('Processing:'||l_proc, 10);
693 end if;
694 --
695 -- check that the value is unique
696 --
697 /*
698 fnd_log.string(1
699 ,'hxc_hav_shd.chk_duplicate_values'
700 ,'ATT_CAT:'||p_ATTRIBUTE_CATEGORY||
701 ' ATT1:'||p_ATTRIBUTE1||
702 ' ATT2:'||p_ATTRIBUTE2||
703 ' ATT3:'||p_ATTRIBUTE3||
704 ' ATT4:'||p_ATTRIBUTE4);
705 fnd_log.string(1
706 ,'hxc_hav_shd.chk_duplicate_values'
707 ,' ATT5:'||p_ATTRIBUTE5||
708 ' ATT6:'||p_ATTRIBUTE6||
709 ' ATT7:'||p_ATTRIBUTE7||
710 ' ATT8:'||p_ATTRIBUTE8||
711 ' ATT9:'||p_ATTRIBUTE9||
712 ' ATT10:'||p_ATTRIBUTE10||
713 ' ATT11:'||p_ATTRIBUTE11||
714 ' ATT12:'||p_ATTRIBUTE12||
715 ' ATT13:'||p_ATTRIBUTE13||
716 ' ATT14:'||p_ATTRIBUTE14||
717 ' ATT15:'||p_ATTRIBUTE15||
718 ' ATT16:'||p_ATTRIBUTE16||
719 ' ATT17:'||p_ATTRIBUTE17||
720 ' ATT18:'||p_ATTRIBUTE18||
721 ' ATT19:'||p_ATTRIBUTE19||
722 ' ATT20:'||p_ATTRIBUTE20||
723 ' ATT21:'||p_ATTRIBUTE21||
724 ' ATT22:'||p_ATTRIBUTE22||
725 ' ATT23:'||p_ATTRIBUTE23||
726 ' ATT24:'||p_ATTRIBUTE24||
727 ' ATT25:'||p_ATTRIBUTE25||
728 ' ATT26:'||p_ATTRIBUTE26||
729 ' ATT27:'||p_ATTRIBUTE27||
730 ' ATT28:'||p_ATTRIBUTE28||
731 ' ATT29:'||p_ATTRIBUTE29||
732 ' ATT30:'||p_ATTRIBUTE30);
733 fnd_log.string(1
734 ,'hxc_hav_shd.chk_duplicate_values'
735 ,'p_ovn '||p_ovn||
736 ' p_date_from '||p_date_from ||
737 ' p_date_to '||p_date_to ||
738 ' p_alias_definition_id '||p_alias_definition_id||
739 ' p_alias_value_id '||p_alias_value_id);
740 */
741 OPEN csr_chk_duplicate;
742 FETCH csr_chk_duplicate INTO l_dup_value;
743 --
744 IF csr_chk_duplicate%FOUND
745 THEN
746 --
747 hr_utility.set_message(809, 'HXC_ALIAS_VALUE_UNIQUE');
748 hr_utility.raise_error;
749 --
750 END IF;
751 --
752 CLOSE csr_chk_duplicate;
753 --
754 if g_debug then
755 hr_utility.set_location('Leaving:'||l_proc, 20);
756 end if;
757 --
758 END chk_duplicate_values;
759 --
760 --
761 -- ----------------------------------------------------------------------------
765 (p_rec in hxc_hav_shd.g_rec_type
762 -- |---------------------------< insert_validate >----------------------------|
763 -- ----------------------------------------------------------------------------
764 Procedure insert_validate
766 ) is
767 --
768 l_proc varchar2(72);
769 --
770 Begin
771 g_debug:=hr_utility.debug_enabled;
772 if g_debug then
773 l_proc := g_package||'insert_validate';
774 hr_utility.set_location('Entering:'||l_proc, 5);
775 end if;
776 --
777 -- Call all supporting business operations
778 --
779 chk_name (
780 p_name => p_rec.alias_value_name
781 ,p_ovn => p_rec.object_version_number
782 ,p_date_from => p_rec.date_from
783 ,p_date_to => p_rec.date_to
784 ,p_alias_definition_id => p_rec.alias_definition_id
785 ,p_alias_value_id => p_rec.alias_value_id
786 );
787
788 chk_duplicate_values
789 (
790 p_attribute_category => p_rec.attribute_category
791 ,p_attribute1 => p_rec.attribute1
792 ,p_attribute2 => p_rec.attribute2
793 ,p_attribute3 => p_rec.attribute3
794 ,p_attribute4 => p_rec.attribute4
795 ,p_attribute5 => p_rec.attribute5
796 ,p_attribute6 => p_rec.attribute6
797 ,p_attribute7 => p_rec.attribute7
798 ,p_attribute8 => p_rec.attribute8
799 ,p_attribute9 => p_rec.attribute9
800 ,p_attribute10 => p_rec.attribute10
801 ,p_attribute11 => p_rec.attribute11
802 ,p_attribute12 => p_rec.attribute12
803 ,p_attribute13 => p_rec.attribute13
804 ,p_attribute14 => p_rec.attribute14
805 ,p_attribute15 => p_rec.attribute15
806 ,p_attribute16 => p_rec.attribute16
807 ,p_attribute17 => p_rec.attribute17
808 ,p_attribute18 => p_rec.attribute18
809 ,p_attribute19 => p_rec.attribute19
810 ,p_attribute20 => p_rec.attribute20
811 ,p_attribute21 => p_rec.attribute21
812 ,p_attribute22 => p_rec.attribute22
813 ,p_attribute23 => p_rec.attribute23
814 ,p_attribute24 => p_rec.attribute24
815 ,p_attribute25 => p_rec.attribute25
816 ,p_attribute26 => p_rec.attribute26
817 ,p_attribute27 => p_rec.attribute27
818 ,p_attribute28 => p_rec.attribute28
819 ,p_attribute29 => p_rec.attribute29
820 ,p_attribute30 => p_rec.attribute30
821 ,p_ovn => p_rec.object_version_number
822 ,p_date_from => p_rec.date_from
823 ,p_date_to => p_rec.date_to
824 ,p_alias_definition_id => p_rec.alias_definition_id
825 ,p_alias_value_id => p_rec.alias_value_id
826 );
827 --
828 -- EDIT_HERE: As this table does not have a mandatory business_group_id
829 -- column, ensure client_info is populated by calling a suitable
830 -- ???_???_bus.set_security_group_id procedure, or add one of the following
831 -- comments:
832 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
833 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
834 --
835 --
836 hxc_hav_bus.chk_df(p_rec);
837 --
838 if g_debug then
839 hr_utility.set_location(' Leaving:'||l_proc, 10);
840 end if;
841 End insert_validate;
842 --
843 -- ----------------------------------------------------------------------------
844 -- |---------------------------< update_validate >----------------------------|
845 -- ----------------------------------------------------------------------------
846 Procedure update_validate
847 (p_rec in hxc_hav_shd.g_rec_type
848 ) is
849 --
850 l_proc varchar2(72);
851 --
852 Begin
853 g_debug:=hr_utility.debug_enabled;
854 if g_debug then
855 l_proc := g_package||'update_validate';
856 hr_utility.set_location('Entering:'||l_proc, 5);
857 end if;
858 --
859 -- Call all supporting business operations
860 --
861 chk_non_updateable_args
862 (p_rec => p_rec
863 );
864
865 chk_name (
866 p_name => p_rec.alias_value_name
867 ,p_ovn => p_rec.object_version_number
868 ,p_date_from => p_rec.date_from
869 ,p_date_to => p_rec.date_to
870 ,p_alias_definition_id => p_rec.alias_definition_id
871 ,p_alias_value_id => p_rec.alias_value_id
872 );
873
874 chk_duplicate_values
875 (
876 p_attribute_category => p_rec.attribute_category
877 ,p_attribute1 => p_rec.attribute1
878 ,p_attribute2 => p_rec.attribute2
879 ,p_attribute3 => p_rec.attribute3
880 ,p_attribute4 => p_rec.attribute4
881 ,p_attribute5 => p_rec.attribute5
882 ,p_attribute6 => p_rec.attribute6
883 ,p_attribute7 => p_rec.attribute7
884 ,p_attribute8 => p_rec.attribute8
885 ,p_attribute9 => p_rec.attribute9
886 ,p_attribute10 => p_rec.attribute10
887 ,p_attribute11 => p_rec.attribute11
888 ,p_attribute12 => p_rec.attribute12
889 ,p_attribute13 => p_rec.attribute13
890 ,p_attribute14 => p_rec.attribute14
891 ,p_attribute15 => p_rec.attribute15
892 ,p_attribute16 => p_rec.attribute16
893 ,p_attribute17 => p_rec.attribute17
894 ,p_attribute18 => p_rec.attribute18
895 ,p_attribute19 => p_rec.attribute19
896 ,p_attribute20 => p_rec.attribute20
897 ,p_attribute21 => p_rec.attribute21
898 ,p_attribute22 => p_rec.attribute22
899 ,p_attribute23 => p_rec.attribute23
900 ,p_attribute24 => p_rec.attribute24
901 ,p_attribute25 => p_rec.attribute25
902 ,p_attribute26 => p_rec.attribute26
903 ,p_attribute27 => p_rec.attribute27
904 ,p_attribute28 => p_rec.attribute28
905 ,p_attribute29 => p_rec.attribute29
906 ,p_attribute30 => p_rec.attribute30
907 ,p_ovn => p_rec.object_version_number
908 ,p_date_from => p_rec.date_from
909 ,p_date_to => p_rec.date_to
910 ,p_alias_definition_id => p_rec.alias_definition_id
911 ,p_alias_value_id => p_rec.alias_value_id
912 );
913 --
914 -- EDIT_HERE: As this table does not have a mandatory business_group_id
915 -- column, ensure client_info is populated by calling a suitable
916 -- ???_???_bus.set_security_group_id procedure, or add one of the following
917 -- comments:
918 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
919 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
920 --
921 /* chk_non_updateable_args
922 (p_rec => p_rec
923 ); */
924 --
925 --
926 hxc_hav_bus.chk_df(p_rec);
927 --
928 if g_debug then
929 hr_utility.set_location(' Leaving:'||l_proc, 10);
930 end if;
931 End update_validate;
932 --
933 -- ----------------------------------------------------------------------------
934 -- |---------------------------< delete_validate >----------------------------|
935 -- ----------------------------------------------------------------------------
936 Procedure delete_validate
937 (p_rec in hxc_hav_shd.g_rec_type
938 ) is
939 --
940 l_proc varchar2(72);
941 --
942 Begin
943 g_debug:=hr_utility.debug_enabled;
944 if g_debug then
945 l_proc := g_package||'delete_validate';
946 hr_utility.set_location('Entering:'||l_proc, 5);
947 end if;
948 --
949 -- Call all supporting business operations
950 --
951 if g_debug then
952 hr_utility.set_location(' Leaving:'||l_proc, 10);
953 end if;
954 End delete_validate;
955 --
956 end hxc_hav_bus;