[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.12020000.5 2012/07/04 19:34:42 amnaraya ship $ */
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 12-Dec-2011 vvijayku 115.16 13362720 Added new code to update the element
86 information category for an element
87 being created through an Element Design
88 wizard.
89 12-Dec-2011 vvijayku 115.17 13362720 Corrected the code added.
90 21-Dec-2011 vvijayku 115.18 13362720 Added new code to automatically update
91 the Primary Balance id and the Hourly
92 balance id in the columns Element_Information10
93 and Element_Information12 respectively.
94 05-Mar-2012 vvijayku 120.4.12010000.4
95 13772336 Adding new code to insert the Arrears and
96 Accruals Balance Id in Element_InformationXX
97 when a Deduction type element is created for
98 International Payroll product.
99 ================================================================================
100 */
101 --
102
103 g_package varchar2(50);
104
105
106 FUNCTION get_base_classification ( p_legislation_code IN VARCHAR2
107 ,p_business_group_id IN NUMBER
108 ,p_classification_name IN VARCHAR2 )
109 RETURN VARCHAR2 IS
110
111 CURSOR c_base_class_name ( cp_legislation_code VARCHAR2
112 ,cp_business_group_id NUMBER
113 ,cp_classification_name VARCHAR2 ) IS
114 SELECT pec.classification_id
115 ,pec.classification_name
116 ,pect.language
117 FROM pay_element_classifications_tl pect
118 ,pay_element_classifications pec
119 WHERE pect.classification_name = cp_classification_name
120 AND pec.classification_id = pect.classification_id
121 AND ( pec.legislation_code = cp_legislation_code OR
122 pec.business_group_id = cp_business_group_id )
123 ORDER BY pec.classification_id DESC;
124
125 ln_classification_id NUMBER;
126 lv_classification_name VARCHAR2(240);
127 lv_language VARCHAR2(240);
128
129 BEGIN
130
131 OPEN c_base_class_name( p_legislation_code
132 ,p_business_group_id
133 ,p_classification_name );
134
135 FETCH c_base_class_name INTO ln_classification_id
136 ,lv_classification_name
137 ,lv_language;
138 CLOSE c_base_class_name;
139
140 RETURN lv_classification_name;
141
142 END get_base_classification;
143
144 PROCEDURE create_element
145 ( p_validate IN BOOLEAN
146 ,p_save_for_later IN VARCHAR2
147 ,p_rec IN PAY_ELE_TMPLT_OBJ
148 ,p_sub_class IN PAY_ELE_SUB_CLASS_TABLE
149 ,p_freq_rule IN PAY_FREQ_RULE_TABLE
150 ,p_ele_template_id OUT NOCOPY NUMBER
151 ) IS
152
153 CURSOR get_template_id ( cp_legislation_code varchar2
154 ,cp_template_name varchar2) IS
155 SELECT template_id
156 FROM pay_element_templates
157 WHERE legislation_code = cp_legislation_code
158 AND template_name = cp_template_name;
159
160 CURSOR get_busgrp_info ( cp_business_group_name varchar2 ) IS
161 SELECT business_group_id
162 ,legislation_code
163 ,currency_code
164 FROM per_business_groups
165 WHERE name = cp_business_group_name;
166
167 CURSOR get_shd_ele_info ( cp_template_id NUMBER
168 ,cp_element_name VARCHAR2 ) IS
169 SELECT element_type_id
170 ,object_version_number
171 ,payroll_formula_id
172 FROM pay_shadow_element_types
173 WHERE template_id = cp_template_id
174 AND element_name = cp_element_name;
175
176 CURSOR get_proc_priority ( cp_legislation_code VARCHAR2
177 ,cp_classification_name VARCHAR2 ) IS
178 SELECT default_priority
179 ,default_high_priority
180 ,default_low_priority
181 FROM pay_element_classifications
182 WHERE legislation_code = cp_legislation_code
183 AND classification_name = cp_classification_name;
184
185 CURSOR get_rule_mode ( cp_legislation_code VARCHAR2 ) IS
186 SELECT rule_mode
187 FROM pay_legislation_rules
188 WHERE legislation_code = cp_legislation_code
189 AND rule_type = 'SEP_CHEQUE_IV';
190
191 CURSOR get_shd_formula_id ( cp_template_id NUMBER ) IS
192 SELECT payroll_formula_id
193 FROM pay_shadow_element_types
194 WHERE template_id = cp_template_id
195 AND payroll_formula_id IS NOT NULL;
196
197 CURSOR get_formula_info ( cp_formula_id NUMBER ) IS
198 SELECT ff.formula_name
199 ,ft.formula_type_name
200 FROM pay_shadow_formulas psf
201 ,ff_formulas_f ff
202 ,ff_formula_types ft
203 WHERE psf.formula_id = cp_formula_id
204 AND psf.formula_name = ff.formula_name
205 AND ff.formula_type_id = ft.formula_type_id;
206
207
208 -- Cursor to get the core_schema element_type_id and
209 -- shadow_schema element_type_id based on tempalte_id value
210
211 CURSOR get_core_shadow_object_id( cp_template_id number
212 ,cp_object_type varchar2) IS
213 SELECT core_object_id
214 ,shadow_object_id
215 FROM pay_template_core_objects
216 WHERE template_id = cp_template_id
217 AND core_object_type = cp_object_type;
218
219 -- Cursor to get payroll_id value based on payroll_name
220
221 CURSOR get_payroll_id( cp_bg_id number
222 ,cp_payroll_name varchar2
223 ,cp_eff_date date) IS
224 SELECT payroll_id
225 FROM pay_payrolls_f
226 WHERE business_group_id + 0 = cp_bg_id
227 AND payroll_name = cp_payroll_name
228 AND cp_eff_date between effective_start_date and effective_end_date;
229
230 -- Cursor to get element type id value based on element name and business group id
231
232 CURSOR get_element_type_id (cp_element_name VARCHAR2,
233 cp_business_grp_id NUMBER) IS
234 SELECT pet.element_type_id,
235 upper(pec.classification_name)
236 FROM pay_element_types_f pet,
237 pay_element_classifications pec
238 WHERE pet.element_name = cp_element_name
239 AND pet.business_group_id = cp_business_grp_id
240 AND pec.classification_id = pet.classification_id;
241
242 -- Cursor to get primary balance id value based on shadow element id
243
244 CURSOR get_primary_balance_id (cp_element_id NUMBER) IS
245 SELECT fnd_number.number_to_canonical(pbt.balance_type_id)
246 FROM pay_balance_types pbt,
247 pay_element_types_f pet
248 WHERE pet.element_type_id = cp_element_id
249 AND pbt.balance_name = pet.element_name
250 AND pbt.business_group_id = pet.business_group_id;
251
252 -- Cursor to get hourly balance id value based on shadow element id
253
254 CURSOR get_hourly_balance_id (cp_element_id NUMBER) IS
255 SELECT fnd_number.number_to_canonical(pbt.balance_type_id)
256 FROM pay_balance_types pbt,
257 pay_element_types_f pet
258 WHERE pet.element_type_id = cp_element_id
259 AND pbt.balance_name = pet.element_name||' Hours'
260 AND pbt.business_group_id = pet.business_group_id;
261
262 -- Cursor to get arrears balance id value based on shadow element id
263
264 CURSOR get_arrears_balance_id (cp_element_id NUMBER) IS
265 SELECT fnd_number.number_to_canonical(pbt.balance_type_id)
266 FROM pay_balance_types pbt,
267 pay_element_types_f pet
268 WHERE pet.element_type_id = cp_element_id
269 AND pbt.balance_name = pet.element_name||' Arrears'
270 AND pbt.business_group_id = pet.business_group_id;
271
272 -- Cursor to get accruals balance id value based on shadow element id
273
274 CURSOR get_accruals_balance_id (cp_element_id NUMBER) IS
275 SELECT fnd_number.number_to_canonical(pbt.balance_type_id)
276 FROM pay_balance_types pbt,
277 pay_element_types_f pet
278 WHERE pet.element_type_id = cp_element_id
279 AND pbt.balance_name = pet.element_name||' Accrued'
280 AND pbt.business_group_id = pet.business_group_id;
281
282 ln_business_group_id NUMBER;
283 lv_legislation_code VARCHAR2(240);
284 lv_currency_code VARCHAR2(240);
285
286 l_source_template_id NUMBER;
287 l_object_version_number NUMBER;
288
289 l_proc VARCHAR2(240);
290
291 lv_rule_mode VARCHAR2(240);
292 ln_iv_exists NUMBER;
293
294 l_ele_obj_ver_number NUMBER;
295 ln_shd_ele_type_id NUMBER;
296 ln_processing_priority NUMBER;
297 ln_proc_high_priority NUMBER;
298 ln_proc_low_priority NUMBER;
299 ln_payroll_formula_id NUMBER;
300 lv_information_category VARCHAR2(240) := NULL;
301 lv_ip_installed VARCHAR2(10);
302 ln_element_type_id NUMBER;
303 lv_primary_bal_id VARCHAR2(240) := NULL;
304 lv_hourly_bal_id VARCHAR2(240) := NULL;
305 lv_arrears_bal_id VARCHAR2(240) := NULL;
306 lv_accruals_bal_id VARCHAR2(240) := NULL;
307 lv_classification_name VARCHAR2(240);
308
309 ln_req_id NUMBER;
310 lv_formula_name VARCHAR2(240);
311 lv_formula_type_name VARCHAR2(240);
312
313 l_rec PAY_ELE_TMPLT_OBJ;
314
315 -- added for Sub-classifications
316 lr_sub_class pay_ele_sub_class_obj;
317 lt_sub_class PAY_ELE_SUB_CLASS_TABLE;
318
319 lr_shadow_sub_class_rec pay_ssr_shd.g_rec_type;
320 -- end of sub-classifications declare
321
322 -- added for Frequency rules
323 lr_freq_rule_rec pay_freq_rule_obj;
324 lt_freq_rule_table PAY_FREQ_RULE_TABLE;
325 ln_core_object_id NUMBER;
326 ln_shadow_object_id NUMBER;
327 ln_payroll_id NUMBER;
328 lv_period_6 VARCHAR2(1);
329
330 -- end of frequency rules declare
331
332 BEGIN -- create_element
333
334 l_rec := p_rec;
335
336 -- added initialization for sub-classifications
337 lr_sub_class := pay_ele_sub_class_obj(null);
338 lt_sub_class := p_sub_class;
339
340 lr_shadow_sub_class_rec.sub_classification_rule_id := null;
341 lr_shadow_sub_class_rec.element_type_id := null;
342 lr_shadow_sub_class_rec.element_classification := null;
343 lr_shadow_sub_class_rec.object_version_number := null;
344 lr_shadow_sub_class_rec.exclusion_rule_id := null;
345
346 -- end of initialization of sub-classification
347
348 -- added initialization for Frequency rules
349
350 lr_freq_rule_rec := pay_freq_rule_obj(null,null,null,
351 null,null,null,null,null);
352 lt_freq_rule_table := p_freq_rule;
353
354 -- end of initialization of Frequency rules
355
356 --hr_utility.trace_on(null, 'TMPLT');
357
358 l_proc := g_package || 'create_element';
359
360 hr_utility.set_location('Entering '||l_proc, 10);
361
362 OPEN get_busgrp_info(l_rec.business_group_name);
363 FETCH get_busgrp_info INTO ln_business_group_id
364 ,lv_legislation_code
365 ,lv_currency_code;
366 CLOSE get_busgrp_info;
367
368 hr_utility.trace('Entered element_classification '||
369 l_rec.element_classification);
370
371 l_rec.element_classification :=
372 get_base_classification( lv_legislation_code
373 ,ln_business_group_id
374 ,l_rec.element_classification);
375
376 hr_utility.trace('BASE element_classification '||
377 l_rec.element_classification);
378
379 IF l_rec.legislation_code IS NULL THEN
380 l_rec.legislation_code := lv_legislation_code;
381 END IF;
382
383 hr_utility.set_location('Entering '||l_proc, 20);
384
385 OPEN get_template_id ( l_rec.legislation_code, l_rec.calculation_rule );
386 FETCH get_template_id INTO l_source_template_id;
387 CLOSE get_template_id;
388
389 hr_utility.set_location(l_proc, 40);
390
391 IF l_rec.process_mode IS NOT NULL AND
392 l_rec.process_mode <> 'N' THEN
393
394 lv_rule_mode := NULL;
395
396 OPEN get_rule_mode( l_rec.legislation_code );
397 FETCH get_rule_mode INTO lv_rule_mode;
398 CLOSE get_rule_mode;
399
400 hr_utility.set_location(l_proc, 50);
401
402 IF lv_rule_mode IS NOT NULL THEN
403
404 SELECT COUNT(*)
405 INTO ln_iv_exists
406 FROM pay_shadow_element_types pset
407 ,pay_shadow_input_values psiv
408 WHERE pset.template_id = l_source_template_id
409 AND psiv.element_type_id = pset.element_type_id
410 AND psiv.name = lv_rule_mode;
411
412 IF ln_iv_exists > 0 THEN
413
414 IF l_rec.process_mode = 'S' THEN
415
416 l_rec.configuration_information1 := 'Y';
417 l_rec.configuration_information2 := 'Y';
418 l_rec.configuration_information3 := 'Y';
419
420 ELSIF l_rec.process_mode = 'P' THEN
421
422 l_rec.configuration_information1 := 'Y';
423 l_rec.configuration_information2 := 'N';
424 l_rec.configuration_information3 := 'Y';
425
426 END IF;
427
428 hr_utility.set_location(l_proc, 60);
429
430 END IF;
431
432 END IF;
433
434 END IF;
435
436 /**********************************************************************
437 ** Cursor Name: get_proc_priority
438 ** Purpose : This gets default priority, default high priority
439 ** and default low priority of the element classification.
440 ** Using entered priority if it is not null
441 **********************************************************************/
442
443
444 OPEN get_proc_priority( l_rec.legislation_code
445 , l_rec.element_classification);
446 FETCH get_proc_priority INTO ln_processing_priority
447 ,ln_proc_high_priority
448 ,ln_proc_low_priority;
449 CLOSE get_proc_priority;
450
451
452 IF p_rec.processing_priority is not null THEN
453
454 ln_processing_priority := p_rec.processing_priority;
455
456 END IF;
457
458 hr_utility.set_location(l_proc, 70);
459
460 BEGIN
461
462 EXECUTE IMMEDIATE 'BEGIN :a := PAY_'||l_rec.legislation_code||
463 '_RULES.ELEMENT_TEMPLATE_PRE_PROCESS(:b); END;'
464 USING OUT l_rec, IN l_rec;
465
466 hr_utility.trace('l_rec.configuration_information1 ' ||
467 l_rec.configuration_information1);
468 hr_utility.trace('l_rec.configuration_information4 ' ||
469 l_rec.configuration_information4);
470 hr_utility.trace('l_rec.configuration_information5 ' ||
471 l_rec.configuration_information5);
472 hr_utility.trace('l_rec.configuration_information6 ' ||
473 l_rec.configuration_information6);
474 hr_utility.trace('l_rec.configuration_information7 ' ||
475 l_rec.configuration_information7);
476
477 EXCEPTION
478 WHEN Cannot_Find_Prog_Unit THEN
479 null;
480 WHEN others THEN
481 raise;
482
483 END;
484
485 hr_utility.set_location(l_proc, 80);
486
487 pay_element_template_api.create_user_structure
488 (p_validate => p_validate
489 ,p_effective_date => l_rec.effective_date
490 ,p_business_group_id => ln_business_group_id
491 ,p_source_template_id => l_source_template_id
492 ,p_base_name => l_rec.element_name
493 ,p_base_processing_priority => ln_processing_priority
494 ,p_preference_info_category => l_rec.preference_info_category
495 ,p_preference_information1 => l_rec.preference_information1
496 ,p_preference_information2 => l_rec.preference_information2
497 ,p_preference_information3 => l_rec.preference_information3
498 ,p_preference_information4 => l_rec.preference_information4
499 ,p_preference_information5 => l_rec.preference_information5
500 ,p_preference_information6 => l_rec.preference_information6
501 ,p_preference_information7 => l_rec.preference_information7
502 ,p_preference_information8 => l_rec.preference_information8
503 ,p_preference_information9 => l_rec.preference_information9
504 ,p_preference_information10 => l_rec.preference_information10
505 ,p_preference_information11 => l_rec.preference_information11
506 ,p_preference_information12 => l_rec.preference_information12
507 ,p_preference_information13 => l_rec.preference_information13
508 ,p_preference_information14 => l_rec.preference_information14
509 ,p_preference_information15 => l_rec.preference_information15
510 ,p_preference_information16 => l_rec.preference_information16
511 ,p_preference_information17 => l_rec.preference_information17
512 ,p_preference_information18 => l_rec.preference_information18
513 ,p_preference_information19 => l_rec.preference_information19
514 ,p_preference_information20 => l_rec.preference_information20
515 ,p_preference_information21 => l_rec.preference_information21
516 ,p_preference_information22 => l_rec.preference_information22
517 ,p_preference_information23 => l_rec.preference_information23
518 ,p_preference_information24 => l_rec.preference_information24
519 ,p_preference_information25 => l_rec.preference_information25
520 ,p_preference_information26 => l_rec.preference_information26
521 ,p_preference_information27 => l_rec.preference_information27
522 ,p_preference_information28 => l_rec.preference_information28
523 ,p_preference_information29 => l_rec.preference_information29
524 ,p_preference_information30 => l_rec.preference_information30
525 ,p_configuration_info_category => l_rec.configuration_info_category
526 ,p_configuration_information1 => l_rec.configuration_information1
527 ,p_configuration_information2 => l_rec.configuration_information2
528 ,p_configuration_information3 => l_rec.configuration_information3
529 ,p_configuration_information4 => l_rec.configuration_information4
530 ,p_configuration_information5 => l_rec.configuration_information5
531 ,p_configuration_information6 => l_rec.configuration_information6
532 ,p_configuration_information7 => l_rec.configuration_information7
533 ,p_configuration_information8 => l_rec.configuration_information8
534 ,p_configuration_information9 => l_rec.configuration_information9
535 ,p_configuration_information10 => l_rec.configuration_information10
536 ,p_configuration_information11 => l_rec.configuration_information11
537 ,p_configuration_information12 => l_rec.configuration_information12
538 ,p_configuration_information13 => l_rec.configuration_information13
539 ,p_configuration_information14 => l_rec.configuration_information14
540 ,p_configuration_information15 => l_rec.configuration_information15
541 ,p_configuration_information16 => l_rec.configuration_information16
542 ,p_configuration_information17 => l_rec.configuration_information17
543 ,p_configuration_information18 => l_rec.configuration_information18
544 ,p_configuration_information19 => l_rec.configuration_information19
545 ,p_configuration_information20 => l_rec.configuration_information20
546 ,p_configuration_information21 => l_rec.configuration_information21
547 ,p_configuration_information22 => l_rec.configuration_information22
548 ,p_configuration_information23 => l_rec.configuration_information23
549 ,p_configuration_information24 => l_rec.configuration_information24
550 ,p_configuration_information25 => l_rec.configuration_information25
551 ,p_configuration_information26 => l_rec.configuration_information26
552 ,p_configuration_information27 => l_rec.configuration_information27
553 ,p_configuration_information28 => l_rec.configuration_information28
554 ,p_configuration_information29 => l_rec.configuration_information29
555 ,p_configuration_information30 => l_rec.configuration_information30
556 ,p_template_id => p_ele_template_id
557 ,p_object_version_number => l_object_version_number
558 );
559
560 hr_utility.set_location(l_proc, 50);
561
562 OPEN get_shd_ele_info( p_ele_template_id, l_rec.element_name );
563 FETCH get_shd_ele_info INTO ln_shd_ele_type_id
564 ,l_ele_obj_ver_number
565 ,ln_payroll_formula_id;
566 CLOSE get_shd_ele_info;
567
568 IF ln_shd_ele_type_id IS NOT NULL THEN
569
570 hr_utility.trace('Sub-class reccount='||to_char(lt_sub_class.count));
571
572 IF lt_sub_class.count > 0 THEN
573
574 FOR j IN lt_sub_class.FIRST..lt_sub_class.LAST LOOP
575
576 lr_sub_class := lt_sub_class(j);
577
578 IF lr_sub_class.name IS NOT NULL THEN
579
580 hr_utility.trace('Sub-class rec-'||to_char(j)||
581 '='||lr_sub_class.name);
582
583 hr_utility.trace('Entered SUB element_classification '||
584 lr_sub_class.name);
585
586 lr_sub_class.name :=
587 get_base_classification( lv_legislation_code
588 ,ln_business_group_id
589 ,lr_sub_class.name);
590
591 hr_utility.trace('BASE SUB element_classification '||
592 lr_sub_class.name);
593
594 lr_shadow_sub_class_rec.sub_classification_rule_id := null;
595 lr_shadow_sub_class_rec.element_type_id := ln_shd_ele_type_id;
596 lr_shadow_sub_class_rec.element_classification :=
597 lr_sub_class.name;
598 lr_shadow_sub_class_rec.object_version_number := null;
599 lr_shadow_sub_class_rec.exclusion_rule_id := null;
600
601 pay_ssr_ins.ins(l_rec.effective_date, lr_shadow_sub_class_rec);
602
603 ELSIf lr_sub_class.name IS NULL THEN
604
605 hr_utility.trace('Exit from Sub-class loop');
606 exit;
607
608 END IF; -- lr_sub_class.name is not null
609
610 END LOOP;
611
612 END IF;
613
614 END IF; --ln_shd_ele_type_id is not null
615
616 /* End of Sub-classification rules creation */
617
618 hr_utility.set_location(l_proc, 60);
619
620 IF p_validate <> TRUE THEN
621
622 lv_ip_installed := pay_ip_utility.get_ip_installation(ln_business_group_id);
623
624 IF lv_ip_installed = 'N' THEN
625 lv_information_category := upper(l_rec.legislation_code)||'_'||upper(l_rec.element_classification);
626 END IF;
627
628 pay_shadow_element_api.update_shadow_element
629 (p_validate => p_validate
630 ,p_effective_date => l_rec.effective_date
631 ,p_element_type_id => ln_shd_ele_type_id
632 ,p_reporting_name => l_rec.reporting_name
633 ,p_description => l_rec.element_description
634 ,p_classification_name => l_rec.element_classification
635 ,p_post_termination_rule => l_rec.termination_rule
636 ,p_standard_link_flag => l_rec.standard_link
637 ,p_processing_type => l_rec.processing_type
638 ,p_element_information_category => lv_information_category
639 ,p_once_each_period_flag => l_rec.proc_once_pay_period
640 ,p_process_mode => l_rec.process_mode
641 ,p_input_currency_code => l_rec.input_currency_code
642 ,p_output_currency_code => lv_currency_code
643 ,p_multiple_entries_allowed_fla => l_rec.multiple_entries_allowed
644 ,p_object_version_number => l_ele_obj_ver_number);
645
646 hr_utility.set_location(l_proc, 70);
647
648 BEGIN
649
650 EXECUTE IMMEDIATE 'BEGIN PAY_'||l_rec.legislation_code||
651 '_RULES.ELEMENT_TEMPLATE_UPD_USER_STRU(:c); END;'
652 USING p_ele_template_id;
653
654 hr_utility.set_location(l_proc, 100);
655
656 EXCEPTION
657 WHEN Cannot_Find_Prog_Unit THEN
658 null;
659 WHEN others THEN
660 raise;
661
662 END;
663
664 IF p_save_for_later <> 'Y' THEN
665
666 pay_element_template_api.generate_part1
667 (p_validate => FALSE
668 ,p_effective_date => l_rec.effective_date
669 ,p_hr_only => FALSE
670 ,p_hr_to_payroll => FALSE
671 ,p_template_id => p_ele_template_id);
672
673 hr_utility.set_location(l_proc, 80);
674
675 pay_element_template_api.generate_part2
676 (p_validate => FALSE
677 ,p_effective_date => l_rec.effective_date
678 ,p_template_id => p_ele_template_id);
679
680 hr_utility.set_location(l_proc, 90);
681
682 lv_ip_installed := pay_ip_utility.get_ip_installation(ln_business_group_id);
683
684 IF lv_ip_installed = 'N' THEN
685
686 OPEN get_element_type_id (l_rec.element_name,ln_business_group_id);
687 FETCH get_element_type_id INTO ln_element_type_id,lv_classification_name;
688 CLOSE get_element_type_id;
689
690 OPEN get_primary_balance_id (ln_element_type_id);
691 FETCH get_primary_balance_id INTO lv_primary_bal_id;
692 CLOSE get_primary_balance_id;
693
694 OPEN get_hourly_balance_id (ln_element_type_id);
695 FETCH get_hourly_balance_id INTO lv_hourly_bal_id;
696 CLOSE get_hourly_balance_id;
697
698 IF (lv_classification_name = 'INVOLUNTARY DEDUCTIONS') OR
699 (lv_classification_name = 'VOLUNTARY DEDUCTIONS') OR
700 (lv_classification_name = 'PRE-TAX DEDUCTIONS') OR
701 (lv_classification_name = 'TAX DEDUCTIONS') THEN
702
703 OPEN get_arrears_balance_id (ln_element_type_id);
704 FETCH get_arrears_balance_id INTO lv_arrears_bal_id;
705 CLOSE get_arrears_balance_id;
706
707 OPEN get_accruals_balance_id (ln_element_type_id);
708 FETCH get_accruals_balance_id INTO lv_accruals_bal_id;
709 CLOSE get_accruals_balance_id;
710
711 END IF;
712
713 UPDATE pay_element_types_f set ELEMENT_INFORMATION10 = lv_primary_bal_id , ELEMENT_INFORMATION12 = lv_hourly_bal_id, ELEMENT_INFORMATION11 = lv_arrears_bal_id, ELEMENT_INFORMATION13 = lv_accruals_bal_id
714 WHERE element_type_id = ln_element_type_id
715 AND element_information_category = upper(l_rec.legislation_code)||'_'||lv_classification_name;
716 COMMIT;
717
718 END IF;
719
720 FOR formula in get_shd_formula_id( p_ele_template_id )
721 LOOP
722
723 OPEN get_formula_info(formula.payroll_formula_id);
724 FETCH get_formula_info INTO lv_formula_name
725 ,lv_formula_type_name;
726 CLOSE get_formula_info;
727
728 ln_req_id := fnd_request.submit_request(
729 application => 'FF',
730 program => 'SINGLECOMPILE',
731 argument1 => lv_formula_type_name,
732 argument2 => lv_formula_name);
733
734
735 END LOOP;
736
737 END IF;
738
739
740 BEGIN
741
742 EXECUTE IMMEDIATE 'BEGIN PAY_'||l_rec.legislation_code||
743 '_RULES.ELEMENT_TEMPLATE_POST_PROCESS(:c); END;'
744 USING p_ele_template_id;
745
746 hr_utility.set_location(l_proc, 100);
747
748 EXCEPTION
749 WHEN Cannot_Find_Prog_Unit THEN
750 null;
751 WHEN others THEN
752 raise;
753
754 END;
755
756 /* Added the Frequency Rule creation in core schema */
757
758 hr_utility.trace('Start of Frequency Rule Creation ');
759
760 OPEN get_core_shadow_object_id(p_ele_template_id,'ET');
761 FETCH get_core_shadow_object_id INTO ln_core_object_id,
762 ln_shadow_object_id;
763 CLOSE get_core_shadow_object_id;
764
765 lv_period_6 := 'N';
766 hr_utility.trace('ln_core_object_id :'||to_char(ln_core_object_id));
767
768 IF ln_core_object_id IS NOT NULL AND
769 lt_freq_rule_table.count > 0 THEN
770
771 hr_utility.trace('Freq Rule Rec Count :'||
772 to_char(lt_freq_rule_table.count));
773 hr_utility.trace('Start of Freq Rule Recs Loop');
774
775 FOR k IN lt_freq_rule_table.FIRST..lt_freq_rule_table.LAST
776 LOOP
777
778 lr_freq_rule_rec := pay_freq_rule_obj(null,null,null,
779 null,null,null,null,null);
780 lr_freq_rule_rec := lt_freq_rule_table(k);
781
782 hr_utility.trace('Freq Rule Rec :'||to_char(k));
783 hr_utility.trace('Period 1 : '||lr_freq_rule_rec.period_1);
784 hr_utility.trace('Period 2 : '||lr_freq_rule_rec.period_2);
785 hr_utility.trace('Period 3 : '||lr_freq_rule_rec.period_3);
786 hr_utility.trace('Period 4 : '||lr_freq_rule_rec.period_4);
787 hr_utility.trace('Period 5 : '||lr_freq_rule_rec.period_5);
788 hr_utility.trace('RuleDateCode : '||lr_freq_rule_rec.date_option);
789
790 IF lr_freq_rule_rec.payroll IS NOT NULL THEN
791
792 OPEN get_payroll_id(ln_business_group_id
793 ,lr_freq_rule_rec.payroll
794 ,l_rec.effective_date);
795
796 FETCH get_payroll_id INTO ln_payroll_id;
797 CLOSE get_payroll_id;
798
799 hr_utility.trace('Payroll Id :'||to_char(ln_payroll_id));
800
801 pay_pyepfreq_pkg.hr_ele_pay_freq_rules
802 (p_context => 'ON-UPDATE',
803 p_eletype_id => ln_core_object_id,
804 p_payroll_id => ln_payroll_id,
805 p_period_type => lr_freq_rule_rec.period_type,
806 p_bg_id => ln_business_group_id,
807 p_period_1 => lr_freq_rule_rec.period_1,
808 p_period_2 => lr_freq_rule_rec.period_2,
809 p_period_3 => lr_freq_rule_rec.period_3,
810 p_period_4 => lr_freq_rule_rec.period_4,
811 p_period_5 => lr_freq_rule_rec.period_5,
812 p_period_6 => lv_period_6,
813 p_eff_date => l_rec.effective_date,
814 p_rule_date_code => lr_freq_rule_rec.date_option,
815 p_leg_code => null);
816
817 END IF; -- lr_freq_rule_rec.payroll is not null
818
819 END LOOP;
820
821 hr_utility.trace('End of Freq Rule Recs Loop');
822
823 /* Initialize Frequency Rule Plsql table that is used in
824 Summary Page of Element Template Wizard. */
825
826 pay_pyepfreq_pkg.initialise_freqrule_table;
827
828 END IF; -- ln_core_object_id is not null
829
830 /* End of Frequency Rule creation in core schema */
831
832 END IF;
833
834 hr_utility.set_location('Leaving '||l_proc, 1000);
835
836 END create_element;
837
838
839 PROCEDURE delete_element
840 ( p_validate IN BOOLEAN,
841 p_template_id IN NUMBER
842 ) IS
843
844 l_proc VARCHAR2(240);
845 l_legislation_code varchar2(30);
846
847 cursor cur_leg_code IS
848 SELECT
849 pbg.legislation_code
850 FROM
851 per_business_groups pbg,
852 pay_element_templates pet
853 WHERE
854 pet.template_id = p_template_id AND
855 pet.business_group_id = pbg.business_group_id;
856
857 BEGIN
858
859 l_proc := 'delete_element';
860
861 hr_utility.set_location(l_proc, 10);
862
863 OPEN cur_leg_code;
864 FETCH cur_leg_code
865 INTO l_legislation_code;
866 CLOSE cur_leg_code;
867
868 hr_utility.set_location(l_proc, 20);
869
870 BEGIN
871 EXECUTE IMMEDIATE 'BEGIN PAY_'||l_legislation_code||
872 '_RULES.DELETE_PRE_PROCESS(:c); END;'
873 USING p_template_id;
874
875 hr_utility.set_location(l_proc, 30);
876
877 EXCEPTION
878 WHEN Cannot_Find_Prog_Unit THEN
879 null;
880 WHEN others THEN
881 raise;
882
883 END;
884
885 pay_element_template_api.delete_user_structure
886 (p_validate => p_validate,
887 p_drop_formula_packages => TRUE,
888 p_template_id => p_template_id);
889
890 BEGIN
891
892 EXECUTE IMMEDIATE 'BEGIN PAY_'||l_legislation_code||
893 '_RULES.DELETE_POST_PROCESS(:c); END;'
894 USING p_template_id;
895
896 hr_utility.set_location(l_proc, 50);
897
898 EXCEPTION
899 WHEN Cannot_Find_Prog_Unit THEN
900 null;
901 WHEN others THEN
902 raise;
903
904 END;
905
906 END delete_element;
907
908 BEGIN
909
910 g_package := 'pay_element_template_user_init.';
911
912 END pay_element_template_user_init;