1 Package Body per_abv_bus as
2 /* $Header: peabvrhi.pkb 120.0.12010000.2 2009/05/27 06:36:51 brsinha ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_abv_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_assignment_budget_value_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_assignment_budget_value_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , per_assignment_budget_values_f abv
30 where abv.assignment_budget_value_id = p_assignment_budget_value_id
31 and pbg.business_group_id = abv.business_group_id;
32 --
33 -- Declare local variables
34 --
35 l_security_group_id number;
36 l_proc varchar2(72) := g_package||'set_security_group_id';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'assignment_budget_value_id'
47 ,p_argument_value => p_assignment_budget_value_id
48 );
49 --
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 --
53 if csr_sec_grp%notfound then
54 --
55 close csr_sec_grp;
56 --
57 -- The primary key is invalid therefore we must error
58 --
59 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
60 fnd_message.raise_error;
61 --
62 end if;
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 20);
72 --
73 end set_security_group_id;
74 --
75 -- ---------------------------------------------------------------------------
76 -- |---------------------< return_legislation_code >-------------------------|
77 -- ---------------------------------------------------------------------------
78 --
79 Function return_legislation_code
80 (p_assignment_budget_value_id in number
81 )
82 Return Varchar2 Is
83 --
84 -- Declare cursor
85 --
86 cursor csr_leg_code is
87 select pbg.legislation_code
88 from per_business_groups pbg
89 , per_assignment_budget_values_f abv
90 where abv.assignment_budget_value_id = p_assignment_budget_value_id
91 and pbg.business_group_id = abv.business_group_id;
92 --
93 -- Declare local variables
94 --
95 l_legislation_code varchar2(150);
96 l_proc varchar2(72) := g_package||'return_legislation_code';
97 --
98 Begin
99 --
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_api.mandatory_arg_error
105 (p_api_name => l_proc
106 ,p_argument => 'assignment_budget_value_id'
107 ,p_argument_value => p_assignment_budget_value_id
108 );
109 --
110 if ( nvl(per_abv_bus.g_assignment_budget_value_id, hr_api.g_number)
111 = p_assignment_budget_value_id) then
112 --
113 -- The legislation code has already been found with a previous
114 -- call to this function. Just return the value in the global
115 -- variable.
116 --
117 l_legislation_code := per_abv_bus.g_legislation_code;
118 hr_utility.set_location(l_proc, 20);
119 else
120 --
121 -- The ID is different to the last call to this function
122 -- or this is the first call to this function.
126 --
123 --
124 open csr_leg_code;
125 fetch csr_leg_code into l_legislation_code;
127 if csr_leg_code%notfound then
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 close csr_leg_code;
132 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
133 fnd_message.raise_error;
134 end if;
135 hr_utility.set_location(l_proc,30);
136 --
137 -- Set the global variables so the values are
138 -- available for the next call to this function.
139 --
140 close csr_leg_code;
141 per_abv_bus.g_assignment_budget_value_id := p_assignment_budget_value_id;
142 per_abv_bus.g_legislation_code := l_legislation_code;
143 end if;
144 hr_utility.set_location(' Leaving:'|| l_proc, 40);
145 return l_legislation_code;
146 end return_legislation_code;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |-----------------------< chk_non_updateable_args >------------------------|
150 -- ----------------------------------------------------------------------------
151 -- {Start Of Comments}
152 --
153 -- Description:
154 -- This procedure is used to ensure that non updateable attributes have
155 -- not been updated. If an attribute has been updated an error is generated.
156 --
157 -- Pre Conditions:
158 -- g_old_rec has been populated with details of the values currently in
159 -- the database.
160 --
161 -- In Arguments:
162 -- p_rec has been populated with the updated values the user would like the
163 -- record set to.
164 --
165 -- Post Success:
166 -- Processing continues if all the non updateable attributes have not
167 -- changed.
168 --
169 -- Post Failure:
170 -- An application error is raised if any of the non updatable attributes
171 -- have been altered.
172 --
173 -- {End Of Comments}
174 -- ----------------------------------------------------------------------------
175 Procedure chk_non_updateable_args
176 (p_effective_date in date
177 ,p_rec in per_abv_shd.g_rec_type
178 ) IS
179 --
180 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
181 l_error EXCEPTION;
182 l_argument varchar2(30);
183 --
184 Begin
185 --
186 -- Only proceed with the validation if a row exists for the current
187 -- record in the HR Schema.
188 --
189 IF NOT per_abv_shd.api_updating
190 (p_assignment_budget_value_id => p_rec.assignment_budget_value_id
191 ,p_effective_date => p_effective_date
192 ,p_object_version_number => p_rec.object_version_number
193 ) THEN
194 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
195 fnd_message.set_token('PROCEDURE ', l_proc);
196 fnd_message.set_token('STEP ', '5');
197 fnd_message.raise_error;
198 END IF;
199 --
200 -- EDIT_HERE: Add checks to ensure non-updateable args have
201 -- not been updated.
202 --
203 EXCEPTION
204 WHEN l_error THEN
205 hr_api.argument_changed_error
206 (p_api_name => l_proc
207 ,p_argument => l_argument);
208 WHEN OTHERS THEN
209 RAISE;
210 End chk_non_updateable_args;
211
212
213 --
214 -- ----------------------------------------------------------------------------
215 -- |--------------------------< chk_unit >------------------------------------|
216 -- ----------------------------------------------------------------------------
217 -- {Start Of Comments}
218 --
219 -- Description:
220 -- This procedure is used to ensure that the budget unit exists in hr_lookups
221 --
222 procedure chk_unit(p_unit in varchar2
223 ,p_effective_date in date)
224 is
225 cursor c is
226 select 'x'
227 from hr_lookups
228 where p_unit = lookup_code
229 and lookup_type='BUDGET_MEASUREMENT_TYPE'
230 and enabled_flag = 'Y'
231 and p_effective_date between nvl(start_date_active,hr_api.g_sot) and nvl(start_date_active,hr_api.g_eot);
232 l_exists varchar2(1);
233 begin
234 --
235 open c;
236 fetch c into l_exists;
237
238 if not c%found then
239 --
240 close c;
241 hr_utility.set_message(801,'HR_289395_INVALID_ABV_UNIT');
242 hr_utility.raise_error;
243 --
244 end if;
245
246 close c;
247 --
248 end chk_unit;
249 --
250
251 --
252 --
253 -- ----------------------------------------------------------------------------
254 -- |------------------------< chk_assignment_id >-----------------------------|
255 -- ----------------------------------------------------------------------------
256 -- {Start Of Comments}
257 --
258 -- Description:
259 -- This procedure is used to ensure that the assignment exists in
260 -- per_assignments_f and is in the same business group.
261 --
262 procedure chk_assignment_id(p_assignment_id number
263 ,p_business_group_id number
264 ,p_effective_date date) is
265 cursor c is
266 select 'x'
267 from per_assignments_f
268 where assignment_id = p_assignment_id
269 and p_effective_date between effective_start_date and effective_end_date
270 and business_group_id = p_business_group_id;
271
272 l_exists varchar2(1);
273 begin
274 --
275 open c;
276 fetch c into l_exists;
277
281 --
278 -- Should have been not c%found, modified while fixing for Bug 2172818
279
280 if not c%found then
282 close c;
283 hr_utility.set_message(801,'HR_289396_ABV_ASSIGNMENT_ERROR');
284 hr_utility.raise_error;
285 --
286 end if;
287
288 close c;
289 --
290 end chk_assignment_id;
291 --
292
293
294 -- ----------------------------------------------------------------------------
295 -- |-----------------------< chk_unique_row >---------------------------------|
296 -- ----------------------------------------------------------------------------
297 -- {Start Of Comments}
298 --
299 -- Description:
300 -- This procedure is used to ensure that the same budget unit is not used
301 -- more than once for the same assignment.
302 --
303 procedure chk_unique_row(p_assignment_id number
304 ,p_assignment_budget_value_id number
305 ,p_unit varchar2
306 ,p_effective_date date) is
307 cursor c is
308 select 'x'
309 from per_assignment_budget_values_f
310 where assignment_id = p_assignment_id
311 and unit = p_unit
312 and p_effective_date between effective_start_date and effective_end_date
313 and assignment_budget_value_id <> nvl(p_assignment_budget_value_id, -1);
314
315 l_exists varchar2(1);
316 begin
317 --
318 open c;
319 fetch c into l_exists;
320
321 if c%found then
322 --
323 close c;
324 hr_utility.set_message(801,'HR_289367_ABV_DUPLICATE_UNIT');
325 hr_utility.raise_error;
326 --
327 end if;
328
329 close c;
330 --
331 end chk_unique_row;
332 --
333 --
334 -- ----------------------------------------------------------------------------
335 -- |--------------------------< dt_update_validate >--------------------------|
336 -- ----------------------------------------------------------------------------
337 -- {Start Of Comments}
338 --
339 -- Description:
340 -- This procedure is used for referential integrity of datetracked
341 -- parent entities when a datetrack update operation is taking place
342 -- and where there is no cascading of update defined for this entity.
343 --
344 -- Prerequisites:
345 -- This procedure is called from the update_validate.
346 --
347 -- In Parameters:
348 --
349 -- Post Success:
350 -- Processing continues.
351 --
352 -- Post Failure:
353 --
354 -- Developer Implementation Notes:
355 -- This procedure should not need maintenance unless the HR Schema model
356 -- changes.
357 --
358 -- Access Status:
359 -- Internal Row Handler Use Only.
360 --
361 -- {End Of Comments}
362 -- ----------------------------------------------------------------------------
363 Procedure dt_update_validate
364 (p_assignment_id in number default hr_api.g_number
365 ,p_datetrack_mode in varchar2
366 ,p_validation_start_date in date
367 ,p_validation_end_date in date
368 ) Is
369 --
370 l_proc varchar2(72) := g_package||'dt_update_validate';
371 l_integrity_error Exception;
372 l_table_name all_tables.table_name%TYPE;
373 --
374 Begin
375 --
376 -- Ensure that the p_datetrack_mode argument is not null
377 --
378 hr_api.mandatory_arg_error
379 (p_api_name => l_proc
380 ,p_argument => 'datetrack_mode'
381 ,p_argument_value => p_datetrack_mode
382 );
383 --
384 -- Mode will be valid, as this is checked at the start of the upd.
385 --
386 -- Ensure the arguments are not null
387 --
388 hr_api.mandatory_arg_error
389 (p_api_name => l_proc
390 ,p_argument => 'validation_start_date'
391 ,p_argument_value => p_validation_start_date
392 );
393 --
394 hr_api.mandatory_arg_error
395 (p_api_name => l_proc
396 ,p_argument => 'validation_end_date'
397 ,p_argument_value => p_validation_end_date
398 );
399 --
400 If ((nvl(p_assignment_id, hr_api.g_number) <> hr_api.g_number) and
401 NOT (dt_api.check_min_max_dates
402 (p_base_table_name => 'per_all_assignments_f'
403 ,p_base_key_column => 'ASSIGNMENT_ID'
404 ,p_base_key_value => p_assignment_id
405 ,p_from_date => p_validation_start_date
406 ,p_to_date => p_validation_end_date))) Then
407 l_table_name := 'all assignments';
408 raise l_integrity_error;
409 End If;
410 --
411 Exception
412 When l_integrity_error Then
413 --
414 -- A referential integrity check was violated therefore
415 -- we must error
416 --
417 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
418 fnd_message.set_token('TABLE_NAME', l_table_name);
419 fnd_message.raise_error;
420 When Others Then
421 --
422 -- An unhandled or unexpected error has occurred which
423 -- we must report
424 --
425 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
426 fnd_message.set_token('PROCEDURE', l_proc);
427 fnd_message.set_token('STEP','15');
428 fnd_message.raise_error;
429 End dt_update_validate;
430 --
431 -- ----------------------------------------------------------------------------
432 -- |--------------------------< dt_delete_validate >--------------------------|
436 -- Description:
433 -- ----------------------------------------------------------------------------
434 -- {Start Of Comments}
435 --
437 -- This procedure is used for referential integrity of datetracked
438 -- child entities when either a datetrack DELETE or ZAP is in operation
439 -- and where there is no cascading of delete defined for this entity.
440 -- For the datetrack mode of DELETE or ZAP we must ensure that no
441 -- datetracked child rows exist between the validation start and end
442 -- dates.
443 --
444 -- Prerequisites:
445 -- This procedure is called from the delete_validate.
446 --
447 -- In Parameters:
448 --
449 -- Post Success:
450 -- Processing continues.
451 --
452 -- Post Failure:
453 -- If a row exists by determining the returning Boolean value from the
454 -- generic dt_api.rows_exist function then we must supply an error via
455 -- the use of the local exception handler l_rows_exist.
456 --
457 -- Developer Implementation Notes:
458 -- This procedure should not need maintenance unless the HR Schema model
459 -- changes.
460 --
461 -- Access Status:
462 -- Internal Row Handler Use Only.
463 --
464 -- {End Of Comments}
465 -- ----------------------------------------------------------------------------
466 Procedure dt_delete_validate
467 (p_assignment_budget_value_id in number
468 ,p_datetrack_mode in varchar2
469 ,p_validation_start_date in date
470 ,p_validation_end_date in date
471 ) Is
472 --
473 l_proc varchar2(72) := g_package||'dt_delete_validate';
474 l_rows_exist Exception;
475 l_table_name all_tables.table_name%TYPE;
476 --
477 Begin
478 --
479 -- Ensure that the p_datetrack_mode argument is not null
480 --
481 hr_api.mandatory_arg_error
482 (p_api_name => l_proc
483 ,p_argument => 'datetrack_mode'
484 ,p_argument_value => p_datetrack_mode
485 );
486 --
487 -- Only perform the validation if the datetrack mode is either
488 -- DELETE or ZAP
489 --
490 If (p_datetrack_mode = hr_api.g_delete or
491 p_datetrack_mode = hr_api.g_zap) then
492 --
493 --
494 -- Ensure the arguments are not null
495 --
496 hr_api.mandatory_arg_error
497 (p_api_name => l_proc
498 ,p_argument => 'validation_start_date'
499 ,p_argument_value => p_validation_start_date
500 );
501 --
502 hr_api.mandatory_arg_error
503 (p_api_name => l_proc
504 ,p_argument => 'validation_end_date'
505 ,p_argument_value => p_validation_end_date
506 );
507 --
508 hr_api.mandatory_arg_error
509 (p_api_name => l_proc
510 ,p_argument => 'assignment_budget_value_id'
511 ,p_argument_value => p_assignment_budget_value_id
512 );
513 --
514 --
515 --
516 End If;
517 --
518 Exception
519 When l_rows_exist Then
520 --
521 -- A referential integrity check was violated therefore
522 -- we must error
523 --
524 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
525 fnd_message.set_token('TABLE_NAME', l_table_name);
526 fnd_message.raise_error;
527 When Others Then
528 --
529 -- An unhandled or unexpected error has occurred which
530 -- we must report
531 --
532 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
533 fnd_message.set_token('PROCEDURE', l_proc);
534 fnd_message.set_token('STEP','15');
535 fnd_message.raise_error;
536 --
537 End dt_delete_validate;
538 --
539 -- ----------------------------------------------------------------------------
540 -- |---------------------------< insert_validate >----------------------------|
541 -- ----------------------------------------------------------------------------
542 Procedure insert_validate
543 (p_rec in per_abv_shd.g_rec_type
544 ,p_effective_date in date
545 ,p_datetrack_mode in varchar2
546 ,p_validation_start_date in date
547 ,p_validation_end_date in date
548 ) is
549 --
550 l_proc varchar2(72) := g_package||'insert_validate';
551 --
552 Begin
553 hr_utility.set_location('Entering:'||l_proc, 5);
554 --
555 -- Call all supporting business operations
556 --
557 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
558 --
559 chk_assignment_id(p_assignment_id => p_rec.assignment_id
560 ,p_business_group_id => p_rec.business_group_id
561 ,p_effective_date => p_effective_date);
562 --
563 chk_unique_row(p_assignment_id => p_rec.assignment_id
564 ,p_assignment_budget_value_id => p_rec.assignment_budget_value_id
565 ,p_unit => p_rec.unit
566 ,p_effective_date => p_effective_date);
567 --
568 chk_unit (p_unit =>p_rec.unit
569 ,p_effective_date => p_effective_date);
570
571 --
572
573 hr_utility.set_location(' Leaving:'||l_proc, 10);
574 End insert_validate;
575 --
576 -- ----------------------------------------------------------------------------
577 -- |---------------------------< update_validate >----------------------------|
578 -- ----------------------------------------------------------------------------
582 ,p_datetrack_mode in varchar2
579 Procedure update_validate
580 (p_rec in per_abv_shd.g_rec_type
581 ,p_effective_date in date
583 ,p_validation_start_date in date
584 ,p_validation_end_date in date
585 ) is
586 --
587 l_proc varchar2(72) := g_package||'update_validate';
588 --
589 Begin
590 hr_utility.set_location('Entering:'||l_proc, 5);
591 --
592 -- Call all supporting business operations
593 --
594 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
595 --
596 chk_unique_row(p_assignment_id => p_rec.assignment_id
597 ,p_assignment_budget_value_id => p_rec.assignment_budget_value_id
598 ,p_unit => p_rec.unit
599 ,p_effective_date => p_effective_date);
600 --
601 -- Call the datetrack update integrity operation
602 --
603 chk_unit (p_unit =>p_rec.unit
604 ,p_effective_date => p_effective_date);
605
606 dt_update_validate
607 (p_assignment_id => p_rec.assignment_id
608 ,p_datetrack_mode => p_datetrack_mode
609 ,p_validation_start_date => p_validation_start_date
610 ,p_validation_end_date => p_validation_end_date
611 );
612 --
613 chk_non_updateable_args
614 (p_effective_date => p_effective_date
615 ,p_rec => p_rec
616 );
617 --
618 --
619 hr_utility.set_location(' Leaving:'||l_proc, 10);
620 End update_validate;
621 --
622 -- ----------------------------------------------------------------------------
623 -- |---------------------------< delete_validate >----------------------------|
624 -- ----------------------------------------------------------------------------
625 Procedure delete_validate
626 (p_rec in per_abv_shd.g_rec_type
627 ,p_effective_date in date
628 ,p_datetrack_mode in varchar2
629 ,p_validation_start_date in date
630 ,p_validation_end_date in date
631 ) is
632 --
633 l_proc varchar2(72) := g_package||'delete_validate';
634 --
635 Begin
636 hr_utility.set_location('Entering:'||l_proc, 5);
637 --
638 -- Call all supporting business operations
639 --
640 dt_delete_validate
641 (p_datetrack_mode => p_datetrack_mode
642 ,p_validation_start_date => p_validation_start_date
643 ,p_validation_end_date => p_validation_end_date
644 ,p_assignment_budget_value_id => p_rec.assignment_budget_value_id
645 );
646 --
647 hr_utility.set_location(' Leaving:'||l_proc, 10);
648 End delete_validate;
649 --
650 end per_abv_bus;
[Home] [Help]