DBA Data[Home] [Help]

PACKAGE BODY: APPS.PAY_ELEMENT_TEMPLATE_GEN

Source


1 Package Body pay_element_template_gen as
2 /* $Header: pyetmgen.pkb 120.3 2006/07/11 15:41:46 arashid ship $ */
3 --
4 -- Package Variables
5 --
6 g_package  varchar2(33) := '  pay_element_template_gen.';
7 ---------------------------------------------------------------------------
8 -- Record for holding primary key and rowid information. This is because --
9 -- the code uses currently available code (as opposed to proper APIs).   --
10 ---------------------------------------------------------------------------
11 type g_rowid_id_rec is record
12   (id                             number
13   ,rowid                          varchar2(128)
14   ,effective_date                 date
15   );
16 --
17 type t_rowid_id_recs is table of g_rowid_id_rec index by binary_integer;
18 ---------------------------------------------------------------------------
19 -- Record for holding primary key and object version number. This is for --
20 -- the iterative tables which use row handler calls.                     --
21 ---------------------------------------------------------------------------
22 type g_id_ovn_rec is record
23   (id             number
24   ,ovn            number
25   ,effective_date date
26   );
27 --
28 type t_id_ovn_recs is table of g_id_ovn_rec index by binary_integer;
29 --------------------------------------------------------
30 -- Constant definitions for CORE_OBJECT_TYPE lookups. --
31 --------------------------------------------------------
32 g_sf_lookup_type  constant varchar2(30) default pay_tco_shd.g_sf_lookup_type;
33 g_sbt_lookup_type constant varchar2(30) default pay_tco_shd.g_sbt_lookup_type;
34 g_sdb_lookup_type constant varchar2(30) default pay_tco_shd.g_sdb_lookup_type;
35 g_set_lookup_type constant varchar2(30) default pay_tco_shd.g_set_lookup_type;
36 g_ssr_lookup_type constant varchar2(30) default pay_tco_shd.g_ssr_lookup_type;
37 g_sbc_lookup_type constant varchar2(30) default pay_tco_shd.g_sbc_lookup_type;
38 g_siv_lookup_type constant varchar2(30) default pay_tco_shd.g_siv_lookup_type;
39 g_sbf_lookup_type constant varchar2(30) default pay_tco_shd.g_sbf_lookup_type;
40 g_sfr_lookup_type constant varchar2(30) default pay_tco_shd.g_sfr_lookup_type;
41 g_spr_lookup_type constant varchar2(30) default pay_tco_shd.g_spr_lookup_type;
42 g_sir_lookup_type constant varchar2(30) default pay_tco_shd.g_sir_lookup_type;
43 g_seu_lookup_type constant varchar2(30) default pay_tco_shd.g_seu_lookup_type;
44 g_sgb_lookup_type constant varchar2(30) default pay_tco_shd.g_sgb_lookup_type;
45 g_sba_lookup_type constant varchar2(30) default pay_tco_shd.g_sba_lookup_type;
46 --------------------------------------------------
47 -- Constant definitions for formula type names. --
48 --------------------------------------------------
49 g_skip_formula_type    constant varchar2(30) default
50 pay_sf_shd.g_skip_formula_type;
51 g_payroll_formula_type constant varchar2(30) default
52 pay_sf_shd.g_payroll_formula_type;
53 g_input_val_formula_type constant varchar2(30) default
54 pay_sf_shd.g_input_val_formula_type;
55 g_iterative_formula_type constant varchar2(30) default
56 pay_sf_shd.g_iterative_formula_type;
57 g_proration_formula_type constant varchar2(30) default
58 pay_sf_shd.g_proration_formula_type;
59 ------------------------------------
60 -- Cached FORMULA_TYPE_ID values. --
61 ------------------------------------
62 g_skip_formula_type_id      number;
63 g_payroll_formula_type_id   number;
64 g_input_val_formula_type_id number;
65 g_iterative_formula_type_id number;
66 g_proration_formula_type_id number;
67 -------------------------------------------------
68 -- Constant definitions for event group names. --
69 -------------------------------------------------
70 g_proration_event_group  constant varchar2(30) default 'P';
71 g_retro_event_group      constant varchar2(30) default 'R';
72 --------------------
73 -- Deletion mode. --
74 --------------------
75 g_zap_deletion_mode    constant varchar2(30) default 'ZAP';
76 -- ----------------------------------------------------------------------------
77 -- |-------------------------< get_formula_type_id >--------------------------|
78 -- ----------------------------------------------------------------------------
79 function get_formula_type_id
80 (p_formula_type_name in varchar2
81 ) return number is
82 begin
83   if p_formula_type_name = g_payroll_formula_type then
84     return g_payroll_formula_type_id;
85   elsif p_formula_type_name = g_skip_formula_type then
86     return g_skip_formula_type_id;
87   elsif p_formula_type_name = g_input_val_formula_type then
88     return g_input_val_formula_type_id;
89   elsif  p_formula_type_name = g_iterative_formula_type then
90     return g_iterative_formula_type_id;
91   elsif p_formula_type_name = g_proration_formula_type then
92     return g_proration_formula_type_id;
93   end if;
94   return null;
95 end get_formula_type_id;
96 -- ----------------------------------------------------------------------------
97 -- |--------------------------< get_formula_types  >--------------------------|
98 -- ----------------------------------------------------------------------------
99 procedure get_formula_types is
100 --
101   procedure get_formula_type_id
102   (p_formula_type    in varchar2
103   ,p_formula_type_id out nocopy number
104   ) is
105   cursor csr_formula_type(p_formula_type in varchar2) is
106   select formula_type_id
107   from   ff_formula_types
108   where formula_type_name = p_formula_type
109   ;
110   begin
111     open csr_formula_type(p_formula_type);
112     fetch csr_formula_type into p_formula_type_id;
113     if csr_formula_type%notfound then
114       close csr_formula_type;
115       hr_utility.set_message(801, 'PAY_50058_ETM_BAD_FORMULA_TYPE');
116       hr_utility.set_message_token('FORMULA_TYPE', p_formula_type);
117       hr_utility.raise_error;
118     end if;
119     close csr_formula_type;
120   end get_formula_type_id;
121 --
122 begin
123   get_formula_type_id(g_skip_formula_type, g_skip_formula_type_id);
124   --
125   get_formula_type_id(g_payroll_formula_type, g_payroll_formula_type_id);
126   --
127   get_formula_type_id(g_input_val_formula_type, g_input_val_formula_type_id);
128   --
129   get_formula_type_id(g_iterative_formula_type, g_iterative_formula_type_id);
130   --
131   get_formula_type_id(g_proration_formula_type, g_proration_formula_type_id);
132 end get_formula_types;
133 -- ----------------------------------------------------------------------------
134 -- |-------------------------< get_ele_classification_id >--------------------|
135 -- ----------------------------------------------------------------------------
136 function get_ele_classification_id
137 (p_business_group_id in            number
138 ,p_legislation_code  in            varchar2
139 ,p_classification    in            varchar2
140 ,p_non_payments_flag    out nocopy varchar2
141 ) return number is
142 l_proc              varchar2(72) := g_package||'get_ele_classification_id';
143 l_classification_id number;
144 --
145 cursor csr_ele_classification
146 (p_business_group_id in number
147 ,p_legislation_code  in varchar2
148 ,p_classification    in varchar2
149 ) is
150 select classification_id
151 ,      non_payments_flag
152 from   pay_element_classifications
153 where  upper(classification_name) = p_classification
154 and
155 (
156   (business_group_id is null and legislation_code is null)
157   or (legislation_code is null and business_group_id = p_business_group_id)
158   or (business_group_id is null and legislation_code = p_legislation_code)
159 );
160 begin
161   open csr_ele_classification
162        (p_business_group_id
163        ,p_legislation_code
164        ,upper(p_classification)
165        );
166   fetch csr_ele_classification
167   into  l_classification_id
168   ,     p_non_payments_flag
169   ;
170   if csr_ele_classification%notfound then
171     close csr_ele_classification;
172     hr_utility.set_message(801, 'PAY_50060_ETM_BAD_ELE_CLASS');
173     hr_utility.set_message_token('CLASSIFICATION', p_classification);
174     hr_utility.raise_error;
175   end if;
176   close csr_ele_classification;
177   --
178   return l_classification_id;
179 end get_ele_classification_id;
180 -- ----------------------------------------------------------------------------
181 -- |------------------------< get_ben_classification_id >---------------------|
182 -- ----------------------------------------------------------------------------
183 function get_ben_classification_id
184 (p_business_group_id  in number
185 ,p_legislation_code   in varchar2
186 ,p_ben_classification in varchar2
187 ) return number is
188 l_proc                  varchar2(72) := g_package||'get_ben_classification_id';
189 l_ben_classification_id number;
190 --
191 cursor csr_ben_classification
192 (p_business_group_id  in number
193 ,p_legislation_code   in varchar2
194 ,p_ben_classification in varchar2
195 ) is
196 select benefit_classification_id
197 from   ben_benefit_classifications
198 where  upper(benefit_classification_name) = p_ben_classification
199 and
200 (
201   (business_group_id is null and legislation_code is null)
202   or (legislation_code is null and business_group_id = p_business_group_id)
203   or (business_group_id is null and legislation_code = p_legislation_code)
204 );
205 begin
206   open csr_ben_classification
207        (p_business_group_id
208        ,p_legislation_code
209        ,upper(p_ben_classification)
210        );
211   fetch csr_ben_classification into l_ben_classification_id;
212   if csr_ben_classification%notfound then
213     close csr_ben_classification;
214     hr_utility.set_message(801, 'PAY_50061_ETM_BAD_BEN_CLASS');
215     hr_utility.set_message_token('CLASSIFICATION', p_ben_classification);
216     hr_utility.raise_error;
217   end if;
218   close csr_ben_classification;
219   --
220   return l_ben_classification_id;
221 end get_ben_classification_id;
222 -- ----------------------------------------------------------------------------
223 -- |----------------------------< get_formula_id  >---------------------------|
224 -- ----------------------------------------------------------------------------
225 function get_formula_id
226 (p_effective_date    in date
227 ,p_business_group_id in number
228 ,p_legislation_code  in varchar2
229 ,p_formula_type_id   in number
230 ,p_formula           in varchar2
231 ) return number is
232 l_proc       varchar2(72) := g_package||'get_formula_id';
233 l_formula_id number;
234   --
235 cursor csr_formula
236 (p_effective_date    in     date
237 ,p_business_group_id in     number
238 ,p_legislation_code  in     varchar2
239 ,p_formula_type_id   in     number
240 ,p_formula           in varchar2
241 ) is
242   select formula_id
243   from ff_formulas_f
244   where formula_type_id = p_formula_type_id
245   and   upper(formula_name) = p_formula
246   and (p_effective_date between
247        effective_start_date and nvl(effective_end_date, hr_api.g_eot))
248   and
249   (
250     (business_group_id is null and legislation_code is null)
251     or (legislation_code is null and business_group_id = p_business_group_id)
252     or (business_group_id is null and legislation_code = p_legislation_code)
253   );
254 begin
255   open csr_formula
256        (p_effective_date
257        ,p_business_group_id
258        ,p_legislation_code
259        ,p_formula_type_id
260        ,upper(p_formula)
261        );
262   fetch csr_formula into l_formula_id;
263   if csr_formula%notfound then
264     close csr_formula;
265     hr_utility.set_message(801, 'PAY_50062_ETM_BAD_FORMULA_NAME');
266     hr_utility.set_message_token('FORMULA_NAME', p_formula);
267     hr_utility.raise_error;
268   end if;
269   close csr_formula;
270   --
271   return l_formula_id;
272 end get_formula_id;
273 -- ----------------------------------------------------------------------------
274 -- |----------------------------< get_balance_id  >---------------------------|
275 -- ----------------------------------------------------------------------------
276 function get_balance_id
277 (p_business_group_id in number
278 ,p_legislation_code  in varchar2
279 ,p_balance           in varchar2
280 ) return number is
281 l_proc       varchar2(72) := g_package||'get_balance_id';
282 l_balance_id number;
283 --
284 cursor csr_balance
285 (p_business_group_id in number
286 ,p_legislation_code  in varchar2
287 ,p_balance           in varchar2
288 ) is
289 select balance_type_id
290 from pay_balance_types
291 where upper(balance_name) = p_balance
292 and
293 (
294   (business_group_id is null and legislation_code is null)
295   or (legislation_code is null and business_group_id = p_business_group_id)
296   or (business_group_id is null and legislation_code = p_legislation_code)
297 );
298 begin
299   open csr_balance
300        (p_business_group_id
301        ,p_legislation_code
302        ,upper(p_balance)
303        );
304   fetch csr_balance into l_balance_id;
305   if csr_balance%notfound then
306     close csr_balance;
307     hr_utility.set_message(801, 'PAY_50063_ETM_BAD_BALANCE_NAME');
308     hr_utility.set_message_token('BALANCE_NAME', p_balance);
309   end if;
310   close csr_balance;
311   --
312   return l_balance_id;
313 end get_balance_id;
314 -- ----------------------------------------------------------------------------
315 -- |-------------------------< get_element_type_id  >-------------------------|
316 -- ----------------------------------------------------------------------------
317 function get_element_type_id
318 (p_effective_date    in date
319 ,p_business_group_id in number
320 ,p_legislation_code  in varchar2
321 ,p_element_name      in varchar2
322 ) return number is
323 l_proc            varchar2(72) := g_package||'get_element_type_id';
324 l_element_type_id number;
325   --
326 cursor csr_element_name
327 (p_effective_date    in date
328 ,p_business_group_id in number
329 ,p_legislation_code  in varchar2
330 ,p_element_name      in varchar2
331 ) is
332 select element_type_id
333 from  pay_element_types_f
334 where upper(element_name) = p_element_name
335 and   p_effective_date between
336       effective_start_date and nvl(effective_end_date, hr_api.g_eot)
337 and
338 (
339   (business_group_id is null and legislation_code is null)
340   or (legislation_code is null and business_group_id = p_business_group_id)
341   or (business_group_id is null and legislation_code = p_legislation_code)
342 );
343 begin
344   open csr_element_name
345        (p_effective_date
346        ,p_business_group_id
347        ,p_legislation_code
348        ,upper(p_element_name)
349        );
350   fetch csr_element_name into l_element_type_id;
351   if csr_element_name%notfound then
352     close csr_element_name;
353     hr_utility.set_message(801, 'PAY_50215_ETM_BAD_ELEMENT_TYPE');
354     hr_utility.set_message_token('ELEMENT_TYPE', p_element_name);
355     hr_utility.raise_error;
356   end if;
357   close csr_element_name;
358   --
359   return l_element_type_id;
360 end get_element_type_id;
361 -- ----------------------------------------------------------------------------
362 -- |-------------------------< get_event_group_id >---------------------------|
363 -- ----------------------------------------------------------------------------
364 function get_event_group_id
365 (p_business_group_id in     number
366 ,p_legislation_code  in     varchar2
367 ,p_event_group_type  in     varchar2
368 ,p_event_group       in     varchar2
369 ) return number is
370 l_proc           varchar2(72) := g_package||'get_event_group_id';
371 l_event_group_id number;
372 --
373 cursor csr_event_group
374 (p_business_group_id in number
375 ,p_legislation_code  in varchar2
376 ,p_event_group_type  in varchar2
377 ,p_event_group       in varchar2
378 ) is
379 select event_group_id
380 from pay_event_groups
381 where event_group_type = p_event_group_type
382 and   upper(event_group_name) = p_event_group
383 and
384 (
385   (business_group_id is null and legislation_code is null) or
386   (legislation_code is null and business_group_id = p_business_group_id) or
387   (business_group_id is null and legislation_code = p_legislation_code)
388 );
389 begin
390   open csr_event_group
391        (p_business_group_id
392        ,p_legislation_code
393        ,p_event_group_type
394        ,upper(p_event_group)
395        );
396   fetch csr_event_group into l_event_group_id;
397   if csr_event_group%notfound then
398     close csr_event_group;
399     hr_utility.set_message(801, 'PAY_50201_ETM_BAD_EVENT_GROUP');
400     hr_utility.set_message_token('EVENT_GROUP', p_event_group);
401     hr_utility.raise_error;
402   end if;
403   close csr_event_group;
404   --
405   return l_event_group_id;
406 end get_event_group_id;
407 -- ----------------------------------------------------------------------------
408 -- |-------------------------< get_bal_dimension_id >-------------------------|
409 -- ----------------------------------------------------------------------------
410 function get_bal_dimension_id
411 (p_business_group_id in number
412 ,p_legislation_code  in varchar2
413 ,p_bal_dimension     in varchar2
414 ) return number is
415 l_proc             varchar2(72) := g_package||'get_bal_dimension_id';
416 l_bal_dimension_id number;
417 --
418 cursor csr_bal_dimension
419 (p_business_group_id in number
420 ,p_legislation_code  in varchar2
421 ,p_bal_dimension     in varchar2
422 ) is
423 select balance_dimension_id
424 from   pay_balance_dimensions
425 where  upper(dimension_name) = p_bal_dimension
426 and
427 (
428  (business_group_id is null and legislation_code is null)
429   or (legislation_code is null and business_group_id = p_business_group_id)
430   or (business_group_id is null and legislation_code = p_legislation_code)
431 );
432 begin
433   open csr_bal_dimension
434        (p_business_group_id
435        ,p_legislation_code
436        ,upper(p_bal_dimension)
437        );
438   fetch csr_bal_dimension into l_bal_dimension_id;
439   if csr_bal_dimension%notfound then
440     close csr_bal_dimension;
441     hr_utility.set_message(801, 'PAY_50059_ETM_BAD_BAL_DIMENSON');
442     hr_utility.set_message_token('BALANCE_DIMENSION', p_bal_dimension);
443     hr_utility.raise_error;
444   end if;
445   close csr_bal_dimension;
446   --
447   return l_bal_dimension_id;
448 end get_bal_dimension_id;
449 -- ----------------------------------------------------------------------------
450 -- |---------------------------< get_run_type_id  >---------------------------|
451 -- ----------------------------------------------------------------------------
452 function get_run_type_id
453 (p_effective_date    in date
454 ,p_business_group_id in number
455 ,p_legislation_code  in varchar2
456 ,p_run_type          in varchar2
457 ) return number is
458 l_proc        varchar2(72) := g_package||'get_run_type_id';
459 l_run_type_id number;
460   --
461 cursor csr_run_type
462 (p_effective_date    in date
463 ,p_business_group_id in number
464 ,p_legislation_code  in varchar2
465 ,p_run_type          in varchar2
466 ) is
467 select run_type_id
468 from  pay_run_types_f
469 where upper(run_type_name) = p_run_type
470 and   p_effective_date between
471       effective_start_date and nvl(effective_end_date, hr_api.g_eot)
472 and
473 (
474   (business_group_id is null and legislation_code is null)
475   or (legislation_code is null and business_group_id = p_business_group_id)
476   or (business_group_id is null and legislation_code = p_legislation_code)
477 );
478 begin
479   open csr_run_type
480        (p_effective_date
481        ,p_business_group_id
482        ,p_legislation_code
483        ,upper(p_run_type)
484        );
485   fetch csr_run_type into l_run_type_id;
486   if csr_run_type%notfound then
487     close csr_run_type;
488     hr_utility.set_message(801, 'PAY_50064_ETM_BAD_RUN_TYPE');
489     hr_utility.set_message_token('RUN_TYPE', p_run_type);
490     hr_utility.raise_error;
491   end if;
492   close csr_run_type;
493   --
494   return l_run_type_id;
495 end get_run_type_id;
496 -- ----------------------------------------------------------------------------
497 -- |--------------------------< get_bal_category_id >-------------------------|
498 -- ----------------------------------------------------------------------------
499 function get_bal_category_id
500 (p_effective_date    in date
501 ,p_business_group_id in number
502 ,p_legislation_code  in varchar2
503 ,p_bal_category      in varchar2
504 ) return number is
505 l_proc                varchar2(72) := g_package||'get_bal_category_id';
506 l_balance_category_id number;
507 --
508 cursor csr_balance_category
509 (p_effective_date    in date
510 ,p_business_group_id in number
511 ,p_legislation_code  in varchar2
512 ,p_bal_category      in varchar2
513 ) is
514 select balance_category_id
515 from   pay_balance_categories_f
516 where  upper(category_name) = p_bal_category
517 and    p_effective_date between
518        effective_start_date and effective_end_date
519 and
520 (
521   (business_group_id is null and legislation_code is null)
522   or (legislation_code is null and business_group_id = p_business_group_id)
523   or (business_group_id is null and legislation_code = p_legislation_code)
524 );
525 begin
526   open csr_balance_category
527        (p_effective_date
528        ,p_business_group_id
529        ,p_legislation_code
530        ,upper(p_bal_category)
531        );
532   fetch csr_balance_category into l_balance_category_id;
533   if csr_balance_category%notfound then
534     close csr_balance_category;
535     hr_utility.set_message(801, 'PAY_51520_ETM_BAD_BAL_CATEGORY');
536     hr_utility.set_message_token('BALANCE_CATEGORY', p_bal_category);
537     hr_utility.raise_error;
538   end if;
539   close csr_balance_category;
540   --
541   return l_balance_category_id;
542 end get_bal_category_id;
543 -- ----------------------------------------------------------------------------
544 -- |----------------------< get_bal_attribute_def_id >------------------------|
545 -- ----------------------------------------------------------------------------
546 function get_bal_attribute_def_id
547 (p_business_group_id in number
548 ,p_legislation_code  in varchar2
549 ,p_attribute         in varchar2
550 ) return number is
551 l_proc         varchar2(72) := g_package||'get_bal_attribute_def_id';
552 l_attribute_id number;
553 --
554 cursor csr_attribute
555 (p_business_group_id in number
556 ,p_legislation_code  in varchar2
557 ,p_attribute         in varchar2
558 ) is
559 select attribute_id
560 from pay_bal_attribute_definitions
561 where upper(attribute_name) = p_attribute
562 and
563 (
564   (business_group_id is null and legislation_code is null)
565   or (legislation_code is null and business_group_id = p_business_group_id)
566   or (business_group_id is null and legislation_code = p_legislation_code)
567 );
568 begin
569   open csr_attribute
570        (p_business_group_id
571        ,p_legislation_code
572        ,upper(p_attribute)
573        );
574   fetch csr_attribute into l_attribute_id;
575   if csr_attribute%notfound then
576     close csr_attribute;
577     hr_utility.set_message(801, 'PAY_50211_ETM_BAD_BAL_ATTR_DEF');
578     hr_utility.set_message_token('BALANCE_ATTR_DEF', p_attribute);
579     hr_utility.raise_error;
580   end if;
581   close csr_attribute;
582   --
583   return l_attribute_id;
584 end get_bal_attribute_def_id;
585 -- ----------------------------------------------------------------------------
586 -- |------------------------< flush_generation_tables >-----------------------|
587 -- ----------------------------------------------------------------------------
588 procedure flush_generation_tables
589   (p_sf_core_objects      in out nocopy pay_element_template_util.t_core_objects
590   ,p_sbt_core_objects     in out nocopy pay_element_template_util.t_core_objects
591   ,p_sdb_core_objects     in out nocopy pay_element_template_util.t_core_objects
592   ,p_set_core_objects     in out nocopy pay_element_template_util.t_core_objects
593   ,p_ssr_core_objects     in out nocopy pay_element_template_util.t_core_objects
594   ,p_sbc_core_objects     in out nocopy pay_element_template_util.t_core_objects
595   ,p_siv_core_objects     in out nocopy pay_element_template_util.t_core_objects
596   ,p_sbf_core_objects     in out nocopy pay_element_template_util.t_core_objects
597   ,p_spr_core_objects     in out nocopy pay_element_template_util.t_core_objects
598   ,p_sfr_core_objects     in out nocopy pay_element_template_util.t_core_objects
599   ,p_sir_core_objects     in out nocopy pay_element_template_util.t_core_objects
600   ,p_seu_core_objects     in out nocopy pay_element_template_util.t_core_objects
601   ,p_sgb_core_objects     in out nocopy pay_element_template_util.t_core_objects
602   ,p_sba_core_objects     in out nocopy pay_element_template_util.t_core_objects
603   ) is
604   l_proc                varchar2(72) := g_package||'flush_generation_tables';
605 begin
606   hr_utility.set_location('Entering:'|| l_proc, 10);
607   p_sf_core_objects.delete;
608   p_sbt_core_objects.delete;
609   p_sdb_core_objects.delete;
610   p_set_core_objects.delete;
611   p_ssr_core_objects.delete;
612   p_sbc_core_objects.delete;
613   p_siv_core_objects.delete;
614   p_sbf_core_objects.delete;
615   p_spr_core_objects.delete;
616   p_sfr_core_objects.delete;
617   p_sir_core_objects.delete;
618   p_seu_core_objects.delete;
619   p_sgb_core_objects.delete;
620   p_sba_core_objects.delete;
621   hr_utility.set_location(' Leaving:'||l_proc, 20);
622 end flush_generation_tables;
623 -- ----------------------------------------------------------------------------
624 -- |----------------------< create_generation_tables >------------------------|
625 -- ----------------------------------------------------------------------------
626 procedure create_generation_tables
627   (p_all_core_objects          in     pay_element_template_util.t_core_objects
628   ,p_index_by_core_object_id   in     boolean default false
629   ,p_sf_core_objects     in out nocopy pay_element_template_util.t_core_objects
630   ,p_sbt_core_objects    in out nocopy pay_element_template_util.t_core_objects
631   ,p_sdb_core_objects    in out nocopy pay_element_template_util.t_core_objects
632   ,p_set_core_objects    in out nocopy pay_element_template_util.t_core_objects
633   ,p_ssr_core_objects    in out nocopy pay_element_template_util.t_core_objects
634   ,p_sbc_core_objects    in out nocopy pay_element_template_util.t_core_objects
635   ,p_siv_core_objects    in out nocopy pay_element_template_util.t_core_objects
636   ,p_sbf_core_objects    in out nocopy pay_element_template_util.t_core_objects
637   ,p_spr_core_objects    in out nocopy pay_element_template_util.t_core_objects
638   ,p_sfr_core_objects    in out nocopy pay_element_template_util.t_core_objects
639   ,p_sir_core_objects    in out nocopy pay_element_template_util.t_core_objects
640   ,p_seu_core_objects    in out nocopy pay_element_template_util.t_core_objects
641   ,p_sgb_core_objects    in out nocopy pay_element_template_util.t_core_objects
642   ,p_sba_core_objects    in out nocopy pay_element_template_util.t_core_objects
643   ) is
644   l_proc                varchar2(72) := g_package||'create_generation_tables';
645   i                     number;
646   l_index               number;
647   l_core_object_type    varchar2(30);
648   --
649 begin
650   hr_utility.set_location('Entering:'|| l_proc, 10);
651   --
652   -- Flush the current generation tables.
653   --
654   flush_generation_tables
655   (p_sf_core_objects               => p_sf_core_objects
656   ,p_sbt_core_objects              => p_sbt_core_objects
657   ,p_sdb_core_objects              => p_sdb_core_objects
658   ,p_set_core_objects              => p_set_core_objects
659   ,p_ssr_core_objects              => p_ssr_core_objects
660   ,p_sbc_core_objects              => p_sbc_core_objects
661   ,p_siv_core_objects              => p_siv_core_objects
662   ,p_sbf_core_objects              => p_sbf_core_objects
663   ,p_spr_core_objects              => p_spr_core_objects
664   ,p_sfr_core_objects              => p_sfr_core_objects
665   ,p_sir_core_objects              => p_sir_core_objects
666   ,p_seu_core_objects              => p_seu_core_objects
667   ,p_sgb_core_objects              => p_sgb_core_objects
668   ,p_sba_core_objects              => p_sba_core_objects
669   );
670   --
671   -- For each core object, set entries in the per-shadow-table generation
672   -- tables.
673   --
674   i := p_all_core_objects.first;
675   loop
676     exit when not p_all_core_objects.exists(i);
677     --
678     -- Index by shadow_object_id or core_object_id according to the caller's
679     -- choice.
680     --
681     if p_index_by_core_object_id then
682       l_index := p_all_core_objects(i).core_object_id;
683     else
684       l_index := p_all_core_objects(i).shadow_object_id;
685     end if;
686     l_core_object_type := p_all_core_objects(i).core_object_type;
687     if l_core_object_type = g_sf_lookup_type then
688       p_sf_core_objects(l_index) := p_all_core_objects(i);
689     elsif l_core_object_type = g_sbt_lookup_type then
690      p_sbt_core_objects(l_index) := p_all_core_objects(i);
691     elsif l_core_object_type = g_sdb_lookup_type then
692      p_sdb_core_objects(l_index) := p_all_core_objects(i);
693     elsif l_core_object_type = g_set_lookup_type then
694      p_set_core_objects(l_index) := p_all_core_objects(i);
695     elsif l_core_object_type = g_ssr_lookup_type then
696      p_ssr_core_objects(l_index) := p_all_core_objects(i);
697     elsif l_core_object_type = g_sbc_lookup_type then
698      p_sbc_core_objects(l_index) := p_all_core_objects(i);
699     elsif l_core_object_type = g_siv_lookup_type then
700      p_siv_core_objects(l_index) := p_all_core_objects(i);
701     elsif l_core_object_type = g_sbf_lookup_type then
702      p_sbf_core_objects(l_index) := p_all_core_objects(i);
703     elsif l_core_object_type = g_spr_lookup_type then
704      p_spr_core_objects(l_index) := p_all_core_objects(i);
705     elsif l_core_object_type = g_sfr_lookup_type then
706      p_sfr_core_objects(l_index) := p_all_core_objects(i);
707     elsif l_core_object_type = g_sir_lookup_type then
708      p_sir_core_objects(l_index) := p_all_core_objects(i);
709     elsif l_core_object_type = g_seu_lookup_type then
710      p_seu_core_objects(l_index) := p_all_core_objects(i);
711     elsif l_core_object_type = g_sgb_lookup_type then
712      p_sgb_core_objects(l_index) := p_all_core_objects(i);
713     elsif l_core_object_type = g_sba_lookup_type then
714      p_sba_core_objects(l_index) := p_all_core_objects(i);
715     end if;
716     --
717     i := p_all_core_objects.next(i);
718   end loop;
719   hr_utility.set_location(' Leaving:'||l_proc, 90);
720 end create_generation_tables;
721 -- ----------------------------------------------------------------------------
722 -- |-------------------------< core_object_exists >---------------------------|
723 -- ----------------------------------------------------------------------------
724 function core_object_exists
725   (p_shadow_object_id              in     number
726   ,p_object_type                   in     varchar2
727   ,p_core_objects                  in     pay_element_template_util.t_core_objects
728   ) return boolean is
729   l_proc                varchar2(72) := g_package||'core_object_exists';
730   l_core_object_id      number;
731   l_count               number;
732   --
733   cursor csr_sf_core_object_exists(p_core_object_id in number) is
734   select count(0)
735   from   ff_formulas_f
736   where  formula_id = p_core_object_id;
737   --
738   cursor csr_set_core_object_exists(p_core_object_id in number) is
739   select count(0)
740   from   pay_element_types_f
741   where  element_type_id = p_core_object_id;
742   --
743   cursor csr_sbt_core_object_exists(p_core_object_id in number) is
744   select count(0)
745   from   pay_balance_types
746   where  balance_type_id = p_core_object_id;
747   --
748   cursor csr_sdb_core_object_exists(p_core_object_id in number) is
749   select count(0)
750   from   pay_defined_balances
751   where  defined_balance_id = p_core_object_id;
752   --
753   cursor csr_ssr_core_object_exists(p_core_object_id in number) is
754   select count(0)
755   from   pay_sub_classification_rules_f
756   where  sub_classification_rule_id = p_core_object_id;
757   --
758   cursor csr_sbc_core_object_exists(p_core_object_id in number) is
759   select count(0)
760   from   pay_balance_classifications
761   where  balance_classification_id = p_core_object_id;
762   --
763   cursor csr_siv_core_object_exists(p_core_object_id in number) is
764   select count(0)
765   from   pay_input_values_f
766   where  input_value_id = p_core_object_id;
767   --
768   cursor csr_sbf_core_object_exists(p_core_object_id in number) is
769   select count(0)
770   from   pay_balance_feeds_f
771   where  balance_feed_id = p_core_object_id;
772   --
773   cursor csr_sfr_core_object_exists(p_core_object_id in number) is
774   select count(0)
775   from   pay_formula_result_rules_f
776   where  formula_result_rule_id = p_core_object_id;
777   --
778   cursor csr_spr_core_object_exists(p_core_object_id in number) is
779   select count(0)
780   from   pay_status_processing_rules_f
781   where  status_processing_rule_id = p_core_object_id;
782   --
783   cursor csr_sir_core_object_exists(p_core_object_id in number) is
784   select count(0)
785   from   pay_iterative_rules_f
786   where  iterative_rule_id = p_core_object_id;
787   --
788   cursor csr_seu_core_object_exists(p_core_object_id in number) is
789   select count(0)
790   from   pay_element_type_usages_f
791   where  element_type_usage_id = p_core_object_id;
792   --
793   cursor csr_sgb_core_object_exists(p_core_object_id in number) is
794   select count(0)
795   from   pay_grossup_bal_exclusions
796   where  grossup_balances_id = p_core_object_id;
797   --
798   cursor csr_sba_core_object_exists(p_core_object_id in number) is
799   select count(0)
800   from   pay_balance_attributes
801   where  balance_attribute_id = p_core_object_id;
802   --
803 begin
804   hr_utility.set_location('Entering:'|| l_proc, 10);
805   --
806   -- A core object corresponding a shadow object exists if there is
807   -- a row for it in PAY_TEMPLATE_CORE_OBJECTS, and the generated object
808   -- still exists in the core schema.
809   --
810   if p_core_objects.exists(p_shadow_object_id) then
811     l_core_object_id := p_core_objects(p_shadow_object_id).core_object_id;
812     if p_object_type = g_sf_lookup_type then
813       hr_utility.set_location(l_proc, 20);
814       open csr_sf_core_object_exists(l_core_object_id);
815       fetch csr_sf_core_object_exists into l_count;
816       close csr_sf_core_object_exists;
817       return l_count > 0;
818     elsif p_object_type = g_set_lookup_type then
819       hr_utility.set_location(l_proc, 30);
820       open csr_set_core_object_exists(l_core_object_id);
821       fetch csr_set_core_object_exists into l_count;
822       close csr_set_core_object_exists;
823       return l_count > 0;
824     elsif p_object_type = g_sbt_lookup_type then
825       hr_utility.set_location(l_proc, 40);
826       open csr_sbt_core_object_exists(l_core_object_id);
827       fetch csr_sbt_core_object_exists into l_count;
828       close csr_sbt_core_object_exists;
829       return l_count > 0;
830     elsif p_object_type = g_sdb_lookup_type then
831       hr_utility.set_location(l_proc, 50);
832       open csr_sdb_core_object_exists(l_core_object_id);
833       fetch csr_sdb_core_object_exists into l_count;
834       close csr_sdb_core_object_exists;
835       return l_count > 0;
836     elsif p_object_type = g_ssr_lookup_type then
837       hr_utility.set_location(l_proc, 60);
838       open csr_ssr_core_object_exists(l_core_object_id);
839       fetch csr_ssr_core_object_exists into l_count;
840       close csr_ssr_core_object_exists;
841       return l_count > 0;
842     elsif p_object_type = g_sbc_lookup_type then
843       hr_utility.set_location(l_proc, 70);
844       open csr_sbc_core_object_exists(l_core_object_id);
845       fetch csr_sbc_core_object_exists into l_count;
846       close csr_sbc_core_object_exists;
847       return l_count > 0;
848     elsif p_object_type = g_siv_lookup_type then
849       hr_utility.set_location(l_proc, 80);
850       open csr_siv_core_object_exists(l_core_object_id);
851       fetch csr_siv_core_object_exists into l_count;
852       close csr_siv_core_object_exists;
853       return l_count > 0;
854     elsif p_object_type = g_sbf_lookup_type then
855       hr_utility.set_location(l_proc, 90);
856       open csr_sbf_core_object_exists(l_core_object_id);
857       fetch csr_sbf_core_object_exists into l_count;
858       close csr_sbf_core_object_exists;
859       return l_count > 0;
860     elsif p_object_type = g_spr_lookup_type then
861       hr_utility.set_location(l_proc, 95);
862       open csr_spr_core_object_exists(l_core_object_id);
863       fetch csr_spr_core_object_exists into l_count;
864       close csr_spr_core_object_exists;
865       return l_count > 0;
866     elsif p_object_type = g_sfr_lookup_type then
867       hr_utility.set_location(l_proc, 100);
868       open csr_sfr_core_object_exists(l_core_object_id);
869       fetch csr_sfr_core_object_exists into l_count;
870       close csr_sfr_core_object_exists;
871       return l_count > 0;
872     elsif p_object_type = g_sir_lookup_type then
873       hr_utility.set_location(l_proc, 110);
874       open csr_sir_core_object_exists(l_core_object_id);
875       fetch csr_sir_core_object_exists into l_count;
876       close csr_sir_core_object_exists;
877       return l_count > 0;
878     elsif p_object_type = g_seu_lookup_type then
879       hr_utility.set_location(l_proc, 120);
880       open csr_seu_core_object_exists(l_core_object_id);
881       fetch csr_seu_core_object_exists into l_count;
882       close csr_seu_core_object_exists;
883       return l_count > 0;
884     elsif p_object_type = g_sgb_lookup_type then
885       hr_utility.set_location(l_proc, 130);
886       open csr_sgb_core_object_exists(l_core_object_id);
887       fetch csr_sgb_core_object_exists into l_count;
888       close csr_sgb_core_object_exists;
889       return l_count > 0;
890     elsif p_object_type = g_sba_lookup_type then
891       hr_utility.set_location(l_proc, 140);
892       open csr_sba_core_object_exists(l_core_object_id);
893       fetch csr_sba_core_object_exists into l_count;
894       close csr_sba_core_object_exists;
895       return l_count > 0;
896     end if;
897     --
898     -- Should never get here.
899     --
900     hr_general.assert_condition(false);
901   end if;
902   --
903   -- Object does not exist because no rows in PAY_TEMPLATE_CORE_OBJECTS.
904   --
905   return false;
906   --
907   hr_utility.set_location('Leaving:'|| l_proc, 500);
908 exception
909   when others then
910     hr_utility.set_location('Leaving:'|| l_proc, 510);
911     if csr_sf_core_object_exists%isopen then
912       close csr_sf_core_object_exists;
913     end if;
914     --
915     if csr_set_core_object_exists%isopen then
916       close csr_set_core_object_exists;
917     end if;
918     --
919     if csr_sbt_core_object_exists%isopen then
920       close csr_sbt_core_object_exists;
921     end if;
922     --
923     if csr_sdb_core_object_exists%isopen then
924       close csr_sdb_core_object_exists;
925     end if;
926     --
927     if csr_ssr_core_object_exists%isopen then
928       close csr_ssr_core_object_exists;
929     end if;
930     --
931     if csr_sbc_core_object_exists%isopen then
932       close csr_sbc_core_object_exists;
933     end if;
934     --
935     if csr_siv_core_object_exists%isopen then
936       close csr_siv_core_object_exists;
937     end if;
938     --
939     if csr_sbf_core_object_exists%isopen then
940       close csr_sbf_core_object_exists;
941     end if;
942     --
943     if csr_spr_core_object_exists%isopen then
944       close csr_spr_core_object_exists;
945     end if;
946     --
947     if csr_sfr_core_object_exists%isopen then
948       close csr_sfr_core_object_exists;
949     end if;
950     --
951     if csr_sir_core_object_exists%isopen then
952       close csr_sir_core_object_exists;
953     end if;
954     --
955     if csr_seu_core_object_exists%isopen then
956       close csr_seu_core_object_exists;
957     end if;
958     --
959     if csr_sgb_core_object_exists%isopen then
960       close csr_sgb_core_object_exists;
961     end if;
962     --
963     if csr_sba_core_object_exists%isopen then
964       close csr_sba_core_object_exists;
965     end if;
966     --
967     raise;
968 end core_object_exists;
969 -- ----------------------------------------------------------------------------
970 -- |-------------------------< update_core_objects >--------------------------|
971 -- ----------------------------------------------------------------------------
972 procedure update_core_objects
973   (p_effective_date       in     date
974   ,p_template_id          in     number
975   ,p_core_object_type     in     varchar2
976   ,p_shadow_object_id     in     number
977   ,p_core_object_id       in     number
978   ,p_core_objects         in out nocopy pay_element_template_util.t_core_objects
979   ) is
980   l_proc                varchar2(72) := g_package||'update_core_objects';
981   l_core_object         pay_tco_shd.g_rec_type;
982 begin
983   hr_utility.set_location('Entering:'|| l_proc, 10);
984   --
985   if p_core_objects.exists(p_shadow_object_id) then
986     l_core_object := p_core_objects(p_shadow_object_id);
987     l_core_object.core_object_id := p_core_object_id;
988     pay_tco_upd.upd(l_core_object);
989   else
990     l_core_object.template_id       := p_template_id;
991     l_core_object.core_object_type  := p_core_object_type;
992     l_core_object.shadow_object_id  := p_shadow_object_id;
993     l_core_object.core_object_id    := p_core_object_id;
994     l_core_object.effective_date    := p_effective_date;
995     pay_tco_ins.ins(l_core_object);
996   end if;
997   --
998   -- Insert to allow indexing by the shadow_object_id.
999   --
1000   p_core_objects(p_shadow_object_id) := l_core_object;
1001   hr_utility.set_location('Leaving:'|| l_proc, 100);
1002 end update_core_objects;
1003 -- ----------------------------------------------------------------------------
1004 -- |---------------------------< new_core_object >----------------------------|
1005 -- ----------------------------------------------------------------------------
1006 function new_core_object
1007   (p_core_object                   in     pay_tco_shd.g_rec_type
1008   ,p_all_core_objects              in     pay_element_template_util.t_core_objects
1009   ) return boolean is
1010   l_proc                varchar2(72) := g_package||'new_core_object';
1011   l_template_core_object_id        number;
1012 begin
1013   hr_utility.set_location('Entering:'|| l_proc, 100);
1014   l_template_core_object_id := p_core_object.template_core_object_id;
1015   return not p_all_core_objects.exists(l_template_core_object_id);
1016 end new_core_object;
1017 -- ----------------------------------------------------------------------------
1018 -- |---------------------------< gen_formulas >-------------------------------|
1019 -- ----------------------------------------------------------------------------
1020 procedure gen_formulas
1021   (p_effective_date          in     date
1022   ,p_template_id             in     number
1023   ,p_hr_only                 in     boolean
1024   ,p_formulas                in     pay_element_template_util.t_formulas
1025   ,p_sf_core_objects         in out nocopy pay_element_template_util.t_core_objects
1026   ) is
1027   l_proc                varchar2(72) := g_package||'gen_formulas';
1028   l_shadow_object_id    number;
1029   i                     number;
1030   --
1031   l_rowid               varchar2(128);
1032   l_formula_id          number;
1033   l_formula_name        varchar2(80);
1034   l_last_update_date    date;
1035   l_formula_type_id     number;
1036   --
1037 begin
1038   hr_utility.set_location('Entering:'|| l_proc, 10);
1039   --
1040   i := p_formulas.first;
1041   loop
1042     exit when not p_formulas.exists(i);
1043     --
1044     -- Only generate if a generated core object does not exist.
1045     --
1046     l_shadow_object_id := i;
1047     if not core_object_exists
1048            (p_shadow_object_id => l_shadow_object_id
1049            ,p_object_type      => g_sf_lookup_type
1050            ,p_core_objects     => p_sf_core_objects
1051            )
1052        and
1053        (
1054          not p_hr_only or
1055          (p_hr_only and
1056            (
1057              p_formulas(i).formula_type_name = g_input_val_formula_type
1058            )
1059          )
1060        )
1061     then
1062       hr_utility.set_location(l_proc, 20);
1063       --
1064       -- Set up the in/out parameters.
1065       --
1066       l_formula_name := p_formulas(i).formula_name;
1067       l_formula_id := null;
1068       l_rowid := null;
1069       l_last_update_date := hr_api.g_sys;
1070 
1071       --
1072       -- Get the formula type. If formula type is null then consider
1073       -- formula to be of type 'Oracle Payroll' and use g_payroll_formula_id.
1074       --
1075       if p_formulas(i).formula_type_name is null or
1076         p_formulas(i).formula_type_name = g_payroll_formula_type then
1077         l_formula_type_id := g_payroll_formula_type_id;
1078       else
1079         l_formula_type_id := get_formula_type_id(p_formulas(i).formula_type_name);
1080       end if;
1081       --
1082       -- Insert the formula row.
1083       --
1084       ff_formulas_f_pkg.insert_row
1085       (x_rowid                      => l_rowid
1086       ,x_formula_id                 => l_formula_id
1087       ,x_effective_start_date       => p_effective_date
1088       ,x_effective_end_date         => hr_api.g_eot
1089       ,x_business_group_id          => p_formulas(i).business_group_id
1090       ,x_legislation_code           => null
1091       ,x_formula_type_id            => l_formula_type_id
1092       ,x_formula_name               => l_formula_name
1093       ,x_description                => p_formulas(i).description
1094       ,x_formula_text               => p_formulas(i).formula_text
1095       ,x_sticky_flag                => 'N'
1096       ,x_last_update_date           => l_last_update_date
1097       );
1098       --
1099       -- Set up the core object table rows.
1100       --
1101       hr_utility.set_location(l_proc, 30);
1102       update_core_objects
1103       (p_effective_date             => p_effective_date
1104       ,p_template_id                => p_template_id
1105       ,p_core_object_type           => g_sf_lookup_type
1106       ,p_shadow_object_id           => l_shadow_object_id
1107       ,p_core_object_id             => l_formula_id
1108       ,p_core_objects               => p_sf_core_objects
1109       );
1110     end if;
1111     --
1112     i := p_formulas.next(i);
1113   end loop;
1114   --
1115   hr_utility.set_location('Leaving:'|| l_proc, 100);
1116 end gen_formulas;
1117 -- ----------------------------------------------------------------------------
1118 -- |----------------------------< gen_balance_type >--------------------------|
1119 -- ----------------------------------------------------------------------------
1120 procedure gen_balance_type
1121   (p_effective_date    in     date
1122   ,p_template_id       in     number
1123   ,p_business_group_id in     number
1124   ,p_legislation_code  in     varchar2
1125   ,p_balance_type_id   in     number
1126   ,p_balance_types     in     pay_element_template_util.t_balance_types
1127   ,p_siv_core_objects  in     pay_element_template_util.t_core_objects
1128   ,p_sbt_core_objects  in out nocopy pay_element_template_util.t_core_objects
1129   ) is
1130   l_proc                 varchar2(72) := g_package||'gen_balance_type';
1131   l_rowid                varchar2(128);
1132   l_balance_type_id      number;
1133   l_base_balance_type_id number;
1134   l_balance_category_id  number;
1135   l_input_value_id       number;
1136   l_id                   number;
1137   l_id1                  number;
1138 begin
1139   hr_utility.set_location('Entering:' || l_proc, 10);
1140   --
1141   -- Only generate if a generated core object does not exist.
1142   --
1143   l_id := p_balance_type_id;
1144   if not core_object_exists
1145          (p_shadow_object_id => l_id
1146          ,p_object_type      => g_sbt_lookup_type
1147          ,p_core_objects     => p_sbt_core_objects
1148          ) then
1149     hr_utility.set_location(l_proc, 20);
1150     --
1151     -- Set up the in/out parameters.
1152     --
1153     l_balance_type_id := null;
1154     l_rowid := null;
1155     --
1156     -- Set up the balance_category_id value.
1157     --
1158     l_balance_category_id := null;
1159     if p_balance_types(l_id).category_name is not null then
1160       --
1161       l_balance_category_id :=
1162       get_bal_category_id
1163       (p_effective_date    => p_effective_date
1164       ,p_business_group_id => p_business_group_id
1165       ,p_legislation_code  => p_legislation_code
1166       ,p_bal_category      => p_balance_types(l_id).category_name
1167       );
1168     end if;
1169     --
1170     -- Set up the base_balance_type_id value.
1171     --
1172     l_base_balance_type_id := null;
1173     if p_balance_types(l_id).base_balance_name is not null then
1174       --
1175       -- External base balance.
1176       --
1177       l_base_balance_type_id :=
1178       get_balance_id
1179       (p_business_group_id => p_business_group_id
1180       ,p_legislation_code  => p_legislation_code
1181       ,p_balance           => p_balance_types(l_id).base_balance_name
1182       );
1183       --
1184     elsif p_balance_types(l_id).base_balance_type_id is not null then
1185       --
1186       -- The base balance type may or may not be generated. If it's not been
1187       -- generated then make a recursive call to generate it.
1188       --
1189       l_id1 := p_balance_types(l_id).base_balance_type_id;
1190       if not core_object_exists
1191              (p_shadow_object_id => l_id1
1192              ,p_object_type      => g_sbt_lookup_type
1193              ,p_core_objects     => p_sbt_core_objects
1194              ) then
1195         gen_balance_type
1196         (p_effective_date    => p_effective_date
1197         ,p_template_id       => p_template_id
1198         ,p_business_group_id => p_business_group_id
1199         ,p_legislation_code  => p_legislation_code
1200         ,p_balance_type_id   => l_id1
1201         ,p_balance_types     => p_balance_types
1202         ,p_siv_core_objects  => p_siv_core_objects
1203         ,p_sbt_core_objects  => p_sbt_core_objects
1204         );
1205       end if;
1206       --
1207       -- Core object exists at this stage.
1208       --
1209       l_base_balance_type_id := p_sbt_core_objects(l_id1).core_object_id;
1210     end if;
1211     --
1212     -- Set up the input_value_id value.
1213     --
1214     l_input_value_id := null;
1215     if p_balance_types(l_id).input_value_id is not null then
1216       l_input_value_id :=
1217       p_siv_core_objects(p_balance_types(l_id).input_value_id).core_object_id;
1218     end if;
1219     --
1220     -- Insert the balance row.
1221     --
1222     pay_balance_types_pkg.insert_row
1223     (x_rowid                        => l_rowid
1224     ,x_balance_type_id              => l_balance_type_id
1225     ,x_business_group_id            => p_business_group_id
1226     ,x_legislation_code             => null
1227     ,x_currency_code                => p_balance_types(l_id).currency_code
1228     ,x_assignment_remuneration_flag =>
1229      p_balance_types(l_id).assignment_remuneration_flag
1230     ,x_balance_name                 => p_balance_types(l_id).balance_name
1231     ,x_base_balance_name            => p_balance_types(l_id).balance_name
1232     ,x_balance_uom                  => p_balance_types(l_id).balance_uom
1233     ,x_comments                     => p_balance_types(l_id).comments
1234     ,x_legislation_subgroup         => null
1235     ,x_reporting_name               => p_balance_types(l_id).reporting_name
1236     ,x_attribute_category           => p_balance_types(l_id).attribute_category
1237     ,x_attribute1                   => p_balance_types(l_id).attribute1
1238     ,x_attribute2                   => p_balance_types(l_id).attribute2
1239     ,x_attribute3                   => p_balance_types(l_id).attribute3
1240     ,x_attribute4                   => p_balance_types(l_id).attribute4
1241     ,x_attribute5                   => p_balance_types(l_id).attribute5
1242     ,x_attribute6                   => p_balance_types(l_id).attribute6
1243     ,x_attribute7                   => p_balance_types(l_id).attribute7
1244     ,x_attribute8                   => p_balance_types(l_id).attribute8
1245     ,x_attribute9                   => p_balance_types(l_id).attribute9
1246     ,x_attribute10                  => p_balance_types(l_id).attribute10
1247     ,x_attribute11                  => p_balance_types(l_id).attribute11
1248     ,x_attribute12                  => p_balance_types(l_id).attribute12
1249     ,x_attribute13                  => p_balance_types(l_id).attribute13
1250     ,x_attribute14                  => p_balance_types(l_id).attribute14
1251     ,x_attribute15                  => p_balance_types(l_id).attribute15
1252     ,x_attribute16                  => p_balance_types(l_id).attribute16
1253     ,x_attribute17                  => p_balance_types(l_id).attribute17
1254     ,x_attribute18                  => p_balance_types(l_id).attribute18
1255     ,x_attribute19                  => p_balance_types(l_id).attribute19
1256     ,x_attribute20                  => p_balance_types(l_id).attribute20
1257     ,x_balance_category_id          => l_balance_category_id
1258     ,x_base_balance_type_id         => l_base_balance_type_id
1259     ,x_input_value_id               => l_input_value_id
1260     );
1261     --
1262     -- Set up the core object table rows.
1263     --
1264     hr_utility.set_location(l_proc, 30);
1265     update_core_objects
1266     (p_effective_date             => p_effective_date
1267     ,p_template_id                => p_template_id
1268     ,p_core_object_type           => g_sbt_lookup_type
1269     ,p_shadow_object_id           => p_balance_type_id
1270     ,p_core_object_id             => l_balance_type_id
1271     ,p_core_objects               => p_sbt_core_objects
1272     );
1273   end if;
1274   hr_utility.set_location('Leaving:'|| l_proc, 100);
1275 end gen_balance_type;
1276 -- ----------------------------------------------------------------------------
1277 -- |---------------------------< gen_balance_types >--------------------------|
1278 -- ----------------------------------------------------------------------------
1279 procedure gen_balance_types
1280   (p_effective_date    in     date
1281   ,p_template_id       in     number
1282   ,p_business_group_id in     number
1283   ,p_legislation_code  in     varchar2
1284   ,p_balance_types     in     pay_element_template_util.t_balance_types
1285   ,p_siv_core_objects  in     pay_element_template_util.t_core_objects
1286   ,p_sbt_core_objects  in out nocopy pay_element_template_util.t_core_objects
1287   ) is
1288   l_proc                 varchar2(72) := g_package||'gen_balance_types';
1289   i                      number;
1290   --
1291 begin
1292   hr_utility.set_location('Entering:'|| l_proc, 10);
1293   --
1294   i := p_balance_types.first;
1295   loop
1296     exit when not p_balance_types.exists(i);
1297     --
1298     gen_balance_type
1299     (p_effective_date    => p_effective_date
1300     ,p_template_id       => p_template_id
1301     ,p_business_group_id => p_business_group_id
1302     ,p_legislation_code  => p_legislation_code
1303     ,p_balance_type_id   => i
1304     ,p_balance_types     => p_balance_types
1305     ,p_siv_core_objects  => p_siv_core_objects
1306     ,p_sbt_core_objects  => p_sbt_core_objects
1307     );
1308     --
1309     i := p_balance_types.next(i);
1310   end loop;
1311   --
1312   hr_utility.set_location('Leaving:'|| l_proc, 100);
1313 end gen_balance_types;
1314 -- ----------------------------------------------------------------------------
1315 -- |--------------------------< gen_defined_balances >------------------------|
1316 -- ----------------------------------------------------------------------------
1317 procedure gen_defined_balances
1318   (p_effective_date      in     date
1319   ,p_template_id         in     number
1320   ,p_business_group_id   in     number
1321   ,p_legislation_code    in     varchar2
1322   ,p_defined_balances    in     pay_element_template_util.t_defined_balances
1323   ,p_sbt_core_objects    in     pay_element_template_util.t_core_objects
1324   ,p_sdb_core_objects    in out nocopy pay_element_template_util.t_core_objects
1325   ) is
1326   l_proc                   varchar2(72) := g_package||'gen_defined_balances';
1327   l_shadow_object_id       number;
1328   i                        number;
1329   --
1330   l_rowid                  varchar2(128);
1331   l_balance_type_id        number;
1332   l_shadow_balance_type_id number;
1333   l_balance_dimension_id   number;
1334   l_defined_balance_id     number;
1335   --
1336 begin
1337   hr_utility.set_location('Entering:'|| l_proc, 10);
1338   --
1339   i := p_defined_balances.first;
1340   loop
1341     exit when not p_defined_balances.exists(i);
1342     --
1343     -- Only generate if a generated core object does not exist.
1344     --
1345     l_shadow_object_id := i;
1346     if not core_object_exists
1347            (p_shadow_object_id => l_shadow_object_id
1348            ,p_object_type      => g_sdb_lookup_type
1349            ,p_core_objects     => p_sdb_core_objects
1350            ) then
1351       hr_utility.set_location(l_proc, 20);
1352       --
1353       -- Set up the in/out parameters.
1354       --
1355       l_defined_balance_id := null;
1356       l_rowid := null;
1357       --
1358       -- Set up balance_dimension_id.
1359       --
1360       l_balance_dimension_id :=
1361       get_bal_dimension_id
1362       (p_business_group_id => p_business_group_id
1363       ,p_legislation_code  => p_legislation_code
1364       ,p_bal_dimension     => p_defined_balances(i).dimension_name
1365       );
1366       --
1367       -- Set up the balance_balance_type_id from the table of generated
1368       -- balance types.
1369       --
1370       l_shadow_balance_type_id := p_defined_balances(i).balance_type_id;
1371       l_balance_type_id :=
1372       p_sbt_core_objects(l_shadow_balance_type_id).core_object_id;
1373       --
1374       -- Insert the defined balance row.
1375       --
1376       pay_defined_balances_pkg.insert_row
1377       (x_rowid                        => l_rowid
1378       ,x_defined_balance_id           => l_defined_balance_id
1379       ,x_business_group_id            => p_business_group_id
1380       ,x_legislation_code             => null
1381       ,x_balance_type_id              => l_balance_type_id
1382       ,x_balance_dimension_id         => l_balance_dimension_id
1383       ,x_force_latest_balance_flag    =>
1384              p_defined_balances(i).force_latest_balance_flag
1385       ,x_legislation_subgroup         => null
1386       ,x_grossup_allowed_flag         =>
1387              nvl(p_defined_balances(i).grossup_allowed_flag, 'N')
1388       );
1389       --
1390       -- Set up the core object table rows.
1391       --
1392       hr_utility.set_location(l_proc, 30);
1393       update_core_objects
1394       (p_effective_date             => p_effective_date
1395       ,p_template_id                => p_template_id
1396       ,p_core_object_type           => g_sdb_lookup_type
1397       ,p_shadow_object_id           => l_shadow_object_id
1398       ,p_core_object_id             => l_defined_balance_id
1399       ,p_core_objects               => p_sdb_core_objects
1400       );
1401     end if;
1402     --
1403     i := p_defined_balances.next(i);
1404   end loop;
1405   --
1406   hr_utility.set_location('Leaving:'|| l_proc, 100);
1407 end gen_defined_balances;
1408 -- ----------------------------------------------------------------------------
1409 -- |---------------------------< gen_element_types >--------------------------|
1410 -- ----------------------------------------------------------------------------
1411 procedure gen_element_types
1412   (p_effective_date           in     date
1413   ,p_template_id              in     number
1414   ,p_business_group_id        in     number
1415   ,p_hr_only                  in     boolean
1416   ,p_legislation_code         in     varchar2
1417   ,p_base_processing_priority in     number
1418   ,p_element_types            in     pay_element_template_util.t_element_types
1419   ,p_sf_core_objects          in     pay_element_template_util.t_core_objects
1420   ,p_set_core_objects         in out nocopy pay_element_template_util.t_core_objects
1421   ,p_spr_core_objects         in out nocopy pay_element_template_util.t_core_objects
1422   ) is
1423   l_proc                      varchar2(72) := g_package||'gen_element_types';
1424   l_shadow_object_id          number;
1425   i                           number;
1426   --
1427   l_rowid                     varchar2(128);
1428   l_element_type_id           number;
1429   l_ele_classification_id     number;
1430   l_ben_classification_id     number;
1431   l_formula_id                number;
1432   l_iterative_formula_id      number;
1433   l_processing_priority       number;
1434   l_non_payments_flag         varchar2(30);
1435   l_status_processing_rule_id number;
1436   l_bus_grp_currency_code     varchar2(30);
1437   l_proration_group_id        number;
1438   l_proration_formula_id      number;
1439   l_recalc_event_group_id     number;
1440   --
1441 begin
1442   hr_utility.set_location('Entering:'|| l_proc, 10);
1443   --
1444   l_bus_grp_currency_code :=
1445   hr_general.default_currency_code(p_business_group_id);
1446   --
1447   i := p_element_types.first;
1448   loop
1449     exit when not p_element_types.exists(i);
1450     --
1451     -- Only generate if a generated core object does not exist.
1452     --
1453     l_shadow_object_id := i;
1454     if not core_object_exists
1455            (p_shadow_object_id => l_shadow_object_id
1456            ,p_object_type      => g_set_lookup_type
1457            ,p_core_objects     => p_set_core_objects
1458            ) then
1459       hr_utility.set_location(l_proc, 20);
1460       --
1461       -- Set up the in/out parameters.
1462       --
1463       l_element_type_id := null;
1464       l_rowid := null;
1465       --
1466       -- Set up processing priority.
1467       --
1468       l_processing_priority := p_base_processing_priority +
1469                                p_element_types(i).relative_processing_priority;
1470       --
1471       -- Map names to id values.
1472       --
1473       l_ele_classification_id :=
1474       get_ele_classification_id
1475       (p_business_group_id => p_business_group_id
1476       ,p_legislation_code  => p_legislation_code
1477       ,p_classification    => p_element_types(i).classification_name
1478       ,p_non_payments_flag => l_non_payments_flag
1479       );
1480       --
1481       l_ben_classification_id := null;
1482       if p_element_types(i).benefit_classification_name is not null then
1483         l_ben_classification_id :=
1484         get_ben_classification_id
1485         (p_business_group_id  => p_business_group_id
1486         ,p_legislation_code   => p_legislation_code
1487         ,p_ben_classification => p_element_types(i).benefit_classification_name
1488         );
1489       end if;
1490       --
1491       l_formula_id := null;
1492       if p_element_types(i).skip_formula is not null then
1493         l_formula_id :=
1494         get_formula_id
1495         (p_effective_date     => p_effective_date
1496         ,p_business_group_id  => p_business_group_id
1497         ,p_legislation_code   => p_legislation_code
1498         ,p_formula_type_id    => g_skip_formula_type_id
1499         ,p_formula            => p_element_types(i).skip_formula
1500         );
1501       end if;
1502       --
1503       l_iterative_formula_id := null;
1504       if p_element_types(i).iterative_formula_name is not null then
1505         l_iterative_formula_id :=
1506         get_formula_id
1507         (p_effective_date     => p_effective_date
1508         ,p_business_group_id  => p_business_group_id
1509         ,p_legislation_code   => p_legislation_code
1510         ,p_formula_type_id    => g_iterative_formula_type_id
1511         ,p_formula            => p_element_types(i).iterative_formula_name
1512         );
1513       end if;
1514       --
1515       l_proration_formula_id := null;
1516       if p_element_types(i).proration_formula is not null then
1517         l_proration_formula_id :=
1518         get_formula_id
1519         (p_effective_date     => p_effective_date
1520         ,p_business_group_id  => p_business_group_id
1521         ,p_legislation_code   => p_legislation_code
1522         ,p_formula_type_id    => g_proration_formula_type_id
1523         ,p_formula            => p_element_types(i).proration_formula
1524         );
1525       end if;
1526       --
1527       l_proration_group_id := null;
1528       if p_element_types(i).proration_group is not null then
1529         l_proration_group_id :=
1530         get_event_group_id
1531         (p_business_group_id => p_business_group_id
1532         ,p_legislation_code  => p_legislation_code
1533         ,p_event_group_type  => g_proration_event_group
1534         ,p_event_group       => p_element_types(i).proration_group
1535         );
1536       end if;
1537       --
1538       l_recalc_event_group_id := null;
1539       if p_element_types(i).recalc_event_group is not null then
1540         l_recalc_event_group_id :=
1541         get_event_group_id
1542         (p_business_group_id => p_business_group_id
1543         ,p_legislation_code  => p_legislation_code
1544         ,p_event_group_type  => g_retro_event_group
1545         ,p_event_group       => p_element_types(i).recalc_event_group
1546         );
1547       end if;
1548 
1549       --
1550       -- The business rule checking has to be performed here because the
1551       -- creation API does not contain business rule checking code!
1552       --
1553       hr_utility.set_location(l_proc, 40);
1554       hr_elements.chk_element_type
1555       (p_element_name                 => p_element_types(i).element_name
1556       ,p_element_type_id              => null
1557       ,p_val_start_date               => p_effective_date
1558       ,p_val_end_date                 => hr_api.g_eot
1559       ,p_reporting_name               => p_element_types(i).reporting_name
1560       ,p_rowid                        => null
1561       ,p_recurring_flag               => p_element_types(i).processing_type
1562       ,p_standard_flag                => p_element_types(i).standard_link_flag
1563       ,p_scndry_ent_allwd_flag        =>
1564        p_element_types(i).additional_entry_allowed_flag
1565       ,p_process_in_run_flag          => p_element_types(i).process_in_run_flag
1566       ,p_indirect_only_flag           => p_element_types(i).indirect_only_flag
1567       ,p_adjustment_only_flag         => p_element_types(i).adjustment_only_flag
1568       ,p_multiply_value_flag          => p_element_types(i).multiply_value_flag
1569       ,p_classification_type          => l_non_payments_flag
1570       ,p_output_currency_code         => p_element_types(i).output_currency_code
1571       ,p_input_currency_code          => p_element_types(i).input_currency_code
1572       ,p_business_group_id            => p_business_group_id
1573       ,p_legislation_code             => p_legislation_code
1574       ,p_bus_grp_currency_code        => l_bus_grp_currency_code
1575       );
1576       --
1577       -- Insert the element row.
1578       --
1579       hr_utility.set_location(l_proc, 50);
1580       pay_element_types_pkg.insert_row
1581       (p_rowid                        => l_rowid
1582       ,p_element_type_id              => l_element_type_id
1583       ,p_effective_start_date         => p_effective_date
1584       ,p_effective_end_date           => hr_api.g_eot
1585       ,p_business_group_id            => p_business_group_id
1586       ,p_legislation_code             => null
1587       ,p_formula_id                   => l_formula_id
1588       ,p_input_currency_code          => p_element_types(i).input_currency_code
1589       ,p_output_currency_code         => p_element_types(i).output_currency_code
1590       ,p_classification_id            => l_ele_classification_id
1591       ,p_benefit_classification_id    => l_ben_classification_id
1592       ,p_additional_entry_allowed     =>
1593        p_element_types(i).additional_entry_allowed_flag
1594       ,p_adjustment_only_flag         => p_element_types(i).adjustment_only_flag
1595       ,p_closed_for_entry_flag        =>
1596        p_element_types(i).closed_for_entry_flag
1597       ,p_element_name                 => p_element_types(i).element_name
1598       ,p_base_element_name            => p_element_types(i).element_name
1599       ,p_indirect_only_flag           => p_element_types(i).indirect_only_flag
1600       ,p_multiple_entries_allowed     =>
1601        p_element_types(i).multiple_entries_allowed_flag
1602       ,p_multiply_value_flag          => p_element_types(i).multiply_value_flag
1603       ,p_post_termination_rule        =>
1604        p_element_types(i).post_termination_rule
1605       ,p_process_in_run_flag          => p_element_types(i).process_in_run_flag
1606       ,p_processing_priority          => l_processing_priority
1607       ,p_processing_type              => p_element_types(i).processing_type
1608       ,p_standard_link_flag           => p_element_types(i).standard_link_flag
1609       ,p_comment_id                   => null
1610       ,p_description                  => p_element_types(i).description
1611       ,p_legislation_subgroup         => null
1612       ,p_qualifying_age               => p_element_types(i).qualifying_age
1613       ,p_qualifying_length_of_service =>
1614        p_element_types(i).qualifying_length_of_service
1615       ,p_qualifying_units             => p_element_types(i).qualifying_units
1616       ,p_reporting_name               => p_element_types(i).reporting_name
1617       ,p_attribute_category           => p_element_types(i).attribute_category
1618       ,p_attribute1                   => p_element_types(i).attribute1
1619       ,p_attribute2                   => p_element_types(i).attribute2
1620       ,p_attribute3                   => p_element_types(i).attribute3
1621       ,p_attribute4                   => p_element_types(i).attribute4
1622       ,p_attribute5                   => p_element_types(i).attribute5
1623       ,p_attribute6                   => p_element_types(i).attribute6
1624       ,p_attribute7                   => p_element_types(i).attribute7
1625       ,p_attribute8                   => p_element_types(i).attribute8
1626       ,p_attribute9                   => p_element_types(i).attribute9
1627       ,p_attribute10                  => p_element_types(i).attribute10
1628       ,p_attribute11                  => p_element_types(i).attribute11
1629       ,p_attribute12                  => p_element_types(i).attribute12
1630       ,p_attribute13                  => p_element_types(i).attribute13
1631       ,p_attribute14                  => p_element_types(i).attribute14
1632       ,p_attribute15                  => p_element_types(i).attribute15
1633       ,p_attribute16                  => p_element_types(i).attribute16
1634       ,p_attribute17                  => p_element_types(i).attribute17
1635       ,p_attribute18                  => p_element_types(i).attribute18
1636       ,p_attribute19                  => p_element_types(i).attribute19
1637       ,p_attribute20                  => p_element_types(i).attribute20
1638       ,p_element_information_category =>
1639        p_element_types(i).element_information_category
1640       ,p_element_information1        => p_element_types(i).element_information1
1641       ,p_element_information2        => p_element_types(i).element_information2
1642       ,p_element_information3        => p_element_types(i).element_information3
1643       ,p_element_information4        => p_element_types(i).element_information4
1644       ,p_element_information5        => p_element_types(i).element_information5
1645       ,p_element_information6        => p_element_types(i).element_information6
1646       ,p_element_information7        => p_element_types(i).element_information7
1647       ,p_element_information8        => p_element_types(i).element_information8
1648       ,p_element_information9        => p_element_types(i).element_information9
1649       ,p_element_information10       => p_element_types(i).element_information10
1650       ,p_element_information11       => p_element_types(i).element_information11
1651       ,p_element_information12       => p_element_types(i).element_information12
1652       ,p_element_information13       => p_element_types(i).element_information13
1653       ,p_element_information14       => p_element_types(i).element_information14
1654       ,p_element_information15       => p_element_types(i).element_information15
1655       ,p_element_information16       => p_element_types(i).element_information16
1656       ,p_element_information17       => p_element_types(i).element_information17
1657       ,p_element_information18       => p_element_types(i).element_information18
1658       ,p_element_information19       => p_element_types(i).element_information19
1659       ,p_element_information20       => p_element_types(i).element_information20
1660       ,p_non_payments_flag           => l_non_payments_flag
1661       --
1662       -- Don't generate the additional input values for benefits. They should
1663       -- be defined in the template.
1664       --
1665       ,p_default_benefit_uom         => null
1666       ,p_contributions_used          => 'N'
1667       ,p_third_party_pay_only_flag   =>
1668        p_element_types(i).third_party_pay_only_flag
1669       ,p_retro_summ_ele_id           => null
1670       ,p_iterative_flag              => p_element_types(i).iterative_flag
1671       ,p_iterative_formula_id        => l_iterative_formula_id
1672       ,p_iterative_priority          => p_element_types(i).iterative_priority
1673       ,p_process_mode                => p_element_types(i).process_mode
1674       ,p_grossup_flag                => p_element_types(i).grossup_flag
1675       ,p_advance_indicator           => p_element_types(i).advance_indicator
1676       ,p_advance_payable             => p_element_types(i).advance_payable
1677       ,p_advance_deduction           => p_element_types(i).advance_deduction
1678       ,p_process_advance_entry       => p_element_types(i).process_advance_entry
1679       ,p_proration_group_id          => l_proration_group_id
1680       ,p_proration_formula_id        => l_proration_formula_id
1681       ,p_recalc_event_group_id       => l_recalc_event_group_id
1682       ,p_once_each_period_flag       => p_element_types(i).once_each_period_flag
1683       );
1684       --
1685       -- Set up the core object table rows.
1686       --
1687       hr_utility.set_location(l_proc, 40);
1688       update_core_objects
1689       (p_effective_date             => p_effective_date
1690       ,p_template_id                => p_template_id
1691       ,p_core_object_type           => g_set_lookup_type
1692       ,p_shadow_object_id           => l_shadow_object_id
1693       ,p_core_object_id             => l_element_type_id
1694       ,p_core_objects               => p_set_core_objects
1695       );
1696     end if;
1697     --
1698     -- Create a "Standard" status processing rule for the element if
1699     -- it is associated with a payroll formula. Note: for status processing
1700     -- rules, the shadow_object_id is that of the element type.
1701     --
1702     if not p_hr_only and
1703        p_element_types(i).payroll_formula_id is not null and
1704        not core_object_exists
1705            (p_shadow_object_id => i
1706            ,p_object_type      => g_spr_lookup_type
1707            ,p_core_objects     => p_spr_core_objects
1708            ) then
1709       hr_utility.set_location(l_proc, 50);
1710       --
1711       l_element_type_id := p_set_core_objects(i).core_object_id;
1712       l_shadow_object_id := p_element_types(i).payroll_formula_id;
1713       l_formula_id := p_sf_core_objects(l_shadow_object_id).core_object_id;
1714       l_rowid := null;
1715       l_status_processing_rule_id := null;
1716       --
1717       pay_status_rules_pkg.insert_row
1718       (x_rowid                      => l_rowid
1719       ,x_status_processing_rule_id  => l_status_processing_rule_id
1720       ,x_effective_start_date       => p_effective_date
1721       ,x_effective_end_date         => hr_api.g_eot
1722       ,x_business_group_id          => p_business_group_id
1723       ,x_legislation_code           => null
1724       ,x_element_type_id            => l_element_type_id
1725       ,x_assignment_status_type_id  => null
1726       ,x_formula_id                 => l_formula_id
1727       ,x_processing_rule            => 'P'
1728       ,x_comment_id                 => null
1729       ,x_legislation_subgroup       => null
1730       ,x_last_update_date           => hr_api.g_sys
1731       ,x_last_updated_by            => -1
1732       ,x_last_update_login          => -1
1733       ,x_created_by                 => -1
1734       ,x_creation_date              => hr_api.g_sys
1735       );
1736       --
1737       -- Set up the core object table rows.
1738       --
1739       hr_utility.set_location(l_proc, 60);
1740       update_core_objects
1741       (p_effective_date             => p_effective_date
1742       ,p_template_id                => p_template_id
1743       ,p_core_object_type           => g_spr_lookup_type
1744       ,p_shadow_object_id           => i
1745       ,p_core_object_id             => l_status_processing_rule_id
1746       ,p_core_objects               => p_spr_core_objects
1747       );
1748     end if;
1749     --
1750     i := p_element_types.next(i);
1751   end loop;
1752   --
1753   hr_utility.set_location('Leaving:'|| l_proc, 100);
1754 end gen_element_types;
1755 -- ----------------------------------------------------------------------------
1756 -- |----------------------< implicit_sub_classi_rules >-----------------------|
1757 -- ----------------------------------------------------------------------------
1758 procedure implicit_sub_classi_rules
1759 (p_effective_date      in     date
1760 ,p_template_id         in     number
1761 ,p_business_group_id   in     number
1762 ,p_legislation_code    in     varchar2
1763 ,p_all_core_objects    in     pay_element_template_util.t_core_objects
1764 ,p_set_core_objects    in     pay_element_template_util.t_core_objects
1765 ,p_sub_classi_rules    in     pay_element_template_util.t_sub_classi_rules
1766 ,p_ssr_core_objects    in out nocopy pay_element_template_util.t_core_objects
1767 ) is
1768 l_proc                 varchar2(72) := g_package||'implicit_sub_classi_rules';
1769 i                            number;
1770 l_shadow_object_id           number;
1771 l_element_type_id            number;
1772 l_shadow_element_type_id     number;
1773 l_ele_classification_id      number;
1774 l_sub_classification_rule_id number;
1775 l_non_payments_flag          varchar2(30);
1776 --
1777 -- Cursor to detect a sub-classification rule exists for a given
1778 -- element_type_id, and classification_id. Don't need to worry about
1779 -- date-track because this cursor is only executed if element_type_id
1780 -- is that of a newly created element.
1781 --
1782 cursor csr_sub_classi_rule
1783 (p_element_type_id   in     number
1784 ,p_classification_id in     number
1785 ) is
1786 select sub_classification_rule_id
1787 from   pay_sub_classification_rules_f
1788 where  element_type_id = p_element_type_id
1789 and    classification_id = p_classification_id;
1790 begin
1791   hr_utility.set_location('Entering:'|| l_proc, 10);
1792   i := p_sub_classi_rules.first;
1793   loop
1794     exit when not p_sub_classi_rules.exists(i);
1795     --
1796     l_shadow_element_type_id := p_sub_classi_rules(i).element_type_id;
1797     l_shadow_object_id := i;
1798     --
1799     -- Check to set if the element type is newly generated.
1800     --
1801     if new_core_object
1802        (p_set_core_objects(l_shadow_element_type_id)
1803        ,p_all_core_objects
1804        ) then
1805       l_element_type_id :=
1806       p_set_core_objects(l_shadow_element_type_id).core_object_id;
1807       --
1808       -- Get the classification_id.
1809       --
1810       l_ele_classification_id :=
1811       get_ele_classification_id
1812       (p_business_group_id => p_business_group_id
1813       ,p_legislation_code  => p_legislation_code
1814       ,p_classification    => p_sub_classi_rules(i).element_classification
1815       ,p_non_payments_flag => l_non_payments_flag
1816       );
1817       --
1818       -- Look for the implicitly created sub-classification rule.
1819       --
1820       hr_utility.set_location(l_proc, 20);
1821       open csr_sub_classi_rule(l_element_type_id ,l_ele_classification_id);
1822       fetch csr_sub_classi_rule into l_sub_classification_rule_id;
1823       if csr_sub_classi_rule%found then
1824         --
1825         -- Sub-classification rule found. Update the template generation
1826         -- table.
1827         --
1828         hr_utility.set_location(l_proc, 30);
1829         update_core_objects
1830         (p_effective_date             => p_effective_date
1831         ,p_template_id                => p_template_id
1832         ,p_core_object_type           => g_ssr_lookup_type
1833         ,p_shadow_object_id           => l_shadow_object_id
1834         ,p_core_object_id             => l_sub_classification_rule_id
1835         ,p_core_objects               => p_ssr_core_objects
1836         );
1837       end if;
1838       close csr_sub_classi_rule;
1839     end if;
1840     --
1841     i := p_sub_classi_rules.next(i);
1842   end loop;
1843   hr_utility.set_location('Leaving:'|| l_proc, 100);
1844 exception
1845   when others then
1846     hr_utility.set_location('Leaving:'|| l_proc, 110);
1847     if csr_sub_classi_rule%isopen then
1848       close csr_sub_classi_rule;
1849     end if;
1850     raise;
1851 end implicit_sub_classi_rules;
1852 -- ----------------------------------------------------------------------------
1853 -- |-------------------------< gen_sub_classi_rules >-------------------------|
1854 -- ----------------------------------------------------------------------------
1855 procedure gen_sub_classi_rules
1856   (p_effective_date      in     date
1857   ,p_template_id         in     number
1858   ,p_business_group_id   in     number
1859   ,p_legislation_code    in     varchar2
1860   ,p_sub_classi_rules    in     pay_element_template_util.t_sub_classi_rules
1861   ,p_set_core_objects    in     pay_element_template_util.t_core_objects
1862   ,p_ssr_core_objects    in out nocopy pay_element_template_util.t_core_objects
1863   ) is
1864   l_proc                       varchar2(72) := g_package||'gen_sub_classi_rules';
1865   l_shadow_object_id           number;
1866   i                            number;
1867   --
1868   l_rowid                      varchar2(128);
1869   l_element_type_id            number;
1870   l_sub_classification_rule_id number;
1871   l_ele_classification_id      number;
1872   l_non_payments_flag          varchar2(30);
1873   --
1874 begin
1875   hr_utility.set_location('Entering:'|| l_proc, 10);
1876   --
1877   i := p_sub_classi_rules.first;
1878   loop
1879     exit when not p_sub_classi_rules.exists(i);
1880     --
1881     -- Only generate if a generated core object does not exist.
1882     --
1883     l_shadow_object_id := i;
1884     if not core_object_exists
1885            (p_shadow_object_id => l_shadow_object_id
1886            ,p_object_type      => g_ssr_lookup_type
1887            ,p_core_objects     => p_ssr_core_objects
1888            ) then
1889       hr_utility.set_location(l_proc, 20);
1890       --
1891       -- Set up the in/out parameters.
1892       --
1893       l_sub_classification_rule_id := null;
1894       l_rowid := null;
1895       --
1896       -- Set up the classification_id.
1897       --
1898       l_ele_classification_id :=
1899       get_ele_classification_id
1900       (p_business_group_id => p_business_group_id
1901       ,p_legislation_code  => p_legislation_code
1902       ,p_classification    => p_sub_classi_rules(i).element_classification
1903       ,p_non_payments_flag => l_non_payments_flag
1904       );
1905       --
1906       -- Set up the element_type_id value.
1907       --
1908       l_element_type_id :=
1909       p_set_core_objects(p_sub_classi_rules(i).element_type_id).core_object_id;
1910       --
1911       -- Insert the secondary classification row.
1912       --
1913       hr_utility.set_location(l_proc, 30);
1914       pay_sub_class_rules_pkg.insert_row
1915       (p_rowid                        => l_rowid
1916       ,p_sub_classification_rule_id   => l_sub_classification_rule_id
1917       ,p_effective_start_date         => p_effective_date
1918       ,p_effective_end_date           => hr_api.g_eot
1919       ,p_element_type_id              => l_element_type_id
1920       ,p_classification_id            => l_ele_classification_id
1921       ,p_business_group_id            => p_business_group_id
1922       ,p_legislation_code             => null
1923       ,p_last_update_date             => hr_api.g_sys
1924       ,p_last_updated_by              => -1
1925       ,p_last_update_login            => -1
1926       ,p_created_by                   => -1
1927       ,p_creation_date                => hr_api.g_sys
1928       );
1929       --
1930       -- Set up the core object table rows.
1931       --
1932       hr_utility.set_location(l_proc, 30);
1933       update_core_objects
1934       (p_effective_date             => p_effective_date
1935       ,p_template_id                => p_template_id
1936       ,p_core_object_type           => g_ssr_lookup_type
1937       ,p_shadow_object_id           => l_shadow_object_id
1938       ,p_core_object_id             => l_sub_classification_rule_id
1939       ,p_core_objects               => p_ssr_core_objects
1940       );
1941     end if;
1942     --
1943     i := p_sub_classi_rules.next(i);
1944   end loop;
1945   --
1946   hr_utility.set_location('Leaving:'|| l_proc, 100);
1947 end gen_sub_classi_rules;
1948 -- ----------------------------------------------------------------------------
1949 -- |-------------------------< gen_balance_classis >--------------------------|
1950 -- ----------------------------------------------------------------------------
1951 procedure gen_balance_classis
1952   (p_effective_date      in     date
1953   ,p_template_id         in     number
1954   ,p_business_group_id   in     number
1955   ,p_legislation_code    in     varchar2
1956   ,p_balance_classis     in     pay_element_template_util.t_balance_classis
1957   ,p_sbt_core_objects    in     pay_element_template_util.t_core_objects
1958   ,p_sbc_core_objects    in out nocopy pay_element_template_util.t_core_objects
1959   ) is
1960   l_proc                      varchar2(72) := g_package||'gen_balance_classis';
1961   l_shadow_object_id          number;
1962   i                           number;
1963   --
1964   l_rowid                     varchar2(128);
1965   l_balance_type_id           number;
1966   l_balance_classification_id number;
1967   l_ele_classification_id     number;
1968   l_non_payments_flag         varchar2(30);
1969   --
1970 begin
1971   hr_utility.set_location('Entering:'|| l_proc, 10);
1972   --
1973   i := p_balance_classis.first;
1974   loop
1975     exit when not p_balance_classis.exists(i);
1976     --
1977     -- Only generate if a generated core object does not exist.
1978     --
1979     l_shadow_object_id := i;
1980     if not core_object_exists
1981            (p_shadow_object_id => l_shadow_object_id
1982            ,p_object_type      => g_sbc_lookup_type
1983            ,p_core_objects     => p_sbc_core_objects
1984            ) then
1985       hr_utility.set_location(l_proc, 20);
1986       --
1987       -- Set up the in/out parameters.
1988       --
1989       l_balance_classification_id := null;
1990       l_rowid := null;
1991       --
1992       -- Set up the classification_id.
1993       --
1994       l_ele_classification_id :=
1995       get_ele_classification_id
1996       (p_business_group_id => p_business_group_id
1997       ,p_legislation_code  => p_legislation_code
1998       ,p_classification    => p_balance_classis(i).element_classification
1999       ,p_non_payments_flag => l_non_payments_flag
2000       );
2001       --
2002       -- Set up the balance_type_id value.
2003       --
2004       l_balance_type_id :=
2005       p_sbt_core_objects(p_balance_classis(i).balance_type_id).core_object_id;
2006       --
2007       -- Insert the balance classification row.
2008       --
2009       pay_bal_classifications_pkg.insert_row
2010       (x_rowid                        => l_rowid
2011       ,x_balance_classification_id    => l_balance_classification_id
2012       ,x_business_group_id            => p_business_group_id
2013       ,x_legislation_code             => null
2014       ,x_balance_type_id              => l_balance_type_id
2015       ,x_classification_id            => l_ele_classification_id
2016       ,x_scale                        => p_balance_classis(i).scale
2017       ,x_legislation_subgroup         => null
2018       );
2019       --
2020       -- Set up the core object table rows.
2021       --
2022       hr_utility.set_location(l_proc, 30);
2023       update_core_objects
2024       (p_effective_date             => p_effective_date
2025       ,p_template_id                => p_template_id
2026       ,p_core_object_type           => g_sbc_lookup_type
2027       ,p_shadow_object_id           => l_shadow_object_id
2028       ,p_core_object_id             => l_balance_classification_id
2029       ,p_core_objects               => p_sbc_core_objects
2030       );
2031     end if;
2032     --
2033     i := p_balance_classis.next(i);
2034   end loop;
2035   --
2036   hr_utility.set_location('Leaving:'|| l_proc, 100);
2037 end gen_balance_classis;
2038 -- ----------------------------------------------------------------------------
2039 -- |-------------------------< implicit_input_values >------------------------|
2040 -- ----------------------------------------------------------------------------
2041 procedure implicit_input_values
2042   (p_effective_date   in     date
2043   ,p_template_id      in     number
2044   ,p_all_core_objects in     pay_element_template_util.t_core_objects
2045   ,p_set_core_objects in     pay_element_template_util.t_core_objects
2046   ,p_input_values     in     pay_element_template_util.t_input_values
2047   ,p_siv_core_objects in out nocopy pay_element_template_util.t_core_objects
2048   ) is
2049   l_proc                   varchar2(72) := g_package||'implicit_input_values';
2050   i                        number;
2051   l_shadow_element_type_id number;
2052   l_element_type_id        number;
2053   l_name                   varchar2(256);
2054   --
2055   -- Cursor to detect an input value of a given name for a given
2056   -- element_type_id exists. Don't need to worry about date-track because
2057   -- this cursor is only executed if element_type_id is that of a newly
2058   -- created element.
2059   -- Note: in R11i, this cursor looks for 'Pay Value' on the pay_input_values_f
2060   -- base table whereas, in R11 the translated name appears on the base table.
2061   --
2062   cursor csr_input_value
2063   (p_element_type_id in     number
2064   ,p_name            in     varchar2
2065   ) is
2066   select rowid
2067   ,      input_value_id
2068   ,      effective_start_date
2069   ,      effective_end_date
2070   ,      element_type_id
2071   ,      lookup_type
2072   ,      business_group_id
2073   ,      display_sequence
2074   ,      generate_db_items_flag
2075   ,      hot_default_flag
2076   ,      mandatory_flag
2077   ,      name
2078   ,      uom
2079   ,      default_value
2080   ,      max_value
2081   ,      min_value
2082   ,      warning_or_error
2083   from   pay_input_values_f
2084   where  element_type_id = p_element_type_id
2085   and    name = 'Pay Value';
2086   --
2087   l_input_value csr_input_value%rowtype;
2088 begin
2089   hr_utility.set_location('Entering:'|| l_proc, 10);
2090   i := p_input_values.first;
2091   loop
2092     exit when not p_input_values.exists(i);
2093     --
2094     -- Hate to hard code, but 'Pay Value' is the only implicitly
2095     -- generated input value that this code is concerned with. Check to
2096     -- see if this input value is a 'Pay Value' and that its element
2097     -- type is newly created.
2098     --
2099     l_shadow_element_type_id := p_input_values(i).element_type_id;
2100     l_name := upper(p_input_values(i).name);
2101     if (l_name = 'PAY VALUE') and
2102        new_core_object
2103        (p_set_core_objects(l_shadow_element_type_id)
2104        ,p_all_core_objects
2105        ) then
2106       --
2107       -- It's a new element, so check for the implicitly created 'Pay Value'.
2108       --
2109       hr_utility.set_location(l_proc, 20);
2110       l_element_type_id :=
2111       p_set_core_objects(l_shadow_element_type_id).core_object_id;
2112       open csr_input_value(l_element_type_id ,l_name);
2113       fetch csr_input_value into l_input_value;
2114       if csr_input_value%found then
2115         --
2116         -- Input value rule found. Update the template generation table.
2117         --
2118         hr_utility.set_location(l_proc, 30);
2119         update_core_objects
2120         (p_effective_date             => p_effective_date
2121         ,p_template_id                => p_template_id
2122         ,p_core_object_type           => g_siv_lookup_type
2123         ,p_shadow_object_id           => p_input_values(i).input_value_id
2124         ,p_core_object_id             => l_input_value.input_value_id
2125         ,p_core_objects               => p_siv_core_objects
2126         );
2127         --
2128         -- Update the input value if the mandatory flags differ - there
2129         -- shouldn't be any other difference of interest.
2130         --
2131         hr_utility.set_location(l_proc, 35);
2132         if l_input_value.mandatory_flag <>
2133            p_input_values(i).mandatory_flag then
2134           pay_input_values_pkg.update_row
2135           (p_rowid                  => l_input_value.rowid
2136           ,p_input_value_id         => l_input_value.input_value_id
2137           ,p_effective_start_date   => l_input_value.effective_start_date
2138           ,p_effective_end_date     => l_input_value.effective_end_date
2139           ,p_element_type_id        => l_input_value.element_type_id
2140           ,p_lookup_type            => l_input_value.lookup_type
2141           ,p_business_group_id      => l_input_value.business_group_id
2142           ,p_legislation_code       => null
2143           ,p_formula_id             => null
2144           ,p_display_sequence       => l_input_value.display_sequence
2145           ,p_generate_db_items_flag => l_input_value.generate_db_items_flag
2146           ,p_hot_default_flag       => l_input_value.hot_default_flag
2147           ,p_mandatory_flag         => p_input_values(i).mandatory_flag
2148           ,p_name                   => l_input_value.name
2149           ,p_uom                    => l_input_value.uom
2150           ,p_default_value          => l_input_value.default_value
2151           ,p_legislation_subgroup   => null
2152           ,p_max_value              => l_input_value.max_value
2153           ,p_min_value              => l_input_value.min_value
2154           ,p_warning_or_error       => l_input_value.warning_or_error
2155           ,p_recreate_db_items      => 'Y'
2156           ,p_base_name              => l_input_value.name
2157           );
2158         end if;
2159       end if;
2160       close csr_input_value;
2161     end if;
2162     --
2163     i := p_input_values.next(i);
2164   end loop;
2165   hr_utility.set_location('Leaving:'|| l_proc, 100);
2166 exception
2167   when others then
2168     hr_utility.set_location('Leaving:'|| l_proc, 110);
2169     if csr_input_value%isopen then
2170       close csr_input_value;
2171     end if;
2172     raise;
2173 end implicit_input_values;
2174 -- ----------------------------------------------------------------------------
2175 -- |---------------------------< gen_input_values >---------------------------|
2176 -- ----------------------------------------------------------------------------
2177 procedure gen_input_values
2178   (p_effective_date    in     date
2179   ,p_template_id       in     number
2180   ,p_business_group_id in     number
2181   ,p_legislation_code  in     varchar2
2182   ,p_input_values      in     pay_element_template_util.t_input_values
2183   ,p_set_core_objects  in     pay_element_template_util.t_core_objects
2184   ,p_sf_core_objects   in     pay_element_template_util.t_core_objects
2185   ,p_siv_core_objects  in out nocopy pay_element_template_util.t_core_objects
2186   ) is
2187   l_proc                       varchar2(72) := g_package||'gen_input_values';
2188   l_shadow_object_id           number;
2189   i                            number;
2190   --
2191   l_rowid                      varchar2(128);
2192   l_element_type_id            number;
2193   l_input_value_id             number;
2194   l_min_value                  varchar2(2000);
2195   l_max_value                  varchar2(2000);
2196   l_default_value              varchar2(2000);
2197   l_formula_id		       number;
2198   --
2199 begin
2200   hr_utility.set_location('Entering:'|| l_proc, 10);
2201   --
2202   i := p_input_values.first;
2203   loop
2204     exit when not p_input_values.exists(i);
2205     --
2206     -- Only generate if a generated core object does not exist.
2207     --
2208     l_shadow_object_id := i;
2209     if not core_object_exists
2210            (p_shadow_object_id => l_shadow_object_id
2211            ,p_object_type      => g_siv_lookup_type
2212            ,p_core_objects     => p_siv_core_objects
2213            ) then
2214       hr_utility.set_location(l_proc, 20);
2215       --
2216       -- Set up the in/out parameters.
2217       --
2218       l_input_value_id := null;
2219       l_rowid := null;
2220       --
2221       -- Set up the element_type_id value.
2222       --
2223       l_element_type_id:=
2224       p_set_core_objects(p_input_values(i).element_type_id).core_object_id;
2225       --
2226       -- Insert the input value row.
2227       --
2228       hr_utility.set_location(l_proc, 30);
2229       --
2230 
2231       l_default_value := p_input_values(i).default_value;
2232 
2233       if p_input_values(i).lookup_type is not null then
2234         l_min_value := null;
2235         l_max_value := null;
2236         l_formula_id := null;
2237       elsif p_input_values(i).formula_id is not null then
2238         l_min_value := null;
2239         l_max_value := null;
2240         l_formula_id :=
2241         p_sf_core_objects(p_input_values(i).formula_id).core_object_id;
2242       elsif p_input_values(i).input_validation_formula is not null then
2243         l_min_value := null;
2244         l_max_value := null;
2245         --
2246 	l_formula_id :=
2247         get_formula_id
2248         (p_effective_date     => p_effective_date
2249         ,p_business_group_id  => p_business_group_id
2250         ,p_legislation_code   => p_legislation_code
2251         ,p_formula_type_id    => g_input_val_formula_type_id
2252         ,p_formula            => p_input_values(i).input_validation_formula
2253         );
2254       else
2255         l_min_value := p_input_values(i).min_value;
2256         l_max_value := p_input_values(i).max_value;
2257         l_formula_id := null;
2258       end if;
2259 
2260       pay_input_values_pkg.insert_row
2261       (p_rowid                        => l_rowid
2262       ,p_input_value_id               => l_input_value_id
2263       ,p_effective_start_date         => p_effective_date
2264       ,p_effective_end_date           => hr_api.g_eot
2265       ,p_element_type_id              => l_element_type_id
2266       ,p_lookup_type                  => p_input_values(i).lookup_type
2267       ,p_business_group_id            => p_business_group_id
2268       ,p_legislation_code             => null
2269       ,p_formula_id                   => l_formula_id
2270       ,p_display_sequence             => p_input_values(i).display_sequence
2271       ,p_generate_db_items_flag       => p_input_values(i).generate_db_items_flag
2272       ,p_hot_default_flag             => p_input_values(i).hot_default_flag
2273       ,p_mandatory_flag               => p_input_values(i).mandatory_flag
2274       ,p_name                         => p_input_values(i).name
2275       ,p_base_name                    => p_input_values(i).name
2276       ,p_uom                          => p_input_values(i).uom
2277       ,p_default_value                => l_default_value
2278       ,p_legislation_subgroup         => null
2279       ,p_min_value                    => l_min_value
2280       ,p_max_value                    => l_max_value
2281       ,p_warning_or_error             => p_input_values(i).warning_or_error
2282       );
2283       --
2284       -- Set up the core object table rows.
2285       --
2286       hr_utility.set_location(l_proc, 30);
2287       update_core_objects
2288       (p_effective_date             => p_effective_date
2289       ,p_template_id                => p_template_id
2290       ,p_core_object_type           => g_siv_lookup_type
2291       ,p_shadow_object_id           => l_shadow_object_id
2292       ,p_core_object_id             => l_input_value_id
2293       ,p_core_objects               => p_siv_core_objects
2294       );
2295     end if;
2296     --
2297     i := p_input_values.next(i);
2298   end loop;
2299   --
2300   hr_utility.set_location('Leaving:'|| l_proc, 100);
2301 end gen_input_values;
2302 -- ----------------------------------------------------------------------------
2303 -- |---------------------------< gen_balance_feeds >--------------------------|
2304 -- ----------------------------------------------------------------------------
2305 procedure gen_balance_feeds
2306   (p_effective_date    in     date
2307   ,p_template_id       in     number
2308   ,p_business_group_id in     number
2309   ,p_legislation_code  in     varchar2
2310   ,p_balance_feeds     in     pay_element_template_util.t_balance_feeds
2311   ,p_sbt_core_objects  in     pay_element_template_util.t_core_objects
2312   ,p_siv_core_objects  in     pay_element_template_util.t_core_objects
2313   ,p_sbf_core_objects  in out nocopy pay_element_template_util.t_core_objects
2314   ) is
2315   l_proc                       varchar2(72) := g_package||'gen_balance_feeds';
2316   l_shadow_object_id           number;
2317   i                            number;
2318   --
2319   l_rowid                      varchar2(128);
2320   l_balance_type_id            number;
2321   l_input_value_id             number;
2322   l_balance_feed_id            number;
2323   l_check_latest_balances      boolean;
2324   --
2325 begin
2326   hr_utility.set_location('Entering:'|| l_proc, 10);
2327   --
2328   i := p_balance_feeds.first;
2329   loop
2330     exit when not p_balance_feeds.exists(i);
2331     --
2332     -- Only generate if a generated core object does not exist.
2333     --
2334     l_shadow_object_id := i;
2335     if not core_object_exists
2336            (p_shadow_object_id => l_shadow_object_id
2337            ,p_object_type      => g_sbf_lookup_type
2338            ,p_core_objects     => p_sbf_core_objects
2339            ) then
2340       hr_utility.set_location(l_proc, 20);
2341       --
2342       -- Set up the in/out parameters.
2343       --
2344       l_balance_feed_id := null;
2345       l_rowid := null;
2346       --
2347       -- Set up the balance_type_id value.
2348       --
2349       if p_balance_feeds(i).balance_name is not null then
2350         --
2351         -- External balance - set up the name.
2352         --
2353         l_balance_type_id :=
2354         get_balance_id
2355         (p_business_group_id => p_business_group_id
2356         ,p_legislation_code  => p_legislation_code
2357         ,p_balance           => p_balance_feeds(i).balance_name
2358         );
2359       else
2360         --
2361         -- Generated balance - get balance_type_id from generated objects table.
2362         --
2363         l_balance_type_id :=
2364         p_sbt_core_objects(p_balance_feeds(i).balance_type_id).core_object_id;
2365       end if;
2366       --
2367       -- Set up the input_value_id value.
2368       --
2369       l_input_value_id :=
2370       p_siv_core_objects(p_balance_feeds(i).input_value_id).core_object_id;
2371       --
2372       -- Insert the balance feed row.
2373       --
2374       hr_utility.set_location(l_proc, 30);
2375 
2376       begin
2377         --
2378         -- Disable (unnecessary) latest balance checking.
2379         --
2380         l_check_latest_balances := hrassact.check_latest_balances;
2381         hrassact.check_latest_balances := false;
2382 
2383         pay_balance_feeds_f_pkg.insert_row
2384         (x_rowid                        => l_rowid
2385         ,x_balance_feed_id              => l_balance_feed_id
2386         ,x_effective_start_date         => p_effective_date
2387         ,x_effective_end_date           => hr_api.g_eot
2388         ,x_business_group_id            => p_business_group_id
2389         ,x_legislation_code             => null
2390         ,x_balance_type_id              => l_balance_type_id
2391         ,x_input_value_id               => l_input_value_id
2392         ,x_scale                        => p_balance_feeds(i).scale
2393         ,x_legislation_subgroup         => null
2394         );
2395 
2396         --
2397         -- Reset latest balance checking status.
2398         --
2399         hrassact.check_latest_balances := l_check_latest_balances;
2400 
2401       exception
2402         when others then
2403           --
2404           -- Reset latest balance checking status.
2405           --
2406           hrassact.check_latest_balances := l_check_latest_balances;
2407           raise;
2408       end;
2409 
2410       --
2411       -- Set up the core object table rows.
2412       --
2413       hr_utility.set_location(l_proc, 40);
2414       update_core_objects
2415       (p_effective_date             => p_effective_date
2416       ,p_template_id                => p_template_id
2417       ,p_core_object_type           => g_sbf_lookup_type
2418       ,p_shadow_object_id           => l_shadow_object_id
2419       ,p_core_object_id             => l_balance_feed_id
2420       ,p_core_objects               => p_sbf_core_objects
2421       );
2422     end if;
2423     --
2424     i := p_balance_feeds.next(i);
2425   end loop;
2426   --
2427   hr_utility.set_location('Leaving:'|| l_proc, 100);
2428 end gen_balance_feeds;
2429 -- ----------------------------------------------------------------------------
2430 -- |---------------------------< gen_formula_rules >--------------------------|
2431 -- ----------------------------------------------------------------------------
2432 procedure gen_formula_rules
2433   (p_effective_date    in     date
2434   ,p_template_id       in     number
2435   ,p_business_group_id in     number
2436   ,p_legislation_code  in     varchar2
2437   ,p_formula_rules     in     pay_element_template_util.t_formula_rules
2438   ,p_sf_core_objects   in     pay_element_template_util.t_core_objects
2439   ,p_set_core_objects  in     pay_element_template_util.t_core_objects
2440   ,p_siv_core_objects  in     pay_element_template_util.t_core_objects
2441   ,p_spr_core_objects  in     pay_element_template_util.t_core_objects
2442   ,p_sfr_core_objects  in out nocopy pay_element_template_util.t_core_objects
2443   ) is
2444   l_proc                      varchar2(72) := g_package||'gen_formula_rules';
2445   l_shadow_object_id          number;
2446   i                           number;
2447   --
2448   l_rowid                     varchar2(128);
2449   l_shadow_element_type_id    number;
2450   l_element_type_id           number;
2451   l_shadow_formula_id         number;
2452   l_shadow_input_value_id     number;
2453   l_input_value_id            number;
2454   l_formula_result_rule_id    number;
2455   l_status_processing_rule_id number;
2456   --
2457 begin
2458   hr_utility.set_location('Entering:'|| l_proc, 10);
2459   --
2460   i := p_formula_rules.first;
2461   loop
2462     exit when not p_formula_rules.exists(i);
2463     --
2464     -- Only generate if a generated core object does not exist.
2465     --
2466     l_shadow_object_id := i;
2467     if not core_object_exists
2468            (p_shadow_object_id => l_shadow_object_id
2469            ,p_object_type      => g_sfr_lookup_type
2470            ,p_core_objects     => p_sfr_core_objects
2471            ) then
2472       hr_utility.set_location(l_proc, 20);
2473       -----------------------------------
2474       -- Set up the in/out parameters. --
2475       -----------------------------------
2476       l_formula_result_rule_id := null;
2477       l_rowid := null;
2478       ----------------------------------------
2479       -- Get the status_processing_rule_id. --
2480       ----------------------------------------
2481       l_shadow_element_type_id := p_formula_rules(i).shadow_element_type_id;
2482       if core_object_exists
2483          (p_shadow_object_id => l_shadow_element_type_id
2484          ,p_object_type      => g_spr_lookup_type
2485          ,p_core_objects     => p_spr_core_objects
2486          ) then
2487        l_status_processing_rule_id :=
2488        p_spr_core_objects(l_shadow_element_type_id).core_object_id;
2489       else
2490         hr_utility.set_message(801, 'PAY_50066_ETM_GEN_NO_FK_ROW');
2491         hr_utility.set_message_token('TABLE', 'PAY_STATUS_PROCESSING_RULES_F');
2492         hr_utility.set_message_token('FK_TABLE', 'PAY_ELEMENT_TYPES_F');
2493         hr_utility.raise_error;
2494       end if;
2495       -------------------------------------------------------------------
2496       -- Set up the element_type_id and input_value_id columns for the --
2497       -- formula rule. Raise an error if they have not been generated. --
2498       -------------------------------------------------------------------
2499       l_input_value_id := null;
2500       l_shadow_input_value_id := p_formula_rules(i).input_value_id;
2501       if l_shadow_input_value_id is not null then
2502         if core_object_exists
2503            (p_shadow_object_id => l_shadow_input_value_id
2504            ,p_object_type      => g_siv_lookup_type
2505            ,p_core_objects     => p_siv_core_objects
2506            ) then
2507           l_input_value_id :=
2508           p_siv_core_objects(l_shadow_input_value_id).core_object_id;
2509         else
2510           hr_utility.set_message(801, 'PAY_50066_ETM_GEN_NO_FK_ROW');
2511           hr_utility.set_message_token('TABLE', 'PAY_STATUS_PROCESSING_RULES_F');
2512           hr_utility.set_message_token('FK_TABLE', 'PAY_INPUT_VALUES_F');
2513           hr_utility.raise_error;
2514         end if;
2515       end if;
2516       --
2517       l_element_type_id := null;
2518       l_shadow_element_type_id := p_formula_rules(i).element_type_id;
2519       if l_shadow_element_type_id is not null then
2520         if core_object_exists
2521            (p_shadow_object_id => l_shadow_element_type_id
2522            ,p_object_type      => g_set_lookup_type
2523            ,p_core_objects     => p_set_core_objects
2524            ) then
2525           l_element_type_id :=
2526           p_set_core_objects(l_shadow_element_type_id).core_object_id;
2527         else
2528           hr_utility.set_message(801, 'PAY_50066_ETM_GEN_NO_FK_ROW');
2529           hr_utility.set_message_token('TABLE', 'PAY_STATUS_PROCESSING_RULES_F');
2530           hr_utility.set_message_token('FK_TABLE', 'PAY_ELEMENT_TYPES_F');
2531           hr_utility.raise_error;
2532         end if;
2533       elsif p_formula_rules(i).element_name is not null then
2534         l_element_type_id :=
2535         get_element_type_id
2536         (p_effective_date    => p_effective_date
2537         ,p_business_group_id => p_business_group_id
2538         ,p_legislation_code  => p_legislation_code
2539         ,p_element_name      => p_formula_rules(i).element_name
2540         );
2541       end if;
2542       -----------------------------------------
2543       -- Insert the formula result rule row. --
2544       -----------------------------------------
2545       pay_formula_result_rules_pkg.insert_row
2546       (p_rowid                        => l_rowid
2547       ,p_formula_result_rule_id       => l_formula_result_rule_id
2548       ,p_effective_start_date         => p_effective_date
2549       ,p_effective_end_date           => hr_api.g_eot
2550       ,p_business_group_id            => p_business_group_id
2551       ,p_legislation_code             => null
2552       ,p_element_type_id              => l_element_type_id
2553       ,p_status_processing_rule_id    => l_status_processing_rule_id
2554       ,p_result_name                  => upper(p_formula_rules(i).result_name)
2555       ,p_result_rule_type             => p_formula_rules(i).result_rule_type
2556       ,p_legislation_subgroup         => null
2557       ,p_severity_level               => p_formula_rules(i).severity_level
2558       ,p_input_value_id               => l_input_value_id
2559       ,p_created_by                   => -1
2560       ,p_session_date                 => hr_api.g_sys
2561       );
2562       --
2563       -- Set up the core object table rows.
2564       --
2565       hr_utility.set_location(l_proc, 30);
2566       update_core_objects
2567       (p_effective_date             => p_effective_date
2568       ,p_template_id                => p_template_id
2569       ,p_core_object_type           => g_sfr_lookup_type
2570       ,p_shadow_object_id           => l_shadow_object_id
2571       ,p_core_object_id             => l_formula_result_rule_id
2572       ,p_core_objects               => p_sfr_core_objects
2573       );
2574     end if;
2575     --
2576     i := p_formula_rules.next(i);
2577   end loop;
2578   --
2579   hr_utility.set_location('Leaving:'|| l_proc, 100);
2580 end gen_formula_rules;
2581 -- ----------------------------------------------------------------------------
2582 -- |--------------------------< gen_iterative_rules >-------------------------|
2583 -- ----------------------------------------------------------------------------
2584 procedure gen_iterative_rules
2585   (p_effective_date    in     date
2586   ,p_template_id       in     number
2587   ,p_business_group_id in     number
2588   ,p_iterative_rules   in     pay_element_template_util.t_iterative_rules
2589   ,p_set_core_objects  in     pay_element_template_util.t_core_objects
2590   ,p_siv_core_objects  in     pay_element_template_util.t_core_objects
2591   ,p_sir_core_objects  in out nocopy pay_element_template_util.t_core_objects
2592   ) is
2593   l_proc                      varchar2(72) := g_package||'gen_iterative_rules';
2594   l_shadow_object_id          number;
2595   i                           number;
2596   --
2597   l_shadow_element_type_id    number;
2598   l_element_type_id           number;
2599   l_shadow_input_value_id     number;
2600   l_input_value_id            number;
2601   l_iterative_rule_id         number;
2602   l_object_version_number     number;
2603   l_effective_start_date      date;
2604   l_effective_end_date        date;
2605   --
2606 begin
2607   hr_utility.set_location('Entering:'|| l_proc, 10);
2608   --
2609   i := p_iterative_rules.first;
2610   loop
2611     exit when not p_iterative_rules.exists(i);
2612     --
2613     -- Only generate if a generated core object does not exist.
2614     --
2615     l_shadow_object_id := i;
2616     if not core_object_exists
2617            (p_shadow_object_id => l_shadow_object_id
2618            ,p_object_type      => g_sir_lookup_type
2619            ,p_core_objects     => p_sir_core_objects
2620            ) then
2621       hr_utility.set_location(l_proc, 20);
2622       -----------------------------------
2623       -- Set up the in/out parameters. --
2624       -----------------------------------
2625       l_iterative_rule_id     := null;
2626       l_object_version_number := null;
2627       l_effective_start_date  := null;
2628       l_effective_end_date    := null;
2629       -------------------------------------------------------------------
2630       -- Set up the element_type_id and input_value_id columns for the --
2631       -- iterative rule. Raise an error if they have not been generated. --
2632       -------------------------------------------------------------------
2633       l_input_value_id := null;
2634       l_shadow_input_value_id := p_iterative_rules(i).input_value_id;
2635       if l_shadow_input_value_id is not null then
2636         if core_object_exists
2637            (p_shadow_object_id => l_shadow_input_value_id
2638            ,p_object_type      => g_siv_lookup_type
2639            ,p_core_objects     => p_siv_core_objects
2640            ) then
2641           l_input_value_id :=
2642           p_siv_core_objects(l_shadow_input_value_id).core_object_id;
2643         else
2644           hr_utility.set_message(801, 'PAY_50066_ETM_GEN_NO_FK_ROW');
2645           hr_utility.set_message_token('TABLE', 'PAY_ITERATIVE_RULES_F');
2646           hr_utility.set_message_token('FK_TABLE', 'PAY_INPUT_VALUES_F');
2647           hr_utility.raise_error;
2648         end if;
2649       end if;
2650       --
2651       l_element_type_id := null;
2652       l_shadow_element_type_id := p_iterative_rules(i).element_type_id;
2653       if l_shadow_element_type_id is not null then
2654         if core_object_exists
2655            (p_shadow_object_id => l_shadow_element_type_id
2656            ,p_object_type      => g_set_lookup_type
2657            ,p_core_objects     => p_set_core_objects
2658            ) then
2659           l_element_type_id :=
2660           p_set_core_objects(l_shadow_element_type_id).core_object_id;
2661         else
2662           hr_utility.set_message(801, 'PAY_50066_ETM_GEN_NO_FK_ROW');
2663           hr_utility.set_message_token('TABLE', 'PAY_ITERATIVE_RULES_F');
2664           hr_utility.set_message_token('FK_TABLE', 'PAY_ELEMENT_TYPES_F');
2665           hr_utility.raise_error;
2666         end if;
2667       end if;
2668       ------------------------------------
2669       -- Insert the iterative rule row. --
2670       ------------------------------------
2671       pay_itr_ins.ins
2672       (p_effective_date               => p_effective_date
2673       ,p_element_type_id              => l_element_type_id
2674       ,p_result_name                  => p_iterative_rules(i).result_name
2675       ,p_iterative_rule_type          => p_iterative_rules(i).iterative_rule_type
2676       ,p_input_value_id               => l_input_value_id
2677       ,p_severity_level               => p_iterative_rules(i).severity_level
2678       ,p_business_group_id            => p_business_group_id
2679       ,p_legislation_code             => null
2680       ,p_iterative_rule_id            => l_iterative_rule_id
2681       ,p_object_version_number        => l_object_version_number
2682       ,p_effective_start_date         => l_effective_start_date
2683       ,p_effective_end_date           => l_effective_end_date
2684       );
2685       --
2686       -- Set up the core object table rows.
2687       --
2688       hr_utility.set_location(l_proc, 30);
2689       update_core_objects
2690       (p_effective_date             => p_effective_date
2691       ,p_template_id                => p_template_id
2692       ,p_core_object_type           => g_sir_lookup_type
2693       ,p_shadow_object_id           => l_shadow_object_id
2694       ,p_core_object_id             => l_iterative_rule_id
2695       ,p_core_objects               => p_sir_core_objects
2696       );
2697     end if;
2698     --
2699     i := p_iterative_rules.next(i);
2700   end loop;
2701   --
2702   hr_utility.set_location('Leaving:'|| l_proc, 100);
2703 end gen_iterative_rules;
2704 -- ----------------------------------------------------------------------------
2705 -- |--------------------------< gen_ele_type_usages >--------------------------|
2706 -- ----------------------------------------------------------------------------
2707 procedure gen_ele_type_usages
2708   (p_effective_date    in     date
2709   ,p_template_id       in     number
2710   ,p_business_group_id in     number
2711   ,p_legislation_code  in     varchar2
2712   ,p_ele_type_usages   in     pay_element_template_util.t_ele_type_usages
2713   ,p_set_core_objects  in     pay_element_template_util.t_core_objects
2714   ,p_seu_core_objects  in out nocopy pay_element_template_util.t_core_objects
2715   ) is
2716   l_proc                       varchar2(72) := g_package||'gen_ele_type_usages';
2717   l_shadow_object_id           number;
2718   i                            number;
2719   --
2720   l_run_type_id             number;
2721   l_element_type_id         number;
2722   l_element_type_usage_id   number;
2723   l_effective_start_date    date;
2724   l_effective_end_date      date;
2725   l_object_version_number   number;
2726   --
2727 begin
2728   hr_utility.set_location('Entering:'|| l_proc, 10);
2729   --
2730   i := p_ele_type_usages.first;
2731   loop
2732     exit when not p_ele_type_usages.exists(i);
2733     --
2734     -- Only generate if a generated core object does not exist.
2735     --
2736     l_shadow_object_id := i;
2737     if not core_object_exists
2738            (p_shadow_object_id => l_shadow_object_id
2739            ,p_object_type      => g_seu_lookup_type
2740            ,p_core_objects     => p_seu_core_objects
2741            ) then
2742       hr_utility.set_location(l_proc, 20);
2743       -----------------------------------
2744       -- Set up the in/out parameters. --
2745       -----------------------------------
2746       l_element_type_usage_id := null;
2747       l_effective_start_date  := null;
2748       l_effective_end_date    := null;
2749       l_object_version_number := null;
2750       --
2751       -- Set up the run_type_id value.
2752       --
2753       l_run_type_id :=
2754       get_run_type_id
2755       (p_effective_date    => p_effective_date
2756       ,p_business_group_id => p_business_group_id
2757       ,p_legislation_code  => p_legislation_code
2758       ,p_run_type          => p_ele_type_usages(i).run_type_name
2759       );
2760       --
2761       -- Set up the element_type_id value.
2762       --
2763       l_element_type_id :=
2764       p_set_core_objects(p_ele_type_usages(i).element_type_id).core_object_id;
2765       --
2766       -- Insert the element type usage row.
2767       --
2768       hr_utility.set_location(l_proc, 30);
2769       pay_etu_ins.ins
2770       (p_effective_date               => p_effective_date
2771       ,p_run_type_id                  => l_run_type_id
2772       ,p_element_type_id              => l_element_type_id
2773       ,p_inclusion_flag               => p_ele_type_usages(i).inclusion_flag
2774       ,p_business_group_id            => p_business_group_id
2775       ,p_legislation_code             => null
2776       ,p_element_type_usage_id        => l_element_type_usage_id
2777       ,p_object_version_number        => l_object_version_number
2778       ,p_effective_start_date         => l_effective_start_date
2779       ,p_effective_end_date           => l_effective_end_date
2780       );
2781       --
2782       -- Set up the core object table rows.
2783       --
2784       hr_utility.set_location(l_proc, 40);
2785       update_core_objects
2786       (p_effective_date             => p_effective_date
2787       ,p_template_id                => p_template_id
2788       ,p_core_object_type           => g_seu_lookup_type
2789       ,p_shadow_object_id           => l_shadow_object_id
2790       ,p_core_object_id             => l_element_type_usage_id
2791       ,p_core_objects               => p_seu_core_objects
2792       );
2793     end if;
2794     --
2795     i := p_ele_type_usages.next(i);
2796   end loop;
2797   --
2798   hr_utility.set_location('Leaving:'|| l_proc, 100);
2799 end gen_ele_type_usages;
2800 -- ----------------------------------------------------------------------------
2801 -- |-------------------------< gen_gu_bal_exclusions >------------------------|
2802 -- ----------------------------------------------------------------------------
2803 procedure gen_gu_bal_exclusions
2804   (p_effective_date    in     date
2805   ,p_template_id       in     number
2806   ,p_business_group_id in     number
2807   ,p_legislation_code  in     varchar2
2808   ,p_gu_bal_exclusions in     pay_element_template_util.t_gu_bal_exclusions
2809   ,p_sbt_core_objects  in     pay_element_template_util.t_core_objects
2810   ,p_set_core_objects  in     pay_element_template_util.t_core_objects
2811   ,p_sgb_core_objects  in out nocopy pay_element_template_util.t_core_objects
2812   ) is
2813   l_proc                       varchar2(72) := g_package||'gen_gu_bal_exclusions';
2814   l_shadow_object_id           number;
2815   i                            number;
2816   --
2817   l_balance_type_id            number;
2818   l_source_id                  number;
2819   l_grossup_balances_id        number;
2820   l_object_version_number      number;
2821   --
2822 begin
2823   hr_utility.set_location('Entering:'|| l_proc, 10);
2824   --
2825   i := p_gu_bal_exclusions.first;
2826   loop
2827     exit when not p_gu_bal_exclusions.exists(i);
2828     --
2829     -- Only generate if a generated core object does not exist.
2830     --
2831     l_shadow_object_id := i;
2832     if not core_object_exists
2833            (p_shadow_object_id => l_shadow_object_id
2834            ,p_object_type      => g_sgb_lookup_type
2835            ,p_core_objects     => p_sgb_core_objects
2836            ) then
2837       hr_utility.set_location(l_proc, 20);
2838       --
2839       -- Set up the in/out parameters.
2840       --
2841       l_grossup_balances_id   := null;
2842       l_object_version_number := null;
2843       --
2844       -- Set up the balance_type_id value.
2845       --
2846       if p_gu_bal_exclusions(i).balance_type_name is not null then
2847         --
2848         -- External balance - set up the balance_type_id using the name mapping
2849         -- table.
2850         --
2851         l_balance_type_id :=
2852         get_balance_id
2853         (p_business_group_id => p_business_group_id
2854         ,p_legislation_code  => p_legislation_code
2855         ,p_balance           => p_gu_bal_exclusions(i).balance_type_name
2856         );
2857       else
2858         --
2859         -- Generated balance - get balance_type_id from generated objects table.
2860         --
2861         l_balance_type_id := p_gu_bal_exclusions(i).balance_type_id;
2862         if core_object_exists
2863            (p_shadow_object_id => l_balance_type_id
2864            ,p_object_type      => g_sbt_lookup_type
2865            ,p_core_objects     => p_sbt_core_objects
2866            ) then
2867            l_balance_type_id :=
2868            p_sbt_core_objects(l_balance_type_id).core_object_id;
2869         else
2870           hr_utility.set_message(801, 'PAY_50066_ETM_GEN_NO_FK_ROW');
2871           hr_utility.set_message_token('TABLE', 'PAY_GROSSUP_BAL_EXCLUSIONS');
2872           hr_utility.set_message_token('FK_TABLE', 'PAY_BALANCE_TYPES');
2873           hr_utility.raise_error;
2874         end if;
2875       end if;
2876       --
2877       -- Set up the source_id value.
2878       --
2879       if p_gu_bal_exclusions(i).source_id is not null then
2880         if core_object_exists
2881            (p_shadow_object_id => p_gu_bal_exclusions(i).source_id
2882            ,p_object_type      => g_set_lookup_type
2883            ,p_core_objects     => p_set_core_objects
2884            ) then
2885           l_source_id :=
2886           p_set_core_objects(p_gu_bal_exclusions(i).source_id).core_object_id;
2887         else
2888           hr_utility.set_message(801, 'PAY_50066_ETM_GEN_NO_FK_ROW');
2889           hr_utility.set_message_token('TABLE', 'PAY_GROSSUP_BAL_EXCLUSIONS');
2890           hr_utility.set_message_token('FK_TABLE', 'PAY_ELEMENT_TYPES_F');
2891           hr_utility.raise_error;
2892         end if;
2893       end if;
2894       --
2895       -- Insert the grossup balance exclusion row.
2896       --
2897       hr_utility.set_location(l_proc, 30);
2898       pay_gbe_ins.ins
2899       (p_start_date                   => p_effective_date
2900       ,p_end_date                     => hr_api.g_eot
2901       ,p_source_id                    => l_source_id
2902       ,p_source_type                  => p_gu_bal_exclusions(i).source_type
2903       ,p_balance_type_id              => l_balance_type_id
2904       ,p_grossup_balances_id          => l_grossup_balances_id
2905       ,p_object_version_number        => l_object_version_number
2906       );
2907       --
2908       -- Set up the core object table rows.
2909       --
2910       hr_utility.set_location(l_proc, 40);
2911       update_core_objects
2912       (p_effective_date             => p_effective_date
2913       ,p_template_id                => p_template_id
2914       ,p_core_object_type           => g_sgb_lookup_type
2915       ,p_shadow_object_id           => l_shadow_object_id
2916       ,p_core_object_id             => l_grossup_balances_id
2917       ,p_core_objects               => p_sgb_core_objects
2918       );
2919     end if;
2920     --
2921     i := p_gu_bal_exclusions.next(i);
2922   end loop;
2923   --
2924   hr_utility.set_location('Leaving:'|| l_proc, 100);
2925 end gen_gu_bal_exclusions;
2926 -- ----------------------------------------------------------------------------
2927 -- |-----------------------< gen_balance_attributes >-------------------------|
2928 -- ----------------------------------------------------------------------------
2929 procedure gen_bal_attributes
2930   (p_effective_date     in     date
2931   ,p_template_id        in     number
2932   ,p_business_group_id  in     number
2933   ,p_legislation_code   in     varchar2
2934   ,p_bal_attributes     in     pay_element_template_util.t_bal_attributes
2935   ,p_sdb_core_objects   in     pay_element_template_util.t_core_objects
2936   ,p_sba_core_objects   in out nocopy pay_element_template_util.t_core_objects
2937   ) is
2938   l_proc                 varchar2(72) := g_package||'gen_balance_attributes';
2939   l_shadow_object_id     number;
2940   i                      number;
2941   --
2942   l_balance_attribute_id number;
2943   l_defined_balance_id   number;
2944   l_bal_attribute_def_id number;
2945   --
2946 begin
2947   hr_utility.set_location('Entering:'|| l_proc, 10);
2948   --
2949   i := p_bal_attributes.first;
2950   loop
2951     exit when not p_bal_attributes.exists(i);
2952     --
2953     -- Only generate if a generated core object does not exist.
2954     --
2955     l_shadow_object_id := i;
2956     if not core_object_exists
2957            (p_shadow_object_id => l_shadow_object_id
2958            ,p_object_type      => g_sba_lookup_type
2959            ,p_core_objects     => p_sba_core_objects
2960            ) then
2961       hr_utility.set_location(l_proc, 20);
2962       --
2963       -- Set up the attribute_id for the external balance attribute definition.
2964       --
2965       l_bal_attribute_def_id :=
2966       get_bal_attribute_def_id
2967       (p_business_group_id => p_business_group_id
2968       ,p_legislation_code  => p_legislation_code
2969       ,p_attribute         => p_bal_attributes(i).attribute_name
2970       );
2971       --
2972       -- Set up the defined_balance_id value.
2973       --
2974       l_defined_balance_id :=
2975       p_sdb_core_objects(p_bal_attributes(i).defined_balance_id).core_object_id;
2976       --
2977       -- Insert the balance attribute row.
2978       --
2979       pay_balance_attribute_api.create_balance_attribute
2980       (p_validate             => false
2981       ,p_attribute_id         => l_bal_attribute_def_id
2982       ,p_business_group_id    => p_business_group_id
2983       ,p_legislation_code     => null
2984       ,p_defined_balance_id   => l_defined_balance_id
2985       ,p_balance_attribute_id => l_balance_attribute_id
2986       );
2987       --
2988       -- Set up the core object table rows.
2989       --
2990       hr_utility.set_location(l_proc, 30);
2991       update_core_objects
2992       (p_effective_date   => p_effective_date
2993       ,p_template_id      => p_template_id
2994       ,p_core_object_type => g_sba_lookup_type
2995       ,p_shadow_object_id => l_shadow_object_id
2996       ,p_core_object_id   => l_balance_attribute_id
2997       ,p_core_objects     => p_sba_core_objects
2998       );
2999     end if;
3000     --
3001     i := p_bal_attributes.next(i);
3002   end loop;
3003   --
3004   hr_utility.set_location('Leaving:'|| l_proc, 100);
3005 end gen_bal_attributes;
3006 -- ----------------------------------------------------------------------------
3007 -- |---------------------------< generate_part1 >-----------------------------|
3008 -- ----------------------------------------------------------------------------
3009 procedure generate_part1
3010   (p_effective_date                in     date
3011   ,p_hr_only                       in     boolean
3012   ,p_hr_to_payroll                 in     boolean default false
3013   ,p_template_id                   in     number
3014   ) is
3015   l_proc                     varchar2(72) := g_package||'generate_part1';
3016   l_business_group_id        number;
3017   l_legislation_code         varchar2(30);
3018   l_base_processing_priority number;
3019   l_template_type            varchar2(2000);
3020   -----------------------------
3021   -- PL/SQL template tables. --
3022   -----------------------------
3023   l_element_template        pay_etm_shd.g_rec_type;
3024   l_exclusion_rules         pay_element_template_util.t_exclusion_rules;
3025   l_formulas                pay_element_template_util.t_formulas;
3026   l_balance_types           pay_element_template_util.t_balance_types;
3027   l_defined_balances        pay_element_template_util.t_defined_balances;
3028   l_element_types           pay_element_template_util.t_element_types;
3029   l_sub_classi_rules        pay_element_template_util.t_sub_classi_rules;
3030   l_balance_classis         pay_element_template_util.t_balance_classis;
3031   l_input_values            pay_element_template_util.t_input_values;
3032   l_balance_feeds           pay_element_template_util.t_balance_feeds;
3033   l_formula_rules           pay_element_template_util.t_formula_rules;
3034   l_iterative_rules         pay_element_template_util.t_iterative_rules;
3035   l_ele_type_usages         pay_element_template_util.t_ele_type_usages;
3036   l_gu_bal_exclusions       pay_element_template_util.t_gu_bal_exclusions;
3037   l_bal_attributes          pay_element_template_util.t_bal_attributes;
3038   l_template_ff_usages      pay_element_template_util.t_template_ff_usages;
3039   ------------------------
3040   -- Generation tables. --
3041   ------------------------
3042   l_all_core_objects        pay_element_template_util.t_core_objects;
3043   l_sf_core_objects         pay_element_template_util.t_core_objects;
3044   l_sbt_core_objects        pay_element_template_util.t_core_objects;
3045   l_sdb_core_objects        pay_element_template_util.t_core_objects;
3046   l_set_core_objects        pay_element_template_util.t_core_objects;
3047   l_ssr_core_objects        pay_element_template_util.t_core_objects;
3048   l_sbc_core_objects        pay_element_template_util.t_core_objects;
3049   l_siv_core_objects        pay_element_template_util.t_core_objects;
3050   l_sbf_core_objects        pay_element_template_util.t_core_objects;
3051   l_spr_core_objects        pay_element_template_util.t_core_objects;
3052   l_sfr_core_objects        pay_element_template_util.t_core_objects;
3053   l_sir_core_objects        pay_element_template_util.t_core_objects;
3054   l_seu_core_objects        pay_element_template_util.t_core_objects;
3055   l_sgb_core_objects        pay_element_template_util.t_core_objects;
3056   l_sba_core_objects        pay_element_template_util.t_core_objects;
3057 begin
3058   hr_utility.set_location('Entering:'|| l_proc, 10);
3059   ----------------------------------------------
3060   -- Check that the template type is correct. --
3061   ----------------------------------------------
3062   l_template_type :=
3063   pay_element_template_util.get_template_type(p_template_id);
3064   if l_template_type is null or l_template_type <> 'U' then
3065     hr_utility.set_message(801, 'PAY_50065_BAD_USER_TEMPLATE');
3066     hr_utility.raise_error;
3067   end if;
3068   ---------------------------------------
3069   -- Read in the template information. --
3070   ---------------------------------------
3071   pay_element_template_util.create_plsql_template
3072   (p_template_id                  => p_template_id
3073   ,p_generate_part1               => true
3074   ,p_core_objects                 => l_all_core_objects
3075   ,p_element_template             => l_element_template
3076   ,p_exclusion_rules              => l_exclusion_rules
3077   ,p_formulas                     => l_formulas
3078   ,p_balance_types                => l_balance_types
3079   ,p_defined_balances             => l_defined_balances
3080   ,p_element_types                => l_element_types
3081   ,p_sub_classi_rules             => l_sub_classi_rules
3082   ,p_balance_classis              => l_balance_classis
3083   ,p_input_values                 => l_input_values
3084   ,p_balance_feeds                => l_balance_feeds
3085   ,p_formula_rules                => l_formula_rules
3086   ,p_iterative_rules              => l_iterative_rules
3087   ,p_ele_type_usages              => l_ele_type_usages
3088   ,p_gu_bal_exclusions            => l_gu_bal_exclusions
3089   ,p_bal_attributes               => l_bal_attributes
3090   ,p_template_ff_usages           => l_template_ff_usages
3091   );
3092   -----------------------------------------------
3093   -- Read in information on generated objects. --
3094   -----------------------------------------------
3095   hr_utility.set_location(l_proc, 20);
3096   create_generation_tables
3097   (p_all_core_objects             => l_all_core_objects
3098   ,p_index_by_core_object_id      => false
3099   ,p_sf_core_objects              => l_sf_core_objects
3100   ,p_sbt_core_objects             => l_sbt_core_objects
3101   ,p_sdb_core_objects             => l_sdb_core_objects
3102   ,p_set_core_objects             => l_set_core_objects
3103   ,p_ssr_core_objects             => l_ssr_core_objects
3104   ,p_sbc_core_objects             => l_sbc_core_objects
3105   ,p_siv_core_objects             => l_siv_core_objects
3106   ,p_sbf_core_objects             => l_sbf_core_objects
3107   ,p_spr_core_objects             => l_spr_core_objects
3108   ,p_sfr_core_objects             => l_sfr_core_objects
3109   ,p_sir_core_objects             => l_sir_core_objects
3110   ,p_seu_core_objects             => l_seu_core_objects
3111   ,p_sgb_core_objects             => l_sgb_core_objects
3112   ,p_sba_core_objects             => l_sba_core_objects
3113   );
3114   --
3115   hr_utility.set_location(l_proc, 30);
3116   get_formula_types;
3117   l_business_group_id := l_element_template.business_group_id;
3118   l_legislation_code :=
3119   hr_api.return_legislation_code(l_business_group_id);
3120   hr_general.assert_condition(l_legislation_code is not null);
3121   ---------------------------------
3122   -- At last, do the generation! --
3123   ---------------------------------
3124   -----------------
3125   -- 1. Formulas --
3126   -----------------
3127   gen_formulas
3128   (p_effective_date                 => p_effective_date
3129   ,p_template_id                    => p_template_id
3130   ,p_hr_only                        => p_hr_only
3131   ,p_formulas                       => l_formulas
3132   ,p_sf_core_objects                => l_sf_core_objects
3133   );
3134   -----------------------
3135   -- 2. Element Types. --
3136   -----------------------
3137   hr_utility.set_location(l_proc, 70);
3138   l_base_processing_priority := l_element_template.base_processing_priority;
3139   gen_element_types
3140   (p_effective_date                 => p_effective_date
3141   ,p_template_id                    => p_template_id
3142   ,p_business_group_id              => l_business_group_id
3143   ,p_hr_only                        => p_hr_only
3144   ,p_legislation_code               => l_legislation_code
3145   ,p_base_processing_priority       => l_base_processing_priority
3146   ,p_element_types                  => l_element_types
3147   ,p_sf_core_objects                => l_sf_core_objects
3148   ,p_set_core_objects               => l_set_core_objects
3149   ,p_spr_core_objects               => l_spr_core_objects
3150   );
3151   ------------------------------
3152   -- 3. Element Input Values. --
3153   ------------------------------
3154   hr_utility.set_location(l_proc, 100);
3155   implicit_input_values
3156   (p_effective_date                 => p_effective_date
3157   ,p_template_id                    => p_template_id
3158   ,p_all_core_objects               => l_all_core_objects
3159   ,p_input_values                   => l_input_values
3160   ,p_set_core_objects               => l_set_core_objects
3161   ,p_siv_core_objects               => l_siv_core_objects
3162   );
3163   gen_input_values
3164   (p_effective_date                 => p_effective_date
3165   ,p_template_id                    => p_template_id
3166   ,p_business_group_id              => l_business_group_id
3167   ,p_legislation_code		    => l_legislation_code
3168   ,p_input_values                   => l_input_values
3169   ,p_set_core_objects               => l_set_core_objects
3170   ,p_sf_core_objects                => l_sf_core_objects
3171   ,p_siv_core_objects               => l_siv_core_objects
3172   );
3173   ----------------------
3174   -- 4. Balance Types --
3175   ----------------------
3176   hr_utility.set_location(l_proc, 50);
3177   gen_balance_types
3178   (p_effective_date                 => p_effective_date
3179   ,p_template_id                    => p_template_id
3180   ,p_business_group_id              => l_business_group_id
3181   ,p_legislation_code               => l_legislation_code
3182   ,p_balance_types                  => l_balance_types
3183   ,p_sbt_core_objects               => l_sbt_core_objects
3184   ,p_siv_core_objects               => l_siv_core_objects
3185   );
3186   --------------------------
3187   -- 5. Defined Balances. --
3188   --------------------------
3189   hr_utility.set_location(l_proc, 60);
3190   if not p_hr_only then
3191     gen_defined_balances
3192     (p_effective_date                 => p_effective_date
3193     ,p_template_id                    => p_template_id
3194     ,p_business_group_id              => l_business_group_id
3195     ,p_legislation_code               => l_legislation_code
3196     ,p_defined_balances               => l_defined_balances
3197     ,p_sbt_core_objects               => l_sbt_core_objects
3198     ,p_sdb_core_objects               => l_sdb_core_objects
3199     );
3200   end if;
3201   ----------------------------------
3202   -- 6. Sub-Classification Rules. --
3203   ----------------------------------
3204   --
3205   -- Have to allow for the implicitly created sub-classification
3206   -- rules whatever options the user chooses, otherwise the code
3207   -- may error when it's time to generate everything.
3208   --
3209   hr_utility.set_location(l_proc, 80);
3210   implicit_sub_classi_rules
3211   (p_effective_date                 => p_effective_date
3212   ,p_template_id                    => p_template_id
3213   ,p_business_group_id              => l_business_group_id
3214   ,p_legislation_code               => l_legislation_code
3215   ,p_all_core_objects               => l_all_core_objects
3216   ,p_sub_classi_rules               => l_sub_classi_rules
3217   ,p_set_core_objects               => l_set_core_objects
3218   ,p_ssr_core_objects               => l_ssr_core_objects
3219   );
3220   if not p_hr_only then
3221     gen_sub_classi_rules
3222     (p_effective_date                 => p_effective_date
3223     ,p_template_id                    => p_template_id
3224     ,p_business_group_id              => l_business_group_id
3225     ,p_legislation_code               => l_legislation_code
3226     ,p_sub_classi_rules               => l_sub_classi_rules
3227     ,p_set_core_objects               => l_set_core_objects
3228     ,p_ssr_core_objects               => l_ssr_core_objects
3229     );
3230     ---------------------------------
3231     -- 7. Balance Classifications. --
3232     ---------------------------------
3233     hr_utility.set_location(l_proc, 90);
3234     gen_balance_classis
3235     (p_effective_date                 => p_effective_date
3236     ,p_template_id                    => p_template_id
3237     ,p_business_group_id              => l_business_group_id
3238     ,p_legislation_code               => l_legislation_code
3239     ,p_balance_classis                => l_balance_classis
3240     ,p_sbt_core_objects               => l_sbt_core_objects
3241     ,p_sbc_core_objects               => l_sbc_core_objects
3242     );
3243     -----------------------
3244     -- 8. Balance Feeds. --
3245     -----------------------
3246     hr_utility.set_location(l_proc, 110);
3247     gen_balance_feeds
3248     (p_effective_date                 => p_effective_date
3249     ,p_template_id                    => p_template_id
3250     ,p_business_group_id              => l_business_group_id
3251     ,p_legislation_code               => l_legislation_code
3252     ,p_balance_feeds                  => l_balance_feeds
3253     ,p_sbt_core_objects               => l_sbt_core_objects
3254     ,p_siv_core_objects               => l_siv_core_objects
3255     ,p_sbf_core_objects               => l_sbf_core_objects
3256     );
3257     -----------------------------
3258     -- 9. Element type usages. --
3259     -----------------------------
3260     hr_utility.set_location(l_proc, 120);
3261     gen_ele_type_usages
3262     (p_effective_date               => p_effective_date
3263     ,p_template_id                  => p_template_id
3264     ,p_business_group_id            => l_business_group_id
3265     ,p_legislation_code               => l_legislation_code
3266     ,p_ele_type_usages              => l_ele_type_usages
3267     ,p_set_core_objects             => l_set_core_objects
3268     ,p_seu_core_objects             => l_seu_core_objects
3269     );
3270     -------------------------------------
3271     -- 10. Grossup balance exclusions. --
3272     -------------------------------------
3273     hr_utility.set_location(l_proc, 130);
3274     gen_gu_bal_exclusions
3275     (p_effective_date               => p_effective_date
3276     ,p_template_id                  => p_template_id
3277     ,p_business_group_id            => l_business_group_id
3278     ,p_legislation_code             => l_legislation_code
3279     ,p_gu_bal_exclusions            => l_gu_bal_exclusions
3280     ,p_sbt_core_objects             => l_sbt_core_objects
3281     ,p_set_core_objects             => l_set_core_objects
3282     ,p_sgb_core_objects             => l_sgb_core_objects
3283     );
3284     -----------------------------
3285     -- 11. Balance attributes. --
3286     -----------------------------
3287     hr_utility.set_location(l_proc, 140);
3288     gen_bal_attributes
3289     (p_effective_date                 => p_effective_date
3290     ,p_template_id                    => p_template_id
3291     ,p_business_group_id              => l_business_group_id
3292     ,p_legislation_code               => l_legislation_code
3293     ,p_bal_attributes                 => l_bal_attributes
3294     ,p_sdb_core_objects               => l_sdb_core_objects
3295     ,p_sba_core_objects               => l_sba_core_objects
3296     );
3297   end if;
3298   hr_utility.set_location('Leaving:'|| l_proc, 200);
3299 exception
3300   when others then
3301     hr_utility.set_location('Leaving:'|| l_proc, 210);
3302     raise;
3303 end generate_part1;
3304 -- ----------------------------------------------------------------------------
3305 -- |---------------------------< generate_part2 >-----------------------------|
3306 -- ----------------------------------------------------------------------------
3307 procedure generate_part2
3308   (p_effective_date                in     date
3309   ,p_template_id                   in     number
3310   ) is
3311   l_proc                     varchar2(72) := g_package||'generate_part2';
3312   l_business_group_id        number;
3313   l_legislation_code         varchar2(30);
3314   l_template_type            varchar2(2000);
3315   -----------------------------
3316   -- PL/SQL template tables. --
3317   -----------------------------
3318   l_element_template        pay_etm_shd.g_rec_type;
3319   l_exclusion_rules         pay_element_template_util.t_exclusion_rules;
3320   l_formulas                pay_element_template_util.t_formulas;
3321   l_balance_types           pay_element_template_util.t_balance_types;
3322   l_defined_balances        pay_element_template_util.t_defined_balances;
3323   l_element_types           pay_element_template_util.t_element_types;
3324   l_sub_classi_rules        pay_element_template_util.t_sub_classi_rules;
3325   l_balance_classis         pay_element_template_util.t_balance_classis;
3326   l_input_values            pay_element_template_util.t_input_values;
3327   l_balance_feeds           pay_element_template_util.t_balance_feeds;
3328   l_formula_rules           pay_element_template_util.t_formula_rules;
3329   l_iterative_rules         pay_element_template_util.t_iterative_rules;
3330   l_ele_type_usages         pay_element_template_util.t_ele_type_usages;
3331   l_gu_bal_exclusions       pay_element_template_util.t_gu_bal_exclusions;
3332   l_template_ff_usages      pay_element_template_util.t_template_ff_usages;
3333   l_bal_attributes          pay_element_template_util.t_bal_attributes;
3334   ------------------------
3335   -- Generation tables. --
3336   ------------------------
3337   l_all_core_objects        pay_element_template_util.t_core_objects;
3338   l_sf_core_objects         pay_element_template_util.t_core_objects;
3339   l_sbt_core_objects        pay_element_template_util.t_core_objects;
3340   l_sdb_core_objects        pay_element_template_util.t_core_objects;
3341   l_set_core_objects        pay_element_template_util.t_core_objects;
3342   l_ssr_core_objects        pay_element_template_util.t_core_objects;
3343   l_sbc_core_objects        pay_element_template_util.t_core_objects;
3344   l_siv_core_objects        pay_element_template_util.t_core_objects;
3345   l_sbf_core_objects        pay_element_template_util.t_core_objects;
3346   l_spr_core_objects        pay_element_template_util.t_core_objects;
3347   l_sfr_core_objects        pay_element_template_util.t_core_objects;
3348   l_sir_core_objects        pay_element_template_util.t_core_objects;
3349   l_seu_core_objects        pay_element_template_util.t_core_objects;
3350   l_sgb_core_objects        pay_element_template_util.t_core_objects;
3351   l_sba_core_objects        pay_element_template_util.t_core_objects;
3352   --
3353 begin
3354   hr_utility.set_location('Entering:'|| l_proc, 10);
3355   ----------------------------------------------
3356   -- Check that the template type is correct. --
3357   ----------------------------------------------
3358   l_template_type :=
3359   pay_element_template_util.get_template_type(p_template_id);
3360   if l_template_type is null or l_template_type <> 'U' then
3361     hr_utility.set_message(801, 'PAY_50065_BAD_USER_TEMPLATE');
3362     hr_utility.raise_error;
3363   end if;
3364   ---------------------------------------
3365   -- Read in the template information. --
3366   ---------------------------------------
3367   pay_element_template_util.create_plsql_template
3368   (p_template_id                  => p_template_id
3369   ,p_generate_part2               => true
3370   ,p_element_template             => l_element_template
3371   ,p_core_objects                 => l_all_core_objects
3372   ,p_exclusion_rules              => l_exclusion_rules
3373   ,p_formulas                     => l_formulas
3374   ,p_balance_types                => l_balance_types
3375   ,p_defined_balances             => l_defined_balances
3376   ,p_element_types                => l_element_types
3377   ,p_sub_classi_rules             => l_sub_classi_rules
3378   ,p_balance_classis              => l_balance_classis
3379   ,p_input_values                 => l_input_values
3380   ,p_balance_feeds                => l_balance_feeds
3381   ,p_formula_rules                => l_formula_rules
3382   ,p_iterative_rules              => l_iterative_rules
3383   ,p_ele_type_usages              => l_ele_type_usages
3384   ,p_gu_bal_exclusions            => l_gu_bal_exclusions
3385   ,p_template_ff_usages           => l_template_ff_usages
3386   ,p_bal_attributes               => l_bal_attributes
3387   );
3388   --
3389   l_business_group_id := l_element_template.business_group_id;
3390   l_legislation_code :=
3391   hr_api.return_legislation_code(l_business_group_id);
3392   --
3393   get_formula_types;
3394   -----------------------------------------------
3395   -- Read in information on generated objects. --
3396   -----------------------------------------------
3397   hr_utility.set_location(l_proc, 20);
3398   create_generation_tables
3399   (p_all_core_objects             => l_all_core_objects
3400   ,p_index_by_core_object_id      => false
3401   ,p_sf_core_objects              => l_sf_core_objects
3402   ,p_sbt_core_objects             => l_sbt_core_objects
3403   ,p_sdb_core_objects             => l_sdb_core_objects
3404   ,p_set_core_objects             => l_set_core_objects
3405   ,p_ssr_core_objects             => l_ssr_core_objects
3406   ,p_sbc_core_objects             => l_sbc_core_objects
3407   ,p_siv_core_objects             => l_siv_core_objects
3408   ,p_sbf_core_objects             => l_sbf_core_objects
3409   ,p_spr_core_objects             => l_spr_core_objects
3410   ,p_sfr_core_objects             => l_sfr_core_objects
3411   ,p_sir_core_objects             => l_sir_core_objects
3412   ,p_seu_core_objects             => l_seu_core_objects
3413   ,p_sgb_core_objects             => l_sgb_core_objects
3414   ,p_sba_core_objects             => l_sba_core_objects
3415   );
3416 
3417   -----------------------------------------------------------------
3418   -- DEVELOPER'S NOTE: This procedure should only generate those --
3419   -- objects that depend upon formula compilation taking place.  --
3420   -----------------------------------------------------------------
3421 
3422   ----------------------------------------
3423   -- Generate the formula result rules. --
3424   ----------------------------------------
3425   hr_utility.set_location(l_proc, 30);
3426   gen_formula_rules
3427   (p_effective_date               => p_effective_date
3428   ,p_template_id                  => p_template_id
3429   ,p_business_group_id            => l_business_group_id
3430   ,p_legislation_code             => l_legislation_code
3431   ,p_formula_rules                => l_formula_rules
3432   ,p_sf_core_objects              => l_sf_core_objects
3433   ,p_set_core_objects             => l_set_core_objects
3434   ,p_siv_core_objects             => l_siv_core_objects
3435   ,p_spr_core_objects             => l_spr_core_objects
3436   ,p_sfr_core_objects             => l_sfr_core_objects
3437   );
3438   -----------------------------------
3439   -- Generate the iterative rules. --
3440   -----------------------------------
3441   hr_utility.set_location(l_proc, 40);
3442   gen_iterative_rules
3443   (p_effective_date               => p_effective_date
3444   ,p_template_id                  => p_template_id
3445   ,p_business_group_id            => l_business_group_id
3446   ,p_iterative_rules              => l_iterative_rules
3447   ,p_set_core_objects             => l_set_core_objects
3448   ,p_siv_core_objects             => l_siv_core_objects
3449   ,p_sir_core_objects             => l_sir_core_objects
3450   );
3451   hr_utility.set_location('Leaving:'|| l_proc, 200);
3452 end generate_part2;
3453 -- ----------------------------------------------------------------------------
3454 -- |---------------------------< core_objects_lock >--------------------------|
3455 -- ----------------------------------------------------------------------------
3456 procedure core_objects_lock
3457   (p_core_object_type          in     varchar2
3458   ,p_core_objects              in     pay_element_template_util.t_core_objects
3459   ,p_rowid_id_recs             in out nocopy t_rowid_id_recs
3460   ,p_id_ovn_recs               in out nocopy t_id_ovn_recs
3461   ) is
3462   l_proc                     varchar2(72) := g_package||'core_objects_lock';
3463   l_rowid_id_rec             g_rowid_id_rec;
3464   l_id_ovn_rec               g_id_ovn_rec;
3465   i                          number;
3466   j                          number;
3467   --
3468   cursor csr_formulas_lock(p_formula_id in number) is
3469   select formula_id
3470   ,      rowid
3471   ,      effective_start_date
3472   from   ff_formulas_f
3473   where  formula_id = p_formula_id
3474   for    update of formula_id;
3475   --
3476   cursor csr_balance_types_lock(p_balance_type_id in number) is
3477   select balance_type_id
3478   ,      rowid
3479   ,      null
3480   from   pay_balance_types
3481   where  balance_type_id = p_balance_type_id
3482   for    update of balance_type_id;
3483   --
3484   cursor csr_defined_balances_lock(p_defined_balance_id in number) is
3485   select defined_balance_id
3486   ,      rowid
3487   ,      null
3488   from   pay_defined_balances
3489   where  defined_balance_id = p_defined_balance_id
3490   for    update of defined_balance_id;
3491   --
3492   cursor csr_element_types_lock(p_element_type_id in number) is
3493   select element_type_id
3494   ,      rowid
3495   ,      effective_start_date
3496   from   pay_element_types_f
3497   where  element_type_id = p_element_type_id
3498   for    update of element_type_id;
3499   --
3500   cursor csr_balance_feeds_lock(p_balance_feed_id in number) is
3501   select balance_feed_id
3502   ,      rowid
3503   ,      effective_start_date
3504   from   pay_balance_feeds_f
3505   where  balance_feed_id = p_balance_feed_id
3506   for    update of balance_feed_id;
3507   --
3508   cursor csr_sub_classi_rules_lock(p_sub_classification_rule_id in number) is
3509   select sub_classification_rule_id
3510   ,      rowid
3511   ,      effective_start_date
3512   from   pay_sub_classification_rules_f
3513   where  sub_classification_rule_id = p_sub_classification_rule_id
3514   for    update of sub_classification_rule_id;
3515   --
3516   cursor csr_balance_classis_lock(p_balance_classification_id in number) is
3517   select balance_classification_id
3518   ,      rowid
3519   ,      null
3520   from   pay_balance_classifications
3521   where  balance_classification_id = p_balance_classification_id
3522   for    update of balance_classification_id;
3523   --
3524   cursor csr_input_values_lock(p_input_value_id in number) is
3525   select input_value_id
3526   ,      rowid
3527   ,      effective_start_date
3528   from   pay_input_values_f
3529   where  input_value_id = p_input_value_id
3530   for    update of input_value_id;
3531   --
3532   cursor csr_status_rules_lock(p_status_processing_rule_id in number) is
3533   select status_processing_rule_id
3534   ,      rowid
3535   ,      effective_start_date
3536   from   pay_status_processing_rules_f
3537   where  status_processing_rule_id = p_status_processing_rule_id
3538   for    update of status_processing_rule_id;
3539   --
3540   cursor csr_formula_rules_lock(p_formula_result_rule_id in number) is
3541   select formula_result_rule_id
3542   ,      rowid
3543   ,      effective_start_date
3544   from   pay_formula_result_rules_f
3545   where  formula_result_rule_id = p_formula_result_rule_id
3546   for    update of formula_result_rule_id;
3547   --
3548   cursor csr_iterative_rules_lock(p_iterative_rule_id in number) is
3549   select iterative_rule_id
3550   ,      object_version_number
3551   ,      effective_start_date
3552   from   pay_iterative_rules_f
3553   where  iterative_rule_id = p_iterative_rule_id
3554   for    update of iterative_rule_id;
3555   --
3556   cursor csr_ele_type_usages_lock(p_element_type_usage_id in number) is
3557   select element_type_usage_id
3558   ,      object_version_number
3559   ,      effective_start_date
3560   from   pay_element_type_usages_f
3561   where  element_type_usage_id = p_element_type_usage_id
3562   for    update of element_type_usage_id;
3563   --
3564   cursor csr_gu_bal_exclusions_lock(p_grossup_balances_id in number) is
3565   select grossup_balances_id
3566   ,      object_version_number
3567   ,      null
3568   from   pay_grossup_bal_exclusions
3569   where  grossup_balances_id = p_grossup_balances_id
3570   for    update of grossup_balances_id;
3571   --
3572   cursor csr_bal_attributes_lock(p_balance_attribute_id in number) is
3573   select balance_attribute_id
3574   ,      to_number(null)
3575   ,      null
3576   from   pay_balance_attributes
3577   where  balance_attribute_id = p_balance_attribute_id
3578   for    update of balance_attribute_id;
3579   --
3580 begin
3581   --
3582   -- Generated formulas.
3583   --
3584   hr_utility.set_location('Entering:'|| l_proc, 10);
3585   if p_core_object_type = g_sf_lookup_type then
3586     hr_utility.set_location(l_proc, 15);
3587     i := p_core_objects.first;
3588     j := 0;
3589     loop
3590       exit when not p_core_objects.exists(i);
3591       --
3592       open csr_formulas_lock(p_core_objects(i).core_object_id);
3593       loop
3594         fetch csr_formulas_lock into l_rowid_id_rec;
3595         exit when csr_formulas_lock%notfound;
3596         p_rowid_id_recs(j) := l_rowid_id_rec;
3597         j := j + 1;
3598       end loop;
3599       close csr_formulas_lock;
3600       --
3601       i := p_core_objects.next(i);
3602     end loop;
3603   end if;
3604   --
3605   -- Generated balance types.
3606   --
3607   if p_core_object_type = g_sbt_lookup_type then
3608     hr_utility.set_location(l_proc, 20);
3609     i := p_core_objects.first;
3610     loop
3611       exit when not p_core_objects.exists(i);
3612       --
3613       open csr_balance_types_lock(p_core_objects(i).core_object_id);
3614       loop
3615         fetch csr_balance_types_lock into l_rowid_id_rec;
3616         exit when csr_balance_types_lock%notfound;
3617         --
3618         -- Set up array index as the balance_type_id to allow base
3619         -- balances to be deleted before the balances that reference them.
3620         --
3621         j := l_rowid_id_rec.id;
3622         p_rowid_id_recs(j) := l_rowid_id_rec;
3623       end loop;
3624       close csr_balance_types_lock;
3625       --
3626       i := p_core_objects.next(i);
3627     end loop;
3628   end if;
3629   --
3630   -- Generated defined balances.
3631   --
3632   if p_core_object_type = g_sdb_lookup_type then
3633     hr_utility.set_location(l_proc, 30);
3634     i := p_core_objects.first;
3635     j := 0;
3636     loop
3637       exit when not p_core_objects.exists(i);
3638       --
3639       open csr_defined_balances_lock(p_core_objects(i).core_object_id);
3640       loop
3641         fetch csr_defined_balances_lock into l_rowid_id_rec;
3642         exit when csr_defined_balances_lock%notfound;
3643         p_rowid_id_recs(j) := l_rowid_id_rec;
3644         j := j + 1;
3645       end loop;
3646       close csr_defined_balances_lock;
3647       --
3648       i := p_core_objects.next(i);
3649     end loop;
3650   end if;
3651   --
3652   -- Generated element types.
3653   --
3654   if p_core_object_type = g_set_lookup_type then
3655     hr_utility.set_location(l_proc, 40);
3656     i := p_core_objects.first;
3657     j := 0;
3658     loop
3659       exit when not p_core_objects.exists(i);
3660       --
3661       open csr_element_types_lock(p_core_objects(i).core_object_id);
3662       loop
3663         fetch csr_element_types_lock into l_rowid_id_rec;
3664         exit when csr_element_types_lock%notfound;
3665         p_rowid_id_recs(j) := l_rowid_id_rec;
3666         j := j + 1;
3667       end loop;
3668       close csr_element_types_lock;
3669       --
3670       i := p_core_objects.next(i);
3671     end loop;
3672   end if;
3673   --
3674   -- Generated sub-classification rules.
3675   --
3676   if p_core_object_type = g_ssr_lookup_type then
3677     hr_utility.set_location(l_proc, 50);
3678     i := p_core_objects.first;
3679     j := 0;
3680     loop
3681       exit when not p_core_objects.exists(i);
3682       --
3683       open csr_sub_classi_rules_lock(p_core_objects(i).core_object_id);
3684       loop
3685         fetch csr_sub_classi_rules_lock into l_rowid_id_rec;
3686         exit when csr_sub_classi_rules_lock%notfound;
3687         p_rowid_id_recs(j) := l_rowid_id_rec;
3688         j := j + 1;
3689       end loop;
3690       close csr_sub_classi_rules_lock;
3691       --
3692       i := p_core_objects.next(i);
3693     end loop;
3694   end if;
3695   --
3696   -- Generated balance classifications.
3697   --
3698   if p_core_object_type = g_sbc_lookup_type then
3699     hr_utility.set_location(l_proc, 60);
3700     i := p_core_objects.first;
3701     j := 0;
3702     loop
3703       exit when not p_core_objects.exists(i);
3704       --
3705       open csr_balance_classis_lock(p_core_objects(i).core_object_id);
3706       loop
3707         fetch csr_balance_classis_lock into l_rowid_id_rec;
3708         exit when csr_balance_classis_lock%notfound;
3709         p_rowid_id_recs(j) := l_rowid_id_rec;
3710         j := j + 1;
3711       end loop;
3712       close csr_balance_classis_lock;
3713       --
3714       i := p_core_objects.next(i);
3715     end loop;
3716   end if;
3717   --
3718   -- Generated input values.
3719   --
3720   if p_core_object_type = g_siv_lookup_type then
3721     hr_utility.set_location(l_proc, 70);
3722     i := p_core_objects.first;
3723     j := 0;
3724     loop
3725       exit when not p_core_objects.exists(i);
3726       --
3727       open csr_input_values_lock(p_core_objects(i).core_object_id);
3728       loop
3729         fetch csr_input_values_lock into l_rowid_id_rec;
3730         exit when csr_input_values_lock%notfound;
3731         p_rowid_id_recs(j) := l_rowid_id_rec;
3732         j := j + 1;
3733       end loop;
3734       close csr_input_values_lock;
3735       --
3736       i := p_core_objects.next(i);
3737     end loop;
3738   end if;
3739   --
3740   -- Generated balance feeds.
3741   --
3742   if p_core_object_type = g_sbf_lookup_type then
3743     hr_utility.set_location(l_proc, 80);
3744     i := p_core_objects.first;
3745     j := 0;
3746     loop
3747       exit when not p_core_objects.exists(i);
3748       --
3749       open csr_balance_feeds_lock(p_core_objects(i).core_object_id);
3750       loop
3751         fetch csr_balance_feeds_lock into l_rowid_id_rec;
3752         exit when csr_balance_feeds_lock%notfound;
3753         p_rowid_id_recs(j) := l_rowid_id_rec;
3754         j := j + 1;
3755       end loop;
3756       close csr_balance_feeds_lock;
3757       --
3758       i := p_core_objects.next(i);
3759     end loop;
3760   end if;
3761   --
3762   -- Generated status processing rules.
3763   --
3764   if p_core_object_type = g_spr_lookup_type then
3765     hr_utility.set_location(l_proc, 90);
3766     i := p_core_objects.first;
3767     j := 0;
3768     loop
3769       exit when not p_core_objects.exists(i);
3770       --
3771       open csr_status_rules_lock(p_core_objects(i).core_object_id);
3772       loop
3773         fetch csr_status_rules_lock into l_rowid_id_rec;
3774         exit when csr_status_rules_lock%notfound;
3775         p_rowid_id_recs(j) := l_rowid_id_rec;
3776         j := j + 1;
3777       end loop;
3778       close csr_status_rules_lock;
3779       --
3780       i := p_core_objects.next(i);
3781     end loop;
3782   end if;
3783   --
3784   -- Generated formula rules.
3785   --
3786   if p_core_object_type = g_sfr_lookup_type then
3787     hr_utility.set_location(l_proc, 100);
3788     i := p_core_objects.first;
3789     j := 0;
3790     loop
3791       exit when not p_core_objects.exists(i);
3792       --
3793       open csr_formula_rules_lock(p_core_objects(i).core_object_id);
3794       loop
3795         fetch csr_formula_rules_lock into l_rowid_id_rec;
3796         exit when csr_formula_rules_lock%notfound;
3797         p_rowid_id_recs(j) := l_rowid_id_rec;
3798         j := j + 1;
3799       end loop;
3800       close csr_formula_rules_lock;
3801       --
3802       i := p_core_objects.next(i);
3803     end loop;
3804   end if;
3805   --
3806   -- Generated iterative rules.
3807   --
3808   if p_core_object_type = g_sir_lookup_type then
3809     hr_utility.set_location(l_proc, 110);
3810     i := p_core_objects.first;
3811     j := 0;
3812     loop
3813       exit when not p_core_objects.exists(i);
3814       --
3815       open csr_iterative_rules_lock(p_core_objects(i).core_object_id);
3816       loop
3817         fetch csr_iterative_rules_lock into l_id_ovn_rec;
3818         exit when csr_iterative_rules_lock%notfound;
3819         p_id_ovn_recs(j) := l_id_ovn_rec;
3820         j := j + 1;
3821       end loop;
3822       close csr_iterative_rules_lock;
3823       --
3824       i := p_core_objects.next(i);
3825     end loop;
3826   end if;
3827   --
3828   -- Generated element type usages.
3829   --
3830   if p_core_object_type = g_seu_lookup_type then
3831     hr_utility.set_location(l_proc, 120);
3832     i := p_core_objects.first;
3833     j := 0;
3834     loop
3835       exit when not p_core_objects.exists(i);
3836       --
3837       open csr_ele_type_usages_lock(p_core_objects(i).core_object_id);
3838       loop
3839         fetch csr_ele_type_usages_lock into l_id_ovn_rec;
3840         exit when csr_ele_type_usages_lock%notfound;
3841         p_id_ovn_recs(j) := l_id_ovn_rec;
3842         j := j + 1;
3843       end loop;
3844       close csr_ele_type_usages_lock;
3845       --
3846       i := p_core_objects.next(i);
3847     end loop;
3848   end if;
3849   --
3850   -- Generated grossup balance exclusions.
3851   --
3852   if p_core_object_type = g_sgb_lookup_type then
3853     hr_utility.set_location(l_proc, 130);
3854     i := p_core_objects.first;
3855     j := 0;
3856     loop
3857       exit when not p_core_objects.exists(i);
3858       --
3859       open csr_gu_bal_exclusions_lock(p_core_objects(i).core_object_id);
3860       loop
3861         fetch csr_gu_bal_exclusions_lock into l_id_ovn_rec;
3862         exit when csr_gu_bal_exclusions_lock%notfound;
3863         p_id_ovn_recs(j) := l_id_ovn_rec;
3864         j := j + 1;
3865       end loop;
3866       close csr_gu_bal_exclusions_lock;
3867       --
3868       i := p_core_objects.next(i);
3869     end loop;
3870   end if;
3871   --
3872   -- Generated balance attributes.
3873   --
3874   if p_core_object_type = g_sba_lookup_type then
3875     hr_utility.set_location(l_proc, 140);
3876     i := p_core_objects.first;
3877     j := 0;
3878     loop
3879       exit when not p_core_objects.exists(i);
3880       --
3881       open csr_bal_attributes_lock(p_core_objects(i).core_object_id);
3882       loop
3883         fetch csr_bal_attributes_lock into l_id_ovn_rec;
3884         exit when csr_bal_attributes_lock%notfound;
3885         p_id_ovn_recs(j) := l_id_ovn_rec;
3886         j := j + 1;
3887       end loop;
3888       close csr_bal_attributes_lock;
3889       --
3890       i := p_core_objects.next(i);
3891     end loop;
3892   end if;
3893   --
3894   hr_utility.set_location('Leaving:'|| l_proc, 200);
3895   return;
3896 exception
3897   when others then
3898     hr_utility.set_location('Leaving:'|| l_proc, 210);
3899     --
3900     if csr_formulas_lock%isopen then
3901       close csr_formulas_lock;
3902     end if;
3903     --
3904     if csr_balance_types_lock%isopen then
3905       close csr_balance_types_lock;
3906     end if;
3907     --
3908     if csr_defined_balances_lock%isopen then
3909       close csr_defined_balances_lock;
3910     end if;
3911     --
3912     if csr_element_types_lock%isopen then
3913       close csr_element_types_lock;
3914     end if;
3915     --
3916     if csr_sub_classi_rules_lock%isopen then
3917       close csr_sub_classi_rules_lock;
3918     end if;
3919     --
3920     if csr_balance_classis_lock%isopen then
3921       close csr_balance_classis_lock;
3922     end if;
3923     --
3924     if csr_input_values_lock%isopen then
3925       close csr_input_values_lock;
3926     end if;
3927     --
3928     if csr_balance_feeds_lock%isopen then
3929       close csr_balance_feeds_lock;
3930     end if;
3931     --
3932     if csr_status_rules_lock%isopen then
3933       close csr_status_rules_lock;
3934     end if;
3935     --
3936     if csr_formula_rules_lock%isopen then
3937       close csr_formula_rules_lock;
3938     end if;
3939     --
3940     if csr_iterative_rules_lock%isopen then
3941       close csr_iterative_rules_lock;
3942     end if;
3943     --
3944     if csr_ele_type_usages_lock%isopen then
3945       close csr_ele_type_usages_lock;
3946     end if;
3947     --
3948     if csr_gu_bal_exclusions_lock%isopen then
3949       close csr_gu_bal_exclusions_lock;
3950     end if;
3951     --
3952     if csr_bal_attributes_lock%isopen then
3953       close csr_bal_attributes_lock;
3954     end if;
3955     --
3956     raise;
3957 end core_objects_lock;
3958 -- ----------------------------------------------------------------------------
3959 -- |-----------------------------< el_balance_type >-------------------------|
3960 -- ----------------------------------------------------------------------------
3961 procedure del_balance_type
3962 (p_balance_type_id in            number
3963 ,p_rowid_id_recs   in            t_rowid_id_recs
3964 ,p_remaining_recs  in out nocopy t_rowid_id_recs
3965 ) is
3966 --
3967 cursor csr_child_balances(p_balance_type_id in number) is
3968 select pbt.balance_type_id
3969 ,      pbt.balance_name
3970 from   pay_balance_types pbt
3971 where  pbt.base_balance_type_id = p_balance_type_id;
3972 --
3973 l_balance_name varchar2(320);
3974 begin
3975   --
3976   -- Only delete balances on the remaining records list. If not on the list,
3977   -- they may have been deleted before.
3978   --
3979   if p_remaining_recs.exists(p_balance_type_id) then
3980     --
3981     -- Delete from the remaining records list to avoid any chance of a
3982     -- double delete even though the template engine prevents circular
3983     -- relationships, the core schema may have been updated.
3984     --
3985     p_remaining_recs.delete(p_balance_type_id);
3986     --
3987     -- Loop through the child balances.
3988     --
3989     for crec in csr_child_balances(p_balance_type_id) loop
3990       --
3991       -- Recursively delete the child balances if they belong to the
3992       -- template.
3993       --
3994       if p_rowid_id_recs.exists(crec.balance_type_id) then
3995         del_balance_type
3996         (p_balance_type_id => crec.balance_type_id
3997         ,p_rowid_id_recs   => p_rowid_id_recs
3998         ,p_remaining_recs  => p_remaining_recs
3999         );
4000       else
4001         --
4002         -- The child balance is external to the template - raise an error.
4003         --
4004         select pbt.balance_name
4005         into   l_balance_name
4006         from   pay_balance_types pbt
4007         where  pbt.balance_type_id = p_balance_type_id
4008         ;
4009         hr_utility.set_message(801, 'PAY_50213_ETM_EXTERNAL_BAL_DEL');
4010         hr_utility.set_message_token('BASE_BALANCE', l_balance_name);
4011         hr_utility.set_message_token('EXT_BALANCE', crec.balance_name);
4012         hr_utility.raise_error;
4013       end if;
4014     end loop;
4015     --
4016     -- Delete this balance.
4017     --
4018     pay_balance_types_pkg.delete_row
4019     (x_rowid           => p_rowid_id_recs(p_balance_type_id).rowid
4020     ,x_balance_type_id => p_balance_type_id
4021     );
4022   end if;
4023 end del_balance_type;
4024 -- ----------------------------------------------------------------------------
4025 -- |----------------------------< del_balance_types >-------------------------|
4026 -- ----------------------------------------------------------------------------
4027 procedure del_balance_types
4028   (p_rowid_id_recs                 in     t_rowid_id_recs
4029   ) is
4030   l_proc                varchar2(72) := g_package||'del_balance_types';
4031   i                     number;
4032   l_remaining_recs      t_rowid_id_recs;
4033 begin
4034   hr_utility.set_location('Entering:'|| l_proc, 10);
4035   l_remaining_recs := p_rowid_id_recs;
4036   --
4037   i := p_rowid_id_recs.first;
4038   loop
4039     exit when not p_rowid_id_recs.exists(i);
4040     --
4041     del_balance_type
4042     (p_balance_type_id => i
4043     ,p_rowid_id_recs   => p_rowid_id_recs
4044     ,p_remaining_recs  => l_remaining_recs
4045     );
4046     --
4047     i := p_rowid_id_recs.next(i);
4048   end loop;
4049   hr_utility.set_location('Leaving:'|| l_proc, 100);
4050 end del_balance_types;
4051 -- ----------------------------------------------------------------------------
4052 -- |--------------------------< del_defined_balances >------------------------|
4053 -- ----------------------------------------------------------------------------
4054 procedure del_defined_balances
4055   (p_rowid_id_recs                 in     t_rowid_id_recs
4056   ) is
4057   l_proc                varchar2(72) := g_package||'del_defined_balances';
4058   i                     number;
4059 begin
4060   hr_utility.set_location('Entering:'|| l_proc, 10);
4061   i := p_rowid_id_recs.first;
4062   loop
4063     exit when not p_rowid_id_recs.exists(i);
4064     --
4065       pay_defined_balances_pkg.delete_row
4066       (x_rowid                        => p_rowid_id_recs(i).rowid
4067       ,x_defined_balance_id           => p_rowid_id_recs(i).id
4068       );
4069     --
4070     i := p_rowid_id_recs.next(i);
4071   end loop;
4072   hr_utility.set_location('Leaving:'|| l_proc, 100);
4073 end del_defined_balances;
4074 -- ----------------------------------------------------------------------------
4075 -- |---------------------------< del_element_types >--------------------------|
4076 -- ----------------------------------------------------------------------------
4077 procedure del_element_types
4078   (p_rowid_id_recs                 in     t_rowid_id_recs
4079   ) is
4080   l_proc                varchar2(72) := g_package||'del_element_types';
4081   i                     number;
4082   l_id                  number;
4083 begin
4084   hr_utility.set_location('Entering:'|| l_proc, 10);
4085   i := p_rowid_id_recs.first;
4086   loop
4087     exit when not p_rowid_id_recs.exists(i);
4088     --
4089     if l_id is null or l_id <> p_rowid_id_recs(i).id then
4090       pay_element_types_pkg.delete_row
4091       (p_rowid                        => p_rowid_id_recs(i).rowid
4092       ,p_element_type_id              => p_rowid_id_recs(i).id
4093       ,p_session_date                 => p_rowid_id_recs(i).effective_date
4094       ,p_processing_priority          => null
4095       ,p_delete_mode                  => g_zap_deletion_mode
4096       );
4097       l_id := p_rowid_id_recs(i).id;
4098     end if;
4099     --
4100     i := p_rowid_id_recs.next(i);
4101   end loop;
4102   hr_utility.set_location('Leaving:'|| l_proc, 100);
4103 end del_element_types;
4104 -- ----------------------------------------------------------------------------
4105 -- |---------------------------< del_sub_classi_rules >-----------------------|
4106 -- ----------------------------------------------------------------------------
4107 procedure del_sub_classi_rules
4108   (p_rowid_id_recs                 in     t_rowid_id_recs
4109   ) is
4110   l_proc                varchar2(72) := g_package||'del_sub_classi_rules';
4111   i                     number;
4112 begin
4113   hr_utility.set_location('Entering:'|| l_proc, 10);
4114   i := p_rowid_id_recs.first;
4115   loop
4116     exit when not p_rowid_id_recs.exists(i);
4117     --
4118       pay_sub_class_rules_pkg.delete_row
4119       (p_rowid                        => p_rowid_id_recs(i).rowid
4120       ,p_sub_classification_rule_id   => p_rowid_id_recs(i).id
4121       ,p_delete_mode                  => g_zap_deletion_mode
4122       ,p_validation_start_date        => hr_api.g_sot
4123       ,p_validation_end_date          => hr_api.g_eot
4124       );
4125     --
4126     i := p_rowid_id_recs.next(i);
4127   end loop;
4128   hr_utility.set_location('Leaving:'|| l_proc, 100);
4129 end del_sub_classi_rules;
4130 -- ----------------------------------------------------------------------------
4131 -- |---------------------------< del_balance_classis >------------------------|
4132 -- ----------------------------------------------------------------------------
4133 procedure del_balance_classis
4134   (p_rowid_id_recs                 in     t_rowid_id_recs
4135   ) is
4136   l_proc                varchar2(72) := g_package||'del_balance_classis';
4137   i                     number;
4138 begin
4139   hr_utility.set_location('Entering:'|| l_proc, 10);
4140   i := p_rowid_id_recs.first;
4141   loop
4142     exit when not p_rowid_id_recs.exists(i);
4143     --
4144       pay_bal_classifications_pkg.delete_row
4145       (x_rowid                        => p_rowid_id_recs(i).rowid
4146       ,x_balance_classification_id    => p_rowid_id_recs(i).id
4147       );
4148     --
4149     i := p_rowid_id_recs.next(i);
4150   end loop;
4151   hr_utility.set_location('Leaving:'|| l_proc, 100);
4152 end del_balance_classis;
4153 -- ----------------------------------------------------------------------------
4154 -- |----------------------------< del_input_values >--------------------------|
4155 -- ----------------------------------------------------------------------------
4156 procedure del_input_values
4157   (p_rowid_id_recs                 in     t_rowid_id_recs
4158   ) is
4159   l_proc                varchar2(72) := g_package||'del_input_values';
4160   i                     number;
4161   l_id                  number;
4162 begin
4163   hr_utility.set_location('Entering:'|| l_proc, 10);
4164   i := p_rowid_id_recs.first;
4165   loop
4166     exit when not p_rowid_id_recs.exists(i);
4167     --
4168     if l_id is null or l_id <> p_rowid_id_recs(i).id then
4169       pay_input_values_pkg.delete_row
4170       (p_rowid                        => p_rowid_id_recs(i).rowid
4171       ,p_input_value_id               => p_rowid_id_recs(i).id
4172       ,p_delete_mode                  => g_zap_deletion_mode
4173       ,p_session_date                 => p_rowid_id_recs(i).effective_date
4174       ,p_validation_start_date        => hr_api.g_sot
4175       ,p_validation_end_date          => hr_api.g_eot
4176       );
4177       l_id := p_rowid_id_recs(i).id;
4178     end if;
4179     --
4180     i := p_rowid_id_recs.next(i);
4181   end loop;
4182   hr_utility.set_location('Leaving:'|| l_proc, 100);
4183 end del_input_values;
4184 -- ----------------------------------------------------------------------------
4185 -- |----------------------------< del_formula_rules >-------------------------|
4186 -- ----------------------------------------------------------------------------
4187 procedure del_formula_rules
4188   (p_rowid_id_recs                 in     t_rowid_id_recs
4189   ) is
4190   l_proc                varchar2(72) := g_package||'del_formula_rules';
4191   i                     number;
4192 begin
4193   hr_utility.set_location('Entering:'|| l_proc, 10);
4194   i := p_rowid_id_recs.first;
4195   loop
4196     exit when not p_rowid_id_recs.exists(i);
4197     --
4198       pay_formula_result_rules_pkg.delete_row
4199       (p_rowid                        => p_rowid_id_recs(i).rowid
4200       );
4201     --
4202     i := p_rowid_id_recs.next(i);
4203   end loop;
4204   hr_utility.set_location('Leaving:'|| l_proc, 100);
4205 end del_formula_rules;
4206 -- ----------------------------------------------------------------------------
4207 -- |----------------------------< del_status_rules >-------------------------|
4208 -- ----------------------------------------------------------------------------
4209 procedure del_status_rules
4210   (p_rowid_id_recs                 in     t_rowid_id_recs
4211   ) is
4212   l_proc                varchar2(72) := g_package||'del_status_rules';
4213   i                     number;
4214   l_id                  number;
4215 begin
4216   hr_utility.set_location('Entering:'|| l_proc, 10);
4217   i := p_rowid_id_recs.first;
4218   loop
4219     exit when not p_rowid_id_recs.exists(i);
4220     --
4221     if l_id is null or l_id <> p_rowid_id_recs(i).id then
4222       pay_status_rules_pkg.delete_row
4223       (x_rowid                     => p_rowid_id_recs(i).rowid
4224       ,p_session_date              => p_rowid_id_recs(i).effective_date
4225       ,p_delete_mode               => g_zap_deletion_mode
4226       ,p_status_processing_rule_id => p_rowid_id_recs(i).id
4227       );
4228       l_id := p_rowid_id_recs(i).id;
4229     end if;
4230     --
4231     i := p_rowid_id_recs.next(i);
4232   end loop;
4233   hr_utility.set_location('Leaving:'|| l_proc, 100);
4234 end del_status_rules;
4235 -- ----------------------------------------------------------------------------
4236 -- |--------------------------< del_iterative_rules >-------------------------|
4237 -- ----------------------------------------------------------------------------
4238 procedure del_iterative_rules
4239   (p_id_ovn_recs                  in        t_id_ovn_recs
4240   ) is
4241   --
4242   l_proc                  varchar2(72) := g_package||'del_iterative_rules';
4243   i                       number;
4244   l_effective_start_date  date;
4245   l_effective_end_date    date;
4246   l_ovn                   number;
4247   l_id                    number;
4248   --
4249 begin
4250   hr_utility.set_location('Entering:'||l_proc, 10);
4251   --
4252   i := p_id_ovn_recs.first;
4253   loop
4254     exit when not p_id_ovn_recs.exists(i);
4255     --
4256     if l_id is null or l_id <> p_id_ovn_recs(i).id then
4257       l_ovn := p_id_ovn_recs(i).ovn;
4258       l_effective_start_date := null;
4259       l_effective_end_date   := null;
4260       --
4261       pay_itr_del.del
4262       (p_effective_date              => p_id_ovn_recs(i).effective_date
4263       ,p_datetrack_mode              => g_zap_deletion_mode
4264       ,p_iterative_rule_id           => p_id_ovn_recs(i).id
4265       ,p_object_version_number       => l_ovn
4266       ,p_effective_start_date        => l_effective_start_date
4267       ,p_effective_end_date          => l_effective_end_date
4268       );
4269       --
4270       l_id := p_id_ovn_recs(i).id;
4271     end if;
4272     --
4273     i := p_id_ovn_recs.next(i);
4274   end loop;
4275   hr_utility.set_location('Leaving:'||l_proc, 100);
4276 end del_iterative_rules;
4277 -- ----------------------------------------------------------------------------
4278 -- -------------------------< del_ele_type_usages >---------------------------|
4279 -- ----------------------------------------------------------------------------
4280 procedure del_ele_type_usages
4281   (p_id_ovn_recs                in        t_id_ovn_recs
4282   ) is
4283   --
4284   l_proc                  varchar2(72)  := g_package||'del_ele_type_usages';
4285   i                       number;
4286   l_effective_start_date  date;
4287   l_effective_end_date    date;
4288   l_ovn                   number;
4289   l_id                    number;
4290   --
4291 begin
4292   hr_utility.set_location('Entering:'||l_proc, 10);
4293   --
4294   i := p_id_ovn_recs.first;
4295   loop
4296     exit when not p_id_ovn_recs.exists(i);
4297     --
4298     if l_id is null or l_id <> p_id_ovn_recs(i).id then
4299       l_ovn := p_id_ovn_recs(i).ovn;
4300       l_effective_start_date  := null;
4301       l_effective_end_date    := null;
4302       --
4303       pay_etu_del.del
4304       (p_effective_date               => p_id_ovn_recs(i).effective_date
4305       ,p_datetrack_mode               => g_zap_deletion_mode
4306       ,p_element_type_usage_id        => p_id_ovn_recs(i).id
4307       ,p_object_version_number        => l_ovn
4308       ,p_effective_start_date         => l_effective_start_date
4309       ,p_effective_end_date           => l_effective_end_date
4310       );
4311       --
4312       l_id := p_id_ovn_recs(i).id;
4313     end if;
4314     --
4315     i := p_id_ovn_recs.next(i);
4316   end loop;
4317   hr_utility.set_location('Leaving:'||l_proc, 100);
4318 end del_ele_type_usages;
4319 -- ----------------------------------------------------------------------------
4320 -- |-----------------------< del_gu_bal_exclusions >--------------------------|
4321 -- ----------------------------------------------------------------------------
4322 procedure del_gu_bal_exclusions
4323   (p_id_ovn_recs                in        t_id_ovn_recs
4324   ) is
4325   --
4326   l_proc    varchar2(72) := g_package||'del_gu_bal_exclusions';
4327   i         number;
4328   --
4329 begin
4330   hr_utility.set_location('Entering:'||l_proc, 10);
4331   i := p_id_ovn_recs.first;
4332   loop
4333     exit when not p_id_ovn_recs.exists(i);
4334     --
4335       pay_gbe_del.del
4336       (p_grossup_balances_id          => p_id_ovn_recs(i).id
4337       ,p_object_version_number        => p_id_ovn_recs(i).ovn
4338       );
4339     --
4340     i := p_id_ovn_recs.next(i);
4341   end loop;
4342   hr_utility.set_location('Leaving:'||l_proc, 100);
4343 end del_gu_bal_exclusions;
4344 -- ----------------------------------------------------------------------------
4345 -- |-------------------------< del_bal_attributes >---------------------------|
4346 -- ----------------------------------------------------------------------------
4347 procedure del_bal_attributes
4348   (p_id_ovn_recs                in        t_id_ovn_recs
4349   ) is
4350   --
4351   l_proc    varchar2(72) := g_package||'del_bal_attributes';
4352   i         number;
4353   --
4354 begin
4355   hr_utility.set_location('Entering:'||l_proc, 10);
4356   i := p_id_ovn_recs.first;
4357   loop
4358     exit when not p_id_ovn_recs.exists(i);
4359     --
4360       pay_balance_attribute_api.delete_balance_attribute
4361       (p_balance_attribute_id => p_id_ovn_recs(i).id
4362       );
4363     --
4364     i := p_id_ovn_recs.next(i);
4365   end loop;
4366   hr_utility.set_location('Leaving:'||l_proc, 100);
4367 end del_bal_attributes;
4368 -- ----------------------------------------------------------------------------
4369 -- |------------------------------< del_formulas >----------------------------|
4370 -- ----------------------------------------------------------------------------
4371 procedure del_formulas
4372   (p_rowid_id_recs                 in     t_rowid_id_recs
4373   ) is
4374   l_proc                varchar2(72) := g_package||'del_formulas';
4375   i                     number;
4376   l_formula_id          number;
4377   --
4378 begin
4379   hr_utility.set_location('Entering:'|| l_proc, 10);
4380   i := p_rowid_id_recs.first;
4381   loop
4382     exit when not p_rowid_id_recs.exists(i);
4383     --
4384     if l_formula_id is null or l_formula_id <> p_rowid_id_recs(i).id then
4385       l_formula_id := p_rowid_id_recs(i).id;
4386       --
4387       ff_formulas_f_pkg.delete_row
4388       (x_rowid                        => p_rowid_id_recs(i).rowid
4389       ,x_formula_id                   => l_formula_id
4390       ,x_dt_delete_mode               => g_zap_deletion_mode
4391       ,x_validation_start_date        => hr_api.g_sot
4392       ,x_validation_end_date          => hr_api.g_eot
4393       );
4394       --
4395       l_formula_id := p_rowid_id_recs(i).id;
4396     end if;
4397     --
4398     i := p_rowid_id_recs.next(i);
4399   end loop;
4400   hr_utility.set_location('Leaving:'|| l_proc, 100);
4401 end del_formulas;
4402 -- ----------------------------------------------------------------------------
4403 -- |----------------------------< del_formula_info >--------------------------|
4404 -- ----------------------------------------------------------------------------
4405 procedure del_formula_info
4406   (p_rowid_id_recs                 in     t_rowid_id_recs
4407   ) is
4408   l_proc                varchar2(72) := g_package||'del_formula_info';
4409   i                     number;
4410   l_formula_id          number;
4411   --
4412 begin
4413   hr_utility.set_location('Entering:'|| l_proc, 10);
4414   i := p_rowid_id_recs.first;
4415   loop
4416     exit when not p_rowid_id_recs.exists(i);
4417     --
4418     -- Delete the compiled information and usages table rows before the
4419     -- formula row.
4420     --
4421     l_formula_id := p_rowid_id_recs(i).id;
4422     delete from ff_compiled_info_f where formula_id = l_formula_id;
4423     --
4424     delete from ff_fdi_usages_f where formula_id = l_formula_id;
4425     --
4426     i := p_rowid_id_recs.next(i);
4427   end loop;
4428   hr_utility.set_location('Leaving:'|| l_proc, 100);
4429 end del_formula_info;
4430 -- ----------------------------------------------------------------------------
4431 -- |-------------------------< drop_formula_packages >------------------------|
4432 -- ----------------------------------------------------------------------------
4433 procedure drop_formula_packages
4434   (p_rowid_id_recs                 in out nocopy t_rowid_id_recs
4435   ) is
4436   l_proc                varchar2(72) := g_package||'drop_formula_packages';
4437   i                     number;
4438   j                     number;
4439   k                     number;
4440   --
4441   -- Cursor to get formula package names. It carries out a LIKE query to get
4442   -- the name of an object of the appropriate type.
4443   --
4444   cursor csr_package_names
4445   (p_package_name in varchar2
4446   ,p_object_type in varchar2
4447   ) is
4448   select object_name package_name
4449   from   user_objects
4450   where  object_name like p_package_name
4451   and    object_type = p_object_type
4452   ;
4453 begin
4454   hr_utility.set_location('Entering:'|| l_proc, 10);
4455   --
4456   -- Delete records with duplicate id values.
4457   --
4458   i := p_rowid_id_recs.first;
4459   loop
4460     exit when not p_rowid_id_recs.exists(i);
4461     --
4462     j := p_rowid_id_recs.next(i);
4463     loop
4464       exit when not p_rowid_id_recs.exists(j);
4465       --
4466       if p_rowid_id_recs(i).id = p_rowid_id_recs(j).id then
4467         k := j;
4468         j := p_rowid_id_recs.next(j);
4469         p_rowid_id_recs.delete(k);
4470       else
4471         j := p_rowid_id_recs.next(j);
4472       end if;
4473     end loop;
4474     --
4475     i := p_rowid_id_recs.next(i);
4476   end loop;
4477   --
4478   -- Get the package names, and drop the packages.
4479   --
4480   i := p_rowid_id_recs.first;
4481   loop
4482     exit when not p_rowid_id_recs.exists(i);
4483     --
4484     -- Handle FFP packages.
4485     --
4486     for crec in csr_package_names
4487     (p_package_name => 'FFP' || p_rowid_id_recs(i).id || '_%'
4488     ,p_object_type  => 'PACKAGE'
4489     )
4490     loop
4491       execute immediate 'DROP PACKAGE ' || crec.package_name;
4492     end loop;
4493     --
4494     -- Drop FFW package bodies only.
4495     --
4496     for crec in csr_package_names
4497     (p_package_name => 'FFW' || p_rowid_id_recs(i).id || '_%'
4498     ,p_object_type  => 'PACKAGE BODY'
4499     )
4500     loop
4501       execute immediate 'DROP PACKAGE BODY ' || crec.package_name;
4502     end loop;
4503     --
4504     i := p_rowid_id_recs.next(i);
4505   end loop;
4506   hr_utility.set_location('Leaving:'|| l_proc, 100);
4507 exception
4508   when others then
4509     hr_utility.set_location('Leaving:'|| l_proc, 110);
4510     if csr_package_names%isopen then
4511       close csr_package_names;
4512     end if;
4513 end drop_formula_packages;
4514 -- ----------------------------------------------------------------------------
4515 -- |-----------------------------< zap_core_objects >-------------------------|
4516 -- ----------------------------------------------------------------------------
4517 procedure zap_core_objects
4518   (p_all_core_objects         in     pay_element_template_util.t_core_objects
4519   ,p_drop_formula_packages    in     boolean
4520   ) is
4521   l_proc                    varchar2(72) := g_package||'zap_core_objects';
4522   --
4523   -- Generation tables.
4524   --
4525   l_sf_core_objects         pay_element_template_util.t_core_objects;
4526   l_sbt_core_objects        pay_element_template_util.t_core_objects;
4527   l_sdb_core_objects        pay_element_template_util.t_core_objects;
4528   l_set_core_objects        pay_element_template_util.t_core_objects;
4529   l_ssr_core_objects        pay_element_template_util.t_core_objects;
4530   l_sbc_core_objects        pay_element_template_util.t_core_objects;
4531   l_siv_core_objects        pay_element_template_util.t_core_objects;
4532   l_sbf_core_objects        pay_element_template_util.t_core_objects;
4533   l_spr_core_objects        pay_element_template_util.t_core_objects;
4534   l_sfr_core_objects        pay_element_template_util.t_core_objects;
4535   l_sir_core_objects        pay_element_template_util.t_core_objects;
4536   l_seu_core_objects        pay_element_template_util.t_core_objects;
4537   l_sgb_core_objects        pay_element_template_util.t_core_objects;
4538   l_sba_core_objects        pay_element_template_util.t_core_objects;
4539   --
4540   -- Deletion tables for the generated objects.
4541   --
4542   l_sf_rowid_id_recs        t_rowid_id_recs;
4543   l_sbt_rowid_id_recs       t_rowid_id_recs;
4544   l_sdb_rowid_id_recs       t_rowid_id_recs;
4545   l_set_rowid_id_recs       t_rowid_id_recs;
4546   l_ssr_rowid_id_recs       t_rowid_id_recs;
4547   l_sbc_rowid_id_recs       t_rowid_id_recs;
4548   l_siv_rowid_id_recs       t_rowid_id_recs;
4549   l_sbf_rowid_id_recs       t_rowid_id_recs;
4550   l_spr_rowid_id_recs       t_rowid_id_recs;
4551   l_sfr_rowid_id_recs       t_rowid_id_recs;
4552   l_null_rowid_id_recs      t_rowid_id_recs;
4553   l_sir_id_ovn_recs         t_id_ovn_recs;
4554   l_seu_id_ovn_recs         t_id_ovn_recs;
4555   l_sgb_id_ovn_recs         t_id_ovn_recs;
4556   l_sba_id_ovn_recs         t_id_ovn_recs;
4557   l_null_id_ovn_recs        t_id_ovn_recs;
4558 begin
4559   hr_utility.set_location('Entering:'|| l_proc, 10);
4560   --
4561   -- Create the table of generated objects.
4562   --
4563   create_generation_tables
4564   (p_all_core_objects             => p_all_core_objects
4565   ,p_index_by_core_object_id      => true
4566   ,p_sf_core_objects              => l_sf_core_objects
4567   ,p_sbt_core_objects             => l_sbt_core_objects
4568   ,p_sdb_core_objects             => l_sdb_core_objects
4569   ,p_set_core_objects             => l_set_core_objects
4570   ,p_ssr_core_objects             => l_ssr_core_objects
4571   ,p_sbc_core_objects             => l_sbc_core_objects
4572   ,p_siv_core_objects             => l_siv_core_objects
4573   ,p_sbf_core_objects             => l_sbf_core_objects
4574   ,p_spr_core_objects             => l_spr_core_objects
4575   ,p_sfr_core_objects             => l_sfr_core_objects
4576   ,p_sir_core_objects             => l_sir_core_objects
4577   ,p_seu_core_objects             => l_seu_core_objects
4578   ,p_sgb_core_objects             => l_sgb_core_objects
4579   ,p_sba_core_objects             => l_sba_core_objects
4580   );
4581   --
4582   -- Lock the generated objects in lock ladder order.
4583   --
4584   hr_utility.set_location(l_proc, 20);
4585   core_objects_lock
4586   (p_core_object_type             => g_sf_lookup_type
4587   ,p_core_objects                 => l_sf_core_objects
4588   ,p_rowid_id_recs                => l_sf_rowid_id_recs
4589   ,p_id_ovn_recs                  => l_null_id_ovn_recs
4590   );
4591   core_objects_lock
4592   (p_core_object_type             => g_sbt_lookup_type
4593   ,p_core_objects                 => l_sbt_core_objects
4594   ,p_rowid_id_recs                => l_sbt_rowid_id_recs
4595   ,p_id_ovn_recs                  => l_null_id_ovn_recs
4596   );
4597   core_objects_lock
4598   (p_core_object_type             => g_sdb_lookup_type
4599   ,p_core_objects                 => l_sdb_core_objects
4600   ,p_rowid_id_recs                => l_sdb_rowid_id_recs
4601   ,p_id_ovn_recs                  => l_null_id_ovn_recs
4602   );
4603   core_objects_lock
4604   (p_core_object_type             => g_set_lookup_type
4605   ,p_core_objects                 => l_set_core_objects
4606   ,p_rowid_id_recs                => l_set_rowid_id_recs
4607   ,p_id_ovn_recs                  => l_null_id_ovn_recs
4608   );
4609   core_objects_lock
4610   (p_core_object_type             => g_ssr_lookup_type
4611   ,p_core_objects                 => l_ssr_core_objects
4612   ,p_rowid_id_recs                => l_ssr_rowid_id_recs
4613   ,p_id_ovn_recs                  => l_null_id_ovn_recs
4614   );
4615   core_objects_lock
4616   (p_core_object_type             => g_sbc_lookup_type
4617   ,p_core_objects                 => l_sbc_core_objects
4618   ,p_rowid_id_recs                => l_sbc_rowid_id_recs
4619   ,p_id_ovn_recs                  => l_null_id_ovn_recs
4620   );
4621   core_objects_lock
4622   (p_core_object_type             => g_siv_lookup_type
4623   ,p_core_objects                 => l_siv_core_objects
4624   ,p_rowid_id_recs                => l_siv_rowid_id_recs
4625   ,p_id_ovn_recs                  => l_null_id_ovn_recs
4626   );
4627   core_objects_lock
4628   (p_core_object_type             => g_sbf_lookup_type
4629   ,p_core_objects                 => l_sbf_core_objects
4630   ,p_rowid_id_recs                => l_sbf_rowid_id_recs
4631   ,p_id_ovn_recs                  => l_null_id_ovn_recs
4632   );
4633   core_objects_lock
4634   (p_core_object_type             => g_spr_lookup_type
4635   ,p_core_objects                 => l_spr_core_objects
4636   ,p_rowid_id_recs                => l_spr_rowid_id_recs
4637   ,p_id_ovn_recs                  => l_null_id_ovn_recs
4638   );
4639   core_objects_lock
4640   (p_core_object_type             => g_sfr_lookup_type
4641   ,p_core_objects                 => l_sfr_core_objects
4642   ,p_rowid_id_recs                => l_sfr_rowid_id_recs
4643   ,p_id_ovn_recs                  => l_null_id_ovn_recs
4644   );
4645   core_objects_lock
4646   (p_core_object_type             => g_sir_lookup_type
4647   ,p_core_objects                 => l_sir_core_objects
4648   ,p_rowid_id_recs                => l_null_rowid_id_recs
4649   ,p_id_ovn_recs                  => l_sir_id_ovn_recs
4650   );
4651   core_objects_lock
4652   (p_core_object_type             => g_seu_lookup_type
4653   ,p_core_objects                 => l_seu_core_objects
4654   ,p_rowid_id_recs                => l_null_rowid_id_recs
4655   ,p_id_ovn_recs                  => l_seu_id_ovn_recs
4656   );
4657   core_objects_lock
4658   (p_core_object_type             => g_sgb_lookup_type
4659   ,p_core_objects                 => l_sgb_core_objects
4660   ,p_rowid_id_recs                => l_null_rowid_id_recs
4661   ,p_id_ovn_recs                  => l_sgb_id_ovn_recs
4662   );
4663   core_objects_lock
4664   (p_core_object_type             => g_sba_lookup_type
4665   ,p_core_objects                 => l_sba_core_objects
4666   ,p_rowid_id_recs                => l_null_rowid_id_recs
4667   ,p_id_ovn_recs                  => l_sba_id_ovn_recs
4668   );
4669   --
4670   -- Delete the generated objects in child-first order.
4671   -- The formulas are deleted in two parts. This is because
4672   -- the formulas can be attached to input values and also the database
4673   -- items of the input values can be used in formulas.
4674   --
4675   del_bal_attributes
4676   (p_id_ovn_recs                  => l_sba_id_ovn_recs
4677   );
4678   del_gu_bal_exclusions
4679   (p_id_ovn_recs                  => l_sgb_id_ovn_recs
4680   );
4681   del_ele_type_usages
4682   (p_id_ovn_recs                  => l_seu_id_ovn_recs
4683   );
4684   del_iterative_rules
4685   (p_id_ovn_recs                  => l_sir_id_ovn_recs
4686   );
4687   del_formula_rules
4688   (p_rowid_id_recs                => l_sfr_rowid_id_recs
4689   );
4690   del_status_rules
4691   (p_rowid_id_recs                => l_spr_rowid_id_recs
4692   );
4693   del_formula_info
4694   (p_rowid_id_recs                => l_sf_rowid_id_recs
4695   );
4696   del_input_values
4697   (p_rowid_id_recs                => l_siv_rowid_id_recs
4698   );
4699   del_formulas
4700   (p_rowid_id_recs                => l_sf_rowid_id_recs
4701   );
4702   del_balance_classis
4703   (p_rowid_id_recs                => l_sbc_rowid_id_recs
4704   );
4705   del_sub_classi_rules
4706   (p_rowid_id_recs                => l_ssr_rowid_id_recs
4707   );
4708   del_element_types
4709   (p_rowid_id_recs                => l_set_rowid_id_recs
4710   );
4711   del_defined_balances
4712   (p_rowid_id_recs                => l_sdb_rowid_id_recs
4713   );
4714   del_balance_types
4715   (p_rowid_id_recs                => l_sbt_rowid_id_recs
4716   );
4717   hr_utility.set_location(l_proc, 30);
4718   if p_drop_formula_packages then
4719     drop_formula_packages
4720     (p_rowid_id_recs                => l_sf_rowid_id_recs
4721     );
4722   end if;
4723   hr_utility.set_location('Leaving:' || l_proc, 100);
4724 end zap_core_objects;
4725 --
4726 end pay_element_template_gen;