[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_ELEMENT_TEMPLATE_PKG
Source
1 PACKAGE BODY pay_in_element_template_pkg AS
2 /* $Header: pyineltm.pkb 120.27.12010000.2 2008/09/16 10:28:57 rsaharay ship $ */
3
4 /*========================================================================
5 Global Variables
6 ========================================================================*/
7 g_debug BOOLEAN;
8
9 /*========================================================================
10 Private Functions
11 ========================================================================*/
12 --------------------------------------------------------------------------
13
14 --------------------------------------------------------------------------
15 -- Name : GET_EXCLUSION_RULE_ID --
16 -- Type : FUNCTION --
17 -- Access : Private --
18 -- Description : Procedure to fetch the template_id --
19 -- Parameters : --
20 -- IN : p_template_name VARCHAR2 --
21 -- OUT : p_template_id NUMBER --
22 -- RETURN : N/A --
23 --------------------------------------------------------------------------
24 FUNCTION get_exclusion_rule_id
25 (p_template_rec IN pay_in_etw_struct.t_template_setup_rec
26 ,p_exclusion_tag IN VARCHAR2
27 )
28 RETURN NUMBER
29 IS
30
31 l_procedure VARCHAR2(100):= g_package||'get_exclusion_rule_id';
32 l_message VARCHAR2(1000);
33 BEGIN
34 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
35
36 IF p_exclusion_tag IS NULL THEN
37 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
38
39 RETURN TO_NUMBER(NULL);
40 END IF ;
41
42 FOR i IN p_template_rec.er_setup.FIRST
43 ..p_template_rec.er_setup.LAST
44 LOOP
45
46 IF p_template_rec.er_setup(i).tag = p_exclusion_tag
47 THEN
48 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
49
50 RETURN p_template_rec.er_setup(i).rule_id;
51 END IF ;
52
53 END LOOP;
54 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
55
56 RETURN TO_NUMBER(NULL);
57
58 EXCEPTION
59 WHEN OTHERS THEN
60 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,50);
61 l_message := pay_in_utils.get_pay_message
62 ('PER_IN_ORACLE_GENERIC_ERROR',
63 'FUNCTION:'||l_procedure,
64 'SQLERRMC:'||SQLERRM);
65 pay_in_utils.trace('SQLERRM',l_message);
66 RAISE ;
67
68 END get_exclusion_rule_id;
69
70 --------------------------------------------------------------------------
71 -- Name : GET_IV_ID --
72 -- Type : FUNCTION --
73 -- Access : Private --
74 -- Description : Procedure to fetch the iv_id of base element --
75 -- Parameters : --
76 -- IN : p_template_name VARCHAR2 --
77 -- OUT : p_template_id NUMBER --
78 -- RETURN : N/A --
79 --------------------------------------------------------------------------
80 FUNCTION get_iv_id
81 (p_template_rec IN pay_in_etw_struct.t_template_setup_rec
82 ,p_input_value IN VARCHAR2
83 )
84 RETURN NUMBER
85 IS
86 l_procedure VARCHAR2(100):= g_package||'get_iv_id';
87 l_message VARCHAR2(1000);
88
89 BEGIN
90 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
91
92 FOR i IN p_template_rec.iv_setup.FIRST
93 ..p_template_rec.iv_setup.LAST
94 LOOP
95 IF p_template_rec.iv_setup(i).input_value_name
96 = p_input_value
97 THEN
98 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
99
100 RETURN p_template_rec.iv_setup(i).input_value_id;
101 END IF ;
102
103 END LOOP;
104 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
105
106 RETURN TO_NUMBER(NULL);
107
108 EXCEPTION
109 WHEN OTHERS THEN
110 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,40);
111 l_message := pay_in_utils.get_pay_message
112 ('PER_IN_ORACLE_GENERIC_ERROR',
113 'FUNCTION:'||l_procedure,
114 'SQLERRMC:'||SQLERRM);
115 pay_in_utils.trace('SQLERRM',l_message);
116 RAISE ;
117
118 END get_iv_id;
119
120 --------------------------------------------------------------------------
121 -- Name : GET_AET_ID --
122 -- Type : FUNCTION --
123 -- Access : Private --
124 -- Description : Procedure to fetch the Additional Element Type Id --
125 -- Parameters : --
126 -- IN : p_template_rec VARCHAR2 --
127 -- OUT : p_template_id NUMBER --
128 -- RETURN : N/A --
129 --------------------------------------------------------------------------
130 FUNCTION get_aet_id
131 (p_template_rec IN pay_in_etw_struct.t_template_setup_rec
132 ,p_element_name IN VARCHAR2
133 )
134 RETURN NUMBER
135 IS
136
137 l_procedure VARCHAR2(100):= g_package||'get_aet_id';
138 l_message VARCHAR2(1000);
139
140 BEGIN
141 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
142
143 FOR i IN p_template_rec.ae_setup.FIRST
144 ..p_template_rec.ae_setup.LAST
145 LOOP
146 IF p_template_rec.ae_setup(i).element_name
147 = p_element_name
148 THEN
149 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,20);
150
151 RETURN p_template_rec.ae_setup(i).element_id;
152 END IF ;
153
154 END LOOP;
155 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,30);
156
157 RETURN TO_NUMBER(NULL);
158
159 EXCEPTION
160 WHEN OTHERS THEN
161 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,40);
162 l_message := pay_in_utils.get_pay_message
163 ('PER_IN_ORACLE_GENERIC_ERROR',
164 'FUNCTION:'||l_procedure,
165 'SQLERRMC:'||SQLERRM);
166 pay_in_utils.trace('SQLERRM',l_message);
167 RAISE ;
168 END get_aet_id;
169
170 --------------------------------------------------------------------------
171 -- Name : GET_AIV_ID --
172 -- Type : FUNCTION --
173 -- Access : Private --
174 -- Description : Procedure to fetch the iv_id of additional elements --
175 -- Parameters : --
176 -- IN : p_template_name VARCHAR2 --
177 -- OUT : p_template_id NUMBER --
178 -- RETURN : N/A --
179 --------------------------------------------------------------------------
180 FUNCTION get_aiv_id
181 (p_template_rec IN pay_in_etw_struct.t_template_setup_rec
182 ,p_element_id IN NUMBER
183 ,p_input_value IN VARCHAR2
184 )
185 RETURN NUMBER
186 IS
187 l_procedure VARCHAR2(100):= g_package||'get_aiv_id';
188 l_message VARCHAR2(1000);
189
190 BEGIN
191 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
192
193 FOR i IN p_template_rec.ae_setup.FIRST
194 ..p_template_rec.ae_setup.LAST
195 LOOP
196 IF p_template_rec.ae_setup(i).element_id = p_element_id
197 THEN
198
199 FOR j IN p_template_rec.ae_setup(i).iv_setup.FIRST
200 ..p_template_rec.ae_setup(i).iv_setup.LAST
201 LOOP
202 IF p_template_rec.ae_setup(i).iv_setup(j).input_value_name
203 = p_input_value
204 THEN
205 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,20);
206
207 RETURN p_template_rec.ae_setup(i).iv_setup(j).input_value_id;
208 END IF ;
209 END LOOP ;
210 END IF ;
211 END LOOP;
212
213 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,30);
214
215 RETURN TO_NUMBER(NULL);
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,40);
220 l_message := pay_in_utils.get_pay_message
221 ('PER_IN_ORACLE_GENERIC_ERROR',
222 'FUNCTION:'||l_procedure,
223 'SQLERRMC:'||SQLERRM);
224 pay_in_utils.trace('SQLERRM',l_message);
225 RAISE ;
226
227 END get_aiv_id;
228
229
230 --------------------------------------------------------------------------
231 -- Name : GET_TEXT --
232 -- Type : FUNCTION --
233 -- Access : Private --
234 -- Description : Procedure to fetch the template_id --
235 -- Parameters : --
236 -- IN : p_template_name VARCHAR2 --
237 -- OUT : p_template_id NUMBER --
238 -- RETURN : N/A --
239 --------------------------------------------------------------------------
240 FUNCTION get_text (p_formula_name IN VARCHAR2)
241 RETURN VARCHAR2
242 IS
243 l_procedure CONSTANT VARCHAR2(100):= g_package||'get_text';
244 l_message VARCHAR2(1000);
245
246 BEGIN
247 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
248
249 FOR i IN 1..pay_in_etw_struct.g_formula_obj.COUNT
250 LOOP
251
252 IF pay_in_etw_struct.g_formula_obj(i).NAME = p_formula_name
253 THEN
254 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,20);
255
256 RETURN pay_in_etw_struct.g_formula_obj(i).text;
257 END IF;
258
259 END LOOP;
260 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,30);
261
262 RETURN NULL;
263
264 EXCEPTION
265 WHEN OTHERS THEN
266 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,40);
267 l_message := pay_in_utils.get_pay_message
268 ('PER_IN_ORACLE_GENERIC_ERROR',
269 'FUNCTION:'||l_procedure,
270 'SQLERRMC:'||SQLERRM);
271 pay_in_utils.trace('SQLERRM',l_message);
272 RAISE ;
273
274 END get_text;
275
276 /*========================================================================
277 Private Procedures
278 ========================================================================*/
279 --------------------------------------------------------------------------
280 -- Name : GET_ELEMENT_TEMPLATE --
281 -- Type : PROCEDURE --
282 -- Access : Private --
283 -- Description : Procedure to fetch the template_id --
284 -- Parameters : --
285 -- IN : p_template_name VARCHAR2 --
286 -- OUT : p_template_id NUMBER --
287 -- RETURN : N/A --
288 --------------------------------------------------------------------------
289 PROCEDURE get_element_template
290 (p_template_id IN NUMBER
291 ,p_element_template OUT NOCOPY pay_etm_shd.g_rec_type
292 )
293 IS
294 l_procedure VARCHAR2(100):= g_package||'get_element_template';
295 l_message VARCHAR2(1000);
296
297 CURSOR csr_element_template(p_template_id IN NUMBER)
298 IS
299 SELECT
300 template_id,
301 template_type,
302 template_name,
303 base_processing_priority,
304 business_group_id,
305 legislation_code,
306 version_number,
307 base_name,
308 max_base_name_length,
309 configuration_info_category,
310 configuration_information1,
311 configuration_information2,
312 configuration_information3,
313 configuration_information4,
314 configuration_information5,
315 configuration_information6,
316 configuration_information7,
317 configuration_information8,
318 configuration_information9,
319 configuration_information10,
320 configuration_information11,
321 configuration_information12,
322 configuration_information13,
323 configuration_information14,
324 configuration_information15,
325 configuration_information16,
326 configuration_information17,
327 configuration_information18,
328 configuration_information19,
329 configuration_information20,
330 configuration_information21,
331 configuration_information22,
332 configuration_information23,
333 configuration_information24,
334 configuration_information25,
335 configuration_information26,
336 configuration_information27,
337 configuration_information28,
338 configuration_information29,
339 configuration_information30,
340 configuration_info_category,
341 configuration_information1,
342 configuration_information2,
343 configuration_information3,
344 configuration_information4,
345 configuration_information5,
346 configuration_information6,
347 configuration_information7,
348 configuration_information8,
349 configuration_information9,
350 configuration_information10,
351 configuration_information11,
352 configuration_information12,
353 configuration_information13,
354 configuration_information14,
355 configuration_information15,
356 configuration_information16,
357 configuration_information17,
358 configuration_information18,
359 configuration_information19,
360 configuration_information20,
361 configuration_information21,
362 configuration_information22,
363 configuration_information23,
364 configuration_information24,
365 configuration_information25,
366 configuration_information26,
367 configuration_information27,
368 configuration_information28,
369 configuration_information29,
370 configuration_information30,
371 object_version_number
372 FROM pay_element_templates
373 WHERE template_id = p_template_id
374 FOR UPDATE OF template_id;
375
376 BEGIN
377 pay_in_utils.set_location(g_debug, 'Entering: '||l_procedure,10);
378
379 OPEN csr_element_template(p_template_id);
380 FETCH csr_element_template INTO p_element_template;
381 CLOSE csr_element_template;
382
383 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,20);
384 EXCEPTION
385 WHEN OTHERS THEN
386 IF csr_element_template%ISOPEN THEN
387 CLOSE csr_element_template;
388 END IF;
389 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,40);
390 l_message := pay_in_utils.get_pay_message
391 ('PER_IN_ORACLE_GENERIC_ERROR',
392 'FUNCTION:'||l_procedure,
393 'SQLERRMC:'||SQLERRM);
394 pay_in_utils.trace('SQLERRM',l_message);
395 RAISE ;
396
397 END get_element_template;
398
399 --------------------------------------------------------------------------
400 -- Name : GET_TEMPLATE --
401 -- Type : PROCEDURE --
402 -- Access : Private --
403 -- Description : Procedure to fetch the template_id --
404 -- Parameters : --
405 -- IN : p_template_name VARCHAR2 --
406 -- OUT : p_template_rec t_template_setup_rec --
407 -- RETURN : N/A --
408 --------------------------------------------------------------------------
409 PROCEDURE get_template
410 (p_template_name IN VARCHAR2
411 ,p_template_rec OUT NOCOPY pay_in_etw_struct.t_template_setup_rec
412 )
413 IS
414 l_procedure CONSTANT VARCHAR2(100):= g_package||'get_template';
415 l_message VARCHAR2(1000);
416
417 BEGIN
418 pay_in_utils.set_location(g_debug,'Entering : '||l_procedure,10);
419
420 FOR i IN 1..pay_in_etw_struct.g_template_obj.COUNT
421 LOOP
422
423 IF pay_in_etw_struct.g_template_obj(i).template_name = p_template_name THEN
424
425 pay_in_utils.set_location(g_debug,l_procedure,20);
426
427 p_template_rec := pay_in_etw_struct.g_template_obj(i);
428
429 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,30);
430
431 RETURN ;
432
433 END IF;
434
435 END LOOP;
436 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,40);
437
438 EXCEPTION
439 WHEN OTHERS THEN
440 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,50);
441 l_message := pay_in_utils.get_pay_message
442 ('PER_IN_ORACLE_GENERIC_ERROR',
443 'FUNCTION:'||l_procedure,
444 'SQLERRMC:'||SQLERRM);
445 pay_in_utils.trace('SQLERRM',l_message);
446 RAISE ;
447
448 END get_template;
449
450
451 /*========================================================================
452 Public Procedures
453 ========================================================================*/
454 --------------------------------------------------------------------------
455 -- Name : CREATE_TEMPLATE --
456 -- Type : PROCEDURE --
457 -- Access : Public --
458 -- Description : Procedure to fetch the template_id --
459 -- Parameters : --
460 -- IN : p_template_name VARCHAR2 --
461 -- OUT : p_template_id NUMBER --
462 -- RETURN : N/A --
463 --------------------------------------------------------------------------
464 PROCEDURE create_template
465 (p_template_name IN VARCHAR2
466 ,p_template_id OUT NOCOPY NUMBER
467 )
468 IS
469
470 l_procedure CONSTANT VARCHAR2(100):= g_package||'create_template';
471 l_message VARCHAR2(1000);
472 l_effective_date CONSTANT DATE := TO_DATE('01/04/2005','DD/MM/YYYY');
473
474 l_template_exists VARCHAR2(1);
475 l_template_id pay_element_templates.template_id%TYPE;
476 l_enabled_flag fnd_currencies.enabled_flag%TYPE;
477 l_object_version_number NUMBER ;
478 l_template_rec pay_in_etw_struct.t_template_setup_rec;
479 l_sequence NUMBER;
480 l_db_items_flag VARCHAR2(1);
481 l_balance_feed_id pay_balance_feeds_f.balance_feed_id%TYPE ;
482 l_formula_id ff_formulas_f.formula_id%TYPE;
483 l_result_rule_id pay_formula_result_rules_f.formula_result_rule_id%TYPE;
484
485 l_aet_id NUMBER ;
486 BEGIN
487 g_debug := hr_utility.debug_enabled;
488
489
490 hr_utility.trace('l_procedure: '||l_procedure);
491 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
492
493 l_template_exists := 'N';
494
495 BEGIN
496 SELECT 'Y', template_id
497 INTO l_template_exists, l_template_id
498 FROM pay_element_templates
499 WHERE template_type = pay_in_etw_struct.g_template_type
500 AND legislation_code = pay_in_etw_struct.g_legislation_code
501 AND template_name = p_template_name;
502 EXCEPTION
503 WHEN OTHERS THEN
504 NULL;
505 END;
506 pay_in_utils.set_location(g_debug,l_procedure,20);
507
508 IF (l_template_exists = 'Y')
509 THEN
510 pay_in_utils.set_location(g_debug,l_procedure,25);
511
512 BEGIN
513
514 DELETE FROM pay_ele_tmplt_class_usages
515 WHERE template_id = l_template_id;
516
517 pay_element_template_api.delete_user_structure(FALSE ,TRUE ,
518 l_template_id);
519 l_template_exists := 'N';
520 EXCEPTION
521 WHEN OTHERS THEN
522 l_template_exists := 'N';
523 NULL;
524 END;
525 END IF;
526
527 pay_in_utils.set_location(g_debug,l_procedure,30);
528
529 IF NOT hr_utility.chk_product_install('Oracle Human Resources','IN') OR
530 l_template_exists = 'Y'
531 THEN
532 pay_in_utils.set_location(g_debug,l_procedure,35);
533 RETURN;
534 END IF;
535
536 SELECT enabled_flag
537 INTO l_enabled_flag
538 FROM fnd_currencies
539 WHERE currency_code = pay_in_etw_struct.g_currency_code;
540
541 UPDATE fnd_currencies
542 SET enabled_flag = 'Y'
543 WHERE currency_code = pay_in_etw_struct.g_currency_code
544 AND enabled_flag <> 'Y';
545
546 pay_in_utils.set_location(g_debug,l_procedure,40);
547
548 pay_in_etw_struct.init_code;
549 pay_in_etw_struct.init_formula;
550
551 pay_in_utils.set_location(g_debug,l_procedure,50);
552
553 get_template
554 (p_template_name => p_template_name
555 ,p_template_rec => l_template_rec
556 );
557
558 IF g_debug THEN
559 pay_in_utils.trace('Template Name ',l_template_rec.template_name);
560 pay_in_utils.trace('Category ',l_template_rec.category);
561 pay_in_utils.trace('Priority ',l_template_rec.priority);
562 END IF;
563
564 --
565 -- PAY_ELEMENT_TEMPLATES row.
566 --
567 pay_in_utils.set_location(g_debug,l_procedure,60);
568 pay_etm_ins.ins
569 (p_template_id => l_template_rec.template_id
570 ,p_effective_date => l_effective_date
571 ,p_template_type => pay_in_etw_struct.g_template_type
572 ,p_template_name => l_template_rec.template_name
573 ,p_base_processing_priority => l_template_rec.priority
574 ,p_max_base_name_length => pay_in_etw_struct.g_max_length
575 ,p_version_number => 1
576 ,p_legislation_code => pay_in_etw_struct.g_legislation_code
577 ,p_object_version_number => l_object_version_number
578 );
579
580 --
581 -- EXCLUSION RULES.
582 --
583 pay_in_utils.set_location(g_debug,l_procedure,70);
584 FOR i IN 1..l_template_rec.er_setup.COUNT
585 LOOP
586 pay_ter_ins.ins
587 (p_exclusion_rule_id => l_template_rec.er_setup(i).rule_id
588 ,p_template_id => l_template_rec.template_id
589 ,p_flexfield_column => l_template_rec.er_setup(i).ff_column
590 ,p_exclusion_value => l_template_rec.er_setup(i).value
591 ,p_description => l_template_rec.er_setup(i).descr
592 ,p_object_version_number => l_object_version_number
593 );
594 END LOOP;
595
596 --
597 -- USER FORMULAS
598 --
599 pay_in_utils.set_location(g_debug,l_procedure,80);
600 IF l_template_rec.uf_setup.formula_name IS NOT NULL THEN
601 BEGIN
602
603 SELECT formula_id, object_version_number
604 INTO l_template_rec.uf_setup.formula_id, l_object_version_number
605 FROM pay_shadow_formulas
606 WHERE template_type = pay_in_etw_struct.g_template_type
607 AND legislation_code= pay_in_etw_struct.g_legislation_code
608 AND formula_name = l_template_rec.uf_setup.formula_name;
609
610 pay_sf_upd.upd
611 (p_formula_id => l_template_rec.uf_setup.formula_id
612 ,p_description => l_template_rec.uf_setup.description
613 ,p_formula_text => get_text(l_template_rec.uf_setup.formula_name)
614 ,p_object_version_number => l_object_version_number
615 ,p_effective_date => l_effective_date
616 );
617
618 EXCEPTION
619 WHEN NO_DATA_FOUND THEN
620 pay_in_utils.set_location(g_debug,l_procedure,90);
621 pay_sf_ins.ins
622 (p_formula_id => l_template_rec.uf_setup.formula_id
623 ,p_template_type => pay_in_etw_struct.g_template_type
624 ,p_legislation_code => pay_in_etw_struct.g_legislation_code
625 ,p_formula_name => l_template_rec.uf_setup.formula_name
626 ,p_description => l_template_rec.uf_setup.description
627 ,p_formula_text => get_text(l_template_rec.uf_setup.formula_name)
628 ,p_object_version_number => l_object_version_number
629 ,p_effective_date => l_effective_date
630 );
631 END ;
632 ELSE
633 pay_in_utils.set_location(g_debug,l_procedure,100);
634
635 l_template_rec.uf_setup.formula_id := NULL ;
636 END IF ;
637 --
638 -- BASE Element
639 --
640 pay_in_utils.set_location(g_debug,l_procedure,110);
641 pay_set_ins.ins
642 (p_element_type_id => l_template_rec.base_element_id
643 ,p_template_id => l_template_rec.template_id
644 ,p_element_name => null
645 ,p_reporting_name => null
646 ,p_relative_processing_priority => 0
647 ,p_processing_type => 'R'
648 ,p_classification_name => l_template_rec.category
649 ,p_input_currency_code => pay_in_etw_struct.g_currency_code
650 ,p_output_currency_code => pay_in_etw_struct.g_currency_code
651 ,p_multiple_entries_allowed_fla => 'N'
652 ,p_post_termination_rule => 'F'
653 ,p_process_in_run_flag => 'Y'
654 ,p_additional_entry_allowed_fla => 'N'
655 ,p_adjustment_only_flag => 'N'
656 ,p_closed_for_entry_flag => 'N'
657 ,p_indirect_only_flag => 'N'
658 ,p_multiply_value_flag => 'N'
659 ,p_standard_link_flag => 'N'
660 ,p_process_mode => NULL
661 ,p_payroll_formula_id => l_template_rec.uf_setup.formula_id
662 ,p_skip_formula => NULL
663 ,p_object_version_number => l_object_version_number
664 ,p_effective_date => l_effective_date
665 ,p_exclusion_rule_id => NULL
666 );
667
668
669 --
670 -- BASE Element - Input Values
671 --
672 pay_in_utils.set_location(g_debug,l_procedure,120);
673 FOR i IN 1..l_template_rec.iv_setup.COUNT
674 LOOP
675 l_db_items_flag := 'N';
676 IF l_template_rec.iv_setup(i).input_value_name = 'Pay Value'
677 THEN
678 l_db_items_flag := 'Y';
679 END IF;
680
681 pay_siv_ins.ins
682 (p_input_value_id => l_template_rec.iv_setup(i).input_value_id
683 ,p_element_type_id => l_template_rec.base_element_id
684 ,p_display_sequence => i
685 ,p_generate_db_items_flag => l_db_items_flag
686 ,p_hot_default_flag => 'N'
687 ,p_mandatory_flag => l_template_rec.iv_setup(i).mandatory_flag
688 ,p_name => l_template_rec.iv_setup(i).input_value_name
689 ,p_uom => l_template_rec.iv_setup(i).uom
690 ,p_default_value => l_template_rec.iv_setup(i).default_value
691 ,p_default_value_column => l_template_rec.iv_setup(i).def_value_column
692 ,p_lookup_type => l_template_rec.iv_setup(i).lookup_type
693 ,p_min_value => l_template_rec.iv_setup(i).min_value
694 ,p_warning_or_error => l_template_rec.iv_setup(i).warn_or_error
695 ,p_exclusion_rule_id => get_exclusion_rule_id(l_template_rec,
696 l_template_rec.iv_setup(i).exclusion_tag)
697 ,p_object_version_number => l_object_version_number
698 ,p_effective_date => l_effective_date
699 );
700
701 IF l_template_rec.iv_setup(i).balance_name IS NOT NULL THEN
702 pay_in_utils.set_location(g_debug,l_procedure,125);
703
704 pay_sbf_ins.ins
705 (p_balance_feed_id => l_balance_feed_id
706 ,p_balance_name => l_template_rec.iv_setup(i).balance_name
707 ,p_input_value_id => l_template_rec.iv_setup(i).input_value_id
708 ,p_scale => 1
709 ,p_object_version_number => l_object_version_number
710 ,p_exclusion_rule_id => get_exclusion_rule_id(l_template_rec,
711 l_template_rec.iv_setup(i).exclusion_tag)
712 ,p_effective_date => l_effective_date
713 );
714 END IF;
715
716 END LOOP ;
717
718 --
719 -- BASE Element - Balance Feeds
720 --
721 pay_in_utils.set_location(g_debug,l_procedure,130);
722 FOR i IN 1..l_template_rec.bf_setup.COUNT
723 LOOP
724 pay_sbf_ins.ins
725 (p_balance_feed_id => l_balance_feed_id
726 ,p_balance_name => l_template_rec.bf_setup(i).balance_name
727 ,p_input_value_id => get_iv_id(l_template_rec, l_template_rec.bf_setup(i).iv_name)
728 ,p_scale => l_template_rec.bf_setup(i).scale
729 ,p_object_version_number => l_object_version_number
730 ,p_exclusion_rule_id => get_exclusion_rule_id(l_template_rec,
731 l_template_rec.bf_setup(i).exclusion_tag)
732 ,p_effective_date => l_effective_date
733 );
734 END LOOP;
735
736 --
737 -- Additional Elements
738 --
739
740 pay_in_utils.set_location(g_debug,l_procedure,140);
741 FOR i IN 1..l_template_rec.ae_setup.COUNT
742 LOOP
743
744 --
745 -- User Formulas for Additional Elements
746 --
747 IF l_template_rec.ae_setup(i).uf_setup.formula_name IS NOT NULL THEN
748 BEGIN
749
750 SELECT formula_id, object_version_number
751 INTO l_template_rec.ae_setup(i).uf_setup.formula_id, l_object_version_number
752 FROM pay_shadow_formulas
753 WHERE template_type = pay_in_etw_struct.g_template_type
754 AND legislation_code= pay_in_etw_struct.g_legislation_code
755 AND formula_name = l_template_rec.ae_setup(i).uf_setup.formula_name;
756
757 pay_in_utils.set_location(g_debug,l_procedure,150);
758
759 pay_sf_upd.upd
760 (p_formula_id => l_template_rec.ae_setup(i).uf_setup.formula_id
761 ,p_description => l_template_rec.ae_setup(i).uf_setup.description
762 ,p_formula_text => get_text(l_template_rec.ae_setup(i).uf_setup.formula_name)
763 ,p_object_version_number => l_object_version_number
764 ,p_effective_date => l_effective_date
765 );
766
767 EXCEPTION
768 WHEN NO_DATA_FOUND THEN
769 pay_in_utils.set_location(g_debug,l_procedure,160);
770
771 pay_sf_ins.ins
772 (p_formula_id => l_template_rec.ae_setup(i).uf_setup.formula_id
773 ,p_template_type => pay_in_etw_struct.g_template_type
774 ,p_legislation_code => pay_in_etw_struct.g_legislation_code
775 ,p_formula_name => l_template_rec.ae_setup(i).uf_setup.formula_name
776 ,p_description => l_template_rec.ae_setup(i).uf_setup.description
777 ,p_formula_text => get_text(l_template_rec.ae_setup(i).uf_setup.formula_name)
778 ,p_object_version_number => l_object_version_number
779 ,p_effective_date => l_effective_date
780 );
781 END ;
782 ELSE
783 pay_in_utils.set_location(g_debug,l_procedure,170);
784
785 l_template_rec.ae_setup(i).uf_setup.formula_id := NULL ;
786 END IF ;
787
788
789
790
791
792 pay_in_utils.set_location(g_debug,l_procedure,180);
793 pay_set_ins.ins
794 (p_element_type_id => l_template_rec.ae_setup(i).element_id
795 ,p_template_id => l_template_rec.template_id
796 ,p_element_name => l_template_rec.ae_setup(i).element_name
797 ,p_reporting_name => NULL --Fix for bug 5718112
798 ,p_relative_processing_priority => l_template_rec.ae_setup(i).priority
799 ,p_processing_type => 'N'
800 ,p_classification_name => l_template_rec.ae_setup(i).classification
801 ,p_input_currency_code => pay_in_etw_struct.g_currency_code
802 ,p_output_currency_code => pay_in_etw_struct.g_currency_code
803 ,p_multiple_entries_allowed_fla => 'N'
804 ,p_post_termination_rule => 'F'
805 ,p_process_in_run_flag => 'Y'
806 ,p_additional_entry_allowed_fla => 'N'
807 ,p_adjustment_only_flag => 'N'
808 ,p_closed_for_entry_flag => 'N'
809 ,p_indirect_only_flag => 'N'
810 ,p_multiply_value_flag => 'N'
811 ,p_standard_link_flag => 'N'
812 ,p_process_mode => NULL
813 ,p_payroll_formula_id => l_template_rec.ae_setup(i).uf_setup.formula_id
814 ,p_skip_formula => NULL
815 ,p_object_version_number => l_object_version_number
816 ,p_effective_date => l_effective_date
817 ,p_exclusion_rule_id => get_exclusion_rule_id(l_template_rec,
818 l_template_rec.ae_setup(i).exclusion_tag)
819 );
820
821 --
822 -- Additional Elements - Input Values
823 --
824 pay_in_utils.set_location(g_debug,l_procedure,190);
825 FOR j IN 1..l_template_rec.ae_setup(i).iv_setup.COUNT
826 LOOP
827
828 l_db_items_flag := 'N';
829 IF l_template_rec.ae_setup(i).iv_setup(j).input_value_name = 'Pay Value'
830 THEN
831 l_db_items_flag := 'Y';
832 END IF;
833 pay_in_utils.set_location(g_debug,l_procedure,200);
834
835 pay_siv_ins.ins
836 (p_input_value_id => l_template_rec.ae_setup(i).iv_setup(j).input_value_id
837 ,p_element_type_id => l_template_rec.ae_setup(i).element_id
838 ,p_display_sequence => j
839 ,p_generate_db_items_flag => l_db_items_flag
840 ,p_hot_default_flag => 'N'
841 ,p_mandatory_flag => l_template_rec.ae_setup(i).iv_setup(j).mandatory_flag
842 ,p_name => l_template_rec.ae_setup(i).iv_setup(j).input_value_name
843 ,p_uom => l_template_rec.ae_setup(i).iv_setup(j).uom
844 ,p_lookup_type => l_template_rec.ae_setup(i).iv_setup(j).lookup_type
845 ,p_default_value => l_template_rec.ae_setup(i).iv_setup(j).default_value
846 ,p_default_value_column => l_template_rec.ae_setup(i).iv_setup(j).def_value_column
847 ,p_min_value => l_template_rec.ae_setup(i).iv_setup(j).min_value
848 ,p_warning_or_error => l_template_rec.ae_setup(i).iv_setup(j).warn_or_error
849 ,p_exclusion_rule_id => get_exclusion_rule_id(l_template_rec,
850 l_template_rec.ae_setup(i).iv_setup(j).exclusion_tag)
851 ,p_object_version_number => l_object_version_number
852 ,p_effective_date => l_effective_date
853 );
854
855 IF l_template_rec.ae_setup(i).iv_setup(j).balance_name IS NOT NULL
856 THEN
857 pay_in_utils.set_location(g_debug,l_procedure,210);
858
859 pay_sbf_ins.ins
860 (p_balance_feed_id => l_balance_feed_id
861 ,p_balance_name => l_template_rec.ae_setup(i).iv_setup(j).balance_name
862 ,p_input_value_id => l_template_rec.ae_setup(i).iv_setup(j).input_value_id
863 ,p_scale => 1
864 ,p_object_version_number => l_object_version_number
865 ,p_exclusion_rule_id => get_exclusion_rule_id(l_template_rec,
866 l_template_rec.ae_setup(i).iv_setup(j).exclusion_tag)
867 ,p_effective_date => l_effective_date
868 );
869 END IF;
870
871 END LOOP;
872
873 --
874 -- Additional Elements - Balance Feeds
875 --
876 pay_in_utils.set_location(g_debug,l_procedure,220);
877 FOR j IN 1..l_template_rec.ae_setup(i).bf_setup.COUNT
878 LOOP
879
880 pay_sbf_ins.ins
881 (p_balance_feed_id => l_balance_feed_id
882 ,p_balance_name => l_template_rec.ae_setup(i).bf_setup(j).balance_name
883 ,p_input_value_id => get_aiv_id(l_template_rec
884 ,l_template_rec.ae_setup(i).element_id
885 ,l_template_rec.ae_setup(i).bf_setup(j).iv_name)
886 ,p_scale => l_template_rec.ae_setup(i).bf_setup(j).scale
887 ,p_object_version_number => l_object_version_number
888 ,p_exclusion_rule_id => get_exclusion_rule_id(l_template_rec,
889 l_template_rec.ae_setup(i).bf_setup(j).exclusion_tag)
890 ,p_effective_date => l_effective_date
891 );
892 END LOOP;
893
894 END LOOP ;
895
896 --
897 -- User Defined Formula Result Rules for Base Elements
898 --
899 pay_in_utils.set_location(g_debug,l_procedure,230);
900 FOR i IN 1..l_template_rec.uf_setup.frs_setup.COUNT
901 LOOP
902 pay_in_utils.set_location(g_debug,l_procedure,240);
903
904 IF l_template_rec.uf_setup.frs_setup(i).result_rule_type = 'D' THEN
905 pay_in_utils.set_location(g_debug,l_procedure,250);
906 pay_sfr_ins.ins
907 (p_formula_result_rule_id => l_result_rule_id
908 ,p_shadow_element_type_id => l_template_rec.base_element_id
909 ,p_result_name => l_template_rec.uf_setup.frs_setup(i).result_name
910 ,p_result_rule_type => l_template_rec.uf_setup.frs_setup(i).result_rule_type
911 ,p_element_type_id => l_template_rec.base_element_id
912 ,p_input_value_id => get_iv_id(l_template_rec, l_template_rec.uf_setup.frs_setup(i).input_value_name)
913 ,p_exclusion_rule_id => get_exclusion_rule_id(l_template_rec,
914 l_template_rec.uf_setup.frs_setup(i).exclusion_tag)
915 ,p_object_version_number => l_object_version_number
916 ,p_effective_date => l_effective_date
917 );
918 ELSIF l_template_rec.uf_setup.frs_setup(i).result_rule_type = 'M' THEN
919 pay_in_utils.set_location(g_debug,l_procedure,260);
920 pay_sfr_ins.ins
921 (p_formula_result_rule_id => l_result_rule_id
922 ,p_shadow_element_type_id => l_template_rec.base_element_id
923 ,p_result_name => l_template_rec.uf_setup.frs_setup(i).result_name
924 ,p_result_rule_type => l_template_rec.uf_setup.frs_setup(i).result_rule_type
925 ,p_exclusion_rule_id => get_exclusion_rule_id(l_template_rec,
926 l_template_rec.uf_setup.frs_setup(i).exclusion_tag)
927 ,p_severity_level => l_template_rec.uf_setup.frs_setup(i).severity_level
928 ,p_object_version_number => l_object_version_number
929 ,p_effective_date => l_effective_date
930 );
931 ELSIF l_template_rec.uf_setup.frs_setup(i).result_rule_type = 'I' THEN
932 pay_in_utils.set_location(g_debug,l_procedure,270);
933 l_aet_id := get_aet_id(l_template_rec
934 ,l_template_rec.uf_setup.frs_setup(i).element_name);
935 pay_sfr_ins.ins
936 (p_formula_result_rule_id => l_result_rule_id
937 ,p_shadow_element_type_id => l_template_rec.base_element_id
938 ,p_result_name => l_template_rec.uf_setup.frs_setup(i).result_name
939 ,p_result_rule_type => l_template_rec.uf_setup.frs_setup(i).result_rule_type
940 ,p_element_type_id => l_aet_id
941 ,p_input_value_id => get_aiv_id(l_template_rec
942 ,l_aet_id
943 ,l_template_rec.uf_setup.frs_setup(i).input_value_name)
944 ,p_exclusion_rule_id => get_exclusion_rule_id(l_template_rec,
945 l_template_rec.uf_setup.frs_setup(i).exclusion_tag)
946 ,p_severity_level => l_template_rec.uf_setup.frs_setup(i).severity_level
947 ,p_object_version_number => l_object_version_number
948 ,p_effective_date => l_effective_date
949 );
950
951 END IF;
952 END LOOP;
953
954
955 --
956 -- User Defined Formula Result Rules for Additional Elements
957 --
958 pay_in_utils.set_location(g_debug,l_procedure,280);
959 FOR j IN 1..l_template_rec.ae_setup.COUNT
960 LOOP
961 pay_in_utils.set_location(g_debug,l_procedure,290);
962 FOR i IN 1..l_template_rec.ae_setup(j).uf_setup.frs_setup.COUNT
963 LOOP
964 pay_in_utils.set_location(g_debug,l_procedure,300);
965 IF l_template_rec.ae_setup(j).uf_setup.frs_setup(i).result_rule_type = 'D' THEN
966 pay_in_utils.set_location(g_debug,l_procedure,310);
967 pay_sfr_ins.ins
968 (p_formula_result_rule_id => l_result_rule_id
969 ,p_shadow_element_type_id => l_template_rec.ae_setup(j).element_id
970 ,p_result_name => l_template_rec.ae_setup(j).uf_setup.frs_setup(i).result_name
971 ,p_result_rule_type => l_template_rec.ae_setup(j).uf_setup.frs_setup(i).result_rule_type
972 ,p_element_type_id => l_template_rec.ae_setup(j).element_id
973 ,p_input_value_id => get_aiv_id(l_template_rec
974 ,l_template_rec.ae_setup(j).element_id
975 ,l_template_rec.ae_setup(j).uf_setup.frs_setup(i).input_value_name)
976 ,p_exclusion_rule_id => get_exclusion_rule_id(l_template_rec,
977 l_template_rec.ae_setup(j).uf_setup.frs_setup(i).exclusion_tag)
978 ,p_object_version_number => l_object_version_number
979 ,p_effective_date => l_effective_date
980 );
981 ELSIF l_template_rec.ae_setup(j).uf_setup.frs_setup(i).result_rule_type = 'M' THEN
982 pay_in_utils.set_location(g_debug,l_procedure,320);
983 pay_sfr_ins.ins
984 (p_formula_result_rule_id => l_result_rule_id
985 ,p_shadow_element_type_id => l_template_rec.ae_setup(j).element_id
986 ,p_result_name => l_template_rec.ae_setup(j).uf_setup.frs_setup(i).result_name
987 ,p_result_rule_type => l_template_rec.ae_setup(j).uf_setup.frs_setup(i).result_rule_type
988 ,p_exclusion_rule_id => get_exclusion_rule_id(l_template_rec,
989 l_template_rec.ae_setup(j).uf_setup.frs_setup(i).exclusion_tag)
990 ,p_severity_level => l_template_rec.ae_setup(j).uf_setup.frs_setup(i).severity_level
991 ,p_object_version_number => l_object_version_number
992 ,p_effective_date => l_effective_date
993 );
994 ELSIF l_template_rec.ae_setup(j).uf_setup.frs_setup(i).result_rule_type = 'I' THEN
995 pay_in_utils.set_location(g_debug,l_procedure,330);
996 l_aet_id := get_aet_id(l_template_rec
997 ,l_template_rec.ae_setup(j).uf_setup.frs_setup(i).element_name);
998
999 pay_sfr_ins.ins
1000 (p_formula_result_rule_id => l_result_rule_id
1001 ,p_shadow_element_type_id => l_template_rec.ae_setup(j).element_id
1002 ,p_result_name => l_template_rec.ae_setup(j).uf_setup.frs_setup(i).result_name
1003 ,p_result_rule_type => l_template_rec.ae_setup(j).uf_setup.frs_setup(i).result_rule_type
1004 ,p_element_type_id => l_aet_id
1005 ,p_input_value_id => get_aiv_id(l_template_rec
1006 ,l_aet_id
1007 ,l_template_rec.ae_setup(j).uf_setup.frs_setup(i).input_value_name)
1008 ,p_exclusion_rule_id => get_exclusion_rule_id(l_template_rec,
1009 l_template_rec.ae_setup(j).uf_setup.frs_setup(i).exclusion_tag)
1010 ,p_severity_level => l_template_rec.ae_setup(j).uf_setup.frs_setup(i).severity_level
1011 ,p_object_version_number => l_object_version_number
1012 ,p_effective_date => l_effective_date
1013 );
1014
1015 END IF;
1016 END LOOP ;
1017 END LOOP;
1018
1019 pay_in_utils.set_location(g_debug,l_procedure,340);
1020
1021 UPDATE fnd_currencies
1022 SET enabled_flag = l_enabled_flag
1023 WHERE currency_code = pay_in_etw_struct.g_currency_code;
1024
1025 --
1026 -- PAY_ELE_TMPLT_CLASS_USAGES row.
1027 --
1028 pay_in_utils.set_location(g_debug,l_procedure,350);
1029 create_template_association( l_template_rec.template_id, l_template_rec.category );
1030
1031 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,360);
1032
1033 EXCEPTION
1034 WHEN OTHERS THEN
1035 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,370);
1036 l_message := pay_in_utils.get_pay_message
1037 ('PER_IN_ORACLE_GENERIC_ERROR',
1038 'FUNCTION:'||l_procedure,
1039 'SQLERRMC:'||SQLERRM);
1040 pay_in_utils.trace('SQLERRM',l_message);
1041 RAISE ;
1042
1043 END create_template;
1044
1045 --------------------------------------------------------------------------
1046 -- Name : CREATE_TEMPLATE_ASSOCIATION --
1047 -- Type : PROCEDURE --
1048 -- Access : Public --
1049 -- Description : Procedure to associate template with classification --
1050 -- Parameters : --
1051 -- IN : p_template_name VARCHAR2 --
1052 -- p_classification_name VARCHAR2 --
1053 -- RETURN : N/A --
1054 --------------------------------------------------------------------------
1055 PROCEDURE create_template_association
1056 (p_template_id IN NUMBER
1057 ,p_classification IN VARCHAR2 )
1058 IS
1059 l_classification_id pay_element_classifications.classification_id%TYPE ;
1060 l_exists NUMBER;
1061 l_ele_tmplt_class_id NUMBER;
1062
1063 l_procedure CONSTANT VARCHAR2(100):= g_package||'create_template_association';
1064 l_message VARCHAR2(1000);
1065
1066 BEGIN
1067 g_debug := hr_utility.debug_enabled;
1068 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1069
1070 SELECT classification_id
1071 INTO l_classification_id
1072 FROM pay_element_classifications
1073 WHERE legislation_code = pay_in_etw_struct.g_legislation_code
1074 AND classification_name = p_classification;
1075
1076 pay_in_utils.set_location(g_debug,l_procedure,20);
1077 SELECT count(*)
1078 INTO l_exists
1079 FROM pay_ele_tmplt_class_usages
1080 WHERE classification_id = l_classification_id
1081 AND template_id = p_template_id;
1082
1083 pay_in_utils.set_location(g_debug,l_procedure,30);
1084 IF l_exists = 0 THEN
1085
1086 pay_in_utils.set_location(g_debug,l_procedure,40);
1087 SELECT pay_ele_tmplt_class_usg_s.nextval
1088 INTO l_ele_tmplt_class_id
1089 FROM dual;
1090
1091 pay_in_utils.set_location(g_debug,l_procedure,50);
1092 INSERT INTO pay_ele_tmplt_class_usages
1093 ( ele_template_classification_id
1094 ,classification_id
1095 ,template_id
1096 ,display_process_mode
1097 ,display_arrearage )
1098 VALUES ( l_ele_tmplt_class_id
1099 ,l_classification_id
1100 ,p_template_id
1101 ,'Y'
1102 ,null);
1103
1104 END IF;
1105 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
1106
1107 EXCEPTION
1108 WHEN OTHERS THEN
1109 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,70);
1110 l_message := pay_in_utils.get_pay_message
1111 ('PER_IN_ORACLE_GENERIC_ERROR',
1112 'FUNCTION:'||l_procedure,
1113 'SQLERRMC:'||SQLERRM);
1114 pay_in_utils.trace('SQLERRM',l_message);
1115 RAISE ;
1116
1117 END create_template_association;
1118
1119 --------------------------------------------------------------------------
1120 -- Name : DELETE_TEMPLATE_ASSOCIATION --
1121 -- Type : PROCEDURE --
1122 -- Access : Public --
1123 -- Description : Procedure to fetch the template_id --
1124 -- Parameters : --
1125 -- IN : p_template_name VARCHAR2 --
1126 -- OUT : p_template_id NUMBER --
1127 -- RETURN : N/A --
1128 --------------------------------------------------------------------------
1129 PROCEDURE delete_template_association
1130 (p_template_name IN VARCHAR2
1131 ,p_classification IN VARCHAR2 )
1132 IS
1133 l_procedure CONSTANT VARCHAR2(100):= g_package||'delete_template_association';
1134 l_message VARCHAR2(1000);
1135 BEGIN
1136 g_debug := hr_utility.debug_enabled;
1137 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1138
1139 DELETE FROM pay_ele_tmplt_class_usages
1140 WHERE ele_template_classification_id
1141 IN (SELECT petcu.ele_template_classification_id
1142 FROM pay_ele_tmplt_class_usages petcu
1143 ,pay_element_classifications pec
1144 ,pay_element_templates pet
1145 WHERE petcu.classification_id = pec.classification_id
1146 AND petcu.template_id = pet.template_id
1147 AND pet.template_name = p_template_name
1148 AND pec.classification_name = p_classification
1149 AND pec.legislation_code = pay_in_etw_struct.g_legislation_code
1150 AND pet.legislation_code = pay_in_etw_struct.g_legislation_code);
1151
1152 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1153 EXCEPTION
1154 WHEN NO_DATA_FOUND THEN
1155 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,30);
1156 NULL;
1157 WHEN OTHERS THEN
1158 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,40);
1159 l_message := pay_in_utils.get_pay_message
1160 ('PER_IN_ORACLE_GENERIC_ERROR',
1161 'FUNCTION:'||l_procedure,
1162 'SQLERRMC:'||SQLERRM);
1163 pay_in_utils.trace('SQLERRM',l_message);
1164 RAISE ;
1165 END delete_template_association;
1166
1167 --------------------------------------------------------------------------
1168 -- Name : ELEMENT_TEMPLATE_PRE_PROCESS --
1169 -- Type : PROCEDURE --
1170 -- Access : Public --
1171 -- Description : Procedure to initialize the elements for ETW --
1172 -- Parameters : --
1173 -- IN : p_template_obj PAY_ELE_TMPLT_OBJ --
1174 -- OUT : p_template_obj PAY_ELE_TMPLT_OBJ --
1175 -- RETURN : N/A --
1176 --------------------------------------------------------------------------
1177 FUNCTION element_template_pre_process
1178 (p_template_obj IN PAY_ELE_TMPLT_OBJ)
1179 RETURN PAY_ELE_TMPLT_OBJ
1180 IS
1181 l_procedure VARCHAR2(100):= g_package||'element_template_pre_process';
1182 l_message VARCHAR2(1000);
1183
1184 l_template_obj PAY_ELE_TMPLT_OBJ;
1185
1186 BEGIN
1187
1188 g_debug := hr_utility.debug_enabled;
1189 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1190
1191 l_template_obj := p_template_obj;
1192
1193 IF g_debug THEN
1194
1195 pay_in_utils.trace('Classification ',l_template_obj.element_classification);
1196 pay_in_utils.trace('Processing Type ',l_template_obj.processing_type);
1197 pay_in_utils.trace('Conf Info Catg ',l_template_obj.configuration_info_category);
1198 pay_in_utils.trace('Conf Info 1 ',l_template_obj.configuration_information1);
1199 pay_in_utils.trace('Conf Info 2 ',l_template_obj.configuration_information2);
1200 pay_in_utils.trace('Conf Info 3 ',l_template_obj.configuration_information3);
1201 pay_in_utils.trace('Conf Info 4 ',l_template_obj.configuration_information4);
1202 pay_in_utils.trace('Conf Info 5 ',l_template_obj.configuration_information5);
1203 pay_in_utils.trace('Conf Info 6 ',l_template_obj.configuration_information6);
1204 pay_in_utils.trace('Conf Info 7 ',l_template_obj.configuration_information7);
1205 pay_in_utils.trace('Conf Info 8 ',l_template_obj.configuration_information8);
1206 pay_in_utils.trace('Conf Info 9 ',l_template_obj.configuration_information9);
1207 pay_in_utils.trace('Conf Info 10 ',l_template_obj.configuration_information10);
1208
1209 END IF;
1210
1211 l_template_obj.configuration_information1 := NVL(l_template_obj.configuration_information1,'N');
1212 l_template_obj.configuration_information2 := NVL(l_template_obj.configuration_information2,'N');
1213 l_template_obj.configuration_information3 := NVL(l_template_obj.configuration_information3,'N');
1214 l_template_obj.configuration_information4 := NVL(l_template_obj.configuration_information4,'N');
1215 l_template_obj.configuration_information5 := NVL(l_template_obj.configuration_information5,'N');
1216 l_template_obj.configuration_information6 := NVL(l_template_obj.configuration_information6,'N');
1217 l_template_obj.configuration_information7 := NVL(l_template_obj.configuration_information7,'N');
1218 l_template_obj.configuration_information8 := NVL(l_template_obj.configuration_information8,'N');
1219 l_template_obj.configuration_information9 := NVL(l_template_obj.configuration_information9,'N');
1220 l_template_obj.configuration_information10 := NVL(l_template_obj.configuration_information10,'N');
1221
1222 /*
1223 ----------------------------------------------------------------
1224 | Sr# | Classification | Template Name |
1225 ----------------------------------------------------------------
1226 | 1 | Fringe Benefits | Fringe Benefits |
1227 | 2 | Allowances | Fixed Allowance |
1228 | 3 | Allowances | Actual Expense Allowances |
1229 | 4 | Perquisites | Free Education |
1230 | 5 | Perquisites | Company Accommodation |
1231 | 6 | Perquisites | Loan at Concessional Rate |
1232 | 7 | Perquisites | Company Movable Assets |
1233 | 8 | Perquisites | Other Perquisites |
1234 | 9 | Earnings | Leave Travel Concession |
1235 | 10 | Earnings | Earnings |
1236 | 11 | Perquisites | Transfer of Company Assets |
1237 | 12 | Employer Charges| Employer Charges |
1238 ----------------------------------------------------------------
1239 */
1240
1241 IF l_template_obj.element_classification = 'Allowances' THEN
1242 pay_in_utils.set_location(g_debug,l_procedure,20);
1243
1244 IF l_template_obj.configuration_info_category = 'IN Fixed Allowance' THEN
1245 --
1246 -- CI1 - Allowance Name
1247 -- CI2 - Enable Projections
1248 -- CI3 - Is CEA or HEA
1249 -- CI4 - Enable Advances
1250 --
1251 /* Set Projection exclusion rule as per the Processing Type */
1252 pay_in_utils.set_location(g_debug,l_procedure,30);
1253 IF l_template_obj.processing_type = 'R' THEN
1254 pay_in_utils.set_location(g_debug,l_procedure,70);
1255 l_template_obj.configuration_information2 := 'Y'; -- Projections
1256 END IF ;
1257
1258 /* For CEA/HEA, set the Claim Exemption u/s 10 Exclusion Rule */
1259 IF l_template_obj.configuration_information1
1260 IN ('Children Education Allowance',
1261 'Hostel Expenditure Allowance')
1262 THEN
1263 pay_in_utils.set_location(g_debug,l_procedure,40);
1264 l_template_obj.configuration_information3 := 'Y';
1265 END IF ;
1266
1267 END IF;
1268
1269 pay_in_utils.set_location(g_debug,l_procedure,60);
1270 IF l_template_obj.configuration_info_category = 'IN Actual Expense Allowances' THEN
1271 --
1272 -- CI1 - Allowance Name
1273 -- CI2 - Nature of Expense
1274 -- CI3 - Enable Advances
1275 -- CI4 - Enable Projections
1276 -- CI5 - Create Expense Element
1277 -- CI6 - Is HRA
1278 -- CI7 - Is Ent
1279 -- CI8 - HRA + Advance
1280 -- CI9 - Ent + Advance
1281
1282
1283 -- Recurring Element NonRec Expense - Create Expense Element
1284 -- Recurring Element recurring Expense - Create Expense Input
1285 -- Non Recurring Element - Create Expense Input
1286
1287 /* Set Projection exclusion rule as per the Processing Type */
1288 IF l_template_obj.processing_type = 'R' THEN
1289
1290 pay_in_utils.set_location(g_debug,l_procedure,70);
1291 l_template_obj.configuration_information4 := 'Y'; -- Projection
1292
1293 IF (l_template_obj.configuration_information2 = 'N') THEN
1294 l_template_obj.configuration_information5 := 'Y'; -- Create Exp Element
1295 END IF ;
1296
1297
1298 ELSE /* Non recurring Allowance */
1299 pay_in_utils.set_location(g_debug,l_procedure,75);
1300 l_template_obj.configuration_information2 := 'Y';-- Create Exp Input
1301 l_template_obj.configuration_information5 := 'N';-- Create Exp Element
1302 END IF ;
1303
1304 IF l_template_obj.configuration_information1 = 'House Rent Allowance'
1305 THEN
1306
1307 pay_in_utils.set_location(g_debug,l_procedure,90);
1308 l_template_obj.configuration_information2 := 'N'; -- No Exp Projections
1309 l_template_obj.configuration_information5 := 'N'; -- No Exp Element
1310 l_template_obj.configuration_information6 := 'Y'; -- Is HRA
1311 l_template_obj.configuration_information8 := l_template_obj.configuration_information3;
1312
1313
1314 ELSIF l_template_obj.configuration_information1 = 'Entertainment Allowance'
1315 THEN
1316 l_template_obj.configuration_information2 := 'N'; -- No Exp Projections
1317 l_template_obj.configuration_information5 := 'N'; -- No Exp Element
1318 l_template_obj.configuration_information7 := 'Y'; -- Is Entertainment
1319 l_template_obj.configuration_information9 := l_template_obj.configuration_information3;
1320
1321 END IF;
1322
1323 END IF ;
1324 END IF ;
1325
1326 /*
1327 For Perquisites except for Other Perquisites, the Taxable should be
1328 defaulted to ALL
1329 */
1330
1331
1332 pay_in_utils.set_location(g_debug,l_procedure,130);
1333
1334 IF l_template_obj.element_classification = 'Perquisites' THEN
1335 pay_in_utils.set_location(g_debug,l_procedure,140);
1336 /* Set Projection exclusion rule as per the Processing Type */
1337 IF l_template_obj.processing_type = 'R' and l_template_obj.configuration_information2 = 'Y' THEN
1338 pay_in_utils.set_location(g_debug,l_procedure,150);
1339 l_template_obj.configuration_information2 := 'Y'; -- Projections
1340 ELSE
1341 pay_in_utils.set_location(g_debug,l_procedure,160);
1342 l_template_obj.configuration_information2 := 'N'; -- No Projections
1343 END IF ;
1344
1345 IF l_template_obj.configuration_information1 IN ('Club Expenditure','Credit Cards') --Club and Car Perqs will have an additional input Official Purpose Expense
1346 THEN
1347 l_template_obj.configuration_information5 := 'Y';
1348 ELSE
1349 l_template_obj.configuration_information5 := 'N';
1350 END IF ;
1351
1352 -- Since defaulting is not happening, we set the values explicitly
1353 IF l_template_obj.configuration_info_category <> 'IN Other Perquisites'
1354 THEN
1355 l_template_obj.configuration_information1 :=
1356 REPLACE(l_template_obj.configuration_info_category,
1357 pay_in_etw_struct.g_legislation_code||' ');
1358 l_template_obj.configuration_information3 := REPLACE(l_template_obj.configuration_information3
1359 , 'N', 'ALL');
1360 END IF ;
1361
1362
1363 END IF ;
1364 pay_in_utils.set_location(g_debug,l_procedure,170);
1365 IF l_template_obj.element_classification = 'Earnings' THEN
1366 pay_in_utils.set_location(g_debug,l_procedure,180);
1367 /* For Recurring Earnings if Processing Type is Recurring,
1368 Projections are enabled */
1369
1370 IF l_template_obj.configuration_info_category = 'IN Earnings' THEN
1371 pay_in_utils.set_location(g_debug,l_procedure,190);
1372
1373 IF l_template_obj.processing_type = 'R' THEN
1374 l_template_obj.configuration_information1 := 'Y';
1375 IF l_template_obj.configuration_information5 = 'Y' THEN
1376 l_template_obj.configuration_information13 := 'Y';
1377 ELSE
1378 l_template_obj.configuration_information13 := 'N';
1379 END IF ;
1380 ELSE
1381 l_template_obj.configuration_information1 := 'N';
1382 END IF ;
1383
1384 END IF;
1385
1386 IF l_template_obj.configuration_info_category = 'IN Leave Travel Concession'
1387 THEN
1388 /* LTC is always non-recurring element entry */
1389 pay_in_utils.set_location(g_debug,l_procedure,190);
1390 l_template_obj.processing_type := 'N';
1391 END IF;
1392
1393 END IF ;
1394
1395 /*
1396 For Medical Fringe Benefits we need to set the Exclusion Rule
1397 */
1398 g_debug := hr_utility.debug_enabled;
1399
1400 pay_in_utils.set_location(g_debug,l_procedure,200);
1401 IF l_template_obj.element_classification = 'Fringe Benefits' THEN
1402 pay_in_utils.set_location(g_debug,l_procedure,210);
1403
1404 IF l_template_obj.configuration_information1 = 'Superannuation Fund' THEN
1405 pay_in_utils.set_location(g_debug,l_procedure,215);
1406 l_template_obj.configuration_information5 := 'Y';
1407 END IF ;
1408
1409 /* Set Medical exclusion rule as per the user input */
1410 pay_in_utils.set_location(g_debug,l_procedure,220);
1411 IF l_template_obj.configuration_information1 <> 'Employees Welfare Expense' THEN
1412 pay_in_utils.set_location(g_debug,l_procedure,230);
1413 l_template_obj.configuration_information3 := 'N'; -- Override Medical
1414 ELSE
1415 IF l_template_obj.configuration_information3 = 'Y' THEN
1416 IF l_template_obj.processing_type = 'R' THEN
1417 pay_in_utils.set_location(g_debug,l_procedure,231);
1418 l_template_obj.configuration_information4 := 'Y';
1419 END IF ;
1420 END IF ;
1421 END IF ;
1422
1423 END IF ;
1424
1425 pay_in_utils.set_location(g_debug,l_procedure,240);
1426
1427 IF l_template_obj.element_classification = 'Employer Charges' THEN
1428 pay_in_utils.set_location(g_debug,l_procedure,250);
1429 /* For Employer Charges if Processing Type is Recurring,
1430 Projections are enabled*/
1431 pay_in_utils.set_location(g_debug,l_procedure,260);
1432 IF l_template_obj.processing_type = 'R' THEN
1433 l_template_obj.configuration_information1 := 'Y';
1434 ELSE
1435 l_template_obj.configuration_information1 := 'N';
1436 END IF ;
1437 END IF ;
1438
1439
1440
1441 IF g_debug THEN
1442 pay_in_utils.trace('Classification ',l_template_obj.element_classification);
1443 pay_in_utils.trace('Processing Type ',l_template_obj.processing_type);
1444 pay_in_utils.trace('Conf Info Catg ',l_template_obj.configuration_info_category);
1445 pay_in_utils.trace('Conf Info 1 ',l_template_obj.configuration_information1);
1446 pay_in_utils.trace('Conf Info 2 ',l_template_obj.configuration_information2);
1447 pay_in_utils.trace('Conf Info 3 ',l_template_obj.configuration_information3);
1448 pay_in_utils.trace('Conf Info 4 ',l_template_obj.configuration_information4);
1449 pay_in_utils.trace('Conf Info 5 ',l_template_obj.configuration_information5);
1450 pay_in_utils.trace('Conf Info 6 ',l_template_obj.configuration_information6);
1451 pay_in_utils.trace('Conf Info 7 ',l_template_obj.configuration_information7);
1452 pay_in_utils.trace('Conf Info 8 ',l_template_obj.configuration_information8);
1453 pay_in_utils.trace('Conf Info 9 ',l_template_obj.configuration_information9);
1454 pay_in_utils.trace('Conf Info 10 ',l_template_obj.configuration_information10);
1455
1456 END IF;
1457 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,240);
1458 RETURN l_template_obj;
1459 EXCEPTION
1460 WHEN OTHERS THEN
1461 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure,250);
1462 l_message := pay_in_utils.get_pay_message
1463 ('PER_IN_ORACLE_GENERIC_ERROR',
1464 'FUNCTION:'||l_procedure,
1465 'SQLERRMC:'||SQLERRM);
1466 pay_in_utils.trace('SQLERRM',l_message);
1467 RAISE ;
1468
1469 END element_template_pre_process;
1470
1471 --------------------------------------------------------------------------
1472 -- Name : ELEMENT_TEMPLATE_UPD_USER_STRU --
1473 -- Type : PROCEDURE --
1474 -- Access : Public --
1475 -- Description : Procedure to initialize the elements for ETW --
1476 -- Parameters : --
1477 -- IN : p_template_id NUMBER --
1478 -- OUT : N/A --
1479 -- RETURN : N/A --
1480 --------------------------------------------------------------------------
1481 PROCEDURE element_template_upd_user_stru
1482 (p_template_id IN NUMBER)
1483
1484
1485 IS
1486 l_procedure VARCHAR2(100):= g_package||'element_template_upd_user_stru';
1487
1488 BEGIN
1489
1490 NULL;
1491
1492 END element_template_upd_user_stru;
1493
1494 --------------------------------------------------------------------------
1495 -- Name : ELEMENT_TEMPLATE_POST_PROCESS --
1496 -- Type : PROCEDURE --
1497 -- Access : Public --
1498 -- Description : Procedure to initialize the elements for ETW --
1499 -- Parameters : --
1500 -- IN : p_template_id NUMBER --
1501 -- OUT : N/A --
1502 -- RETURN : N/A --
1503 --------------------------------------------------------------------------
1504 PROCEDURE element_template_post_process(p_template_id IN NUMBER)
1505 IS
1506 l_procedure VARCHAR2(100):= g_package||'element_template_post_process';
1507
1508 CURSOR csr_set IS
1509 SELECT pet.element_type_id
1510 ,pet.business_group_id
1511 ,pet.effective_start_date
1512 ,pet.object_version_number
1513 ,pec.classification_name
1514 ,pet.element_name
1515 ,pet.reporting_name
1516 FROM pay_element_types_f pet
1517 ,pay_element_templates tmp
1518 ,pay_element_classifications pec
1519 WHERE pet.element_name = tmp.base_name
1520 AND tmp.template_id = p_template_id
1521 AND pet.classification_id = pec.classification_id;
1522
1523 CURSOR csr_ae_set(p_base_name VARCHAR2
1524 , p_business_group_id NUMBER) IS
1525 SELECT pet.element_type_id
1526 ,pet.object_version_number
1527 FROM pay_element_types_f pet
1528 ,pay_element_classifications pec
1529 WHERE pet.element_name = p_base_name || ' Paid MP'
1530 AND pet.classification_id = pec.classification_id
1531 AND pet.business_group_id = p_business_group_id
1532 AND pec.classification_name = 'Paid Monetary Perquisite'
1533 AND pec.legislation_code = 'IN';
1534
1535 l_element csr_set%ROWTYPE ;
1536
1537 l_template pay_etm_shd.g_rec_type;
1538 l_template_rec pay_in_etw_struct.t_template_setup_rec;
1539
1540 CURSOR csr_alwn_details (p_allowance_name IN VARCHAR2)
1541 IS
1542 SELECT catg.allowance_name
1543 ,catg.category_code
1544 ,exem.exemption_amount
1545 FROM pay_in_allowance_categories_v catg
1546 ,pay_in_allowance_max_exem_v exem
1547 WHERE catg.allowance_name = exem.allowance_name
1548 AND catg.allowance_name = p_allowance_name;
1549
1550 l_alwn_details csr_alwn_details%ROWTYPE;
1551
1552 CURSOR csr_sec_class (p_element_id IN VARCHAR2
1553 ,p_effective_date IN DATE )
1554 IS
1555 SELECT pec.classification_name
1556 FROM pay_sub_classification_rules_f pscr
1557 ,pay_element_classifications pec
1558 WHERE pscr.classification_id = pec.classification_id
1559 AND pec.parent_classification_id =
1560 (SELECT classification_id FROM pay_element_classifications
1561 WHERE classification_name = 'Perquisites'
1562 AND legislation_code = 'IN')
1563 AND element_type_id = p_element_id
1564 AND p_effective_date BETWEEN pscr.effective_start_date
1565 AND pscr.effective_end_date;
1566
1567 CURSOR csr_ae_type_id (p_element_name VARCHAR2
1568 ,p_business_group_id NUMBER
1569 ,p_effective_date DATE)
1570 IS
1571 SELECT element_type_id, object_version_number
1572 FROM pay_element_types_f
1573 WHERE element_name = p_element_name || ' Paid MP'
1574 AND business_group_id = p_business_group_id
1575 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1576
1577 l_sec_class pay_element_classifications.classification_name%TYPE ;
1578 l_exp_nature VARCHAR2(1);
1579
1580 l_et_start_date DATE ;
1581 l_et_end_date DATE ;
1582 l_comment_id NUMBER ;
1583 l_priority_warn BOOLEAN ;
1584 l_name_warn BOOLEAN ;
1585 l_change_warn BOOLEAN ;
1586
1587 l_st_start_date DATE ;
1588 l_st_end_date DATE ;
1589 l_st_ovn NUMBER ;
1590 l_st_warn BOOLEAN ;
1591
1592 l_balance_feed_id NUMBER ;
1593 l_bf_start_date DATE ;
1594 l_bf_end_date DATE ;
1595 l_bf_ovn NUMBER ;
1596 l_bf_warn BOOLEAN ;
1597
1598 l_et_name pay_element_types_f.element_name%TYPE ;
1599 l_cr_result BOOLEAN ;
1600
1601 l_element_id NUMBER ;
1602 l_input_value_id NUMBER ;
1603 l_rowid ROWID ;
1604 l_result_rule_id NUMBER ;
1605
1606 l_excl_rule_id pay_template_exclusion_rules.exclusion_rule_id%TYPE ;
1607 l_ff_column pay_template_exclusion_rules.flexfield_column%TYPE;
1608 l_excl_def_value pay_template_exclusion_rules.exclusion_value%TYPE ;
1609 l_excl_set_value pay_template_exclusion_rules.exclusion_value%TYPE ;
1610
1611 l_flx_val_set_id NUMBER;
1612
1613 l_ele_type_id NUMBER;
1614 l_object_version_number NUMBER;
1615 l_effective_start_date DATE;
1616 l_effective_end_date DATE;
1617 l_balance_feeds_warning BOOLEAN;
1618 l_processing_rules_warning BOOLEAN;
1619 l_et_id NUMBER;
1620 l_ovn NUMBER;
1621
1622 BEGIN
1623 g_debug := hr_utility.debug_enabled;
1624
1625 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1626
1627 OPEN csr_set;
1628 FETCH csr_set INTO l_element;
1629 CLOSE csr_set;
1630
1631 pay_in_utils.set_location(g_debug,l_procedure,20);
1632 get_element_template(p_template_id, l_template);
1633
1634 IF l_element.classification_name = 'Allowances' THEN
1635
1636 pay_in_utils.set_location(g_debug,l_procedure,30);
1637 OPEN csr_alwn_details(l_template.configuration_information1);
1638 FETCH csr_alwn_details
1639 INTO l_alwn_details;
1640 CLOSE csr_alwn_details;
1641
1642 IF (l_template.configuration_info_category = 'IN Fixed Allowance') THEN
1643
1644 SELECT DECODE(l_template.configuration_information2,
1645 'Y','R',l_template.configuration_information2)
1646 INTO l_exp_nature
1647 FROM dual;
1648 ELSE /* IN Actual Expense Allowances */
1649 IF(l_template.configuration_information2 = 'Y') THEN
1650 UPDATE pay_element_templates
1651 SET configuration_information2 = 'N'
1652 WHERE template_id = p_template_id;
1653 l_template.configuration_information2 := 'N';
1654 END IF;
1655 l_exp_nature := l_template.configuration_information2;
1656
1657 END IF;
1658
1659 pay_in_utils.set_location(g_debug,l_procedure,40);
1660 pay_element_types_api.update_element_type
1661 (
1662 p_effective_date => l_element.effective_start_date
1663 , p_datetrack_update_mode => hr_api.g_correction
1664 , p_element_type_id => l_element.element_type_id
1665 , p_object_version_number => l_element.object_version_number
1666 , p_element_information_category => UPPER(pay_in_etw_struct.g_legislation_code||'_'||
1667 l_element.classification_name)
1668 , p_element_information1 => l_alwn_details.allowance_name
1669 , p_element_information2 => l_alwn_details.category_code
1670 , p_element_information3 => l_alwn_details.exemption_amount
1671 , p_element_information4 => l_exp_nature
1672 , p_effective_start_date => l_et_start_date
1673 , p_effective_end_date => l_et_end_date
1674 , p_comment_id => l_comment_id
1675 , p_processing_priority_warning => l_priority_warn
1676 , p_element_name_warning => l_name_warn
1677 , p_element_name_change_warning => l_change_warn
1678 );
1679
1680 IF l_template.configuration_information1 = 'House Rent Allowance' THEN
1681
1682 pay_in_utils.del_form_res_rule
1683 (p_element_type_id => l_element.element_type_id
1684 ,p_effective_date => l_element.effective_start_date
1685 );
1686
1687 --
1688 -- Delete Balance Feeds
1689 --
1690 pay_in_utils.delete_balance_feeds
1691 (p_balance_name => 'Taxable Allowances for Projection'
1692 ,p_element_name => l_element.element_name
1693 ,p_input_value_name => 'Standard Taxable Value'
1694 ,p_effective_date => l_element.effective_start_date
1695 );
1696
1697 pay_in_utils.delete_balance_feeds
1698 (p_balance_name => 'Taxable Allowances'
1699 ,p_element_name => l_element.element_name
1700 ,p_input_value_name => 'Taxable Value'
1701 ,p_effective_date => l_element.effective_start_date
1702 );
1703 --
1704 -- Delete input values : Allowance Amount, Taxable Value, Standard Taxable Value
1705 --
1706
1707 DELETE FROM pay_input_values_f
1708 WHERE element_type_id = l_element.element_type_id
1709 AND NAME IN ('Allowance Amount','Taxable Value','Standard Taxable Value')
1710 AND l_element.effective_start_date BETWEEN effective_start_date AND effective_end_date;
1711
1712 END IF ;
1713
1714 ELSIF l_element.classification_name = 'Perquisites' THEN
1715
1716 pay_in_utils.set_location(g_debug,l_procedure,50);
1717 pay_element_types_api.update_element_type
1718 (
1719 p_effective_date => l_element.effective_start_date
1720 , p_datetrack_update_mode => hr_api.g_correction
1721 , p_element_type_id => l_element.element_type_id
1722 , p_object_version_number => l_element.object_version_number
1723 , p_element_information_category => UPPER(pay_in_etw_struct.g_legislation_code||'_'||
1724 l_element.classification_name)
1725 , p_element_information1 => l_template.configuration_information1
1726 , p_element_information6 => l_template.configuration_information3
1727 , p_effective_start_date => l_et_start_date
1728 , p_effective_end_date => l_et_end_date
1729 , p_comment_id => l_comment_id
1730 , p_processing_priority_warning => l_priority_warn
1731 , p_element_name_warning => l_name_warn
1732 , p_element_name_change_warning => l_change_warn
1733 );
1734
1735
1736 OPEN csr_sec_class(l_element.element_type_id, l_element.effective_start_date);
1737 LOOP
1738 FETCH csr_sec_class INTO l_sec_class;
1739 EXIT WHEN csr_sec_class%NOTFOUND ;
1740 pay_in_utils.trace('Secondary Classification',l_sec_class);
1741
1742 IF l_sec_class = 'Monetary Perquisite' THEN
1743 pay_balance_feeds_api.create_balance_feed
1744 (
1745 p_effective_date => l_element.effective_start_date
1746 ,p_balance_type_id => pay_in_utils.get_balance_type_id
1747 ('ER Taxable Monetary Perquisite')
1748 ,p_input_value_id => pay_in_utils.get_input_value_id
1749 (l_element.effective_start_date,
1750 l_element.element_type_id,
1751 'Employer Taxable Amount')
1752 ,p_scale => 1
1753 ,p_business_group_id => l_element.business_group_id
1754 ,p_balance_feed_id => l_balance_feed_id
1755 ,p_effective_start_date => l_bf_start_date
1756 ,p_effective_end_date => l_bf_end_date
1757 ,p_object_version_number => l_bf_ovn
1758 ,p_exist_run_result_warning => l_bf_warn
1759 );
1760
1761 ELSIF l_sec_class = 'Non Monetary Perquisite' THEN
1762 pay_balance_feeds_api.create_balance_feed
1763 (
1764 p_effective_date => l_element.effective_start_date
1765 ,p_balance_type_id => pay_in_utils.get_balance_type_id
1766 ('ER Taxable Non Monetary Perquisite')
1767 ,p_input_value_id => pay_in_utils.get_input_value_id
1768 (l_element.effective_start_date,
1769 l_element.element_type_id,
1770 'Employer Taxable Amount')
1771 ,p_scale => 1
1772 ,p_business_group_id => l_element.business_group_id
1773 ,p_balance_feed_id => l_balance_feed_id
1774 ,p_effective_start_date => l_bf_start_date
1775 ,p_effective_end_date => l_bf_end_date
1776 ,p_object_version_number => l_bf_ovn
1777 ,p_exist_run_result_warning => l_bf_warn
1778 );
1779 END IF ;
1780
1781 END LOOP ;
1782 CLOSE csr_sec_class;
1783
1784 OPEN csr_ae_set(l_element.element_name, l_element.business_group_id);
1785 FETCH csr_ae_set INTO l_ele_type_id, l_object_version_number;
1786 CLOSE csr_ae_set;
1787
1788 IF (l_ele_type_id IS NOT NULL)AND((l_sec_class = 'Non Monetary Perquisite') OR
1789 (l_sec_class = 'Monetary Perquisite' AND
1790 l_template.configuration_information4 = 'N')) THEN
1791 pay_element_types_api.delete_element_type
1792 (p_validate => FALSE
1793 ,p_effective_date => l_element.effective_start_date
1794 ,p_datetrack_delete_mode => hr_api.g_zap
1795 ,p_element_type_id => l_ele_type_id
1796 ,p_object_version_number => l_object_version_number
1797 ,p_effective_start_date => l_effective_start_date
1798 ,p_effective_end_date => l_effective_end_date
1799 ,p_balance_feeds_warning => l_balance_feeds_warning
1800 ,p_processing_rules_warning => l_processing_rules_warning
1801 );
1802 END IF;
1803
1804 IF (l_sec_class = 'Monetary Perquisite' AND
1805 l_template.configuration_information4 = 'Y') THEN
1806
1807 IF (l_element.reporting_name IS NOT NULL) THEN
1808 l_et_start_date := NULL;
1809 l_et_end_date := NULL;
1810 l_comment_id := NULL;
1811 l_priority_warn := NULL;
1812 l_name_warn := NULL;
1813 l_change_warn := NULL;
1814
1815 OPEN csr_ae_type_id(l_element.element_name
1816 ,l_element.business_group_id
1817 ,l_element.effective_start_date);
1818 FETCH csr_ae_type_id INTO l_et_id, l_ovn;
1819 CLOSE csr_ae_type_id;
1820 /*
1821 SELECT element_type_id, object_version_number
1822 INTO l_et_id, l_ovn
1823 FROM pay_element_types_f
1824 WHERE element_name = l_element.element_name || ' Paid MP'
1825 AND business_group_id = l_element.business_group_id
1826 AND l_element.effective_start_date between effective_start_date and effective_end_date;
1827 */
1828 pay_element_types_api.update_element_type
1829 (
1830 p_effective_date => l_element.effective_start_date
1831 , p_datetrack_update_mode => hr_api.g_correction
1832 , p_element_type_id => l_et_id
1833 , p_object_version_number => l_ovn
1834 , p_reporting_name => l_element.reporting_name || ' Paid MP'
1835 , p_once_each_period_flag => 'N'
1836 , p_effective_start_date => l_et_start_date
1837 , p_effective_end_date => l_et_end_date
1838 , p_comment_id => l_comment_id
1839 , p_processing_priority_warning => l_priority_warn
1840 , p_element_name_warning => l_name_warn
1841 , p_element_name_change_warning => l_change_warn
1842 );
1843 END IF;
1844 END IF;
1845
1846 END IF ;
1847
1848 pay_in_utils.set_location(g_debug,l_procedure,60);
1849 pay_in_etw_struct.init_code;
1850
1851 pay_in_utils.set_location(g_debug,l_procedure,70);
1852 get_template
1853 (p_template_name => l_template.template_name
1854 ,p_template_rec => l_template_rec
1855 );
1856
1857 pay_in_utils.set_location(g_debug,l_procedure,80);
1858
1859 IF (l_template.template_name = 'Leave Travel Concession')
1860 THEN
1861 l_input_value_id := pay_in_utils.get_input_value_id(l_element.effective_start_date
1862 ,l_element.element_type_id
1863 ,'LTC Journey Block'
1864 );
1865
1866 SELECT flex_value_set_id
1867 INTO l_flx_val_set_id
1868 FROM fnd_flex_value_sets
1869 WHERE flex_value_set_name = 'PER_IN_LTC_BLOCK';
1870
1871 IF g_debug THEN
1872 pay_in_utils.trace('Input Value Id ',TO_CHAR(l_input_value_id));
1873 pay_in_utils.trace('Flex Value Set ID ',TO_CHAR(l_flx_val_set_id));
1874 END IF;
1875
1876 UPDATE pay_input_values_f
1877 SET value_set_id = l_flx_val_set_id
1878 WHERE input_value_id = l_input_value_id
1879 AND l_element.effective_start_date BETWEEN effective_start_date AND effective_end_date;
1880
1881 END IF;
1882
1883 IF l_template_rec.sf_setup.formula_name IS NOT NULL THEN
1884
1885 pay_in_utils.set_location(g_debug,l_procedure,90);
1886 pay_status_processing_rule_api.create_status_process_rule
1887 (
1888 p_effective_date => l_element.effective_start_date
1889 ,p_element_type_id => l_element.element_type_id
1890 ,p_business_group_id => l_element.business_group_id
1891 ,p_formula_id => pay_in_utils.get_formula_id(l_element.effective_start_date,
1892 l_template_rec.sf_setup.formula_name)
1893 ,p_status_processing_rule_id => l_template_rec.sf_setup.status_rule_id
1894 ,p_effective_start_date => l_st_start_date
1895 ,p_effective_end_date => l_st_end_date
1896 ,p_object_version_number => l_st_ovn
1897 ,p_formula_mismatch_warning => l_st_warn
1898 );
1899
1900 FOR i IN 1..l_template_rec.sf_setup.frs_setup.COUNT
1901 LOOP
1902 pay_in_utils.set_location(g_debug,l_procedure,100);
1903
1904
1905 IF (l_template.template_name = 'Other Perquisites' OR
1906 l_template.template_name = 'Free Education' OR
1907 l_template.template_name = 'Company Accommodation') AND
1908 l_sec_class = 'Monetary Perquisite' AND
1909 l_template.configuration_information4 = 'Y' AND
1910 l_template_rec.sf_setup.frs_setup(i).result_name = 'FED_TO_NET_PAY' THEN
1911 l_template_rec.sf_setup.frs_setup(i).element_name := l_element.element_name || ' Paid MP';
1912 l_template_rec.sf_setup.frs_setup(i).input_value_name := 'Pay Value';
1913 END IF;
1914
1915
1916 IF g_debug THEN
1917 pay_in_utils.trace('===================================','================');
1918 pay_in_utils.trace('result_name ',l_template_rec.sf_setup.frs_setup(i).result_name);
1919 pay_in_utils.trace('result_rule_type ',l_template_rec.sf_setup.frs_setup(i).result_rule_type);
1920 pay_in_utils.trace('input_value_name ',l_template_rec.sf_setup.frs_setup(i).input_value_name);
1921 pay_in_utils.trace('element_name ',l_template_rec.sf_setup.frs_setup(i).element_name);
1922 pay_in_utils.trace('severity_level ',l_template_rec.sf_setup.frs_setup(i).severity_level);
1923 pay_in_utils.trace('exclusion_tag ',l_template_rec.sf_setup.frs_setup(i).exclusion_tag);
1924 pay_in_utils.trace('===================================','================');
1925 END IF;
1926
1927 -- Check for Exclusions
1928 l_cr_result := TRUE ;
1929 IF l_template_rec.sf_setup.frs_setup(i).exclusion_tag IS NOT NULL THEN
1930 pay_in_utils.set_location(g_debug,l_procedure,110);
1931
1932 FOR j IN 1..l_template_rec.er_setup.COUNT
1933 LOOP
1934 pay_in_utils.trace('===================================','================');
1935 pay_in_utils.trace('ff_column',l_template_rec.er_setup(j).ff_column);
1936 pay_in_utils.trace('value',l_template_rec.er_setup(j).value);
1937 pay_in_utils.trace('rule_id=',l_template_rec.er_setup(j).rule_id);
1938 pay_in_utils.trace('===================================','================');
1939
1940 IF l_template_rec.er_setup(j).tag = l_template_rec.sf_setup.frs_setup(i).exclusion_tag
1941 THEN
1942 pay_in_utils.set_location(g_debug,l_procedure,120);
1943
1944 l_excl_def_value := l_template_rec.er_setup(j).value;
1945 l_ff_column := l_template_rec.er_setup(j).ff_column;
1946 pay_in_utils.trace('l_excl_def_value',l_excl_def_value);
1947
1948 EXIT ;
1949 END IF ;
1950 END LOOP ;
1951
1952 pay_in_utils.set_location(g_debug,l_procedure,130);
1953 SELECT DECODE(l_ff_column,
1954 'CONFIGURATION_INFORMATION2',l_template.configuration_information2,
1955 'CONFIGURATION_INFORMATION3',l_template.configuration_information3,
1956 'CONFIGURATION_INFORMATION4',l_template.configuration_information4,
1957 'CONFIGURATION_INFORMATION5',l_template.configuration_information5,
1958 'CONFIGURATION_INFORMATION6',l_template.configuration_information6,
1959 'CONFIGURATION_INFORMATION7',l_template.configuration_information7,
1960 'CONFIGURATION_INFORMATION8',l_template.configuration_information8,
1961 'CONFIGURATION_INFORMATION9',l_template.configuration_information9,
1962 'CONFIGURATION_INFORMATION10',l_template.configuration_information10)
1963 INTO l_excl_set_value
1964 FROM dual ;
1965 pay_in_utils.trace('l_excl_set_value',l_excl_set_value);
1966
1967 IF l_excl_set_value = l_excl_def_value THEN
1968 /* if the two values are different, then we need to create the result */
1969 l_cr_result := FALSE;
1970 END IF ;
1971
1972 END IF ;
1973
1974 /* At this stage we are aware of whether we want to create the result or not */
1975 IF l_cr_result THEN
1976 pay_in_utils.set_location(g_debug,l_procedure,140);
1977
1978 IF (l_template_rec.sf_setup.frs_setup(i).result_rule_type = 'I' AND
1979 l_template_rec.sf_setup.frs_setup(i).element_name IS NULL) THEN
1980 NULL;
1981 ELSE
1982 pay_in_utils.ins_form_res_rule
1983 (
1984 p_business_group_id => l_element.business_group_id
1985 ,p_effective_date => l_element.effective_start_date
1986 ,p_status_processing_rule_id => l_template_rec.sf_setup.status_rule_id
1987 ,p_input_value_name => l_template_rec.sf_setup.frs_setup(i).input_value_name
1988 ,p_element_name => l_template_rec.sf_setup.frs_setup(i).element_name
1989 ,p_result_name => l_template_rec.sf_setup.frs_setup(i).result_name
1990 ,p_result_rule_type => l_template_rec.sf_setup.frs_setup(i).result_rule_type
1991 ,p_severity_level => l_template_rec.sf_setup.frs_setup(i).severity_level
1992 ,p_element_type_id => l_element.element_type_id
1993 );
1994 END IF;
1995
1996 END IF ;
1997
1998 END LOOP ;
1999
2000 END IF ;
2001
2002 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,170);
2003 END element_template_post_process;
2004
2005 --------------------------------------------------------------------------
2006 -- Name : DELETE_PRE_PROCESS --
2007 -- Type : PROCEDURE --
2008 -- Access : Public --
2009 -- Description : Procedure to initialize the elements for ETW --
2010 -- Parameters : --
2011 -- IN : p_template_id NUMBER --
2012 -- OUT : N/A --
2013 -- RETURN : N/A --
2014 --------------------------------------------------------------------------
2015 PROCEDURE delete_pre_process
2016 (p_template_id IN NUMBER)
2017 IS
2018
2019 CURSOR csr_et IS
2020 SELECT pet.element_type_id
2021 , pet.effective_start_date
2022 FROM pay_element_types_f pet,
2023 pay_shadow_element_types pset
2024 WHERE pset.template_id = p_template_id
2025 AND pset.element_name = pet.element_name;
2026
2027 l_procedure CONSTANT VARCHAR2(100) := g_package ||'delete_pre_process';
2028
2029 BEGIN
2030 g_debug := hr_utility.debug_enabled;
2031 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2032
2033 FOR i IN csr_et
2034 LOOP
2035 pay_in_utils.set_location(g_debug,l_procedure,20);
2036 IF g_debug THEN
2037 pay_in_utils.trace('Element Type Id ',i.element_type_id);
2038 pay_in_utils.trace('Effective Date ',to_char(i.effective_start_date,'DD-Mon-YYYY'));
2039 END IF ;
2040
2041 pay_in_utils.del_form_res_rule(i.element_type_id, i.effective_start_date);
2042
2043 END LOOP ; -- csr_et ends
2044
2045 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,100);
2046
2047
2048 EXCEPTION
2049 WHEN OTHERS THEN
2050 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,120);
2051 pay_in_utils.trace('SQL Code ',SQLCODE);
2052 pay_in_utils.trace('SQL Code ',SQLERRM);
2053 RAISE ;
2054 END delete_pre_process;
2055
2056 END pay_in_element_template_pkg;