[Home] [Help]
PACKAGE BODY: APPS.PAY_TCO_BUS
Source
1 Package Body pay_tco_bus as
2 /* $Header: pytcorhi.pkb 120.0 2005/05/29 09:01:02 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_tco_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 ) is
17 --
18 -- Cursor to get the template information.
19 --
20 cursor csr_get_template_info is
21 select pet.business_group_id
22 from pay_element_templates pet
23 where pet.template_id = p_template_id;
24 --
25 l_proc varchar2(72) := g_package||'get_template_info';
26 l_api_updating boolean;
27 l_valid varchar2(1);
28 --
29 Begin
30 hr_utility.set_location('Entering:'||l_proc, 5);
31 open csr_get_template_info;
32 fetch csr_get_template_info
33 into p_business_group_id;
34 close csr_get_template_info;
35 hr_utility.set_location(' Leaving:'||l_proc, 15);
36 End get_template_info;
37 -- ----------------------------------------------------------------------------
38 -- |-----------------------< chk_non_updateable_args >------------------------|
39 -- ----------------------------------------------------------------------------
40 Procedure chk_non_updateable_args
41 (p_rec in pay_tco_shd.g_rec_type
42 ) is
43 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
44 l_updating boolean;
45 l_error exception;
46 l_argument varchar2(30);
47 l_api_updating boolean;
48 --
49 Begin
50 hr_utility.set_location('Entering:'||l_proc, 5);
51 l_api_updating := pay_tco_shd.api_updating
52 (p_template_core_object_id => p_rec.template_core_object_id
53 ,p_object_version_number => p_rec.object_version_number
54 );
55 if not l_api_updating then
56 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
57 hr_utility.set_message_token('PROCEDURE', l_proc);
58 hr_utility.set_message_token('STEP', '10');
59 hr_utility.raise_error;
60 end if;
61 --
62 hr_utility.set_location(l_proc, 15);
63 --
64 -- p_template_id
65 --
66 if nvl(p_rec.template_id, hr_api.g_number) <>
67 nvl(pay_tco_shd.g_old_rec.template_id, hr_api.g_number)
68 then
69 l_argument := 'p_template_id';
70 raise l_error;
71 end if;
72 --
73 -- p_core_object_type
74 --
75 if nvl(p_rec.core_object_type, hr_api.g_varchar2) <>
76 nvl(pay_tco_shd.g_old_rec.core_object_type, hr_api.g_varchar2)
77 then
78 l_argument := 'p_core_object_type';
79 raise l_error;
80 end if;
81 --
82 -- p_shadow_object_id
83 --
84 if nvl(p_rec.shadow_object_id, hr_api.g_number) <>
85 nvl(pay_tco_shd.g_old_rec.shadow_object_id, hr_api.g_number)
86 then
87 l_argument := 'p_shadow_object_id';
88 raise l_error;
89 end if;
90 hr_utility.set_location(' Leaving:'||l_proc, 20);
91 exception
92 when l_error then
93 hr_utility.set_location('Leaving:'||l_proc, 25);
94 hr_api.argument_changed_error
95 (p_api_name => l_proc
96 ,p_argument => l_argument);
97 when others then
98 hr_utility.set_location('Leaving:'||l_proc, 30);
99 raise;
100 End chk_non_updateable_args;
101 -- ----------------------------------------------------------------------------
102 -- |-------------------------< chk_effective_date >---------------------------|
103 -- ----------------------------------------------------------------------------
104 Procedure chk_effective_date
105 (p_effective_date in date
106 ,p_template_core_object_id in number
107 ,p_object_version_number in number
108 ) is
109 l_proc varchar2(72) := g_package||'chk_effective_date';
110 l_api_updating boolean;
111 --
112 Begin
113 hr_utility.set_location('Entering:'||l_proc, 5);
114 l_api_updating := pay_tco_shd.api_updating
115 (p_template_core_object_id => p_template_core_object_id
116 ,p_object_version_number => p_object_version_number
117 );
118 if (l_api_updating and nvl(p_effective_date, hr_api.g_date) <>
119 nvl(pay_tco_shd.g_old_rec.effective_date, hr_api.g_date)) or
120 not l_api_updating
121 then
122 --
123 -- Check that the effective date is not null.
124 --
125 hr_api.mandatory_arg_error
126 (p_api_name => l_proc
127 ,p_argument => 'p_effective_date'
128 ,p_argument_value => p_effective_date
129 );
130 end if;
131 hr_utility.set_location(' Leaving:'||l_proc, 15);
132 End chk_effective_date;
133 -- ----------------------------------------------------------------------------
134 -- |---------------------------< chk_template_id >----------------------------|
135 -- ----------------------------------------------------------------------------
136 Procedure chk_template_id
137 (p_template_id in number
138 ) is
139 --
140 -- Cursor to check that template_id is valid.
141 --
142 cursor csr_template_id_valid is
143 select null
144 from pay_element_templates pet
145 where pet.template_id = p_template_id
146 and pet.template_type = 'U';
147 --
148 l_proc varchar2(72) := g_package||'chk_template_id';
149 l_valid varchar2(1);
150 --
151 Begin
152 hr_utility.set_location('Entering:'||l_proc, 5);
153 --
154 -- Check that template_id is not null.
155 --
156 hr_api.mandatory_arg_error
157 (p_api_name => l_proc
158 ,p_argument => 'p_template_id'
159 ,p_argument_value => p_template_id
160 );
161 --
162 -- Check that template_id is valid.
163 --
164 open csr_template_id_valid;
165 fetch csr_template_id_valid into l_valid;
166 if csr_template_id_valid%notfound then
167 hr_utility.set_location(' Leaving:'||l_proc, 10);
168 close csr_template_id_valid;
169 fnd_message.set_name('PAY', 'PAY_50065_BAD_USER_TEMPLATE');
170 fnd_message.raise_error;
171 end if;
172 close csr_template_id_valid;
173 hr_utility.set_location(' Leaving:'||l_proc, 15);
174 End chk_template_id;
175 -- ----------------------------------------------------------------------------
176 -- |-----------------------< chk_core_object_type >---------------------------|
177 -- ----------------------------------------------------------------------------
178 Procedure chk_core_object_type
179 (p_effective_date in date
180 ,p_core_object_type in varchar2
181 ) is
182 --
183 l_proc varchar2(72) := g_package||'chk_core_object_type';
184 --
185 Begin
186 hr_utility.set_location('Entering:'||l_proc, 5);
187 --
188 -- Check that the core object type is not null.
189 --
190 hr_api.mandatory_arg_error
191 (p_api_name => l_proc
192 ,p_argument => 'p_core_object_type'
193 ,p_argument_value => p_core_object_type
194 );
195 --
196 -- Validate against hr_lookups.
197 --
198 if hr_api.not_exists_in_hr_lookups
199 (p_effective_date => p_effective_date
200 ,p_lookup_type => 'CORE_OBJECT_TYPE'
201 ,p_lookup_code => p_core_object_type
202 )
203 then
204 hr_utility.set_location(' Leaving:'||l_proc, 10);
205 fnd_message.set_name('PAY', 'HR_52966_INVALID_LOOKUP');
206 fnd_message.set_token('LOOKUP_TYPE', 'CORE_OBJECT_TYPE');
207 fnd_message.set_token('COLUMN', 'CORE_OBJECT_TYPE');
208 fnd_message.raise_error;
209 end if;
210 hr_utility.set_location(' Leaving:'||l_proc, 20);
211 End chk_core_object_type;
212 -- ----------------------------------------------------------------------------
213 -- |-----------------------< chk_shadow_object_id >---------------------------|
214 -- ----------------------------------------------------------------------------
215 Procedure chk_shadow_object_id
216 (p_shadow_object_id in number
217 ,p_core_object_type in varchar2
218 ,p_template_id in number
219 ) is
220 --
221 -- Cursor to check that the combination of shadow_object_id and
222 -- core_object_type is unique.
223 --
224 cursor csr_comb_exists is
225 select null
226 from pay_template_core_objects tco
227 where tco.shadow_object_id = p_shadow_object_id
228 and tco.core_object_type = p_core_object_type;
229 --
230 -- Cursors to check that the shadow_object_id belongs to the template
231 -- specified by template_id.
232 --
233 cursor csr_sf_belongs is
234 select null
235 from pay_shadow_element_types pset
236 where pset.template_id = p_template_id
237 and pset.payroll_formula_id = p_shadow_object_id;
238 --
239 cursor csr_sfiv_belongs is
240 select null
241 from pay_shadow_input_values psiv
242 , pay_shadow_element_types pset
243 where pset.template_id = p_template_id
244 and pset.element_type_id = psiv.element_type_id
245 and psiv.formula_id = p_shadow_object_id;
246 --
247 cursor csr_sbt_belongs is
248 select null
249 from pay_shadow_balance_types sbt
250 where sbt.template_id = p_template_id
251 and sbt.balance_type_id = p_shadow_object_id;
252 --
253 cursor csr_sdb_belongs is
254 select null
255 from pay_shadow_balance_types sbt
256 , pay_shadow_defined_balances sdb
257 where sbt.template_id = p_template_id
258 and sdb.balance_type_id = sbt.balance_type_id
259 and sdb.defined_balance_id = p_shadow_object_id;
260 --
261 cursor csr_set_belongs is
262 select null
263 from pay_shadow_element_types pset
264 where pset.template_id = p_template_id
265 and pset.element_type_id = p_shadow_object_id;
266 --
267 cursor csr_ssr_belongs is
268 select null
269 from pay_shadow_element_types pset
270 , pay_shadow_sub_classi_rules scr
271 where pset.template_id = p_template_id
272 and scr.element_type_id = pset.element_type_id
273 and scr.sub_classification_rule_id = p_shadow_object_id;
274 --
275 cursor csr_sbc_belongs is
276 select null
277 from pay_shadow_balance_types sbt
278 , pay_shadow_balance_classi sbc
279 where sbt.template_id = p_template_id
280 and sbc.balance_type_id = sbt.balance_type_id
281 and sbc.balance_classification_id = p_shadow_object_id;
282 --
283 cursor csr_siv_belongs is
284 select null
285 from pay_shadow_element_types pset
286 , pay_shadow_input_values siv
287 where pset.template_id = p_template_id
288 and siv.element_type_id = pset.element_type_id
289 and siv.input_value_id = p_shadow_object_id;
290 --
291 cursor csr_sbf_belongs is
292 select null
293 from pay_shadow_element_types pset
294 , pay_shadow_input_values siv
295 , pay_shadow_balance_feeds sbf
296 where pset.template_id = p_template_id
297 and siv.element_type_id = pset.element_type_id
298 and sbf.input_value_id = siv.input_value_id
299 and sbf.balance_feed_id = p_shadow_object_id;
300 --
301 cursor csr_sfr_belongs is
302 select null
303 from pay_shadow_element_types pset
304 , pay_shadow_formula_rules sfr
305 where pset.template_id = p_template_id
306 and sfr.shadow_element_type_id = pset.element_type_id
307 and sfr.formula_result_rule_id = p_shadow_object_id;
308 --
309 cursor csr_sir_belongs is
310 select null
311 from pay_shadow_element_types pset
312 , pay_shadow_iterative_rules sir
313 where pset.template_id = p_template_id
314 and sir.element_type_id = pset.element_type_id
315 and sir.iterative_rule_id = p_shadow_object_id;
316 --
317 cursor csr_seu_belongs is
318 select null
319 from pay_shadow_element_types pset
320 , pay_shadow_ele_type_usages etu
321 where pset.template_id = p_template_id
322 and etu.element_type_id = pset.element_type_id
323 and etu.element_type_usage_id = p_shadow_object_id;
324 --
325 cursor csr_sgb_belongs is
326 select null
327 from pay_shadow_element_types pset
328 , pay_shadow_gu_bal_exclusions sgb
329 where pset.template_id = p_template_id
330 and sgb.source_id = pset.element_type_id
331 and sgb.grossup_balances_id = p_shadow_object_id;
332 --
333 cursor csr_sba_belongs is
334 select null
335 from pay_shadow_balance_types sbt
336 , pay_shadow_defined_balances sdb
337 , pay_shadow_bal_attributes sba
338 where sba.balance_attribute_id = p_shadow_object_id
339 and sba.defined_balance_id = sdb.defined_balance_id
340 and sdb.balance_type_id = sbt.balance_type_id
341 and sbt.template_id = p_template_id;
342 --
343 l_proc varchar2(72) := g_package||'chk_shadow_object_id';
344 l_exists varchar(1);
345 l_error exception;
346 l_table varchar2(2000);
347 --
348 Begin
349 hr_utility.set_location('Entering:'||l_proc, 5);
350 --
351 -- Check that shadow_object_id is not null.
352 --
353 hr_api.mandatory_arg_error
354 (p_api_name => l_proc
355 ,p_argument => 'p_shadow_object_id'
356 ,p_argument_value => p_shadow_object_id
357 );
358 --
359 -- Check that the core_object_type and shadow_object_id are a
360 -- unique combination.
361 --
362 open csr_comb_exists;
363 fetch csr_comb_exists into l_exists;
364 if csr_comb_exists%found then
365 hr_utility.set_location(l_proc, 10);
366 close csr_comb_exists;
367 fnd_message.set_name('PAY', 'PAY_50126_TCO_SHAD_OBJ_EXISTS');
368 fnd_message.raise_error;
369 end if;
370 close csr_comb_exists;
371 --
372 -- Check that the shadow_object_id belongs to the template.
373 --
374
375 if p_core_object_type = pay_tco_shd.g_sf_lookup_type then
376 l_table := 'PAY_SHADOW_FORMULAS';
377 --
378 -- Look for element type payroll formula.
379 --
380 open csr_sf_belongs;
381 fetch csr_sf_belongs into l_exists;
382 if csr_sf_belongs%notfound then
383 close csr_sf_belongs;
384 --
385 -- Look for input value formula.
386 --
387 open csr_sfiv_belongs;
388 fetch csr_sfiv_belongs into l_exists;
389 if csr_sfiv_belongs%notfound then
390 close csr_sfiv_belongs;
391 raise l_error;
392 end if;
393 close csr_sfiv_belongs;
394 end if;
395
396 if csr_sf_belongs%isopen then
397 close csr_sf_belongs;
398 end if;
399 elsif p_core_object_type = pay_tco_shd.g_sbt_lookup_type then
400 l_table := 'PAY_SHADOW_BALANCE_TYPES';
401 open csr_sbt_belongs;
402 fetch csr_sbt_belongs into l_exists;
403 if csr_sbt_belongs%notfound then
404 close csr_sbt_belongs;
405 raise l_error;
406 end if;
407 close csr_sbt_belongs;
408 elsif p_core_object_type = pay_tco_shd.g_sdb_lookup_type then
409 l_table := 'PAY_SHADOW_DEFINED_BALANCES';
410 open csr_sdb_belongs;
411 fetch csr_sdb_belongs into l_exists;
412 if csr_sdb_belongs%notfound then
413 close csr_sdb_belongs;
414 raise l_error;
415 end if;
416 close csr_sdb_belongs;
417 elsif p_core_object_type = pay_tco_shd.g_set_lookup_type or
421 open csr_set_belongs;
418 p_core_object_type = pay_tco_shd.g_spr_lookup_type
419 then
420 l_table := 'PAY_SHADOW_ELEMENT_TYPES';
422 fetch csr_set_belongs into l_exists;
423 if csr_set_belongs%notfound then
424 close csr_set_belongs;
425 raise l_error;
426 end if;
427 close csr_set_belongs;
428 elsif p_core_object_type = pay_tco_shd.g_ssr_lookup_type then
429 l_table := 'PAY_SHADOW_SUB_CLASSI_RULES';
430 open csr_ssr_belongs;
431 fetch csr_ssr_belongs into l_exists;
432 if csr_ssr_belongs%notfound then
433 close csr_ssr_belongs;
434 raise l_error;
435 end if;
436 close csr_ssr_belongs;
437 elsif p_core_object_type = pay_tco_shd.g_sbc_lookup_type then
438 l_table := 'PAY_SHADOW_BALANCE_CLASSI';
439 open csr_sbc_belongs;
440 fetch csr_sbc_belongs into l_exists;
441 if csr_sbc_belongs%notfound then
442 close csr_sbc_belongs;
443 raise l_error;
444 end if;
445 close csr_sbc_belongs;
446 elsif p_core_object_type = pay_tco_shd.g_siv_lookup_type then
447 l_table := 'PAY_SHADOW_INPUT_VALUES';
448 open csr_siv_belongs;
449 fetch csr_siv_belongs into l_exists;
450 if csr_siv_belongs%notfound then
451 close csr_siv_belongs;
452 raise l_error;
453 end if;
454 close csr_siv_belongs;
455 elsif p_core_object_type = pay_tco_shd.g_sbf_lookup_type then
456 l_table := 'PAY_SHADOW_BALANCE_FEEDS';
457 open csr_sbf_belongs;
458 fetch csr_sbf_belongs into l_exists;
459 if csr_sbf_belongs%notfound then
460 close csr_sbf_belongs;
461 raise l_error;
462 end if;
463 close csr_sbf_belongs;
464 elsif p_core_object_type = pay_tco_shd.g_sfr_lookup_type then
465 l_table := 'PAY_SHADOW_FORMULA_RULES';
466 open csr_sfr_belongs;
467 fetch csr_sfr_belongs into l_exists;
468 if csr_sfr_belongs%notfound then
469 close csr_sfr_belongs;
470 raise l_error;
471 end if;
472 close csr_sfr_belongs;
473 elsif p_core_object_type = pay_tco_shd.g_sir_lookup_type then
474 l_table := 'PAY_SHADOW_ITERATIVE_RULES';
475 open csr_sir_belongs;
476 fetch csr_sir_belongs into l_exists;
477 if csr_sir_belongs%notfound then
478 close csr_sir_belongs;
479 raise l_error;
480 end if;
481 close csr_sir_belongs;
482 elsif p_core_object_type = pay_tco_shd.g_seu_lookup_type then
483 l_table := 'PAY_SHADOW_ELE_TYPE_USAGES';
484 open csr_seu_belongs;
485 fetch csr_seu_belongs into l_exists;
486 if csr_seu_belongs%notfound then
487 close csr_seu_belongs;
488 raise l_error;
489 end if;
490 close csr_seu_belongs;
491 elsif p_core_object_type = pay_tco_shd.g_sgb_lookup_type then
492 l_table := 'PAY_SHADOW_GU_BAL_EXCLUSIONS';
493 open csr_sgb_belongs;
494 fetch csr_sgb_belongs into l_exists;
495 if csr_sgb_belongs%notfound then
496 close csr_sgb_belongs;
497 raise l_error;
498 end if;
499 close csr_sgb_belongs;
500 elsif p_core_object_type = pay_tco_shd.g_sba_lookup_type then
501 l_table := 'PAY_SHADOW_BAL_ATTRIBUTES';
502 open csr_sba_belongs;
503 fetch csr_sba_belongs into l_exists;
504 if csr_sba_belongs%notfound then
505 close csr_sba_belongs;
506 raise l_error;
507 end if;
508 close csr_sba_belongs;
509 else
510 hr_general.assert_condition(false);
511 end if;
512 hr_utility.set_location(' Leaving:'||l_proc, 15);
513 exception
514 when l_error then
515 hr_utility.set_location(' Leaving:'||l_proc, 20);
516 fnd_message.set_name('PAY', 'PAY_50127_TCO_SHAD_NOT_FOUND');
517 fnd_message.set_token('TABLE', l_table);
518 fnd_message.raise_error;
519 when others then
520 hr_utility.set_location(' Leaving:'||l_proc, 25);
521 raise;
522 End chk_shadow_object_id;
523 -- ----------------------------------------------------------------------------
524 -- |-------------------------< chk_core_object_id >---------------------------|
525 -- ----------------------------------------------------------------------------
526 Procedure chk_core_object_id
527 (p_core_object_id in number
528 ,p_core_object_type in varchar2
529 ,p_effective_date in date
530 ,p_business_group_id in number
531 ,p_template_core_object_id in number
532 ,p_object_version_number in number
533 ) is
534 --
535 -- Cursor to check that the combination of core_object_id and
536 -- core_object_type is unique.
537 --
538 cursor csr_comb_exists is
539 select null
540 from pay_template_core_objects tco
541 where tco.core_object_id = p_core_object_id
542 and tco.core_object_type = p_core_object_type;
543 --
544 -- Cursors to check that the core_object_id exists to the template
545 -- specified by template_id.
546 --
547 cursor csr_sf_exists is
548 select null
549 from ff_formulas_f ff
550 where ff.formula_id = p_core_object_id
551 and ff.business_group_id = p_business_group_id
552 and p_effective_date between
553 ff.effective_start_date and ff.effective_end_date;
554 --
558 where bt.balance_type_id = p_core_object_id
555 cursor csr_sbt_exists is
556 select null
557 from pay_balance_types bt
559 and bt.business_group_id = p_business_group_id;
560 --
561 cursor csr_sdb_exists is
562 select null
563 from pay_defined_balances db
564 where db.defined_balance_id = p_core_object_id
565 and db.business_group_id = p_business_group_id;
566 --
567 cursor csr_set_exists is
568 select null
569 from pay_element_types_f et
570 where et.element_type_id = p_core_object_id
571 and et.business_group_id = p_business_group_id
572 and p_effective_date between
573 et.effective_start_date and et.effective_end_date;
574 --
575 cursor csr_ssr_exists is
576 select null
577 from pay_sub_classification_rules_f scr
578 where scr.sub_classification_rule_id = p_core_object_id
579 and scr.business_group_id = p_business_group_id
580 and p_effective_date between
581 scr.effective_start_date and scr.effective_end_date;
582 --
583 cursor csr_sbc_exists is
584 select null
585 from pay_balance_classifications bc
586 where bc.balance_classification_id = p_core_object_id
587 and bc.business_group_id = p_business_group_id;
588 --
589 cursor csr_siv_exists is
590 select null
591 from pay_input_values_f iv
592 where iv.input_value_id = p_core_object_id
593 and iv.business_group_id = p_business_group_id
594 and p_effective_date between
595 iv.effective_start_date and iv.effective_end_date;
596 --
597 cursor csr_sbf_exists is
598 select null
599 from pay_balance_feeds_f bf
600 where bf.balance_feed_id = p_core_object_id
601 and bf.business_group_id = p_business_group_id
602 and p_effective_date between
603 bf.effective_start_date and bf.effective_end_date;
604 --
605 cursor csr_sfr_exists is
606 select null
607 from pay_formula_result_rules_f frr
608 where frr.formula_result_rule_id = p_core_object_id
609 and frr.business_group_id = p_business_group_id
610 and p_effective_date between
611 frr.effective_start_date and frr.effective_end_date;
612 --
613 cursor csr_spr_exists is
614 select null
615 from pay_status_processing_rules_f spr
616 where spr.status_processing_rule_id = p_core_object_id
617 and spr.business_group_id = p_business_group_id
618 and p_effective_date between
619 spr.effective_start_date and spr.effective_end_date;
620 --
621 cursor csr_sir_exists is
622 select null
623 from pay_iterative_rules_f pir
624 where pir.iterative_rule_id = p_core_object_id
625 and pir.business_group_id = p_business_group_id
626 and p_effective_date between
627 pir.effective_start_date and pir.effective_end_date;
628 --
629 cursor csr_sgb_exists is
630 select null
631 from pay_grossup_bal_exclusions gbe
632 , pay_balance_types pbt
633 where gbe.grossup_balances_id = p_core_object_id
634 and gbe.balance_type_id = pbt.balance_type_id
635 and pbt.business_group_id = p_business_group_id;
636 --
637 cursor csr_seu_exists is
638 select null
639 from pay_element_type_usages_f etu
640 where etu.business_group_id = p_business_group_id
641 and etu.element_type_usage_id = p_core_object_id
642 and p_effective_date between
643 etu.effective_start_date and etu.effective_end_date;
644 --
645 cursor csr_sba_exists is
646 select null
647 from pay_balance_attributes ba
648 where ba.business_group_id = p_business_group_id
649 and ba.balance_attribute_id = p_core_object_id;
650 --
651 l_proc varchar2(72) := g_package||'chk_core_object_id';
652 l_exists varchar(1);
653 l_error exception;
654 l_table varchar2(2000);
655 l_api_updating boolean;
656 --
657 Begin
658 hr_utility.set_location('Entering:'||l_proc, 5);
659 l_api_updating := pay_tco_shd.api_updating
660 (p_template_core_object_id => p_template_core_object_id
661 ,p_object_version_number => p_object_version_number
662 );
663 if (l_api_updating and nvl(p_core_object_id, hr_api.g_number) <>
664 nvl(pay_tco_shd.g_old_rec.core_object_id, hr_api.g_number)) or
665 not l_api_updating
666 then
667 --
668 -- Check that core_object_id is not null.
669 --
670 hr_api.mandatory_arg_error
671 (p_api_name => l_proc
672 ,p_argument => 'p_core_object_id'
673 ,p_argument_value => p_core_object_id
674 );
675 --
676 -- Check that the core_object_type and core_object_id are a
677 -- unique combination.
678 --
679 open csr_comb_exists;
680 fetch csr_comb_exists into l_exists;
681 if csr_comb_exists%found then
682 hr_utility.set_location(l_proc, 10);
683 close csr_comb_exists;
684 fnd_message.set_name('PAY', 'PAY_50124_TCO_CORE_OBJ_EXISTS');
685 fnd_message.raise_error;
686 end if;
687 close csr_comb_exists;
688 --
689 -- Check that the core_object_id exists to the template.
690 --
691 if p_core_object_type = pay_tco_shd.g_sf_lookup_type then
695 if csr_sf_exists%notfound then
692 l_table := 'FF_FORMULAS_F';
693 open csr_sf_exists;
694 fetch csr_sf_exists into l_exists;
696 close csr_sf_exists;
697 raise l_error;
698 end if;
699 close csr_sf_exists;
700 elsif p_core_object_type = pay_tco_shd.g_sbt_lookup_type then
701 l_table := 'PAY_BALANCE_TYPES';
702 open csr_sbt_exists;
703 fetch csr_sbt_exists into l_exists;
704 if csr_sbt_exists%notfound then
705 close csr_sbt_exists;
706 raise l_error;
707 end if;
708 close csr_sbt_exists;
709 elsif p_core_object_type = pay_tco_shd.g_sdb_lookup_type then
710 l_table := 'PAY_DEFINED_BALANCES';
711 open csr_sdb_exists;
712 fetch csr_sdb_exists into l_exists;
713 if csr_sdb_exists%notfound then
714 close csr_sdb_exists;
715 raise l_error;
716 end if;
717 close csr_sdb_exists;
718 elsif p_core_object_type = pay_tco_shd.g_set_lookup_type
719 then
720 l_table := 'PAY_ELEMENT_TYPES_F';
721 open csr_set_exists;
722 fetch csr_set_exists into l_exists;
723 if csr_set_exists%notfound then
724 close csr_set_exists;
725 raise l_error;
726 end if;
727 close csr_set_exists;
728 elsif p_core_object_type = pay_tco_shd.g_ssr_lookup_type then
729 l_table := 'PAY_SUB_CLASSIFICATION_RULES_F';
730 open csr_ssr_exists;
731 fetch csr_ssr_exists into l_exists;
732 if csr_ssr_exists%notfound then
733 close csr_ssr_exists;
734 raise l_error;
735 end if;
736 close csr_ssr_exists;
737 elsif p_core_object_type = pay_tco_shd.g_sbc_lookup_type then
738 l_table := 'PAY_BALANCE_CLASSIFICATIONS';
739 open csr_sbc_exists;
740 fetch csr_sbc_exists into l_exists;
741 if csr_sbc_exists%notfound then
742 close csr_sbc_exists;
743 raise l_error;
744 end if;
745 close csr_sbc_exists;
746 elsif p_core_object_type = pay_tco_shd.g_siv_lookup_type then
747 l_table := 'PAY_INPUT_VALUES_F';
748 open csr_siv_exists;
749 fetch csr_siv_exists into l_exists;
750 if csr_siv_exists%notfound then
751 close csr_siv_exists;
752 raise l_error;
753 end if;
754 close csr_siv_exists;
755 elsif p_core_object_type = pay_tco_shd.g_sbf_lookup_type then
756 l_table := 'PAY_BALANCE_FEEDS_F';
757 open csr_sbf_exists;
758 fetch csr_sbf_exists into l_exists;
759 if csr_sbf_exists%notfound then
760 close csr_sbf_exists;
761 raise l_error;
762 end if;
763 close csr_sbf_exists;
764 elsif p_core_object_type = pay_tco_shd.g_spr_lookup_type then
765 l_table := 'PAY_STATUS_PROCESSING_RULES_F';
766 open csr_spr_exists;
767 fetch csr_spr_exists into l_exists;
768 if csr_spr_exists%notfound then
769 close csr_spr_exists;
770 raise l_error;
771 end if;
772 close csr_spr_exists;
773 elsif p_core_object_type = pay_tco_shd.g_sfr_lookup_type then
774 l_table := 'PAY_FORMULA_RESULT_RULES_F';
775 open csr_sfr_exists;
776 fetch csr_sfr_exists into l_exists;
777 if csr_sfr_exists%notfound then
778 close csr_sfr_exists;
779 raise l_error;
780 end if;
781 close csr_sfr_exists;
782 elsif p_core_object_type = pay_tco_shd.g_sir_lookup_type then
783 l_table := 'PAY_ITERATIVE_RULES_F';
784 open csr_sir_exists;
785 fetch csr_sir_exists into l_exists;
786 if csr_sir_exists%notfound then
787 close csr_sir_exists;
788 raise l_error;
789 end if;
790 close csr_sir_exists;
791 elsif p_core_object_type = pay_tco_shd.g_sgb_lookup_type then
792 l_table := 'PAY_GROSSUP_BAL_EXCLUSIONS';
793 open csr_sgb_exists;
794 fetch csr_sgb_exists into l_exists;
795 if csr_sgb_exists%notfound then
796 close csr_sgb_exists;
797 raise l_error;
798 end if;
799 close csr_sgb_exists;
800 elsif p_core_object_type = pay_tco_shd.g_seu_lookup_type then
801 l_table := 'PAY_ELEMENT_TYPE_USAGES_F';
802 open csr_seu_exists;
803 fetch csr_seu_exists into l_exists;
804 if csr_seu_exists%notfound then
805 close csr_seu_exists;
806 raise l_error;
807 end if;
808 close csr_seu_exists;
809 elsif p_core_object_type = pay_tco_shd.g_sba_lookup_type then
810 l_table := 'PAY_BALANCE_ATTRIBUTES';
811 open csr_sba_exists;
812 fetch csr_sba_exists into l_exists;
813 if csr_sba_exists%notfound then
814 close csr_sba_exists;
815 raise l_error;
816 end if;
817 close csr_sba_exists;
818 else
819 hr_general.assert_condition(false);
820 end if;
821 end if;
822 hr_utility.set_location(' Leaving:'||l_proc, 15);
823 exception
824 when l_error then
825 hr_utility.set_location(' Leaving:'||l_proc, 20);
826 fnd_message.set_name('PAY', 'PAY_50125_TCO_CORE_NOT_FOUND');
827 fnd_message.set_token('TABLE', l_table);
828 fnd_message.raise_error;
829 when others then
830 hr_utility.set_location(' Leaving:'||l_proc, 25);
831 raise;
832 End chk_core_object_id;
833 -- ----------------------------------------------------------------------------
834 -- |---------------------------< insert_validate >----------------------------|
835 -- ----------------------------------------------------------------------------
836 Procedure insert_validate(p_rec in pay_tco_shd.g_rec_type) is
837 --
838 l_proc varchar2(72) := g_package||'insert_validate';
839 l_business_group_id number;
840 --
841 Begin
842 hr_utility.set_location('Entering:'||l_proc, 5);
843 --
844 -- Call all supporting business operations
845 --
846 chk_template_id(p_template_id => p_rec.template_id);
847 --
848 get_template_info
849 (p_template_id => p_rec.template_id
850 ,p_business_group_id => l_business_group_id
851 );
852 --
853 chk_effective_date
854 (p_effective_date => p_rec.effective_date
855 ,p_template_core_object_id => p_rec.template_core_object_id
856 ,p_object_version_number => p_rec.object_version_number
857 );
858 --
859 chk_core_object_type
860 (p_effective_date => p_rec.effective_date
861 ,p_core_object_type => p_rec.core_object_type
862 );
863 --
864 chk_shadow_object_id
865 (p_shadow_object_id => p_rec.shadow_object_id
866 ,p_core_object_type => p_rec.core_object_type
867 ,p_template_id => p_rec.template_id
868 );
869 --
870 chk_core_object_id
871 (p_core_object_id => p_rec.core_object_id
872 ,p_core_object_type => p_rec.core_object_type
873 ,p_effective_date => p_rec.effective_date
874 ,p_business_group_id => l_business_group_id
875 ,p_template_core_object_id => p_rec.template_core_object_id
876 ,p_object_version_number => p_rec.object_version_number
877 );
878 hr_utility.set_location(' Leaving:'||l_proc, 10);
879 End insert_validate;
880 --
881 -- ----------------------------------------------------------------------------
882 -- |---------------------------< update_validate >----------------------------|
883 -- ----------------------------------------------------------------------------
884 Procedure update_validate(p_rec in pay_tco_shd.g_rec_type) is
885 --
886 l_proc varchar2(72) := g_package||'update_validate';
887 l_business_group_id number;
888 --
889 Begin
890 hr_utility.set_location('Entering:'||l_proc, 5);
891 --
892 -- Call all supporting business operations
893 --
894 chk_non_updateable_args(p_rec);
895 --
896 get_template_info
897 (p_template_id => p_rec.template_id
898 ,p_business_group_id => l_business_group_id
899 );
900 --
901 chk_effective_date
902 (p_effective_date => p_rec.effective_date
903 ,p_template_core_object_id => p_rec.template_core_object_id
904 ,p_object_version_number => p_rec.object_version_number
905 );
906 --
907 chk_core_object_id
908 (p_core_object_id => p_rec.core_object_id
909 ,p_core_object_type => p_rec.core_object_type
910 ,p_effective_date => p_rec.effective_date
911 ,p_business_group_id => l_business_group_id
912 ,p_template_core_object_id => p_rec.template_core_object_id
913 ,p_object_version_number => p_rec.object_version_number
914 );
915 hr_utility.set_location(' Leaving:'||l_proc, 10);
916 End update_validate;
917 --
918 -- ----------------------------------------------------------------------------
919 -- |---------------------------< delete_validate >----------------------------|
920 -- ----------------------------------------------------------------------------
921 Procedure delete_validate(p_rec in pay_tco_shd.g_rec_type) is
922 --
923 l_proc varchar2(72) := g_package||'delete_validate';
924 --
925 Begin
926 hr_utility.set_location('Entering:'||l_proc, 5);
927 --
928 -- Call all supporting business operations
929 --
930 hr_utility.set_location(' Leaving:'||l_proc, 10);
931 End delete_validate;
932 --
933 end pay_tco_bus;