[Home] [Help]
PACKAGE BODY: APPS.FF_FCU_BUS
Source
1 Package Body ff_fcu_bus as
2 /* $Header: fffcurhi.pkb 120.1 2005/10/05 01:51 adkumar noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ff_fcu_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_function_id number default null;
15 g_sequence_number number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_function_id in number
23 ,p_sequence_number in number
24 ,p_associated_column1 in varchar2 default null
25 ,p_associated_column2 in varchar2 default null
26 ) is
27 --
28 -- Declare cursor
29 --
30 -- EDIT_HERE In the following cursor statement add join(s) between
31 -- ff_function_context_usages and PER_BUSINESS_GROUPS_PERF
32 -- so that the security_group_id for
33 -- the current business group context can be derived.
34 -- Remove this comment when the edit has been completed.
35 cursor csr_sec_grp is
36 select pbg.security_group_id,
37 pbg.legislation_code
38 from per_business_groups_perf pbg
39 , ff_functions fnc
40 where fnc.function_id = p_function_id
41 and pbg.business_group_id(+) = fnc.business_group_id;
42 --
43 -- Declare local variables
44 --
45 l_security_group_id number;
46 l_proc varchar2(72) := g_package||'set_security_group_id';
47 l_legislation_code varchar2(150);
48 --
49 begin
50 --
51 hr_utility.set_location('Entering:'|| l_proc, 10);
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 => 'function_id'
58 ,p_argument_value => p_function_id
59 );
60 hr_api.mandatory_arg_error
61 (p_api_name => l_proc
62 ,p_argument => 'sequence_number'
63 ,p_argument_value => p_sequence_number
64 );
65 --
66 open csr_sec_grp;
67 fetch csr_sec_grp into l_security_group_id
68 , l_legislation_code;
69 --
70 if csr_sec_grp%notfound then
71 --
72 close csr_sec_grp;
73 --
74 -- The primary key is invalid therefore we must error
75 --
76 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
77 hr_multi_message.add
78 (p_associated_column1
79 => nvl(p_associated_column1,'FUNCTION_ID')
80 ,p_associated_column2
81 => nvl(p_associated_column2,'SEQUENCE_NUMBER')
82 );
83 --
84 else
85 close csr_sec_grp;
86 --
87 -- Set the security_group_id in CLIENT_INFO
88 --
89 hr_api.set_security_group_id
90 (p_security_group_id => l_security_group_id
91 );
92 --
93 -- Set the sessions legislation context in HR_SESSION_DATA
94 --
95 hr_api.set_legislation_context(l_legislation_code);
96 end if;
97 --
98 hr_utility.set_location(' Leaving:'|| l_proc, 20);
99 --
100 end set_security_group_id;
101 --
102 -- ---------------------------------------------------------------------------
103 -- |---------------------< return_legislation_code >-------------------------|
104 -- ---------------------------------------------------------------------------
105 --
106 Function return_legislation_code
107 (p_function_id in number
108 ,p_sequence_number in number
109 )
110 Return Varchar2 Is
111 --
112 -- Declare cursor
113 --
114 -- EDIT_HERE In the following cursor statement add join(s) between
115 -- ff_function_context_usages and PER_BUSINESS_GROUPS_PERF
116 -- so that the legislation_code for
117 -- the current business group context can be derived.
118 -- Remove this comment when the edit has been completed.
119 cursor csr_leg_code is
120 select pbg.legislation_code
121 from per_business_groups_perf pbg
122 , ff_function_context_usages fcu
123 , ff_functions fff
124 where fcu.function_id = fff.function_id
125 and fcu.function_id = p_function_id
126 and fcu.sequence_number = p_sequence_number
127 and pbg.business_group_id = fff.business_group_id;
128 --
129 -- Declare local variables
130 --
131 l_legislation_code varchar2(150);
132 l_proc varchar2(72) := g_package||'return_legislation_code';
133 --
134 Begin
135 --
136 hr_utility.set_location('Entering:'|| l_proc, 10);
137 --
138 -- Ensure that all the mandatory parameter are not null
139 --
140 hr_api.mandatory_arg_error
141 (p_api_name => l_proc
142 ,p_argument => 'function_id'
143 ,p_argument_value => p_function_id
144 );
145 hr_api.mandatory_arg_error
146 (p_api_name => l_proc
147 ,p_argument => 'sequence_number'
148 ,p_argument_value => p_sequence_number
149 );
150 --
151 if (( nvl(ff_fcu_bus.g_function_id, hr_api.g_number)
152 = p_function_id)
153 and ( nvl(ff_fcu_bus.g_sequence_number, hr_api.g_number)
154 = p_sequence_number)) then
155 --
156 -- The legislation code has already been found with a previous
157 -- call to this function. Just return the value in the global
158 -- variable.
159 --
160 l_legislation_code := ff_fcu_bus.g_legislation_code;
161 hr_utility.set_location(l_proc, 20);
162 else
163 --
164 -- The ID is different to the last call to this function
165 -- or this is the first call to this function.
166 --
167 open csr_leg_code;
168 fetch csr_leg_code into l_legislation_code;
169 --
170 if csr_leg_code%notfound then
171 --
172 -- The primary key is invalid therefore we must error
173 --
174 close csr_leg_code;
175 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
176 fnd_message.raise_error;
177 end if;
178 hr_utility.set_location(l_proc,30);
179 --
180 -- Set the global variables so the values are
181 -- available for the next call to this function.
182 --
183 close csr_leg_code;
184 ff_fcu_bus.g_function_id := p_function_id;
185 ff_fcu_bus.g_sequence_number := p_sequence_number;
186 ff_fcu_bus.g_legislation_code := l_legislation_code;
187 end if;
188 hr_utility.set_location(' Leaving:'|| l_proc, 40);
189 return l_legislation_code;
190 end return_legislation_code;
191 --
192 -- ----------------------------------------------------------------------------
193 -- |-----------------------< chk_non_updateable_args >------------------------|
194 -- ----------------------------------------------------------------------------
195 -- {Start Of Comments}
196 --
197 -- Description:
198 -- This procedure is used to ensure that non updateable attributes have
199 -- not been updated. If an attribute has been updated an error is generated.
200 --
201 -- Pre Conditions:
202 -- g_old_rec has been populated with details of the values currently in
203 -- the database.
204 --
205 -- In Arguments:
206 -- p_rec has been populated with the updated values the user would like the
207 -- record set to.
208 --
209 -- Post Success:
210 -- Processing continues if all the non updateable attributes have not
211 -- changed.
212 --
213 -- Post Failure:
214 -- An application error is raised if any of the non updatable attributes
215 -- have been altered.
216 --
217 -- {End Of Comments}
218 -- ----------------------------------------------------------------------------
219 Procedure chk_non_updateable_args
220 (p_rec in ff_fcu_shd.g_rec_type
221 ) IS
222 --
223 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
224 --
225 Begin
226 --
227 -- Only proceed with the validation if a row exists for the current
228 -- record in the HR Schema.
229 --
230 IF NOT ff_fcu_shd.api_updating
231 (p_function_id => p_rec.function_id
232 ,p_sequence_number => p_rec.sequence_number
233 ,p_object_version_number => p_rec.object_version_number
234 ) THEN
235 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
236 fnd_message.set_token('PROCEDURE ', l_proc);
237 fnd_message.set_token('STEP ', '5');
238 fnd_message.raise_error;
239 END IF;
240 --
241 --
242 if nvl(p_rec.function_id, hr_api.g_number) <>
243 ff_fcu_shd.g_old_rec.function_id then
244 hr_api.argument_changed_error
245 (p_api_name => l_proc
246 ,p_argument => 'FUNCTION_ID'
247 ,p_base_table => ff_fcu_shd.g_tab_nam
248 );
249 end if;
250 --
251 if nvl(p_rec.sequence_number, hr_api.g_number) <>
252 ff_fcu_shd.g_old_rec.sequence_number then
253 hr_api.argument_changed_error
254 (p_api_name => l_proc
255 ,p_argument => 'SEQUENCE_NUMBER'
256 ,p_base_table => ff_fcu_shd.g_tab_nam
257 );
258 end if;
259 --
260 End chk_non_updateable_args;
261
262 --
263 -- ----------------------------------------------------------------------------
264 -- |--------------------------< chk_function_id >---------------------------|
265 -- ----------------------------------------------------------------------------
266 procedure chk_function_id
267 (p_function_id in number
268 ) is
269 --
270 cursor csr_function_id(p_function_id in number) is
271 select NULL
272 from FF_FUNCTIONS fnc
273 where fnc.function_id = p_function_id
274 ;
275 --
276 l_proc varchar2(100) := g_package || 'chk_function_id';
277 l_exists varchar2(1);
278
279 begin
280 --
281 -- FUNCTION_ID is mandatory.
282 --
283 hr_api.mandatory_arg_error
284 (p_api_name => l_proc
285 ,p_argument => 'FUNCTION_ID'
286 ,p_argument_value => p_function_id
287 );
288 --
289 open csr_function_id(p_function_id => p_function_id);
290 fetch csr_function_id into l_exists;
291 if csr_function_id%notfound then
292 close csr_function_id;
293 ff_fcu_shd.constraint_error('FF_FUNCTION_CONTEXT_USAGES_FK1');
294 end if;
295 close csr_function_id;
296 exception
297 when app_exception.application_exception then
298 if hr_multi_message.exception_add
299 (p_associated_column1 => 'FF_FUNCTION_CONTEXT_USAGES.FUNCTION_ID'
300 ) then
301 raise;
302 end if;
303 when others then
304 if csr_function_id%isopen then
305 close csr_function_id;
306 end if;
307 raise;
308 end chk_function_id;
309 --
310 -- ----------------------------------------------------------------------------
311 -- |--------------------------< chk_context_id >---------------------------|
312 -- ----------------------------------------------------------------------------
313 procedure chk_context_id
314 (p_context_id in number
315 ) is
316 --
317 cursor csr_context is
318 select NULL
319 from FF_CONTEXTS fc
320 where fc.context_id = p_context_id;
321
322 --
323 l_proc varchar2(100) := g_package || 'chk_context_id';
324 l_exists varchar2(1);
325 begin
326 -- CONTEXT_ID is mandatory.
327 --
328 hr_api.mandatory_arg_error
329 (p_api_name => l_proc
330 ,p_argument => 'CONTEXT_ID'
331 ,p_argument_value => p_context_id
332 );
333 --
334 open csr_context;
335 fetch csr_context into l_exists;
336 if csr_context%notfound then
337 close csr_context;
338 ff_fcu_shd.constraint_error('FF_FUNCTION_CONTEXT_USAGES_FK2');
339 end if;
340 close csr_context;
341 exception
342 when app_exception.application_exception then
343 if hr_multi_message.exception_add
344 (p_associated_column1 => 'FF_FUNCTION_CONTEXT_USAGES.CONTEXT_ID'
345 ) then
346 raise;
347 end if;
348 when others then
349 if csr_context%isopen then
350 close csr_context;
351 end if;
352 raise;
353 end chk_context_id;
354 --
355 -- ----------------------------------------------------------------------------
356 -- |--------------------------< chk_unique >----------------------------------|
357 -- ----------------------------------------------------------------------------
358 procedure chk_unique
359 (p_function_id in number
360 ,p_context_id in number
361 ,p_sequence_number in number
362 ) is
363 --
364 cursor csr_unique_context
365 is
366 select NULL
367 from ff_function_context_usages ffcu
368 where ffcu.function_id = p_function_id
369 and ffcu.context_id = p_context_id
370 and ffcu.sequence_number <> nvl(p_sequence_number,-1);
371 --
372 l_proc varchar2(100) := g_package || 'chk_unique';
373 l_exists varchar2(1);
374
375 begin
376 --
377 --
378 if hr_multi_message.no_exclusive_error
379 (p_check_column1 => 'FF_FUNCTION_CONTEXT_USAGES.FUNCTION_ID'
380 ,p_check_column2 => 'FF_FUNCTION_CONTEXT_USAGES.CONTEXT_ID'
381 ,p_associated_column1 => 'FF_FUNCTION_CONTEXT_USAGES.FUNCTION_ID'
382 ,p_associated_column2 => 'FF_FUNCTION_CONTEXT_USAGES.CONTEXT_ID'
383 ) then
384
385 open csr_unique_context;
386 fetch csr_unique_context into l_exists;
387 if csr_unique_context%found then
388 close csr_unique_context;
389 ff_fcu_shd.constraint_error('FF_FUNCTION_CONTEXT_USAGES_UK2');
390 end if;
391 close csr_unique_context;
395 if hr_multi_message.exception_add
392 end if;
393 exception
394 when app_exception.application_exception then
396 (p_same_associated_columns => 'Y'
397 ) then
398 raise;
399 end if;
400 when others then
401 if csr_unique_context%isopen then
402 close csr_unique_context;
403 end if;
404 raise;
405 end chk_unique;
406 --
407 -- ----------------------------------------------------------------------------
408 -- |----------------------< chk_startup_action >------------------------------|
409 -- ----------------------------------------------------------------------------
410 --
411 -- Description:
412 -- This procedure will check that the current action is allowed according
413 -- to the current startup mode.
414 --
415 -- ----------------------------------------------------------------------------
416 PROCEDURE chk_startup_action
417 (p_insert IN boolean
418 ,p_business_group_id IN number
419 ,p_legislation_code IN varchar2
420 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
421 --
422 BEGIN
423 --
424 -- Call the supporting procedure to check startup mode
425
426 IF (p_insert) THEN
427
428 if p_business_group_id is not null and p_legislation_code is not null then
429 fnd_message.set_name('PAY', 'PAY_33179_BGLEG_INVALID');
430 fnd_message.raise_error;
431 end if;
432
433 hr_startup_data_api_support.chk_startup_action
434 (p_generic_allowed => TRUE
435 ,p_startup_allowed => TRUE
436 ,p_user_allowed => TRUE
437 ,p_business_group_id => p_business_group_id
438 ,p_legislation_code => p_legislation_code
439 ,p_legislation_subgroup => p_legislation_subgroup
440 );
441 ELSE
442 hr_startup_data_api_support.chk_upd_del_startup_action
443 (p_generic_allowed => TRUE
444 ,p_startup_allowed => TRUE
445 ,p_user_allowed => TRUE
446 ,p_business_group_id => p_business_group_id
447 ,p_legislation_code => p_legislation_code
448 ,p_legislation_subgroup => p_legislation_subgroup
449 );
450 END IF;
451 --
452 END chk_startup_action;
453 --
454 -- ----------------------------------------------------------------------------
455 -- |---------------------------< insert_validate >----------------------------|
456 -- ----------------------------------------------------------------------------
457 Procedure insert_validate
458 (p_rec in ff_fcu_shd.g_rec_type
459 ) is
460 --
461 cursor csr_business_group is
462 select business_group_id
463 , legislation_code
464 from FF_FUNCTIONS fnc
465 where fnc.function_id = p_rec.function_id;
466 --
467 l_proc varchar2(72) := g_package||'insert_validate';
468 l_business_group_id ff_functions.business_group_id%type;
469 l_legislation_code ff_functions.legislation_code%type;
470 --
471 Begin
472 hr_utility.set_location('Entering:'||l_proc, 5);
473 --
474 -- Call all supporting business operations
475 --
476 -----------------------------------------------------------------------------
477 chk_function_id(p_function_id => p_rec.function_id);
478 -----------------------------------------------------------------------------
479 open csr_business_group;
480 fetch csr_business_group into l_business_group_id,l_legislation_code;
481 close csr_business_group;
482
483 chk_startup_action(true
484 ,l_business_group_id
485 ,l_legislation_code
486 );
487
488 --
489
490 -- Validate Dependent Attributes
491 --
492 -----------------------------------------------------------------------------
493 chk_context_id(p_context_id => p_rec.context_id);
494
495 -----------------------------------------------------------------------------
496 chk_unique(p_function_id => p_rec.function_id
497 ,p_context_id => p_rec.context_id
498 ,p_sequence_number => p_rec.sequence_number);
499
500 hr_utility.set_location(' Leaving:'||l_proc, 10);
501
502 End insert_validate;
503 --
504 -- ----------------------------------------------------------------------------
505 -- |---------------------------< update_validate >----------------------------|
506 -- ----------------------------------------------------------------------------
507 Procedure update_validate
508 (p_rec in ff_fcu_shd.g_rec_type
509 ) is
510 --
511 cursor csr_business_group is
512 select business_group_id
513 , legislation_code
514 from FF_FUNCTIONS fnc
515 where fnc.function_id = p_rec.function_id;
516 --
517 l_business_group_id ff_functions.business_group_id%type;
518 l_legislation_code ff_functions.legislation_code%type;
519 l_proc varchar2(72) := g_package||'update_validate';
520 --
521 Begin
522 hr_utility.set_location('Entering:'||l_proc, 5);
523 --
524 -- Call all supporting business operations
525 --
526 -----------------------------------------------------------------------------
527 chk_function_id(p_function_id => p_rec.function_id);
531 close csr_business_group;
528 -----------------------------------------------------------------------------
529 open csr_business_group;
530 fetch csr_business_group into l_business_group_id,l_legislation_code;
532
533 chk_startup_action(true
534 ,l_business_group_id
535 ,l_legislation_code
536 );
537
538 --
539
540 -- Validate Dependent Attributes
541 --
542 -----------------------------------------------------------------------------
543 chk_context_id(p_context_id => p_rec.context_id);
544
545 -----------------------------------------------------------------------------
546 chk_unique(p_function_id => p_rec.function_id
547 ,p_context_id => p_rec.context_id
548 ,p_sequence_number => p_rec.sequence_number);
549
550 -----------------------------------------------------------------------------
551 chk_non_updateable_args
552 (p_rec => p_rec
553 );
554 --
555 --
556 hr_utility.set_location(' Leaving:'||l_proc, 10);
557 End update_validate;
558 --
559 -- ----------------------------------------------------------------------------
560 -- |---------------------------< delete_validate >----------------------------|
561 -- ----------------------------------------------------------------------------
562 Procedure delete_validate
563 (p_rec in ff_fcu_shd.g_rec_type
564 ) is
565 --
566 cursor csr_business_group is
567 select business_group_id
568 , legislation_code
569 from FF_FUNCTIONS fnc
570 where fnc.function_id = p_rec.function_id;
571 --
572 l_business_group_id ff_functions.business_group_id%type;
573 l_legislation_code ff_functions.legislation_code%type;
574 l_proc varchar2(72) := g_package||'delete_validate';
575 --
576 Begin
577 hr_utility.set_location('Entering:'||l_proc, 5);
578 --
579 -----------------------------------------------------------------------------
580 chk_function_id(p_function_id => p_rec.function_id);
581 -----------------------------------------------------------------------------
582 open csr_business_group;
583 fetch csr_business_group into l_business_group_id,l_legislation_code;
584 close csr_business_group;
585
586 chk_startup_action(true
587 ,l_business_group_id
588 ,l_legislation_code
589 );
590 --
591 -- Validate Dependent Attributes
592 --
593 --
594 hr_utility.set_location(' Leaving:'||l_proc, 10);
595 End delete_validate;
596 --
597 end ff_fcu_bus;