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