[Home] [Help]
PACKAGE BODY: APPS.PAY_ELEMENT_TEMPLATE_UTIL
Source
1 Package Body pay_element_template_util as
2 /* $Header: pyetmutl.pkb 120.0 2005/05/29 04:42:49 appldev noship $ */
3 ----------------
4 -- Exceptions --
5 ----------------
6 plsql_value_error exception;
7 pragma exception_init(plsql_value_error, -6502);
8 -----------------------
9 -- Package Variables --
10 -----------------------
11 g_package varchar2(33) := ' pay_element_template_util.';
12 -- -----------------------------------------------------------------------
13 -- |------------------------< get_template_type >-----------------|
14 -- -----------------------------------------------------------------------
15 function get_template_type(p_template_id in number) return varchar2 is
16 cursor get_template_type is
17 select template_type from pay_element_templates pet
18 where pet.template_id = p_template_id;
19 --
20 l_template_type varchar2(2000);
21 begin
22 open get_template_type;
23 fetch get_template_type into l_template_type;
24 if get_template_type%notfound then
25 l_template_type := null;
26 end if;
27 close get_template_type;
28 return l_template_type;
29 end get_template_type;
30 -- -----------------------------------------------------------------------
31 -- |----------------------< busgrp_in_legislation >-------------------|
32 -- -----------------------------------------------------------------------
33 function busgrp_in_legislation
34 (p_business_group_id in number
35 ,p_legislation_code in varchar2
36 ) return boolean is
37 cursor csr_busgrp_in_legislation
38 (p_business_group_id in number ,p_legislation_code in varchar2) is
39 select 'Y'
40 from per_business_groups_perf
41 where business_group_id = p_business_group_id
42 and legislation_code = p_legislation_code;
43 l_ret boolean := true;
44 l_exists varchar2(1);
45 begin
46 open csr_busgrp_in_legislation(p_business_group_id, p_legislation_code);
47 fetch csr_busgrp_in_legislation into l_exists;
48 if csr_busgrp_in_legislation%notfound then
49 l_ret := false;
50 end if;
51 close csr_busgrp_in_legislation;
52 return l_ret;
53 exception
54 when others then
55 if csr_busgrp_in_legislation%isopen then
56 close csr_busgrp_in_legislation;
57 end if;
58 raise;
59 end busgrp_in_legislation;
60 -- ---------------------------------------------------------------------------
61 -- |-----------------------------< hr_mb_substrb >--------------------------|
62 -- ---------------------------------------------------------------------------
63 function hr_mb_substrb
64 (p_char in varchar2
65 ,p_m in number
66 ,p_n in number default null
67 ) return varchar2 is
68 l_n number;
69 l_strblen number;
70 l_strclen number;
71 l_str varchar2(32767);
72 begin
73 --
74 -- Make sure we use integer portion of p_n.
75 --
76 l_n := trunc(p_n);
77 --
78 -- Get the string portion starting at character position p_m.
79 --
80 l_str := substr(p_char, p_m);
81 --
82 -- If l_n is null or greater than the length of l_str then return l_str.
83 --
84 if l_n is null or l_n >= lengthb(l_str) then
85 return l_str;
86 end if;
87 --
88 -- If l_n <= 0 then return null.
89 --
90 if l_n <= 0 then
91 return null;
92 end if;
93 --
94 -- An n-byte multibyte string can have at most n characters, so
95 -- starting with an n character return string strip off 1 character
96 -- at a time from the end until the return string fits into n bytes.
97 --
98 l_strclen := l_n;
99 l_str := substr(l_str, 1, l_strclen);
100 l_strblen := lengthb(l_str);
101 while l_strblen > l_n loop
102 --
103 -- Exit when string is short enough.
104 --
105 exit when l_strblen <= l_n;
106 --
107 -- Strip off a character.
108 --
109 l_strclen := l_strclen - 1;
110 --
111 -- Exhausted all the characters from the string, so return null.
112 -- For example, l_n = 1 and a single character multibyte string
113 -- is actually 2 bytes long.
114 --
115 if l_strclen = 0 then
116 return null;
117 end if;
118 l_str := substr(l_str, 1, l_strclen);
119 l_strblen := lengthb(l_str);
120 end loop;
121 return substr(l_str, 1, l_strclen);
122 end hr_mb_substrb;
123 -- ----------------------------------------------------------------------------
124 -- |------------------------< flush_plsql_template >--------------------------|
125 -- ----------------------------------------------------------------------------
126 procedure flush_plsql_template
127 (p_element_template in out nocopy pay_etm_shd.g_rec_type
128 ,p_core_objects in out nocopy t_core_objects
129 ,p_exclusion_rules in out nocopy t_exclusion_rules
130 ,p_formulas in out nocopy t_formulas
131 ,p_balance_types in out nocopy t_balance_types
132 ,p_defined_balances in out nocopy t_defined_balances
133 ,p_element_types in out nocopy t_element_types
134 ,p_sub_classi_rules in out nocopy t_sub_classi_rules
135 ,p_balance_classis in out nocopy t_balance_classis
136 ,p_input_values in out nocopy t_input_values
137 ,p_balance_feeds in out nocopy t_balance_feeds
138 ,p_formula_rules in out nocopy t_formula_rules
139 ,p_iterative_rules in out nocopy t_iterative_rules
140 ,p_ele_type_usages in out nocopy t_ele_type_usages
141 ,p_gu_bal_exclusions in out nocopy t_gu_bal_exclusions
142 ,p_bal_attributes in out nocopy t_bal_attributes
143 ,p_template_ff_usages in out nocopy t_template_ff_usages
144 ) is
145 l_proc varchar2(72) := g_package||'flush_plsql_template';
146 begin
147 hr_utility.set_location('Entering:'|| l_proc, 10);
148 p_element_template := null;
149 p_core_objects.delete;
150 p_exclusion_rules.delete;
151 p_element_types.delete;
152 p_input_values.delete;
153 p_formulas.delete;
154 p_formula_rules.delete;
155 p_balance_types.delete;
156 p_balance_feeds.delete;
157 p_defined_balances.delete;
158 p_balance_classis.delete;
159 p_sub_classi_rules.delete;
160 p_iterative_rules.delete;
161 p_ele_type_usages.delete;
162 p_gu_bal_exclusions.delete;
163 p_bal_attributes.delete;
164 p_template_ff_usages.delete;
165 hr_utility.set_location(' Leaving:'||l_proc, 20);
166 end flush_plsql_template;
167 -- ----------------------------------------------------------------------------
168 -- |------------------------< get_element_template >--------------------------|
169 -- ----------------------------------------------------------------------------
170 procedure get_element_template
171 (p_template_id in number
172 ,p_element_template out nocopy pay_etm_shd.g_rec_type
173 ) is
174 l_proc varchar2(72) := g_package||'get_element_template';
175 --
176 cursor csr_element_template(p_template_id in number) is
177 select
178 template_id,
179 template_type,
180 template_name,
181 base_processing_priority,
182 business_group_id,
183 legislation_code,
184 version_number,
185 base_name,
186 max_base_name_length,
187 preference_info_category,
188 preference_information1,
189 preference_information2,
190 preference_information3,
191 preference_information4,
192 preference_information5,
193 preference_information6,
194 preference_information7,
195 preference_information8,
196 preference_information9,
197 preference_information10,
198 preference_information11,
199 preference_information12,
200 preference_information13,
201 preference_information14,
202 preference_information15,
203 preference_information16,
204 preference_information17,
205 preference_information18,
206 preference_information19,
207 preference_information20,
208 preference_information21,
209 preference_information22,
210 preference_information23,
211 preference_information24,
212 preference_information25,
213 preference_information26,
214 preference_information27,
215 preference_information28,
216 preference_information29,
217 preference_information30,
218 configuration_info_category,
219 configuration_information1,
220 configuration_information2,
221 configuration_information3,
222 configuration_information4,
223 configuration_information5,
224 configuration_information6,
225 configuration_information7,
226 configuration_information8,
227 configuration_information9,
228 configuration_information10,
229 configuration_information11,
230 configuration_information12,
231 configuration_information13,
232 configuration_information14,
233 configuration_information15,
234 configuration_information16,
235 configuration_information17,
236 configuration_information18,
237 configuration_information19,
238 configuration_information20,
239 configuration_information21,
240 configuration_information22,
241 configuration_information23,
242 configuration_information24,
243 configuration_information25,
244 configuration_information26,
245 configuration_information27,
246 configuration_information28,
247 configuration_information29,
248 configuration_information30,
249 object_version_number
250 from pay_element_templates
251 where template_id = p_template_id
252 for update of template_id;
253 --
254 begin
255 hr_utility.set_location('Entering:'|| l_proc, 10);
256 open csr_element_template(p_template_id);
257 fetch csr_element_template into p_element_template;
258 if csr_element_template%notfound then
259 close csr_element_template;
260 --
261 -- The template_id is invalid therefore we must error
262 --
263 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
264 hr_utility.raise_error;
265 end if;
266 close csr_element_template;
267 hr_utility.set_location('Leaving:'|| l_proc, 20);
268 exception
269 when others then
270 hr_utility.set_location('Leaving:'|| l_proc, 30);
271 if csr_element_template%isopen then
272 close csr_element_template;
273 end if;
274 raise;
275 end get_element_template;
276 -- ----------------------------------------------------------------------------
277 -- |-------------------------< get_core_objects >-----------------------------|
278 -- ----------------------------------------------------------------------------
279 procedure get_core_objects
280 (p_template_id in number
281 ,p_core_objects in out nocopy pay_element_template_util.t_core_objects
282 ) is
283 l_proc varchar2(72) := g_package||'get_core_objects';
284 --
285 cursor csr_core_objects(p_template_id in number) is
286 select
287 template_core_object_id,
288 template_id,
289 core_object_type,
290 core_object_id,
291 shadow_object_id,
292 effective_date,
293 object_version_number
294 from pay_template_core_objects
295 where template_id = p_template_id
296 order by core_object_type
297 for update of core_object_id;
298 begin
299 --
300 -- Get the template core objects for the template.
301 --
302 for crec in csr_core_objects(p_template_id) loop
303 p_core_objects(crec.core_object_id) := crec;
304 end loop;
305 end get_core_objects;
306 -- ----------------------------------------------------------------------------
307 -- |------------------------< get_exclusion_rules >---------------------------|
308 -- ----------------------------------------------------------------------------
309 procedure get_exclusion_rules
310 (p_template_id in number
311 ,p_exclusion_rules out nocopy t_exclusion_rules
312 ) is
313 l_proc varchar2(72) := g_package||'get_exclusion_rules';
314 --
315 cursor csr_exclusion_rules(p_template_id in number) is
316 select
317 exclusion_rule_id,
318 template_id,
319 flexfield_column,
320 exclusion_value,
321 description,
322 object_version_number
323 from pay_template_exclusion_rules
324 where template_id = p_template_id
325 for update of exclusion_rule_id;
326 --
327 begin
328 for crec in csr_exclusion_rules(p_template_id) loop
329 p_exclusion_rules(crec.exclusion_rule_id) := crec;
330 end loop;
331 end get_exclusion_rules;
332 -- ----------------------------------------------------------------------------
333 -- |--------------------------< get_element_types >---------------------------|
334 -- ----------------------------------------------------------------------------
335 procedure get_element_types
336 (p_template_id in number
337 ,p_element_types out nocopy t_element_types
338 ) is
339 l_proc varchar2(72) := g_package||'get_element_types';
340 --
341 cursor csr_element_types(p_template_id in number) is
342 select
343 element_type_id,
344 template_id,
345 classification_name,
346 additional_entry_allowed_flag,
347 adjustment_only_flag,
348 closed_for_entry_flag,
349 element_name,
350 indirect_only_flag,
351 multiple_entries_allowed_flag,
352 multiply_value_flag,
353 post_termination_rule,
354 process_in_run_flag,
355 relative_processing_priority,
356 processing_type,
357 standard_link_flag,
358 input_currency_code,
359 output_currency_code,
360 benefit_classification_name,
361 description,
362 qualifying_age,
363 qualifying_length_of_service,
364 qualifying_units,
365 reporting_name,
366 attribute_category,
367 attribute1,
368 attribute2,
369 attribute3,
370 attribute4,
371 attribute5,
372 attribute6,
373 attribute7,
374 attribute8,
375 attribute9,
376 attribute10,
377 attribute11,
378 attribute12,
379 attribute13,
380 attribute14,
381 attribute15,
382 attribute16,
383 attribute17,
384 attribute18,
385 attribute19,
386 attribute20,
387 element_information_category,
388 element_information1,
389 element_information2,
390 element_information3,
391 element_information4,
392 element_information5,
393 element_information6,
394 element_information7,
395 element_information8,
396 element_information9,
397 element_information10,
398 element_information11,
399 element_information12,
400 element_information13,
401 element_information14,
402 element_information15,
403 element_information16,
404 element_information17,
405 element_information18,
406 element_information19,
407 element_information20,
408 third_party_pay_only_flag,
409 skip_formula,
410 payroll_formula_id,
411 exclusion_rule_id,
412 iterative_flag,
413 iterative_priority,
414 iterative_formula_name,
415 process_mode,
416 grossup_flag,
420 process_advance_entry,
417 advance_indicator,
418 advance_payable,
419 advance_deduction,
421 proration_group,
422 proration_formula,
423 recalc_event_group,
424 once_each_period_flag,
425 object_version_number
426 from pay_shadow_element_types
427 where template_id = p_template_id
428 for update of element_type_id;
429 begin
430 for crec in csr_element_types(p_template_id) loop
431 p_element_types(crec.element_type_id) := crec;
432 end loop;
433 end get_element_types;
434 -- ----------------------------------------------------------------------------
435 -- |--------------------------< get_balance_types >---------------------------|
436 -- ----------------------------------------------------------------------------
437 procedure get_balance_types
438 (p_template_id in number
439 ,p_balance_types out nocopy t_balance_types
440 ) is
441 l_proc varchar2(72) := g_package||'get_balance_types';
442 --
443 cursor csr_balance_types(p_template_id in number) is
444 select
445 balance_type_id,
446 template_id,
447 assignment_remuneration_flag,
448 balance_name,
449 balance_uom,
450 currency_code,
451 comments,
452 reporting_name,
453 attribute_category,
454 attribute1,
455 attribute2,
456 attribute3,
457 attribute4,
458 attribute5,
459 attribute6,
460 attribute7,
461 attribute8,
462 attribute9,
463 attribute10,
464 attribute11,
465 attribute12,
466 attribute13,
467 attribute14,
468 attribute15,
469 attribute16,
470 attribute17,
471 attribute18,
472 attribute19,
473 attribute20,
474 jurisdiction_level,
475 tax_type,
476 exclusion_rule_id,
477 object_version_number,
478 category_name,
479 base_balance_type_id,
480 base_balance_name,
481 input_value_id
482 from pay_shadow_balance_types
483 where template_id = p_template_id
484 for update of balance_type_id;
485 begin
486 for crec in csr_balance_types(p_template_id) loop
487 p_balance_types(crec.balance_type_id) := crec;
488 end loop;
489 end get_balance_types;
490 -- ----------------------------------------------------------------------------
491 -- |-----------------------------< get_formulas >-----------------------------|
492 -- ----------------------------------------------------------------------------
493 procedure get_formulas
494 (p_template_id in number
495 ,p_formulas in out nocopy t_formulas
496 ) is
497 l_proc varchar2(72) := g_package||'get_formulas';
498 --
499 cursor csr_set_formulas(p_template_id in number) is
500 select f.formula_id
501 , f.template_type
502 , f.legislation_code
503 , f.business_group_id
504 , f.formula_name
505 , f.description
506 , f.formula_text
507 , f.formula_type_name
508 , f.object_version_number
509 from pay_shadow_formulas f
510 , pay_shadow_element_types et
511 where et.template_id = p_template_id
512 and et.payroll_formula_id is not null
513 and f.formula_id = et.payroll_formula_id
514 for update of f.formula_id;
515 --
516 cursor csr_siv_formulas(p_template_id in number) is
517 select f.formula_id
518 , f.template_type
519 , f.legislation_code
520 , f.business_group_id
521 , f.formula_name
522 , f.description
523 , f.formula_text
524 , f.formula_type_name
525 , f.object_version_number
526 from pay_shadow_formulas f
527 , pay_shadow_element_types et
528 , pay_shadow_input_values iv
529 where et.template_id = p_template_id
530 and et.element_type_id = iv.element_type_id
531 and iv.formula_id is not null
532 and f.formula_id = iv.formula_id
533 for update of f.formula_id;
534 --
535 cursor csr_tfu_formulas(p_template_id in number) is
536 select f.formula_id
537 , f.template_type
538 , f.legislation_code
539 , f.business_group_id
540 , f.formula_name
541 , f.description
542 , f.formula_text
543 , f.formula_type_name
544 , f.object_version_number
545 from pay_shadow_formulas f
546 , pay_template_ff_usages tfu
547 where tfu.template_id = p_template_id
548 and f.formula_id = tfu.formula_id
549 for update of f.formula_id;
550 begin
551 hr_utility.set_location('Entering:'|| l_proc, 10);
552
553 --
554 -- Element payroll formulas.
555 --
556 for crec in csr_set_formulas(p_template_id) loop
557 p_formulas(crec.formula_id) := crec;
558 end loop;
559
560 --
561 -- Input validation formulas.
562 --
563 for crec in csr_siv_formulas(p_template_id) loop
564 p_formulas(crec.formula_id) := crec;
565 end loop;
566
567 --
568 -- Template formula usages.
569 --
570 for crec in csr_tfu_formulas(p_template_id) loop
574 hr_utility.set_location('Leaving:'|| l_proc, 20);
571 p_formulas(crec.formula_id) := crec;
572 end loop;
573
575 exception
576 --
577 -- Catch exception for overflow of the 32k buffer when reading from
578 -- the LONG formula text column.
579 --
580 when plsql_value_error then
581 hr_utility.set_location('Leaving:'|| l_proc, 30);
582 fnd_message.set_name(801, 'PAY_50068_ETM_GEN_FF_TOO_LONG');
583 fnd_message.set_token('BASE_NAME', '');
584 fnd_message.set_token('LENGTH', 32767);
585 fnd_message.raise_error;
586 when others then
587 hr_utility.set_location('Leaving:'|| l_proc, 40);
588 raise;
589 end get_formulas;
590 -- ----------------------------------------------------------------------------
591 -- |---------------------------< get_balance_feeds >--------------------------|
592 -- ----------------------------------------------------------------------------
593 procedure get_balance_feeds
594 (p_input_values in t_input_values
595 ,p_balance_feeds out nocopy t_balance_feeds
596 ) is
597 l_proc varchar2(72) := g_package||'get_balance_feeds';
598 l_input_value_id number;
599 i number;
600 --
601 cursor csr_balance_feeds(p_input_value_id in number) is
602 select
603 balance_feed_id,
604 balance_type_id,
605 input_value_id,
606 scale,
607 balance_name,
608 exclusion_rule_id,
609 object_version_number
610 from pay_shadow_balance_feeds
611 where input_value_id = p_input_value_id
612 for update of balance_feed_id;
613 begin
614 --
615 -- Exit if no balance types in the PL/SQL element template.
616 --
617 if p_input_values.count = 0 then
618 return;
619 end if;
620 --
621 -- For each input value get the balance feeds.
622 --
623 i := p_input_values.first;
624 loop
625 exit when not p_input_values.exists(i);
626 --
627 l_input_value_id := p_input_values(i).input_value_id;
628 for crec in csr_balance_feeds(l_input_value_id) loop
629 p_balance_feeds(crec.balance_feed_id) := crec;
630 end loop;
631 --
632 i := p_input_values.next(i);
633 end loop;
634 end get_balance_feeds;
635 -- ----------------------------------------------------------------------------
636 -- |------------------------< get_defined_balances >--------------------------|
637 -- ----------------------------------------------------------------------------
638 procedure get_defined_balances
639 (p_balance_types in t_balance_types
640 ,p_defined_balances out nocopy t_defined_balances
641 ) is
642 l_proc varchar2(72) := g_package||'get_defined_balances';
643 l_balance_type_id number;
644 i number;
645 --
646 cursor csr_defined_balances(p_balance_type_id in number) is
647 select
648 defined_balance_id,
649 balance_type_id,
650 dimension_name,
651 force_latest_balance_flag,
652 grossup_allowed_flag,
653 object_version_number,
654 exclusion_rule_id
655 from pay_shadow_defined_balances
656 where balance_type_id = p_balance_type_id
657 for update of defined_balance_id;
658 begin
659 --
660 -- Exit if no balance types in the PL/SQL element template.
661 --
662 if p_balance_types.count = 0 then
663 return;
664 end if;
665 --
666 -- For each balance type get the defined balances.
667 --
668 i := p_balance_types.first;
669 loop
670 exit when not p_balance_types.exists(i);
671 --
672 l_balance_type_id := p_balance_types(i).balance_type_id;
673 for crec in csr_defined_balances(l_balance_type_id) loop
674 p_defined_balances(crec.defined_balance_id) := crec;
675 end loop;
676 --
677 i := p_balance_types.next(i);
678 end loop;
679 end get_defined_balances;
680 -- ----------------------------------------------------------------------------
681 -- |------------------------< get_balance_classis >---------------------------|
682 -- ----------------------------------------------------------------------------
683 procedure get_balance_classis
684 (p_balance_types in t_balance_types
685 ,p_balance_classis out nocopy t_balance_classis
686 ) is
687 l_proc varchar2(72) := g_package||'get_balance_classis';
688 l_balance_type_id number;
689 i number;
690 --
691 cursor csr_balance_classis(p_balance_type_id in number) is
692 select
693 balance_classification_id,
694 balance_type_id,
695 element_classification,
696 scale,
697 object_version_number,
698 exclusion_rule_id
699 from pay_shadow_balance_classi
700 where balance_type_id = p_balance_type_id
701 for update of balance_classification_id;
702 begin
703 --
704 -- Exit if no balance types in the PL/SQL element template.
705 --
706 if p_balance_types.count = 0 then
707 return;
708 end if;
709 --
710 -- For each balance type get the balance classifications.
711 --
712 i := p_balance_types.first;
713 loop
714 exit when not p_balance_types.exists(i);
715 --
719 end loop;
716 l_balance_type_id := p_balance_types(i).balance_type_id;
717 for crec in csr_balance_classis(l_balance_type_id) loop
718 p_balance_classis(crec.balance_classification_id) := crec;
720 --
721 i := p_balance_types.next(i);
722 end loop;
723 end get_balance_classis;
724 -- ----------------------------------------------------------------------------
725 -- |-------------------------< get_input_values >-----------------------------|
726 -- ----------------------------------------------------------------------------
727 procedure get_input_values
728 (p_element_types in t_element_types
729 ,p_input_values out nocopy t_input_values
730 ) is
731 l_proc varchar2(72) := g_package||'get_input_values';
732 l_element_type_id number;
733 i number;
734 --
735 cursor csr_input_values(p_element_type_id in number) is
736 select
737 input_value_id,
738 element_type_id,
739 display_sequence,
740 generate_db_items_flag,
741 hot_default_flag,
742 mandatory_flag,
743 name,
744 uom,
745 lookup_type,
746 default_value,
747 max_value,
748 min_value,
749 warning_or_error,
750 default_value_column,
751 exclusion_rule_id,
752 formula_id,
753 input_validation_formula,
754 object_version_number
755 from pay_shadow_input_values
756 where element_type_id = p_element_type_id
757 for update of input_value_id;
758 begin
759 --
760 -- Exit if no element types in the PL/SQL element template.
761 --
762 if p_element_types.count = 0 then
763 return;
764 end if;
765 --
766 -- For each element type get the input values.
767 --
768 i := p_element_types.first;
769 loop
770 exit when not p_element_types.exists(i);
771 --
772 l_element_type_id := p_element_types(i).element_type_id;
773 for crec in csr_input_values(l_element_type_id) loop
774 p_input_values(crec.input_value_id) := crec;
775 end loop;
776 --
777 i := p_element_types.next(i);
778 end loop;
779 end get_input_values;
780 -- ----------------------------------------------------------------------------
781 -- |-------------------------< get_formula_rules >----------------------------|
782 -- ----------------------------------------------------------------------------
783 procedure get_formula_rules
784 (p_element_types in t_element_types
785 ,p_formula_rules out nocopy t_formula_rules
786 ) is
787 l_proc varchar2(72) := g_package||'get_formula_rules';
788 l_element_type_id number;
789 i number;
790 --
791 cursor csr_formula_rules(p_element_type_id in number) is
792 select
793 formula_result_rule_id,
794 shadow_element_type_id,
795 element_type_id,
796 result_name,
797 result_rule_type,
798 severity_level,
799 input_value_id,
800 exclusion_rule_id,
801 object_version_number,
802 element_name
803 from pay_shadow_formula_rules
804 where shadow_element_type_id = p_element_type_id
805 for update of formula_result_rule_id;
806 begin
807 --
808 -- Exit if no element types in the PL/SQL element template.
809 --
810 if p_element_types.count = 0 then
811 return;
812 end if;
813 --
814 -- For each element type get the formula rules.
815 --
816 i := p_element_types.first;
817 loop
818 exit when not p_element_types.exists(i);
819 --
820 l_element_type_id := p_element_types(i).element_type_id;
821 for crec in csr_formula_rules(l_element_type_id) loop
822 p_formula_rules(crec.formula_result_rule_id) := crec;
823 end loop;
824 --
825 i := p_element_types.next(i);
826 end loop;
827 end get_formula_rules;
828 -- ----------------------------------------------------------------------------
829 -- |-------------------------< get_sub_classi_rules >-------------------------|
830 -- ----------------------------------------------------------------------------
831 procedure get_sub_classi_rules
832 (p_element_types in t_element_types
833 ,p_sub_classi_rules out nocopy t_sub_classi_rules
834 ) is
835 l_proc varchar2(72) := g_package||'get_sub_classi_rules';
836 l_element_type_id number;
837 i number;
838 --
839 cursor csr_sub_classi_rules(p_element_type_id in number) is
840 select
841 sub_classification_rule_id,
842 element_type_id,
843 element_classification,
844 object_version_number,
845 exclusion_rule_id
846 from pay_shadow_sub_classi_rules
847 where element_type_id = p_element_type_id
848 for update of sub_classification_rule_id;
849 begin
850 --
851 -- Exit if no element types in the PL/SQL element template.
852 --
853 if p_element_types.count = 0 then
854 return;
855 end if;
856 --
857 -- For each element type get the sub-classification rules.
858 --
859 i := p_element_types.first;
860 loop
864 for crec in csr_sub_classi_rules(l_element_type_id) loop
861 exit when not p_element_types.exists(i);
862 --
863 l_element_type_id := p_element_types(i).element_type_id;
865 p_sub_classi_rules(crec.sub_classification_rule_id) := crec;
866 end loop;
867 --
868 i := p_element_types.next(i);
869 end loop;
870 end get_sub_classi_rules;
871 -- ----------------------------------------------------------------------------
872 -- |-------------------------< get_iterative_rules >----------------------------|
873 -- ----------------------------------------------------------------------------
874 procedure get_iterative_rules
875 (p_element_types in t_element_types
876 ,p_iterative_rules out nocopy t_iterative_rules
877 ) is
878 --
879 l_proc varchar2(72) := g_package||'get_iterative_rules';
880 l_element_type_id number;
881 i number;
882 --
883 cursor csr_iterative_rules(p_element_type_id in number) is
884 select
885 iterative_rule_id,
886 element_type_id,
887 result_name,
888 iterative_rule_type,
889 input_value_id,
890 severity_level,
891 exclusion_rule_id,
892 object_version_number
893 from pay_shadow_iterative_rules
894 where element_type_id = p_element_type_id
895 for update of iterative_rule_id;
896 begin
897 --
898 -- Exit if no element types in the PL/SQL element template.
899 --
900 if p_element_types.count = 0 then
901 return;
902 end if;
903 --
904 -- For each element type get the iterative rules.
905 --
906 i := p_element_types.first;
907 loop
908 exit when not p_element_types.exists(i);
909 --
910 l_element_type_id := p_element_types(i).element_type_id;
911 for crec in csr_iterative_rules(l_element_type_id) loop
912 p_iterative_rules(crec.iterative_rule_id) := crec;
913 end loop;
914 --
915 i := p_element_types.next(i);
916 end loop;
917 end get_iterative_rules;
918 -- ----------------------------------------------------------------------------
919 -- |-----------------------< get_ele_type_usages >----------------------------|
920 -- ----------------------------------------------------------------------------
921 procedure get_ele_type_usages
922 (p_element_types in t_element_types
923 ,p_ele_type_usages out nocopy t_ele_type_usages
924 ) is
925 l_proc varchar2(72) := g_package||'get_ele_type_usages';
926 l_element_type_id number;
927 i number;
928 --
929 cursor csr_ele_type_usages(p_element_type_id in number) is
930 select
931 element_type_usage_id,
932 element_type_id,
933 inclusion_flag,
934 run_type_name,
935 exclusion_rule_id,
936 object_version_number
937 from pay_shadow_ele_type_usages
938 where element_type_id = p_element_type_id
939 for update of element_type_usage_id;
940 begin
941 --
942 -- Exit if no element types in the PL/SQL element template.
943 --
944 if p_element_types.count = 0 then
945 return;
946 end if;
947 --
948 -- For each element type get the element type usages.
949 --
950 i := p_element_types.first;
951 loop
952 exit when not p_element_types.exists(i);
953 --
954 l_element_type_id := p_element_types(i).element_type_id;
955 for crec in csr_ele_type_usages(l_element_type_id) loop
956 p_ele_type_usages(crec.element_type_usage_id) := crec;
957 end loop;
958 --
959 i := p_element_types.next(i);
960 end loop;
961 end get_ele_type_usages;
962 -- ----------------------------------------------------------------------------
963 -- |------------------------< get_gu_bal_exclusions>--------------------------|
964 -- ----------------------------------------------------------------------------
965 procedure get_gu_bal_exclusions
966 (p_element_types in t_element_types
967 ,p_gu_bal_exclusions out nocopy t_gu_bal_exclusions
968 ) is
969 --
970 l_proc varchar2(72) := g_package||'get_gu_bal_exclusions';
971 l_element_type_id number;
972 i number;
973 --
974 cursor csr_gu_bal_exclusions(p_element_type_id in number) is
975 select
976 grossup_balances_id,
977 source_id,
978 source_type,
979 balance_type_name,
980 balance_type_id,
981 exclusion_rule_id,
982 object_version_number
983 from pay_shadow_gu_bal_exclusions
984 where source_id = p_element_type_id
985 for update of grossup_balances_id;
986 begin
987 --
988 -- Exit if no element types in the PL/SQL element template.
989 --
990 if p_element_types.count = 0 then
991 return;
992 end if;
993 --
994 -- For each element type get the grossup balance exclusions.
995 --
996 i := p_element_types.first;
997 loop
998 exit when not p_element_types.exists(i);
999 --
1000 l_element_type_id := p_element_types(i).element_type_id;
1004 --
1001 for crec in csr_gu_bal_exclusions(l_element_type_id) loop
1002 p_gu_bal_exclusions(crec.grossup_balances_id) := crec;
1003 end loop;
1005 i := p_element_types.next(i);
1006 end loop;
1007 end get_gu_bal_exclusions;
1008 -- ----------------------------------------------------------------------------
1009 -- |-------------------------< get_bal_attributes >---------------------------|
1010 -- ----------------------------------------------------------------------------
1011 procedure get_bal_attributes
1012 (p_defined_balances in t_defined_balances
1013 ,p_bal_attributes out nocopy t_bal_attributes
1014 ) is
1015 l_proc varchar2(72) := g_package||'get_bal_attributes';
1016 l_defined_balance_id number;
1017 i number;
1018 --
1019 cursor csr_bal_attributes(p_defined_balance_id in number) is
1020 select
1021 balance_attribute_id,
1022 attribute_name,
1023 defined_balance_id,
1024 object_version_number,
1025 exclusion_rule_id
1026 from pay_shadow_bal_attributes
1027 where defined_balance_id = p_defined_balance_id
1028 for update of balance_attribute_id;
1029 begin
1030 hr_utility.set_location('Entering:'|| l_proc, 10);
1031 --
1032 -- Exit if no balance types in the PL/SQL element template.
1033 --
1034 if p_defined_balances.count = 0 then
1035 hr_utility.set_location('Leaving:'|| l_proc, 20);
1036 return;
1037 end if;
1038 --
1039 -- For each balance type get the defined balances.
1040 --
1041 i := p_defined_balances.first;
1042 loop
1043 exit when not p_defined_balances.exists(i);
1044 --
1045 l_defined_balance_id := p_defined_balances(i).defined_balance_id;
1046 for crec in csr_bal_attributes(l_defined_balance_id) loop
1047 p_bal_attributes(crec.balance_attribute_id) := crec;
1048 end loop;
1049 --
1050 i := p_defined_balances.next(i);
1051 end loop;
1052 hr_utility.set_location('Leaving:'|| l_proc, 30);
1053 end get_bal_attributes;
1054 -- ----------------------------------------------------------------------------
1055 -- |-----------------------< get_template_ff_usages >-------------------------|
1056 -- ----------------------------------------------------------------------------
1057 procedure get_template_ff_usages
1058 (p_template_id in number
1059 ,p_template_ff_usages out nocopy t_template_ff_usages
1060 ) is
1061 l_proc varchar2(72) := g_package||'get_template_ff_usages';
1062 l_rec pay_tfu_shd.g_rec_type;
1063 l_tfu_id binary_integer;
1064 --
1065 cursor csr_template_ff_usages(p_template_id in number) is
1066 select
1067 template_ff_usage_id,
1068 template_id,
1069 formula_id,
1070 object_id,
1071 object_version_number,
1072 exclusion_rule_id
1073 from pay_template_ff_usages
1074 where template_id = p_template_id
1075 for update of template_ff_usage_id;
1076 begin
1077 hr_utility.set_location('Entering:'|| l_proc, 10);
1078 for crec in csr_template_ff_usages(p_template_id => p_template_id) loop
1079 p_template_ff_usages(crec.template_ff_usage_id) := crec;
1080 end loop;
1081 hr_utility.set_location('Leaving:'|| l_proc, 20);
1082 end get_template_ff_usages;
1083 -- ----------------------------------------------------------------------------
1084 -- |----------------------------< exclusion_on >------------------------------|
1085 -- ----------------------------------------------------------------------------
1086 function exclusion_on
1087 (p_element_template in pay_etm_shd.g_rec_type
1088 ,p_rec in pay_ter_shd.g_rec_type
1089 ) return boolean is
1090 l_proc varchar2(72) := g_package||'exclusion_on';
1091 l_exclusion_on boolean;
1092 begin
1093 hr_utility.set_location('Entering:'|| l_proc, 10);
1094 l_exclusion_on := false;
1095 if upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION1' and
1096 p_element_template.configuration_information1 =
1097 p_rec.exclusion_value then
1098 l_exclusion_on := true;
1099 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION2' and
1100 p_element_template.configuration_information2 =
1101 p_rec.exclusion_value then
1102 l_exclusion_on := true;
1103 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION3' and
1104 p_element_template.configuration_information3 =
1105 p_rec.exclusion_value then
1106 l_exclusion_on := true;
1107 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION4' and
1108 p_element_template.configuration_information4 =
1109 p_rec.exclusion_value then
1110 l_exclusion_on := true;
1111 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION5' and
1112 p_element_template.configuration_information5 =
1113 p_rec.exclusion_value then
1114 l_exclusion_on := true;
1115 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION6' and
1116 p_element_template.configuration_information6 =
1117 p_rec.exclusion_value then
1118 l_exclusion_on := true;
1119 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION7' and
1120 p_element_template.configuration_information7 =
1121 p_rec.exclusion_value then
1122 l_exclusion_on := true;
1126 l_exclusion_on := true;
1123 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION8' and
1124 p_element_template.configuration_information8 =
1125 p_rec.exclusion_value then
1127 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION9' and
1128 p_element_template.configuration_information9 =
1129 p_rec.exclusion_value then
1130 l_exclusion_on := true;
1131 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION10' and
1132 p_element_template.configuration_information10 =
1133 p_rec.exclusion_value then
1134 l_exclusion_on := true;
1135 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION11' and
1136 p_element_template.configuration_information11 =
1137 p_rec.exclusion_value then
1138 l_exclusion_on := true;
1139 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION12' and
1140 p_element_template.configuration_information12 =
1141 p_rec.exclusion_value then
1142 l_exclusion_on := true;
1143 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION13' and
1144 p_element_template.configuration_information13 =
1145 p_rec.exclusion_value then
1146 l_exclusion_on := true;
1147 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION14' and
1148 p_element_template.configuration_information14 =
1149 p_rec.exclusion_value then
1150 l_exclusion_on := true;
1151 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION15' and
1152 p_element_template.configuration_information15 =
1153 p_rec.exclusion_value then
1154 l_exclusion_on := true;
1155 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION16' and
1156 p_element_template.configuration_information16 =
1157 p_rec.exclusion_value then
1158 l_exclusion_on := true;
1159 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION17' and
1160 p_element_template.configuration_information17 =
1161 p_rec.exclusion_value then
1162 l_exclusion_on := true;
1163 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION18' and
1164 p_element_template.configuration_information18 =
1165 p_rec.exclusion_value then
1166 l_exclusion_on := true;
1167 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION19' and
1168 p_element_template.configuration_information19 =
1169 p_rec.exclusion_value then
1170 l_exclusion_on := true;
1171 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION20' and
1172 p_element_template.configuration_information20 =
1173 p_rec.exclusion_value then
1174 l_exclusion_on := true;
1175 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION21' and
1176 p_element_template.configuration_information21 =
1177 p_rec.exclusion_value then
1178 l_exclusion_on := true;
1179 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION22' and
1180 p_element_template.configuration_information22 =
1181 p_rec.exclusion_value then
1182 l_exclusion_on := true;
1183 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION23' and
1184 p_element_template.configuration_information23 =
1185 p_rec.exclusion_value then
1186 l_exclusion_on := true;
1187 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION24' and
1188 p_element_template.configuration_information24 =
1189 p_rec.exclusion_value then
1190 l_exclusion_on := true;
1191 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION25' and
1192 p_element_template.configuration_information25 =
1193 p_rec.exclusion_value then
1194 l_exclusion_on := true;
1195 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION26' and
1196 p_element_template.configuration_information26 =
1197 p_rec.exclusion_value then
1198 l_exclusion_on := true;
1199 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION27' and
1200 p_element_template.configuration_information27 =
1201 p_rec.exclusion_value then
1202 l_exclusion_on := true;
1203 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION28' and
1204 p_element_template.configuration_information28 =
1205 p_rec.exclusion_value then
1206 l_exclusion_on := true;
1207 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION29' and
1208 p_element_template.configuration_information29 =
1209 p_rec.exclusion_value then
1210 l_exclusion_on := true;
1211 elsif upper(p_rec.flexfield_column) = 'CONFIGURATION_INFORMATION30' and
1212 p_element_template.configuration_information30 =
1213 p_rec.exclusion_value then
1214 l_exclusion_on := true;
1215 end if;
1216 hr_utility.set_location('Leaving:'|| l_proc, 20);
1217 return l_exclusion_on;
1218 end exclusion_on;
1219 -- ----------------------------------------------------------------------------
1220 -- |-----------------------< exclude_defined_balance >------------------------|
1221 -- ----------------------------------------------------------------------------
1222 procedure exclude_defined_balance
1223 (p_i in number
1224 ,p_defined_balances in out nocopy t_defined_balances
1225 ,p_bal_attributes in out nocopy t_bal_attributes
1226 ) is
1227 l_proc varchar2(72) := g_package||'exclude_defined_balance';
1228 l_defined_balance_id number;
1229 i number;
1230 j number;
1231 begin
1232 hr_utility.set_location('Entering:'|| l_proc, 10);
1236 l_defined_balance_id := p_defined_balances(p_i).defined_balance_id;
1233 --
1234 -- Get the defined_balance_id for this defined balance.
1235 --
1237 --
1238 -- Delete the defined balance.
1239 --
1240 p_defined_balances.delete(p_i);
1241 --
1242 -- Exclude any associated balance attributes.
1243 --
1244 i := p_bal_attributes.first;
1245 loop
1246 exit when not p_bal_attributes.exists(i);
1247 --
1248 if p_bal_attributes(i).defined_balance_id = l_defined_balance_id then
1249 j := i;
1250 i := p_bal_attributes.next(i);
1251 p_bal_attributes.delete(j);
1252 else
1253 i := p_bal_attributes.next(i);
1254 end if;
1255 --
1256 end loop;
1257 hr_utility.set_location('Leaving:'|| l_proc, 20);
1258 end exclude_defined_balance;
1259 -- ----------------------------------------------------------------------------
1260 -- |-------------------------< exclude_balance_type >-------------------------|
1261 -- ----------------------------------------------------------------------------
1262 procedure exclude_balance_type
1263 (p_i in number
1264 ,p_balance_types in out nocopy t_balance_types
1265 ,p_defined_balances in out nocopy t_defined_balances
1266 ,p_balance_classis in out nocopy t_balance_classis
1267 ,p_balance_feeds in out nocopy t_balance_feeds
1268 ,p_gu_bal_exclusions in out nocopy t_gu_bal_exclusions
1269 ,p_bal_attributes in out nocopy t_bal_attributes
1270 ) is
1271 l_proc varchar2(72) := g_package||'exclude_balance_type';
1272 l_balance_type_id number;
1273 i number;
1274 j number;
1275 l_remaining_balances t_balance_types;
1276 begin
1277 hr_utility.set_location('Entering:'|| l_proc, 10);
1278 --
1279 -- Confirm that this balance has not already been excluded.
1280 --
1281 if not p_balance_types.exists(p_i) then
1282 return;
1283 end if;
1284 --
1285 -- Get the balance_type_id for this balance type.
1286 --
1287 l_balance_type_id := p_balance_types(p_i).balance_type_id;
1288 --
1289 -- Delete the balance type.
1290 --
1291 p_balance_types.delete(p_i);
1292 --
1293 -- Exclude any associated balance feeds.
1294 --
1295 i := p_balance_feeds.first;
1296 loop
1297 exit when not p_balance_feeds.exists(i);
1298 --
1299 if p_balance_feeds(i).balance_type_id is not null and
1300 p_balance_feeds(i).balance_type_id = l_balance_type_id then
1301 j := i;
1302 i := p_balance_feeds.next(i);
1303 p_balance_feeds.delete(j);
1304 else
1305 i := p_balance_feeds.next(i);
1306 end if;
1307 --
1308 end loop;
1309 --
1310 -- Exclude any associated defined balances.
1311 --
1312 i := p_defined_balances.first;
1313 loop
1314 exit when not p_defined_balances.exists(i);
1315 --
1316 if p_defined_balances(i).balance_type_id = l_balance_type_id then
1317 j := i;
1318 i := p_defined_balances.next(i);
1319 exclude_defined_balance
1320 (p_i => j
1321 ,p_defined_balances => p_defined_balances
1322 ,p_bal_attributes => p_bal_attributes
1323 );
1324 else
1325 i := p_defined_balances.next(i);
1326 end if;
1327 --
1328 end loop;
1329 --
1330 -- Exclude any balance classifications.
1331 --
1332 i := p_balance_classis.first;
1333 loop
1334 exit when not p_balance_classis.exists(i);
1335 --
1336 if p_balance_classis(i).balance_type_id = l_balance_type_id then
1337 j := i;
1338 i := p_balance_classis.next(i);
1339 p_balance_classis.delete(j);
1340 else
1341 i := p_balance_classis.next(i);
1342 end if;
1343 --
1344 end loop;
1345 --
1346 -- Exclude any associated grossup balance exclusions.
1347 --
1348 i := p_gu_bal_exclusions.first;
1349 loop
1350 exit when not p_gu_bal_exclusions.exists(i);
1351 --
1352 if p_gu_bal_exclusions(i).balance_type_id is not null and
1353 p_gu_bal_exclusions(i).balance_type_id = l_balance_type_id then
1354 j := i;
1355 i := p_gu_bal_exclusions.next(i);
1356 p_gu_bal_exclusions.delete(j);
1357 else
1358 i := p_gu_bal_exclusions.next(i);
1359 end if;
1360 --
1361 end loop;
1362 --
1363 -- Recursively exclude any child balances.
1364 --
1365 l_remaining_balances := p_balance_types;
1366 i := p_balance_types.first;
1367 loop
1368 exit when not p_balance_types.exists(i);
1369 --
1370 if p_balance_types(i).base_balance_type_id is not null and
1371 p_balance_types(i).base_balance_type_id = l_balance_type_id then
1372 --
1373 exclude_balance_type
1374 (p_i => i
1375 ,p_balance_types => l_remaining_balances
1376 ,p_defined_balances => p_defined_balances
1377 ,p_balance_classis => p_balance_classis
1378 ,p_balance_feeds => p_balance_feeds
1379 ,p_gu_bal_exclusions => p_gu_bal_exclusions
1380 ,p_bal_attributes => p_bal_attributes
1381 );
1382 end if;
1383 --
1384 i := p_balance_types.next(i);
1385 --
1389 --
1386 end loop;
1387 --
1388 p_balance_types := l_remaining_balances;
1390 hr_utility.set_location('Leaving:'|| l_proc, 20);
1391 end exclude_balance_type;
1392 -- ----------------------------------------------------------------------------
1393 -- |-------------------------< exclude_input_value >--------------------------|
1394 -- ----------------------------------------------------------------------------
1395 procedure exclude_input_value
1396 (p_i in number
1397 ,p_input_values in out nocopy t_input_values
1398 ,p_balance_feeds in out nocopy t_balance_feeds
1399 ,p_formula_rules in out nocopy t_formula_rules
1400 ,p_iterative_rules in out nocopy t_iterative_rules
1401 ,p_balance_types in out nocopy t_balance_types
1402 ,p_defined_balances in out nocopy t_defined_balances
1403 ,p_balance_classis in out nocopy t_balance_classis
1404 ,p_gu_bal_exclusions in out nocopy t_gu_bal_exclusions
1405 ,p_bal_attributes in out nocopy t_bal_attributes
1406 ) is
1407 l_proc varchar2(72) := g_package||'exclude_input_value';
1408 l_input_value_id number;
1409 i number;
1410 j number;
1411 l_remaining_balances t_balance_types;
1412 begin
1413 hr_utility.set_location('Entering:'|| l_proc, 10);
1414 --
1415 -- Get the input_value_id for this input value.
1416 --
1417 l_input_value_id := p_input_values(p_i).input_value_id;
1418 --
1419 -- Delete the input value.
1420 --
1421 p_input_values.delete(p_i);
1422 --
1423 -- Exclude any associated formula result rules.
1424 --
1425 i := p_formula_rules.first;
1426 loop
1427 exit when not p_formula_rules.exists(i);
1428 --
1429 if p_formula_rules(i).input_value_id is not null and
1430 p_formula_rules(i).input_value_id = l_input_value_id then
1431 j := i;
1432 i := p_formula_rules.next(i);
1433 p_formula_rules.delete(j);
1434 else
1435 i := p_formula_rules.next(i);
1436 end if;
1437 --
1438 end loop;
1439 --
1440 -- Exclude any associated balance feeds.
1441 --
1442 i := p_balance_feeds.first;
1443 loop
1444 exit when not p_balance_feeds.exists(i);
1445 --
1446 if p_balance_feeds(i).input_value_id = l_input_value_id then
1447 j := i;
1448 i := p_balance_feeds.next(i);
1449 p_balance_feeds.delete(j);
1450 else
1451 i := p_balance_feeds.next(i);
1452 end if;
1453 --
1454 end loop;
1455 --
1456 -- Exclude any associated iterative rules.
1457 --
1458 i := p_iterative_rules.first;
1459 loop
1460 exit when not p_iterative_rules.exists(i);
1461 --
1462 if p_iterative_rules(i).input_value_id = l_input_value_id then
1463 j := i;
1464 i := p_iterative_rules.next(i);
1465 p_iterative_rules.delete(j);
1466 else
1467 i := p_iterative_rules.next(i);
1468 end if;
1469 --
1470 end loop;
1471 --
1472 -- Exclude the balance types that reference this input value.
1473 --
1474 l_remaining_balances := p_balance_types;
1475 i := p_balance_types.first;
1476 loop
1477 exit when not p_balance_types.exists(i);
1478 --
1479 if p_balance_types(i).input_value_id = l_input_value_id then
1480 exclude_balance_type
1481 (p_i => i
1482 ,p_balance_types => l_remaining_balances
1483 ,p_defined_balances => p_defined_balances
1484 ,p_balance_classis => p_balance_classis
1485 ,p_balance_feeds => p_balance_feeds
1486 ,p_gu_bal_exclusions => p_gu_bal_exclusions
1487 ,p_bal_attributes => p_bal_attributes
1488 );
1489 end if;
1490 --
1491 i := p_balance_types.next(i);
1492 --
1493 end loop;
1494 --
1495 p_balance_types := l_remaining_balances;
1496 --
1497 hr_utility.set_location('Leaving:'|| l_proc, 20);
1498 end exclude_input_value;
1499 -- ----------------------------------------------------------------------------
1500 -- |-------------------------< exclude_element_type >-------------------------|
1501 -- ----------------------------------------------------------------------------
1502 procedure exclude_element_type
1503 (p_i in number
1504 ,p_element_types in out nocopy t_element_types
1505 ,p_sub_classi_rules in out nocopy t_sub_classi_rules
1506 ,p_input_values in out nocopy t_input_values
1507 ,p_balance_feeds in out nocopy t_balance_feeds
1508 ,p_formula_rules in out nocopy t_formula_rules
1509 ,p_iterative_rules in out nocopy t_iterative_rules
1510 ,p_ele_type_usages in out nocopy t_ele_type_usages
1511 ,p_balance_types in out nocopy t_balance_types
1512 ,p_defined_balances in out nocopy t_defined_balances
1513 ,p_balance_classis in out nocopy t_balance_classis
1514 ,p_gu_bal_exclusions in out nocopy t_gu_bal_exclusions
1515 ,p_bal_attributes in out nocopy t_bal_attributes
1516 ,p_template_ff_usages in out nocopy t_template_ff_usages
1517 ) is
1518 l_proc varchar2(72) := g_package||'exclude_element_type';
1522 begin
1519 l_element_type_id number;
1520 i number;
1521 j number;
1523 hr_utility.set_location('Entering:'|| l_proc, 10);
1524 --
1525 -- Get the element_type_id for this element type.
1526 --
1527 l_element_type_id := p_element_types(p_i).element_type_id;
1528 --
1529 -- Delete the element type.
1530 --
1531 p_element_types.delete(p_i);
1532 --
1533 -- Exclude any associated formula result rules.
1534 --
1535 i := p_formula_rules.first;
1536 loop
1537 exit when not p_formula_rules.exists(i);
1538 --
1539 if (p_formula_rules(i).shadow_element_type_id = l_element_type_id) or
1540 (p_formula_rules(i).element_type_id is not null and
1541 p_formula_rules(i).element_type_id = l_element_type_id) then
1542 j := i;
1543 i := p_formula_rules.next(i);
1544 p_formula_rules.delete(j);
1545 else
1546 i := p_formula_rules.next(i);
1547 end if;
1548 --
1549 end loop;
1550 --
1551 -- Exclude any associated input values.
1552 --
1553 i := p_input_values.first;
1554 loop
1555 exit when not p_input_values.exists(i);
1556 --
1557 if p_input_values(i).element_type_id = l_element_type_id then
1558 j := i;
1559 i := p_input_values.next(i);
1560 exclude_input_value
1561 (p_i => j
1562 ,p_input_values => p_input_values
1563 ,p_balance_feeds => p_balance_feeds
1564 ,p_formula_rules => p_formula_rules
1565 ,p_iterative_rules => p_iterative_rules
1566 ,p_balance_types => p_balance_types
1567 ,p_defined_balances => p_defined_balances
1568 ,p_balance_classis => p_balance_classis
1569 ,p_gu_bal_exclusions => p_gu_bal_exclusions
1570 ,p_bal_attributes => p_bal_attributes
1571 );
1572 else
1573 i := p_input_values.next(i);
1574 end if;
1575 --
1576 end loop;
1577 --
1578 -- Exclude any associated sub-classification rules.
1579 --
1580 i := p_sub_classi_rules.first;
1581 loop
1582 exit when not p_sub_classi_rules.exists(i);
1583 --
1584 if p_sub_classi_rules(i).element_type_id = l_element_type_id then
1585 j := i;
1586 i := p_sub_classi_rules.next(i);
1587 p_sub_classi_rules.delete(j);
1588 else
1589 i := p_sub_classi_rules.next(i);
1590 end if;
1591 --
1592 end loop;
1593 --
1594 -- Exclude any associated iterative rules.
1595 --
1596 i := p_iterative_rules.first;
1597 loop
1598 exit when not p_iterative_rules.exists(i);
1599 --
1600 if p_iterative_rules(i).element_type_id = l_element_type_id then
1601 j := i;
1602 i := p_iterative_rules.next(i);
1603 p_iterative_rules.delete(j);
1604 else
1605 i := p_iterative_rules.next(i);
1606 end if;
1607 --
1608 end loop;
1609 --
1610 -- Exclude any associated element type usages.
1611 --
1612 i := p_ele_type_usages.first;
1613 loop
1614 exit when not p_ele_type_usages.exists(i);
1615 --
1616 if p_ele_type_usages(i).element_type_id = l_element_type_id then
1617 j := i;
1618 i := p_ele_type_usages.next(i);
1619 p_ele_type_usages.delete(j);
1620 else
1621 i := p_ele_type_usages.next(i);
1622 end if;
1623 --
1624 end loop;
1625 --
1626 -- Exclude any associated grossup balance exclusions.
1627 --
1628 i := p_gu_bal_exclusions.first;
1629 loop
1630 exit when not p_gu_bal_exclusions.exists(i);
1631 --
1632 if p_gu_bal_exclusions(i).source_id = l_element_type_id then
1633 j := i;
1634 i := p_gu_bal_exclusions.next(i);
1635 p_gu_bal_exclusions.delete(j);
1636 else
1637 i := p_gu_bal_exclusions.next(i);
1638 end if;
1639 --
1640 end loop;
1641 --
1642 -- Exclude any associated template formula usages.
1643 --
1644 i := p_template_ff_usages.first;
1645 loop
1646 exit when not p_template_ff_usages.exists(i);
1647 --
1648 if p_template_ff_usages(i).object_id = l_element_type_id then
1649 j := i;
1650 i := p_template_ff_usages.next(i);
1651 p_template_ff_usages.delete(j);
1652 else
1653 i := p_template_ff_usages.next(i);
1654 end if;
1655 --
1656 end loop;
1657 hr_utility.set_location('Leaving:'|| l_proc, 20);
1658 end exclude_element_type;
1659 -- ----------------------------------------------------------------------------
1660 -- |--------------------------< exclude_formulas >----------------------------|
1661 -- ----------------------------------------------------------------------------
1662 procedure exclude_formulas
1663 (p_formulas in out nocopy t_formulas
1664 ,p_element_types in out nocopy t_element_types
1665 ,p_input_values in out nocopy t_input_values
1666 ) is
1667 l_proc varchar2(72) := g_package||'exclude_formulas';
1668 l_formula_id number;
1672 begin
1669 l_formula_not_used boolean;
1670 i number;
1671 j number;
1673 hr_utility.set_location('Entering:'|| l_proc, 10);
1674 i := p_formulas.first;
1675 loop
1676 exit when not p_formulas.exists(i);
1677 --
1678 l_formula_id := p_formulas(i).formula_id;
1679 l_formula_not_used := true;
1680 --
1681 -- Check to see if the formula is used by any element types.
1682 --
1683 j := p_element_types.first;
1684 loop
1685 exit when not p_element_types.exists(j);
1686 --
1687 if p_element_types(j).payroll_formula_id = l_formula_id then
1688 l_formula_not_used := false;
1689 exit;
1690 end if;
1691 --
1692 j := p_element_types.next(j);
1693 end loop;
1694 --
1695 -- Check to see if the formula is used by any input values.
1696 --
1697 if l_formula_not_used then
1698 j := p_input_values.first;
1699 loop
1700 exit when not p_input_values.exists(j);
1701 --
1702 if p_input_values(j).formula_id = l_formula_id then
1703 l_formula_not_used := false;
1704 exit;
1705 end if;
1706 --
1707 j := p_input_values.next(j);
1708 end loop;
1709 end if;
1710 --
1711 if l_formula_not_used then
1712 j := i;
1713 i := p_formulas.next(i);
1714 p_formulas.delete(j);
1715 else
1716 i := p_formulas.next(i);
1717 end if;
1718 end loop;
1719 --
1720 hr_utility.set_location('Leaving:'|| l_proc, 50);
1721 end exclude_formulas;
1722 -- ----------------------------------------------------------------------------
1723 -- |----------------------------< apply_ff_usages >---------------------------|
1724 -- ----------------------------------------------------------------------------
1725 procedure apply_ff_usages
1726 (p_template_ff_usages in out nocopy t_template_ff_usages
1727 ,p_element_types in out nocopy t_element_types
1728 ) is
1729 i number;
1730 j number;
1731 k number;
1732 l_applied t_element_types;
1733 begin
1734 i := p_template_ff_usages.first;
1735 loop
1736 exit when not p_template_ff_usages.exists(i);
1737 --
1738 -- Check to see if the formula is used by any element types.
1739 --
1740 j := p_element_types.first;
1741 loop
1742 exit when not p_element_types.exists(j);
1743 --
1744 if p_element_types(j).element_type_id = p_template_ff_usages(i).object_id
1745 then
1746 if l_applied.exists(j) then
1747 --
1748 -- A formula usage has already been applied to this element. This
1749 -- means that the exclusion rules were not applied correctly.
1750 --
1751 fnd_message.set_name(801, 'PAY_50206_MULTIPLE_FF_USAGES');
1752 fnd_message.set_token('TABLE', 'PAY_SHADOW_ELEMENT_TYPES');
1753 fnd_message.raise_error;
1754 else
1755 p_element_types(j).payroll_formula_id :=
1756 p_template_ff_usages(i).formula_id;
1757 l_applied(j) := p_element_types(j);
1758 end if;
1759 end if;
1760 --
1761 j := p_element_types.next(j);
1762 end loop;
1763 --
1764 i := p_template_ff_usages.next(i);
1765 end loop;
1766 --
1767 -- All the formula usages have been successfully applied now.
1768 --
1769 p_template_ff_usages.delete;
1770 end apply_ff_usages;
1771 -- ----------------------------------------------------------------------------
1772 -- |-----------------------< apply_exclusion_rules >--------------------------|
1773 -- ----------------------------------------------------------------------------
1774 procedure apply_exclusion_rules
1775 (p_element_template in pay_etm_shd.g_rec_type
1776 ,p_exclusion_rules in t_exclusion_rules
1777 ,p_formulas in out nocopy t_formulas
1778 ,p_balance_types in out nocopy t_balance_types
1779 ,p_defined_balances in out nocopy t_defined_balances
1780 ,p_element_types in out nocopy t_element_types
1781 ,p_sub_classi_rules in out nocopy t_sub_classi_rules
1782 ,p_balance_classis in out nocopy t_balance_classis
1783 ,p_input_values in out nocopy t_input_values
1784 ,p_balance_feeds in out nocopy t_balance_feeds
1785 ,p_formula_rules in out nocopy t_formula_rules
1786 ,p_iterative_rules in out nocopy t_iterative_rules
1787 ,p_ele_type_usages in out nocopy t_ele_type_usages
1788 ,p_gu_bal_exclusions in out nocopy t_gu_bal_exclusions
1789 ,p_bal_attributes in out nocopy t_bal_attributes
1790 ,p_template_ff_usages in out nocopy t_template_ff_usages
1791 ) is
1792 l_proc varchar2(72) := g_package||'apply_exclusion_rules';
1793 i number;
1794 j number;
1795 k number;
1796 l_exclusion_rule_id number;
1797 l_remaining_balances t_balance_types;
1798 begin
1799 hr_utility.set_location('Entering:'|| l_proc, 10);
1800 i := p_exclusion_rules.first;
1801 loop
1805 --
1802 exit when not p_exclusion_rules.exists(i);
1803 if exclusion_on(p_element_template, p_exclusion_rules(i)) then
1804 l_exclusion_rule_id := p_exclusion_rules(i).exclusion_rule_id;
1806 -- Exclude element types.
1807 --
1808 j := p_element_types.first;
1809 loop
1810 exit when not p_element_types.exists(j);
1811 --
1812 if p_element_types(j).exclusion_rule_id = l_exclusion_rule_id then
1813 k := j;
1814 j := p_element_types.next(j);
1815 exclude_element_type
1816 (p_i => k
1817 ,p_element_types => p_element_types
1818 ,p_sub_classi_rules => p_sub_classi_rules
1819 ,p_input_values => p_input_values
1820 ,p_balance_feeds => p_balance_feeds
1821 ,p_formula_rules => p_formula_rules
1822 ,p_iterative_rules => p_iterative_rules
1823 ,p_ele_type_usages => p_ele_type_usages
1824 ,p_balance_types => p_balance_types
1825 ,p_defined_balances => p_defined_balances
1826 ,p_balance_classis => p_balance_classis
1827 ,p_gu_bal_exclusions => p_gu_bal_exclusions
1828 ,p_bal_attributes => p_bal_attributes
1829 ,p_template_ff_usages => p_template_ff_usages
1830 );
1831 else
1832 j := p_element_types.next(j);
1833 end if;
1834 --
1835 end loop;
1836 --
1837 -- Exclude input values. Moved ahead of balance types because
1838 -- balance types have an input_value_id column.
1839 --
1840 j := p_input_values.first;
1841 loop
1842 exit when not p_input_values.exists(j);
1843 --
1844 if p_input_values(j).exclusion_rule_id = l_exclusion_rule_id then
1845 k := j;
1846 j := p_input_values.next(j);
1847 exclude_input_value
1848 (p_i => k
1849 ,p_input_values => p_input_values
1850 ,p_balance_feeds => p_balance_feeds
1851 ,p_formula_rules => p_formula_rules
1852 ,p_iterative_rules => p_iterative_rules
1853 ,p_balance_types => p_balance_types
1854 ,p_defined_balances => p_defined_balances
1855 ,p_balance_classis => p_balance_classis
1856 ,p_gu_bal_exclusions => p_gu_bal_exclusions
1857 ,p_bal_attributes => p_bal_attributes
1858 );
1859 else
1860 j := p_input_values.next(j);
1861 end if;
1862 --
1863 end loop;
1864 --
1865 -- Exclude balance types.
1866 --
1867 l_remaining_balances := p_balance_types;
1868 j := p_balance_types.first;
1869 loop
1870 exit when not p_balance_types.exists(j);
1871 --
1872 if p_balance_types(j).exclusion_rule_id = l_exclusion_rule_id then
1873 exclude_balance_type
1874 (p_i => j
1875 ,p_balance_types => l_remaining_balances
1876 ,p_defined_balances => p_defined_balances
1877 ,p_balance_classis => p_balance_classis
1878 ,p_balance_feeds => p_balance_feeds
1879 ,p_gu_bal_exclusions => p_gu_bal_exclusions
1880 ,p_bal_attributes => p_bal_attributes
1881 );
1882 end if;
1883 --
1884 j := p_balance_types.next(j);
1885 --
1886 end loop;
1887 --
1888 p_balance_types := l_remaining_balances;
1889 --
1890 -- Exclude formula rules.
1891 --
1892 j := p_formula_rules.first;
1893 loop
1894 exit when not p_formula_rules.exists(j);
1895 --
1896 if p_formula_rules(j).exclusion_rule_id = l_exclusion_rule_id then
1897 k := j;
1898 j := p_formula_rules.next(j);
1899 p_formula_rules.delete(k);
1900 else
1901 j := p_formula_rules.next(j);
1902 end if;
1903 --
1904 end loop;
1905 --
1906 -- Exclude balance feeds.
1907 --
1908 j := p_balance_feeds.first;
1909 loop
1910 exit when not p_balance_feeds.exists(j);
1911 --
1912 if p_balance_feeds(j).exclusion_rule_id = l_exclusion_rule_id then
1913 k := j;
1914 j := p_balance_feeds.next(j);
1915 p_balance_feeds.delete(k);
1916 else
1917 j := p_balance_feeds.next(j);
1918 end if;
1919 --
1920 end loop;
1921 --
1922 -- Exclude balance classifications.
1923 --
1924 j := p_balance_classis.first;
1925 loop
1926 exit when not p_balance_classis.exists(j);
1927 --
1928 if p_balance_classis(j).exclusion_rule_id = l_exclusion_rule_id then
1929 k := j;
1930 j := p_balance_classis.next(j);
1931 p_balance_classis.delete(k);
1932 else
1933 j := p_balance_classis.next(j);
1934 end if;
1938 -- Exclude defined balances.
1935 --
1936 end loop;
1937 --
1939 --
1940 j := p_defined_balances.first;
1941 loop
1942 exit when not p_defined_balances.exists(j);
1943 --
1944 if p_defined_balances(j).exclusion_rule_id = l_exclusion_rule_id then
1945 k := j;
1946 j := p_defined_balances.next(j);
1947 exclude_defined_balance
1948 (p_i => k
1949 ,p_defined_balances => p_defined_balances
1950 ,p_bal_attributes => p_bal_attributes
1951 );
1952 else
1953 j := p_defined_balances.next(j);
1954 end if;
1955 --
1956 end loop;
1957 --
1958 -- Exclude sub-classification rules.
1959 --
1960 j := p_sub_classi_rules.first;
1961 loop
1962 exit when not p_sub_classi_rules.exists(j);
1963 --
1964 if p_sub_classi_rules(j).exclusion_rule_id = l_exclusion_rule_id then
1965 k := j;
1966 j := p_sub_classi_rules.next(j);
1967 p_sub_classi_rules.delete(k);
1968 else
1969 j := p_sub_classi_rules.next(j);
1970 end if;
1971 --
1972 end loop;
1973 --
1974 -- Exclude iterative rules.
1975 --
1976 j := p_iterative_rules.first;
1977 loop
1978 exit when not p_iterative_rules.exists(j);
1979 --
1980 if p_iterative_rules(j).exclusion_rule_id = l_exclusion_rule_id then
1981 k := j;
1982 j := p_iterative_rules.next(j);
1983 p_iterative_rules.delete(k);
1984 else
1985 j := p_iterative_rules.next(j);
1986 end if;
1987 --
1988 end loop;
1989 --
1990 -- Exclude element type usages
1991 --
1992 j := p_ele_type_usages.first;
1993 loop
1994 exit when not p_ele_type_usages.exists(j);
1995 --
1996 if p_ele_type_usages(j).exclusion_rule_id = l_exclusion_rule_id then
1997 k := j;
1998 j := p_ele_type_usages.next(j);
1999 p_ele_type_usages.delete(k);
2000 else
2001 j := p_ele_type_usages.next(j);
2002 end if;
2003 --
2004 end loop;
2005 --
2006 -- Exclude grossup balance exclusions
2007 --
2008 j := p_gu_bal_exclusions.first;
2009 loop
2010 exit when not p_gu_bal_exclusions.exists(j);
2011 --
2012 if p_gu_bal_exclusions(j).exclusion_rule_id = l_exclusion_rule_id then
2013 k := j;
2014 j := p_gu_bal_exclusions.next(j);
2015 p_gu_bal_exclusions.delete(k);
2016 else
2017 j := p_gu_bal_exclusions.next(j);
2018 end if;
2019 --
2020 end loop;
2021 --
2022 -- Exclude template ff usages.
2023 --
2024 j := p_template_ff_usages.first;
2025 loop
2026 exit when not p_template_ff_usages.exists(j);
2027 --
2028 if p_template_ff_usages(j).exclusion_rule_id = l_exclusion_rule_id then
2029 k := j;
2030 j := p_template_ff_usages.next(j);
2031 p_template_ff_usages.delete(k);
2032 else
2033 j := p_template_ff_usages.next(j);
2034 end if;
2035 --
2036 end loop;
2037 --
2038 -- Exclude balance attributes.
2039 --
2040 j := p_bal_attributes.first;
2041 loop
2042 exit when not p_bal_attributes.exists(j);
2043 --
2044 if p_bal_attributes(j).exclusion_rule_id = l_exclusion_rule_id then
2045 k := j;
2046 j := p_bal_attributes.next(j);
2047 p_bal_attributes.delete(k);
2048 else
2049 j := p_bal_attributes.next(j);
2050 end if;
2051 --
2052 end loop;
2053 end if;
2054 i := p_exclusion_rules.next(i);
2055 end loop;
2056 --
2057 -- Apply the template formula usages.
2058 --
2059 apply_ff_usages
2060 (p_template_ff_usages => p_template_ff_usages
2061 ,p_element_types => p_element_types
2062 );
2063 --
2064 -- Exclusion rules have been applied. Now take out the formulas that
2065 -- have no references in the template.
2066 --
2067 exclude_formulas
2068 (p_formulas => p_formulas
2069 ,p_element_types => p_element_types
2070 ,p_input_values => p_input_values
2071 );
2072 hr_utility.set_location('Leaving:'|| l_proc, 20);
2073 end apply_exclusion_rules;
2074 -- ----------------------------------------------------------------------------
2075 -- |----------------------< set_input_value_defaults >------------------------|
2076 -- ----------------------------------------------------------------------------
2077 procedure set_input_value_defaults
2078 (p_element_template in pay_etm_shd.g_rec_type
2079 ,p_input_values in out nocopy t_input_values
2080 ) is
2081 l_proc varchar2(72) := g_package||'set_input_value_defaults';
2082 i number;
2083 begin
2084 hr_utility.set_location('Entering:'|| l_proc, 10);
2085 i := p_input_values.first;
2089 if p_input_values(i).default_value_column is not null then
2086 loop
2087 exit when not p_input_values.exists(i);
2088 --
2090 if upper(p_input_values(i).default_value_column) =
2091 'CONFIGURATION_INFORMATION1'
2092 and p_element_template.configuration_information1 is not null then
2093 p_input_values(i).default_value :=
2094 p_element_template.configuration_information1;
2095 elsif upper(p_input_values(i).default_value_column) =
2096 'CONFIGURATION_INFORMATION2' and
2097 p_element_template.configuration_information2 is not null then
2098 p_input_values(i).default_value :=
2099 p_element_template.configuration_information2;
2100 elsif upper(p_input_values(i).default_value_column) =
2101 'CONFIGURATION_INFORMATION3' and
2102 p_element_template.configuration_information3 is not null then
2103 p_input_values(i).default_value :=
2104 p_element_template.configuration_information3;
2105 elsif upper(p_input_values(i).default_value_column) =
2106 'CONFIGURATION_INFORMATION4' and
2107 p_element_template.configuration_information4 is not null then
2108 p_input_values(i).default_value :=
2109 p_element_template.configuration_information4;
2110 elsif upper(p_input_values(i).default_value_column) =
2111 'CONFIGURATION_INFORMATION5' and
2112 p_element_template.configuration_information5 is not null then
2113 p_input_values(i).default_value :=
2114 p_element_template.configuration_information5;
2115 elsif upper(p_input_values(i).default_value_column) =
2116 'CONFIGURATION_INFORMATION6' and
2117 p_element_template.configuration_information6 is not null then
2118 p_input_values(i).default_value :=
2119 p_element_template.configuration_information6;
2120 elsif upper(p_input_values(i).default_value_column) =
2121 'CONFIGURATION_INFORMATION7' and
2122 p_element_template.configuration_information7 is not null then
2123 p_input_values(i).default_value :=
2124 p_element_template.configuration_information7;
2125 elsif upper(p_input_values(i).default_value_column) =
2126 'CONFIGURATION_INFORMATION8' and
2127 p_element_template.configuration_information8 is not null then
2128 p_input_values(i).default_value :=
2129 p_element_template.configuration_information8;
2130 elsif upper(p_input_values(i).default_value_column) =
2131 'CONFIGURATION_INFORMATION9' and
2132 p_element_template.configuration_information9 is not null then
2133 p_input_values(i).default_value :=
2134 p_element_template.configuration_information9;
2135 elsif upper(p_input_values(i).default_value_column) =
2136 'CONFIGURATION_INFORMATION10' and
2137 p_element_template.configuration_information10 is not null then
2138 p_input_values(i).default_value :=
2139 p_element_template.configuration_information10;
2140 elsif upper(p_input_values(i).default_value_column) =
2141 'CONFIGURATION_INFORMATION11' and
2142 p_element_template.configuration_information11 is not null then
2143 p_input_values(i).default_value :=
2144 p_element_template.configuration_information11;
2145 elsif upper(p_input_values(i).default_value_column) =
2146 'CONFIGURATION_INFORMATION12' and
2147 p_element_template.configuration_information12 is not null then
2148 p_input_values(i).default_value :=
2149 p_element_template.configuration_information12;
2150 elsif upper(p_input_values(i).default_value_column) =
2151 'CONFIGURATION_INFORMATION13' and
2152 p_element_template.configuration_information13 is not null then
2153 p_input_values(i).default_value :=
2154 p_element_template.configuration_information13;
2155 elsif upper(p_input_values(i).default_value_column) =
2156 'CONFIGURATION_INFORMATION14' and
2157 p_element_template.configuration_information14 is not null then
2158 p_input_values(i).default_value :=
2159 p_element_template.configuration_information14;
2160 elsif upper(p_input_values(i).default_value_column) =
2161 'CONFIGURATION_INFORMATION15' and
2162 p_element_template.configuration_information15 is not null then
2163 p_input_values(i).default_value :=
2164 p_element_template.configuration_information15;
2165 elsif upper(p_input_values(i).default_value_column) =
2166 'CONFIGURATION_INFORMATION16' and
2167 p_element_template.configuration_information16 is not null then
2168 p_input_values(i).default_value :=
2169 p_element_template.configuration_information16;
2170 elsif upper(p_input_values(i).default_value_column) =
2171 'CONFIGURATION_INFORMATION17' and
2172 p_element_template.configuration_information17 is not null then
2173 p_input_values(i).default_value :=
2174 p_element_template.configuration_information17;
2175 elsif upper(p_input_values(i).default_value_column) =
2176 'CONFIGURATION_INFORMATION18' and
2177 p_element_template.configuration_information18 is not null then
2178 p_input_values(i).default_value :=
2179 p_element_template.configuration_information18;
2180 elsif upper(p_input_values(i).default_value_column) =
2181 'CONFIGURATION_INFORMATION19' and
2185 elsif upper(p_input_values(i).default_value_column) =
2182 p_element_template.configuration_information19 is not null then
2183 p_input_values(i).default_value :=
2184 p_element_template.configuration_information19;
2186 'CONFIGURATION_INFORMATION20' and
2187 p_element_template.configuration_information20 is not null then
2188 p_input_values(i).default_value :=
2189 p_element_template.configuration_information20;
2190 elsif upper(p_input_values(i).default_value_column) =
2191 'CONFIGURATION_INFORMATION21' and
2192 p_element_template.configuration_information21 is not null then
2193 p_input_values(i).default_value :=
2194 p_element_template.configuration_information21;
2195 elsif upper(p_input_values(i).default_value_column) =
2196 'CONFIGURATION_INFORMATION22' and
2197 p_element_template.configuration_information22 is not null then
2198 p_input_values(i).default_value :=
2199 p_element_template.configuration_information22;
2200 elsif upper(p_input_values(i).default_value_column) =
2201 'CONFIGURATION_INFORMATION23' and
2202 p_element_template.configuration_information23 is not null then
2203 p_input_values(i).default_value :=
2204 p_element_template.configuration_information23;
2205 elsif upper(p_input_values(i).default_value_column) =
2206 'CONFIGURATION_INFORMATION24' and
2207 p_element_template.configuration_information24 is not null then
2208 p_input_values(i).default_value :=
2209 p_element_template.configuration_information24;
2210 elsif upper(p_input_values(i).default_value_column) =
2211 'CONFIGURATION_INFORMATION25' and
2212 p_element_template.configuration_information25 is not null then
2213 p_input_values(i).default_value :=
2214 p_element_template.configuration_information25;
2215 elsif upper(p_input_values(i).default_value_column) =
2216 'CONFIGURATION_INFORMATION26' and
2217 p_element_template.configuration_information26 is not null then
2218 p_input_values(i).default_value :=
2219 p_element_template.configuration_information26;
2220 elsif upper(p_input_values(i).default_value_column) =
2221 'CONFIGURATION_INFORMATION27' and
2222 p_element_template.configuration_information27 is not null then
2223 p_input_values(i).default_value :=
2224 p_element_template.configuration_information27;
2225 elsif upper(p_input_values(i).default_value_column) =
2226 'CONFIGURATION_INFORMATION28' and
2227 p_element_template.configuration_information28 is not null then
2228 p_input_values(i).default_value :=
2229 p_element_template.configuration_information28;
2230 elsif upper(p_input_values(i).default_value_column) =
2231 'CONFIGURATION_INFORMATION29' and
2232 p_element_template.configuration_information29 is not null then
2233 p_input_values(i).default_value :=
2234 p_element_template.configuration_information29;
2235 elsif upper(p_input_values(i).default_value_column) =
2236 'CONFIGURATION_INFORMATION30' and
2237 p_element_template.configuration_information30 is not null then
2238 p_input_values(i).default_value :=
2239 p_element_template.configuration_information30;
2240 end if;
2241 end if;
2242 --
2243 i := p_input_values.next(i);
2244 end loop;
2245 hr_utility.set_location('Leaving:'|| l_proc, 20);
2246 end set_input_value_defaults;
2247 -- ----------------------------------------------------------------------------
2248 -- |------------------------< create_plsql_template >-------------------------|
2249 -- ----------------------------------------------------------------------------
2250 procedure create_plsql_template
2251 (p_lock in boolean default false
2252 ,p_template_id in number
2253 ,p_generate_part1 in boolean default false
2254 ,p_generate_part2 in boolean default false
2255 ,p_element_template in out nocopy pay_etm_shd.g_rec_type
2256 ,p_core_objects in out nocopy t_core_objects
2257 ,p_exclusion_rules in out nocopy t_exclusion_rules
2258 ,p_formulas in out nocopy t_formulas
2259 ,p_balance_types in out nocopy t_balance_types
2260 ,p_defined_balances in out nocopy t_defined_balances
2261 ,p_element_types in out nocopy t_element_types
2262 ,p_sub_classi_rules in out nocopy t_sub_classi_rules
2263 ,p_balance_classis in out nocopy t_balance_classis
2264 ,p_input_values in out nocopy t_input_values
2265 ,p_balance_feeds in out nocopy t_balance_feeds
2266 ,p_formula_rules in out nocopy t_formula_rules
2267 ,p_iterative_rules in out nocopy t_iterative_rules
2268 ,p_ele_type_usages in out nocopy t_ele_type_usages
2269 ,p_gu_bal_exclusions in out nocopy t_gu_bal_exclusions
2270 ,p_bal_attributes in out nocopy t_bal_attributes
2271 ,p_template_ff_usages in out nocopy t_template_ff_usages
2272 ) is
2273 l_proc varchar2(72) := g_package||'create_plsql_template';
2274 --
2275 begin
2279 -- Get the shadow schema rows in locking ladder order.
2276 hr_utility.set_location('Entering:'|| l_proc, 10);
2277 savepoint create_plsql_template;
2278 --
2280 --
2281 get_element_template
2282 (p_template_id => p_template_id
2283 ,p_element_template => p_element_template
2284 );
2285 get_core_objects
2286 (p_template_id => p_template_id
2287 ,p_core_objects => p_core_objects
2288 );
2289 --
2290 if not p_generate_part1 and not p_generate_part2 then
2291 get_exclusion_rules
2292 (p_template_id => p_template_id
2293 ,p_exclusion_rules => p_exclusion_rules
2294 );
2295 end if;
2296 --
2297 if not p_generate_part2 then
2298 get_formulas
2299 (p_template_id => p_template_id
2300 ,p_formulas => p_formulas
2301 );
2302 get_balance_types
2303 (p_template_id => p_template_id
2304 ,p_balance_types => p_balance_types
2305 );
2306 get_defined_balances
2307 (p_balance_types => p_balance_types
2308 ,p_defined_balances => p_defined_balances
2309 );
2310 end if;
2311 --
2312 get_element_types
2313 (p_template_id => p_template_id
2314 ,p_element_types => p_element_types
2315 );
2316 --
2317 if not p_generate_part2 then
2318 get_sub_classi_rules
2319 (p_element_types => p_element_types
2320 ,p_sub_classi_rules => p_sub_classi_rules
2321 );
2322 get_balance_classis
2323 (p_balance_types => p_balance_types
2324 ,p_balance_classis => p_balance_classis
2325 );
2326 get_input_values
2327 (p_element_types => p_element_types
2328 ,p_input_values => p_input_values
2329 );
2330 get_balance_feeds
2331 (p_input_values => p_input_values
2332 ,p_balance_feeds => p_balance_feeds
2333 );
2334 end if;
2335 --
2336 if not p_generate_part1 then
2337 get_formula_rules
2338 (p_element_types => p_element_types
2339 ,p_formula_rules => p_formula_rules
2340 );
2341 get_iterative_rules
2342 (p_element_types => p_element_types
2343 ,p_iterative_rules => p_iterative_rules
2344 );
2345 end if;
2346 --
2347 if not p_generate_part2 then
2348 get_ele_type_usages
2349 (p_element_types => p_element_types
2350 ,p_ele_type_usages => p_ele_type_usages
2351 );
2352 get_gu_bal_exclusions
2353 (p_element_types => p_element_types
2354 ,p_gu_bal_exclusions => p_gu_bal_exclusions
2355 );
2356 end if;
2357 --
2358 if not p_generate_part1 and not p_generate_part2 then
2359 get_template_ff_usages
2360 (p_template_id => p_template_id
2361 ,p_template_ff_usages => p_template_ff_usages
2362 );
2363 end if;
2364 --
2365 if not p_generate_part2 then
2366 get_bal_attributes
2367 (p_defined_balances => p_defined_balances
2368 ,p_bal_attributes => p_bal_attributes
2369 );
2370 end if;
2371 --
2372 -- Release locks if required.
2373 --
2374 if not p_lock then
2375 --
2376 -- Release locks.
2377 --
2378 rollback to create_plsql_template;
2379 end if;
2380 hr_utility.set_location(' Leaving:'||l_proc, 90);
2381 exception
2382 when others then
2383 hr_utility.set_location(' Leaving:'||l_proc, 100);
2384 flush_plsql_template
2385 (p_element_template => p_element_template
2386 ,p_core_objects => p_core_objects
2387 ,p_formulas => p_formulas
2388 ,p_exclusion_rules => p_exclusion_rules
2389 ,p_balance_types => p_balance_types
2390 ,p_defined_balances => p_defined_balances
2391 ,p_element_types => p_element_types
2392 ,p_sub_classi_rules => p_sub_classi_rules
2393 ,p_balance_classis => p_balance_classis
2394 ,p_input_values => p_input_values
2395 ,p_balance_feeds => p_balance_feeds
2396 ,p_formula_rules => p_formula_rules
2397 ,p_iterative_rules => p_iterative_rules
2398 ,p_ele_type_usages => p_ele_type_usages
2399 ,p_gu_bal_exclusions => p_gu_bal_exclusions
2400 ,p_bal_attributes => p_bal_attributes
2401 ,p_template_ff_usages => p_template_ff_usages
2402 );
2403 --
2404 -- Release locks.
2405 --
2406 rollback to create_plsql_template;
2407 raise;
2408 end create_plsql_template;
2409 -- ----------------------------------------------------------------------------
2410 -- |------------------------< insert_balance_type >---------------------------|
2411 -- ----------------------------------------------------------------------------
2412 procedure insert_balance_type
2413 (p_effective_date in date
2414 ,p_balance_type_id in number
2418 ) is
2415 ,p_template_id in number
2416 ,p_input_values in t_input_values
2417 ,p_balance_types in out nocopy t_balance_types
2419 l_id number;
2420 begin
2421 --
2422 -- Return if this balance type has already been inserted (this is possible
2423 -- if it is the base balance for another balance type).
2424 --
2425 if p_balance_types(p_balance_type_id).balance_type_id <> p_balance_type_id
2426 then
2427 return;
2428 end if;
2429 --
2430 -- Go ahead and insert the balance type.
2431 --
2432 p_balance_types(p_balance_type_id).balance_type_id := null;
2433 p_balance_types(p_balance_type_id).object_version_number := null;
2434 p_balance_types(p_balance_type_id).exclusion_rule_id := null;
2435 --
2436 p_balance_types(p_balance_type_id).template_id := p_template_id;
2437 --
2438 l_id := p_balance_types(p_balance_type_id).input_value_id;
2439 if l_id is not null then
2440 p_balance_types(p_balance_type_id).input_value_id :=
2441 p_input_values(l_id).input_value_id;
2442 end if;
2443 --
2444 l_id := p_balance_types(p_balance_type_id).base_balance_type_id;
2445 if l_id is not null then
2446 --
2447 -- Recursively insert the base balance if it has not been already created.
2448 --
2449 if l_id = p_balance_types(l_id).balance_type_id then
2450 insert_balance_type
2451 (p_effective_date => p_effective_date
2452 ,p_balance_type_id => l_id
2453 ,p_template_id => p_template_id
2454 ,p_input_values => p_input_values
2455 ,p_balance_types => p_balance_types
2456 );
2457 end if;
2458 p_balance_types(p_balance_type_id).base_balance_type_id :=
2459 p_balance_types(l_id).balance_type_id;
2460 end if;
2461 --
2462 pay_sbt_ins.ins(p_effective_date, p_balance_types(p_balance_type_id));
2463 --
2464 end insert_balance_type;
2465 -- ----------------------------------------------------------------------------
2466 -- |------------------------< plsql_to_db_template >--------------------------|
2467 -- ----------------------------------------------------------------------------
2468 procedure plsql_to_db_template
2469 (p_effective_date in date
2470 ,p_element_template in out nocopy pay_etm_shd.g_rec_type
2471 ,p_exclusion_rules in out nocopy t_exclusion_rules
2472 ,p_formulas in out nocopy t_formulas
2473 ,p_balance_types in out nocopy t_balance_types
2474 ,p_defined_balances in out nocopy t_defined_balances
2475 ,p_element_types in out nocopy t_element_types
2476 ,p_sub_classi_rules in out nocopy t_sub_classi_rules
2477 ,p_balance_classis in out nocopy t_balance_classis
2478 ,p_input_values in out nocopy t_input_values
2479 ,p_balance_feeds in out nocopy t_balance_feeds
2480 ,p_formula_rules in out nocopy t_formula_rules
2481 ,p_iterative_rules in out nocopy t_iterative_rules
2482 ,p_ele_type_usages in out nocopy t_ele_type_usages
2483 ,p_gu_bal_exclusions in out nocopy t_gu_bal_exclusions
2484 ,p_bal_attributes in out nocopy t_bal_attributes
2485 ,p_template_id out nocopy number
2486 ,p_object_version_number out nocopy number
2487 ) is
2488 l_proc varchar2(72) := g_package||'plsql_to_db_template';
2489 l_id number;
2490 i number;
2491 begin
2492 hr_utility.set_location('Entering:'|| l_proc, 10);
2493 ------------------------------------------
2494 -- Create row in pay_element_templates. --
2495 ------------------------------------------
2496 p_element_template.template_id := null;
2497 p_element_template.object_version_number := null;
2498 pay_etm_ins.ins(p_effective_date, p_element_template);
2499 p_template_id := p_element_template.template_id;
2500 p_object_version_number := p_element_template.object_version_number;
2501 -----------------------------------
2502 -- Create the template formulas. --
2503 -----------------------------------
2504 hr_utility.set_location(l_proc, 20);
2505 i := p_formulas.first;
2506 loop
2507 exit when not p_formulas.exists(i);
2508 --
2509 p_formulas(i).formula_id := null;
2510 p_formulas(i).object_version_number := null;
2511 pay_sf_ins.ins(p_effective_date, p_formulas(i));
2512 --
2513 i := p_formulas.next(i);
2514 end loop;
2515 -------------------------------
2516 -- Create the element types. --
2517 -------------------------------
2518 hr_utility.set_location(l_proc, 30);
2519 i := p_element_types.first;
2520 loop
2521 exit when not p_element_types.exists(i);
2522 --
2523 p_element_types(i).element_type_id := null;
2524 p_element_types(i).object_version_number := null;
2525 p_element_types(i).exclusion_rule_id := null;
2526 --
2527 p_element_types(i).template_id := p_element_template.template_id;
2528 l_id := p_element_types(i).payroll_formula_id;
2529 if l_id is not null then
2530 p_element_types(i).payroll_formula_id := p_formulas(l_id).formula_id;
2531 end if;
2532 pay_set_ins.ins(p_effective_date, p_element_types(i));
2533 --
2534 i := p_element_types.next(i);
2535 end loop;
2539 hr_utility.set_location(l_proc, 40);
2536 ------------------------------
2537 -- Create the input values. --
2538 ------------------------------
2540 i := p_input_values.first;
2541 loop
2542 exit when not p_input_values.exists(i);
2543 --
2544 p_input_values(i).input_value_id := null;
2545 p_input_values(i).object_version_number := null;
2546 p_input_values(i).exclusion_rule_id := null;
2547 --
2548 l_id := p_input_values(i).element_type_id;
2549 p_input_values(i).element_type_id := p_element_types(l_id).element_type_id;
2550 --
2551 l_id := p_input_values(i).formula_id;
2552 if l_id is not null then
2553 p_input_values(i).formula_id := p_formulas(l_id).formula_id;
2554 end if;
2555 pay_siv_ins.ins(p_effective_date, p_input_values(i));
2556 --
2557 i := p_input_values.next(i);
2558 end loop;
2559 -------------------------------
2560 -- Create the balance types. --
2561 -------------------------------
2562 hr_utility.set_location(l_proc, 50);
2563 i := p_balance_types.first;
2564 loop
2565 exit when not p_balance_types.exists(i);
2566 --
2567 insert_balance_type
2568 (p_effective_date => p_effective_date
2569 ,p_balance_type_id => i
2570 ,p_template_id => p_element_template.template_id
2571 ,p_input_values => p_input_values
2572 ,p_balance_types => p_balance_types
2573 );
2574 --
2575 i := p_balance_types.next(i);
2576 end loop;
2577 ------------------------------------------
2578 -- Create the sub-classification rules. --
2579 ------------------------------------------
2580 hr_utility.set_location(l_proc, 60);
2581 i := p_sub_classi_rules.first;
2582 loop
2583 exit when not p_sub_classi_rules.exists(i);
2584 --
2585 p_sub_classi_rules(i).sub_classification_rule_id := null;
2586 p_sub_classi_rules(i).object_version_number := null;
2587 p_sub_classi_rules(i).exclusion_rule_id := null;
2588 --
2589 l_id := p_sub_classi_rules(i).element_type_id;
2590 p_sub_classi_rules(i).element_type_id :=
2591 p_element_types(l_id).element_type_id;
2592 pay_ssr_ins.ins(p_effective_date, p_sub_classi_rules(i));
2593 --
2594 i := p_sub_classi_rules.next(i);
2595 end loop;
2596 --------------------------------------
2597 -- Create the formula result rules. --
2598 --------------------------------------
2599 hr_utility.set_location(l_proc, 70);
2600 i := p_formula_rules.first;
2601 loop
2602 exit when not p_formula_rules.exists(i);
2603 --
2604 p_formula_rules(i).formula_result_rule_id := null;
2605 p_formula_rules(i).object_version_number := null;
2606 p_formula_rules(i).exclusion_rule_id := null;
2607 --
2608 l_id := p_formula_rules(i).shadow_element_type_id;
2609 p_formula_rules(i).shadow_element_type_id :=
2610 p_element_types(l_id).element_type_id;
2611 l_id := p_formula_rules(i).element_type_id;
2612 if l_id is not null then
2613 p_formula_rules(i).element_type_id :=
2614 p_element_types(l_id).element_type_id;
2615 end if;
2616 l_id := p_formula_rules(i).input_value_id;
2617 if l_id is not null then
2618 p_formula_rules(i).input_value_id :=
2619 p_input_values(l_id).input_value_id;
2620 end if;
2621 pay_sfr_ins.ins(p_effective_date, p_formula_rules(i));
2622 --
2623 i := p_formula_rules.next(i);
2624 end loop;
2625 -----------------------------------------
2626 -- Create the balance classifications. --
2627 -----------------------------------------
2628 hr_utility.set_location(l_proc, 80);
2629 i := p_balance_classis.first;
2630 loop
2631 exit when not p_balance_classis.exists(i);
2632 --
2633 p_balance_classis(i).balance_classification_id := null;
2634 p_balance_classis(i).object_version_number := null;
2635 p_balance_classis(i).exclusion_rule_id := null;
2636 --
2637 l_id := p_balance_classis(i).balance_type_id;
2638 p_balance_classis(i).balance_type_id :=
2639 p_balance_types(l_id).balance_type_id;
2640 pay_sbc_ins.ins(p_effective_date, p_balance_classis(i));
2641 --
2642 i := p_balance_classis.next(i);
2643 end loop;
2644 ----------------------------------
2645 -- Create the defined balances. --
2646 ----------------------------------
2647 hr_utility.set_location(l_proc, 90);
2648 i := p_defined_balances.first;
2649 loop
2650 exit when not p_defined_balances.exists(i);
2651 --
2652 p_defined_balances(i).defined_balance_id := null;
2653 p_defined_balances(i).object_version_number := null;
2654 p_defined_balances(i).exclusion_rule_id := null;
2655 --
2656 l_id := p_defined_balances(i).balance_type_id;
2657 p_defined_balances(i).balance_type_id :=
2658 p_balance_types(l_id).balance_type_id;
2659 pay_sdb_ins.ins(p_effective_date, p_defined_balances(i));
2660 --
2661 i := p_defined_balances.next(i);
2662 end loop;
2663 -------------------------------
2664 -- Create the balance feeds. --
2665 -------------------------------
2666 hr_utility.set_location(l_proc, 100);
2667 i := p_balance_feeds.first;
2668 loop
2669 exit when not p_balance_feeds.exists(i);
2670 --
2674 --
2671 p_balance_feeds(i).balance_feed_id := null;
2672 p_balance_feeds(i).object_version_number := null;
2673 p_balance_feeds(i).exclusion_rule_id := null;
2675 l_id := p_balance_feeds(i).balance_type_id;
2676 if l_id is not null then
2677 p_balance_feeds(i).balance_type_id :=
2678 p_balance_types(l_id).balance_type_id;
2679 end if;
2680 l_id := p_balance_feeds(i).input_value_id;
2681 p_balance_feeds(i).input_value_id := p_input_values(l_id).input_value_id;
2682 pay_sbf_ins.ins(p_effective_date, p_balance_feeds(i));
2683 --
2684 i := p_balance_feeds.next(i);
2685 end loop;
2686 ---------------------------------
2687 -- Create the iterative rules. --
2688 ---------------------------------
2689 hr_utility.set_location(l_proc, 110);
2690 i := p_iterative_rules.first;
2691 loop
2692 exit when not p_iterative_rules.exists(i);
2693 --
2694 p_iterative_rules(i).iterative_rule_id := null;
2695 p_iterative_rules(i).object_version_number := null;
2696 p_iterative_rules(i).exclusion_rule_id := null;
2697 --
2698 l_id := p_iterative_rules(i).input_value_id;
2699 if l_id is not null then
2700 p_iterative_rules(i).input_value_id :=
2701 p_input_values(l_id).input_value_id;
2702 end if;
2703 l_id := p_iterative_rules(i).element_type_id;
2704 p_iterative_rules(i).element_type_id := p_element_types(l_id).element_type_id;
2705 pay_sir_ins.ins(p_effective_date, p_iterative_rules(i));
2706 --
2707 i := p_iterative_rules.next(i);
2708 end loop;
2709 -------------------------------------
2710 -- Create the element type usages. --
2711 -------------------------------------
2712 hr_utility.set_location(l_proc, 120);
2713 i := p_ele_type_usages.first;
2714 loop
2715 exit when not p_ele_type_usages.exists(i);
2716 --
2717 p_ele_type_usages(i).element_type_usage_id := null;
2718 p_ele_type_usages(i).object_version_number := null;
2719 p_ele_type_usages(i).exclusion_rule_id := null;
2720 --
2721 l_id := p_ele_type_usages(i).element_type_id;
2722 p_ele_type_usages(i).element_type_id := p_element_types(l_id).element_type_id;
2723 pay_seu_ins.ins(p_effective_date, p_ele_type_usages(i));
2724 --
2725 i := p_ele_type_usages.next(i);
2726 end loop;
2727 --------------------------------------------
2728 -- Create the grossup balance exclusions. --
2729 --------------------------------------------
2730 hr_utility.set_location(l_proc, 130);
2731 i := p_gu_bal_exclusions.first;
2732 loop
2733 exit when not p_gu_bal_exclusions.exists(i);
2734 --
2735 p_gu_bal_exclusions(i).grossup_balances_id := null;
2736 p_gu_bal_exclusions(i).object_version_number := null;
2737 p_gu_bal_exclusions(i).exclusion_rule_id := null;
2738 --
2739 l_id := p_gu_bal_exclusions(i).balance_type_id;
2740 if l_id is not null then
2741 p_gu_bal_exclusions(i).balance_type_id :=
2742 p_balance_types(l_id).balance_type_id;
2743 end if;
2744 l_id := p_gu_bal_exclusions(i).source_id;
2745 p_gu_bal_exclusions(i).source_id := p_element_types(l_id).element_type_id;
2746 pay_sgb_ins.ins(p_effective_date, p_gu_bal_exclusions(i));
2747 --
2748 i := p_gu_bal_exclusions.next(i);
2749 end loop;
2750 ------------------------------------
2751 -- Create the balance attributes. --
2752 ------------------------------------
2753 hr_utility.set_location(l_proc, 140);
2754 i := p_bal_attributes.first;
2755 loop
2756 exit when not p_bal_attributes.exists(i);
2757 --
2758 p_bal_attributes(i).balance_attribute_id := null;
2759 p_bal_attributes(i).object_version_number := null;
2760 p_bal_attributes(i).exclusion_rule_id := null;
2761 --
2762 l_id := p_bal_attributes(i).defined_balance_id;
2763 if l_id is not null then
2764 p_bal_attributes(i).defined_balance_id :=
2765 p_defined_balances(l_id).defined_balance_id;
2766 end if;
2767 pay_sba_ins.ins(p_bal_attributes(i));
2768 --
2769 i := p_bal_attributes.next(i);
2770 end loop;
2771 --
2772 hr_utility.set_location('Leaving:'|| l_proc, 500);
2773 end plsql_to_db_template;
2774 -- ----------------------------------------------------------------------------
2775 -- |---------------------------< replace_name >-------------------------------|
2776 -- ----------------------------------------------------------------------------
2777 procedure replace_name
2778 (p_string in out nocopy varchar2
2779 ,p_base_name in varchar2
2780 ,p_string_length in number
2781 ) is
2782 l_proc varchar2(72) := g_package||'replace_name';
2783 l_temp_string varchar2(32767);
2784 begin
2785 hr_utility.set_location('Entering:'|| l_proc, 10);
2786 l_temp_string := replace(p_string, g_name_placeholder, p_base_name);
2787 p_string := hr_mb_substrb(l_temp_string, 1, p_string_length);
2788 hr_utility.set_location('Leaving:'|| l_proc, 20);
2789 end replace_name;
2790 -- ----------------------------------------------------------------------------
2791 -- |---------------------------< prefix_name >--------------------------------|
2792 -- ----------------------------------------------------------------------------
2793 procedure prefix_name
2797 ) is
2794 (p_string in out nocopy varchar2
2795 ,p_maximum_string_length in number
2796 ,p_prefix in varchar2
2798 l_proc varchar2(72) := g_package||'prefix_name';
2799 begin
2800 hr_utility.set_location('Entering:'|| l_proc, 10);
2801 --
2802 -- Preserve the prefix at the expense of the suffix.
2803 --
2804 p_string :=
2805 hr_mb_substrb(p_prefix || p_string, 1, p_maximum_string_length);
2806 hr_utility.set_location('Leaving:'|| l_proc, 20);
2807 end prefix_name;
2808 -- ----------------------------------------------------------------------------
2809 -- |-------------------< create_plsql_user_structure >------------------------|
2810 -- ----------------------------------------------------------------------------
2811 procedure create_plsql_user_structure
2812 (p_business_group_id in number
2813 ,p_base_name in varchar2
2814 ,p_base_processing_priority in number default null
2815 ,p_preference_info_category in varchar2 default null
2816 ,p_preference_information1 in varchar2 default null
2817 ,p_preference_information2 in varchar2 default null
2818 ,p_preference_information3 in varchar2 default null
2819 ,p_preference_information4 in varchar2 default null
2820 ,p_preference_information5 in varchar2 default null
2821 ,p_preference_information6 in varchar2 default null
2822 ,p_preference_information7 in varchar2 default null
2823 ,p_preference_information8 in varchar2 default null
2824 ,p_preference_information9 in varchar2 default null
2825 ,p_preference_information10 in varchar2 default null
2826 ,p_preference_information11 in varchar2 default null
2827 ,p_preference_information12 in varchar2 default null
2828 ,p_preference_information13 in varchar2 default null
2829 ,p_preference_information14 in varchar2 default null
2830 ,p_preference_information15 in varchar2 default null
2831 ,p_preference_information16 in varchar2 default null
2832 ,p_preference_information17 in varchar2 default null
2833 ,p_preference_information18 in varchar2 default null
2834 ,p_preference_information19 in varchar2 default null
2835 ,p_preference_information20 in varchar2 default null
2836 ,p_preference_information21 in varchar2 default null
2837 ,p_preference_information22 in varchar2 default null
2838 ,p_preference_information23 in varchar2 default null
2839 ,p_preference_information24 in varchar2 default null
2840 ,p_preference_information25 in varchar2 default null
2841 ,p_preference_information26 in varchar2 default null
2842 ,p_preference_information27 in varchar2 default null
2843 ,p_preference_information28 in varchar2 default null
2844 ,p_preference_information29 in varchar2 default null
2845 ,p_preference_information30 in varchar2 default null
2846 ,p_configuration_info_category in varchar2 default null
2847 ,p_configuration_information1 in varchar2 default null
2848 ,p_configuration_information2 in varchar2 default null
2849 ,p_configuration_information3 in varchar2 default null
2850 ,p_configuration_information4 in varchar2 default null
2851 ,p_configuration_information5 in varchar2 default null
2852 ,p_configuration_information6 in varchar2 default null
2853 ,p_configuration_information7 in varchar2 default null
2854 ,p_configuration_information8 in varchar2 default null
2855 ,p_configuration_information9 in varchar2 default null
2856 ,p_configuration_information10 in varchar2 default null
2857 ,p_configuration_information11 in varchar2 default null
2858 ,p_configuration_information12 in varchar2 default null
2859 ,p_configuration_information13 in varchar2 default null
2860 ,p_configuration_information14 in varchar2 default null
2861 ,p_configuration_information15 in varchar2 default null
2862 ,p_configuration_information16 in varchar2 default null
2863 ,p_configuration_information17 in varchar2 default null
2864 ,p_configuration_information18 in varchar2 default null
2865 ,p_configuration_information19 in varchar2 default null
2866 ,p_configuration_information20 in varchar2 default null
2867 ,p_configuration_information21 in varchar2 default null
2868 ,p_configuration_information22 in varchar2 default null
2869 ,p_configuration_information23 in varchar2 default null
2870 ,p_configuration_information24 in varchar2 default null
2871 ,p_configuration_information25 in varchar2 default null
2872 ,p_configuration_information26 in varchar2 default null
2873 ,p_configuration_information27 in varchar2 default null
2874 ,p_configuration_information28 in varchar2 default null
2875 ,p_configuration_information29 in varchar2 default null
2876 ,p_configuration_information30 in varchar2 default null
2877 ,p_prefix_reporting_name in varchar2 default 'N'
2878 ,p_element_template in out nocopy pay_etm_shd.g_rec_type
2879 ,p_exclusion_rules in out nocopy t_exclusion_rules
2880 ,p_formulas in out nocopy t_formulas
2881 ,p_balance_types in out nocopy t_balance_types
2885 ,p_balance_classis in out nocopy t_balance_classis
2882 ,p_defined_balances in out nocopy t_defined_balances
2883 ,p_element_types in out nocopy t_element_types
2884 ,p_sub_classi_rules in out nocopy t_sub_classi_rules
2886 ,p_input_values in out nocopy t_input_values
2887 ,p_balance_feeds in out nocopy t_balance_feeds
2888 ,p_formula_rules in out nocopy t_formula_rules
2889 ,p_iterative_rules in out nocopy t_iterative_rules
2890 ,p_ele_type_usages in out nocopy t_ele_type_usages
2891 ,p_gu_bal_exclusions in out nocopy t_gu_bal_exclusions
2892 ,p_bal_attributes in out nocopy t_bal_attributes
2893 ,p_template_ff_usages in out nocopy t_template_ff_usages
2894 ) is
2895 l_proc varchar2(72) := g_package||'create_plsql_user_structure';
2896 i number;
2897 l_base_name varchar2(2000);
2898 --
2899 begin
2900 hr_utility.set_location('Entering:'|| l_proc, 10);
2901 ---------------------------------------------------------------------------
2902 -- Keep a local copy of the base name with spaces replaced by '_'. --
2903 ---------------------------------------------------------------------------
2904 l_base_name := upper(replace(p_base_name, ' ', '_'));
2905 ---------------------------------------------------------------------------
2906 -- Change template type to a user structure and set up base name for the --
2907 -- element template. --
2908 ---------------------------------------------------------------------------
2909 p_element_template.template_type := 'U';
2910 p_element_template.base_name := p_base_name;
2911 ------------------------------------------------------------------------
2912 -- Check that p_business_group_id is valid for the element template's --
2913 -- legislation and apply it. --
2914 ------------------------------------------------------------------------
2915 if not busgrp_in_legislation
2916 (p_business_group_id, p_element_template.legislation_code) then
2917 fnd_message.set_name(801, 'PAY_50067_ETM_BUS_LEG_MISMATCH' );
2918 fnd_message.raise_error;
2919 end if;
2920 p_element_template.business_group_id := p_business_group_id;
2921 p_element_template.legislation_code := null;
2922 i := p_formulas.first;
2923 loop
2924 exit when not p_formulas.exists(i);
2925 p_formulas(i).business_group_id := p_business_group_id;
2926 p_formulas(i).legislation_code := null;
2927 p_formulas(i).template_type := 'U';
2928 i := p_formulas.next(i);
2929 end loop;
2930 --------------------------------------
2931 -- Update base processing priority. --
2932 --------------------------------------
2933 if p_base_processing_priority is not null then
2934 p_element_template.base_processing_priority := p_base_processing_priority;
2935 end if;
2936 --------------------------------------
2937 -- Update the preference flexfield. --
2938 --------------------------------------
2939 if p_preference_info_category is not null then
2940 p_element_template.preference_info_category := p_preference_info_category;
2941 end if;
2942 if p_preference_information1 is not null then
2943 p_element_template.preference_information1 := p_preference_information1;
2944 end if;
2945 if p_preference_information2 is not null then
2946 p_element_template.preference_information2 := p_preference_information2;
2947 end if;
2948 if p_preference_information3 is not null then
2949 p_element_template.preference_information3 := p_preference_information3;
2950 end if;
2951 if p_preference_information4 is not null then
2952 p_element_template.preference_information4 := p_preference_information4;
2953 end if;
2954 if p_preference_information5 is not null then
2955 p_element_template.preference_information5 := p_preference_information5;
2956 end if;
2957 if p_preference_information6 is not null then
2958 p_element_template.preference_information6 := p_preference_information6;
2959 end if;
2960 if p_preference_information7 is not null then
2961 p_element_template.preference_information7 := p_preference_information7;
2962 end if;
2963 if p_preference_information8 is not null then
2964 p_element_template.preference_information8 := p_preference_information8;
2965 end if;
2966 if p_preference_information9 is not null then
2967 p_element_template.preference_information9 := p_preference_information9;
2968 end if;
2969 if p_preference_information10 is not null then
2970 p_element_template.preference_information10 := p_preference_information10;
2971 end if;
2972 if p_preference_information11 is not null then
2973 p_element_template.preference_information11 := p_preference_information11;
2974 end if;
2975 if p_preference_information12 is not null then
2976 p_element_template.preference_information12 := p_preference_information12;
2977 end if;
2978 if p_preference_information13 is not null then
2979 p_element_template.preference_information13 := p_preference_information13;
2980 end if;
2981 if p_preference_information14 is not null then
2982 p_element_template.preference_information14 := p_preference_information14;
2983 end if;
2984 if p_preference_information15 is not null then
2985 p_element_template.preference_information15 := p_preference_information15;
2989 end if;
2986 end if;
2987 if p_preference_information16 is not null then
2988 p_element_template.preference_information16 := p_preference_information16;
2990 if p_preference_information17 is not null then
2991 p_element_template.preference_information17 := p_preference_information17;
2992 end if;
2993 if p_preference_information18 is not null then
2994 p_element_template.preference_information18 := p_preference_information18;
2995 end if;
2996 if p_preference_information19 is not null then
2997 p_element_template.preference_information19 := p_preference_information19;
2998 end if;
2999 if p_preference_information20 is not null then
3000 p_element_template.preference_information20 := p_preference_information20;
3001 end if;
3002 if p_preference_information21 is not null then
3003 p_element_template.preference_information21 := p_preference_information21;
3004 end if;
3005 if p_preference_information22 is not null then
3006 p_element_template.preference_information22 := p_preference_information22;
3007 end if;
3008 if p_preference_information23 is not null then
3009 p_element_template.preference_information23 := p_preference_information23;
3010 end if;
3011 if p_preference_information24 is not null then
3012 p_element_template.preference_information24 := p_preference_information24;
3013 end if;
3014 if p_preference_information25 is not null then
3015 p_element_template.preference_information25 := p_preference_information25;
3016 end if;
3017 if p_preference_information26 is not null then
3018 p_element_template.preference_information26 := p_preference_information26;
3019 end if;
3020 if p_preference_information27 is not null then
3021 p_element_template.preference_information27 := p_preference_information27;
3022 end if;
3023 if p_preference_information28 is not null then
3024 p_element_template.preference_information28 := p_preference_information28;
3025 end if;
3026 if p_preference_information29 is not null then
3027 p_element_template.preference_information29 := p_preference_information29;
3028 end if;
3029 if p_preference_information30 is not null then
3030 p_element_template.preference_information30 := p_preference_information30;
3031 end if;
3032 -----------------------------------------
3033 -- Update the configuration flexfield. --
3034 -----------------------------------------
3035 if p_configuration_info_category is not null then
3036 p_element_template.configuration_info_category :=
3037 p_configuration_info_category;
3038 end if;
3039 if p_configuration_information1 is not null then
3040 p_element_template.configuration_information1 :=
3041 p_configuration_information1;
3042 end if;
3043 if p_configuration_information2 is not null then
3044 p_element_template.configuration_information2 :=
3045 p_configuration_information2;
3046 end if;
3047 if p_configuration_information3 is not null then
3048 p_element_template.configuration_information3 :=
3049 p_configuration_information3;
3050 end if;
3051 if p_configuration_information4 is not null then
3052 p_element_template.configuration_information4 :=
3053 p_configuration_information4;
3054 end if;
3055 if p_configuration_information5 is not null then
3056 p_element_template.configuration_information5 :=
3057 p_configuration_information5;
3058 end if;
3059 if p_configuration_information6 is not null then
3060 p_element_template.configuration_information6 :=
3061 p_configuration_information6;
3062 end if;
3063 if p_configuration_information7 is not null then
3064 p_element_template.configuration_information7 :=
3065 p_configuration_information7;
3066 end if;
3067 if p_configuration_information8 is not null then
3068 p_element_template.configuration_information8 :=
3069 p_configuration_information8;
3070 end if;
3071 if p_configuration_information9 is not null then
3072 p_element_template.configuration_information9 :=
3073 p_configuration_information9;
3074 end if;
3075 if p_configuration_information10 is not null then
3076 p_element_template.configuration_information10 :=
3077 p_configuration_information10;
3078 end if;
3079 if p_configuration_information11 is not null then
3080 p_element_template.configuration_information11 :=
3081 p_configuration_information11;
3082 end if;
3083 if p_configuration_information12 is not null then
3084 p_element_template.configuration_information12 :=
3085 p_configuration_information12;
3086 end if;
3087 if p_configuration_information13 is not null then
3088 p_element_template.configuration_information13 :=
3089 p_configuration_information13;
3090 end if;
3091 if p_configuration_information14 is not null then
3092 p_element_template.configuration_information14 :=
3093 p_configuration_information14;
3094 end if;
3095 if p_configuration_information15 is not null then
3096 p_element_template.configuration_information15 :=
3097 p_configuration_information15;
3098 end if;
3099 if p_configuration_information16 is not null then
3100 p_element_template.configuration_information16 :=
3101 p_configuration_information16;
3102 end if;
3103 if p_configuration_information17 is not null then
3104 p_element_template.configuration_information17 :=
3105 p_configuration_information17;
3106 end if;
3110 end if;
3107 if p_configuration_information18 is not null then
3108 p_element_template.configuration_information18 :=
3109 p_configuration_information18;
3111 if p_configuration_information19 is not null then
3112 p_element_template.configuration_information19 :=
3113 p_configuration_information19;
3114 end if;
3115 if p_configuration_information20 is not null then
3116 p_element_template.configuration_information20 :=
3117 p_configuration_information20;
3118 end if;
3119 if p_configuration_information21 is not null then
3120 p_element_template.configuration_information21 :=
3121 p_configuration_information21;
3122 end if;
3123 if p_configuration_information22 is not null then
3124 p_element_template.configuration_information22 :=
3125 p_configuration_information22;
3126 end if;
3127 if p_configuration_information23 is not null then
3128 p_element_template.configuration_information23 :=
3129 p_configuration_information23;
3130 end if;
3131 if p_configuration_information24 is not null then
3132 p_element_template.configuration_information24 :=
3133 p_configuration_information24;
3134 end if;
3135 if p_configuration_information25 is not null then
3136 p_element_template.configuration_information25 :=
3137 p_configuration_information25;
3138 end if;
3139 if p_configuration_information26 is not null then
3140 p_element_template.configuration_information26 :=
3141 p_configuration_information26;
3142 end if;
3143 if p_configuration_information27 is not null then
3144 p_element_template.configuration_information27 :=
3145 p_configuration_information27;
3146 end if;
3147 if p_configuration_information28 is not null then
3148 p_element_template.configuration_information28 :=
3149 p_configuration_information28;
3150 end if;
3151 if p_configuration_information29 is not null then
3152 p_element_template.configuration_information29 :=
3153 p_configuration_information29;
3154 end if;
3155 if p_configuration_information30 is not null then
3156 p_element_template.configuration_information30 :=
3157 p_configuration_information30;
3158 end if;
3159 -----------------------------------------------------------
3160 -- Carry out placeholder substitution using p_base_name. --
3161 -----------------------------------------------------------
3162 i := p_element_types.first;
3163 loop
3164 exit when not p_element_types.exists(i);
3165 prefix_name (p_element_types(i).element_name, 80, p_base_name);
3166 if p_prefix_reporting_name = 'Y' then
3167 prefix_name (p_element_types(i).reporting_name, 80, p_base_name);
3168 end if;
3169 replace_name(p_element_types(i).attribute1, p_base_name, 150);
3170 replace_name(p_element_types(i).attribute2, p_base_name, 150);
3171 replace_name(p_element_types(i).attribute3, p_base_name, 150);
3172 replace_name(p_element_types(i).attribute4, p_base_name, 150);
3173 replace_name(p_element_types(i).attribute5, p_base_name, 150);
3174 replace_name(p_element_types(i).attribute6, p_base_name, 150);
3175 replace_name(p_element_types(i).attribute7, p_base_name, 150);
3176 replace_name(p_element_types(i).attribute8, p_base_name, 150);
3177 replace_name(p_element_types(i).attribute9, p_base_name, 150);
3178 replace_name(p_element_types(i).attribute10, p_base_name, 150);
3179 replace_name(p_element_types(i).attribute11, p_base_name, 150);
3180 replace_name(p_element_types(i).attribute12, p_base_name, 150);
3181 replace_name(p_element_types(i).attribute13, p_base_name, 150);
3182 replace_name(p_element_types(i).attribute14, p_base_name, 150);
3183 replace_name(p_element_types(i).attribute15, p_base_name, 150);
3184 replace_name(p_element_types(i).attribute16, p_base_name, 150);
3185 replace_name(p_element_types(i).attribute17, p_base_name, 150);
3186 replace_name(p_element_types(i).attribute18, p_base_name, 150);
3187 replace_name(p_element_types(i).attribute19, p_base_name, 150);
3188 replace_name(p_element_types(i).attribute20, p_base_name, 150);
3189 replace_name(p_element_types(i).element_information1, p_base_name, 150);
3190 replace_name(p_element_types(i).element_information2, p_base_name, 150);
3191 replace_name(p_element_types(i).element_information3, p_base_name, 150);
3192 replace_name(p_element_types(i).element_information4, p_base_name, 150);
3193 replace_name(p_element_types(i).element_information5, p_base_name, 150);
3194 replace_name(p_element_types(i).element_information6, p_base_name, 150);
3195 replace_name(p_element_types(i).element_information7, p_base_name, 150);
3196 replace_name(p_element_types(i).element_information8, p_base_name, 150);
3197 replace_name(p_element_types(i).element_information9, p_base_name, 150);
3198 replace_name(p_element_types(i).element_information10, p_base_name, 150);
3199 replace_name(p_element_types(i).element_information11, p_base_name, 150);
3200 replace_name(p_element_types(i).element_information12, p_base_name, 150);
3201 replace_name(p_element_types(i).element_information13, p_base_name, 150);
3202 replace_name(p_element_types(i).element_information14, p_base_name, 150);
3203 replace_name(p_element_types(i).element_information15, p_base_name, 150);
3204 replace_name(p_element_types(i).element_information16, p_base_name, 150);
3205 replace_name(p_element_types(i).element_information17, p_base_name, 150);
3206 replace_name(p_element_types(i).element_information18, p_base_name, 150);
3207 replace_name(p_element_types(i).element_information19, p_base_name, 150);
3208 replace_name(p_element_types(i).element_information20, p_base_name, 150);
3209 i := p_element_types.next(i);
3210 end loop;
3211
3212 i := p_balance_types.first;
3213 loop
3214 exit when not p_balance_types.exists(i);
3215 prefix_name (p_balance_types(i).balance_name, 80, p_base_name);
3216 prefix_name (p_balance_types(i).reporting_name, 80, p_base_name);
3217 replace_name(p_balance_types(i).attribute1, p_base_name, 150);
3218 replace_name(p_balance_types(i).attribute2, p_base_name, 150);
3219 replace_name(p_balance_types(i).attribute3, p_base_name, 150);
3220 replace_name(p_balance_types(i).attribute4, p_base_name, 150);
3221 replace_name(p_balance_types(i).attribute5, p_base_name, 150);
3222 replace_name(p_balance_types(i).attribute6, p_base_name, 150);
3223 replace_name(p_balance_types(i).attribute7, p_base_name, 150);
3224 replace_name(p_balance_types(i).attribute8, p_base_name, 150);
3225 replace_name(p_balance_types(i).attribute9, p_base_name, 150);
3226 replace_name(p_balance_types(i).attribute10, p_base_name, 150);
3227 replace_name(p_balance_types(i).attribute11, p_base_name, 150);
3228 replace_name(p_balance_types(i).attribute12, p_base_name, 150);
3229 replace_name(p_balance_types(i).attribute13, p_base_name, 150);
3230 replace_name(p_balance_types(i).attribute14, p_base_name, 150);
3234 replace_name(p_balance_types(i).attribute18, p_base_name, 150);
3231 replace_name(p_balance_types(i).attribute15, p_base_name, 150);
3232 replace_name(p_balance_types(i).attribute16, p_base_name, 150);
3233 replace_name(p_balance_types(i).attribute17, p_base_name, 150);
3235 replace_name(p_balance_types(i).attribute19, p_base_name, 150);
3236 replace_name(p_balance_types(i).attribute20, p_base_name, 150);
3237 i := p_balance_types.next(i);
3238 end loop;
3239
3240 i := p_formulas.first;
3241 loop
3242 exit when not p_formulas.exists(i);
3243 prefix_name(p_formulas(i).formula_name, 80, l_base_name);
3244 begin
3245 replace_name(p_formulas(i).formula_text, l_base_name, 32767);
3246 exception
3247 when plsql_value_error then
3248 hr_utility.set_location('Leaving:'|| l_proc, 20);
3249 fnd_message.set_name(801, 'PAY_50068_ETM_GEN_FF_TOO_LONG');
3250 fnd_message.set_token('BASE_NAME', l_base_name, false);
3251 fnd_message.set_token('LENGTH', 32767);
3252 fnd_message.raise_error;
3253 when others then
3254 hr_utility.set_location('Leaving:'|| l_proc, 30);
3255 raise;
3256 end;
3257 i := p_formulas.next(i);
3258 end loop;
3259 ------------------------------------
3260 -- Set defaults for input values. --
3261 ------------------------------------
3262 set_input_value_defaults
3263 (p_element_template => p_element_template
3264 ,p_input_values => p_input_values
3265 );
3266 ----------------------------
3267 -- Apply exclusion rules. --
3268 ----------------------------
3269 apply_exclusion_rules
3270 (p_element_template => p_element_template
3271 ,p_exclusion_rules => p_exclusion_rules
3272 ,p_formulas => p_formulas
3273 ,p_balance_types => p_balance_types
3274 ,p_defined_balances => p_defined_balances
3275 ,p_element_types => p_element_types
3276 ,p_sub_classi_rules => p_sub_classi_rules
3277 ,p_balance_classis => p_balance_classis
3278 ,p_input_values => p_input_values
3279 ,p_balance_feeds => p_balance_feeds
3280 ,p_formula_rules => p_formula_rules
3281 ,p_iterative_rules => p_iterative_rules
3282 ,p_ele_type_usages => p_ele_type_usages
3283 ,p_gu_bal_exclusions => p_gu_bal_exclusions
3284 ,p_bal_attributes => p_bal_attributes
3285 ,p_template_ff_usages => p_template_ff_usages
3286 );
3287 ----------------------------------------
3288 -- Apply the template formula usages. --
3289 ----------------------------------------
3290 hr_utility.set_location('Leaving:'|| l_proc, 40);
3291 end create_plsql_user_structure;
3292 -- ----------------------------------------------------------------------------
3293 -- |---------------------------< delete_template >----------------------------|
3294 -- ----------------------------------------------------------------------------
3295 procedure delete_template
3296 (p_template_id in number
3297 ,p_formulas in t_formulas
3298 ,p_delete_formulas in boolean default true
3299 ) is
3300 l_proc varchar2(72) := g_package||'delete_template';
3301 i number;
3302 l_ovn number;
3303 begin
3304 hr_utility.set_location('Entering:'|| l_proc, 10);
3305 ---------------------------------------------
3306 -- Delete template core object rows first. --
3307 ---------------------------------------------
3308 delete from pay_template_core_objects
3309 where template_id = p_template_id
3310 ;
3311 ----------------------------------
3312 -- Delete in foreign key order. --
3313 ----------------------------------
3314 -------------------------------
3315 -- pay_shadow_bal_attributes --
3316 -------------------------------
3317 delete from pay_shadow_bal_attributes
3318 where defined_balance_id in
3319 (
3320 select db.defined_balance_id
3321 from pay_shadow_defined_balances db
3322 , pay_shadow_balance_types bt
3323 where db.balance_type_id = bt.balance_type_id
3324 and bt.template_id = p_template_id
3325 );
3326 ----------------------------
3327 -- pay_template_ff_usages --
3328 ----------------------------
3329 delete from pay_template_ff_usages
3330 where template_id = p_template_id;
3331 ----------------------------------
3332 -- pay_shadow_gu_bal_exclusions --
3333 ----------------------------------
3334 delete from pay_shadow_gu_bal_exclusions
3335 where balance_type_id in
3336 (
3337 select balance_type_id
3338 from pay_shadow_balance_types
3339 where template_id = p_template_id
3340 );
3341 --------------------------------
3342 -- pay_shadow_ele_type_usages --
3343 --------------------------------
3344 delete from pay_shadow_ele_type_usages
3345 where element_type_id in
3346 (
3347 select element_type_id
3348 from pay_shadow_element_types
3349 where template_id = p_template_id
3350 );
3351 --------------------------------
3352 -- pay_shadow_iterative_rules --
3353 --------------------------------
3354 delete from pay_shadow_iterative_rules
3355 where element_type_id in
3356 (
3357 select element_type_id
3358 from pay_shadow_element_types
3359 where template_id = p_template_id
3360 );
3361 ------------------------------
3362 -- pay_shadow_formula_rules --
3363 ------------------------------
3364 delete from pay_shadow_formula_rules
3365 where shadow_element_type_id in
3366 (
3367 select element_type_id
3368 from pay_shadow_element_types
3369 where template_id = p_template_id
3370 );
3371 ------------------------------
3372 -- pay_shadow_balance_feeds --
3373 ------------------------------
3374 delete from pay_shadow_balance_feeds
3375 where input_value_id in
3376 (
3377 select iv.input_value_id
3378 from pay_shadow_input_values iv
3379 , pay_shadow_element_types et
3380 where et.template_id = p_template_id
3381 and iv.element_type_id = et.element_type_id
3382 );
3383 --------------------------------
3384 -- pay_shadow_balance_classis --
3385 --------------------------------
3386 delete from pay_shadow_balance_classi
3387 where balance_type_id in
3388 (
3389 select balance_type_id
3390 from pay_shadow_balance_types
3391 where template_id = p_template_id
3392 );
3393 ---------------------------------
3394 -- pay_shadow_defined_balances --
3395 ---------------------------------
3396 delete from pay_shadow_defined_balances
3397 where balance_type_id in
3398 (
3399 select balance_type_id
3400 from pay_shadow_balance_types
3401 where template_id = p_template_id
3402 );
3403 ---------------------------------
3404 -- pay_shadow_sub_classi_rules --
3405 ---------------------------------
3406 delete from pay_shadow_sub_classi_rules
3407 where element_type_id in
3408 (
3409 select element_type_id
3410 from pay_shadow_element_types
3411 where template_id = p_template_id
3412 );
3413 ------------------------------
3414 -- pay_shadow_balance_types --
3415 ------------------------------
3416 --
3417 -- NULL the base_base_balance_type_id to avoid having to recursively
3418 -- delete because of the base_balance_type_id.
3419 --
3420 update pay_shadow_balance_types
3421 set base_balance_type_id = null
3422 where base_balance_type_id is not null
3423 and template_id = p_template_id;
3424 --
3425 delete from pay_shadow_balance_types
3426 where template_id = p_template_id;
3427 -----------------------------
3428 -- pay_shadow_input_values --
3429 -----------------------------
3430 delete from pay_shadow_input_values
3431 where element_type_id in
3432 (
3433 select element_type_id
3434 from pay_shadow_element_types
3435 where template_id = p_template_id
3436 );
3437 ------------------------------
3438 -- pay_shadow_element_types --
3439 ------------------------------
3440 delete from pay_shadow_element_types
3441 where template_id = p_template_id;
3442 -------------------------
3443 -- pay_shadow_formulas --
3444 -------------------------
3445 --
3446 -- It may not be as fast, but using the formulas table gives the most
3447 -- maintainable code when it comes to deleting the template's formulas.
3448 --
3449 if p_delete_formulas then
3450 i := p_formulas.first;
3451 loop
3452 exit when not p_formulas.exists(i);
3453 --
3454 l_ovn := p_formulas(i).object_version_number;
3455 pay_sf_del.del(i, l_ovn);
3456 --
3457 i := p_formulas.next(i);
3458 end loop;
3459 end if;
3460 ----------------------------------
3461 -- pay_template_exclusion_rules --
3462 ----------------------------------
3463 delete from pay_template_exclusion_rules
3464 where template_id = p_template_id;
3465 ---------------------------
3466 -- pay_element_templates --
3467 ---------------------------
3468 delete from pay_element_templates
3469 where template_id = p_template_id;
3470 --
3471 hr_utility.set_location('Leaving:'|| l_proc, 200);
3472 end delete_template;
3473 --
3474
3475 FUNCTION get_shadow_formula_name(p_formula_id in number)
3476 return varchar2 is
3477
3478 l_formula_name pay_shadow_formulas.formula_name%TYPE;
3479
3480 CURSOR cur_get_formula_name IS
3481 SELECT formula_name
3482 FROM pay_shadow_formulas
3483 WHERE formula_id = p_formula_id;
3484
3485 BEGIN
3486 OPEN cur_get_formula_name;
3487 FETCH cur_get_formula_name INTO l_formula_name;
3488 if cur_get_formula_name%NOTFOUND then
3489 l_formula_name := NULL;
3490 end if;
3491
3492 CLOSE cur_get_formula_name;
3493
3494 RETURN l_formula_name;
3495
3496 END get_shadow_formula_name;
3497 --
3498 end pay_element_template_util;