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