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