1 Package Body per_rsl_bus as
2 /* $Header: perslrhi.pkb 120.1 2005/06/15 05:45:38 bshukla noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_rsl_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_rating_scale_id number default null;
15 g_language varchar2(4) default null;
16 --
17 --
18 -- The following global vaiables are only to be used by the
19 -- validate_translation function.
20 --
21 g_business_group_id number default null;
22 --
23 -- ---------------------------------------------------------------------------
24 -- |----------------------< set_security_group_id >--------------------------|
25 -- ---------------------------------------------------------------------------
26 --
27 Procedure set_security_group_id
28 (p_rating_scale_id in number
29 ,p_associated_column1 in varchar2 default null
30 ) is
31 -- Name : M. Burton
32 -- Date 02-DEC-2002
33 -- Description Amended to add MLS functionality
34 -- In the following cursor statement add join(s) between
35 -- per_rating_scales_tl and PER_BUSINESS_GROUPS
36 -- so that the security_group_id for
37 -- the current business group context can be derived.
38 cursor csr_sec_grp is
39 select pbg.security_group_id
40 from per_business_groups pbg
41 , per_rating_scales_tl rsl_tl
42 , per_rating_scales rsl
43 where rsl.rating_scale_id = p_rating_scale_id
44 and rsl.rating_scale_id = rsl_tl.rating_scale_id
45 and pbg.business_group_id = rsl.business_group_id;
46 --
47 -- Declare local variables
48 --
49 l_security_group_id number;
50 l_proc varchar2(72) := g_package||'set_security_group_id';
51 --
52 begin
53 --
54 hr_utility.set_location('Entering:'|| l_proc, 10);
55 --
56 -- Ensure that all the mandatory parameter are not null
57 --
58 hr_api.mandatory_arg_error
59 (p_api_name => l_proc
60 ,p_argument => 'rating_scale_id'
61 ,p_argument_value => p_rating_scale_id
62 );
63 --
64 --
65 open csr_sec_grp;
66 fetch csr_sec_grp into l_security_group_id;
67 --
68 if csr_sec_grp%notfound then
69 --
70 close csr_sec_grp;
71 --
72 -- The primary key is invalid therefore we must error
73 --
74 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
75 hr_multi_message.add
76 (p_associated_column1
77 => nvl(p_associated_column1,'RATING_SCALE_ID')
78 );
79 --
80 else
81 close csr_sec_grp;
82 --
83 -- Set the security_group_id in CLIENT_INFO
84 --
85 hr_api.set_security_group_id
86 (p_security_group_id => l_security_group_id
87 );
88 end if;
89 --
90 hr_utility.set_location(' Leaving:'|| l_proc, 20);
91 --
92 end set_security_group_id;
93 -- ---------------------------------------------------------------------------
94 -- |---------------------< return_legislation_code >-------------------------|
95 -- ---------------------------------------------------------------------------
96 --
97 Function return_legislation_code
98 (p_rating_scale_id in number
99 ,p_language in varchar2
100 )
101 Return Varchar2 Is
102 --
103 -- Declare cursor
104 -- Name : M. Burton
105 -- Date 29-NOV-2002
106 -- Declare cursor
107 --
108 -- In the following cursor statement add join(s) between
109 -- per_rating_scales_tl and PER_BUSINESS_GROUPS
110 -- so that the legislation_code for
111 -- the current business group context can be derived.
112
113 cursor csr_leg_code is
114 select pbg.legislation_code
115 from per_business_groups pbg
116 , per_rating_scales_tl rsl_tl
117 , per_rating_scales rsl
118 where
119 rsl.rating_scale_id = p_rating_scale_id
120 and rsl_tl.language = p_language
121 and pbg.business_group_id = rsl.business_group_id
122 and rsl.rating_scale_id = rsl_tl.rating_scale_id;
123 -- Declare local variables
124 --
125 l_legislation_code varchar2(150);
126 l_proc varchar2(72) := g_package||'return_legislation_code';
127 --
128 Begin
129 --
130 hr_utility.set_location('Entering:'|| l_proc, 10);
131 --
132 -- Ensure that all the mandatory parameter are not null
133 --
134 hr_api.mandatory_arg_error
135 (p_api_name => l_proc
136 ,p_argument => 'rating_scale_id'
137 ,p_argument_value => p_rating_scale_id
138 );
139 --
140 --
141 if (( nvl(per_rsl_bus.g_rating_scale_id, hr_api.g_number)
142 = p_rating_scale_id)
143 and ( nvl(per_rsl_bus.g_language, hr_api.g_varchar2)
144 = p_language)) then
145 --
146 -- The legislation code has already been found with a previous
147 -- call to this function. Just return the value in the global
148 -- variable.
149 --
150 l_legislation_code := per_rsl_bus.g_legislation_code;
151 hr_utility.set_location(l_proc, 20);
152 else
153 --
154 -- The ID is different to the last call to this function
155 -- or this is the first call to this function.
156 --
157 open csr_leg_code;
158 fetch csr_leg_code into l_legislation_code;
159 --
160 if csr_leg_code%notfound then
161 --
162 -- The primary key is invalid therefore we must error
163 --
164 close csr_leg_code;
165 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
166 fnd_message.raise_error;
167 end if;
168 hr_utility.set_location(l_proc,30);
169 --
170 -- Set the global variables so the values are
171 -- available for the next call to this function.
172 --
173 close csr_leg_code;
174 per_rsl_bus.g_rating_scale_id := p_rating_scale_id;
175 per_rsl_bus.g_language := p_language;
176 per_rsl_bus.g_legislation_code := l_legislation_code;
177 end if;
178 hr_utility.set_location(' Leaving:'|| l_proc, 40);
179 return l_legislation_code;
180 end return_legislation_code;
181 --
182 -- ----------------------------------------------------------------------------
183 -- |-----------------------< chk_non_updateable_args >------------------------|
184 -- ----------------------------------------------------------------------------
185 -- {Start Of Comments}
186 --
187 -- Description:
188 -- This procedure is used to ensure that non updateable attributes have
189 -- not been updated. If an attribute has been updated an error is generated.
190 --
191 -- Pre Conditions:
192 -- g_old_rec has been populated with details of the values currently in
193 -- the database.
194 --
195 -- In Arguments:
196 -- p_rec has been populated with the updated values the user would like the
197 -- record set to.
198 --
199 -- Post Success:
200 -- Processing continues if all the non updateable attributes have not
201 -- changed.
202 --
203 -- Post Failure:
204 -- An application error is raised if any of the non updatable attributes
205 -- have been altered.
206 --
207 -- {End Of Comments}
208 -- ----------------------------------------------------------------------------
209 Procedure chk_non_updateable_args
210 (p_rec in per_rsl_shd.g_rec_type
211 ) IS
212 --
213 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
214 --
215 Begin
216 --
217 -- Only proceed with the validation if a row exists for the current
218 -- record in the HR Schema.
219 --
220 IF NOT per_rsl_shd.api_updating
221 (p_rating_scale_id => p_rec.rating_scale_id,
222 p_language => p_rec.language )
223 THEN
224 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
225 fnd_message.set_token('PROCEDURE ', l_proc);
226 fnd_message.set_token('STEP ', '5');
227 fnd_message.raise_error;
228 END IF;
229 --
230
231 End chk_non_updateable_args;
232 --
233 -- ----------------------------------------------------------------------------
234 -- |---------------------------<chk_name>-------------------------------------|
235 -- ----------------------------------------------------------------------------
236 -- {Start Of Comments}
237 --
238 -- Description
239 -- - Validates that NAME exists
240 -- - Validates that NAME is UNIQUE for the BUSINESS_GROUP
241 --
242 -- Pre-conditions
243 -- - A valid BUSINESS_GROUP_ID
244 --
245 --
246 -- In Arguments:
247 -- p_rating_scale_id
248 -- p_business_group_id
249 -- p_name
250 -- p_object_version_number
251 --
252 -- Post Success:
253 -- Process continues if:
254 -- All the in parameters are valid.
255 --
256 -- Post Failure:
257 -- An application error is raised and processing is terminated if any of
258 -- the folowing cases are found:
259 -- - The NAME does not exist.
260 -- - The NAME is not UNIQUE for the BUSINESS_GROUP
261 --
262 -- Access Status
263 -- Internal Table Handler Use Only.
264 --
265 --
266
267 procedure chk_name
268 (p_rating_scale_id in per_rating_scales_tl.rating_scale_id%TYPE
269 ,p_language in per_rating_scales_tl.language%TYPE
270 ,p_business_group_id in per_rating_scales.business_group_id%TYPE
271 ,p_name in per_rating_scales_tl.name%TYPE
272 )
273 is
274 --
275 l_exists per_rating_scales.business_group_id%TYPE;
276 l_api_updating boolean;
277 l_proc varchar2(72) := g_package||'chk_name';
278 l_business_group_id number(15);
279 --
280 --
281 -- Cursor to check name is unique within business group
282 cursor csr_name_exists is
283 select rsc.business_group_id
284 from per_rating_scales rsc
285 , per_rating_scales_tl rsl
286 where rsc.rating_scale_id = rsl.rating_scale_id
287 and rsl.language = p_language
288 and ( (p_rating_scale_id is null)
289 or(rsc.rating_scale_id <> p_rating_scale_id)
290 )
291 and rsl.name = p_name
292 and p_business_group_id is null
293 UNION
294 select rsc.business_group_id
295 from per_rating_scales rsc
296 , per_rating_scales_tl rsl
297 where rsc.rating_scale_id = rsl.rating_scale_id
298 and rsl.language = p_language
299 and ( (p_rating_scale_id is null)
300 or(rsc.rating_scale_id <> p_rating_scale_id)
301 )
302 and rsl.name = p_name
303 and (rsc.business_group_id = p_business_group_id or
304 rsc.business_group_id is null)
305 and p_business_group_id is not null;
306 --
307 begin
308 hr_utility.set_location ('Entering:'|| l_proc, 1);
309 --
310 --
311 if p_name is null then
312 hr_utility.set_message(801, 'HR_51571_RSC_NAME_MANDATORY');
313 hr_utility.raise_error;
314 end if;
315 --
316 -- Only proceed with validation if:
317 -- a) The current g_old_rec is current and
318 -- b) The value for name has changed.
319 --
320 l_api_updating := per_rsl_shd.api_updating
321 (p_rating_scale_id => p_rating_scale_id
322 ,p_language => p_language
323 );
324 --
325 hr_utility.set_location (l_proc, 3);
326 --
327 if (l_api_updating AND
328 nvl(per_rsl_shd.g_old_rec.name, hr_api.g_varchar2)
329 <> nvl(p_name, hr_api.g_varchar2)
330 or not l_api_updating)
331 then
332 --
333 hr_utility.set_location (l_proc, 4);
334 --
335 -- Check that NAME is UNIQUE
336 --
337 open csr_name_exists;
338 hr_utility.set_location (l_proc, 100);
339 fetch csr_name_exists into l_exists;
340 if csr_name_exists%found then
341 hr_utility.set_location(l_proc, 10);
342 close csr_name_exists;
343 hr_utility.set_location(to_char(l_exists), 99);
344 if l_exists is null then
345 fnd_message.set_name('PER', 'HR_52696_RSC_NAME_IN_GLOB');
346 fnd_message.raise_error;
347 else
348 fnd_message.set_name('PER', 'HR_52697_RSC_NAME_IN_BUSGRP');
349 fnd_message.raise_error;
350 end if;
351 end if;
352 close csr_name_exists;
353 end if;
354 --
355 hr_utility.set_location ('Leaving '||l_proc, 20);
356 end chk_name;
357 --
358 -- ----------------------------------------------------------------------------
359 -- |------------------------< validate_translation>--------------------------|
360 -- ----------------------------------------------------------------------------
361 Procedure validate_translation
362 (p_rec in per_rsl_shd.g_rec_type
363 ,p_rating_scale_id in per_rating_scales_tl.rating_scale_id%TYPE default null
364 ) IS
365 --
366 l_proc varchar2(72) := g_package||'validate_translation';
367 --
368 -- Declare cursor
369 --
370 cursor csr_rating_scale is
371 select rsc.business_group_id
372 from per_rating_scales rsc
373 where rsc.rating_scale_id = nvl(p_rec.rating_scale_id, p_rating_scale_id);
374 --
375 l_rsc_rec csr_rating_scale%ROWTYPE;
376 --
377 Begin
378 --
379 hr_utility.set_location('Entering:'||l_proc,5);
380 --
381 open csr_rating_scale;
382 --
383 fetch csr_rating_scale into l_rsc_rec;
384 --
385 close csr_rating_scale;
386 --
387 validate_translation
388 (p_rating_scale_id => p_rec.rating_scale_id
389 ,p_language => p_rec.language
390 ,p_name => p_rec.name
391 ,p_description => p_rec.description
392 ,p_business_group_id => l_rsc_rec.business_group_id
393 );
394 END;
395 --
396 -- ----------------------------------------------------------------------------
397 -- |---------------------------< insert_validate >----------------------------|
398 -- ----------------------------------------------------------------------------
399 Procedure insert_validate
400 (p_rec in per_rsl_shd.g_rec_type
401 ,p_rating_scale_id in per_rating_scales_tl.rating_scale_id%TYPE
402 ) is
403 --
404 -- Name : M. Burton
405 -- Date 02-DEC-2002
406 -- Description Amended with comment
407 l_proc varchar2(72) := g_package||'insert_validate';
408 --
409 Begin
410 hr_utility.set_location('Entering:'||l_proc, 5);
411 --
412 -- Call all supporting business operations
413 --
414 --
415 validate_translation
416 ( p_rec
417 , p_rating_scale_id
418 );
419 --
420 -- Validate Dependent Attributes
421 --
422 --
423 hr_utility.set_location(' Leaving:'||l_proc, 10);
424 End insert_validate;
425 --
426 -- ----------------------------------------------------------------------------
427 -- |---------------------------< update_validate >----------------------------|
428 -- ----------------------------------------------------------------------------
429 Procedure update_validate
430 (p_rec in per_rsl_shd.g_rec_type
431 ) is
432 --
433 l_proc varchar2(72) := g_package||'update_validate';
434 --
435 Begin
436 hr_utility.set_location('Entering:'||l_proc, 5);
437 --
438 -- Call all supporting business operations
439 --
440 --
441 -- As this table does not have a mandatory business_group_id
442
443 -- No business group context. HR_STANDARD_LOOKUPS used for validation."
444
445 -- Validate Dependent Attributes
446 --
447 chk_non_updateable_args
448 (p_rec => p_rec
449 );
450 --
451 validate_translation
452 ( p_rec
453 );
454 --
455 --
456 hr_utility.set_location(' Leaving:'||l_proc, 10);
457 End update_validate;
458 --
459 -- ----------------------------------------------------------------------------
460 -- |---------------------------< delete_validate >----------------------------|
461 -- ----------------------------------------------------------------------------
462 Procedure delete_validate
463 (p_rec in per_rsl_shd.g_rec_type
464 ) is
465 --
466 l_proc varchar2(72) := g_package||'delete_validate';
467 --
468 Begin
469 hr_utility.set_location('Entering:'||l_proc, 5);
470 --
471 -- Call all supporting business operations
472 --
473 hr_utility.set_location(' Leaving:'||l_proc, 10);
474 End delete_validate;
475 --
476 -- ----------------------------------------------------------------------------
477 -- |-----------------------< set_translation_globals >------------------------|
478 -- ----------------------------------------------------------------------------
479 -- {Start Of Comments}
480 --
481 -- Description:
482 -- This procedure stores values required by validate_translations.
483 --
484 -- Prerequisites:
485 -- This procedure is called from from the MLS widget enabled forms.
486 --
487 -- In Parameters:
488 --
489 -- Post Success:
490 -- Processing continues.
491 --
492 -- Post Failure:
493 -- If a business rules fails the error will not be handled by this procedure
494 --
495 -- Developer Implementation Notes:
496 --
497 -- Access Status:
498 -- MLS Widget enabled forms only just before calling validate_translation.
499 --
500 -- {End Of Comments}
501 -- ----------------------------------------------------------------------------
502 PROCEDURE set_translation_globals
503 (p_business_group_id in number
504 ) IS
505 --
506 l_proc varchar2(72) := g_package||'set_translation_globals';
507 --
508 BEGIN
509 --
510 hr_utility.set_location('Entering:'||l_proc,5);
511 --
512 g_business_group_id := p_business_group_id;
513 --
514 hr_utility.set_location('Leaving:'||l_proc,10);
515 --
516 END;
517 --
518 -- ----------------------------------------------------------------------------
519 -- |---------------------------< validate_translation >------------------------|
520 -- ----------------------------------------------------------------------------
521 -- {Start Of Comments}
522 --
523 -- Description:
524 -- This procedure performs the validation for the MLS widget.
525 --
526 -- Prerequisites:
527 -- This procedure is called from from the MLS widget.
528 --
529 -- In Parameters:
530 --
531 -- Post Success:
532 -- Processing continues.
533 --
534 -- Post Failure:
535 -- If a business rules fails the error will not be handled by this procedure
536 --
537 -- Developer Implementation Notes:
538 --
539 -- Access Status:
540 -- MLS Widget Only.
541 --
542 -- {End Of Comments}
543 -- ----------------------------------------------------------------------------
544 Procedure validate_translation
545 (p_rating_scale_id in number
546 ,p_language in varchar2
547 ,p_name in varchar2
548 ,p_description in varchar2
549 ,p_business_group_id in number default null
550 ) IS
551 --
552 l_proc varchar2(72) := g_package||'validate_translation';
553 --
554 BEGIN
558 chk_name
555 --
556 hr_utility.set_location('Entering:'||l_proc,5);
557 --
559 ( p_rating_scale_id => p_rating_scale_id
560 , p_language => p_language
561 , p_name => p_name
562 , p_business_group_id => nvl(p_business_group_id, g_business_group_id)
563 );
564 --
565 hr_utility.set_location('Leaving:'||l_proc,10);
566 --
567 END;
568 --
569 end per_rsl_bus;
[Home] [Help]