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