4 -- ----------------------------------------------------------------------------
1 Package Body pay_pbf_bus as
2 /* $Header: pypbfrhi.pkb 120.4 2011/01/25 09:17:04 asnell ship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_pbf_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_balance_feed_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_balance_feed_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,pbg.legislation_code
29 from PER_BUSINESS_GROUPS_PERF pbg
30 , pay_balance_feeds_f pbf
31 where pbf.balance_feed_id = p_balance_feed_id
32 and pbg.business_group_id = pbf.business_group_id;
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_legislation_code varchar2(150);
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 --
40 begin
41 --
42 hr_utility.set_location('Entering:'|| l_proc, 10);
43 --
44 -- Ensure that all the mandatory parameter are not null
45 --
46 hr_api.mandatory_arg_error
47 (p_api_name => l_proc
48 ,p_argument => 'balance_feed_id'
49 ,p_argument_value => p_balance_feed_id
50 );
51 --
52 open csr_sec_grp;
53 fetch csr_sec_grp into l_security_group_id,l_legislation_code;
54 --
55 if csr_sec_grp%notfound then
56 --
57 close csr_sec_grp;
58 --
59 -- The primary key is invalid therefore we must error
60 --
61 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
62 hr_multi_message.add
63 (p_associated_column1
64 => nvl(p_associated_column1,'BALANCE_FEED_ID')
65 );
66 --
67 else
68 close csr_sec_grp;
69 --
70 -- Set the security_group_id in CLIENT_INFO
71 --
72 hr_api.set_security_group_id
73 (p_security_group_id => l_security_group_id
74 );
75
76 -- Set the sessions legislation context in HR_SESSION_DATA
77 --
78 hr_api.set_legislation_context(l_legislation_code);
79
80 end if;
81 --
82 hr_utility.set_location(' Leaving:'|| l_proc, 20);
83 --
84 end set_security_group_id;
85 --
89 --
86 -- ---------------------------------------------------------------------------
87 -- |---------------------< return_legislation_code >-------------------------|
88 -- ---------------------------------------------------------------------------
90 Function return_legislation_code
91 (p_balance_feed_id in number
92 )
93 Return Varchar2 Is
94 --
95 -- Declare cursor
96 --
97 cursor csr_leg_code is
98 select pbg.legislation_code
99 from per_business_groups_perf pbg
100 , pay_balance_feeds_f pbf
101 where pbf.balance_feed_id = p_balance_feed_id
102 and pbg.business_group_id (+) = pbf.business_group_id;
103 --
104 -- Declare local variables
105 --
106 l_legislation_code varchar2(150);
107 l_proc varchar2(72) := g_package||'return_legislation_code';
108 --
109 Begin
110 --
111 hr_utility.set_location('Entering:'|| l_proc, 10);
112 --
113 -- Ensure that all the mandatory parameter are not null
114 --
115 hr_api.mandatory_arg_error
116 (p_api_name => l_proc
117 ,p_argument => 'balance_feed_id'
118 ,p_argument_value => p_balance_feed_id
119 );
120 --
121 if ( nvl(pay_pbf_bus.g_balance_feed_id, hr_api.g_number)
122 = p_balance_feed_id) then
123 --
124 -- The legislation code has already been found with a previous
125 -- call to this function. Just return the value in the global
126 -- variable.
127 --
128 l_legislation_code := pay_pbf_bus.g_legislation_code;
129 hr_utility.set_location(l_proc, 20);
130 else
131 --
132 -- The ID is different to the last call to this function
133 -- or this is the first call to this function.
134 --
135 open csr_leg_code;
136 fetch csr_leg_code into l_legislation_code;
137 --
138 if csr_leg_code%notfound then
139 --
140 -- The primary key is invalid therefore we must error
141 --
142 close csr_leg_code;
143 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
144 fnd_message.raise_error;
145 end if;
146 hr_utility.set_location(l_proc,30);
147 --
148 -- Set the global variables so the values are
149 -- available for the next call to this function.
150 --
151 close csr_leg_code;
152 pay_pbf_bus.g_balance_feed_id := p_balance_feed_id;
153 pay_pbf_bus.g_legislation_code := l_legislation_code;
154 end if;
155 hr_utility.set_location(' Leaving:'|| l_proc, 40);
156 return l_legislation_code;
157 end return_legislation_code;
158 --
159 -- ----------------------------------------------------------------------------
160 -- |-----------------------< chk_non_updateable_args >------------------------|
161 -- ----------------------------------------------------------------------------
162 -- {Start Of Comments}
163 --
164 -- Description:
165 -- This procedure is used to ensure that non updateable attributes have
166 -- not been updated. If an attribute has been updated an error is generated.
167 --
168 -- Pre Conditions:
169 -- g_old_rec has been populated with details of the values currently in
170 -- the database.
171 --
172 -- In Arguments:
173 -- p_rec has been populated with the updated values the user would like the
174 -- record set to.
175 --
176 -- Post Success:
177 -- Processing continues if all the non updateable attributes have not
178 -- changed.
179 --
183 --
180 -- Post Failure:
181 -- An application error is raised if any of the non updatable attributes
182 -- have been altered.
184 -- {End Of Comments}
185 -- ----------------------------------------------------------------------------
186 Procedure chk_non_updateable_args
187 (p_effective_date in date
188 ,p_rec in pay_pbf_shd.g_rec_type
189 ) IS
190 --
191 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
192 l_argument varchar2(30);
193 --
194 Begin
195 --
196 hr_utility.set_location('Entering:'||l_proc, 5);
197
198 -- Only proceed with the validation if a row exists for the current
199 -- record in the HR Schema.
200 --
201 IF NOT pay_pbf_shd.api_updating
202 (p_balance_feed_id => p_rec.balance_feed_id
203 ,p_effective_date => p_effective_date
204 ,p_object_version_number => p_rec.object_version_number
205 ) THEN
206 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
210 END IF;
207 fnd_message.set_token('PROCEDURE ', l_proc);
208 fnd_message.set_token('STEP ', '5');
209 fnd_message.raise_error;
211 --
212 hr_utility.set_location('Entering:'||l_proc, 6);
213
214 if nvl(p_rec.business_group_id, hr_api.g_number) <>
215 nvl(pay_pbf_shd.g_old_rec.business_group_id, hr_api.g_number) then
216 l_argument := 'business_group_id';
217 raise hr_api.argument_changed;
218 end if;
219
220 hr_utility.set_location('Entering:'||l_proc, 7);
221 if nvl(p_rec.input_value_id, hr_api.g_number) <>
222 nvl(pay_pbf_shd.g_old_rec.input_value_id, hr_api.g_number) then
223 l_argument := 'input_value_id';
224 raise hr_api.argument_changed;
225 end if;
226
227 hr_utility.set_location('Entering:'||l_proc, 8);
228 if nvl(p_rec.balance_type_id, hr_api.g_number) <>
229 nvl(pay_pbf_shd.g_old_rec.balance_type_id, hr_api.g_number) then
230 l_argument := 'balance_type_id';
231 raise hr_api.argument_changed;
232 end if;
233
234 hr_utility.set_location('Entering:'||l_proc, 9);
235 if nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
236 nvl(pay_pbf_shd.g_old_rec.legislation_code, hr_api.g_varchar2) then
237 l_argument := 'legislation_code';
238 raise hr_api.argument_changed;
239 end if;
240
241 hr_utility.set_location('Entering:'||l_proc, 9);
242 if nvl(p_rec.legislation_subgroup, hr_api.g_varchar2) <>
243 nvl(pay_pbf_shd.g_old_rec.legislation_subgroup, hr_api.g_varchar2) then
244 l_argument := 'legislation_subgroup';
245 raise hr_api.argument_changed;
246 end if;
247
248 hr_utility.set_location(' Leaving:'||l_proc, 10);
249
250 Exception
251 When hr_api.argument_changed Then
252 -- A non updatetable attribute has been changed therefore we
253 -- must report this error
254 hr_api.argument_changed_error
255 (p_api_name => l_proc
256 ,p_argument => l_argument
257 );
258
259 --
260 End chk_non_updateable_args;
261 --
262 -- ----------------------------------------------------------------------------
263 -- |--------------------------< dt_update_validate >--------------------------|
264 -- ----------------------------------------------------------------------------
265 -- {Start Of Comments}
266 --
267 -- Description:
268 -- This procedure is used for referential integrity of datetracked
269 -- parent entities when a datetrack update operation is taking place
270 -- and where there is no cascading of update defined for this entity.
271 --
272 -- Prerequisites:
273 -- This procedure is called from the update_validate.
274 --
275 -- In Parameters:
276 --
277 -- Post Success:
278 -- Processing continues.
279 --
280 -- Post Failure:
281 --
282 -- Developer Implementation Notes:
283 -- This procedure should not need maintenance unless the HR Schema model
284 -- changes.
285 --
286 -- Access Status:
287 -- Internal Row Handler Use Only.
288 --
289 -- {End Of Comments}
290 -- ----------------------------------------------------------------------------
291 Procedure dt_update_validate
292 (p_datetrack_mode in varchar2
293 ,p_validation_start_date in date
294 ,p_validation_end_date in date
295 ) Is
296 --
297 l_proc varchar2(72) := g_package||'dt_update_validate';
298 --
299 Begin
300 --
301 -- Ensure that the p_datetrack_mode argument is not null
302 --
303 hr_api.mandatory_arg_error
304 (p_api_name => l_proc
305 ,p_argument => 'datetrack_mode'
306 ,p_argument_value => p_datetrack_mode
307 );
308 --
309 -- Mode will be valid, as this is checked at the start of the upd.
310 --
311 -- Ensure the arguments are not null
312 --
313 hr_api.mandatory_arg_error
314 (p_api_name => l_proc
315 ,p_argument => 'validation_start_date'
316 ,p_argument_value => p_validation_start_date
317 );
318 --
319 hr_api.mandatory_arg_error
320 (p_api_name => l_proc
321 ,p_argument => 'validation_end_date'
322 ,p_argument_value => p_validation_end_date
323 );
324 --
325 --
326 --
327 Exception
328 When Others Then
329 --
330 -- An unhandled or unexpected error has occurred which
331 -- we must report
332 --
333 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
334 fnd_message.set_token('PROCEDURE', l_proc);
335 fnd_message.set_token('STEP','15');
336 fnd_message.raise_error;
337 End dt_update_validate;
338 --
339 -- ----------------------------------------------------------------------------
340 -- |--------------------------< dt_delete_validate >--------------------------|
341 -- ----------------------------------------------------------------------------
342 -- {Start Of Comments}
343 --
344 -- Description:
345 -- This procedure is used for referential integrity of datetracked
346 -- child entities when either a datetrack DELETE or ZAP is in operation
347 -- and where there is no cascading of delete defined for this entity.
348 -- For the datetrack mode of DELETE or ZAP we must ensure that no
349 -- datetracked child rows exist between the validation start and end
350 -- dates.
351 --
352 -- Prerequisites:
356 --
353 -- This procedure is called from the delete_validate.
354 --
355 -- In Parameters:
360 -- Post Failure:
357 -- Post Success:
358 -- Processing continues.
359 --
361 -- If a row exists by determining the returning Boolean value from the
362 -- generic dt_api.rows_exist function then we must supply an error via
363 -- the use of the local exception handler l_rows_exist.
364 --
365 -- Developer Implementation Notes:
366 -- This procedure should not need maintenance unless the HR Schema model
367 -- changes.
368 --
369 -- Access Status:
370 -- Internal Row Handler Use Only.
371 --
372 -- {End Of Comments}
373 -- ----------------------------------------------------------------------------
374 Procedure dt_delete_validate
375 (p_balance_feed_id in number
376 ,p_datetrack_mode in varchar2
377 ,p_validation_start_date in date
378 ,p_validation_end_date in date
379 ) Is
380 --
381 l_proc varchar2(72) := g_package||'dt_delete_validate';
382 --
383 Begin
384 --
385 -- Ensure that the p_datetrack_mode argument is not null
386 --
387 hr_api.mandatory_arg_error
388 (p_api_name => l_proc
389 ,p_argument => 'datetrack_mode'
390 ,p_argument_value => p_datetrack_mode
391 );
392 --
393 -- Only perform the validation if the datetrack mode is either
394 -- DELETE or ZAP
395 --
396 If (p_datetrack_mode = hr_api.g_delete or
397 p_datetrack_mode = hr_api.g_zap) then
398 --
399 --
400 -- Ensure the arguments are not null
401 --
402 hr_api.mandatory_arg_error
403 (p_api_name => l_proc
404 ,p_argument => 'validation_start_date'
405 ,p_argument_value => p_validation_start_date
406 );
407 --
408 hr_api.mandatory_arg_error
409 (p_api_name => l_proc
410 ,p_argument => 'validation_end_date'
411 ,p_argument_value => p_validation_end_date
412 );
413 --
414 hr_api.mandatory_arg_error
415 (p_api_name => l_proc
416 ,p_argument => 'balance_feed_id'
417 ,p_argument_value => p_balance_feed_id
418 );
419 --
420 --
421 --
422 End If;
423 --
424 Exception
425 When Others Then
426 --
427 -- An unhandled or unexpected error has occurred which
428 -- we must report
429 --
430 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
431 fnd_message.set_token('PROCEDURE', l_proc);
432 fnd_message.set_token('STEP','15');
433 fnd_message.raise_error;
434 --
435 End dt_delete_validate;
436 --
437 -- ----------------------------------------------------------------------------
438 -- |----------------------< chk_startup_action >------------------------------|
439 -- ----------------------------------------------------------------------------
440 --
441 -- Description:
442 -- This procedure will check that the current action is allowed according
443 -- to the current startup mode.
444 --
445 -- ----------------------------------------------------------------------------
446 PROCEDURE chk_startup_action
447 (p_insert IN boolean
448 ,p_business_group_id IN number
449 ,p_legislation_code IN varchar2
450 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
451 --
452 BEGIN
453 --
454 -- Call the supporting procedure to check startup mode
455 -- EDIT_HERE: The following call should be edited if certain types of rows
456 -- are not permitted.
457 IF (p_insert) THEN
458 hr_startup_data_api_support.chk_startup_action
459 (p_generic_allowed => FALSE
460 ,p_startup_allowed => TRUE
461 ,p_user_allowed => TRUE
462 ,p_business_group_id => p_business_group_id
463 ,p_legislation_code => p_legislation_code
464 ,p_legislation_subgroup => p_legislation_subgroup
465 );
466 ELSE
467 hr_startup_data_api_support.chk_upd_del_startup_action
468 (p_generic_allowed => FALSE
469 ,p_startup_allowed => TRUE
470 ,p_user_allowed => TRUE
471 ,p_business_group_id => p_business_group_id
472 ,p_legislation_code => p_legislation_code
473 ,p_legislation_subgroup => p_legislation_subgroup
474 );
475 END IF;
476 --
477 END chk_startup_action;
478 --
479 --
480 -- check procedures
484 --
481 -- ----------------------------------------------------------------------------
482 -- |------------------------< chk_business_group_id >-------------------------|
483 -- ----------------------------------------------------------------------------
485 -- Description:
486 -- This procedure is used to validate the business group id against the
487 -- parent table
488 --
489 -- ----------------------------------------------------------------------------
490 Procedure chk_business_group_id
491 (p_business_group_id in number
492 ,p_input_value_id in number
493 ,p_effective_date in date
494 ) is
495 --
496 l_proc varchar2(72) := g_package||'chk_business_group_id';
497 l_exists varchar2(1);
498 l_balance_init_flag number := 0;
499
500 Cursor c_chk_bg_id
501 is
502 select '1'
503 from hr_organization_units
504 where business_group_id = p_business_group_id;
505 -- and ( l_balance_init_flag = 1 or
506 -- ( p_effective_date between date_from
507 -- and nvl(date_to, hr_api.g_eot) ) );
508 --
509 Begin
510 hr_utility.set_location('Entering:'||l_proc, 5);
511 --
512
513 --
514 hr_api.mandatory_arg_error
515 (p_api_name => l_proc
516 ,p_argument => 'input_value_id'
517 ,p_argument_value => p_input_value_id
518 );
519 --
520
521 if get_balance_init_flag ( p_input_value_id => p_input_value_id ) then
522 l_balance_init_flag := 1;
523 end if;
524
525 If p_business_group_id is not null then
526 Open c_chk_bg_id;
527 Fetch c_chk_bg_id into l_exists;
528 If c_chk_bg_id%notfound Then
529 --
530 Close c_chk_bg_id;
531 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
532 fnd_message.set_token('COLUMN','BUSINESS_GROUP_ID');
533 fnd_message.set_token('TABLE','HR_ORGANIZATION_UNITS');
534 fnd_message.raise_error;
535 --
536 End If;
537 Close c_chk_bg_id;
538 End If;
539 --
543 -- ----------------------------------------------------------------------------
540 hr_utility.set_location('Leaving:'||l_proc, 10);
541 End;
542 --
544 -- |------------------------< chk_legislation_code >--------------------------|
545 -- ----------------------------------------------------------------------------
546 --
547 -- Description:
548 -- This procedure is used to validate the legislation code against the
549 -- parent table
550 --
551 -- ----------------------------------------------------------------------------
552 Procedure chk_legislation_code
553 (p_legislation_code in varchar2)
554 is
555 --
556 l_proc varchar2(72) := g_package||'chk_legislation_code';
557 l_exists varchar2(1);
558 Cursor c_chk_leg_code
559 is
560 select '1'
561 from fnd_territories
562 where territory_code = p_legislation_code;
563 --
564 Begin
565 hr_utility.set_location('Entering:'||l_proc, 5);
566 --
567 If p_legislation_code is not null then
568
569 Open c_chk_leg_code;
570 Fetch c_chk_leg_code into l_exists;
571 If c_chk_leg_code%notfound Then
572 --
573 Close c_chk_leg_code;
574 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
575 fnd_message.set_token('COLUMN','LEGISLATION_CODE');
576 fnd_message.set_token('TABLE','FND_TERRITORIES');
580 Close c_chk_leg_code;
577 fnd_message.raise_error;
578 --
579 End If;
581
582 End If;
583 --
584 hr_utility.set_location('Leaving:'||l_proc, 10);
585 End;
586 --
587 -- ----------------------------------------------------------------------------
588 -- |------------------------< chk_input_value_id >----------------------------|
589 -- ----------------------------------------------------------------------------
590 --
591 -- Description:
592 -- This procedure is used to validate the input value id against the
593 -- parent table
594 --
595 -- ----------------------------------------------------------------------------
596 Procedure chk_input_value_id
597 (p_input_value_id in number
598 ,p_business_group_id in pay_balance_feeds_f.business_group_id%type
599 ,p_legislation_code in pay_balance_feeds_f.legislation_code%type
600 ) is
601 --
602 l_proc varchar2(72) := g_package||'chk_input_value_id';
603 l_exists varchar2(1);
604 l_legislation_code per_business_groups.legislation_code%type;
605
606 cursor csr_ipv_id is
607 select '1'
608 from pay_input_values_f
609 where input_value_id = p_input_value_id
610 and nvl(legislation_code, nvl(l_legislation_code, hr_api.g_varchar2))
611 = nvl(l_legislation_code, hr_api.g_varchar2)
612 and nvl(business_group_id, nvl(p_business_group_id, hr_api.g_number))
613 = nvl(p_business_group_id, hr_api.g_number);
614
615 cursor csr_ipv_multiple_feeds is
616 select null
617 from pay_balance_feeds_f pbf
618 where pbf.input_value_id = p_input_value_id;
619 --
620 Begin
621 hr_utility.set_location('Entering:'||l_proc, 5);
622 --
623
624 l_legislation_code := nvl(p_legislation_code
625 ,hr_api.return_legislation_code
626 (p_business_group_id));
627
628 Open csr_ipv_id;
629 Fetch csr_ipv_id into l_exists;
630 If csr_ipv_id%notfound Then
631 --
632 Close csr_ipv_id;
633 fnd_message.set_name('PAY','PAY_34154_LEG_BUS_MISMATCH');
634 fnd_message.set_token('ENTITY1','INITIAL BALANCE FEED');
635 fnd_message.set_token('ENTITY2','INPUT VALUE');
636 fnd_message.raise_error;
637 --
638 End If;
639 Close csr_ipv_id;
640
641 -- no other balance feed must be created with the input value when creating
642 -- initial balance feed.
643
644 if get_balance_init_flag (p_input_value_id => p_input_value_id) then
645
646 open csr_ipv_multiple_feeds;
647 fetch csr_ipv_multiple_feeds into l_exists;
648
649 if csr_ipv_multiple_feeds%found then
650
651 close csr_ipv_multiple_feeds;
652 fnd_message.set_name('PAY','PAY_33245_BIFEED_IPV_MUL_FEEDS');
653 fnd_message.raise_error;
654
655 end if;
656
657 close csr_ipv_multiple_feeds;
658
659 end if;
660
661 --
662 hr_utility.set_location('Leaving:'||l_proc, 10);
663 End;
664 --
665 -- ----------------------------------------------------------------------------
666 -- |------------------------< chk_balance_type_id >-------------------------|
667 -- ----------------------------------------------------------------------------
668 --
669 -- Description:
670 -- This procedure is used to validate the balance type id against the
671 -- parent table
672 --
673 -- ----------------------------------------------------------------------------
674 Procedure chk_balance_type_id
675 (p_balance_type_id in number
676 ,p_business_group_id in pay_balance_feeds_f.business_group_id%type
677 ,p_legislation_code in pay_balance_feeds_f.legislation_code%type
678 ) is
679 --
680 l_proc varchar2(72) := g_package||'chk_balance_type_id';
681 l_exists varchar2(1);
682 l_legislation_code per_business_groups.legislation_code%type;
683
684 cursor csr_bal_type_id is
685 select '1'
686 from pay_balance_types
687 where balance_type_id = p_balance_type_id
688 and nvl(legislation_code, nvl(l_legislation_code, hr_api.g_varchar2))
689 = nvl(l_legislation_code, hr_api.g_varchar2)
690 and nvl(business_group_id, nvl(p_business_group_id, hr_api.g_number))
691 = nvl(p_business_group_id, hr_api.g_number);
692
693 --
694 Begin
695 hr_utility.set_location('Entering:'||l_proc, 5);
696 --
697
698 l_legislation_code := nvl(p_legislation_code
699 ,hr_api.return_legislation_code
700 (p_business_group_id));
701
702 Open csr_bal_type_id;
703 Fetch csr_bal_type_id into l_exists;
704 If csr_bal_type_id%notfound Then
705 --
706 Close csr_bal_type_id;
707 fnd_message.set_name('PAY','PAY_34154_LEG_BUS_MISMATCH');
708 fnd_message.set_token('ENTITY1','INITIAL BALANCE FEED');
709 fnd_message.set_token('ENTITY2','BALANCE');
710 fnd_message.raise_error;
711 --
712 End If;
713 Close csr_bal_type_id;
714 --
715 hr_utility.set_location('Leaving:'||l_proc, 10);
716 End;
717 --
721 -- {Start Of Comments}
718 -- ----------------------------------------------------------------------------
719 -- |-----------------< chk_bal_class_exists >---------------------------------|
720 -- ----------------------------------------------------------------------------
722 --
723 -- Description:
724 -- This procedure is used to ensure that for a balance_type_id if there
725 -- already exists a balance classification that feeds the balance then the
726 -- insert/update/delete balance feed operation cannot be performed.
727 --
728 -- Bug 10427303
729 -- This procedure was initially not allowing creation of initial balance feeds
730 -- for balances which have balance classifications. Hence added the
731 -- input_value_id argument to this procedure, to allow creation of
732 -- Initial Balance feeds even though the balance is fed by classifications.
733 --
734 -- In Arguments:
735 -- The balance type id for which feed is being created/updated/deleted
736 -- The Input Value id for which feed is being created/updated/deleted #10427303
737 --
738 -- Post Success:
739 -- Processing continues and balance feed is inserted/updated/deleted
740 --
741 -- Post Failure:
745 -- {End Of Comments}
742 -- An application error is raised if a balance classification already exists
743 -- for the balance to which feed is being created/updated/deleted.
744 --
746
747 procedure chk_bal_class_exists
748 (p_balance_type_id number,
749 p_input_value_id number) is --Bug 10427303
750 --
751 l_proc varchar2(72) := g_package||'chk_bal_class_exists';
752
753 cursor csr_classifications_exist is
754 select bcl.classification_id
755 from pay_balance_classifications bcl
756 where bcl.balance_type_id = p_balance_type_id;
757
758 --
759 l_allow_adjust_feed boolean;
760 l_classification_id number;
761 --
762 begin
763
764 hr_utility.set_location('Entering:'||l_proc, 5);
765 --
766 l_allow_adjust_feed := False;
767 if hr_startup_data_api_support.return_startup_mode
768 IN ('STARTUP') then -- {startupMode
769 -- in startup node allow adjustment feeds to classification balance
770 if not pay_pbf_bus.is_pay_value(p_input_value_id) then
771 l_allow_adjust_feed := True;
772 end if;
773 end if; -- }startupMode
774
775 open csr_classifications_exist;
776 fetch csr_classifications_exist into l_classification_id;
777 if csr_classifications_exist%found then
778 close csr_classifications_exist;
779 if not (get_balance_init_flag(p_input_value_id))
780 and not l_allow_adjust_feed then --Bug # 10427303/ 10352801
781 fnd_message.set_name('PAY','HR_7444_BAL_FEED_READ_ONLY');
782 fnd_message.raise_error;
783 end if;
784 else
785 close csr_classifications_exist;
786 end if;
787 --
788 hr_utility.set_location(' Leaving:'||l_proc, 10);
789 --
790 end chk_bal_class_exists;
791 --
792 -- ---------------------------------------------------------------------------
793 -- |----------------------< chk_initial_feed_exists >-------------------------|
794 -- ----------------------------------------------------------------------------
795 -- {Start Of Comments}
796 --
797 -- Description:
798 -- This procedure is used to ensure that when creating a feed for a
799 -- balance, no initial balance feed already exists for that balance
800 --
801 -- In Arguments:
802 -- The balance type id for which feed is being created
803 --
804 -- Post Success:
805 -- Processing continues and balance feed is inserted
806 --
807 -- Post Failure:
808 -- An application error is raised if an initial feed already exists
809 -- for the balance to which feed is being created.
810 --
811 -- {End Of Comments}
812
813 procedure chk_initial_feed_exists
814 (p_balance_type_id in number
815 ,p_business_group_id in pay_balance_feeds_f.business_group_id%type
816 ,p_legislation_code in pay_balance_feeds_f.legislation_code%type
817 ) is
818 --
819 l_proc varchar2(72) := g_package||'chk_initial_feed_exists';
820 l_legislation_code per_business_groups.legislation_code%type;
821
822 CURSOR csr_initial_feed_exist IS
823 SELECT 1
824 FROM pay_balance_feeds_f blf,
825 pay_input_values_f inv,
826 pay_element_types_f elt,
827 pay_element_classifications ec
828 WHERE blf.balance_type_id = p_balance_type_id
829 AND blf.input_value_id = inv.input_value_id
830 AND inv.element_type_id = elt.element_type_id
831 AND nvl(elt.legislation_code
832 ,nvl(l_legislation_code, '~nvl~'))
833 = nvl(l_legislation_code, '~nvl~')
834 AND nvl(elt.business_group_id
835 ,nvl(p_business_group_id, -1))
836 = nvl(p_business_group_id, -1)
837 AND elt.classification_id = ec.classification_id
838 AND ec.balance_initialization_flag ='Y';
839
840 --
841 l_balance_initialization_flag varchar2(30);
842 --
843 begin
844
845 hr_utility.set_location('Entering:'||l_proc, 5);
846 --
847
848 l_legislation_code := nvl(p_legislation_code
849 ,hr_api.return_legislation_code
850 (p_business_group_id));
851
852 open csr_initial_feed_exist;
853 fetch csr_initial_feed_exist into l_balance_initialization_flag;
854 if csr_initial_feed_exist%found then
855 close csr_initial_feed_exist;
856 fnd_message.set_name('PAY','HR_7875_BAL_FEED_HAS_INIT_FEED');
857 fnd_message.raise_error;
858 else
859 close csr_initial_feed_exist;
860 end if;
861 --
862 hr_utility.set_location(' Leaving:'||l_proc, 10);
863 --
864 end chk_initial_feed_exists;
865 --
866 -- ---------------------------------------------------------------------------
867 -- |-------------------------------< chk_uom >--------------------------------|
868 -- ----------------------------------------------------------------------------
869 -- {Start Of Comments}
870 --
871 -- Description:
872 -- This procedure is used to ensure the UOM of the input value and the balance
873 -- must match ie. be in the same class
874 --
875 -- In Arguments:
876 -- The input value id and the balance type id for which feed is being created
877 --
878 -- Post Success:
879 -- Processing continues and balance feed is created.
880 --
881 -- Post Failure:
882 -- An application error is raised if the uom of the balance for which feed is
883 -- being created and that of the input value do not match.
884 --
885 -- {End Of Comments}
886 procedure chk_uom
887 (p_input_value_id number
891 l_proc varchar2(72) := g_package||'chk_uom';
888 ,p_balance_type_id number
889 ,p_effective_date date) is
890 --
892
893 CURSOR csr_bal_uom IS
894 SELECT pbt.balance_uom
895 FROM pay_balance_types pbt
896 WHERE pbt.balance_type_id = p_balance_type_id;
897 --
898 CURSOR csr_ipv_uom IS
899 SELECT piv.uom
900 FROM pay_input_values_f piv
901 WHERE piv.input_value_id = p_input_value_id
902 AND p_effective_date between piv.effective_start_date
903 AND piv.effective_end_date;
904 --
905 l_bal_uom varchar2(30);
906 l_ipv_uom varchar2(30);
907 --
908 begin
909
910 hr_utility.set_location('Entering:'||l_proc, 5);
911 --
912 open csr_bal_uom;
913 fetch csr_bal_uom into l_bal_uom;
914 close csr_bal_uom;
915 --
916 open csr_ipv_uom;
917 fetch csr_ipv_uom into l_ipv_uom;
918 close csr_ipv_uom;
919 --
920 if substr(l_bal_uom,1,1) <> substr(l_ipv_uom,1,1) then
921 fnd_message.set_name('PAY','HR_6553_BAL_WRONG_UOM');
922 fnd_message.raise_error;
923 end if;
924 --
925 hr_utility.set_location(' Leaving:'||l_proc, 10);
926 --
927 end chk_uom;
928 -- ---------------------------------------------------------------------------
929 -- |--------------------------< chk_currency_match >--------------------------|
930 -- ----------------------------------------------------------------------------
931 -- {Start Of Comments}
932 --
933 -- Description:
934 -- This procedure is used to ensure the output currency code of the input
935 -- value that feeds the balance and the currency code of the balance must
936 -- match if the UOM of the balance is of type 'Money'
937 --
938 -- In Arguments:
939 -- The input value id and the balance type id for which feed is being created
940 --
941 -- Post Success:
942 -- Processing continues and balance feed is created.
943 --
944 -- Post Failure:
945 -- An application error is raised if the currency codes do not match
946 --
947 -- {End Of Comments}
948
949 procedure chk_currency_match
950 (p_input_value_id number
951 ,p_balance_type_id number) is
952 --
953 l_proc varchar2(72) := g_package||'chk_currency_match';
954
955 CURSOR csr_balance_uom IS
956 SELECT balance_uom
957 FROM pay_balance_types
958 WHERE balance_type_id = p_balance_type_id;
959
960 CURSOR csr_currency_match IS
961 SELECT 1
962 FROM pay_balance_types pbt,
963 pay_input_values_f piv,
964 pay_element_types_f pet
965 WHERE pbt.balance_type_id = p_balance_type_id
966 AND piv.input_value_id = p_input_value_id
967 AND pet.element_type_id = piv.element_type_id
968 AND pbt.currency_code = pet.output_currency_code;
969
970 --
971 l_exists number;
972 l_uom varchar2(30);
973 --
974 begin
975
976 hr_utility.set_location('Entering:'||l_proc, 5);
977 --
978 open csr_balance_uom;
979 fetch csr_balance_uom into l_uom;
980 if csr_balance_uom%found then
981 close csr_balance_uom;
982 if (l_uom = 'M') then
983 open csr_currency_match;
984 fetch csr_currency_match into l_exists;
985 if csr_currency_match%notfound then
986 close csr_currency_match;
987 fnd_message.set_name('PAY','PAY_33246_BF_CUR_MISMATCH');
988 fnd_message.raise_error;
989 else
990 close csr_currency_match;
991 end if;
992 end if;
993 else
994 close csr_balance_uom;
995 end if;
996 --
997 hr_utility.set_location(' Leaving:'||l_proc, 10);
998
999 end chk_currency_match;
1000 --
1001 -- ---------------------------------------------------------------------------
1002 -- |----------------------------< chk_scale_value >---------------------------|
1003 -- ----------------------------------------------------------------------------
1004 -- {Start Of Comments}
1005 --
1006 -- Description:
1007 -- This procedure is used to ensure that valid value for Scale is entered for
1008 -- the feed that is being created.
1009 --
1010 -- In Arguments:
1011 -- The scale and the effective date on which feed is created or updated.
1012 --
1013 -- Post Success:
1014 -- Processing continues and balance feed is created.
1015 --
1016 -- Post Failure:
1017 -- An application error is raised if the value for scale does not exist in
1018 -- hr_lookups table for lookup_type 'ADD_SUBTRACT'
1019 --
1020 -- {End Of Comments}
1021
1022 procedure chk_scale_value
1023 (p_scale number
1024 ,p_input_value_id number
1025 ,p_effective_date date) is
1026 --
1027 l_proc varchar2(72) := g_package||'chk_scale_value';
1028
1029 --
1030 l_exists number;
1031 --
1032 begin
1033
1034 hr_utility.set_location('Entering:'||l_proc, 5);
1035 --
1036 if hr_api.NOT_EXISTS_IN_HR_LOOKUPS
1037 (p_effective_date => p_effective_date
1038 ,p_lookup_type => 'ADD_SUBTRACT'
1039 ,p_lookup_code => p_scale
1040 )
1041 then
1042 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1043 fnd_message.raise_error;
1044 end if;
1045
1046 --
1047 hr_api.mandatory_arg_error
1048 (p_api_name => l_proc
1049 ,p_argument => 'input_value_id'
1050 ,p_argument_value => p_input_value_id
1051 );
1052 --
1053
1057 fnd_message.set_name('PAY','PAY_33242_BIFEED_INV_SCALE');
1054 -- Scale value must only be 1 when initial balance feed is being created
1055
1056 if get_balance_init_flag (p_input_value_id => p_input_value_id) and p_scale <> 1 then
1058 fnd_message.raise_error;
1059 end if;
1060 --
1061 hr_utility.set_location(' Leaving:'||l_proc, 10);
1062
1063 end chk_scale_value;
1064 --
1065 -- ---------------------------------------------------------------------------
1066 -- |--------------------------< chk_bal_feed_unique >-------------------------|
1067 -- ----------------------------------------------------------------------------
1068 -- {Start Of Comments}
1069 --
1070 -- Description:
1071 -- This procedure is used to ensure that balance feeds are date-effectively
1072 -- unique ie. an input value cannot have two balance feeds to the same balance
1073 -- type at the same point in (date-effective) time
1074 --
1075 -- In Arguments:
1076 -- The balance_type_id, input_value_id, effective_start_date and
1077 -- effective_end_date on which feed is being created.
1078 --
1079 -- Post Success:
1080 -- Processing continues and balance feed is created.
1081 --
1082 -- Post Failure:
1083 -- An application error is raised if balance feed already exists for the input
1084 -- value id in the same date-effective time.
1085 --
1086 -- {End Of Comments}
1087
1088 procedure chk_bal_feed_unique
1089 (p_input_value_id number
1090 ,p_balance_type_id number
1091 ,p_effective_date date) is
1092 --
1093 l_proc varchar2(72) := g_package||'chk_bal_feed_unique';
1094
1095 CURSOR csr_bal_feed_unique IS
1096 SELECT 1
1097 FROM pay_balance_feeds_f
1098 WHERE input_value_id = p_input_value_id
1099 AND balance_type_id =p_balance_type_id
1100 AND p_effective_date between effective_start_date and effective_end_date;
1101
1102 --
1103 l_exists number;
1104 --
1105 begin
1106
1107 hr_utility.set_location('Entering:'||l_proc, 5);
1108 --
1109 open csr_bal_feed_unique;
1110 fetch csr_bal_feed_unique into l_exists;
1111 if csr_bal_feed_unique%found then
1112 close csr_bal_feed_unique;
1113 fnd_message.set_name('PAY','HR_6109_BAL_UNI_FEED');
1114 fnd_message.raise_error;
1115 else
1116 close csr_bal_feed_unique;
1117 end if;
1118 --
1119 hr_utility.set_location(' Leaving:'||l_proc, 10);
1120
1121 end chk_bal_feed_unique;
1122 --
1123 --
1124 -- ---------------------------------------------------------------------------
1125 -- |--------------------------< chk_feed_ipv_life >---------------------------|
1126 -- ----------------------------------------------------------------------------
1127 -- {Start Of Comments}
1128 --
1129 -- Description:
1130 -- This procedure is used to ensure that balance feeds cannot be created
1131 -- outside the life-time of the input value that feeds the balance.
1132 --
1133 -- In Arguments:
1134 -- The input_value_id, and the effective_date on which feed is being created.
1135 --
1136 -- Post Success:
1137 -- Processing continues and balance feed is created.
1138 --
1139 -- Post Failure:
1140 -- An application error is raised if balance feed being created is outside the
1141 -- the life-time of the input value that feeds the balance.
1142 --
1143 -- {End Of Comments}
1144
1145 procedure chk_feed_ipv_life
1146 (p_input_value_id number
1147 ,p_effective_date date) is
1148 --
1149 l_proc varchar2(72) := g_package||'chk_feed_ipv_life';
1150
1151 CURSOR csr_feed_ipv_value IS
1152 SELECT 1
1153 FROM pay_input_values_f
1154 WHERE input_value_id = p_input_value_id
1155 AND p_effective_date between effective_start_date and effective_end_date;
1156 --
1157 l_exists number;
1158 --
1159 begin
1160
1161 hr_utility.set_location('Entering:'||l_proc, 5);
1162 --
1163 open csr_feed_ipv_value;
1164 fetch csr_feed_ipv_value into l_exists;
1165
1166 if(csr_feed_ipv_value%notfound) then
1167 close csr_feed_ipv_value;
1168 --raise error since balance feed is outside the life-time of the input value
1169 fnd_message.set_name('PAY','HR_7048_BAL_FEED_PAST_INP_VAL');
1170 fnd_message.raise_error;
1171 else
1172 close csr_feed_ipv_value;
1173 end if;
1174 --
1175 hr_utility.set_location(' Leaving:'||l_proc, 10);
1176
1177 end chk_feed_ipv_life;
1178 --
1179 -- ---------------------------------------------------------------------------
1180 -- |--------------------------< chk_dtupd_allowed >--------------------------|
1181 -- ---------------------------------------------------------------------------
1182 -- {Start Of Comments}
1183 --
1184 -- Description:
1185 -- This procedure is used to ensure that date effective update is not
1186 -- allowed for initial balance feeds.
1187 --
1188 -- In Arguments:
1189 -- Date track mode for the operation
1190 --
1191 -- Post Success:
1192 -- Processing continues and balance feed is updated.
1193 --
1194 -- Post Failure:
1195 -- An error is raised if date effective update is attempted on
1196 -- an initial balance feed.
1197 --
1198 -- {End Of Comments}
1199
1200 procedure chk_dtupd_allowed
1201 (p_datetrack_mode in varchar2
1202 ,p_input_value_id in number
1203 ) is
1204 --
1205 l_proc varchar2(72) := g_package||'chk_dtupd_allowed';
1209 hr_utility.set_location('Entering:'||l_proc, 5);
1206 --
1207 begin
1208
1210 --
1211
1212 --
1213 hr_api.mandatory_arg_error
1214 (p_api_name => l_proc
1215 ,p_argument => 'input_value_id'
1216 ,p_argument_value => p_input_value_id
1217 );
1218 --
1219
1220 -- Date effective update must not be allowed for the initial balance feed.
1221 --
1222
1223 if get_balance_init_flag (p_input_value_id => p_input_value_id) and
1224 p_datetrack_mode <> hr_api.g_correction then
1225
1226 fnd_message.set_name('PAY','PAY_33244_BIFEED_INV_ACTION');
1227 fnd_message.set_token('ACTION', 'UPDATE');
1228 fnd_message.set_token('MODE', p_datetrack_mode);
1229 fnd_message.raise_error;
1230
1231 end if;
1232 --
1233 hr_utility.set_location(' Leaving:'||l_proc, 10);
1234
1235 end chk_dtupd_allowed;
1236 --
1237 -- ---------------------------------------------------------------------------
1238 -- |--------------------------< chk_dtdel_allowed >--------------------------|
1239 -- ---------------------------------------------------------------------------
1240 -- {Start Of Comments}
1241 --
1242 -- Description:
1243 -- This procedure is used to ensure that date effective delete is not
1244 -- allowed for initial balance feeds.
1245 --
1246 -- In Arguments:
1247 -- Date track mode for the operation
1248 --
1249 -- Post Success:
1250 -- Processing continues and balance feed is deleted.
1251 --
1252 -- Post Failure:
1253 -- An error is raised if date effective delete is attempted on
1254 -- an initial balance feed.
1255 --
1256 -- {End Of Comments}
1257
1258 procedure chk_dtdel_allowed
1259 (p_datetrack_mode in varchar2
1263 l_proc varchar2(72) := g_package||'chk_dtdel_allowed';
1260 ,p_input_value_id in number
1261 ) is
1262 --
1264 --
1265 begin
1266
1267 hr_utility.set_location('Entering:'||l_proc, 5);
1268 --
1269
1270 --
1271 hr_api.mandatory_arg_error
1272 (p_api_name => l_proc
1273 ,p_argument => 'input_value_id'
1274 ,p_argument_value => p_input_value_id
1275 );
1276 --
1277
1278 --
1279 -- Date effective delete must not be allowed for the initial balance feed.
1280 --
1281
1282 if get_balance_init_flag( p_input_value_id => p_input_value_id)
1283 and p_datetrack_mode <> hr_api.g_zap then
1284
1285 fnd_message.set_name('PAY','PAY_33244_BIFEED_INV_ACTION');
1286 fnd_message.set_token('ACTION', 'DELETE');
1287 fnd_message.set_token('MODE', p_datetrack_mode);
1288 fnd_message.raise_error;
1289
1290 end if;
1291 --
1292 hr_utility.set_location(' Leaving:'||l_proc, 10);
1293
1294 end chk_dtdel_allowed;
1295 --
1296 -- ----------------------------------------------------------------------------
1297 -- |---------------------------< insert_validate >----------------------------|
1298 -- ----------------------------------------------------------------------------
1299 Procedure insert_validate
1300 (p_rec in pay_pbf_shd.g_rec_type
1301 ,p_effective_date in date
1302 ,p_datetrack_mode in varchar2
1303 ,p_validation_start_date in date
1304 ,p_validation_end_date in date
1305 ,p_initial_feed in boolean
1306 ,p_exist_run_result_warning out nocopy boolean
1307 ) is
1308 --
1309 l_proc varchar2(72) := g_package||'insert_validate';
1310 l_validation_end_date date;
1311 --
1312 Begin
1313 hr_utility.set_location('Entering:'||l_proc, 5);
1314
1315 -- Clearing the Global variable since the record may have been changed.
1316 --
1317 g_balance_init_flag := NULL;
1318 --
1319
1320 --
1321 -- Call all supporting business operations
1322 --
1323
1324 chk_startup_action(true
1325 ,p_rec.business_group_id
1326 ,p_rec.legislation_code
1327 );
1328 IF hr_startup_data_api_support.g_startup_mode
1329 NOT IN ('GENERIC','STARTUP') THEN
1330 --
1331 -- Validate Important Attributes
1332 --
1333 hr_api.validate_bus_grp_id
1334 (p_business_group_id => p_rec.business_group_id
1335 ,p_associated_column1 => pay_pbf_shd.g_tab_nam
1336 || '.BUSINESS_GROUP_ID');
1337 --
1341 --
1338 -- after validating the set of important attributes,
1339 -- if Multiple Message Detection is enabled and at least
1340 -- one error has been found then abort further validation.
1342 hr_multi_message.end_validation_set;
1343 END IF;
1344 --
1345 -- Validate the following before actually inserting balance feed
1346 --
1347 pay_pbf_bus.chk_business_group_id
1348 (p_business_group_id => p_rec.business_group_id
1349 ,p_input_value_id => p_rec.input_value_id
1350 ,p_effective_date => p_effective_date
1351 );
1352 --
1353
1354 pay_pbf_bus.chk_legislation_code
1355 (p_legislation_code => p_rec.legislation_code
1356 );
1357 --
1358
1359 pay_pbf_bus.chk_input_value_id
1360 (p_input_value_id => p_rec.input_value_id
1361 ,p_business_group_id => p_rec.business_group_id
1362 ,p_legislation_code => p_rec.legislation_code
1363 );
1364 --
1365
1366 pay_pbf_bus.chk_balance_type_id
1367 (p_balance_type_id => p_rec.balance_type_id
1368 ,p_business_group_id => p_rec.business_group_id
1369 ,p_legislation_code => p_rec.legislation_code
1370 );
1371 --
1372 pay_pbf_bus.chk_uom
1373 (p_input_value_id => p_rec.input_value_id
1374 ,p_balance_type_id => p_rec.balance_type_id
1375 ,p_effective_date => p_effective_date
1376 );
1377 --
1378
1379 pay_pbf_bus.chk_currency_match
1380 (p_input_value_id => p_rec.input_value_id
1381 ,p_balance_type_id => p_rec.balance_type_id
1382 );
1383 --
1384
1385 pay_pbf_bus.chk_bal_feed_unique
1386 (p_input_value_id => p_rec.input_value_id
1387 ,p_balance_type_id => p_rec.balance_type_id
1388 ,p_effective_date => p_effective_date
1389 );
1390 --
1391
1392 pay_pbf_bus.chk_bal_class_exists
1393 (p_balance_type_id => p_rec.balance_type_id,
1394 p_input_value_id => p_rec.input_value_id); -- # 10427303
1395
1396 --
1397 -- When called from STARTUP Mode ignore this validation. Bug #8721639
1398 IF hr_startup_data_api_support.g_startup_mode NOT IN ('STARTUP') THEN
1399 -- If trying to create an initial feed then check no other initial feed
1400 -- already exists
1401 if (get_balance_init_flag (p_input_value_id => p_rec.input_value_id) ) then
1402 pay_pbf_bus.chk_initial_feed_exists
1403 (p_balance_type_id => p_rec.balance_type_id
1404 ,p_business_group_id => p_rec.business_group_id
1405 ,p_legislation_code => p_rec.legislation_code
1406 );
1407 end if;
1408 END IF; -- added for Bug #8721639
1409
1410 --
1411
1412 pay_pbf_bus.chk_feed_ipv_life
1413 (p_input_value_id => p_rec.input_value_id
1414 ,p_effective_date => p_effective_date
1415 );
1416
1417 --Bug 10427303. The below call should be made if the mode is not
1418 -- 'STARTUP' or if the mode is 'STARTUP' then the feed is not initial
1419 -- balance feed or adjustment
1420 IF ((hr_startup_data_api_support.g_startup_mode NOT IN ('STARTUP')) or
1421 ((hr_startup_data_api_support.g_startup_mode = 'STARTUP') and
1422 ((not get_balance_init_flag(p_rec.input_value_id)) and
1423 pay_pbf_bus.is_pay_value(p_rec.input_value_id))
1424 )) THEN
1425 -- When creating a balance feed derive the end date
1426 -- ie. input value may be date effectively deleted in
1427 -- the future or there are future balance feeds.
1428 l_validation_end_date := hr_balance_feeds.bal_feed_end_date
1429 (null,
1430 p_rec.balance_type_id,
1431 p_rec.input_value_id,
1432 p_effective_date,
1436 ('BALANCE_FEED',
1433 p_validation_start_date);
1434
1435 if hr_balance_feeds.bf_chk_proc_run_results
1437 'INSERT',
1438 null, null, null, null, null,
1439 p_rec.input_value_id,
1440 p_validation_start_date,
1441 l_validation_end_date) then
1442
1443 fnd_message.set_name('PAY','HR_7876_BAL_FEED_RESULTS_EXIST');
1447 END IF; -- Bug 10427303
1444 p_exist_run_result_warning :=TRUE;
1445
1446 end if;
1448
1449 -- When called from STARTUP Mode ignore this validation. Bug #8721639
1450 IF hr_startup_data_api_support.g_startup_mode NOT IN ('STARTUP') THEN
1451
1452 -- When initial balance feed is being created, the effective end date must be
1453 -- hr_api.g_eot.
1454
1455 if get_balance_init_flag (p_input_value_id => p_rec.input_value_id)
1456 and l_validation_end_date <> hr_api.g_eot then
1457 fnd_message.set_name('PAY','PAY_33243_BIFEED_INV_EDATE');
1458 fnd_message.raise_error;
1459 end if;
1460
1461 END IF; -- added for Bug #8721639
1462
1463 --
1464 -- Validate Dependent Attributes
1465 --
1466 pay_pbf_bus.chk_scale_value
1467 (p_scale => p_rec.scale
1468 ,p_input_value_id => p_rec.input_value_id
1469 ,p_effective_date => p_effective_date
1470 );
1471 --
1472 hr_utility.set_location(' Leaving:'||l_proc, 10);
1473
1474 End insert_validate;
1475 --
1476 -- ----------------------------------------------------------------------------
1477 -- |---------------------------< update_validate >----------------------------|
1478 -- ----------------------------------------------------------------------------
1479 Procedure update_validate
1480 (p_rec in pay_pbf_shd.g_rec_type
1481 ,p_effective_date in date
1482 ,p_datetrack_mode in varchar2
1483 ,p_validation_start_date in date
1484 ,p_validation_end_date in date
1485 ,p_exist_run_result_warning out nocopy boolean
1486 ) is
1487 --
1488 l_proc varchar2(72) := g_package||'update_validate';
1489 --
1490 Begin
1491 hr_utility.set_location('Entering:'||l_proc, 5);
1492 --
1493
1494 -- Clearing the Global variable since the record may have been changed.
1495 --
1496 g_balance_init_flag := NULL;
1497 --
1498
1499 chk_dtupd_allowed ( p_datetrack_mode => p_datetrack_mode
1500 ,p_input_value_id => p_rec.input_value_id );
1501
1502 --
1503 -- Call all supporting business operations
1504 --
1505 -- Check that the fields which cannot be updated have not be changed
1506
1507 chk_non_updateable_args(p_effective_date => p_effective_date
1508 ,p_rec => p_rec
1509 );
1510
1511 chk_startup_action(false
1512 ,p_rec.business_group_id
1513 ,p_rec.legislation_code
1514 );
1515 IF hr_startup_data_api_support.g_startup_mode
1516 NOT IN ('GENERIC','STARTUP') THEN
1517 --
1518 -- Validate Important Attributes
1519 --
1520 hr_api.validate_bus_grp_id
1521 (p_business_group_id => p_rec.business_group_id
1522 ,p_associated_column1 => pay_pbf_shd.g_tab_nam
1523 || '.BUSINESS_GROUP_ID');
1524 --
1525 -- After validating the set of important attributes,
1526 -- if Multiple Message Detection is enabled and at least
1527 -- one error has been found then abort further validation.
1528 --
1529 hr_multi_message.end_validation_set;
1530 END IF;
1531 --
1532 --
1533 -- Validate the following before actually updating balance feed
1534 --
1535 pay_pbf_bus.chk_bal_class_exists
1536 (p_balance_type_id => p_rec.balance_type_id,
1537 p_input_value_id => p_rec.input_value_id); -- # 10427303
1538 --
1539 if hr_balance_feeds.bf_chk_proc_run_results
1540 ('BALANCE_FEED'
1541 ,'UPDATE_DELETE'
1542 ,null, null, null
1543 ,p_rec.balance_feed_id
1544 ,null, null
1545 ,p_validation_start_date
1546 ,p_validation_end_date
1547 ) then
1548 fnd_message.set_name('PAY','HR_7876_BAL_FEED_RESULTS_EXIST');
1549 p_exist_run_result_warning :=TRUE;
1550 end if;
1551 --
1552 -- Validate Dependent Attributes
1553 --
1554 pay_pbf_bus.chk_scale_value
1555 (p_scale => p_rec.scale
1556 ,p_input_value_id => p_rec.input_value_id
1557 ,p_effective_date => p_effective_date
1558 );
1559 --
1560 -- Call the datetrack update integrity operation
1561 --
1562 dt_update_validate
1563 (p_datetrack_mode => p_datetrack_mode
1564 ,p_validation_start_date => p_validation_start_date
1565 ,p_validation_end_date => p_validation_end_date
1566 );
1567 --
1568 --
1569 hr_utility.set_location(' Leaving:'||l_proc, 10);
1570
1571 End update_validate;
1572 --
1573 -- ----------------------------------------------------------------------------
1574 -- |---------------------------< delete_validate >----------------------------|
1575 -- ----------------------------------------------------------------------------
1576 Procedure delete_validate
1577 (p_rec in pay_pbf_shd.g_rec_type
1578 ,p_effective_date in date
1579 ,p_datetrack_mode in varchar2
1580 ,p_validation_start_date in date
1581 ,p_validation_end_date in date
1582 ,p_exist_run_result_warning out nocopy boolean
1583 ) is
1584 --
1585 l_proc varchar2(72) := g_package||'delete_validate';
1586 l_validation_end_date date;
1587 --
1588 Begin
1589 hr_utility.set_location('Entering:'||l_proc, 5);
1590 --
1591
1592 -- Clearing the Global variable since the record may have been changed.
1593 --
1594 g_balance_init_flag := NULL;
1598 -- When called from STARTUP Mode ignore this validation. Bug #8721639
1595 --
1596
1597 --
1599 IF hr_startup_data_api_support.g_startup_mode NOT IN ('STARTUP') THEN
1600 chk_dtdel_allowed ( p_datetrack_mode => p_datetrack_mode
1601 ,p_input_value_id => p_rec.input_value_id);
1602 END IF; -- added for Bug #8721639
1603
1604
1605 chk_startup_action(false
1606 ,pay_pbf_shd.g_old_rec.business_group_id
1607 ,pay_pbf_shd.g_old_rec.legislation_code
1611 NOT IN ('GENERIC','STARTUP') THEN
1608 );
1609
1610 IF hr_startup_data_api_support.g_startup_mode
1612 --
1613 -- Validate Important Attributes
1614 --
1615 --
1616 -- After validating the set of important attributes,
1617 -- if Multiple Message Detection is enabled and at least
1618 -- one error has been found then abort further validation.
1619 --
1620 hr_multi_message.end_validation_set;
1621 END IF;
1622 --
1623 -- Call all supporting business operations
1624 --
1625
1626 -- If extending the lifetime of a balance feed derive the end date ie. input
1627 -- value may be date effectively deleted in the future or there are future
1628 -- future balance feeds.
1629 if (p_datetrack_mode = 'FUTURE_CHANGE' or
1630 (p_datetrack_mode = 'DELETE_NEXT_CHANGE' and
1631 p_validation_end_date = hr_api.g_eot)) then
1632 l_validation_end_date := hr_balance_feeds.bal_feed_end_date
1633 (p_rec.balance_feed_id,
1634 p_rec.balance_type_id,
1635 p_rec.input_value_id,
1636 p_effective_date,
1637 p_validation_start_date);
1638 end if;
1639
1640 if hr_balance_feeds.bf_chk_proc_run_results
1641 ('BALANCE_FEED',
1642 'UPDATE_DELETE',
1643 null, null, null,
1644 p_rec.balance_feed_id,
1645 null, null,
1646 p_validation_start_date,
1647 l_validation_end_date) then
1648
1649 fnd_message.set_name('PAY','HR_7876_BAL_FEED_RESULTS_EXIST');
1650 p_exist_run_result_warning :=TRUE;
1651
1652 end if;
1653 --
1654
1655 pay_pbf_bus.chk_bal_class_exists
1656 (p_balance_type_id => p_rec.balance_type_id,
1657 p_input_value_id => p_rec.input_value_id); -- # 10427303
1658
1659 --
1660 dt_delete_validate
1661 (p_datetrack_mode => p_datetrack_mode
1662 ,p_validation_start_date => p_validation_start_date
1663 ,p_validation_end_date => p_validation_end_date
1664 ,p_balance_feed_id => p_rec.balance_feed_id
1665 );
1666 --
1667 hr_utility.set_location(' Leaving:'||l_proc, 10);
1668
1669 End delete_validate;
1670 --
1671 --
1672 -- ---------------------------------------------------------------------------
1673 -- |-----------------------< get_balance_init_flag >-------------------------|
1674 -- ---------------------------------------------------------------------------
1675 --
1676 Function get_balance_init_flag
1677 (p_input_value_id in number)
1678 Return boolean Is
1679 --
1680 -- Declare cursor
1681 --
1682 cursor csr_balance_init_flag is
1683 SELECT 1
1684 FROM pay_input_values_f inv,
1685 pay_element_types_f elt,
1686 pay_element_classifications ec
1687 WHERE inv.input_value_id = p_input_value_id
1688 AND inv.element_type_id = elt.element_type_id
1689 AND elt.classification_id = ec.classification_id
1690 AND ec.balance_initialization_flag = 'Y';
1691
1692 --
1693 -- Declare local variables
1694 --
1695 l_exists varchar2(1);
1696 l_proc varchar2(72) := g_package||'get_balance_init_flag';
1697 --
1698 Begin
1699 --
1700 hr_utility.set_location('Entering:'|| l_proc, 10);
1701 --
1702 if g_balance_init_flag is null then
1703
1704 open csr_balance_init_flag;
1705 fetch csr_balance_init_flag into l_exists;
1706
1707 if csr_balance_init_flag%found then
1708 g_balance_init_flag := true;
1709 else
1710 g_balance_init_flag := false;
1711 end if;
1712
1713 close csr_balance_init_flag;
1714
1715 end if;
1716
1717 --
1718 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1722 --
1719 return g_balance_init_flag;
1720 --
1721 End get_balance_init_flag;
1723 --
1724 -- ---------------------------------------------------------------------------
1725 -- |---------------------------< is_pay_value >------------------------------|
1726 -- ---------------------------------------------------------------------------
1727 --
1728 Function is_pay_value
1729 (p_input_value_id in number)
1730 Return boolean Is
1731 --
1732 -- Declare cursor
1733 --
1734 cursor csr_is_pay_value is
1735 SELECT 1
1736 FROM pay_input_values_f inv
1737 WHERE inv.input_value_id = p_input_value_id
1738 AND inv.name = 'Pay Value';
1739 --
1740 -- Declare local variables
1741 --
1742 l_exists varchar2(1);
1743 l_proc varchar2(72) := g_package||'is_pay_value';
1744 l_is_pay_value boolean;
1745 --
1746 Begin
1747 --
1748 hr_utility.set_location('Entering:'|| l_proc, 10);
1749 --
1750
1751 open csr_is_pay_value;
1752 fetch csr_is_pay_value into l_exists;
1753
1754 if csr_is_pay_value%found then
1755 l_is_pay_value := true;
1756 else
1757 l_is_pay_value := false;
1758 end if;
1759
1760 close csr_is_pay_value;
1761
1762
1763 --
1764 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1765 return l_is_pay_value;
1766 --
1767 End is_pay_value;
1768 end pay_pbf_bus;
[Home] [Help]