[Home] [Help]
PACKAGE BODY: APPS.PAY_ELEMENT_TEMPLATE_USER_INIT
Source
1 PACKAGE BODY pay_element_template_user_init AS
2 /* $Header: payeletmplusrini.pkb 120.4 2006/11/16 01:23:57 vpandya noship $ */
3 --
4
5 /*
6 ================================================================================
7 ******************************************************************
8 * *
9 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
10 * Chertsey, England. *
11 * *
12 * All rights reserved. *
13 * *
14 * This material has been provided pursuant to an agreement *
15 * containing restrictions on its use. The material is also *
16 * protected by copyright law. No part of this material may *
17 * be copied or distributed, transmitted or transcribed, in *
18 * any form or by any means, electronic, mechanical, magnetic, *
19 * manual, or otherwise, or disclosed to third parties without *
20 * the express written permission of Oracle Corporation UK Ltd, *
21 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
22 * England. *
23 * *
24 ******************************************************************
25
26 Description: This package is used to create earning and deduction
27 elements using Element Templates for Oracle
28 International Payroll.
29
30 Change List
31 -----------
32 Date Name Vers Bug No Description
33 ----------- ---------- ----- ------- -----------------------------------
34 12-NOV-2004 vpandya 115.0 Created.
35 01-DEC-2004 vpandya 115.1 Made p_rec as IN parameter only,
36 Also added l_rec.
37 Calling update_shadow_element.
38 12-DEC-2004 vpandya 115.2 Removed legislation rule check.
39 And calling pre and post process
40 package without checking leg rule.
41 22-DEC-2004 vpandya 115.3 Added SYSTEM to Type Object in
42 declaration section.
43 05-JAN-2005 vpandya 115.4 Calling procedure
44 ELEMENT_TEMPLATE_UPD_USER_STRU
45 That is being called after
46 User Structure generation (Shadow
47 Schema ) and before creating Core
48 Schema. Also calling
49 fnd_request.submit_request to
50 compile formula.
51 27-JAN-2005 vmehta 115.5 Removed reference to SYSTEM schema
52 from objects.
53 18-FEB-2005 ssattini 115.6 Added code to create
54 Sub-classification
55 and Frequency Rules records.
56 07-MAR-2005 ssattini 115.7 Added code to initialise
57 Frequency Rule PL/SQL table
58 used in Summary Page of Element
59 Template Wizard.
60 14-Apr-2005 vpandya 115.8,9 4300619 Setting default processing
61 priority only when
62 p_rec.processing_priority
63 is null or it not in its range.
64 28-APR-2005 pganguly 115.10 Added the delete_element
65 procedure.
66 04-MAY-2005 pganguly 115.11 Changed the procedure in
67 delete_ element.
68 01-AUG-2005 rdhingra 115.12 4518218 Removing code from the call of
69 Cursor Name: get_proc_priority
70 As decided, if the user is allowed
71 to give his own priority then
72 thats what should be carried
73 forward.
74 31-AUG-2005 vpandya 115.13 4585922 Added a cursor get_shd_formula_id
75 and compiling formulas.
76 14-Sep-2005 vpandya 115.14 Changed code for exception so that
77 it raises error if inlined pkg
78 returns any error.
79 15-Nov-2006 vpandya 115.15 5609242 Added a function
80 get_base_classification to get
81 classification name in base (US)
82 language if it entered in
83 translated/pseudo language. Also
84 used for sub classifications.
85 ================================================================================
86 */
87 --
88
89 g_package varchar2(50);
90
91
92 FUNCTION get_base_classification ( p_legislation_code IN VARCHAR2
93 ,p_business_group_id IN NUMBER
94 ,p_classification_name IN VARCHAR2 )
95 RETURN VARCHAR2 IS
96
97 CURSOR c_base_class_name ( cp_legislation_code VARCHAR2
98 ,cp_business_group_id NUMBER
99 ,cp_classification_name VARCHAR2 ) IS
100 SELECT pec.classification_id
101 ,pec.classification_name
102 ,pect.language
103 FROM pay_element_classifications_tl pect
104 ,pay_element_classifications pec
105 WHERE pect.classification_name = cp_classification_name
106 AND pec.classification_id = pect.classification_id
107 AND ( pec.legislation_code = cp_legislation_code OR
108 pec.business_group_id = cp_business_group_id )
109 ORDER BY pec.classification_id DESC;
110
111 ln_classification_id NUMBER;
112 lv_classification_name VARCHAR2(240);
113 lv_language VARCHAR2(240);
114
115 BEGIN
116
117 OPEN c_base_class_name( p_legislation_code
118 ,p_business_group_id
119 ,p_classification_name );
120
121 FETCH c_base_class_name INTO ln_classification_id
122 ,lv_classification_name
123 ,lv_language;
124 CLOSE c_base_class_name;
125
126 RETURN lv_classification_name;
127
128 END get_base_classification;
129
130 PROCEDURE create_element
131 ( p_validate IN BOOLEAN
132 ,p_save_for_later IN VARCHAR2
133 ,p_rec IN PAY_ELE_TMPLT_OBJ
134 ,p_sub_class IN PAY_ELE_SUB_CLASS_TABLE
135 ,p_freq_rule IN PAY_FREQ_RULE_TABLE
136 ,p_ele_template_id OUT NOCOPY NUMBER
137 ) IS
138
139 CURSOR get_template_id ( cp_legislation_code varchar2
140 ,cp_template_name varchar2) IS
141 SELECT template_id
142 FROM pay_element_templates
143 WHERE legislation_code = cp_legislation_code
144 AND template_name = cp_template_name;
145
146 CURSOR get_busgrp_info ( cp_business_group_name varchar2 ) IS
147 SELECT business_group_id
148 ,legislation_code
149 ,currency_code
150 FROM per_business_groups
151 WHERE name = cp_business_group_name;
152
153 CURSOR get_shd_ele_info ( cp_template_id NUMBER
154 ,cp_element_name VARCHAR2 ) IS
155 SELECT element_type_id
156 ,object_version_number
157 ,payroll_formula_id
158 FROM pay_shadow_element_types
159 WHERE template_id = cp_template_id
160 AND element_name = cp_element_name;
161
162 CURSOR get_proc_priority ( cp_legislation_code VARCHAR2
163 ,cp_classification_name VARCHAR2 ) IS
164 SELECT default_priority
165 ,default_high_priority
166 ,default_low_priority
167 FROM pay_element_classifications
168 WHERE legislation_code = cp_legislation_code
169 AND classification_name = cp_classification_name;
170
171 CURSOR get_rule_mode ( cp_legislation_code VARCHAR2 ) IS
172 SELECT rule_mode
173 FROM pay_legislation_rules
174 WHERE legislation_code = cp_legislation_code
175 AND rule_type = 'SEP_CHEQUE_IV';
176
177 CURSOR get_shd_formula_id ( cp_template_id NUMBER ) IS
178 SELECT payroll_formula_id
179 FROM pay_shadow_element_types
180 WHERE template_id = cp_template_id
181 AND payroll_formula_id IS NOT NULL;
182
183 CURSOR get_formula_info ( cp_formula_id NUMBER ) IS
184 SELECT ff.formula_name
185 ,ft.formula_type_name
186 FROM pay_shadow_formulas psf
187 ,ff_formulas_f ff
188 ,ff_formula_types ft
189 WHERE psf.formula_id = cp_formula_id
190 AND psf.formula_name = ff.formula_name
191 AND ff.formula_type_id = ft.formula_type_id;
192
193
194 -- Cursor to get the core_schema element_type_id and
195 -- shadow_schema element_type_id based on tempalte_id value
196
197 CURSOR get_core_shadow_object_id( cp_template_id number
198 ,cp_object_type varchar2) IS
199 SELECT core_object_id
200 ,shadow_object_id
201 FROM pay_template_core_objects
202 WHERE template_id = cp_template_id
203 AND core_object_type = cp_object_type;
204
205 -- Cursor to get payroll_id value based on payroll_name
206
207 CURSOR get_payroll_id( cp_bg_id number
208 ,cp_payroll_name varchar2
209 ,cp_eff_date date) IS
210 SELECT payroll_id
211 FROM pay_payrolls_f
212 WHERE business_group_id + 0 = cp_bg_id
213 AND payroll_name = cp_payroll_name
214 AND cp_eff_date between effective_start_date and effective_end_date;
215
216 ln_business_group_id NUMBER;
217 lv_legislation_code VARCHAR2(240);
218 lv_currency_code VARCHAR2(240);
219
220 l_source_template_id NUMBER;
221 l_object_version_number NUMBER;
222
223 l_proc VARCHAR2(240);
224
225 lv_rule_mode VARCHAR2(240);
226 ln_iv_exists NUMBER;
227
228 l_ele_obj_ver_number NUMBER;
229 ln_shd_ele_type_id NUMBER;
230 ln_processing_priority NUMBER;
231 ln_proc_high_priority NUMBER;
232 ln_proc_low_priority NUMBER;
233 ln_payroll_formula_id NUMBER;
234
235 ln_req_id NUMBER;
236 lv_formula_name VARCHAR2(240);
237 lv_formula_type_name VARCHAR2(240);
238
239 l_rec PAY_ELE_TMPLT_OBJ;
240
241 -- added for Sub-classifications
242 lr_sub_class pay_ele_sub_class_obj;
243 lt_sub_class PAY_ELE_SUB_CLASS_TABLE;
244
245 lr_shadow_sub_class_rec pay_ssr_shd.g_rec_type;
246 -- end of sub-classifications declare
247
248 -- added for Frequency rules
249 lr_freq_rule_rec pay_freq_rule_obj;
250 lt_freq_rule_table PAY_FREQ_RULE_TABLE;
251 ln_core_object_id NUMBER;
252 ln_shadow_object_id NUMBER;
253 ln_payroll_id NUMBER;
254 lv_period_6 VARCHAR2(1);
255
256 -- end of frequency rules declare
257
258 BEGIN -- create_element
259
260 l_rec := p_rec;
261
262 -- added initialization for sub-classifications
263 lr_sub_class := pay_ele_sub_class_obj(null);
264 lt_sub_class := p_sub_class;
265
266 lr_shadow_sub_class_rec.sub_classification_rule_id := null;
267 lr_shadow_sub_class_rec.element_type_id := null;
268 lr_shadow_sub_class_rec.element_classification := null;
269 lr_shadow_sub_class_rec.object_version_number := null;
270 lr_shadow_sub_class_rec.exclusion_rule_id := null;
271
272 -- end of initialization of sub-classification
273
274 -- added initialization for Frequency rules
275
276 lr_freq_rule_rec := pay_freq_rule_obj(null,null,null,
277 null,null,null,null,null);
278 lt_freq_rule_table := p_freq_rule;
279
280 -- end of initialization of Frequency rules
281
282 --hr_utility.trace_on(null, 'TMPLT');
283
284 l_proc := g_package || 'create_element';
285
286 hr_utility.set_location('Entering '||l_proc, 10);
287
288 OPEN get_busgrp_info(l_rec.business_group_name);
289 FETCH get_busgrp_info INTO ln_business_group_id
290 ,lv_legislation_code
291 ,lv_currency_code;
292 CLOSE get_busgrp_info;
293
294 hr_utility.trace('Entered element_classification '||
295 l_rec.element_classification);
296
297 l_rec.element_classification :=
298 get_base_classification( lv_legislation_code
299 ,ln_business_group_id
300 ,l_rec.element_classification);
301
302 hr_utility.trace('BASE element_classification '||
303 l_rec.element_classification);
304
305 IF l_rec.legislation_code IS NULL THEN
306 l_rec.legislation_code := lv_legislation_code;
307 END IF;
308
309 hr_utility.set_location('Entering '||l_proc, 20);
310
311 OPEN get_template_id ( l_rec.legislation_code, l_rec.calculation_rule );
312 FETCH get_template_id INTO l_source_template_id;
313 CLOSE get_template_id;
314
315 hr_utility.set_location(l_proc, 40);
316
317 IF l_rec.process_mode IS NOT NULL AND
318 l_rec.process_mode <> 'N' THEN
319
320 lv_rule_mode := NULL;
321
322 OPEN get_rule_mode( l_rec.legislation_code );
323 FETCH get_rule_mode INTO lv_rule_mode;
324 CLOSE get_rule_mode;
325
326 hr_utility.set_location(l_proc, 50);
327
328 IF lv_rule_mode IS NOT NULL THEN
329
330 SELECT COUNT(*)
331 INTO ln_iv_exists
332 FROM pay_shadow_element_types pset
333 ,pay_shadow_input_values psiv
334 WHERE pset.template_id = l_source_template_id
335 AND psiv.element_type_id = pset.element_type_id
336 AND psiv.name = lv_rule_mode;
337
338 IF ln_iv_exists > 0 THEN
339
340 IF l_rec.process_mode = 'S' THEN
341
342 l_rec.configuration_information1 := 'Y';
343 l_rec.configuration_information2 := 'Y';
344 l_rec.configuration_information3 := 'Y';
345
346 ELSIF l_rec.process_mode = 'P' THEN
347
348 l_rec.configuration_information1 := 'Y';
349 l_rec.configuration_information2 := 'N';
350 l_rec.configuration_information3 := 'Y';
351
352 END IF;
353
354 hr_utility.set_location(l_proc, 60);
355
356 END IF;
357
358 END IF;
359
360 END IF;
361
362 /**********************************************************************
363 ** Cursor Name: get_proc_priority
364 ** Purpose : This gets default priority, default high priority
365 ** and default low priority of the element classification.
366 ** Using entered priority if it is not null
367 **********************************************************************/
368
369
373 ,ln_proc_high_priority
370 OPEN get_proc_priority( l_rec.legislation_code
371 , l_rec.element_classification);
372 FETCH get_proc_priority INTO ln_processing_priority
374 ,ln_proc_low_priority;
375 CLOSE get_proc_priority;
376
377
378 IF p_rec.processing_priority is not null THEN
379
380 ln_processing_priority := p_rec.processing_priority;
381
382 END IF;
383
384 hr_utility.set_location(l_proc, 70);
385
386 BEGIN
387
388 EXECUTE IMMEDIATE 'BEGIN :a := PAY_'||l_rec.legislation_code||
389 '_RULES.ELEMENT_TEMPLATE_PRE_PROCESS(:b); END;'
390 USING OUT l_rec, IN l_rec;
391
392 hr_utility.trace('l_rec.configuration_information1 ' ||
393 l_rec.configuration_information1);
394 hr_utility.trace('l_rec.configuration_information4 ' ||
395 l_rec.configuration_information4);
396 hr_utility.trace('l_rec.configuration_information5 ' ||
397 l_rec.configuration_information5);
398 hr_utility.trace('l_rec.configuration_information6 ' ||
399 l_rec.configuration_information6);
400 hr_utility.trace('l_rec.configuration_information7 ' ||
401 l_rec.configuration_information7);
402
403 EXCEPTION
404 WHEN Cannot_Find_Prog_Unit THEN
405 null;
406 WHEN others THEN
407 raise;
408
409 END;
410
411 hr_utility.set_location(l_proc, 80);
412
413 pay_element_template_api.create_user_structure
414 (p_validate => p_validate
415 ,p_effective_date => l_rec.effective_date
416 ,p_business_group_id => ln_business_group_id
417 ,p_source_template_id => l_source_template_id
418 ,p_base_name => l_rec.element_name
419 ,p_base_processing_priority => ln_processing_priority
420 ,p_preference_info_category => l_rec.preference_info_category
421 ,p_preference_information1 => l_rec.preference_information1
422 ,p_preference_information2 => l_rec.preference_information2
423 ,p_preference_information3 => l_rec.preference_information3
424 ,p_preference_information4 => l_rec.preference_information4
425 ,p_preference_information5 => l_rec.preference_information5
426 ,p_preference_information6 => l_rec.preference_information6
427 ,p_preference_information7 => l_rec.preference_information7
428 ,p_preference_information8 => l_rec.preference_information8
429 ,p_preference_information9 => l_rec.preference_information9
430 ,p_preference_information10 => l_rec.preference_information10
431 ,p_preference_information11 => l_rec.preference_information11
432 ,p_preference_information12 => l_rec.preference_information12
433 ,p_preference_information13 => l_rec.preference_information13
434 ,p_preference_information14 => l_rec.preference_information14
435 ,p_preference_information15 => l_rec.preference_information15
436 ,p_preference_information16 => l_rec.preference_information16
437 ,p_preference_information17 => l_rec.preference_information17
438 ,p_preference_information18 => l_rec.preference_information18
439 ,p_preference_information19 => l_rec.preference_information19
440 ,p_preference_information20 => l_rec.preference_information20
441 ,p_preference_information21 => l_rec.preference_information21
442 ,p_preference_information22 => l_rec.preference_information22
443 ,p_preference_information23 => l_rec.preference_information23
444 ,p_preference_information24 => l_rec.preference_information24
445 ,p_preference_information25 => l_rec.preference_information25
446 ,p_preference_information26 => l_rec.preference_information26
447 ,p_preference_information27 => l_rec.preference_information27
448 ,p_preference_information28 => l_rec.preference_information28
449 ,p_preference_information29 => l_rec.preference_information29
450 ,p_preference_information30 => l_rec.preference_information30
451 ,p_configuration_info_category => l_rec.configuration_info_category
452 ,p_configuration_information1 => l_rec.configuration_information1
453 ,p_configuration_information2 => l_rec.configuration_information2
454 ,p_configuration_information3 => l_rec.configuration_information3
455 ,p_configuration_information4 => l_rec.configuration_information4
456 ,p_configuration_information5 => l_rec.configuration_information5
457 ,p_configuration_information6 => l_rec.configuration_information6
458 ,p_configuration_information7 => l_rec.configuration_information7
459 ,p_configuration_information8 => l_rec.configuration_information8
460 ,p_configuration_information9 => l_rec.configuration_information9
461 ,p_configuration_information10 => l_rec.configuration_information10
462 ,p_configuration_information11 => l_rec.configuration_information11
463 ,p_configuration_information12 => l_rec.configuration_information12
464 ,p_configuration_information13 => l_rec.configuration_information13
465 ,p_configuration_information14 => l_rec.configuration_information14
466 ,p_configuration_information15 => l_rec.configuration_information15
467 ,p_configuration_information16 => l_rec.configuration_information16
468 ,p_configuration_information17 => l_rec.configuration_information17
469 ,p_configuration_information18 => l_rec.configuration_information18
470 ,p_configuration_information19 => l_rec.configuration_information19
474 ,p_configuration_information23 => l_rec.configuration_information23
471 ,p_configuration_information20 => l_rec.configuration_information20
472 ,p_configuration_information21 => l_rec.configuration_information21
473 ,p_configuration_information22 => l_rec.configuration_information22
475 ,p_configuration_information24 => l_rec.configuration_information24
476 ,p_configuration_information25 => l_rec.configuration_information25
477 ,p_configuration_information26 => l_rec.configuration_information26
478 ,p_configuration_information27 => l_rec.configuration_information27
479 ,p_configuration_information28 => l_rec.configuration_information28
480 ,p_configuration_information29 => l_rec.configuration_information29
481 ,p_configuration_information30 => l_rec.configuration_information30
482 ,p_template_id => p_ele_template_id
483 ,p_object_version_number => l_object_version_number
484 );
485
486 hr_utility.set_location(l_proc, 50);
487
488 OPEN get_shd_ele_info( p_ele_template_id, l_rec.element_name );
489 FETCH get_shd_ele_info INTO ln_shd_ele_type_id
490 ,l_ele_obj_ver_number
491 ,ln_payroll_formula_id;
492 CLOSE get_shd_ele_info;
493
494 IF ln_shd_ele_type_id IS NOT NULL THEN
495
496 hr_utility.trace('Sub-class reccount='||to_char(lt_sub_class.count));
497
498 IF lt_sub_class.count > 0 THEN
499
500 FOR j IN lt_sub_class.FIRST..lt_sub_class.LAST LOOP
501
502 lr_sub_class := lt_sub_class(j);
503
504 IF lr_sub_class.name IS NOT NULL THEN
505
506 hr_utility.trace('Sub-class rec-'||to_char(j)||
507 '='||lr_sub_class.name);
508
509 hr_utility.trace('Entered SUB element_classification '||
510 lr_sub_class.name);
511
512 lr_sub_class.name :=
513 get_base_classification( lv_legislation_code
514 ,ln_business_group_id
515 ,lr_sub_class.name);
516
517 hr_utility.trace('BASE SUB element_classification '||
518 lr_sub_class.name);
519
520 lr_shadow_sub_class_rec.sub_classification_rule_id := null;
521 lr_shadow_sub_class_rec.element_type_id := ln_shd_ele_type_id;
522 lr_shadow_sub_class_rec.element_classification :=
523 lr_sub_class.name;
524 lr_shadow_sub_class_rec.object_version_number := null;
525 lr_shadow_sub_class_rec.exclusion_rule_id := null;
526
527 pay_ssr_ins.ins(l_rec.effective_date, lr_shadow_sub_class_rec);
528
529 ELSIf lr_sub_class.name IS NULL THEN
530
531 hr_utility.trace('Exit from Sub-class loop');
532 exit;
533
534 END IF; -- lr_sub_class.name is not null
535
536 END LOOP;
537
538 END IF;
539
540 END IF; --ln_shd_ele_type_id is not null
541
542 /* End of Sub-classification rules creation */
543
544 hr_utility.set_location(l_proc, 60);
545
546 IF p_validate <> TRUE THEN
547
548 pay_shadow_element_api.update_shadow_element
549 (p_validate => p_validate
550 ,p_effective_date => l_rec.effective_date
551 ,p_element_type_id => ln_shd_ele_type_id
552 ,p_reporting_name => l_rec.reporting_name
553 ,p_description => l_rec.element_description
554 ,p_classification_name => l_rec.element_classification
555 ,p_post_termination_rule => l_rec.termination_rule
556 ,p_standard_link_flag => l_rec.standard_link
557 ,p_processing_type => l_rec.processing_type
558 ,p_once_each_period_flag => l_rec.proc_once_pay_period
559 ,p_process_mode => l_rec.process_mode
560 ,p_input_currency_code => l_rec.input_currency_code
561 ,p_output_currency_code => lv_currency_code
562 ,p_multiple_entries_allowed_fla => l_rec.multiple_entries_allowed
563 ,p_object_version_number => l_ele_obj_ver_number);
564
565 hr_utility.set_location(l_proc, 70);
566
567 BEGIN
568
569 EXECUTE IMMEDIATE 'BEGIN PAY_'||l_rec.legislation_code||
570 '_RULES.ELEMENT_TEMPLATE_UPD_USER_STRU(:c); END;'
571 USING p_ele_template_id;
572
573 hr_utility.set_location(l_proc, 100);
574
575 EXCEPTION
576 WHEN Cannot_Find_Prog_Unit THEN
577 null;
578 WHEN others THEN
579 raise;
580
581 END;
582
583 IF p_save_for_later <> 'Y' THEN
584
585 pay_element_template_api.generate_part1
586 (p_validate => FALSE
587 ,p_effective_date => l_rec.effective_date
588 ,p_hr_only => FALSE
589 ,p_hr_to_payroll => FALSE
590 ,p_template_id => p_ele_template_id);
591
592 hr_utility.set_location(l_proc, 80);
593
594 pay_element_template_api.generate_part2
595 (p_validate => FALSE
596 ,p_effective_date => l_rec.effective_date
597 ,p_template_id => p_ele_template_id);
598
602 LOOP
599 hr_utility.set_location(l_proc, 90);
600
601 FOR formula in get_shd_formula_id( p_ele_template_id )
603
604 OPEN get_formula_info(formula.payroll_formula_id);
605 FETCH get_formula_info INTO lv_formula_name
606 ,lv_formula_type_name;
607 CLOSE get_formula_info;
608
609 ln_req_id := fnd_request.submit_request(
610 application => 'FF',
611 program => 'SINGLECOMPILE',
612 argument1 => lv_formula_type_name,
613 argument2 => lv_formula_name);
614
615
616 END LOOP;
617
618 END IF;
619
620
621 BEGIN
622
623 EXECUTE IMMEDIATE 'BEGIN PAY_'||l_rec.legislation_code||
624 '_RULES.ELEMENT_TEMPLATE_POST_PROCESS(:c); END;'
625 USING p_ele_template_id;
626
627 hr_utility.set_location(l_proc, 100);
628
629 EXCEPTION
630 WHEN Cannot_Find_Prog_Unit THEN
631 null;
632 WHEN others THEN
633 raise;
634
635 END;
636
637 /* Added the Frequency Rule creation in core schema */
638
639 hr_utility.trace('Start of Frequency Rule Creation ');
640
641 OPEN get_core_shadow_object_id(p_ele_template_id,'ET');
642 FETCH get_core_shadow_object_id INTO ln_core_object_id,
643 ln_shadow_object_id;
644 CLOSE get_core_shadow_object_id;
645
646 lv_period_6 := 'N';
647 hr_utility.trace('ln_core_object_id :'||to_char(ln_core_object_id));
648
649 IF ln_core_object_id IS NOT NULL AND
650 lt_freq_rule_table.count > 0 THEN
651
652 hr_utility.trace('Freq Rule Rec Count :'||
653 to_char(lt_freq_rule_table.count));
654 hr_utility.trace('Start of Freq Rule Recs Loop');
655
656 FOR k IN lt_freq_rule_table.FIRST..lt_freq_rule_table.LAST
657 LOOP
658
659 lr_freq_rule_rec := pay_freq_rule_obj(null,null,null,
660 null,null,null,null,null);
661 lr_freq_rule_rec := lt_freq_rule_table(k);
662
663 hr_utility.trace('Freq Rule Rec :'||to_char(k));
664 hr_utility.trace('Period 1 : '||lr_freq_rule_rec.period_1);
665 hr_utility.trace('Period 2 : '||lr_freq_rule_rec.period_2);
666 hr_utility.trace('Period 3 : '||lr_freq_rule_rec.period_3);
667 hr_utility.trace('Period 4 : '||lr_freq_rule_rec.period_4);
668 hr_utility.trace('Period 5 : '||lr_freq_rule_rec.period_5);
669 hr_utility.trace('RuleDateCode : '||lr_freq_rule_rec.date_option);
670
671 IF lr_freq_rule_rec.payroll IS NOT NULL THEN
672
673 OPEN get_payroll_id(ln_business_group_id
674 ,lr_freq_rule_rec.payroll
675 ,l_rec.effective_date);
676
677 FETCH get_payroll_id INTO ln_payroll_id;
678 CLOSE get_payroll_id;
679
680 hr_utility.trace('Payroll Id :'||to_char(ln_payroll_id));
681
682 pay_pyepfreq_pkg.hr_ele_pay_freq_rules
683 (p_context => 'ON-UPDATE',
684 p_eletype_id => ln_core_object_id,
685 p_payroll_id => ln_payroll_id,
686 p_period_type => lr_freq_rule_rec.period_type,
687 p_bg_id => ln_business_group_id,
688 p_period_1 => lr_freq_rule_rec.period_1,
689 p_period_2 => lr_freq_rule_rec.period_2,
690 p_period_3 => lr_freq_rule_rec.period_3,
691 p_period_4 => lr_freq_rule_rec.period_4,
692 p_period_5 => lr_freq_rule_rec.period_5,
693 p_period_6 => lv_period_6,
694 p_eff_date => l_rec.effective_date,
695 p_rule_date_code => lr_freq_rule_rec.date_option,
696 p_leg_code => null);
697
698 END IF; -- lr_freq_rule_rec.payroll is not null
699
700 END LOOP;
701
702 hr_utility.trace('End of Freq Rule Recs Loop');
703
704 /* Initialize Frequency Rule Plsql table that is used in
705 Summary Page of Element Template Wizard. */
706
707 pay_pyepfreq_pkg.initialise_freqrule_table;
708
709 END IF; -- ln_core_object_id is not null
710
711 /* End of Frequency Rule creation in core schema */
712
713 END IF;
714
715 hr_utility.set_location('Leaving '||l_proc, 1000);
716
717 END create_element;
718
719
720 PROCEDURE delete_element
721 ( p_validate IN BOOLEAN,
722 p_template_id IN NUMBER
723 ) IS
724
725 l_proc VARCHAR2(240);
726 l_legislation_code varchar2(30);
727
728 cursor cur_leg_code IS
729 SELECT
730 pbg.legislation_code
731 FROM
732 per_business_groups pbg,
733 pay_element_templates pet
734 WHERE
735 pet.template_id = p_template_id AND
736 pet.business_group_id = pbg.business_group_id;
737
738 BEGIN
739
740 l_proc := 'delete_element';
741
742 hr_utility.set_location(l_proc, 10);
743
744 OPEN cur_leg_code;
748
745 FETCH cur_leg_code
746 INTO l_legislation_code;
747 CLOSE cur_leg_code;
749 hr_utility.set_location(l_proc, 20);
750
751 BEGIN
752 EXECUTE IMMEDIATE 'BEGIN PAY_'||l_legislation_code||
753 '_RULES.DELETE_PRE_PROCESS(:c); END;'
754 USING p_template_id;
755
756 hr_utility.set_location(l_proc, 30);
757
758 EXCEPTION
759 WHEN Cannot_Find_Prog_Unit THEN
760 null;
761 WHEN others THEN
762 raise;
763
764 END;
765
766 pay_element_template_api.delete_user_structure
767 (p_validate => p_validate,
768 p_drop_formula_packages => TRUE,
769 p_template_id => p_template_id);
770
771 BEGIN
772
773 EXECUTE IMMEDIATE 'BEGIN PAY_'||l_legislation_code||
774 '_RULES.DELETE_POST_PROCESS(:c); END;'
775 USING p_template_id;
776
777 hr_utility.set_location(l_proc, 50);
778
779 EXCEPTION
780 WHEN Cannot_Find_Prog_Unit THEN
781 null;
782 WHEN others THEN
783 raise;
784
785 END;
786
787 END delete_element;
788
789 BEGIN
790
791 g_package := 'pay_element_template_user_init.';
792
793 END pay_element_template_user_init;