[Home] [Help]
PACKAGE BODY: APPS.PAY_SBT_BUS
Source
1 Package Body pay_sbt_bus as
2 /* $Header: pysbtrhi.pkb 120.0 2005/05/29 08:34:39 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_sbt_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< get_template_info >--------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure get_template_info
14 (p_template_id in number
15 ,p_business_group_id in out nocopy number
16 ,p_template_type in out nocopy varchar2
17 ) is
18 --
19 -- Cursor to get the template information.
20 --
21 cursor csr_get_template_info is
22 select pet.business_group_id
23 , pet.template_type
24 from pay_element_templates pet
25 where pet.template_id = p_template_id;
26 --
27 l_proc varchar2(72) := g_package||'get_template_info';
28 l_api_updating boolean;
29 l_valid varchar2(1);
30 --
31 Begin
32 hr_utility.set_location('Entering:'||l_proc, 5);
33 open csr_get_template_info;
34 fetch csr_get_template_info
35 into p_business_group_id
36 , p_template_type;
37 close csr_get_template_info;
38 hr_utility.set_location(' Leaving:'||l_proc, 15);
39 End get_template_info;
40 -- ----------------------------------------------------------------------------
41 -- |-----------------------< chk_non_updateable_args >------------------------|
42 -- ----------------------------------------------------------------------------
43 Procedure chk_non_updateable_args
44 (p_rec in pay_sbt_shd.g_rec_type
45 ) is
46 --
47 -- Cursor to disallow update if a balance has been generated from
48 -- this shadow balance.
49 --
50 cursor csr_disallow_update is
51 select null
52 from pay_template_core_objects tco
53 where tco.core_object_type = pay_tco_shd.g_sbt_lookup_type
54 and tco.shadow_object_id = p_rec.balance_type_id;
55 --
56 -- Cursor to disallow update of balance UOM if balance feeds to this
57 -- balance exists.
58 --
59 cursor csr_disallow_uom is
60 select null
61 from pay_shadow_balance_feeds sbf
62 where sbf.balance_type_id = p_rec.balance_type_id;
63 --
64 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
65 l_error exception;
66 l_api_updating boolean;
67 l_argument varchar2(30);
68 l_disallow varchar2(1);
69 --
70 Begin
71 hr_utility.set_location('Entering:'||l_proc, 5);
72 l_api_updating := pay_sbt_shd.api_updating
73 (p_balance_type_id => p_rec.balance_type_id
74 ,p_object_version_number => p_rec.object_version_number
75 );
76 if not l_api_updating then
77 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
78 hr_utility.set_message_token('PROCEDURE', l_proc);
79 hr_utility.set_message_token('STEP', '10');
80 hr_utility.raise_error;
81 end if;
82 --
83 hr_utility.set_location(l_proc, 20);
84 --
85 -- Check that the update is actually allowed.
86 --
87 open csr_disallow_update;
88 fetch csr_disallow_update into l_disallow;
89 if csr_disallow_update%found then
90 hr_utility.set_location(l_proc, 25);
91 close csr_disallow_update;
92 fnd_message.set_name('PAY', 'PAY_50113_SBT_CORE_ROW_EXISTS');
93 fnd_message.raise_error;
94 end if;
95 close csr_disallow_update;
96 --
97 -- Check the otherwise non-updateable arguments.
98 --
99 -- p_template_id
100 --
101 if nvl(p_rec.template_id, hr_api.g_number) <>
102 nvl(pay_sbt_shd.g_old_rec.template_id, hr_api.g_number)
103 then
104 l_argument := 'p_template_id';
105 raise l_error;
106 end if;
107 --
108 -- p_balance_uom
109 --
110 if nvl(p_rec.balance_uom, hr_api.g_varchar2) <>
111 nvl(pay_sbt_shd.g_old_rec.balance_uom, hr_api.g_varchar2)
112 then
113 --
114 -- Check to see if the update is allowed.
115 --
116 open csr_disallow_uom;
117 fetch csr_disallow_uom into l_disallow;
118 if csr_disallow_uom%found then
119 close csr_disallow_uom;
120 l_argument := 'p_balance_uom';
121 raise l_error;
122 end if;
123 close csr_disallow_uom;
124 end if;
125 --
126 -- p_input_value_id
127 --
128 if nvl(p_rec.input_value_id, hr_api.g_number) <>
129 nvl(pay_sbt_shd.g_old_rec.input_value_id, hr_api.g_number)
130 then
131 l_argument := 'input_value_id';
132 raise l_error;
133 end if;
134 --
135 hr_utility.set_location('Leaving:'||l_proc, 25);
136 exception
137 when l_error then
138 hr_utility.set_location('Leaving:'||l_proc, 30);
139 hr_api.argument_changed_error
140 (p_api_name => l_proc
141 ,p_argument => l_argument);
142 when others then
143 hr_utility.set_location('Leaving:'||l_proc, 35);
144 raise;
145 End chk_non_updateable_args;
146 -- ----------------------------------------------------------------------------
147 -- |---------------------------< chk_template_id >----------------------------|
148 -- ----------------------------------------------------------------------------
149 Procedure chk_template_id
150 (p_template_id in number
151 ) is
152 --
153 -- Cursor to check that template_id is valid.
154 --
155 cursor csr_template_id_valid is
156 select null
157 from pay_element_templates pet
158 where pet.template_id = p_template_id;
159 --
160 l_proc varchar2(72) := g_package||'chk_template_id';
161 l_valid varchar2(1);
162 --
163 Begin
164 hr_utility.set_location('Entering:'||l_proc, 5);
165 --
166 -- Check that template_id is not null.
167 --
168 hr_api.mandatory_arg_error
169 (p_api_name => l_proc
170 ,p_argument => 'p_template_id'
171 ,p_argument_value => p_template_id
172 );
173 --
174 -- Check that template_id is valid.
175 --
176 open csr_template_id_valid;
177 fetch csr_template_id_valid into l_valid;
178 if csr_template_id_valid%notfound then
179 hr_utility.set_location(' Leaving:'||l_proc, 10);
180 close csr_template_id_valid;
181 fnd_message.set_name('PAY', 'PAY_50114_ETM_INVALID_TEMPLATE');
182 fnd_message.raise_error;
183 end if;
184 close csr_template_id_valid;
185 hr_utility.set_location(' Leaving:'||l_proc, 15);
186 End chk_template_id;
187 -- ----------------------------------------------------------------------------
188 -- |---------------------------< chk_balance_name >---------------------------|
189 -- ----------------------------------------------------------------------------
190 Procedure chk_balance_name
191 (p_balance_name in varchar2
192 ,p_template_id in number
193 ,p_template_type in varchar2
194 ,p_business_group_id in number
195 ,p_balance_type_id in number
196 ,p_object_version_number in number
197 ) is
198 --
199 -- Cursor to check that the balance name is unique within a template
200 -- (template_type = 'T').
201 --
202 cursor csr_T_balance_name_exists is
203 select null
204 from pay_shadow_balance_types sbt
205 where sbt.template_id = p_template_id
206 and upper(nvl(sbt.balance_name, hr_api.g_varchar2)) =
207 upper(nvl(p_balance_name, hr_api.g_varchar2));
208 --
209 -- Cursor to check that the balance name is unique for a business group
210 -- (template type = 'U').
211 --
212 cursor csr_U_balance_name_exists is
213 select null
214 from pay_shadow_balance_types sbt
215 , pay_element_templates pet
216 where upper(sbt.balance_name) = upper(p_balance_name)
217 and pet.template_id = sbt.template_id
218 and pet.template_type = 'U'
219 and pet.business_group_id = p_business_group_id;
220 --
221 l_proc varchar2(72) := g_package||'chk_balance_name';
222 l_exists varchar2(1);
223 l_value varchar2(2000);
224 l_output varchar2(2000);
225 l_rgeflg varchar2(2000);
226 l_nullok varchar2(2000);
227 l_api_updating boolean;
228 --
229 Begin
230 hr_utility.set_location('Entering:'||l_proc, 5);
231 l_api_updating := pay_sbt_shd.api_updating
232 (p_balance_type_id => p_balance_type_id
233 ,p_object_version_number => p_object_version_number
234 );
235 if (l_api_updating and nvl(p_balance_name, hr_api.g_varchar2) <>
236 nvl(pay_sbt_shd.g_old_rec.balance_name, hr_api.g_varchar2)) or
237 not l_api_updating
238 then
239 --
240 -- The name cannot be null if the template type is 'U'.
241 --
242 if p_template_type = 'U' then
243 l_nullok := 'N';
244 else
245 l_nullok := 'Y';
246 end if;
247 --
248 -- Check that the name format is correct (payroll name).
249 --
250 l_value := p_balance_name;
251 if p_template_type = 'T' then
252 --
253 -- If template type is 'T' then the balance name can begin
254 -- with a space which is not the correct format.
255 --
256 l_value := replace(l_value, ' ', 'A');
257 end if;
258 hr_chkfmt.checkformat
259 (value => l_value
260 ,format => 'PAY_NAME'
261 ,output => l_output
262 ,minimum => null
263 ,maximum => null
264 ,nullok => l_nullok
265 ,rgeflg => l_rgeflg
266 ,curcode => null
267 );
268 --
269 -- Uniqueness checks.
270 --
271 if p_template_type = 'T' then
272 --
273 -- Check for uniqueness using the cursor.
274 --
275 open csr_T_balance_name_exists;
276 fetch csr_T_balance_name_exists into l_exists;
277 if csr_T_balance_name_exists%found then
278 close csr_T_balance_name_exists;
279 hr_utility.set_location(' Leaving:'||l_proc, 10);
280 fnd_message.set_name('PAY', 'PAY_50115_SBT_BALANCE_EXISTS');
281 fnd_message.set_token('BALANCE_NAME', p_balance_name);
282 fnd_message.raise_error;
283 end if;
284 close csr_T_balance_name_exists;
285 elsif p_template_type = 'U' then
286 --
287 -- Check for uniqueness using the cursor.
288 --
289 open csr_U_balance_name_exists;
290 fetch csr_U_balance_name_exists into l_exists;
291 if csr_U_balance_name_exists%found then
292 close csr_U_balance_name_exists;
293 hr_utility.set_location(' Leaving:'||l_proc, 15);
294 fnd_message.set_name('PAY', 'PAY_50115_SBT_BALANCE_EXISTS');
295 fnd_message.set_token('BALANCE_NAME', p_balance_name);
296 fnd_message.raise_error;
297 end if;
298 close csr_U_balance_name_exists;
299 end if;
300 end if;
301 hr_utility.set_location(' Leaving:'||l_proc, 20);
302 End chk_balance_name;
303 -- ----------------------------------------------------------------------------
304 -- |----------------------< chk_asg_remuneration_flag >-----------------------|
305 -- ----------------------------------------------------------------------------
306 Procedure chk_asg_remuneration_flag
307 (p_effective_date in date
308 ,p_assignment_remuneration_flag in varchar2
309 ,p_balance_type_id in number
310 ,p_object_version_number in number
311 ) is
312 --
313 l_proc varchar2(72) := g_package||'chk_asg_remuneration_flag';
314 l_api_updating boolean;
315 --
316 Begin
317 hr_utility.set_location('Entering:'||l_proc, 5);
318 l_api_updating := pay_sbt_shd.api_updating
319 (p_balance_type_id => p_balance_type_id
320 ,p_object_version_number => p_object_version_number
321 );
322 if (l_api_updating and
323 nvl(p_assignment_remuneration_flag, hr_api.g_varchar2) <>
324 nvl(pay_sbt_shd.g_old_rec.assignment_remuneration_flag,
325 hr_api.g_varchar2)) or
326 not l_api_updating
327 then
328 --
329 -- Check that the core object type is not null.
330 --
331 hr_api.mandatory_arg_error
332 (p_api_name => l_proc
333 ,p_argument => 'p_assignment_remuneration_flag'
334 ,p_argument_value => p_assignment_remuneration_flag
335 );
336 --
337 -- Validate against hr_lookups.
338 --
339 if hr_api.not_exists_in_hr_lookups
340 (p_effective_date => p_effective_date
341 ,p_lookup_type => 'YES_NO'
342 ,p_lookup_code => p_assignment_remuneration_flag
343 )
344 then
345 hr_utility.set_location(' Leaving:'||l_proc, 10);
346 fnd_message.set_name('PAY', 'HR_52966_INVALID_LOOKUP');
347 fnd_message.set_token('LOOKUP_TYPE', 'YES_NO');
348 fnd_message.set_token('COLUMN', 'ASSIGNMENT_REMUNERATION_FLAG');
349 fnd_message.raise_error;
350 end if;
351 end if;
352 hr_utility.set_location(' Leaving:'||l_proc, 20);
353 End chk_asg_remuneration_flag;
354 -- ----------------------------------------------------------------------------
355 -- |--------------------------< chk_balance_uom >-----------------------------|
356 -- ----------------------------------------------------------------------------
357 Procedure chk_balance_uom
358 (p_effective_date in date
359 ,p_balance_uom in varchar2
360 ,p_balance_type_id in number
361 ,p_input_value_id in number
362 ,p_object_version_number in number
363 ) is
364 --
365 l_proc varchar2(72) := g_package||'chk_balance_uom';
366 l_api_updating boolean;
367 l_exists varchar2(1);
368 --
369 -- Cursor to check the uom of input value matches that of the balance.
370 --
371 Cursor csr_chk_uom is
372 select null
373 from pay_shadow_input_values siv
374 where siv.input_value_id = p_input_value_id
375 and upper(siv.uom) = upper(p_balance_uom);
376 --
377 Begin
378 hr_utility.set_location('Entering:'||l_proc, 5);
379 l_api_updating := pay_sbt_shd.api_updating
380 (p_balance_type_id => p_balance_type_id
381 ,p_object_version_number => p_object_version_number
382 );
383 if (l_api_updating and nvl(p_balance_uom, hr_api.g_varchar2) <>
384 nvl(pay_sbt_shd.g_old_rec.balance_uom, hr_api.g_varchar2)) or
385 not l_api_updating
386 then
387 --
388 -- Check that the core object type is not null.
389 --
390 hr_api.mandatory_arg_error
391 (p_api_name => l_proc
392 ,p_argument => 'p_balance_uom'
393 ,p_argument_value => p_balance_uom
394 );
395 --
396 -- Validate against hr_lookups.
397 --
398 if hr_api.not_exists_in_hr_lookups
399 (p_effective_date => p_effective_date
400 ,p_lookup_type => 'UNITS'
401 ,p_lookup_code => p_balance_uom
402 )
403 then
404 hr_utility.set_location(' Leaving:'||l_proc, 10);
405 fnd_message.set_name('PAY', 'HR_52966_INVALID_LOOKUP');
406 fnd_message.set_token('LOOKUP_TYPE', 'UNITS');
407 fnd_message.set_token('COLUMN', 'BALANCE_UOM');
408 fnd_message.raise_error;
409 end if;
410 end if;
411 --
412 if (l_api_updating and nvl(p_balance_uom, hr_api.g_varchar2) <>
413 nvl(pay_sbt_shd.g_old_rec.balance_uom, hr_api.g_varchar2)) and
414 (p_input_value_id is not null) then
415 --
416 -- Check that the UOMs are compatible
417 --
418 open csr_chk_uom;
419 fetch csr_chk_uom into l_exists;
420 if csr_chk_uom%notfound then
421 hr_utility.set_location(' Leaving:'||l_proc, 10);
422 close csr_chk_uom;
423 fnd_message.set_name('PAY', 'PAY_51522_SBT_UOM_MISMATCH');
424 fnd_message.raise_error;
425 end if;
426 close csr_chk_uom;
427 end if;
428 --
429 hr_utility.set_location(' Leaving:'||l_proc, 20);
430 End chk_balance_uom;
431 -- ----------------------------------------------------------------------------
432 -- |-----------------------------< chk_currency_code >------------------------|
433 -- ----------------------------------------------------------------------------
434 Procedure chk_currency_code
435 (p_currency_code in varchar2
436 ,p_balance_type_id in number
437 ,p_object_version_number in number
438 ) is
439 --
440 -- Check that the currency code is valid.
441 --
442 cursor csr_valid_currency_code is
443 select null
444 from fnd_currencies fc
445 where upper(fc.currency_code) = upper(p_currency_code)
446 and fc.enabled_flag = 'Y'
447 and fc.currency_flag = 'Y';
448 --
449 l_proc varchar2(72) := g_package||'chk_currency_code';
450 l_api_updating boolean;
451 l_valid varchar2(1);
452 --
453 Begin
454 hr_utility.set_location('Entering:'||l_proc, 5);
455 l_api_updating := pay_sbt_shd.api_updating
456 (p_balance_type_id => p_balance_type_id
457 ,p_object_version_number => p_object_version_number
458 );
459 if (l_api_updating and nvl(p_currency_code, hr_api.g_varchar2) <>
460 nvl(pay_sbt_shd.g_old_rec.currency_code, hr_api.g_varchar2)) or
461 not l_api_updating
462 then
463 if p_currency_code is not null then
464 open csr_valid_currency_code;
465 fetch csr_valid_currency_code into l_valid;
466 if csr_valid_currency_code%notfound then
467 hr_utility.set_location(' Leaving:'||l_proc, 10);
468 close csr_valid_currency_code;
469 fnd_message.set_name('PAY', 'HR_51855_QUA_CCY_INV');
470 fnd_message.raise_error;
471 end if;
472 close csr_valid_currency_code;
473 end if;
474 hr_utility.set_location(' Leaving:'||l_proc, 15);
475 end if;
476 End chk_currency_code;
477 -- ----------------------------------------------------------------------------
478 -- |-------------------------< chk_exclusion_rule_id >------------------------|
479 -- ----------------------------------------------------------------------------
480 Procedure chk_exclusion_rule_id
481 (p_exclusion_rule_id in number
482 ,p_template_id in number
483 ,p_balance_type_id in number
484 ,p_object_version_number in number
485 ) is
486 --
487 -- Cursor to check that the exclusion_rule_id is valid.
488 --
489 cursor csr_exclusion_rule_id_valid is
490 select null
491 from pay_template_exclusion_rules ter
492 where ter.exclusion_rule_id = p_exclusion_rule_id
493 and ter.template_id = p_template_id;
494 --
495 l_proc varchar2(72) := g_package||'chk_exclusion_rule_id';
496 l_api_updating boolean;
497 l_valid varchar2(1);
498 --
499 Begin
500 hr_utility.set_location('Entering:'||l_proc, 5);
501 l_api_updating := pay_sbt_shd.api_updating
502 (p_balance_type_id => p_balance_type_id
503 ,p_object_version_number => p_object_version_number
504 );
505 if (l_api_updating and nvl(p_exclusion_rule_id, hr_api.g_number) <>
506 nvl(pay_sbt_shd.g_old_rec.exclusion_rule_id, hr_api.g_number)) or
507 not l_api_updating
508 then
509 if p_exclusion_rule_id is not null then
510 open csr_exclusion_rule_id_valid;
511 fetch csr_exclusion_rule_id_valid into l_valid;
512 if csr_exclusion_rule_id_valid%notfound then
513 hr_utility.set_location('Leaving:'||l_proc, 10);
514 close csr_exclusion_rule_id_valid;
515 fnd_message.set_name('PAY', 'PAY_50100_ETM_INVALID_EXC_RULE');
516 fnd_message.raise_error;
517 end if;
518 close csr_exclusion_rule_id_valid;
519 end if;
520 end if;
521 hr_utility.set_location(' Leaving:'||l_proc, 15);
522 End chk_exclusion_rule_id;
523 -- ----------------------------------------------------------------------------
524 -- |-----------------------< chk_base_balance_type_id >-----------------------|
525 -- ----------------------------------------------------------------------------
526 Procedure chk_base_balance_type_id
527 (p_base_balance_type_id in number
528 ,p_base_balance_name in varchar2
529 ,p_template_id in number
530 ,p_balance_type_id in number
531 ,p_object_version_number in number
532 ) is
533 --
534 l_proc varchar2(72) := g_package||'chk_base_balance_type_id';
535 l_exists varchar2(1);
536 l_api_updating boolean;
537 --
538 Cursor csr_chk_template is
539 select null
540 from pay_shadow_balance_types sbt
541 where sbt.balance_type_id = p_base_balance_type_id
542 and sbt.template_id = p_template_id;
543 --
544 Cursor csr_base_balances is
545 select base_balance_type_id
546 from pay_shadow_balance_types
547 start with balance_type_id = p_base_balance_type_id
548 connect by prior base_balance_type_id = balance_type_id
549 ;
550 Begin
551 hr_utility.set_location('Entering: '||l_proc, 5);
552 --
553 l_api_updating := pay_sbt_shd.api_updating
554 (p_balance_type_id => p_balance_type_id
555 ,p_object_version_number => p_object_version_number
556 );
557
558 if (l_api_updating and nvl(p_base_balance_type_id, hr_api.g_number) <>
559 nvl(pay_sbt_shd.g_old_rec.base_balance_type_id, hr_api.g_number)) or
560 not l_api_updating
561 then
562 --
563 -- Only one of the base_balance_name and base_balance_type_id may be
564 -- not null.
565 --
566 if (p_base_balance_type_id is not null and p_base_balance_name is not null) then
567 fnd_message.set_name('PAY', 'PAY_51523_SBT_ID_TYPE_NOT_NULL');
568 fnd_message.raise_error;
569 end if;
570 --
571 if p_base_balance_type_id is not null then
572 --
573 -- Check that the template of the base balance is same as the
574 -- current balance type
575 --
576 open csr_chk_template;
577 fetch csr_chk_template into l_exists;
578 if csr_chk_template%notfound then
579 hr_utility.set_location(' Leaving:'||l_proc, 20);
580 close csr_chk_template;
581 fnd_message.set_name('PAY', 'PAY_51524_SBT_INVALID_BASENAME');
582 fnd_message.raise_error;
583 end if;
584 close csr_chk_template;
585 --
586 -- Look for circular references in base balance. This is only
587 -- necessary when p_base_balance_type_id is being updated with a
588 -- NOT NULL value.
589 --
590 -- A new balance cannot cause a circular reference. A circular
591 -- reference can be caused if balance B1 is made the base balance for
592 -- balance B2, but balance B2 is the base balance for balance B1 or
593 -- another balance further up from B1.
594 --
595 if l_api_updating then
596 for crec in csr_base_balances loop
597 if crec.base_balance_type_id = p_balance_type_id then
598 fnd_message.set_name('PAY', 'PAY_50212_SBT_CIRCULAR_BAL_REF');
599 fnd_message.raise_error;
600 end if;
601 end loop;
602 end if;
603 end if;
604 end if;
605 hr_utility.set_location(' Leaving:'||l_proc, 30);
606 End chk_base_balance_type_id;
607 -- ----------------------------------------------------------------------------
608 -- |---------------------------< chk_input_value_id >-------------------------|
609 -- ----------------------------------------------------------------------------
610 Procedure chk_input_value_id
611 (p_input_value_id in number
612 ,p_balance_uom in varchar2
613 ,p_template_id in number
614 ) is
615 --
616 -- Cursor to check that the input value exists.
617 --
618 Cursor csr_input_value_exists is
619 select null
620 from pay_shadow_input_values siv
621 ,pay_shadow_element_types sel
622 where siv.input_value_id = p_input_value_id
623 and siv.element_type_id = sel.element_type_id
624 and sel.template_id = p_template_id;
625 --
626 -- Cursor to check the uom of input value matches that of the balance.
627 --
628 Cursor csr_chk_uom is
629 select null
630 from pay_shadow_input_values siv
631 where siv.input_value_id = p_input_value_id
632 and upper(siv.uom) = upper(p_balance_uom);
633 --
634 l_proc varchar2(72) := g_package||'chk_input_value_id';
635 l_exists varchar2(1);
636 --
637 Begin
638 hr_utility.set_location('Entering:'||l_proc, 5);
639 --
640 -- Check that the input value exists.
641 --
642 if p_input_value_id is not null then
643 open csr_input_value_exists;
644 fetch csr_input_value_exists into l_exists;
645 if csr_input_value_exists%notfound then
646 hr_utility.set_location(' Leaving:'||l_proc, 10);
647 close csr_input_value_exists;
648 fnd_message.set_name('PAY', 'PAY_50098_ETM_INVALID_INP_VAL');
649 fnd_message.raise_error;
650 end if;
651 close csr_input_value_exists;
652 --
653 -- Check that the UOMs are compatible
654 --
655 open csr_chk_uom;
656 fetch csr_chk_uom into l_exists;
657 if csr_chk_uom%notfound then
658 hr_utility.set_location(' Leaving:'||l_proc, 10);
659 close csr_chk_uom;
660 fnd_message.set_name('PAY', 'PAY_51522_SBT_UOM_MISMATCH');
661 fnd_message.raise_error;
662 end if;
663 close csr_chk_uom;
664 end if;
665 --
666 hr_utility.set_location(' Leaving:'||l_proc, 15);
667 End chk_input_value_id;
668 -- ----------------------------------------------------------------------------
669 -- |------------------------------< chk_delete >------------------------------|
670 -- ----------------------------------------------------------------------------
671 Procedure chk_delete
672 (p_balance_type_id in number
673 ) is
674 --
675 -- Cursors to check for rows referencing the balance.
676 --
677 cursor csr_defined_balances is
678 select null
679 from pay_shadow_defined_balances sdb
680 where sdb.balance_type_id = p_balance_type_id;
681 --
682 cursor csr_core_objects is
683 select null
684 from pay_template_core_objects tco
685 where tco.core_object_type = pay_tco_shd.g_sbt_lookup_type
686 and tco.shadow_object_id = p_balance_type_id;
687 --
688 cursor csr_balance_types is
689 select null
690 from pay_shadow_balance_types sbt
691 where sbt.base_balance_type_id = p_balance_type_id;
692 --
693 cursor csr_balance_feeds is
694 select null
695 from pay_shadow_balance_feeds sdb
696 where sdb.balance_type_id = p_balance_type_id;
697 --
698 cursor csr_balance_classis is
699 select null
700 from pay_shadow_balance_classi sbc
701 where sbc.balance_type_id = p_balance_type_id;
702 --
703 cursor csr_gu_bal_exclusions is
704 select null
705 from pay_shadow_gu_bal_exclusions
706 where balance_type_id = p_balance_type_id;
707 --
708 l_proc varchar2(72) := g_package||'chk_delete';
709 l_error exception;
710 l_exists varchar2(1);
711 --
712 Begin
713 hr_utility.set_location('Entering:'||l_proc, 5);
714 --
715 open csr_defined_balances;
716 fetch csr_defined_balances into l_exists;
717 if csr_defined_balances%found then
718 hr_utility.set_location(' Leaving:'||l_proc, 10);
719 close csr_defined_balances;
720 raise l_error;
721 end if;
722 close csr_defined_balances;
723 --
724 open csr_core_objects;
725 fetch csr_core_objects into l_exists;
726 if csr_core_objects%found then
727 hr_utility.set_location(' Leaving:'||l_proc, 15);
728 close csr_core_objects;
729 raise l_error;
730 end if;
731 close csr_core_objects;
732 --
733 open csr_balance_feeds;
734 fetch csr_balance_feeds into l_exists;
735 if csr_balance_feeds%found then
736 hr_utility.set_location(' Leaving:'||l_proc, 20);
737 close csr_balance_feeds;
738 raise l_error;
739 end if;
740 close csr_balance_feeds;
741 --
742 open csr_balance_classis;
743 fetch csr_balance_classis into l_exists;
744 if csr_balance_classis%found then
745 hr_utility.set_location(' Leaving:'||l_proc, 25);
746 close csr_balance_classis;
747 raise l_error;
748 end if;
749 close csr_balance_classis;
750 --
751 open csr_gu_bal_exclusions;
752 fetch csr_gu_bal_exclusions into l_exists;
753 if csr_gu_bal_exclusions%found then
754 hr_utility.set_location(' Leaving:'||l_proc, 30);
755 close csr_gu_bal_exclusions;
756 raise l_error;
757 end if;
758 close csr_gu_bal_exclusions;
759 --
760 open csr_balance_types;
761 fetch csr_balance_types into l_exists;
762 if csr_balance_types%found then
763 hr_utility.set_location(' Leaving:'||l_proc, 35);
764 close csr_balance_types;
765 raise l_error;
766 end if;
767 close csr_balance_types;
768 hr_utility.set_location(' Leaving:'||l_proc, 100);
769 exception
770 when l_error then
771 fnd_message.set_name('PAY', 'PAY_50117_SBT_INVALID_DELETE');
772 fnd_message.raise_error;
773 when others then
774 hr_utility.set_location(' Leaving:'||l_proc, 110);
775 raise;
776 End chk_delete;
777 -- ----------------------------------------------------------------------------
778 -- |---------------------------< insert_validate >----------------------------|
779 -- ----------------------------------------------------------------------------
780 Procedure insert_validate
781 (p_effective_date in date
782 ,p_rec in pay_sbt_shd.g_rec_type
783 ) is
784 --
785 l_proc varchar2(72) := g_package||'insert_validate';
786 l_business_group_id number;
787 l_template_type varchar2(2000);
788 --
789 Begin
790 hr_utility.set_location('Entering:'||l_proc, 5);
791 --
792 -- Call all supporting business operations
793 --
794 chk_template_id(p_rec.template_id);
795 --
796 get_template_info
797 (p_template_id => p_rec.template_id
798 ,p_business_group_id => l_business_group_id
799 ,p_template_type => l_template_type
800 );
801 --
802 chk_balance_name
803 (p_balance_name => p_rec.balance_name
804 ,p_template_id => p_rec.template_id
805 ,p_template_type => l_template_type
806 ,p_business_group_id => l_business_group_id
807 ,p_balance_type_id => p_rec.balance_type_id
808 ,p_object_version_number => p_rec.object_version_number
809 );
810 --
811 chk_asg_remuneration_flag
812 (p_effective_date => p_effective_date
813 ,p_assignment_remuneration_flag => p_rec.assignment_remuneration_flag
814 ,p_balance_type_id => p_rec.balance_type_id
815 ,p_object_version_number => p_rec.object_version_number
816 );
817 --
818 chk_balance_uom
819 (p_effective_date => p_effective_date
820 ,p_balance_uom => p_rec.balance_uom
821 ,p_balance_type_id => p_rec.balance_type_id
822 ,p_input_value_id => p_rec.input_value_id
823 ,p_object_version_number => p_rec.object_version_number
824 );
825 --
826 chk_currency_code
827 (p_currency_code => p_rec.currency_code
828 ,p_balance_type_id => p_rec.balance_type_id
829 ,p_object_version_number => p_rec.object_version_number
830 );
831 --
832 chk_exclusion_rule_id
833 (p_exclusion_rule_id => p_rec.exclusion_rule_id
834 ,p_template_id => p_rec.template_id
835 ,p_balance_type_id => p_rec.balance_type_id
836 ,p_object_version_number => p_rec.object_version_number
837 );
838 --
839 chk_base_balance_type_id
840 (p_base_balance_type_id => p_rec.base_balance_type_id
841 ,p_base_balance_name => p_rec.base_balance_name
842 ,p_template_id => p_rec.template_id
843 ,p_balance_type_id => p_rec.balance_type_id
844 ,p_object_version_number => p_rec.object_version_number
845 );
846 --
847 chk_input_value_id
848 (p_input_value_id => p_rec.input_value_id
849 ,p_balance_uom => p_rec.balance_uom
850 ,p_template_id => p_rec.template_id
851 );
852 --
853 hr_utility.set_location(' Leaving:'||l_proc, 10);
854 End insert_validate;
855 --
856 -- ----------------------------------------------------------------------------
857 -- |---------------------------< update_validate >----------------------------|
858 -- ----------------------------------------------------------------------------
859 Procedure update_validate
860 (p_effective_date in date
861 ,p_rec in pay_sbt_shd.g_rec_type
862 ) is
863 --
864 l_proc varchar2(72) := g_package||'update_validate';
865 l_business_group_id number;
866 l_template_type varchar2(2000);
867 --
868 Begin
869 hr_utility.set_location('Entering:'||l_proc, 5);
870 --
871 -- Call all supporting business operations
872 --
873 chk_non_updateable_args(p_rec);
874 --
875 get_template_info
876 (p_template_id => p_rec.template_id
877 ,p_business_group_id => l_business_group_id
878 ,p_template_type => l_template_type
879 );
880 --
881 chk_balance_name
882 (p_balance_name => p_rec.balance_name
883 ,p_template_id => p_rec.template_id
884 ,p_template_type => l_template_type
885 ,p_business_group_id => l_business_group_id
886 ,p_balance_type_id => p_rec.balance_type_id
887 ,p_object_version_number => p_rec.object_version_number
888 );
889 --
890 chk_asg_remuneration_flag
891 (p_effective_date => p_effective_date
892 ,p_assignment_remuneration_flag => p_rec.assignment_remuneration_flag
893 ,p_balance_type_id => p_rec.balance_type_id
894 ,p_object_version_number => p_rec.object_version_number
895 );
896 --
897 chk_balance_uom
898 (p_effective_date => p_effective_date
899 ,p_balance_uom => p_rec.balance_uom
900 ,p_balance_type_id => p_rec.balance_type_id
901 ,p_input_value_id => p_rec.input_value_id
902 ,p_object_version_number => p_rec.object_version_number
903 );
904 --
905 chk_currency_code
906 (p_currency_code => p_rec.currency_code
907 ,p_balance_type_id => p_rec.balance_type_id
908 ,p_object_version_number => p_rec.object_version_number
909 );
910 --
911 chk_exclusion_rule_id
912 (p_exclusion_rule_id => p_rec.exclusion_rule_id
913 ,p_template_id => p_rec.template_id
914 ,p_balance_type_id => p_rec.balance_type_id
915 ,p_object_version_number => p_rec.object_version_number
916 );
917 --
918 chk_base_balance_type_id
919 (p_base_balance_type_id => p_rec.base_balance_type_id
920 ,p_base_balance_name => p_rec.base_balance_name
921 ,p_template_id => p_rec.template_id
922 ,p_balance_type_id => p_rec.balance_type_id
923 ,p_object_version_number => p_rec.object_version_number
924 );
925 --
926 hr_utility.set_location(' Leaving:'||l_proc, 10);
927 End update_validate;
928 --
929 -- ----------------------------------------------------------------------------
930 -- |---------------------------< delete_validate >----------------------------|
931 -- ----------------------------------------------------------------------------
932 Procedure delete_validate(p_rec in pay_sbt_shd.g_rec_type) is
933 --
934 l_proc varchar2(72) := g_package||'delete_validate';
935 --
936 Begin
937 hr_utility.set_location('Entering:'||l_proc, 5);
938 --
939 -- Call all supporting business operations
940 --
941 chk_delete(p_rec.balance_type_id);
942 --
943 hr_utility.set_location(' Leaving:'||l_proc, 10);
944 End delete_validate;
945 --
946 end pay_sbt_bus;