1 Package Body pay_evq_bus as
2 /* $Header: pyevqrhi.pkb 120.0 2005/05/29 04:49:50 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_evq_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_event_qualifier_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_event_qualifier_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id
29 from per_business_groups pbg
30 , pay_event_qualifiers_f evq
31 where evq.event_qualifier_id = p_event_qualifier_id
32 and pbg.business_group_id = evq.business_group_id;
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_proc varchar2(72) := g_package||'set_security_group_id';
38 --
39 begin
40 --
41 hr_utility.set_location('Entering:'|| l_proc, 10);
42 --
43 -- Ensure that all the mandatory parameter are not null
44 --
45 hr_api.mandatory_arg_error
46 (p_api_name => l_proc
47 ,p_argument => 'event_qualifier_id'
48 ,p_argument_value => p_event_qualifier_id
49 );
50 --
51 open csr_sec_grp;
52 fetch csr_sec_grp into l_security_group_id;
53 --
54 if csr_sec_grp%notfound then
55 --
56 close csr_sec_grp;
57 --
58 -- The primary key is invalid therefore we must error
59 --
60 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
61 hr_multi_message.add
62 (p_associated_column1
63 => nvl(p_associated_column1,'EVENT_QUALIFIER_ID')
64 );
65 --
66 else
67 close csr_sec_grp;
68 --
69 -- Set the security_group_id in CLIENT_INFO
70 --
71 hr_api.set_security_group_id
72 (p_security_group_id => l_security_group_id
73 );
74 end if;
75 --
76 hr_utility.set_location(' Leaving:'|| l_proc, 20);
77 --
78 end set_security_group_id;
79 --
80 -- ---------------------------------------------------------------------------
81 -- |---------------------< return_legislation_code >-------------------------|
82 -- ---------------------------------------------------------------------------
83 --
84 Function return_legislation_code
85 (p_event_qualifier_id in number
86 )
87 Return Varchar2 Is
88 --
89 -- Declare cursor
90 --
91 cursor csr_leg_code is
92 select pbg.legislation_code
93 from per_business_groups pbg
94 , pay_event_qualifiers_f evq
95 where evq.event_qualifier_id = p_event_qualifier_id
96 and pbg.business_group_id (+) = evq.business_group_id;
97 --
98 -- Declare local variables
99 --
100 l_legislation_code varchar2(150);
101 l_proc varchar2(72) := g_package||'return_legislation_code';
102 --
103 Begin
104 --
105 hr_utility.set_location('Entering:'|| l_proc, 10);
106 --
107 -- Ensure that all the mandatory parameter are not null
108 --
109 hr_api.mandatory_arg_error
110 (p_api_name => l_proc
111 ,p_argument => 'event_qualifier_id'
112 ,p_argument_value => p_event_qualifier_id
113 );
114 --
115 if ( nvl(pay_evq_bus.g_event_qualifier_id, hr_api.g_number)
116 = p_event_qualifier_id) then
117 --
118 -- The legislation code has already been found with a previous
119 -- call to this function. Just return the value in the global
120 -- variable.
121 --
122 l_legislation_code := pay_evq_bus.g_legislation_code;
123 hr_utility.set_location(l_proc, 20);
124 else
125 --
126 -- The ID is different to the last call to this function
127 -- or this is the first call to this function.
128 --
129 open csr_leg_code;
130 fetch csr_leg_code into l_legislation_code;
131 --
132 if csr_leg_code%notfound then
133 --
134 -- The primary key is invalid therefore we must error
135 --
136 close csr_leg_code;
137 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
138 fnd_message.raise_error;
139 end if;
140 hr_utility.set_location(l_proc,30);
141 --
142 -- Set the global variables so the values are
143 -- available for the next call to this function.
144 --
145 close csr_leg_code;
146 pay_evq_bus.g_event_qualifier_id := p_event_qualifier_id;
147 pay_evq_bus.g_legislation_code := l_legislation_code;
148 end if;
149 hr_utility.set_location(' Leaving:'|| l_proc, 40);
150 return l_legislation_code;
151 end return_legislation_code;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |-----------------------< chk_non_updateable_args >------------------------|
155 -- ----------------------------------------------------------------------------
156 -- {Start Of Comments}
157 --
158 -- Description:
159 -- This procedure is used to ensure that non updateable attributes have
160 -- not been updated. If an attribute has been updated an error is generated.
161 --
162 -- Pre Conditions:
163 -- g_old_rec has been populated with details of the values currently in
164 -- the database.
165 --
166 -- In Arguments:
167 -- p_rec has been populated with the updated values the user would like the
168 -- record set to.
169 --
170 -- Post Success:
171 -- Processing continues if all the non updateable attributes have not
172 -- changed.
173 --
174 -- Post Failure:
175 -- An application error is raised if any of the non updatable attributes
176 -- have been altered.
177 --
178 -- {End Of Comments}
179 -- ----------------------------------------------------------------------------
180 Procedure chk_non_updateable_args
181 (p_effective_date in date
182 ,p_rec in pay_evq_shd.g_rec_type
183 ) IS
184 --
185 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
186 l_error EXCEPTION;
187 l_argument varchar2(30);
188
189 --
190 Begin
191 --
192 -- Only proceed with the validation if a row exists for the current
193 -- record in the HR Schema.
194 --
195 IF NOT pay_evq_shd.api_updating
196 (p_event_qualifier_id => p_rec.event_qualifier_id
197 ,p_effective_date => p_effective_date
198 ,p_object_version_number => p_rec.object_version_number
199 ) THEN
200 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
201 fnd_message.set_token('PROCEDURE ', l_proc);
202 fnd_message.set_token('STEP ', '5');
203 fnd_message.raise_error;
204 END IF;
205 --
206 -- Checks to ensure non-updateable args havent been updated.
207 --
208 --
209 if (nvl(p_rec.event_qualifier_id, hr_api.g_number) <>
210 nvl(pay_evq_shd.g_old_rec.event_qualifier_id, hr_api.g_number)
211 ) then
212 l_argument := 'event_qualifier_id';
213 raise l_error;
214 END IF;
215 --
216 if (nvl(p_rec.dated_table_id, hr_api.g_number) <>
217 nvl(pay_evq_shd.g_old_rec.dated_table_id,hr_api.g_number)
218 ) then
219 l_argument := 'dated_table_id';
220 raise l_error;
221 END IF;
222 --
223 if (nvl(p_rec.column_name, hr_api.g_varchar2) <>
224 nvl(pay_evq_shd.g_old_rec.column_name,hr_api.g_varchar2)
225 ) then
226 l_argument := 'column_name';
227 raise l_error;
228 END IF;
229 --
230 if (nvl(p_rec.qualifier_name, hr_api.g_varchar2) <>
231 nvl(pay_evq_shd.g_old_rec.qualifier_name,hr_api.g_varchar2)
232 ) then
233 l_argument := 'qualifier_name';
234 raise l_error;
235 END IF;
236 --
237 if (nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
238 nvl(pay_evq_shd.g_old_rec.legislation_code,hr_api.g_varchar2)
239 ) then
240 l_argument := 'legislation_code';
241 raise l_error;
242 END IF;
243 --
244 if (nvl(p_rec.business_group_id, hr_api.g_number) <>
245 nvl(pay_evq_shd.g_old_rec.business_group_id,hr_api.g_number)
246 ) then
247 l_argument := 'business_group_id';
248 raise l_error;
249 END IF;
250 --
251 EXCEPTION
252 WHEN l_error THEN
253 hr_api.argument_changed_error
254 (p_api_name => l_proc
255 ,p_argument => l_argument);
256 WHEN OTHERS THEN
257 RAISE;
258
259 End chk_non_updateable_args;
260 --
261 -- ----------------------------------------------------------------------------
262 -- |--------------------------< dt_update_validate >--------------------------|
263 -- ----------------------------------------------------------------------------
264 -- {Start Of Comments}
265 --
266 -- Description:
267 -- This procedure is used for referential integrity of datetracked
268 -- parent entities when a datetrack update operation is taking place
269 -- and where there is no cascading of update defined for this entity.
270 --
271 -- Prerequisites:
272 -- This procedure is called from the update_validate.
273 --
274 -- In Parameters:
275 --
276 -- Post Success:
277 -- Processing continues.
278 --
279 -- Post Failure:
280 --
281 -- Developer Implementation Notes:
282 -- This procedure should not need maintenance unless the HR Schema model
283 -- changes.
284 --
285 -- Access Status:
286 -- Internal Row Handler Use Only.
287 --
288 -- {End Of Comments}
289 -- ----------------------------------------------------------------------------
290 Procedure dt_update_validate
291 (p_datetrack_mode in varchar2
292 ,p_validation_start_date in date
293 ,p_validation_end_date in date
294 ) Is
295 --
296 l_proc varchar2(72) := g_package||'dt_update_validate';
297 --
298 Begin
299 --
300 -- Ensure that the p_datetrack_mode argument is not null
301 --
302 hr_api.mandatory_arg_error
303 (p_api_name => l_proc
304 ,p_argument => 'datetrack_mode'
305 ,p_argument_value => p_datetrack_mode
306 );
307 --
308 -- Mode will be valid, as this is checked at the start of the upd.
309 --
310 -- Ensure the arguments are not null
311 --
312 hr_api.mandatory_arg_error
313 (p_api_name => l_proc
314 ,p_argument => 'validation_start_date'
315 ,p_argument_value => p_validation_start_date
316 );
317 --
318 hr_api.mandatory_arg_error
319 (p_api_name => l_proc
320 ,p_argument => 'validation_end_date'
321 ,p_argument_value => p_validation_end_date
322 );
323 --
324 --
325 --
326 Exception
327 When Others Then
328 --
329 -- An unhandled or unexpected error has occurred which
330 -- we must report
331 --
332 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
333 fnd_message.set_token('PROCEDURE', l_proc);
334 fnd_message.set_token('STEP','15');
335 fnd_message.raise_error;
336 End dt_update_validate;
337 --
338 -- ----------------------------------------------------------------------------
339 -- |--------------------------< dt_delete_validate >--------------------------|
340 -- ----------------------------------------------------------------------------
341 -- {Start Of Comments}
342 --
343 -- Description:
344 -- This procedure is used for referential integrity of datetracked
345 -- child entities when either a datetrack DELETE or ZAP is in operation
346 -- and where there is no cascading of delete defined for this entity.
347 -- For the datetrack mode of DELETE or ZAP we must ensure that no
348 -- datetracked child rows exist between the validation start and end
349 -- dates.
350 --
351 -- Prerequisites:
352 -- This procedure is called from the delete_validate.
353 --
354 -- In Parameters:
355 --
356 -- Post Success:
357 -- Processing continues.
358 --
359 -- Post Failure:
360 -- If a row exists by determining the returning Boolean value from the
361 -- generic dt_api.rows_exist function then we must supply an error via
362 -- the use of the local exception handler l_rows_exist.
363 --
364 -- Developer Implementation Notes:
365 -- This procedure should not need maintenance unless the HR Schema model
366 -- changes.
367 --
368 -- Access Status:
369 -- Internal Row Handler Use Only.
370 --
371 -- {End Of Comments}
372 -- ----------------------------------------------------------------------------
373 Procedure dt_delete_validate
374 (p_event_qualifier_id in number
375 ,p_datetrack_mode in varchar2
376 ,p_validation_start_date in date
377 ,p_validation_end_date in date
378 ) Is
379 --
380 l_proc varchar2(72) := g_package||'dt_delete_validate';
381 --
382 Begin
383 --
384 -- Ensure that the p_datetrack_mode argument is not null
385 --
386 hr_api.mandatory_arg_error
387 (p_api_name => l_proc
388 ,p_argument => 'datetrack_mode'
389 ,p_argument_value => p_datetrack_mode
390 );
391 --
392 -- Only perform the validation if the datetrack mode is either
393 -- DELETE or ZAP
394 --
395 If (p_datetrack_mode = hr_api.g_delete or
396 p_datetrack_mode = hr_api.g_zap) then
397 --
398 --
399 -- Ensure the arguments are not null
400 --
401 hr_api.mandatory_arg_error
402 (p_api_name => l_proc
403 ,p_argument => 'validation_start_date'
404 ,p_argument_value => p_validation_start_date
405 );
406 --
407 hr_api.mandatory_arg_error
408 (p_api_name => l_proc
409 ,p_argument => 'validation_end_date'
410 ,p_argument_value => p_validation_end_date
411 );
412 --
413 hr_api.mandatory_arg_error
414 (p_api_name => l_proc
415 ,p_argument => 'event_qualifier_id'
416 ,p_argument_value => p_event_qualifier_id
417 );
418 --
419 If (dt_api.rows_exist
420 (p_base_table_name => 'pay_event_value_changes_f'
421 ,p_base_key_column => 'event_qualifier_id'
422 ,p_base_key_value => p_event_qualifier_id
423 ,p_from_date => p_validation_start_date
424 ,p_to_date => p_validation_end_date
425 )) Then
426 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
427 fnd_message.set_token('TABLE_NAME','event value changes');
428 hr_multi_message.add;
429 End If;
430 --
431 End If;
432 --
433 Exception
434 When Others Then
435 --
436 -- An unhandled or unexpected error has occurred which
437 -- we must report
438 --
439 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
440 fnd_message.set_token('PROCEDURE', l_proc);
441 fnd_message.set_token('STEP','15');
442 fnd_message.raise_error;
443 --
444 End dt_delete_validate;
445 --
446 -- ----------------------------------------------------------------------------
447 -- |----------------------< chk_startup_action >------------------------------|
448 -- ----------------------------------------------------------------------------
449 --
450 -- Description:
451 -- This procedure will check that the current action is allowed according
452 -- to the current startup mode.
453 --
454 -- ----------------------------------------------------------------------------
455 PROCEDURE chk_startup_action
456 (p_insert IN boolean
457 ,p_business_group_id IN number
458 ,p_legislation_code IN varchar2
459 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
460 --
461 l_proc varchar2(72) := g_package||'chk_startup_action';
462
463 BEGIN
464 --
465 -- Call the supporting procedure to check startup mode
466 -- EDIT_HERE: The following call should be edited if certain types of rows
467 -- are not permitted.
468
469 hr_utility.set_location('Entering:'||l_proc, 5);
470
471 IF (p_insert) THEN
472 hr_startup_data_api_support.chk_startup_action
473 (p_generic_allowed => TRUE
474 ,p_startup_allowed => TRUE
475 ,p_user_allowed => TRUE
476 ,p_business_group_id => p_business_group_id
477 ,p_legislation_code => p_legislation_code
478 ,p_legislation_subgroup => p_legislation_subgroup
479 );
480 ELSE
481 hr_startup_data_api_support.chk_upd_del_startup_action
482 (p_generic_allowed => TRUE
483 ,p_startup_allowed => TRUE
484 ,p_user_allowed => TRUE
485 ,p_business_group_id => p_business_group_id
486 ,p_legislation_code => p_legislation_code
487 ,p_legislation_subgroup => p_legislation_subgroup
488 );
489 END IF;
490 --
491 hr_utility.set_location('Leaving:'||l_proc, 10);
492 END chk_startup_action;
493 -- ----------------------------------------------------------------------------
494 -- |---------------------------< chk_tab_col >----------------------------|
495 -- ----------------------------------------------------------------------------
496 -- {Start of comments}
497 --
498 -- Description:
499 -- This procedure validates the table name passed actually exists
500 -- (EJ:28/4/5) and is in a schema that is allowed to have triggers in it
501 --
502 -- In Parameters:
503 -- A Pl/Sql record structure.
504 --
505 -- Post Success:
506 -- Processing continues.
507 --
508 -- Post Failure:
509 -- Error if column not recognised.
510 --
511 -- Access Status:
512 -- Internal Row Handler Use Only.
513 --
514 -- {End of comments}
515 --
516 Procedure chk_tab_col
517 (p_rec in pay_evq_shd.g_rec_type
518 ) is
519 l_proc varchar2(72) := g_package||'chk_tab_col';
520 l_table varchar2(30);
521 --
522 cursor csr_get_tab is
523 select table_name from pay_dated_tables
524 where dated_table_id = p_rec.dated_table_id;
525 --
526 Begin
527 --
528 hr_utility.set_location('Entering:'||l_proc, 5);
529 --
530 hr_api.mandatory_arg_error
531 (p_api_name => l_proc
532 ,p_argument => 'dated_table_id'
533 ,p_argument_value => p_rec.dated_table_id
534 );
535 --
536 Open csr_get_tab;
537 Fetch csr_get_tab Into l_table;
538 Close csr_get_tab;
539 --
540 If paywsdyg_pkg.is_table_column_valid(l_table,p_rec.column_name) = 'N' Then
541 --
542 -- The combination of table and column does not exist.
543 -- Therefore we must error
544 --
545 fnd_message.set_name('PAY', 'HR_xxxx_INVALID_TABLE_NAME');
546
547 fnd_message.raise_error;
548 End If;
549 --
550 hr_utility.set_location(' Leaving:'||l_proc, 10);
551 end chk_tab_col;
552
553 --
554 -- ----------------------------------------------------------------------------
558 (p_rec in pay_evq_shd.g_rec_type
555 -- |---------------------------< insert_validate >----------------------------|
556 -- ----------------------------------------------------------------------------
557 Procedure insert_validate
559 ,p_effective_date in date
560 ,p_datetrack_mode in varchar2
561 ,p_validation_start_date in date
562 ,p_validation_end_date in date
563 ) is
564 --
565 l_proc varchar2(72) := g_package||'insert_validate';
566 --
567 Begin
568 hr_utility.set_location('Entering:'||l_proc, 5);
569
570 --
571 -- Call all supporting business operations
572 --
573 --
574 if p_rec.business_group_id <> null then
575 chk_startup_action(true
576 ,p_rec.business_group_id
577 ,p_rec.legislation_code
578 );
579 IF hr_startup_data_api_support.g_startup_mode
580 NOT IN ('GENERIC','STARTUP') THEN
581 --
582 -- Validate Important Attributes
583 --
584 hr_api.validate_bus_grp_id
585 (p_business_group_id => p_rec.business_group_id
586 ,p_associated_column1 => pay_evq_shd.g_tab_name
587 || '.BUSINESS_GROUP_ID');
588 end if;
589 -- NOTE, if business_group_id is null then
590 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS
591
592 --
593 -- after validating the set of important attributes,
594 -- if Multiple Message Detection is enabled and at least
595 -- one error has been found then abort further validation.
596 --
597 hr_multi_message.end_validation_set;
598 END IF;
599 --
600 --
601 -- Validate Dependent Attributes
602 --
603 -- Check table and column that this qualifier is associated are valid.
604 chk_tab_col (p_rec => p_rec);
605 --
606 hr_utility.set_location(' Leaving:'||l_proc, 10);
607 End insert_validate;
608 --
609 -- ----------------------------------------------------------------------------
610 -- |---------------------------< update_validate >----------------------------|
611 -- ----------------------------------------------------------------------------
612 Procedure update_validate
613 (p_rec in pay_evq_shd.g_rec_type
614 ,p_effective_date in date
615 ,p_datetrack_mode in varchar2
616 ,p_validation_start_date in date
617 ,p_validation_end_date in date
618 ) is
619 --
620 l_proc varchar2(72) := g_package||'update_validate';
621 --
622 Begin
623 hr_utility.set_location('Entering:'||l_proc, 5);
624 --
625 -- Call all supporting business operations
626 --
627 --
628 if p_rec.business_group_id <> null then
629
630 chk_startup_action(false
631 ,p_rec.business_group_id
632 ,p_rec.legislation_code
633 );
634 IF hr_startup_data_api_support.g_startup_mode
635 NOT IN ('GENERIC','STARTUP') THEN
636 --
637 -- Validate Important Attributes
638 --
639 hr_api.validate_bus_grp_id
640 (p_business_group_id => p_rec.business_group_id
641 ,p_associated_column1 => pay_evq_shd.g_tab_name
642 || '.BUSINESS_GROUP_ID');
643 --
644 -- After validating the set of important attributes,
645 -- if Multiple Message Detection is enabled and at least
646 -- one error has been found then abort further validation.
647 --
648 hr_multi_message.end_validation_set;
649 END IF;
650 end if;
651 --
652 --
653 -- Validate Dependent Attributes
654 --
655 -- Call the datetrack update integrity operation
656 --
657 dt_update_validate
658 (p_datetrack_mode => p_datetrack_mode
659 ,p_validation_start_date => p_validation_start_date
660 ,p_validation_end_date => p_validation_end_date
661 );
662 --
663 chk_non_updateable_args
664 (p_effective_date => p_effective_date
665 ,p_rec => p_rec
666 );
667 --
668 --
669 hr_utility.set_location(' Leaving:'||l_proc, 10);
670 End update_validate;
671 --
672 -- ----------------------------------------------------------------------------
673 -- |---------------------------< delete_validate >----------------------------|
674 -- ----------------------------------------------------------------------------
675 Procedure delete_validate
676 (p_rec in pay_evq_shd.g_rec_type
677 ,p_effective_date in date
678 ,p_datetrack_mode in varchar2
679 ,p_validation_start_date in date
680 ,p_validation_end_date in date
681 ) is
682 --
683 l_proc varchar2(72) := g_package||'delete_validate';
684 --
685 Begin
686 hr_utility.set_location('Entering:'||l_proc, 5);
687 --
688 --
689 chk_startup_action(false
690 ,pay_evq_shd.g_old_rec.business_group_id
691 ,pay_evq_shd.g_old_rec.legislation_code
692 );
693 IF hr_startup_data_api_support.g_startup_mode
694 NOT IN ('GENERIC','STARTUP') THEN
695 --
696 -- Validate Important Attributes
697 --
698 --
699 -- After validating the set of important attributes,
700 -- if Multiple Message Detection is enabled and at least
701 -- one error has been found then abort further validation.
702 --
703 hr_multi_message.end_validation_set;
704 END IF;
705 --
706 -- Call all supporting business operations
707 --
708 dt_delete_validate
709 (p_datetrack_mode => p_datetrack_mode
710 ,p_validation_start_date => p_validation_start_date
711 ,p_validation_end_date => p_validation_end_date
712 ,p_event_qualifier_id => p_rec.event_qualifier_id
713 );
714 --
715 hr_utility.set_location(' Leaving:'||l_proc, 10);
716 End delete_validate;
717 --
718 end pay_evq_bus;
[Home] [Help]