[Home] [Help]
PACKAGE BODY: APPS.PAY_AE_ELEMENT_TEMPLATE_PKG
Source
1 PACKAGE BODY pay_ae_element_template_pkg AS
2 /* $Header: pyaeeltm.pkb 120.19 2006/03/23 02:09:44 abppradh noship $ */
3 ------------------------------------------------------------------------
4 ------------------------------------------------------------------------
5 -- Function get_rate_from_tab_id
6 -- This function is used to obtain rate value from rate table id.
7 ------------------------------------------------------------------------
8 ------------------------------------------------------------------------
9 FUNCTION get_rate_from_tab_id
10 (p_assignment_id IN NUMBER
11 ,p_date_earned IN DATE
12 ,p_business_group_id IN NUMBER
13 ,p_rate_id IN NUMBER)
14 RETURN NUMBER AS
15 CURSOR csr_get_grade IS
16 SELECT grade_id
17 FROM per_all_assignments_f
18 WHERE assignment_id = p_assignment_id
19 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
20 l_grade_id NUMBER;
21
22 CURSOR csr_get_grade_value IS
23 SELECT pg.value
24 FROM pay_rates pr
25 ,pay_grade_rules_f pg
26 WHERE pr.rate_type= 'G'
27 AND pr.rate_id = p_rate_id
28 AND pr.rate_id = pg.rate_id
29 AND pg.grade_or_spinal_point_id = l_grade_id
30 AND pg.rate_type = 'G'
31 AND pg.business_group_id = p_business_group_id
32 AND pr.business_group_id = p_business_group_id
33 AND p_date_earned BETWEEN pg.effective_start_date AND pg.effective_end_date;
34 l_value NUMBER;
35
36 BEGIN
37 l_value := 0;
38 OPEN csr_get_grade;
39 FETCH csr_get_grade INTO l_grade_id;
40 CLOSE csr_get_grade;
41
42 OPEN csr_get_grade_value;
43 FETCH csr_get_grade_value INTO l_value;
44 CLOSE csr_get_grade_value;
45
46 RETURN l_value;
47
48 END get_rate_from_tab_id;
49
50 ------------------------------------------------------------------------
51 ------------------------------------------------------------------------
52 -- Function get_rate_from_tab_name
53 -- This function is used to obtain rate value from rate table name.
54 ------------------------------------------------------------------------
55 ------------------------------------------------------------------------
56 FUNCTION get_rate_from_tab_name
57 (p_assignment_id IN NUMBER
58 ,p_date_earned IN DATE
59 ,p_business_group_id IN NUMBER
60 ,p_rate_table IN VARCHAR2
61 ,p_table_exists OUT NOCOPY VARCHAR2)
62 RETURN NUMBER AS
63 CURSOR csr_get_grade IS
64 SELECT grade_id
65 FROM per_all_assignments_f
66 WHERE assignment_id = p_assignment_id
67 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
68 l_grade_id NUMBER;
69
70 CURSOR csr_chk_grade_table IS
71 SELECT 'Y'
72 FROM pay_rates pr
73 WHERE pr.rate_type = 'G'
74 AND pr.name =p_rate_table
75 AND pr.business_group_id = p_business_group_id;
76
77
78 CURSOR csr_get_grade_value IS
79 SELECT pg.value
80 FROM pay_rates pr
81 ,pay_grade_rules_f pg
82 WHERE pr.rate_type= 'G'
83 AND pr.name = p_rate_table
84 AND pr.rate_id = pg.rate_id
85 AND pg.grade_or_spinal_point_id = l_grade_id
86 AND pg.rate_type = 'G'
87 AND pg.business_group_id = p_business_group_id
88 AND pr.business_group_id = p_business_group_id
89 AND p_date_earned BETWEEN pg.effective_start_date AND pg.effective_end_date;
90 l_value NUMBER;
91 l_exist VARCHAR2(10);
92
93 BEGIN
94 l_grade_id := NULL;
95 l_value := 0;
96 l_exist := 'N';
97 OPEN csr_chk_grade_table;
98 FETCH csr_chk_grade_table INTO l_exist;
99 CLOSE csr_chk_grade_table;
100
101 IF l_exist ='Y' THEN
102 p_table_exists := 'Y';
103 ELSE
104 p_table_exists := 'N';
105 END IF;
106
107 OPEN csr_get_grade;
108 FETCH csr_get_grade INTO l_grade_id;
109 CLOSE csr_get_grade;
110
111 IF l_grade_id IS NULL THEN
112 --p_table_exists := 'N';
113 l_value := 0;
114 END IF;
115
116 OPEN csr_get_grade_value;
117 FETCH csr_get_grade_value INTO l_value;
118 CLOSE csr_get_grade_value;
119
120 RETURN l_value;
121
122 END get_rate_from_tab_name;
123
124 ------------------------------------------------------------------------
125 ------------------------------------------------------------------------
126 -- Function get_absence_days
127 -- This function is used to obtain the number of unpaid leaves in a
128 -- payroll period (used in element template for Unpaid Leave Deduction)
129 ------------------------------------------------------------------------
130 ------------------------------------------------------------------------
131 FUNCTION get_absence_days
132 (p_assignment_id IN NUMBER
133 ,p_date_earned IN DATE
134 ,p_business_group_id IN NUMBER
135 ,p_start_date IN DATE
136 ,p_end_date IN DATE)
137 RETURN NUMBER AS
138
139 CURSOR csr_get_day_range IS
140 SELECT paa.date_start start_date
141 ,paa.date_end end_date
142 FROM per_absence_attendances paa
143 ,per_absence_attendance_types paat
144 ,per_all_assignments_f asg
145 WHERE paat.absence_category ='UL'
146 AND paat.business_group_id = paa.business_group_id
147 AND paat.business_group_id = p_business_group_id
148 AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
149 AND paa.person_id = asg.person_id
150 AND asg.assignment_id = p_assignment_id
151 AND (paa.date_start between p_start_date AND p_end_date
152 AND paa.date_end between p_start_date AND p_end_date)
153 UNION
154 SELECT paa.date_start start_date
155 ,p_end_date end_date
156 FROM per_absence_attendances paa
157 ,per_absence_attendance_types paat
158 ,per_all_assignments_f asg
159 WHERE paat.absence_category ='UL'
160 AND paat.business_group_id = paa.business_group_id
161 AND paat.business_group_id = p_business_group_id
162 AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
163 AND paa.person_id = asg.person_id
164 AND asg.assignment_id = p_assignment_id
165 AND (paa.date_start between p_start_date AND p_end_date
166 AND paa.date_end > p_end_date)
167 UNION
168 SELECT p_start_date start_date
169 ,paa.date_end end_date
170 FROM per_absence_attendances paa
171 ,per_absence_attendance_types paat
172 ,per_all_assignments_f asg
173 WHERE paat.absence_category ='UL'
174 AND paat.business_group_id = paa.business_group_id
175 AND paat.business_group_id = p_business_group_id
176 AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
177 AND paa.person_id = asg.person_id
178 AND asg.assignment_id = p_assignment_id
179 AND (paa.date_start < p_start_date
180 AND paa.date_end between p_start_date AND p_end_date)
181 UNION
182 SELECT p_start_date start_date
183 ,p_end_date end_date
184 FROM per_absence_attendances paa
185 ,per_absence_attendance_types paat
186 ,per_all_assignments_f asg
187 WHERE paat.absence_category ='UL'
188 AND paat.business_group_id = paa.business_group_id
189 AND paat.business_group_id = p_business_group_id
190 AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
191 AND paa.person_id = asg.person_id
192 AND asg.assignment_id = p_assignment_id
193 AND (paa.date_start < p_start_date
194 AND paa.date_end > p_end_date);
195 rec_get_day_range csr_get_day_range%ROWTYPE;
196 l_days NUMBER;
197 l_tot_days NUMBER;
198 l_f_stat NUMBER;
199
200 BEGIN
201 l_days := 0;
202 l_tot_days := 0;
203 l_f_stat := 0;
204 OPEN csr_get_day_range;
205 LOOP
206 FETCH csr_get_day_range INTO rec_get_day_range;
207 EXIT WHEN csr_get_day_range%NOTFOUND;
208 l_f_stat := hr_loc_work_schedule.calc_sch_based_dur
209 (p_assignment_id
210 ,'D'
211 ,'Y'
212 ,rec_get_day_range.start_date
213 ,rec_get_day_range.end_date
214 ,'0'
215 ,'23.59'
216 ,l_days);
217 l_tot_days := l_tot_days + l_days;
218 END LOOP;
219 CLOSE csr_get_day_range;
220
221 RETURN NVL(l_tot_days, 0);
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 l_tot_days := 0;
226 RETURN l_tot_days;
227
228 END get_absence_days;
229
230 ------------------------------------------------------------------------
231 ------------------------------------------------------------------------
232 -- Function get_employee_details
233 -- This function is used to obtain the employee details.
234 ------------------------------------------------------------------------
235 ------------------------------------------------------------------------
236 FUNCTION get_employee_details
237 (p_assignment_id IN NUMBER
238 ,p_date_earned IN DATE
239 ,p_info_type IN VARCHAR2)
240 RETURN VARCHAR2 AS
241 CURSOR csr_get_marital_status IS
242 SELECT marital_status
243 FROM per_all_people_f ppl
244 ,per_all_assignments_f asg
245 WHERE asg.assignment_id = p_assignment_id
246 AND ppl.person_id = asg.person_id
247 AND p_date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
248 AND p_date_earned BETWEEN ppl.effective_start_date AND ppl.effective_end_date;
249
250 CURSOR csr_get_dependent_children IS
251 SELECT COUNT(DISTINCT contact_person_id)
252 FROM per_contact_relationships pcr
253 ,per_all_assignments_f asg
254 WHERE asg.person_id = pcr.person_id
255 AND asg.assignment_id = p_assignment_id
256 AND pcr.contact_type = 'C'
257 AND nvl(pcr.dependent_flag, 'N') = 'Y'
258 AND p_date_earned BETWEEN NVL(pcr.date_start,p_date_earned) AND NVL(pcr.date_end, TO_DATE('4712/12/31','YYYY/MM/DD'));
259
260 l_marital_status VARCHAR2(80);
261 l_value VARCHAR2(100);
262 l_child_cnt NUMBER;
263
264 BEGIN
265 IF p_info_type = 'MARITAL_STATUS' THEN
266 l_marital_status := NULL;
267 OPEN csr_get_marital_status;
268 FETCH csr_get_marital_status INTO l_marital_status;
269 CLOSE csr_get_marital_status;
270
271 l_value := l_marital_status;
272 IF l_value IS NULL THEN
273 l_value := 'NO_DATA_FOUND';
274 END IF;
275 ELSIF p_info_type = 'DEPENDENT_CHILDREN' THEN
276 l_child_cnt := 0;
277 OPEN csr_get_dependent_children;
278 FETCH csr_get_dependent_children INTO l_child_cnt;
279 CLOSE csr_get_dependent_children;
280
281 l_value := l_child_cnt;
282 END IF;
283 RETURN l_value;
284
285 END get_employee_details;
286
287 ------------------------------------------------------------------------
288 ------------------------------------------------------------------------
289 -- Function element_template_post_process
290 -- This function is used to update input value with value set for hourly
291 -- salary and grade allowance template .
292 -- The function also creates balance feeds for information element of
293 -- Housing and Transport allowance template
294 ------------------------------------------------------------------------
295 ------------------------------------------------------------------------
296 PROCEDURE element_template_post_process
297 (p_template_id IN NUMBER) AS
298
299 CURSOR csr_get_template_info IS
300 SELECT base_name
301 ,business_group_id
302 ,template_name
303 FROM pay_element_templates
304 WHERE template_id = p_template_id
305 AND template_type = 'U';
306 rec_get_template_info csr_get_template_info%ROWTYPE;
307
308 CURSOR csr_get_element_type_id
309 (cp_business_group_id NUMBER
310 ,cp_element_name VARCHAR2) IS
311 SELECT element_type_id
312 FROM pay_element_types_f
313 WHERE business_group_id = cp_business_group_id
314 AND element_name = cp_element_name;
315
316 CURSOR csr_get_input_value_id (cp_name VARCHAR2
317 ,cp_element_type_id NUMBER) IS
318 SELECT input_value_id
319 ,effective_start_date
320 FROM pay_input_values_f
321 WHERE element_type_id = cp_element_type_id
322 AND name = cp_name;
323 rec_get_input_value_id csr_get_input_value_id%ROWTYPE;
324
325 CURSOR csr_get_value_set_id IS
326 SELECT flex_value_set_id
327 FROM fnd_flex_value_sets
328 WHERE flex_value_set_name = 'HR_AE_RATE_NAME';
329
330 CURSOR csr_get_valid_element_type_id
331 (cp_business_group_id NUMBER
332 ,cp_element_name VARCHAR2) IS
333 SELECT pet.element_type_id
334 FROM pay_element_types_f pet
335 ,pay_sub_classification_rules_f psc
336 ,pay_element_classifications pec
337 WHERE pet.business_group_id = cp_business_group_id
338 AND pet.element_name = cp_element_name
339 AND pet.element_type_id = psc.element_type_id
340 AND pet.business_group_id = psc.business_group_id
341 AND pec.classification_name = 'Subject to Social Insurance : Earnings'
342 AND pec.legislation_code = 'AE'
343 AND psc.classification_id = pec.classification_id;
344
345 CURSOR csr_get_info_element_det
346 (cp_business_group_id NUMBER
347 ,cp_element_name VARCHAR2) IS
348 SELECT element_type_id
349 ,effective_start_date
350 ,effective_end_date
351 FROM pay_element_types_f
352 WHERE business_group_id = cp_business_group_id
353 AND element_name = cp_element_name;
354 rec_get_info_element_det csr_get_info_element_det%ROWTYPE;
355
356 CURSOR csr_get_classification_id IS
357 SELECT classification_id
358 FROM pay_element_classifications pec
359 WHERE classification_name = 'Subject to Social Insurance : Information'
360 AND legislation_code = 'AE';
361
362 l_base_name pay_element_types_f.element_name%TYPE;
363 l_business_group_id NUMBER;
364 l_element_type_id NUMBER;
365 l_info_element_type_id NUMBER;
366 l_classification_id NUMBER;
367 l_input_value_id NUMBER;
368 l_template_name pay_element_templates.template_name%TYPE;
369 l_value_set_id NUMBER;
370 l_ov_number NUMBER;
371 l_effective_date DATE;
372 l_effective_start_date DATE;
373 l_effective_end_date DATE;
374 l_el_effective_start_date DATE;
375 l_el_effective_end_date DATE;
376 l_default_warning BOOLEAN;
377 l_min_max_warning BOOLEAN;
378 l_link_inp_val_warning BOOLEAN;
379 l_pay_basis_warning BOOLEAN;
380 l_formula_warning BOOLEAN;
381 l_assignment_id_warning BOOLEAN;
382 l_formula_message VARCHAR2(100);
383
384 BEGIN
385 hr_utility.trace('Entering pay_ae_element_template_pkg.element_template_post_process');
386
387 OPEN csr_get_template_info;
388 FETCH csr_get_template_info INTO rec_get_template_info;
389 l_base_name := rec_get_template_info.base_name;
390 l_business_group_id := rec_get_template_info.business_group_id;
391 l_template_name := rec_get_template_info.template_name;
392 CLOSE csr_get_template_info;
393
394 IF l_template_name IN ('Hourly Salary Template', 'Grade Allowance Template') THEN
395 OPEN csr_get_element_type_id (l_business_group_id
396 ,l_base_name);
397 FETCH csr_get_element_type_id INTO l_element_type_id;
398 CLOSE csr_get_element_type_id;
399
400 OPEN csr_get_input_value_id('Grade Rate',l_element_type_id);
401 FETCH csr_get_input_value_id INTO rec_get_input_value_id;
402 l_input_value_id := rec_get_input_value_id.input_value_id;
403 l_effective_date := rec_get_input_value_id.effective_start_date;
404 CLOSE csr_get_input_value_id;
405
406 OPEN csr_get_value_set_id;
407 FETCH csr_get_value_set_id INTO l_value_set_id;
408 CLOSE csr_get_value_set_id;
409
410 IF l_value_set_id is NOT NULL THEN
411 DECLARE
412 CURSOR csr_get_ovn IS
413 SELECT object_version_number
414 FROM pay_input_values_f
415 WHERE input_value_id = l_input_value_id;
416 BEGIN
417 OPEN csr_get_ovn;
418 FETCH csr_get_ovn INTO l_ov_number;
419 CLOSE csr_get_ovn;
420 END;
421
422 pay_input_value_api.update_input_value
423 (p_validate => FALSE
424 ,p_effective_date => l_effective_date
425 ,p_datetrack_mode => 'CORRECTION'
426 ,p_input_value_id => l_input_value_id
427 ,p_object_version_number => l_ov_number
428 ,p_value_set_id => l_value_set_id
429 ,p_effective_start_date => l_effective_start_date
430 ,p_effective_end_date => l_effective_end_date
431 ,p_default_val_warning => l_default_warning
432 ,p_min_max_warning => l_min_max_warning
433 ,p_link_inp_val_warning => l_link_inp_val_warning
434 ,p_pay_basis_warning => l_pay_basis_warning
435 ,p_formula_warning => l_formula_warning
436 ,p_assignment_id_warning => l_assignment_id_warning
437 ,p_formula_message => l_formula_message
438 );
439 END IF;
440 END IF;
441
442 /*Code for updating balance feed of secondary information element*/
443 IF l_template_name IN ('Housing Allowance Template') THEN
444 OPEN csr_get_valid_element_type_id (l_business_group_id
445 ,l_base_name);
446 FETCH csr_get_valid_element_type_id INTO l_element_type_id;
447 CLOSE csr_get_valid_element_type_id;
448
449 IF l_element_type_id IS NOT NULL THEN
450 OPEN csr_get_info_element_det (l_business_group_id
451 ,l_base_name||' Information');
452 FETCH csr_get_info_element_det INTO rec_get_info_element_det;
453 l_info_element_type_id := rec_get_info_element_det.element_type_id;
454 l_el_effective_start_date := rec_get_info_element_det.effective_start_date;
455 l_el_effective_end_date := rec_get_info_element_det.effective_end_date;
456 CLOSE csr_get_info_element_det;
457
458 OPEN csr_get_classification_id;
459 FETCH csr_get_classification_id INTO l_classification_id;
460 CLOSE csr_get_classification_id;
461
462 IF l_info_element_type_id IS NOT NULL and l_classification_id IS NOT NULL THEN
463 DECLARE
464 l_row_id VARCHAR2(30);
465 l_seq NUMBER;
466 BEGIN
467 l_row_id := NULL;
468 SELECT pay_sub_classification_rules_s.nextval
469 INTO l_seq
470 FROM dual;
471 pay_sub_class_rules_pkg.insert_row
472 ( p_rowid => l_row_id
473 ,p_sub_classification_rule_Id => l_seq
474 ,p_effective_start_date => l_el_effective_start_date
475 ,p_effective_end_date => l_el_effective_end_date
476 ,p_element_type_id => l_info_element_type_id
477 ,p_classification_id => l_classification_id
478 ,p_business_group_id => l_business_group_id
479 ,p_legislation_code => NULL
480 ,p_last_update_date => SYSDATE
481 ,p_last_updated_by => -1
482 ,p_last_update_login => -1
483 ,p_created_by => -1
484 ,p_creation_date => SYSDATE);
485 END;
486 END IF;
487 END IF;
488 END IF;
489
490 --Update the input value for Housing, Transport and Shift allowance template
491 IF l_template_name IN ('Housing Allowance Template', 'Transport Allowance Template','Shift Allowance Template') THEN
492 OPEN csr_get_element_type_id (l_business_group_id,l_base_name);
493 FETCH csr_get_element_type_id INTO l_element_type_id;
494 CLOSE csr_get_element_type_id;
495
496 OPEN csr_get_input_value_id('Override Amount',l_element_type_id);
497 FETCH csr_get_input_value_id INTO rec_get_input_value_id;
498 l_input_value_id := rec_get_input_value_id.input_value_id;
499 l_effective_date := rec_get_input_value_id.effective_start_date;
500 CLOSE csr_get_input_value_id;
501
502 DECLARE
503 CURSOR csr_get_ovn IS
504 SELECT object_version_number
505 FROM pay_input_values_f
506 WHERE input_value_id = l_input_value_id;
507 BEGIN
508 OPEN csr_get_ovn;
509 FETCH csr_get_ovn INTO l_ov_number;
510 CLOSE csr_get_ovn;
511 END;
512
513 pay_input_value_api.update_input_value
514 (p_validate => FALSE
515 ,p_effective_date => l_effective_date
516 ,p_datetrack_mode => 'CORRECTION'
517 ,p_input_value_id => l_input_value_id
518 ,p_object_version_number => l_ov_number
519 -- ,p_max_value => '0'
520 ,p_min_value => '0'
521 ,p_warning_or_error => 'E'
522 ,p_effective_start_date => l_effective_start_date
523 ,p_effective_end_date => l_effective_end_date
524 ,p_default_val_warning => l_default_warning
525 ,p_min_max_warning => l_min_max_warning
526 ,p_link_inp_val_warning => l_link_inp_val_warning
527 ,p_pay_basis_warning => l_pay_basis_warning
528 ,p_formula_warning => l_formula_warning
529 ,p_assignment_id_warning => l_assignment_id_warning
530 ,p_formula_message => l_formula_message
531 );
532
533 END IF;
534
535 --Update the input value for unpaid leave template
536 IF l_template_name IN ('Unpaid Leave Template') THEN
537 OPEN csr_get_element_type_id (l_business_group_id
538 ,l_base_name||' Arrears Payment');
539 FETCH csr_get_element_type_id INTO l_element_type_id;
540 CLOSE csr_get_element_type_id;
541
542 OPEN csr_get_input_value_id('Pay Value',l_element_type_id);
543 FETCH csr_get_input_value_id INTO rec_get_input_value_id;
544 l_input_value_id := rec_get_input_value_id.input_value_id;
545 l_effective_date := rec_get_input_value_id.effective_start_date;
546 CLOSE csr_get_input_value_id;
547
548 DECLARE
549 CURSOR csr_get_ovn IS
550 SELECT object_version_number
551 FROM pay_input_values_f
552 WHERE input_value_id = l_input_value_id;
553 BEGIN
554 OPEN csr_get_ovn;
555 FETCH csr_get_ovn INTO l_ov_number;
556 CLOSE csr_get_ovn;
557 END;
558
559 pay_input_value_api.update_input_value
560 (p_validate => FALSE
561 ,p_effective_date => l_effective_date
562 ,p_datetrack_mode => 'CORRECTION'
563 ,p_input_value_id => l_input_value_id
564 ,p_object_version_number => l_ov_number
565 ,p_max_value => '0'
566 ,p_warning_or_error => 'E'
567 ,p_effective_start_date => l_effective_start_date
568 ,p_effective_end_date => l_effective_end_date
569 ,p_default_val_warning => l_default_warning
570 ,p_min_max_warning => l_min_max_warning
571 ,p_link_inp_val_warning => l_link_inp_val_warning
572 ,p_pay_basis_warning => l_pay_basis_warning
573 ,p_formula_warning => l_formula_warning
574 ,p_assignment_id_warning => l_assignment_id_warning
575 ,p_formula_message => l_formula_message
576 );
577
578 END IF;
579
580
581 hr_utility.trace('Leaving pay_ae_element_template_pkg.element_template_post_process');
582
583 END element_template_post_process;
584
585 ------------------------------------------------------------------------
586 ------------------------------------------------------------------------
587 -- Procedure create_flat_amt_template
588 -- This proceudre is used to create a flat amount template
589 ------------------------------------------------------------------------
590 ------------------------------------------------------------------------
591 PROCEDURE create_flat_amt_template IS
592 --
593 TYPE Char80_Table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
594 t_dim Char80_Table;
595 --
596 l_template_id number;
597 l_defined_bal_id number;
598 l_effective_date date;
599 l_ovn number;
600 l_formula_id number;
601 l_rr_id number;
602 l_primary_bal_id number;
603 l_secondary_bal_id number;
604 l_flat_element_id number;
605 l_flat_pay_iv number;
606 l_flat_amt_iv number;
607 l_bal_feed_id number;
608 --
609 CURSOR c_template IS
610 SELECT template_id
611 FROM pay_element_templates
612 WHERE template_name = 'Flat Amount Template'
613 AND template_type = 'T';
614 --
615 BEGIN
616 ----------------------------------------------------------------------------
617 -- Delete the existing template
618 ----------------------------------------------------------------------------
619 FOR c_rec in c_template LOOP
620 l_template_id := c_rec.template_id;
621
622 DELETE FROM pay_ele_tmplt_class_usages
623 WHERE template_id = l_template_id;
624
625 pay_element_template_api.delete_user_structure
626 (p_validate => false
627 ,p_drop_formula_packages => true
628 ,p_template_id => l_template_id);
629 END LOOP;
630 --
631 l_effective_date := to_date('0001/01/01', 'YYYY/MM/DD');
632 ------------------------------------------------------------------------
633 -- SECTION1 :
634 ------------------------------------------------------------------------
635 pay_etm_ins.ins
636 (p_template_id => l_template_id
637 ,p_effective_date => l_effective_date
638 ,p_template_type => 'T'
639 ,p_template_name => 'Flat Amount Template'
640 ,p_base_processing_priority => 2500
641 ,p_max_base_name_length => 50
642 ,p_version_number => 1
643 ,p_legislation_code => 'AE'
644 ,p_object_version_number => l_ovn
645 );
646 -----------------------------------------------------------------------
647 -- SECTION2 : Exclusion Rules.
648 -----------------------------------------------------------------------
649 --No exclusion rules
650
651 ------------------------------------------------------------------------
652 -- SECTION 3 : Formulas
653 ------------------------------------------------------------------------
654 ------------------------
655 -- a) Formula
656 ------------------------
657 pay_sf_ins.ins
658 (p_formula_id => l_formula_id
659 ,p_template_type => 'T'
660 ,p_legislation_code => 'AE'
661 ,p_formula_name => '_FLAT_FF'
662 ,p_description => 'AE Formula for flat amount'
663 ,p_formula_text =>
664 '
665 /* Description: Formula for Flat amount template in UAE legislation
666 */
667 Inputs are Allowance_Amount
668 l_amount = Allowance_Amount
669 RETURN l_amount
670
671
672 /*======================== End Program =======================*/'
673 ,p_object_version_number => l_ovn
674 ,p_effective_date => l_effective_date
675 );
676 ---------------------------------------------------------------------------------
677 -- SECTION 4 : Balances and Classification
678 ---------------------------------------------------------------------------------
679 t_dim(1) := 'Assignment Inception To Date';
680 t_dim(2) := 'Assignment Run';
681 --================
682 -- Primary Balance
683 --================
684 pay_sbt_ins.ins
685 (p_balance_type_id => l_primary_bal_id
686 ,p_template_id => l_template_id
687 ,p_assignment_remuneration_flag => 'N'
688 ,p_balance_name => ''
689 ,p_reporting_name => ''
690 ,p_comments => null
691 ,p_balance_uom => 'M'
692 ,p_currency_code => 'AED'
693 ,p_object_version_number => l_ovn
694 ,p_effective_date => l_effective_date
695 );
696 -- create the defined balances
697 FOR i IN 1..2 LOOP
698 pay_sdb_ins.ins
699 (p_defined_balance_id => l_defined_bal_id
700 ,p_balance_type_id => l_primary_bal_id
701 ,p_dimension_name => t_dim(i)
702 ,p_object_version_number => l_ovn
703 ,p_effective_date => l_effective_date
704 );
705 END LOOP;
706 --
707
708
709 ---------------------------------------------------------------------------------
710 -- SECTION 4 : Elements
711 ---------------------------------------------------------------------------------
712 --====================
713 -- b) 'Base' element.
714 --====================
715 pay_set_ins.ins
716 (p_element_type_id => l_flat_element_id
717 ,p_template_id => l_template_id
718 ,p_element_name => ''
719 ,p_reporting_name => ''
720 ,p_relative_processing_priority => 0
721 ,p_processing_type => 'R'
722 ,p_classification_name => 'Earnings'
723 ,p_input_currency_code => 'AED'
724 ,p_output_currency_code => 'AED'
725 ,p_multiple_entries_allowed_fla => 'N'
726 ,p_post_termination_rule => 'F'
727 ,p_process_in_run_flag => 'Y'
728 ,p_additional_entry_allowed_fla => 'N'
729 ,p_adjustment_only_flag => 'N'
730 ,p_closed_for_entry_flag => 'N'
731 ,p_indirect_only_flag => 'N'
732 ,p_multiply_value_flag => 'N'
733 ,p_standard_link_flag => 'N'
734 ,p_payroll_formula_id => l_formula_id
735 ,p_object_version_number => l_ovn
736 ,p_effective_date => l_effective_date
737 );
738 -- Pay value
739 pay_siv_ins.ins
740 (p_input_value_id => l_flat_pay_iv
741 ,p_element_type_id => l_flat_element_id
742 ,p_display_sequence => 1
743 ,p_generate_db_items_flag => 'Y'
744 ,p_hot_default_flag => 'N'
745 ,p_mandatory_flag => 'X'
746 ,p_name => 'Pay Value'
747 ,p_uom => 'M'
748 ,p_object_version_number => l_ovn
749 ,p_effective_date => l_effective_date
750 );
751
752 pay_sbf_ins.ins
753 (p_balance_feed_id => l_bal_feed_id
754 ,p_balance_type_id => l_primary_bal_id
755 ,p_input_value_id => l_flat_pay_iv
756 ,p_scale => 1
757 ,p_object_version_number => l_ovn
758 ,p_effective_date => l_effective_date
759 );
760
761 pay_siv_ins.ins
762 (p_input_value_id => l_flat_amt_iv
763 ,p_element_type_id => l_flat_element_id
764 --,p_exclusion_rule_id => l_excl_rule_id_perc
765 ,p_display_sequence => 2
766 ,p_generate_db_items_flag => 'Y'
767 ,p_hot_default_flag => 'N'
768 ,p_mandatory_flag => 'Y'
769 ,p_name => 'Allowance Amount'
770 ,p_uom => 'M'
771 ,p_object_version_number => l_ovn
772 ,p_effective_date => l_effective_date
773 );
774 -------------------------------------------------------------------------
775 -- SECTION 6 : Formula rules
776 -------------------------------------------------------------------------
777 pay_sfr_ins.ins
778 (p_formula_result_rule_id => l_rr_id
779 ,p_shadow_element_type_id => l_flat_element_id
780 ,p_element_type_id => ''
781 ,p_input_value_id => l_flat_pay_iv
782 ,p_result_name => 'L_AMOUNT'
783 ,p_result_rule_type => 'D'
784 ,p_object_version_number => l_ovn
785 ,p_effective_date => l_effective_date
786 );
787 -------------------------------------------------------------------------
788 -- SECTION 7 : Insert into pay_ele_tmplt_class_usages
789 -------------------------------------------------------------------------
790 DECLARE
791 CURSOR csr_get_class_id IS
792 SELECT classification_id
793 FROM pay_element_classifications
794 WHERE legislation_code = 'AE'
795 AND classification_name = 'Earnings';
796 l_classification_id NUMBER;
797
798 BEGIN
799 OPEN csr_get_class_id;
800 FETCH csr_get_class_id into l_classification_id;
801 CLOSE csr_get_class_id;
802
803 INSERT INTO pay_ele_tmplt_class_usages
804 (ele_template_classification_id
805 ,classification_id
806 ,template_id
807 ,display_process_mode
808 ,display_arrearage)
809 VALUES
810 (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
811 ,l_classification_id
812 ,l_template_id
813 ,NULL
814 ,NULL);
815 END;
816
817 END create_flat_amt_template;
818
819 ------------------------------------------------------------------------
820 ------------------------------------------------------------------------
821 -- Procedure create_perc_template
822 -- This proceudre is used to create a percent of earnings template
823 ------------------------------------------------------------------------
824 ------------------------------------------------------------------------
825 PROCEDURE create_perc_template IS
826 --
827 TYPE Char80_Table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
828 t_dim Char80_Table;
829 --
830 l_template_id number;
831 l_defined_bal_id number;
832 l_effective_date date;
833 l_ovn number;
834 l_formula_id number;
835 l_rr_id number;
836 l_primary_bal_id number;
837 l_secondary_bal_id number;
838 l_info_element_id number;
839 l_ded_element_id number;
840 l_info_amt_iv number;
841 l_info_perc_iv number;
842 l_info_pay_iv number;
843 l_ded_payvalue_iv number;
844 l_ded_repay_iv number;
845 l_ded_install_iv number;
846 l_ded_process_iv number;
847 l_bal_feed_id number;
848 l_excl_rule_id number;
849 l_excl_rule_id_amt number;
850 l_excl_rule_id_perc number;
851 --
852 CURSOR c_template IS
853 SELECT template_id
854 FROM pay_element_templates
855 WHERE template_name = 'Percentage of Basic Salary Template'
856 AND template_type = 'T';
857 --
858 BEGIN
859 ----------------------------------------------------------------------------
860 -- Delete the existing template
861 ----------------------------------------------------------------------------
862 FOR c_rec in c_template LOOP
863 l_template_id := c_rec.template_id;
864
865 DELETE FROM pay_ele_tmplt_class_usages
866 WHERE template_id = l_template_id;
867
868 pay_element_template_api.delete_user_structure
869 (p_validate => false
870 ,p_drop_formula_packages => true
871 ,p_template_id => l_template_id);
872 END LOOP;
873 --
874 l_effective_date := to_date('0001/01/01', 'YYYY/MM/DD');
875 ------------------------------------------------------------------------
876 -- SECTION1 :
877 ------------------------------------------------------------------------
878 pay_etm_ins.ins
879 (p_template_id => l_template_id
880 ,p_effective_date => l_effective_date
881 ,p_template_type => 'T'
882 ,p_template_name => 'Percentage of Basic Salary Template'
883 ,p_base_processing_priority => 2500
884 ,p_max_base_name_length => 50
885 ,p_version_number => 1
886 ,p_legislation_code => 'AE'
887 ,p_object_version_number => l_ovn
888 );
889 -----------------------------------------------------------------------
890 -- SECTION2 : Exclusion Rules.
891 -----------------------------------------------------------------------
892 --None
893 ------------------------------------------------------------------------
894 -- SECTION 3 : Formulas
895 ------------------------------------------------------------------------
896 ------------------------
897 -- a) Formula
898 ------------------------
899 pay_sf_ins.ins
900 (p_formula_id => l_formula_id
901 ,p_template_type => 'T'
902 ,p_legislation_code => 'AE'
903 ,p_formula_name => '_PCT_FF'
904 ,p_description => 'AE Formula for percentage of earnings'
905 ,p_formula_text =>
906 '
907 /* Description: Formula for Percent of earnings in UAE legislation
908 */
909 Inputs are Percentage_of_earnings
910
911 DEFAULT FOR Percentage_of_earnings IS 0
912
913 monthly_salary = AE_GRATUITY_SALARY_FORMULA()
914
915 l_amount = (Percentage_of_earnings * monthly_salary)/100
916
917 RETURN l_amount
918
919
920 /*======================== End Program =======================*/'
921 ,p_object_version_number => l_ovn
922 ,p_effective_date => l_effective_date
923 );
924 --
925 --
926 ---------------------------------------------------------------------------------
927 -- SECTION 3 : Balances and Classification
928 ---------------------------------------------------------------------------------
929 t_dim(1) := 'Assignment Inception To Date';
930 t_dim(2) := 'Assignment Run';
931 --================
932 -- Primary Balance
933 --================
934 pay_sbt_ins.ins
935 (p_balance_type_id => l_primary_bal_id
936 ,p_template_id => l_template_id
937 ,p_assignment_remuneration_flag => 'N'
938 ,p_balance_name => ''
939 ,p_reporting_name => ''
940 ,p_comments => null
941 ,p_balance_uom => 'M'
942 ,p_currency_code => 'AED'
943 ,p_object_version_number => l_ovn
944 ,p_effective_date => l_effective_date
945 );
946 -- create the defined balances
947 FOR i IN 1..2 LOOP
948 pay_sdb_ins.ins
949 (p_defined_balance_id => l_defined_bal_id
950 ,p_balance_type_id => l_primary_bal_id
951 ,p_dimension_name => t_dim(i)
952 ,p_object_version_number => l_ovn
953 ,p_effective_date => l_effective_date
954 );
955 END LOOP;
956 --
957 ---------------------------------------------------------------------------------
958 -- SECTION 4 : Elements
959 ---------------------------------------------------------------------------------
960 --====================
961 -- b) 'Base' element.
962 --====================
963 pay_set_ins.ins
964 (p_element_type_id => l_info_element_id
965 ,p_template_id => l_template_id
966 ,p_element_name => ''
967 ,p_reporting_name => ''
968 ,p_relative_processing_priority => 0
969 ,p_processing_type => 'R'
970 ,p_classification_name => 'Earnings'
971 ,p_input_currency_code => 'AED'
972 ,p_output_currency_code => 'AED'
973 ,p_multiple_entries_allowed_fla => 'N'
974 ,p_post_termination_rule => 'F'
975 ,p_process_in_run_flag => 'Y'
976 ,p_additional_entry_allowed_fla => 'N'
977 ,p_adjustment_only_flag => 'N'
978 ,p_closed_for_entry_flag => 'N'
979 ,p_indirect_only_flag => 'N'
980 ,p_multiply_value_flag => 'N'
981 ,p_standard_link_flag => 'N'
982 ,p_payroll_formula_id => l_formula_id
983 ,p_object_version_number => l_ovn
984 ,p_effective_date => l_effective_date
985 );
986 -- Pay value
987 pay_siv_ins.ins
988 (p_input_value_id => l_info_pay_iv
989 ,p_element_type_id => l_info_element_id
990 ,p_display_sequence => 1
991 ,p_generate_db_items_flag => 'Y'
992 ,p_hot_default_flag => 'N'
993 ,p_mandatory_flag => 'X'
994 ,p_name => 'Pay Value'
995 ,p_uom => 'M'
996 ,p_object_version_number => l_ovn
997 ,p_effective_date => l_effective_date
998 );
999
1000 pay_sbf_ins.ins
1001 (p_balance_feed_id => l_bal_feed_id
1002 ,p_balance_type_id => l_primary_bal_id
1003 ,p_input_value_id => l_info_pay_iv
1004 ,p_scale => 1
1005 ,p_object_version_number => l_ovn
1006 ,p_effective_date => l_effective_date
1007 );
1008
1009 pay_siv_ins.ins
1010 (p_input_value_id => l_info_perc_iv
1011 ,p_element_type_id => l_info_element_id
1012 --,p_exclusion_rule_id => l_excl_rule_id_perc
1013 ,p_display_sequence => 2
1014 ,p_generate_db_items_flag => 'Y'
1015 ,p_hot_default_flag => 'N'
1016 ,p_mandatory_flag => 'Y'
1017 ,p_name => 'Percentage of Earnings'
1018 ,p_uom => 'N'
1019 ,p_object_version_number => l_ovn
1020 ,p_effective_date => l_effective_date
1021 );
1022
1023 -------------------------------------------------------------------------
1024 -- SECTION 6 : Formula rules
1025 -------------------------------------------------------------------------
1026
1027 pay_sfr_ins.ins
1028 (p_formula_result_rule_id => l_rr_id
1029 ,p_shadow_element_type_id => l_info_element_id
1030 ,p_element_type_id => ''
1031 ,p_input_value_id => l_info_pay_iv
1032 ,p_result_name => 'L_AMOUNT'
1033 ,p_result_rule_type => 'D'
1034 ,p_object_version_number => l_ovn
1035 ,p_effective_date => l_effective_date
1036 );
1037
1038 -------------------------------------------------------------------------
1039 -- SECTION 7 : Insert into pay_ele_tmplt_class_usages
1040 -------------------------------------------------------------------------
1041 DECLARE
1042 CURSOR csr_get_class_id IS
1043 SELECT classification_id
1044 FROM pay_element_classifications
1045 WHERE legislation_code = 'AE'
1046 AND classification_name = 'Earnings';
1047 l_classification_id NUMBER;
1048
1049 BEGIN
1050 OPEN csr_get_class_id;
1051 FETCH csr_get_class_id into l_classification_id;
1052 CLOSE csr_get_class_id;
1053
1054 INSERT INTO pay_ele_tmplt_class_usages
1055 (ele_template_classification_id
1056 ,classification_id
1057 ,template_id
1058 ,display_process_mode
1059 ,display_arrearage)
1060 VALUES
1061 (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
1062 ,l_classification_id
1063 ,l_template_id
1064 ,NULL
1065 ,NULL);
1066 END;
1067
1068 END create_perc_template;
1069
1070 ------------------------------------------------------------------------
1071 ------------------------------------------------------------------------
1072 -- Procedure create_basic_sal_template
1073 -- This proceudre is used to create basic salary template
1074 ------------------------------------------------------------------------
1075 ------------------------------------------------------------------------
1076 PROCEDURE create_basic_sal_template IS
1077 --
1078 TYPE Char80_Table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
1079 t_dim Char80_Table;
1080 --
1081 l_template_id number;
1082 l_defined_bal_id number;
1083 l_effective_date date;
1084 l_ovn number;
1085 l_formula_id number;
1086 l_rr_id number;
1087 l_primary_bal_id number;
1088 l_secondary_bal_id number;
1089 l_basic_element_id number;
1090 l_pay_iv number;
1091 l_rate_name_iv number;
1092 l_bal_feed_id number;
1093 --
1094 CURSOR c_template IS
1095 SELECT template_id
1096 FROM pay_element_templates
1097 WHERE template_name = 'Grade Allowance Template'
1098 AND template_type = 'T';
1099 --
1100 BEGIN
1101 ----------------------------------------------------------------------------
1102 -- Delete the existing template
1103 ----------------------------------------------------------------------------
1104 FOR c_rec in c_template LOOP
1105 l_template_id := c_rec.template_id;
1106
1107 DELETE FROM pay_ele_tmplt_class_usages
1108 WHERE template_id = l_template_id;
1109
1110 pay_element_template_api.delete_user_structure
1111 (p_validate => false
1112 ,p_drop_formula_packages => true
1113 ,p_template_id => l_template_id);
1114 END LOOP;
1115 --
1116 l_effective_date := to_date('0001/01/01', 'YYYY/MM/DD');
1117 ------------------------------------------------------------------------
1118 -- SECTION1 :
1119 ------------------------------------------------------------------------
1120 pay_etm_ins.ins
1121 (p_template_id => l_template_id
1122 ,p_effective_date => l_effective_date
1123 ,p_template_type => 'T'
1124 ,p_template_name => 'Grade Allowance Template'
1125 ,p_base_processing_priority => 2500
1126 ,p_max_base_name_length => 50
1127 ,p_version_number => 1
1128 ,p_legislation_code => 'AE'
1129 ,p_object_version_number => l_ovn
1130 );
1131 -----------------------------------------------------------------------
1132 -- SECTION2 : Exclusion Rules.
1133 -----------------------------------------------------------------------
1134 -- None
1135 ------------------------------------------------------------------------
1136 -- SECTION 3 : Formulas
1137 ------------------------------------------------------------------------
1138 ------------------------
1139 -- a) Formula
1140 ------------------------
1141 pay_sf_ins.ins
1142 (p_formula_id => l_formula_id
1143 ,p_template_type => 'T'
1144 ,p_legislation_code => 'AE'
1145 ,p_formula_name => '_BASIC_FF'
1146 ,p_description => 'AE Formula for basic salary'
1147 ,p_formula_text =>
1148 '
1149 /* Description: Formula for Grade Allowance template in UAE legislation
1150 */
1151 Inputs are Grade_Rate (TEXT)
1152
1153 l_amount = AE_GET_RATE_FROM_TAB_ID(TO_NUMBER(Grade_Rate))
1154 IF l_amount = 0 THEN
1155 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377434_AE_NO_GRADE_ALLW'')
1156 RETURN l_mesg)
1157 ELSE
1158 RETURN l_amount
1159
1160
1161 /*======================== End Program ======================*/'
1162 ,p_object_version_number => l_ovn
1163 ,p_effective_date => l_effective_date
1164 );
1165 --
1166 --
1167 ---------------------------------------------------------------------------------
1168 -- SECTION 3 : Balances and Classification
1169 ---------------------------------------------------------------------------------
1170 t_dim(1) := 'Assignment Inception To Date';
1171 t_dim(2) := 'Assignment Run';
1172 --================
1173 -- Primary Balance
1174 --================
1175 pay_sbt_ins.ins
1176 (p_balance_type_id => l_primary_bal_id
1177 ,p_template_id => l_template_id
1178 ,p_assignment_remuneration_flag => 'N'
1179 ,p_balance_name => ''
1180 ,p_reporting_name => ''
1181 ,p_comments => null
1182 ,p_balance_uom => 'M'
1183 ,p_currency_code => 'AED'
1184 ,p_object_version_number => l_ovn
1185 ,p_effective_date => l_effective_date
1186 );
1187 -- create the defined balances
1188 FOR i IN 1..2 LOOP
1189 pay_sdb_ins.ins
1190 (p_defined_balance_id => l_defined_bal_id
1191 ,p_balance_type_id => l_primary_bal_id
1192 ,p_dimension_name => t_dim(i)
1193 ,p_object_version_number => l_ovn
1194 ,p_effective_date => l_effective_date
1195 );
1196 END LOOP;
1197 --
1198
1199
1200 ---------------------------------------------------------------------------------
1201 -- SECTION 4 : Elements
1202 ---------------------------------------------------------------------------------
1203 --====================
1204 -- b) 'Base' element.
1205 --====================
1206 pay_set_ins.ins
1207 (p_element_type_id => l_basic_element_id
1208 ,p_template_id => l_template_id
1209 ,p_element_name => ''
1210 ,p_reporting_name => ''
1211 ,p_relative_processing_priority => 0
1212 ,p_processing_type => 'R'
1213 ,p_classification_name => 'Earnings'
1214 ,p_input_currency_code => 'AED'
1215 ,p_output_currency_code => 'AED'
1216 ,p_multiple_entries_allowed_fla => 'N'
1217 ,p_post_termination_rule => 'F'
1218 ,p_process_in_run_flag => 'Y'
1219 ,p_additional_entry_allowed_fla => 'N'
1220 ,p_adjustment_only_flag => 'N'
1221 ,p_closed_for_entry_flag => 'N'
1222 ,p_indirect_only_flag => 'N'
1223 ,p_multiply_value_flag => 'N'
1224 ,p_standard_link_flag => 'N'
1225 ,p_payroll_formula_id => l_formula_id
1226 ,p_object_version_number => l_ovn
1227 ,p_effective_date => l_effective_date
1228 );
1229 -- Pay value
1230 pay_siv_ins.ins
1231 (p_input_value_id => l_pay_iv
1232 ,p_element_type_id => l_basic_element_id
1233 ,p_display_sequence => 1
1234 ,p_generate_db_items_flag => 'Y'
1235 ,p_hot_default_flag => 'N'
1236 ,p_mandatory_flag => 'X'
1237 ,p_name => 'Pay Value'
1238 ,p_uom => 'M'
1239 ,p_object_version_number => l_ovn
1240 ,p_effective_date => l_effective_date
1241 );
1242
1243 pay_sbf_ins.ins
1244 (p_balance_feed_id => l_bal_feed_id
1245 ,p_balance_type_id => l_primary_bal_id
1246 ,p_input_value_id => l_pay_iv
1247 ,p_scale => 1
1248 ,p_object_version_number => l_ovn
1249 ,p_effective_date => l_effective_date
1250 );
1251
1252 pay_siv_ins.ins
1253 (p_input_value_id => l_rate_name_iv
1254 ,p_element_type_id => l_basic_element_id
1255 --,p_exclusion_rule_id => l_excl_rule_id_perc
1256 ,p_display_sequence => 2
1257 ,p_generate_db_items_flag => 'Y'
1258 ,p_hot_default_flag => 'N'
1259 ,p_mandatory_flag => 'Y'
1260 ,p_name => 'Grade Rate'
1261 ,p_uom => 'C'
1262 ,p_object_version_number => l_ovn
1263 ,p_effective_date => l_effective_date
1264 );
1265
1266
1267 -------------------------------------------------------------------------
1268 -- SECTION 6 : Formula rules
1269 -------------------------------------------------------------------------
1270
1271 pay_sfr_ins.ins
1272 (p_formula_result_rule_id => l_rr_id
1273 ,p_shadow_element_type_id => l_basic_element_id
1274 ,p_element_type_id => ''
1275 ,p_input_value_id => l_pay_iv
1276 ,p_result_name => 'L_AMOUNT'
1277 ,p_result_rule_type => 'D'
1278 ,p_object_version_number => l_ovn
1279 ,p_effective_date => l_effective_date
1280 );
1281 -- Message (Information)
1282 pay_sfr_ins.ins
1283 (p_formula_result_rule_id => l_rr_id
1284 ,p_shadow_element_type_id => l_basic_element_id
1285 ,p_result_name => 'L_MESG'
1286 ,p_result_rule_type => 'M'
1287 ,p_severity_level => 'I'
1288 ,p_object_version_number => l_ovn
1289 ,p_effective_date => l_effective_date
1290 );
1291 -------------------------------------------------------------------------
1292 -- SECTION 7 : Insert into pay_ele_tmplt_class_usages
1293 -------------------------------------------------------------------------
1294 DECLARE
1295 CURSOR csr_get_class_id IS
1296 SELECT classification_id
1297 FROM pay_element_classifications
1298 WHERE legislation_code = 'AE'
1299 AND classification_name = 'Earnings';
1300 l_classification_id NUMBER;
1301
1302 BEGIN
1303 OPEN csr_get_class_id;
1304 FETCH csr_get_class_id into l_classification_id;
1305 CLOSE csr_get_class_id;
1306
1307 INSERT INTO pay_ele_tmplt_class_usages
1308 (ele_template_classification_id
1309 ,classification_id
1310 ,template_id
1311 ,display_process_mode
1312 ,display_arrearage)
1313 VALUES
1314 (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
1315 ,l_classification_id
1316 ,l_template_id
1317 ,NULL
1318 ,NULL);
1319 END;
1320
1321 END create_basic_sal_template;
1322
1323 ------------------------------------------------------------------------
1324 ------------------------------------------------------------------------
1325 -- Procedure create_hsg_allw_template
1326 -- This procedure is used to create housing allowance template
1327 ------------------------------------------------------------------------
1328 ------------------------------------------------------------------------
1329 PROCEDURE create_hsg_allw_template IS
1330 --
1331 TYPE Char80_Table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
1332 t_dim Char80_Table;
1333 --
1334 l_template_id number;
1335 l_defined_bal_id number;
1336 l_effective_date date;
1337 l_ovn number;
1338 l_formula_id number;
1339 l_rr_id number;
1340 l_primary_bal_id number;
1341 l_secondary_bal_id number;
1342 l_hsg_element_id number;
1343 l_hsg_info_element_id number;
1344 l_pay_iv number;
1345 l_usage_iv number;
1346 l_override_amount_iv number;
1347 l_acco_iv number;
1348 l_grade_rate_iv number;
1349 l_info_pay_iv number;
1350 l_info_amount_iv number;
1351 l_info_acco_prov_iv number;
1352 l_bal_feed_id number;
1353 --
1354 CURSOR c_template IS
1355 SELECT template_id
1356 FROM pay_element_templates
1357 WHERE template_name = 'Housing Allowance Template'
1358 AND template_type = 'T';
1359 --
1360 BEGIN
1361 ----------------------------------------------------------------------------
1362 -- Delete the existing template
1363 ----------------------------------------------------------------------------
1364 FOR c_rec in c_template LOOP
1365 l_template_id := c_rec.template_id;
1366
1367 DELETE FROM pay_ele_tmplt_class_usages
1368 WHERE template_id = l_template_id;
1369
1370 pay_element_template_api.delete_user_structure
1371 (p_validate => false
1372 ,p_drop_formula_packages => true
1373 ,p_template_id => l_template_id);
1374 END LOOP;
1375 --
1376 l_effective_date := to_date('0001/01/01', 'YYYY/MM/DD');
1377 ------------------------------------------------------------------------
1378 -- SECTION1 :
1379 ------------------------------------------------------------------------
1380 pay_etm_ins.ins
1381 (p_template_id => l_template_id
1382 ,p_effective_date => l_effective_date
1383 ,p_template_type => 'T'
1384 ,p_template_name => 'Housing Allowance Template'
1385 ,p_base_processing_priority => 2500
1386 ,p_max_base_name_length => 50
1387 ,p_version_number => 1
1388 ,p_legislation_code => 'AE'
1389 ,p_object_version_number => l_ovn
1390 );
1391 -----------------------------------------------------------------------
1392 -- SECTION2 : Exclusion Rules.
1393 -----------------------------------------------------------------------
1394 --None
1395 ------------------------------------------------------------------------
1396 -- SECTION 3 : Formulas
1397 ------------------------------------------------------------------------
1398 ------------------------
1399 -- a) Formula
1400 ------------------------
1401 pay_sf_ins.ins
1402 (p_formula_id => l_formula_id
1403 ,p_template_type => 'T'
1404 ,p_legislation_code => 'AE'
1405 ,p_formula_name => '_HSG_FF'
1406 ,p_description => 'AE Formula for Housing Allowance'
1407 ,p_formula_text =>
1408 '
1409 /* Description: Formula for housing allowance template in UAE legislation
1410 */
1411
1412 DEFAULT FOR Override_Amount IS 0
1413 DEFAULT FOR SCL_ASG_AE_ACCOMMODATION_PROVIDED IS ''N''
1414
1415 Inputs are Rate_Value_to_be_used_as (TEXT)
1416 ,Override_Amount
1417
1418 IF Override_Amount > 0 AND SCL_ASG_AE_ACCOMMODATION_PROVIDED = ''Y'' THEN
1419 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377441_AE_INV_AMT_HSG'')
1420 RETURN l_mesg)
1421
1422 IF Override_Amount > 0 AND SCL_ASG_AE_ACCOMMODATION_PROVIDED = ''N'' THEN
1423 (l_amount = Override_Amount
1424 RETURN l_amount)
1425 ELSE
1426 (
1427
1428
1429 l_amount = 0
1430 l_info_type = ''MARITAL_STATUS''
1431 l_marital_status = AE_GET_EMP_DETAILS(l_info_type)
1432
1433 IF l_marital_status = ''NO_DATA_FOUND'' THEN
1434 ( l_mesg = AE_GET_MESSAGE(''PER'',''HR_377440_AE_NO_MAR_STATUS'',''ELEMENT:''||ELEMENT_NAME)
1435 RETURN l_mesg)
1436
1437 IF l_marital_status = ''M'' THEN
1438 l_grade_rate_table_name = GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''HSG_ALLOWANCE_GRADE_RATE_TABLE_MARRIED'')
1439 ELSE
1440 l_grade_rate_table_name = GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''HSG_ALLOWANCE_GRADE_RATE_TABLE_SINGLE'')
1441
1442 l_table_exists = ''Y''
1443 l_allowance_value = AE_GET_RATE_FROM_TAB_NAME(l_grade_rate_table_name,l_table_exists)
1444
1445 IF l_table_exists <> ''Y'' THEN
1446 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377435_AE_INV_GRADE_RATE'')
1447 RETURN l_mesg)
1448
1449 IF l_allowance_value = 0 THEN
1450 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377434_AE_NO_GRADE_ALLW'')
1451 RETURN l_mesg)
1452
1453 IF Rate_Value_to_be_used_as = ''P'' THEN
1454 (
1455 monthly_salary = AE_GRATUITY_SALARY_FORMULA()
1456 l_amount = (l_allowance_value * monthly_salary)/100
1457 IF l_marital_status = ''M'' THEN
1458 l_amount = LEAST(GREATEST(L_AMOUNT, TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''HSG_ALLOWANCE_MARRIED_MIN''))),TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''HSG_ALLOWANCE_MARRIED_MAX'')))
1459 ELSE
1460 l_amount = LEAST(GREATEST(L_AMOUNT, TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''HSG_ALLOWANCE_SINGLE_MIN''))),TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''HSG_ALLOWANCE_SINGLE_MAX'')))
1461 )
1462
1463 ELSE
1464 l_amount = l_allowance_value
1465
1466 l_monthly_allowance = l_amount
1467
1468 l_accomodation_provided = SCL_ASG_AE_ACCOMMODATION_PROVIDED
1469
1470 IF l_accomodation_provided = ''Y'' THEN
1471 (l_amount = 0
1472 l_subject_to_si = l_monthly_allowance)
1473
1474 RETURN l_amount
1475 ,l_monthly_allowance
1476 ,l_accomodation_provided
1477 ,l_subject_to_si
1478 ,l_allowance_value
1479 )
1480
1481
1482 /*====================== End Program ===================*/'
1483 ,p_object_version_number => l_ovn
1484 ,p_effective_date => l_effective_date
1485 );
1486 --
1487 --
1488 ---------------------------------------------------------------------------------
1489 -- SECTION 3 : Balances and Classification
1490 ---------------------------------------------------------------------------------
1491 t_dim(1) := 'Assignment Inception To Date';
1492 t_dim(2) := 'Assignment Run';
1493 --================
1494 -- Primary Balance
1495 --================
1496 pay_sbt_ins.ins
1497 (p_balance_type_id => l_primary_bal_id
1498 ,p_template_id => l_template_id
1499 ,p_assignment_remuneration_flag => 'N'
1500 ,p_balance_name => ''
1501 ,p_reporting_name => ''
1502 ,p_comments => null
1503 ,p_balance_uom => 'M'
1504 ,p_currency_code => 'AED'
1505 ,p_object_version_number => l_ovn
1506 ,p_effective_date => l_effective_date
1507 );
1508 -- create the defined balances
1509 FOR i IN 1..2 LOOP
1510 pay_sdb_ins.ins
1511 (p_defined_balance_id => l_defined_bal_id
1512 ,p_balance_type_id => l_primary_bal_id
1513 ,p_dimension_name => t_dim(i)
1514 ,p_object_version_number => l_ovn
1515 ,p_effective_date => l_effective_date
1516 );
1517 END LOOP;
1518 --
1519 ---------------------------------------------------------------------------------
1520 -- SECTION 4 : Elements
1521 ---------------------------------------------------------------------------------
1522 --====================
1523 -- b) 'Base' element.
1524 --====================
1525 pay_set_ins.ins
1526 (p_element_type_id => l_hsg_element_id
1527 ,p_template_id => l_template_id
1528 ,p_element_name => ''
1529 ,p_reporting_name => ''
1530 ,p_relative_processing_priority => 0
1531 ,p_processing_type => 'R'
1532 ,p_classification_name => 'Earnings'
1533 ,p_input_currency_code => 'AED'
1534 ,p_output_currency_code => 'AED'
1535 ,p_multiple_entries_allowed_fla => 'N'
1536 ,p_post_termination_rule => 'F'
1537 ,p_process_in_run_flag => 'Y'
1538 ,p_additional_entry_allowed_fla => 'N'
1539 ,p_adjustment_only_flag => 'N'
1540 ,p_closed_for_entry_flag => 'N'
1541 ,p_indirect_only_flag => 'N'
1542 ,p_multiply_value_flag => 'N'
1543 ,p_standard_link_flag => 'N'
1544 ,p_payroll_formula_id => l_formula_id
1545 ,p_object_version_number => l_ovn
1546 ,p_effective_date => l_effective_date
1547 );
1548 -- Pay value
1549 pay_siv_ins.ins
1550 (p_input_value_id => l_pay_iv
1551 ,p_element_type_id => l_hsg_element_id
1552 ,p_display_sequence => 1
1553 ,p_generate_db_items_flag => 'Y'
1554 ,p_hot_default_flag => 'N'
1555 ,p_mandatory_flag => 'X'
1556 ,p_name => 'Pay Value'
1557 ,p_uom => 'M'
1558 ,p_object_version_number => l_ovn
1559 ,p_effective_date => l_effective_date
1560 );
1561
1562 pay_sbf_ins.ins
1563 (p_balance_feed_id => l_bal_feed_id
1564 ,p_balance_type_id => l_primary_bal_id
1565 ,p_input_value_id => l_pay_iv
1566 ,p_scale => 1
1567 ,p_object_version_number => l_ovn
1568 ,p_effective_date => l_effective_date
1569 );
1570
1571 -- Usage
1572 pay_siv_ins.ins
1573 (p_input_value_id => l_usage_iv
1574 ,p_element_type_id => l_hsg_element_id
1575 ,p_display_sequence => 2
1576 ,p_generate_db_items_flag => 'Y'
1577 ,p_hot_default_flag => 'N'
1578 ,p_mandatory_flag => 'Y'
1579 ,p_name => 'Rate value to be used as'
1580 ,p_uom => 'C'
1581 ,p_lookup_type => 'AE_ALLOWANCE_USAGE'
1582 ,p_object_version_number => l_ovn
1583 ,p_effective_date => l_effective_date
1584 );
1585
1586 -- Usage
1587 pay_siv_ins.ins
1588 (p_input_value_id => l_override_amount_iv
1589 ,p_element_type_id => l_hsg_element_id
1590 ,p_display_sequence => 3
1591 ,p_generate_db_items_flag => 'Y'
1592 ,p_hot_default_flag => 'N'
1593 ,p_mandatory_flag => 'N'
1594 ,p_name => 'Override Amount'
1595 ,p_uom => 'M'
1596 ,p_object_version_number => l_ovn
1597 ,p_effective_date => l_effective_date
1598 );
1599
1600 -- Acco
1601 pay_siv_ins.ins
1602 (p_input_value_id => l_acco_iv
1603 ,p_element_type_id => l_hsg_element_id
1604 ,p_display_sequence => 4
1605 ,p_generate_db_items_flag => 'Y'
1606 ,p_hot_default_flag => 'N'
1607 ,p_mandatory_flag => 'X'
1608 ,p_name => 'Accommodation Provided'
1609 ,p_uom => 'C'
1610 ,p_lookup_type => 'YES_NO'
1611 ,p_object_version_number => l_ovn
1612 ,p_effective_date => l_effective_date
1613 );
1614
1615 -- Grade Rate Value
1616 pay_siv_ins.ins
1617 (p_input_value_id => l_grade_rate_iv
1618 ,p_element_type_id => l_hsg_element_id
1619 ,p_display_sequence => 5
1620 ,p_generate_db_items_flag => 'Y'
1621 ,p_hot_default_flag => 'N'
1622 ,p_mandatory_flag => 'X'
1623 ,p_name => 'Grade Rate Value'
1624 ,p_uom => 'N'
1625 ,p_object_version_number => l_ovn
1626 ,p_effective_date => l_effective_date
1627 );
1628
1629 pay_set_ins.ins
1630 (p_element_type_id => l_hsg_info_element_id
1631 ,p_template_id => l_template_id
1632 ,p_element_name => ' Information'
1633 --,p_reporting_name => ' r'
1634 ,p_relative_processing_priority => 50
1635 ,p_processing_type => 'N'
1636 ,p_classification_name => 'Information'
1637 ,p_input_currency_code => 'AED'
1638 ,p_output_currency_code => 'AED'
1639 ,p_multiple_entries_allowed_fla => 'N'
1640 ,p_post_termination_rule => 'F'
1641 ,p_process_in_run_flag => 'Y'
1642 ,p_additional_entry_allowed_fla => 'N'
1643 ,p_adjustment_only_flag => 'N'
1644 ,p_closed_for_entry_flag => 'N'
1645 ,p_indirect_only_flag => 'Y'
1646 ,p_multiply_value_flag => 'N'
1647 ,p_standard_link_flag => 'N'
1648 --,p_payroll_formula_id => l_formula_id
1649 --,p_skip_formula => ''
1650 ,p_object_version_number => l_ovn
1651 ,p_effective_date => l_effective_date
1652 );
1653 --
1654 pay_siv_ins.ins
1655 (p_input_value_id => l_info_acco_prov_iv
1656 ,p_element_type_id => l_hsg_info_element_id
1657 ,p_display_sequence => 1
1658 ,p_generate_db_items_flag => 'Y'
1659 ,p_hot_default_flag => 'N'
1660 ,p_mandatory_flag => 'X'
1661 ,p_name => 'Accommodation Provided'
1662 ,p_uom => 'C'
1663 ,p_lookup_type => 'YES_NO'
1664 ,p_object_version_number => l_ovn
1665 ,p_effective_date => l_effective_date
1666 );
1667
1668 -- Amount, feeds no balances.
1669 pay_siv_ins.ins
1670 (p_input_value_id => l_info_pay_iv
1671 ,p_element_type_id => l_hsg_info_element_id
1672 --,p_exclusion_rule_id => l_flat_amt_Xrule_id
1673 ,p_display_sequence => 2
1674 ,p_generate_db_items_flag => 'Y'
1675 ,p_hot_default_flag => 'N'
1676 ,p_mandatory_flag => 'X'
1677 ,p_name => 'Pay Value'
1678 ,p_uom => 'M'
1679 ,p_object_version_number => l_ovn
1680 ,p_effective_date => l_effective_date
1681 );
1682
1683 pay_siv_ins.ins
1684 (p_input_value_id => l_info_amount_iv
1685 ,p_element_type_id => l_hsg_info_element_id
1686 --,p_exclusion_rule_id => l_flat_amt_Xrule_id
1687 ,p_display_sequence => 3
1688 ,p_generate_db_items_flag => 'Y'
1689 ,p_hot_default_flag => 'N'
1690 ,p_mandatory_flag => 'X'
1691 ,p_name => 'Monthly Allowance'
1692 ,p_uom => 'M'
1693 ,p_object_version_number => l_ovn
1694 ,p_effective_date => l_effective_date
1695 );
1696 -------------------------------------------------------------------------
1697 -- SECTION 6 : Formula rules
1698 -------------------------------------------------------------------------
1699 pay_sfr_ins.ins
1700 (p_formula_result_rule_id => l_rr_id
1701 ,p_shadow_element_type_id => l_hsg_element_id
1702 ,p_element_type_id => ''
1703 ,p_input_value_id => l_pay_iv
1704 ,p_result_name => 'L_AMOUNT'
1705 ,p_result_rule_type => 'D'
1706 ,p_object_version_number => l_ovn
1707 ,p_effective_date => l_effective_date
1708 );
1709
1710 pay_sfr_ins.ins
1711 (p_formula_result_rule_id => l_rr_id
1712 ,p_shadow_element_type_id => l_hsg_element_id
1713 ,p_element_type_id => l_hsg_info_element_id
1714 ,p_input_value_id => l_info_acco_prov_iv
1715 ,p_result_name => 'L_ACCOMODATION_PROVIDED'
1716 ,p_result_rule_type => 'I'
1717 ,p_object_version_number => l_ovn
1718 ,p_effective_date => l_effective_date
1719 );
1720
1721 pay_sfr_ins.ins
1722 (p_formula_result_rule_id => l_rr_id
1723 ,p_shadow_element_type_id => l_hsg_element_id
1724 ,p_element_type_id => ''
1725 ,p_input_value_id => l_acco_iv
1726 ,p_result_name => 'L_ACCOMODATION_PROVIDED'
1727 ,p_result_rule_type => 'D'
1728 ,p_object_version_number => l_ovn
1729 ,p_effective_date => l_effective_date
1730 );
1731
1732 pay_sfr_ins.ins
1733 (p_formula_result_rule_id => l_rr_id
1734 ,p_shadow_element_type_id => l_hsg_element_id
1735 ,p_element_type_id => ''
1736 ,p_input_value_id => l_grade_rate_iv
1737 ,p_result_name => 'L_ALLOWANCE_VALUE'
1738 ,p_result_rule_type => 'D'
1739 ,p_object_version_number => l_ovn
1740 ,p_effective_date => l_effective_date
1741 );
1742
1743 pay_sfr_ins.ins
1744 (p_formula_result_rule_id => l_rr_id
1745 ,p_shadow_element_type_id => l_hsg_element_id
1746 ,p_element_type_id => l_hsg_info_element_id
1747 ,p_input_value_id => l_info_amount_iv
1748 ,p_result_name => 'L_MONTHLY_ALLOWANCE'
1749 ,p_result_rule_type => 'I'
1750 ,p_object_version_number => l_ovn
1751 ,p_effective_date => l_effective_date
1752 );
1753 pay_sfr_ins.ins
1754 (p_formula_result_rule_id => l_rr_id
1755 ,p_shadow_element_type_id => l_hsg_element_id
1756 ,p_element_type_id => l_hsg_info_element_id
1757 ,p_input_value_id => l_info_pay_iv
1758 ,p_result_name => 'L_SUBJECT_TO_SI'
1759 ,p_result_rule_type => 'I'
1760 ,p_object_version_number => l_ovn
1761 ,p_effective_date => l_effective_date
1762 );
1763
1764 -- Message (Information)
1765 pay_sfr_ins.ins
1766 (p_formula_result_rule_id => l_rr_id
1767 ,p_shadow_element_type_id => l_hsg_element_id
1768 ,p_result_name => 'L_MESG'
1769 ,p_result_rule_type => 'M'
1770 ,p_severity_level => 'I'
1771 ,p_object_version_number => l_ovn
1772 ,p_effective_date => l_effective_date
1773 );
1774 -------------------------------------------------------------------------
1775 -- SECTION 7 : Insert into pay_ele_tmplt_class_usages
1776 -------------------------------------------------------------------------
1777 DECLARE
1778 CURSOR csr_get_class_id IS
1779 SELECT classification_id
1780 FROM pay_element_classifications
1781 WHERE legislation_code = 'AE'
1782 AND classification_name = 'Earnings';
1783 l_classification_id NUMBER;
1784
1785 BEGIN
1786 OPEN csr_get_class_id;
1787 FETCH csr_get_class_id into l_classification_id;
1788 CLOSE csr_get_class_id;
1789
1790 INSERT INTO pay_ele_tmplt_class_usages
1791 (ele_template_classification_id
1792 ,classification_id
1793 ,template_id
1794 ,display_process_mode
1795 ,display_arrearage)
1796 VALUES
1797 (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
1798 ,l_classification_id
1799 ,l_template_id
1800 ,NULL
1801 ,NULL);
1802 END;
1803 END create_hsg_allw_template;
1804
1805 ------------------------------------------------------------------------
1806 ------------------------------------------------------------------------
1807 -- Procedure create_trn_allw_template
1808 -- This procedure is used to create transportation allowance template
1809 ------------------------------------------------------------------------
1810 ------------------------------------------------------------------------
1811 PROCEDURE create_trn_allw_template IS
1812 --
1813 TYPE Char80_Table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
1814 t_dim Char80_Table;
1815 --
1816 l_template_id number;
1817 l_defined_bal_id number;
1818 l_effective_date date;
1819 l_ovn number;
1820 l_formula_id number;
1821 l_rr_id number;
1822 l_primary_bal_id number;
1823 l_secondary_bal_id number;
1824 l_trn_element_id number;
1825 l_pay_iv number;
1826 l_override_amount_iv number;
1827 l_usage_iv number;
1828 l_grade_rate_iv number;
1829 l_trn_prov_iv number;
1830 l_bal_feed_id number;
1831 --
1832 CURSOR c_template IS
1833 SELECT template_id
1834 FROM pay_element_templates
1835 WHERE template_name = 'Transport Allowance Template'
1836 AND template_type = 'T';
1837 --
1838 BEGIN
1839 ----------------------------------------------------------------------------
1840 -- Delete the existing template
1841 ----------------------------------------------------------------------------
1842 FOR c_rec in c_template LOOP
1843 l_template_id := c_rec.template_id;
1844
1845 DELETE FROM pay_ele_tmplt_class_usages
1846 WHERE template_id = l_template_id;
1847
1848 pay_element_template_api.delete_user_structure
1849 (p_validate => false
1850 ,p_drop_formula_packages => true
1851 ,p_template_id => l_template_id);
1852 END LOOP;
1853 --
1854 l_effective_date := to_date('0001/01/01', 'YYYY/MM/DD');
1855 ------------------------------------------------------------------------
1856 -- SECTION1 :
1857 ------------------------------------------------------------------------
1858 pay_etm_ins.ins
1859 (p_template_id => l_template_id
1860 ,p_effective_date => l_effective_date
1861 ,p_template_type => 'T'
1862 ,p_template_name => 'Transport Allowance Template'
1863 ,p_base_processing_priority => 2500
1864 ,p_max_base_name_length => 50
1865 ,p_version_number => 1
1866 ,p_legislation_code => 'AE'
1867 ,p_object_version_number => l_ovn
1868 );
1869 -----------------------------------------------------------------------
1870 -- SECTION2 : Exclusion Rules.
1871 -----------------------------------------------------------------------
1872 -- None
1873 ------------------------------------------------------------------------
1874 -- SECTION 3 : Formulas
1875 ------------------------------------------------------------------------
1876 ------------------------
1877 -- a) Formula
1878 ------------------------
1879 pay_sf_ins.ins
1880 (p_formula_id => l_formula_id
1881 ,p_template_type => 'T'
1882 ,p_legislation_code => 'AE'
1883 ,p_formula_name => '_TRN_FF'
1884 ,p_description => 'AE Formula for Transport Allowance'
1885 ,p_formula_text =>
1886 '
1887 /* Description: Formula for transportation allowance in UAE legislation
1888 */
1889
1890
1891 Inputs are Rate_Value_to_be_used_as (TEXT)
1892 ,Override_Amount
1893
1894 DEFAULT FOR SCL_ASG_AE_TRANSPORTATION_PROVIDED IS ''N''
1895 DEFAULT FOR Override_Amount IS 0
1896
1897 IF Override_Amount > 0 AND SCL_ASG_AE_TRANSPORTATION_PROVIDED = ''Y'' THEN
1898 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377442_AE_INV_AMT_TRN'')
1899 RETURN l_mesg)
1900
1901 IF Override_Amount > 0 AND SCL_ASG_AE_TRANSPORTATION_PROVIDED = ''N'' THEN
1902 (l_amount = Override_Amount
1903 RETURN l_amount)
1904 ELSE
1905 (
1906
1907 l_amount = 0
1908
1909 /*Check if Local Nationality is defined*/
1910 l_exists = AE_LOCAL_NATIONALITY_NOT_DEFINED()
1911
1912 IF l_exists = ''NOTEXISTS'' THEN
1913 (
1914 l_mesg = AE_GET_MESSAGE(''PER'',''HR_377425_AE_LOC_NAT_NOT_DEF'')
1915 return l_mesg
1916 )
1917
1918 l_local_nat = AE_GET_LOCAL_NATIONALITY()
1919 l_matches = AE_LOCAL_NATIONALITY_MATCHES()
1920
1921 IF l_matches = ''MATCH'' THEN
1922 l_local = ''Y''
1923 ELSE
1924 l_local = ''N''
1925
1926
1927 IF l_local = ''Y'' THEN
1928 l_grade_rate_table_name = GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''TRN_ALLOWANCE_GRADE_RATE_TABLE_NATIONAL'')
1929 ELSE
1930 l_grade_rate_table_name = GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''TRN_ALLOWANCE_GRADE_RATE_TABLE_NON_NATIONAL'')
1931
1932 l_table_exists = ''Y''
1933 l_allowance_value = AE_GET_RATE_FROM_TAB_NAME(l_grade_rate_table_name,l_table_exists)
1934
1935 IF l_table_exists <> ''Y'' THEN
1936 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377435_AE_INV_GRADE_RATE'')
1937 RETURN l_mesg)
1938
1939 IF l_allowance_value = 0 THEN
1940 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377434_AE_NO_GRADE_ALLW'')
1941 RETURN l_mesg)
1942
1943 IF Rate_Value_to_be_used_as = ''P'' THEN
1944 (
1945 monthly_salary = AE_GRATUITY_SALARY_FORMULA()
1946 l_amount = (l_allowance_value * monthly_salary)/100
1947 l_amount = LEAST(GREATEST(L_AMOUNT, TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''TRN_ALLOWANCE_MIN''))),TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''TRN_ALLOWANCE_MAX'')))
1948 )
1949
1950 ELSE
1951 l_amount = l_allowance_value
1952
1953 l_monthly_allowance = l_amount
1954
1955 l_transportation_provided = SCL_ASG_AE_TRANSPORTATION_PROVIDED
1956
1957 IF l_transportation_provided = ''Y'' THEN
1958 (l_amount = 0)
1959
1960 RETURN l_amount
1961 ,l_allowance_value
1962 ,l_transportation_provided
1963
1964 )
1965
1966
1967 /*====================== End Program ===================*/'
1968 ,p_object_version_number => l_ovn
1969 ,p_effective_date => l_effective_date
1970 );
1971 --
1972 --
1973 ---------------------------------------------------------------------------------
1974 -- SECTION 3 : Balances and Classification
1975 ---------------------------------------------------------------------------------
1976 t_dim(1) := 'Assignment Inception To Date';
1977 t_dim(2) := 'Assignment Run';
1978 --================
1979 -- Primary Balance
1980 --================
1981 pay_sbt_ins.ins
1982 (p_balance_type_id => l_primary_bal_id
1983 ,p_template_id => l_template_id
1984 ,p_assignment_remuneration_flag => 'N'
1985 ,p_balance_name => ''
1986 ,p_reporting_name => ''
1987 ,p_comments => null
1988 ,p_balance_uom => 'M'
1989 ,p_currency_code => 'AED'
1990 ,p_object_version_number => l_ovn
1991 ,p_effective_date => l_effective_date
1992 );
1993 -- create the defined balances
1994 FOR i IN 1..2 LOOP
1995 pay_sdb_ins.ins
1996 (p_defined_balance_id => l_defined_bal_id
1997 ,p_balance_type_id => l_primary_bal_id
1998 ,p_dimension_name => t_dim(i)
1999 ,p_object_version_number => l_ovn
2000 ,p_effective_date => l_effective_date
2001 );
2002 END LOOP;
2003 --
2004 ---------------------------------------------------------------------------------
2005 -- SECTION 4 : Elements
2006 ---------------------------------------------------------------------------------
2007 --====================
2008 -- b) 'Base' element.
2009 --====================
2010 pay_set_ins.ins
2011 (p_element_type_id => l_trn_element_id
2012 ,p_template_id => l_template_id
2013 ,p_element_name => ''
2014 ,p_reporting_name => ''
2015 ,p_relative_processing_priority => 0
2016 ,p_processing_type => 'R'
2017 ,p_classification_name => 'Earnings'
2018 ,p_input_currency_code => 'AED'
2019 ,p_output_currency_code => 'AED'
2020 ,p_multiple_entries_allowed_fla => 'N'
2021 ,p_post_termination_rule => 'F'
2022 ,p_process_in_run_flag => 'Y'
2023 ,p_additional_entry_allowed_fla => 'N'
2024 ,p_adjustment_only_flag => 'N'
2025 ,p_closed_for_entry_flag => 'N'
2026 ,p_indirect_only_flag => 'N'
2027 ,p_multiply_value_flag => 'N'
2028 ,p_standard_link_flag => 'N'
2029 ,p_payroll_formula_id => l_formula_id
2030 ,p_object_version_number => l_ovn
2031 ,p_effective_date => l_effective_date
2032 );
2033 -- Pay value
2034 pay_siv_ins.ins
2035 (p_input_value_id => l_pay_iv
2036 ,p_element_type_id => l_trn_element_id
2037 ,p_display_sequence => 1
2038 ,p_generate_db_items_flag => 'Y'
2039 ,p_hot_default_flag => 'N'
2040 ,p_mandatory_flag => 'X'
2041 ,p_name => 'Pay Value'
2042 ,p_uom => 'M'
2043 ,p_object_version_number => l_ovn
2044 ,p_effective_date => l_effective_date
2045 );
2046
2047 pay_sbf_ins.ins
2048 (p_balance_feed_id => l_bal_feed_id
2049 ,p_balance_type_id => l_primary_bal_id
2050 ,p_input_value_id => l_pay_iv
2051 ,p_scale => 1
2052 ,p_object_version_number => l_ovn
2053 ,p_effective_date => l_effective_date
2054 );
2055
2056 -- Usage
2057 pay_siv_ins.ins
2058 (p_input_value_id => l_usage_iv
2059 ,p_element_type_id => l_trn_element_id
2060 ,p_display_sequence => 2
2061 ,p_generate_db_items_flag => 'Y'
2062 ,p_hot_default_flag => 'N'
2063 ,p_mandatory_flag => 'Y'
2064 ,p_name => 'Rate value to be used as'
2065 ,p_uom => 'C'
2066 ,p_lookup_type => 'AE_ALLOWANCE_USAGE'
2067 ,p_object_version_number => l_ovn
2068 ,p_effective_date => l_effective_date
2069 );
2070
2071 --Override amount
2072 pay_siv_ins.ins
2073 (p_input_value_id => l_override_amount_iv
2074 ,p_element_type_id => l_trn_element_id
2075 ,p_display_sequence => 3
2076 ,p_generate_db_items_flag => 'Y'
2077 ,p_hot_default_flag => 'N'
2078 ,p_mandatory_flag => 'N'
2079 ,p_name => 'Override Amount'
2080 ,p_uom => 'M'
2081 ,p_object_version_number => l_ovn
2082 ,p_effective_date => l_effective_date
2083 );
2084
2085 -- Trans provided
2086 pay_siv_ins.ins
2087 (p_input_value_id => l_trn_prov_iv
2088 ,p_element_type_id => l_trn_element_id
2089 ,p_display_sequence => 4
2090 ,p_generate_db_items_flag => 'Y'
2091 ,p_hot_default_flag => 'N'
2092 ,p_mandatory_flag => 'X'
2093 ,p_name => 'Transportation Provided'
2094 ,p_uom => 'C'
2095 ,p_lookup_type => 'YES_NO'
2096 ,p_object_version_number => l_ovn
2097 ,p_effective_date => l_effective_date
2098 );
2099
2100 -- Grade Rate value
2101 pay_siv_ins.ins
2102 (p_input_value_id => l_grade_rate_iv
2103 ,p_element_type_id => l_trn_element_id
2104 --,p_exclusion_rule_id => l_flat_amt_Xrule_id
2105 ,p_display_sequence => 5
2106 ,p_generate_db_items_flag => 'Y'
2107 ,p_hot_default_flag => 'N'
2108 ,p_mandatory_flag => 'X'
2109 ,p_name => 'Grade Rate Value'
2110 ,p_uom => 'N'
2111 ,p_object_version_number => l_ovn
2112 ,p_effective_date => l_effective_date
2113 );
2114
2115 -------------------------------------------------------------------------
2116 -- SECTION 6 : Formula rules
2117 -------------------------------------------------------------------------
2118
2119 pay_sfr_ins.ins
2120 (p_formula_result_rule_id => l_rr_id
2121 ,p_shadow_element_type_id => l_trn_element_id
2122 ,p_element_type_id => ''
2123 ,p_input_value_id => l_pay_iv
2124 ,p_result_name => 'L_AMOUNT'
2125 ,p_result_rule_type => 'D'
2126 ,p_object_version_number => l_ovn
2127 ,p_effective_date => l_effective_date
2128 );
2129
2130 pay_sfr_ins.ins
2131 (p_formula_result_rule_id => l_rr_id
2132 ,p_shadow_element_type_id => l_trn_element_id
2133 ,p_element_type_id => ''
2134 ,p_input_value_id => l_trn_prov_iv
2135 ,p_result_name => 'L_TRANSPORTATION_PROVIDED'
2136 ,p_result_rule_type => 'D'
2137 ,p_object_version_number => l_ovn
2138 ,p_effective_date => l_effective_date
2139 );
2140
2141 pay_sfr_ins.ins
2142 (p_formula_result_rule_id => l_rr_id
2143 ,p_shadow_element_type_id => l_trn_element_id
2144 ,p_element_type_id => ''
2145 ,p_input_value_id => l_grade_rate_iv
2146 ,p_result_name => 'L_ALLOWANCE_VALUE'
2147 ,p_result_rule_type => 'D'
2148 ,p_object_version_number => l_ovn
2149 ,p_effective_date => l_effective_date
2150 );
2151
2152 -- Message (Information)
2153 pay_sfr_ins.ins
2154 (p_formula_result_rule_id => l_rr_id
2155 ,p_shadow_element_type_id => l_trn_element_id
2156 ,p_result_name => 'L_MESG'
2157 ,p_result_rule_type => 'M'
2158 ,p_severity_level => 'I'
2159 ,p_object_version_number => l_ovn
2160 ,p_effective_date => l_effective_date
2161 );
2162
2163 -------------------------------------------------------------------------
2164 -- SECTION 7 : Insert into pay_ele_tmplt_class_usages
2165 -------------------------------------------------------------------------
2166 DECLARE
2167 CURSOR csr_get_class_id IS
2168 SELECT classification_id
2169 FROM pay_element_classifications
2170 WHERE legislation_code = 'AE'
2171 AND classification_name = 'Earnings';
2172 l_classification_id NUMBER;
2173
2174 BEGIN
2175 OPEN csr_get_class_id;
2176 FETCH csr_get_class_id into l_classification_id;
2177 CLOSE csr_get_class_id;
2178
2179 INSERT INTO pay_ele_tmplt_class_usages
2180 (ele_template_classification_id
2181 ,classification_id
2182 ,template_id
2183 ,display_process_mode
2184 ,display_arrearage)
2185 VALUES
2186 (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
2187 ,l_classification_id
2188 ,l_template_id
2189 ,NULL
2190 ,NULL);
2191 END;
2192
2193 END create_trn_allw_template;
2194
2195 ------------------------------------------------------------------------
2196 ------------------------------------------------------------------------
2197 -- Procedure create_col_allw_template
2198 -- This procedure is used to create cost of living allowance template
2199 ------------------------------------------------------------------------
2200 ------------------------------------------------------------------------
2201 PROCEDURE create_col_allw_template IS
2202 --
2203 TYPE Char80_Table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
2204 t_dim Char80_Table;
2205 --
2206 l_template_id number;
2207 l_defined_bal_id number;
2208 l_effective_date date;
2209 l_ovn number;
2210 l_formula_id number;
2211 l_rr_id number;
2212 l_primary_bal_id number;
2213 l_secondary_bal_id number;
2214 l_col_element_id number;
2215 l_pay_iv number;
2216 l_max_iv number;
2217 l_min_iv number;
2218 l_percent_iv number;
2219 l_bal_feed_id number;
2220 --
2221 CURSOR c_template IS
2222 SELECT template_id
2223 FROM pay_element_templates
2224 WHERE template_name = 'Cost of Living Allowance Template'
2225 AND template_type = 'T';
2226 --
2227 BEGIN
2228 ----------------------------------------------------------------------------
2229 -- Delete the existing template
2230 ----------------------------------------------------------------------------
2231 FOR c_rec in c_template LOOP
2232 l_template_id := c_rec.template_id;
2233
2234 DELETE FROM pay_ele_tmplt_class_usages
2235 WHERE template_id = l_template_id;
2236
2237 pay_element_template_api.delete_user_structure
2238 (p_validate => false
2239 ,p_drop_formula_packages => true
2240 ,p_template_id => l_template_id);
2241 END LOOP;
2242 --
2243 l_effective_date := to_date('0001/01/01', 'YYYY/MM/DD');
2244 ------------------------------------------------------------------------
2245 -- SECTION1 :
2246 ------------------------------------------------------------------------
2247 pay_etm_ins.ins
2248 (p_template_id => l_template_id
2249 ,p_effective_date => l_effective_date
2250 ,p_template_type => 'T'
2251 ,p_template_name => 'Cost of Living Allowance Template'
2252 ,p_base_processing_priority => 2500
2253 ,p_max_base_name_length => 50
2254 ,p_version_number => 1
2255 ,p_legislation_code => 'AE'
2256 ,p_object_version_number => l_ovn
2257 );
2258 -----------------------------------------------------------------------
2259 -- SECTION2 : Exclusion Rules.
2260 -----------------------------------------------------------------------
2261 --None
2262 ------------------------------------------------------------------------
2263 -- SECTION 3 : Formulas
2264 ------------------------------------------------------------------------
2265 ------------------------
2266 -- a) Formula
2267 ------------------------
2268 pay_sf_ins.ins
2269 (p_formula_id => l_formula_id
2270 ,p_template_type => 'T'
2271 ,p_legislation_code => 'AE'
2272 ,p_formula_name => '_COL_FF'
2273 ,p_description => 'AE Formula for Cost of Living Allowance'
2274 ,p_formula_text =>
2275 '
2276 /* Description: Formula for cost of living template in UAE legislation
2277 */
2278
2279 l_percent = TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''COST_OF_LIVING_ALLOWANCE_PERCENT''))
2280 l_max_value = TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''COST_OF_LIVING_ALLOWANCE_MAX''))
2281 l_min_value = TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''COST_OF_LIVING_ALLOWANCE_MIN''))
2282
2283 monthly_salary = AE_GRATUITY_SALARY_FORMULA()
2284 l_amount = (l_percent * monthly_salary)/100
2285
2286 /*Amount = LEAST(GREATEST(AMOUNT, TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''COST_OF_LIVING_ALLOWANCE_MIN''))),TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''COST_OF_LIVING_ALLOWANCE_MAX'')))*/
2287
2288 l_amount = LEAST(GREATEST(l_amount,l_min_value),l_max_value)
2289
2290
2291 RETURN l_amount
2292 ,l_percent
2293 ,l_min_value
2294 ,l_max_value
2295
2296
2297 /*====================== End Program ==================*/'
2298 ,p_object_version_number => l_ovn
2299 ,p_effective_date => l_effective_date
2300 );
2301 --
2302 --
2303 ---------------------------------------------------------------------------------
2304 -- SECTION 3 : Balances and Classification
2305 ---------------------------------------------------------------------------------
2306 t_dim(1) := 'Assignment Inception To Date';
2307 t_dim(2) := 'Assignment Run';
2308 --================
2309 -- Primary Balance
2310 --================
2311 pay_sbt_ins.ins
2312 (p_balance_type_id => l_primary_bal_id
2313 ,p_template_id => l_template_id
2314 ,p_assignment_remuneration_flag => 'N'
2315 ,p_balance_name => ''
2316 ,p_reporting_name => ''
2317 ,p_comments => null
2318 ,p_balance_uom => 'M'
2319 ,p_currency_code => 'AED'
2320 ,p_object_version_number => l_ovn
2321 ,p_effective_date => l_effective_date
2322 );
2323 -- create the defined balances
2324 FOR i IN 1..2 LOOP
2325 pay_sdb_ins.ins
2326 (p_defined_balance_id => l_defined_bal_id
2327 ,p_balance_type_id => l_primary_bal_id
2328 ,p_dimension_name => t_dim(i)
2329 ,p_object_version_number => l_ovn
2330 ,p_effective_date => l_effective_date
2331 );
2332 END LOOP;
2333 --
2334 ---------------------------------------------------------------------------------
2335 -- SECTION 4 : Elements
2336 ---------------------------------------------------------------------------------
2337 --====================
2338 -- b) 'Base' element.
2339 --====================
2340 pay_set_ins.ins
2341 (p_element_type_id => l_col_element_id
2342 ,p_template_id => l_template_id
2343 ,p_element_name => ''
2344 ,p_reporting_name => ''
2345 ,p_relative_processing_priority => 0
2346 ,p_processing_type => 'R'
2347 ,p_classification_name => 'Earnings'
2348 ,p_input_currency_code => 'AED'
2349 ,p_output_currency_code => 'AED'
2350 ,p_multiple_entries_allowed_fla => 'N'
2351 ,p_post_termination_rule => 'F'
2352 ,p_process_in_run_flag => 'Y'
2353 ,p_additional_entry_allowed_fla => 'N'
2354 ,p_adjustment_only_flag => 'N'
2355 ,p_closed_for_entry_flag => 'N'
2356 ,p_indirect_only_flag => 'N'
2357 ,p_multiply_value_flag => 'N'
2358 ,p_standard_link_flag => 'N'
2359 ,p_payroll_formula_id => l_formula_id
2360 ,p_object_version_number => l_ovn
2361 ,p_effective_date => l_effective_date
2362 );
2363 -- Pay value
2364 pay_siv_ins.ins
2365 (p_input_value_id => l_pay_iv
2366 ,p_element_type_id => l_col_element_id
2367 ,p_display_sequence => 1
2368 ,p_generate_db_items_flag => 'Y'
2369 ,p_hot_default_flag => 'N'
2370 ,p_mandatory_flag => 'X'
2371 ,p_name => 'Pay Value'
2372 ,p_uom => 'M'
2373 ,p_object_version_number => l_ovn
2374 ,p_effective_date => l_effective_date
2375 );
2376
2377 pay_sbf_ins.ins
2378 (p_balance_feed_id => l_bal_feed_id
2379 ,p_balance_type_id => l_primary_bal_id
2380 ,p_input_value_id => l_pay_iv
2381 ,p_scale => 1
2382 ,p_object_version_number => l_ovn
2383 ,p_effective_date => l_effective_date
2384 );
2385
2386 -- Percent
2387 pay_siv_ins.ins
2388 (p_input_value_id => l_percent_iv
2389 ,p_element_type_id => l_col_element_id
2390 ,p_display_sequence => 2
2391 ,p_generate_db_items_flag => 'Y'
2392 ,p_hot_default_flag => 'N'
2393 ,p_mandatory_flag => 'X'
2394 ,p_name => 'Percentage of Earnings'
2395 ,p_uom => 'N'
2396 ,p_object_version_number => l_ovn
2397 ,p_effective_date => l_effective_date
2398 );
2399 --
2400 pay_siv_ins.ins
2401 (p_input_value_id => l_max_iv
2402 ,p_element_type_id => l_col_element_id
2403 ,p_display_sequence => 3
2404 ,p_generate_db_items_flag => 'Y'
2405 ,p_hot_default_flag => 'N'
2406 ,p_mandatory_flag => 'X'
2407 ,p_name => 'Maximum Amount for Allowance'
2408 ,p_uom => 'M'
2409 ,p_object_version_number => l_ovn
2410 ,p_effective_date => l_effective_date
2411 );
2412 --
2413 pay_siv_ins.ins
2414 (p_input_value_id => l_min_iv
2415 ,p_element_type_id => l_col_element_id
2416 ,p_display_sequence => 4
2417 ,p_generate_db_items_flag => 'Y'
2418 ,p_hot_default_flag => 'N'
2419 ,p_mandatory_flag => 'X'
2420 ,p_name => 'Minimum Amount for Allowance'
2421 ,p_uom => 'M'
2422 ,p_object_version_number => l_ovn
2423 ,p_effective_date => l_effective_date
2424 );
2425
2426 -------------------------------------------------------------------------
2427 -- SECTION 6 : Formula rules
2428 -------------------------------------------------------------------------
2429
2430 pay_sfr_ins.ins
2431 (p_formula_result_rule_id => l_rr_id
2432 ,p_shadow_element_type_id => l_col_element_id
2433 ,p_element_type_id => ''
2434 ,p_input_value_id => l_pay_iv
2435 ,p_result_name => 'L_AMOUNT'
2436 ,p_result_rule_type => 'D'
2437 ,p_object_version_number => l_ovn
2438 ,p_effective_date => l_effective_date
2439 );
2440
2441 pay_sfr_ins.ins
2442 (p_formula_result_rule_id => l_rr_id
2443 ,p_shadow_element_type_id => l_col_element_id
2444 ,p_element_type_id => ''
2445 ,p_input_value_id => l_percent_iv
2446 ,p_result_name => 'L_PERCENT'
2447 ,p_result_rule_type => 'D'
2448 ,p_object_version_number => l_ovn
2449 ,p_effective_date => l_effective_date
2450 );
2451
2452 pay_sfr_ins.ins
2453 (p_formula_result_rule_id => l_rr_id
2454 ,p_shadow_element_type_id => l_col_element_id
2455 ,p_element_type_id => ''
2456 ,p_input_value_id => l_max_iv
2457 ,p_result_name => 'L_MAX_VALUE'
2458 ,p_result_rule_type => 'D'
2459 ,p_object_version_number => l_ovn
2460 ,p_effective_date => l_effective_date
2461 );
2462
2463 pay_sfr_ins.ins
2464 (p_formula_result_rule_id => l_rr_id
2465 ,p_shadow_element_type_id => l_col_element_id
2466 ,p_element_type_id => ''
2467 ,p_input_value_id => l_min_iv
2468 ,p_result_name => 'L_MIN_VALUE'
2469 ,p_result_rule_type => 'D'
2470 ,p_object_version_number => l_ovn
2471 ,p_effective_date => l_effective_date
2472 );
2473
2474 -------------------------------------------------------------------------
2475 -- SECTION 7 : Insert into pay_ele_tmplt_class_usages
2476 -------------------------------------------------------------------------
2477 DECLARE
2478 CURSOR csr_get_class_id IS
2479 SELECT classification_id
2480 FROM pay_element_classifications
2481 WHERE legislation_code = 'AE'
2482 AND classification_name = 'Earnings';
2483 l_classification_id NUMBER;
2484
2485 BEGIN
2486 OPEN csr_get_class_id;
2487 FETCH csr_get_class_id into l_classification_id;
2488 CLOSE csr_get_class_id;
2489
2490 INSERT INTO pay_ele_tmplt_class_usages
2491 (ele_template_classification_id
2492 ,classification_id
2493 ,template_id
2494 ,display_process_mode
2495 ,display_arrearage)
2496 VALUES
2497 (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
2498 ,l_classification_id
2499 ,l_template_id
2500 ,NULL
2501 ,NULL);
2502 END;
2503
2504 END create_col_allw_template;
2505
2506 ------------------------------------------------------------------------
2507 ------------------------------------------------------------------------
2508 -- Procedure create_child_allw_template
2509 -- This procedure is used to create chiild allowance template
2510 ------------------------------------------------------------------------
2511 ------------------------------------------------------------------------
2512 PROCEDURE create_child_allw_template IS
2513 --
2514 TYPE Char80_Table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
2515 t_dim Char80_Table;
2516 --
2517 l_template_id number;
2518 l_defined_bal_id number;
2519 l_effective_date date;
2520 l_ovn number;
2521 l_formula_id number;
2522 l_rr_id number;
2523 l_primary_bal_id number;
2524 l_secondary_bal_id number;
2525 l_child_element_id number;
2526 l_pay_iv number;
2527 l_num_child_iv number;
2528 l_allowance_iv number;
2529 l_bal_feed_id number;
2530 --
2531 CURSOR c_template IS
2532 SELECT template_id
2533 FROM pay_element_templates
2534 WHERE template_name = 'Children Social Allowance Template'
2535 AND template_type = 'T';
2536 --
2537 BEGIN
2538 ----------------------------------------------------------------------------
2539 -- Delete the existing template
2540 ----------------------------------------------------------------------------
2541 FOR c_rec in c_template LOOP
2542 l_template_id := c_rec.template_id;
2543
2544 DELETE FROM pay_ele_tmplt_class_usages
2545 WHERE template_id = l_template_id;
2546
2547 pay_element_template_api.delete_user_structure
2548 (p_validate => false
2549 ,p_drop_formula_packages => true
2550 ,p_template_id => l_template_id);
2551 END LOOP;
2552 --
2553 l_effective_date := to_date('0001/01/01', 'YYYY/MM/DD');
2554 ------------------------------------------------------------------------
2555 -- SECTION1 :
2556 ------------------------------------------------------------------------
2557 pay_etm_ins.ins
2558 (p_template_id => l_template_id
2559 ,p_effective_date => l_effective_date
2560 ,p_template_type => 'T'
2561 ,p_template_name => 'Children Social Allowance Template'
2562 ,p_base_processing_priority => 2500
2563 ,p_max_base_name_length => 50
2564 ,p_version_number => 1
2565 ,p_legislation_code => 'AE'
2566 ,p_object_version_number => l_ovn
2567 );
2568 -----------------------------------------------------------------------
2569 -- SECTION2 : Exclusion Rules.
2570 -----------------------------------------------------------------------
2571 -- None
2572 ------------------------------------------------------------------------
2573 -- SECTION 3 : Formulas
2574 ------------------------------------------------------------------------
2575 ------------------------
2576 -- a) Formula
2577 ------------------------
2578 pay_sf_ins.ins
2579 (p_formula_id => l_formula_id
2580 ,p_template_type => 'T'
2581 ,p_legislation_code => 'AE'
2582 ,p_formula_name => '_CA_FF'
2583 ,p_description => 'AE Formula for Children Social Allowance'
2584 ,p_formula_text =>
2585 '
2586 /* Description: Formula for child allowance template in UAE legislation
2587 */
2588 /*Check if Local Nationality is defined*/
2589 l_exists = AE_LOCAL_NATIONALITY_NOT_DEFINED()
2590
2591 IF l_exists = ''NOTEXISTS'' THEN
2592 (
2593 l_mesg = AE_GET_MESSAGE(''PER'',''HR_377425_AE_LOC_NAT_NOT_DEF'')
2594 return l_mesg
2595 )
2596
2597 l_local_nat = AE_GET_LOCAL_NATIONALITY()
2598 l_matches = AE_LOCAL_NATIONALITY_MATCHES()
2599 IF l_matches = ''MATCH'' THEN
2600 (
2601 l_amount = 0
2602 l_info_type = ''DEPENDENT_CHILDREN''
2603 l_count_child = AE_GET_EMP_DETAILS(l_info_type)
2604
2605 l_child_allowance = TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''CHILDREN_SOCIAL_ALLOWANCE''))
2606 l_count = TO_NUMBER(l_count_child)
2607 l_amount = l_child_allowance * l_count
2608
2609
2610 RETURN l_amount, l_count, l_child_allowance
2611 )
2612 ELSE
2613 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377436_AE_CHILD_ALLW_NA'',''LEGISLATION:''||l_local_nat)
2614 RETURN l_mesg)
2615
2616
2617
2618
2619 /*====================== End Program ==================*/'
2620 ,p_object_version_number => l_ovn
2621 ,p_effective_date => l_effective_date
2622 );
2623 --
2624 --
2625 ---------------------------------------------------------------------------------
2626 -- SECTION 3 : Balances and Classification
2627 ---------------------------------------------------------------------------------
2628 t_dim(1) := 'Assignment Inception To Date';
2629 t_dim(2) := 'Assignment Run';
2630 --================
2631 -- Primary Balance
2632 --================
2633 pay_sbt_ins.ins
2634 (p_balance_type_id => l_primary_bal_id
2635 ,p_template_id => l_template_id
2636 ,p_assignment_remuneration_flag => 'N'
2637 ,p_balance_name => ''
2638 ,p_reporting_name => ''
2639 ,p_comments => null
2640 ,p_balance_uom => 'M'
2641 ,p_currency_code => 'AED'
2642 ,p_object_version_number => l_ovn
2643 ,p_effective_date => l_effective_date
2644 );
2645 -- create the defined balances
2646 FOR i IN 1..2 LOOP
2647 pay_sdb_ins.ins
2648 (p_defined_balance_id => l_defined_bal_id
2649 ,p_balance_type_id => l_primary_bal_id
2650 ,p_dimension_name => t_dim(i)
2651 ,p_object_version_number => l_ovn
2652 ,p_effective_date => l_effective_date
2653 );
2654 END LOOP;
2655 --
2656 ---------------------------------------------------------------------------------
2657 -- SECTION 4 : Elements
2658 ---------------------------------------------------------------------------------
2659 --====================
2660 -- b) 'Base' element.
2661 --====================
2662 pay_set_ins.ins
2663 (p_element_type_id => l_child_element_id
2664 ,p_template_id => l_template_id
2665 ,p_element_name => ''
2666 ,p_reporting_name => ''
2667 ,p_relative_processing_priority => 0
2668 ,p_processing_type => 'R'
2669 ,p_classification_name => 'Earnings'
2670 ,p_input_currency_code => 'AED'
2671 ,p_output_currency_code => 'AED'
2672 ,p_multiple_entries_allowed_fla => 'N'
2673 ,p_post_termination_rule => 'F'
2674 ,p_process_in_run_flag => 'Y'
2675 ,p_additional_entry_allowed_fla => 'N'
2676 ,p_adjustment_only_flag => 'N'
2677 ,p_closed_for_entry_flag => 'N'
2678 ,p_indirect_only_flag => 'N'
2679 ,p_multiply_value_flag => 'N'
2680 ,p_standard_link_flag => 'N'
2681 ,p_payroll_formula_id => l_formula_id
2682 ,p_object_version_number => l_ovn
2683 ,p_effective_date => l_effective_date
2684 );
2685 -- Pay value
2686 pay_siv_ins.ins
2687 (p_input_value_id => l_pay_iv
2688 ,p_element_type_id => l_child_element_id
2689 ,p_display_sequence => 1
2690 ,p_generate_db_items_flag => 'Y'
2691 ,p_hot_default_flag => 'N'
2692 ,p_mandatory_flag => 'X'
2693 ,p_name => 'Pay Value'
2694 ,p_uom => 'M'
2695 ,p_object_version_number => l_ovn
2696 ,p_effective_date => l_effective_date
2697 );
2698
2699 pay_sbf_ins.ins
2700 (p_balance_feed_id => l_bal_feed_id
2701 ,p_balance_type_id => l_primary_bal_id
2702 ,p_input_value_id => l_pay_iv
2703 ,p_scale => 1
2704 ,p_object_version_number => l_ovn
2705 ,p_effective_date => l_effective_date
2706 );
2707
2708 -- Number of Dep. Childs
2709 pay_siv_ins.ins
2710 (p_input_value_id => l_num_child_iv
2711 ,p_element_type_id => l_child_element_id
2712 ,p_display_sequence => 2
2713 ,p_generate_db_items_flag => 'Y'
2714 ,p_hot_default_flag => 'N'
2715 ,p_mandatory_flag => 'X'
2716 ,p_name => 'Number of Dependent Children'
2717 ,p_uom => 'N'
2718 ,p_object_version_number => l_ovn
2719 ,p_effective_date => l_effective_date
2720 );
2721
2722 -- Allowance per child
2723 pay_siv_ins.ins
2724 (p_input_value_id => l_allowance_iv
2725 ,p_element_type_id => l_child_element_id
2726 ,p_display_sequence => 3
2727 ,p_generate_db_items_flag => 'Y'
2728 ,p_hot_default_flag => 'N'
2729 ,p_mandatory_flag => 'X'
2730 ,p_name => 'Allowance Per Child'
2731 ,p_uom => 'N'
2732 ,p_object_version_number => l_ovn
2733 ,p_effective_date => l_effective_date
2734 );
2735
2736 -------------------------------------------------------------------------
2737 -- SECTION 6 : Formula rules
2738 -------------------------------------------------------------------------
2739
2740 pay_sfr_ins.ins
2741 (p_formula_result_rule_id => l_rr_id
2742 ,p_shadow_element_type_id => l_child_element_id
2743 ,p_element_type_id => ''
2744 ,p_input_value_id => l_pay_iv
2745 ,p_result_name => 'L_AMOUNT'
2746 ,p_result_rule_type => 'D'
2747 ,p_object_version_number => l_ovn
2748 ,p_effective_date => l_effective_date
2749 );
2750
2751 pay_sfr_ins.ins
2752 (p_formula_result_rule_id => l_rr_id
2753 ,p_shadow_element_type_id => l_child_element_id
2754 ,p_element_type_id => ''
2755 ,p_input_value_id => l_num_child_iv
2756 ,p_result_name => 'L_COUNT'
2757 ,p_result_rule_type => 'D'
2758 ,p_object_version_number => l_ovn
2759 ,p_effective_date => l_effective_date
2760 );
2761
2762 pay_sfr_ins.ins
2763 (p_formula_result_rule_id => l_rr_id
2764 ,p_shadow_element_type_id => l_child_element_id
2765 ,p_element_type_id => ''
2766 ,p_input_value_id => l_allowance_iv
2767 ,p_result_name => 'L_CHILD_ALLOWANCE'
2768 ,p_result_rule_type => 'D'
2769 ,p_object_version_number => l_ovn
2770 ,p_effective_date => l_effective_date
2771 );
2772
2773 -- Message (Information)
2774 pay_sfr_ins.ins
2775 (p_formula_result_rule_id => l_rr_id
2776 ,p_shadow_element_type_id => l_child_element_id
2777 ,p_result_name => 'L_MESG'
2778 ,p_result_rule_type => 'M'
2779 ,p_severity_level => 'I'
2780 ,p_object_version_number => l_ovn
2781 ,p_effective_date => l_effective_date
2782 );
2783 -------------------------------------------------------------------------
2784 -- SECTION 7 : Insert into pay_ele_tmplt_class_usages
2785 -------------------------------------------------------------------------
2786 DECLARE
2787 CURSOR csr_get_class_id IS
2788 SELECT classification_id
2789 FROM pay_element_classifications
2790 WHERE legislation_code = 'AE'
2791 AND classification_name = 'Earnings';
2792 l_classification_id NUMBER;
2793
2794 BEGIN
2795 OPEN csr_get_class_id;
2796 FETCH csr_get_class_id into l_classification_id;
2797 CLOSE csr_get_class_id;
2798
2799 INSERT INTO pay_ele_tmplt_class_usages
2800 (ele_template_classification_id
2801 ,classification_id
2802 ,template_id
2803 ,display_process_mode
2804 ,display_arrearage)
2805 VALUES
2806 (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
2807 ,l_classification_id
2808 ,l_template_id
2809 ,NULL
2810 ,NULL);
2811 END;
2812
2813 END create_child_allw_template;
2814
2815 ------------------------------------------------------------------------
2816 ------------------------------------------------------------------------
2817 -- Procedure create_social_allw_template
2818 -- This procedure is used to create social allowance template
2819 ------------------------------------------------------------------------
2820 ------------------------------------------------------------------------
2821 PROCEDURE create_social_allw_template IS
2822 --
2823 TYPE Char80_Table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
2824 t_dim Char80_Table;
2825 --
2826 l_template_id number;
2827 l_defined_bal_id number;
2828 l_effective_date date;
2829 l_ovn number;
2830 l_formula_id number;
2831 l_rr_id number;
2832 l_primary_bal_id number;
2833 l_secondary_bal_id number;
2834 l_soc_element_id number;
2835 l_pay_iv number;
2836 l_bal_feed_id number;
2837 --
2838 CURSOR c_template IS
2839 SELECT template_id
2840 FROM pay_element_templates
2841 WHERE template_name = 'Social Allowance Template'
2842 AND template_type = 'T';
2843 --
2844 BEGIN
2845 ----------------------------------------------------------------------------
2846 -- Delete the existing template
2847 ----------------------------------------------------------------------------
2848 FOR c_rec in c_template LOOP
2849 l_template_id := c_rec.template_id;
2850
2851 DELETE FROM pay_ele_tmplt_class_usages
2852 WHERE template_id = l_template_id;
2853
2854 pay_element_template_api.delete_user_structure
2855 (p_validate => false
2856 ,p_drop_formula_packages => true
2857 ,p_template_id => l_template_id);
2858 END LOOP;
2859 --
2860 l_effective_date := to_date('0001/01/01', 'YYYY/MM/DD');
2861 ------------------------------------------------------------------------
2862 -- SECTION1 :
2863 ------------------------------------------------------------------------
2864 pay_etm_ins.ins
2865 (p_template_id => l_template_id
2866 ,p_effective_date => l_effective_date
2867 ,p_template_type => 'T'
2868 ,p_template_name => 'Social Allowance Template'
2869 ,p_base_processing_priority => 2500
2870 ,p_max_base_name_length => 50
2871 ,p_version_number => 1
2872 ,p_legislation_code => 'AE'
2873 ,p_object_version_number => l_ovn
2874 );
2875 -----------------------------------------------------------------------
2876 -- SECTION2 : Exclusion Rules.
2877 -----------------------------------------------------------------------
2878 --None
2879 ------------------------------------------------------------------------
2880 -- SECTION 3 : Formulas
2881 ------------------------------------------------------------------------
2882 ------------------------
2883 -- a) Formula
2884 ------------------------
2885 pay_sf_ins.ins
2886 (p_formula_id => l_formula_id
2887 ,p_template_type => 'T'
2888 ,p_legislation_code => 'AE'
2889 ,p_formula_name => '_SOC_FF'
2890 ,p_description => 'AE Formula for Social Allowance'
2891 ,p_formula_text =>
2892 '
2893 /* Description: Formula for social allowance template in UAE legislation
2894 */
2895 /*Check if Local Nationality is defined*/
2896 l_exists = AE_LOCAL_NATIONALITY_NOT_DEFINED()
2897
2898 IF l_exists = ''NOTEXISTS'' THEN
2899 (
2900 l_mesg = AE_GET_MESSAGE(''PER'',''HR_377425_AE_LOC_NAT_NOT_DEF'')
2901 return l_mesg
2902 )
2903
2904 l_local_nat = AE_GET_LOCAL_NATIONALITY()
2905 l_matches = AE_LOCAL_NATIONALITY_MATCHES()
2906 IF l_matches = ''MATCH'' THEN
2907 (
2908 l_amount = 0
2909 l_info_type = ''MARITAL_STATUS''
2910 l_marital_status = AE_GET_EMP_DETAILS(l_info_type)
2911 IF l_marital_status = ''NO_DATA_FOUND'' THEN
2912 ( l_mesg = AE_GET_MESSAGE(''PER'',''HR_377440_AE_NO_MAR_STATUS'',''ELEMENT:''||ELEMENT_NAME)
2913 RETURN l_mesg)
2914
2915 IF l_marital_status = ''M'' THEN
2916 l_amount = TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''SOCIAL_ALLOWANCE_MARRIED''))
2917 ELSE
2918 l_amount = TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''SOCIAL_ALLOWANCE_SINGLE''))
2919
2920 RETURN l_amount,l_marital_status
2921 )
2922 ELSE
2923 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377437_AE_SOCIAL_ALLW_NA'',''LEGISLATION:''||l_local_nat)
2924 RETURN l_mesg)
2925
2926
2927 /*====================== End Program =================*/'
2928 ,p_object_version_number => l_ovn
2929 ,p_effective_date => l_effective_date
2930 );
2931 --
2932 --
2933 ---------------------------------------------------------------------------------
2934 -- SECTION 3 : Balances and Classification
2935 ---------------------------------------------------------------------------------
2936 t_dim(1) := 'Assignment Inception To Date';
2937 t_dim(2) := 'Assignment Run';
2938 --================
2939 -- Primary Balance
2940 --================
2941 pay_sbt_ins.ins
2942 (p_balance_type_id => l_primary_bal_id
2943 ,p_template_id => l_template_id
2944 ,p_assignment_remuneration_flag => 'N'
2945 ,p_balance_name => ''
2946 ,p_reporting_name => ''
2947 ,p_comments => null
2948 ,p_balance_uom => 'M'
2949 ,p_currency_code => 'AED'
2950 ,p_object_version_number => l_ovn
2951 ,p_effective_date => l_effective_date
2952 );
2953 -- create the defined balances
2954 FOR i IN 1..2 LOOP
2955 pay_sdb_ins.ins
2956 (p_defined_balance_id => l_defined_bal_id
2957 ,p_balance_type_id => l_primary_bal_id
2958 ,p_dimension_name => t_dim(i)
2959 ,p_object_version_number => l_ovn
2960 ,p_effective_date => l_effective_date
2961 );
2962 END LOOP;
2963 --
2964 ---------------------------------------------------------------------------------
2965 -- SECTION 4 : Elements
2966 ---------------------------------------------------------------------------------
2967 --====================
2968 -- b) 'Base' element.
2969 --====================
2970 pay_set_ins.ins
2971 (p_element_type_id => l_soc_element_id
2972 ,p_template_id => l_template_id
2973 ,p_element_name => ''
2974 ,p_reporting_name => ''
2975 ,p_relative_processing_priority => 0
2976 ,p_processing_type => 'R'
2977 ,p_classification_name => 'Earnings'
2978 ,p_input_currency_code => 'AED'
2979 ,p_output_currency_code => 'AED'
2980 ,p_multiple_entries_allowed_fla => 'N'
2981 ,p_post_termination_rule => 'F'
2982 ,p_process_in_run_flag => 'Y'
2983 ,p_additional_entry_allowed_fla => 'N'
2984 ,p_adjustment_only_flag => 'N'
2985 ,p_closed_for_entry_flag => 'N'
2986 ,p_indirect_only_flag => 'N'
2987 ,p_multiply_value_flag => 'N'
2988 ,p_standard_link_flag => 'N'
2989 ,p_payroll_formula_id => l_formula_id
2990 ,p_object_version_number => l_ovn
2991 ,p_effective_date => l_effective_date
2992 );
2993 -- Pay value
2994 pay_siv_ins.ins
2995 (p_input_value_id => l_pay_iv
2996 ,p_element_type_id => l_soc_element_id
2997 ,p_display_sequence => 1
2998 ,p_generate_db_items_flag => 'Y'
2999 ,p_hot_default_flag => 'N'
3000 ,p_mandatory_flag => 'X'
3001 ,p_name => 'Pay Value'
3002 ,p_uom => 'M'
3003 ,p_object_version_number => l_ovn
3004 ,p_effective_date => l_effective_date
3005 );
3006
3007 pay_sbf_ins.ins
3008 (p_balance_feed_id => l_bal_feed_id
3009 ,p_balance_type_id => l_primary_bal_id
3010 ,p_input_value_id => l_pay_iv
3011 ,p_scale => 1
3012 ,p_object_version_number => l_ovn
3013 ,p_effective_date => l_effective_date
3014 );
3015
3016
3017 -------------------------------------------------------------------------
3018 -- SECTION 6 : Formula rules
3019 -------------------------------------------------------------------------
3020
3021 pay_sfr_ins.ins
3022 (p_formula_result_rule_id => l_rr_id
3023 ,p_shadow_element_type_id => l_soc_element_id
3024 ,p_element_type_id => ''
3025 ,p_input_value_id => l_pay_iv
3026 ,p_result_name => 'L_AMOUNT'
3027 ,p_result_rule_type => 'D'
3028 ,p_object_version_number => l_ovn
3029 ,p_effective_date => l_effective_date
3030 );
3031
3032 -- Message (Information)
3033 pay_sfr_ins.ins
3034 (p_formula_result_rule_id => l_rr_id
3035 ,p_shadow_element_type_id => l_soc_element_id
3036 ,p_result_name => 'L_MESG'
3037 ,p_result_rule_type => 'M'
3038 ,p_severity_level => 'I'
3039 ,p_object_version_number => l_ovn
3040 ,p_effective_date => l_effective_date
3041 );
3042
3043 -------------------------------------------------------------------------
3044 -- SECTION 7 : Insert into pay_ele_tmplt_class_usages
3045 -------------------------------------------------------------------------
3046 DECLARE
3047 CURSOR csr_get_class_id IS
3048 SELECT classification_id
3049 FROM pay_element_classifications
3050 WHERE legislation_code = 'AE'
3051 AND classification_name = 'Earnings';
3052 l_classification_id NUMBER;
3053
3054 BEGIN
3055 OPEN csr_get_class_id;
3056 FETCH csr_get_class_id into l_classification_id;
3057 CLOSE csr_get_class_id;
3058
3059 INSERT INTO pay_ele_tmplt_class_usages
3060 (ele_template_classification_id
3061 ,classification_id
3062 ,template_id
3063 ,display_process_mode
3064 ,display_arrearage)
3065 VALUES
3066 (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
3067 ,l_classification_id
3068 ,l_template_id
3069 ,NULL
3070 ,NULL);
3071 END;
3072
3073 END create_social_allw_template;
3074
3075 ------------------------------------------------------------------------
3076 ------------------------------------------------------------------------
3077 -- Procedure create_shift_allw_template
3078 -- This procedure is used to create shift allowance template
3079 ------------------------------------------------------------------------
3080 ------------------------------------------------------------------------
3081 PROCEDURE create_shift_allw_template IS
3082 --
3083 TYPE Char80_Table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
3084 t_dim Char80_Table;
3085 --
3086 l_template_id number;
3087 l_defined_bal_id number;
3088 l_effective_date date;
3089 l_ovn number;
3090 l_formula_id number;
3091 l_rr_id number;
3092 l_primary_bal_id number;
3093 l_secondary_bal_id number;
3094 l_shift_element_id number;
3095 l_pay_iv number;
3096 l_override_amount_iv number;
3097 l_usage_iv number;
3098 l_bal_feed_id number;
3099 --
3100 CURSOR c_template IS
3101 SELECT template_id
3102 FROM pay_element_templates
3103 WHERE template_name = 'Shift Allowance Template'
3104 AND template_type = 'T';
3105 --
3106 BEGIN
3107 ----------------------------------------------------------------------------
3108 -- Delete the existing template
3109 ----------------------------------------------------------------------------
3110 FOR c_rec in c_template LOOP
3111 l_template_id := c_rec.template_id;
3112
3113 DELETE FROM pay_ele_tmplt_class_usages
3114 WHERE template_id = l_template_id;
3115
3116 pay_element_template_api.delete_user_structure
3117 (p_validate => false
3118 ,p_drop_formula_packages => true
3119 ,p_template_id => l_template_id);
3120 END LOOP;
3121 --
3122 l_effective_date := to_date('0001/01/01', 'YYYY/MM/DD');
3123 ------------------------------------------------------------------------
3124 -- SECTION1 :
3125 ------------------------------------------------------------------------
3126 pay_etm_ins.ins
3127 (p_template_id => l_template_id
3128 ,p_effective_date => l_effective_date
3129 ,p_template_type => 'T'
3130 ,p_template_name => 'Shift Allowance Template'
3131 ,p_base_processing_priority => 2500
3132 ,p_max_base_name_length => 50
3133 ,p_version_number => 1
3134 ,p_legislation_code => 'AE'
3135 ,p_object_version_number => l_ovn
3136 );
3137 -----------------------------------------------------------------------
3138 -- SECTION2 : Exclusion Rules.
3139 -----------------------------------------------------------------------
3140 --None
3141 ------------------------------------------------------------------------
3142 -- SECTION 3 : Formulas
3143 ------------------------------------------------------------------------
3144 ------------------------
3145 -- a) Formula
3146 ------------------------
3147 pay_sf_ins.ins
3148 (p_formula_id => l_formula_id
3149 ,p_template_type => 'T'
3150 ,p_legislation_code => 'AE'
3151 ,p_formula_name => '_SH_FF'
3152 ,p_description => 'AE Formula for Shift Allowance'
3153 ,p_formula_text =>
3154 '
3155 /* Description: Formula for shift allowance template in UAE legislation
3156 */
3157
3158
3159 Inputs are Rate_Value_to_be_used_as (TEXT)
3160 ,Override_Amount
3161
3162 DEFAULT FOR Override_Amount IS 0
3163
3164 IF Override_Amount > 0 THEN
3165 (l_amount = Override_Amount
3166 RETURN l_amount)
3167 ELSE
3168 (
3169
3170 l_amount = 0
3171
3172 /*Check if Local Nationality is defined*/
3173 l_exists = AE_LOCAL_NATIONALITY_NOT_DEFINED()
3174
3175 IF l_exists = ''NOTEXISTS'' THEN
3176 (
3177 l_mesg = AE_GET_MESSAGE(''PER'',''HR_377425_AE_LOC_NAT_NOT_DEF'')
3178 return l_mesg
3179 )
3180
3181 l_local_nat = AE_GET_LOCAL_NATIONALITY()
3182
3183 l_matches = AE_LOCAL_NATIONALITY_MATCHES()
3184
3185 IF l_matches = ''MATCH'' THEN
3186 l_local = ''Y''
3187 ELSE
3188 l_local = ''N''
3189
3190
3191 IF l_local = ''Y'' THEN
3192 l_grade_rate_table_name = GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''SHIFT_ALLOWANCE_GRADE_RATE_TABLE_NATIONAL'')
3193 ELSE
3194 l_grade_rate_table_name = GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''SHIFT_ALLOWANCE_GRADE_RATE_TABLE_NON_NATIONAL'')
3195
3196 l_table_exists = ''Y''
3197 l_allowance_value = AE_GET_RATE_FROM_TAB_NAME(l_grade_rate_table_name,l_table_exists)
3198
3199 IF l_table_exists <> ''Y'' THEN
3200 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377435_AE_INV_GRADE_RATE'')
3201 RETURN l_mesg)
3202
3203 If l_allowance_value = 0 THEN
3204 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377434_AE_NO_GRADE_ALLW'')
3205 RETURN l_mesg)
3206
3207 IF Rate_Value_to_be_used_as = ''P'' THEN
3208 (
3209 monthly_salary = AE_GRATUITY_SALARY_FORMULA()
3210 l_amount = (l_allowance_value * monthly_salary)/100
3211 l_amount = LEAST(GREATEST(L_AMOUNT, TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''SHIFT_ALLOWANCE_MIN''))),TO_NUMBER(GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''SHIFT_ALLOWANCE_MAX'')))
3212 )
3213
3214 ELSE
3215 l_amount = l_allowance_value
3216
3217 RETURN l_amount
3218
3219 )
3220
3221
3222 /*==================== End Program =================*/'
3223 ,p_object_version_number => l_ovn
3224 ,p_effective_date => l_effective_date
3225 );
3226 --
3227 --
3228 ---------------------------------------------------------------------------------
3229 -- SECTION 3 : Balances and Classification
3230 ---------------------------------------------------------------------------------
3231 t_dim(1) := 'Assignment Inception To Date';
3232 t_dim(2) := 'Assignment Run';
3233 --================
3234 -- Primary Balance
3235 --================
3236 pay_sbt_ins.ins
3237 (p_balance_type_id => l_primary_bal_id
3238 ,p_template_id => l_template_id
3239 ,p_assignment_remuneration_flag => 'N'
3240 ,p_balance_name => ''
3241 ,p_reporting_name => ''
3242 ,p_comments => null
3243 ,p_balance_uom => 'M'
3244 ,p_currency_code => 'AED'
3245 ,p_object_version_number => l_ovn
3246 ,p_effective_date => l_effective_date
3247 );
3248 -- create the defined balances
3249 FOR i IN 1..2 LOOP
3250 pay_sdb_ins.ins
3251 (p_defined_balance_id => l_defined_bal_id
3252 ,p_balance_type_id => l_primary_bal_id
3253 ,p_dimension_name => t_dim(i)
3254 ,p_object_version_number => l_ovn
3255 ,p_effective_date => l_effective_date
3256 );
3257 END LOOP;
3258 --
3259 ---------------------------------------------------------------------------------
3260 -- SECTION 4 : Elements
3261 ---------------------------------------------------------------------------------
3262 --====================
3263 -- b) 'Base' element.
3264 --====================
3265 pay_set_ins.ins
3266 (p_element_type_id => l_shift_element_id
3267 ,p_template_id => l_template_id
3268 ,p_element_name => ''
3269 ,p_reporting_name => ''
3270 ,p_relative_processing_priority => 0
3271 ,p_processing_type => 'R'
3272 ,p_classification_name => 'Earnings'
3273 ,p_input_currency_code => 'AED'
3274 ,p_output_currency_code => 'AED'
3275 ,p_multiple_entries_allowed_fla => 'N'
3276 ,p_post_termination_rule => 'F'
3277 ,p_process_in_run_flag => 'Y'
3278 ,p_additional_entry_allowed_fla => 'N'
3279 ,p_adjustment_only_flag => 'N'
3280 ,p_closed_for_entry_flag => 'N'
3281 ,p_indirect_only_flag => 'N'
3282 ,p_multiply_value_flag => 'N'
3283 ,p_standard_link_flag => 'N'
3284 ,p_payroll_formula_id => l_formula_id
3285 ,p_object_version_number => l_ovn
3286 ,p_effective_date => l_effective_date
3287 );
3288 -- Pay value
3289 pay_siv_ins.ins
3290 (p_input_value_id => l_pay_iv
3291 ,p_element_type_id => l_shift_element_id
3292 ,p_display_sequence => 1
3293 ,p_generate_db_items_flag => 'Y'
3294 ,p_hot_default_flag => 'N'
3295 ,p_mandatory_flag => 'X'
3296 ,p_name => 'Pay Value'
3297 ,p_uom => 'M'
3298 ,p_object_version_number => l_ovn
3299 ,p_effective_date => l_effective_date
3300 );
3301
3302 pay_sbf_ins.ins
3303 (p_balance_feed_id => l_bal_feed_id
3304 ,p_balance_type_id => l_primary_bal_id
3305 ,p_input_value_id => l_pay_iv
3306 ,p_scale => 1
3307 ,p_object_version_number => l_ovn
3308 ,p_effective_date => l_effective_date
3309 );
3310
3311 --
3312 pay_siv_ins.ins
3313 (p_input_value_id => l_usage_iv
3314 ,p_element_type_id => l_shift_element_id
3315 ,p_display_sequence => 2
3316 ,p_generate_db_items_flag => 'Y'
3317 ,p_hot_default_flag => 'N'
3318 ,p_mandatory_flag => 'Y'
3319 ,p_name => 'Rate value to be used as'
3320 ,p_uom => 'C'
3321 ,p_lookup_type => 'AE_ALLOWANCE_USAGE'
3322 ,p_object_version_number => l_ovn
3323 ,p_effective_date => l_effective_date
3324 );
3325
3326 pay_siv_ins.ins
3327 (p_input_value_id => l_override_amount_iv
3328 ,p_element_type_id => l_shift_element_id
3329 ,p_display_sequence => 3
3330 ,p_generate_db_items_flag => 'Y'
3331 ,p_hot_default_flag => 'N'
3332 ,p_mandatory_flag => 'N'
3333 ,p_name => 'Override Amount'
3334 ,p_uom => 'M'
3335 ,p_object_version_number => l_ovn
3336 ,p_effective_date => l_effective_date
3337 );
3338
3339 -------------------------------------------------------------------------
3340 -- SECTION 6 : Formula rules
3341 -------------------------------------------------------------------------
3342
3343 pay_sfr_ins.ins
3344 (p_formula_result_rule_id => l_rr_id
3345 ,p_shadow_element_type_id => l_shift_element_id
3346 ,p_element_type_id => ''
3347 ,p_input_value_id => l_pay_iv
3348 ,p_result_name => 'L_AMOUNT'
3349 ,p_result_rule_type => 'D'
3350 ,p_object_version_number => l_ovn
3351 ,p_effective_date => l_effective_date
3352 );
3353
3354 -- Message (Information)
3355 pay_sfr_ins.ins
3356 (p_formula_result_rule_id => l_rr_id
3357 ,p_shadow_element_type_id => l_shift_element_id
3358 ,p_result_name => 'L_MESG'
3359 ,p_result_rule_type => 'M'
3360 ,p_severity_level => 'I'
3361 ,p_object_version_number => l_ovn
3362 ,p_effective_date => l_effective_date
3363 );
3364
3365 -------------------------------------------------------------------------
3366 -- SECTION 7 : Insert into pay_ele_tmplt_class_usages
3367 -------------------------------------------------------------------------
3368 DECLARE
3369 CURSOR csr_get_class_id IS
3370 SELECT classification_id
3371 FROM pay_element_classifications
3372 WHERE legislation_code = 'AE'
3373 AND classification_name = 'Earnings';
3374 l_classification_id NUMBER;
3375
3376 BEGIN
3377 OPEN csr_get_class_id;
3378 FETCH csr_get_class_id into l_classification_id;
3379 CLOSE csr_get_class_id;
3380
3381 INSERT INTO pay_ele_tmplt_class_usages
3382 (ele_template_classification_id
3383 ,classification_id
3384 ,template_id
3385 ,display_process_mode
3386 ,display_arrearage)
3387 VALUES
3388 (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
3389 ,l_classification_id
3390 ,l_template_id
3391 ,NULL
3392 ,NULL);
3393 END;
3394
3395 END create_shift_allw_template;
3396
3397 ------------------------------------------------------------------------
3398 ------------------------------------------------------------------------
3399 -- Procedure create_hrly_basic_sal_template
3400 -- This procedure is used to create hourly basic salary template
3401 ------------------------------------------------------------------------
3402 ------------------------------------------------------------------------
3403 PROCEDURE create_hrly_basic_sal_template IS
3404 --
3405 TYPE Char80_Table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
3406 t_dim Char80_Table;
3407 --
3408 l_template_id number;
3409 l_defined_bal_id number;
3410 l_effective_date date;
3411 l_ovn number;
3412 l_formula_id number;
3413 l_rr_id number;
3414 l_primary_bal_id number;
3415 l_secondary_bal_id number;
3416 l_hrly_element_id number;
3417 l_pay_iv number;
3418 l_hrs_worked_iv number;
3419 l_rate_name_iv number;
3420 l_bal_feed_id number;
3421 --
3422 CURSOR c_template IS
3423 SELECT template_id
3424 FROM pay_element_templates
3425 WHERE template_name = 'Hourly Salary Template'
3426 AND template_type = 'T';
3427 --
3428 BEGIN
3429 ----------------------------------------------------------------------------
3430 -- Delete the existing template
3431 ----------------------------------------------------------------------------
3432 FOR c_rec in c_template LOOP
3433 l_template_id := c_rec.template_id;
3434
3435 DELETE FROM pay_ele_tmplt_class_usages
3436 WHERE template_id = l_template_id;
3437
3438 pay_element_template_api.delete_user_structure
3439 (p_validate => false
3440 ,p_drop_formula_packages => true
3441 ,p_template_id => l_template_id);
3442 END LOOP;
3443 --
3444 l_effective_date := to_date('0001/01/01', 'YYYY/MM/DD');
3445 ------------------------------------------------------------------------
3446 -- SECTION1 :
3447 ------------------------------------------------------------------------
3448 pay_etm_ins.ins
3449 (p_template_id => l_template_id
3450 ,p_effective_date => l_effective_date
3451 ,p_template_type => 'T'
3452 ,p_template_name => 'Hourly Salary Template'
3453 ,p_base_processing_priority => 2500
3454 ,p_max_base_name_length => 50
3455 ,p_version_number => 1
3456 ,p_legislation_code => 'AE'
3457 ,p_object_version_number => l_ovn
3458 );
3459 -----------------------------------------------------------------------
3460 -- SECTION2 : Exclusion Rules.
3461 -----------------------------------------------------------------------
3462 --None
3463 ------------------------------------------------------------------------
3464 -- SECTION 3 : Formulas
3465 ------------------------------------------------------------------------
3466 ------------------------
3467 -- a) Formula
3468 ------------------------
3469 pay_sf_ins.ins
3470 (p_formula_id => l_formula_id
3471 ,p_template_type => 'T'
3472 ,p_legislation_code => 'AE'
3473 ,p_formula_name => '_HOURLY_FF'
3474 ,p_description => 'AE Formula for hourly salary'
3475 ,p_formula_text =>
3476 '
3477 /* Description: Formula for hourly basic salary template in UAE legislation
3478 */
3479 Inputs are Hours_Worked_in_a_Month
3480 ,Grade_Rate (TEXT)
3481
3482 Hourly_Rate = AE_GET_RATE_FROM_TAB_ID(TO_NUMBER(Grade_Rate))
3483
3484 If Hourly_Rate = 0 THEN
3485 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377434_AE_NO_GRADE_ALLW'')
3486 RETURN l_mesg)
3487 ELSE
3488 (l_amount = Hours_Worked_in_a_Month * Hourly_Rate
3489
3490 RETURN l_amount
3491 )
3492
3493 /*====================== End Program ================*/'
3494 ,p_object_version_number => l_ovn
3495 ,p_effective_date => l_effective_date
3496 );
3497 --
3498 --
3499 ---------------------------------------------------------------------------------
3500 -- SECTION 3 : Balances and Classification
3501 ---------------------------------------------------------------------------------
3502 t_dim(1) := 'Assignment Inception To Date';
3503 t_dim(2) := 'Assignment Run';
3504 --================
3505 -- Primary Balance
3506 --================
3507 pay_sbt_ins.ins
3508 (p_balance_type_id => l_primary_bal_id
3509 ,p_template_id => l_template_id
3510 ,p_assignment_remuneration_flag => 'N'
3511 ,p_balance_name => ''
3512 ,p_reporting_name => ''
3513 ,p_comments => null
3514 ,p_balance_uom => 'M'
3515 ,p_currency_code => 'AED'
3516 ,p_object_version_number => l_ovn
3517 ,p_effective_date => l_effective_date
3518 );
3519 -- create the defined balances
3520 FOR i IN 1..2 LOOP
3521 pay_sdb_ins.ins
3522 (p_defined_balance_id => l_defined_bal_id
3523 ,p_balance_type_id => l_primary_bal_id
3524 ,p_dimension_name => t_dim(i)
3525 ,p_object_version_number => l_ovn
3526 ,p_effective_date => l_effective_date
3527 );
3528 END LOOP;
3529 --
3530 ---------------------------------------------------------------------------------
3531 -- SECTION 4 : Elements
3532 ---------------------------------------------------------------------------------
3533 --====================
3534 -- b) 'Base' element.
3535 --====================
3536 pay_set_ins.ins
3537 (p_element_type_id => l_hrly_element_id
3538 ,p_template_id => l_template_id
3539 ,p_element_name => ''
3540 ,p_reporting_name => ''
3541 ,p_relative_processing_priority => 0
3542 ,p_processing_type => 'R'
3543 ,p_classification_name => 'Earnings'
3544 ,p_input_currency_code => 'AED'
3545 ,p_output_currency_code => 'AED'
3546 ,p_multiple_entries_allowed_fla => 'N'
3547 ,p_post_termination_rule => 'F'
3548 ,p_process_in_run_flag => 'Y'
3549 ,p_additional_entry_allowed_fla => 'N'
3550 ,p_adjustment_only_flag => 'N'
3551 ,p_closed_for_entry_flag => 'N'
3552 ,p_indirect_only_flag => 'N'
3553 ,p_multiply_value_flag => 'N'
3554 ,p_standard_link_flag => 'N'
3555 ,p_payroll_formula_id => l_formula_id
3556 ,p_object_version_number => l_ovn
3557 ,p_effective_date => l_effective_date
3558 );
3559 -- Pay value
3560 pay_siv_ins.ins
3561 (p_input_value_id => l_pay_iv
3562 ,p_element_type_id => l_hrly_element_id
3563 ,p_display_sequence => 1
3564 ,p_generate_db_items_flag => 'Y'
3565 ,p_hot_default_flag => 'N'
3566 ,p_mandatory_flag => 'X'
3567 ,p_name => 'Pay Value'
3568 ,p_uom => 'M'
3569 ,p_object_version_number => l_ovn
3570 ,p_effective_date => l_effective_date
3571 );
3572
3573 pay_sbf_ins.ins
3574 (p_balance_feed_id => l_bal_feed_id
3575 ,p_balance_type_id => l_primary_bal_id
3576 ,p_input_value_id => l_pay_iv
3577 ,p_scale => 1
3578 ,p_object_version_number => l_ovn
3579 ,p_effective_date => l_effective_date
3580 );
3581
3582 pay_siv_ins.ins
3583 (p_input_value_id => l_hrs_worked_iv
3584 ,p_element_type_id => l_hrly_element_id
3585 --,p_exclusion_rule_id => l_excl_rule_id_perc
3586 ,p_display_sequence => 2
3587 ,p_generate_db_items_flag => 'Y'
3588 ,p_hot_default_flag => 'N'
3589 ,p_mandatory_flag => 'Y'
3590 ,p_name => 'Hours Worked in a Month'
3591 ,p_uom => 'H_DECIMAL2'
3592 ,p_object_version_number => l_ovn
3593 ,p_effective_date => l_effective_date
3594 );
3595
3596 pay_siv_ins.ins
3597 (p_input_value_id => l_rate_name_iv
3598 ,p_element_type_id => l_hrly_element_id
3599 --,p_exclusion_rule_id => l_excl_rule_id_perc
3600 ,p_display_sequence => 3
3601 ,p_generate_db_items_flag => 'Y'
3602 ,p_hot_default_flag => 'N'
3603 ,p_mandatory_flag => 'Y'
3604 ,p_name => 'Grade Rate'
3605 ,p_uom => 'C'
3606 ,p_object_version_number => l_ovn
3607 ,p_effective_date => l_effective_date
3608 );
3609
3610 -------------------------------------------------------------------------
3611 -- SECTION 6 : Formula rules
3612 -------------------------------------------------------------------------
3613
3614 pay_sfr_ins.ins
3615 (p_formula_result_rule_id => l_rr_id
3616 ,p_shadow_element_type_id => l_hrly_element_id
3617 ,p_element_type_id => ''
3618 ,p_input_value_id => l_pay_iv
3619 ,p_result_name => 'L_AMOUNT'
3620 ,p_result_rule_type => 'D'
3621 ,p_object_version_number => l_ovn
3622 ,p_effective_date => l_effective_date
3623 );
3624
3625 -- Message (Information)
3626 pay_sfr_ins.ins
3627 (p_formula_result_rule_id => l_rr_id
3628 ,p_shadow_element_type_id => l_hrly_element_id
3629 ,p_result_name => 'L_MESG'
3630 ,p_result_rule_type => 'M'
3631 ,p_severity_level => 'I'
3632 ,p_object_version_number => l_ovn
3633 ,p_effective_date => l_effective_date
3634 );
3635
3636 -------------------------------------------------------------------------
3637 -- SECTION 7 : Insert into pay_ele_tmplt_class_usages
3638 -------------------------------------------------------------------------
3639 DECLARE
3640 CURSOR csr_get_class_id IS
3641 SELECT classification_id
3642 FROM pay_element_classifications
3643 WHERE legislation_code = 'AE'
3644 AND classification_name = 'Earnings';
3645 l_classification_id NUMBER;
3646
3647 BEGIN
3648 OPEN csr_get_class_id;
3649 FETCH csr_get_class_id into l_classification_id;
3650 CLOSE csr_get_class_id;
3651
3652 INSERT INTO pay_ele_tmplt_class_usages
3653 (ele_template_classification_id
3654 ,classification_id
3655 ,template_id
3656 ,display_process_mode
3657 ,display_arrearage)
3658 VALUES
3659 (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
3660 ,l_classification_id
3661 ,l_template_id
3662 ,NULL
3663 ,NULL);
3664 END;
3665
3666 END create_hrly_basic_sal_template;
3667
3668 ------------------------------------------------------------------------
3669 ------------------------------------------------------------------------
3670 -- Procedure create_ot_allw_template
3671 -- This procedure is used to create overtime allowance template
3672 ------------------------------------------------------------------------
3673 ------------------------------------------------------------------------
3674 PROCEDURE create_ot_allw_template IS
3675 --
3676 TYPE Char80_Table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
3677 t_dim Char80_Table;
3678 --
3679 l_template_id number;
3680 l_defined_bal_id number;
3681 l_effective_date date;
3682 l_ovn number;
3683 l_formula_id number;
3684 l_rr_id number;
3685 l_primary_bal_id number;
3686 l_secondary_bal_id number;
3687 l_ot_element_id number;
3688 l_pay_iv number;
3689 l_hrs_regular_iv number;
3690 l_hrs_rest_iv number;
3691 l_rate_regular_iv number;
3692 l_rate_rest_iv number;
3693 l_bal_feed_id number;
3694 --
3695 CURSOR c_template IS
3696 SELECT template_id
3697 FROM pay_element_templates
3698 WHERE template_name = 'Overtime Allowance Template'
3699 AND template_type = 'T';
3700 --
3701 BEGIN
3702 ----------------------------------------------------------------------------
3703 -- Delete the existing template
3704 ----------------------------------------------------------------------------
3705 FOR c_rec in c_template LOOP
3706 l_template_id := c_rec.template_id;
3707
3708 DELETE FROM pay_ele_tmplt_class_usages
3709 WHERE template_id = l_template_id;
3710
3711 pay_element_template_api.delete_user_structure
3712 (p_validate => false
3713 ,p_drop_formula_packages => true
3714 ,p_template_id => l_template_id);
3715 END LOOP;
3716 --
3717 l_effective_date := to_date('0001/01/01', 'YYYY/MM/DD');
3718 ------------------------------------------------------------------------
3719 -- SECTION1 :
3720 ------------------------------------------------------------------------
3721 pay_etm_ins.ins
3722 (p_template_id => l_template_id
3723 ,p_effective_date => l_effective_date
3724 ,p_template_type => 'T'
3725 ,p_template_name => 'Overtime Allowance Template'
3726 ,p_base_processing_priority => 2500
3727 ,p_max_base_name_length => 50
3728 ,p_version_number => 1
3729 ,p_legislation_code => 'AE'
3730 ,p_object_version_number => l_ovn
3731 );
3732 -----------------------------------------------------------------------
3733 -- SECTION2 : Exclusion Rules.
3734 -----------------------------------------------------------------------
3735 -- None
3736 ------------------------------------------------------------------------
3737 -- SECTION 3 : Formulas
3738 ------------------------------------------------------------------------
3739 ------------------------
3740 -- a) Formula
3741 ------------------------
3742 pay_sf_ins.ins
3743 (p_formula_id => l_formula_id
3744 ,p_template_type => 'T'
3745 ,p_legislation_code => 'AE'
3746 ,p_formula_name => '_OT_FF'
3747 ,p_description => 'AE Formula for Overtime Allowance'
3748 ,p_formula_text =>
3749 '
3750 /* Description: Formula for overtime allowance in UAE legislation
3751 */
3752
3753 Inputs are Extra_Hours_Worked
3754 ,Hours_Worked_on_rest_days
3755
3756 DEFAULT FOR Extra_Hours_Worked IS 0
3757 DEFAULT FOR Hours_Worked_on_rest_days IS 0
3758
3759 l_amount = 0
3760 l_monthly_salary = AE_GRATUITY_SALARY_FORMULA()
3761 l_hrly_salary = (l_monthly_salary * 12)/(365 * 8)
3762
3763 l_regular_rate_table_name = GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''OVERTIME_ALLOWANCE_TABLE_REGULAR_DAYS'')
3764
3765 l_table_exists = ''Y''
3766 l_regular_ot = AE_GET_RATE_FROM_TAB_NAME(l_regular_rate_table_name,l_table_exists)
3767
3768 IF l_table_exists <> ''Y'' THEN
3769 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377435_AE_INV_GRADE_RATE'')
3770 RETURN l_mesg)
3771
3772 If l_regular_ot = 0 THEN
3773 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377434_AE_NO_GRADE_ALLW'')
3774 RETURN l_mesg)
3775
3776 l_rest_days_rate_table_name = GET_TABLE_VALUE(''AE_ALLOWANCE_VALUES'',''ALLOWANCE_NAME'',''OVERTIME_ALLOWANCE_TABLE_REST_DAYS'')
3777
3778 l_table_exists = ''Y''
3779 l_rest_days_ot = AE_GET_RATE_FROM_TAB_NAME(l_rest_days_rate_table_name,l_table_exists)
3780
3781 IF l_table_exists <> ''Y'' THEN
3782 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377435_AE_INV_GRADE_RATE'')
3783 RETURN l_mesg)
3784
3785 If l_rest_days_ot = 0 THEN
3786 (l_mesg = AE_GET_MESSAGE(''PER'',''HR_377434_AE_NO_GRADE_ALLW'')
3787 RETURN l_mesg)
3788
3789 l_amount = (Extra_Hours_Worked * (l_regular_ot/100) * l_hrly_salary) + (Hours_Worked_on_rest_days * (l_rest_days_ot/100) * l_hrly_salary)
3790
3791 RETURN l_amount
3792 ,l_regular_ot
3793 ,l_rest_days_ot
3794
3795
3796 /*=================== End Program =================*/'
3797 ,p_object_version_number => l_ovn
3798 ,p_effective_date => l_effective_date
3799 );
3800 --
3801 --
3802 ---------------------------------------------------------------------------------
3803 -- SECTION 3 : Balances and Classification
3804 ---------------------------------------------------------------------------------
3805 t_dim(1) := 'Assignment Inception To Date';
3806 t_dim(2) := 'Assignment Run';
3807 --================
3808 -- Primary Balance
3809 --================
3810 pay_sbt_ins.ins
3811 (p_balance_type_id => l_primary_bal_id
3812 ,p_template_id => l_template_id
3813 ,p_assignment_remuneration_flag => 'N'
3814 ,p_balance_name => ''
3815 ,p_reporting_name => ''
3816 ,p_comments => null
3817 ,p_balance_uom => 'M'
3818 ,p_currency_code => 'AED'
3819 ,p_object_version_number => l_ovn
3820 ,p_effective_date => l_effective_date
3821 );
3822 -- create the defined balances
3823 FOR i IN 1..2 LOOP
3824 pay_sdb_ins.ins
3825 (p_defined_balance_id => l_defined_bal_id
3826 ,p_balance_type_id => l_primary_bal_id
3827 ,p_dimension_name => t_dim(i)
3828 ,p_object_version_number => l_ovn
3829 ,p_effective_date => l_effective_date
3830 );
3831 END LOOP;
3832 --
3833 ---------------------------------------------------------------------------------
3834 -- SECTION 4 : Elements
3835 ---------------------------------------------------------------------------------
3836 --====================
3837 -- b) 'Base' element.
3838 --====================
3839 pay_set_ins.ins
3840 (p_element_type_id => l_ot_element_id
3841 ,p_template_id => l_template_id
3842 ,p_element_name => ''
3843 ,p_reporting_name => ''
3844 ,p_relative_processing_priority => 0
3845 ,p_processing_type => 'N'
3846 ,p_classification_name => 'Earnings'
3847 ,p_input_currency_code => 'AED'
3848 ,p_output_currency_code => 'AED'
3849 ,p_multiple_entries_allowed_fla => 'N'
3850 ,p_post_termination_rule => 'F'
3851 ,p_process_in_run_flag => 'Y'
3852 ,p_additional_entry_allowed_fla => 'N'
3853 ,p_adjustment_only_flag => 'N'
3854 ,p_closed_for_entry_flag => 'N'
3855 ,p_indirect_only_flag => 'N'
3856 ,p_multiply_value_flag => 'N'
3857 ,p_standard_link_flag => 'N'
3858 ,p_payroll_formula_id => l_formula_id
3859 ,p_object_version_number => l_ovn
3860 ,p_effective_date => l_effective_date
3861 );
3862 -- Pay value
3863 pay_siv_ins.ins
3864 (p_input_value_id => l_pay_iv
3865 ,p_element_type_id => l_ot_element_id
3866 ,p_display_sequence => 1
3867 ,p_generate_db_items_flag => 'Y'
3868 ,p_hot_default_flag => 'N'
3869 ,p_mandatory_flag => 'X'
3870 ,p_name => 'Pay Value'
3871 ,p_uom => 'M'
3872 ,p_object_version_number => l_ovn
3873 ,p_effective_date => l_effective_date
3874 );
3875
3876 pay_sbf_ins.ins
3877 (p_balance_feed_id => l_bal_feed_id
3878 ,p_balance_type_id => l_primary_bal_id
3879 ,p_input_value_id => l_pay_iv
3880 ,p_scale => 1
3881 ,p_object_version_number => l_ovn
3882 ,p_effective_date => l_effective_date
3883 );
3884
3885 --Hours outside regular hours
3886 pay_siv_ins.ins
3887 (p_input_value_id => l_hrs_regular_iv
3888 ,p_element_type_id => l_ot_element_id
3889 ,p_display_sequence => 2
3890 ,p_generate_db_items_flag => 'Y'
3891 ,p_hot_default_flag => 'N'
3892 ,p_mandatory_flag => 'N'
3893 ,p_name => 'Extra Hours Worked'
3894 ,p_uom => 'H_DECIMAL2'
3895 ,p_object_version_number => l_ovn
3896 ,p_effective_date => l_effective_date
3897 );
3898
3899 --Hours on Rest Days
3900 pay_siv_ins.ins
3901 (p_input_value_id => l_hrs_rest_iv
3902 ,p_element_type_id => l_ot_element_id
3903 ,p_display_sequence => 3
3904 ,p_generate_db_items_flag => 'Y'
3905 ,p_hot_default_flag => 'N'
3906 ,p_mandatory_flag => 'N'
3907 ,p_name => 'Hours Worked on Rest Days'
3908 ,p_uom => 'H_DECIMAL2'
3909 ,p_object_version_number => l_ovn
3910 ,p_effective_date => l_effective_date
3911 );
3912
3913 --Normal Day Rate
3914 pay_siv_ins.ins
3915 (p_input_value_id => l_rate_regular_iv
3916 ,p_element_type_id => l_ot_element_id
3917 ,p_display_sequence => 4
3918 ,p_generate_db_items_flag => 'Y'
3919 ,p_hot_default_flag => 'N'
3920 ,p_mandatory_flag => 'X'
3921 ,p_name => 'Normal Day Rate'
3922 ,p_uom => 'N'
3923 ,p_object_version_number => l_ovn
3924 ,p_effective_date => l_effective_date
3925 );
3926
3927 --Hours on Rest Days
3928 pay_siv_ins.ins
3929 (p_input_value_id => l_rate_rest_iv
3930 ,p_element_type_id => l_ot_element_id
3931 ,p_display_sequence => 5
3932 ,p_generate_db_items_flag => 'Y'
3933 ,p_hot_default_flag => 'N'
3934 ,p_mandatory_flag => 'X'
3935 ,p_name => 'Rest Day Rate'
3936 ,p_uom => 'N'
3937 ,p_object_version_number => l_ovn
3938 ,p_effective_date => l_effective_date
3939 );
3940
3941 -------------------------------------------------------------------------
3942 -- SECTION 6 : Formula rules
3943 -------------------------------------------------------------------------
3944
3945 pay_sfr_ins.ins
3946 (p_formula_result_rule_id => l_rr_id
3947 ,p_shadow_element_type_id => l_ot_element_id
3948 ,p_element_type_id => ''
3949 ,p_input_value_id => l_pay_iv
3950 ,p_result_name => 'L_AMOUNT'
3951 ,p_result_rule_type => 'D'
3952 ,p_object_version_number => l_ovn
3953 ,p_effective_date => l_effective_date
3954 );
3955
3956 pay_sfr_ins.ins
3957 (p_formula_result_rule_id => l_rr_id
3958 ,p_shadow_element_type_id => l_ot_element_id
3959 ,p_element_type_id => ''
3960 ,p_input_value_id => l_rate_regular_iv
3961 ,p_result_name => 'L_REGULAR_OT'
3962 ,p_result_rule_type => 'D'
3963 ,p_object_version_number => l_ovn
3964 ,p_effective_date => l_effective_date
3965 );
3966
3967 pay_sfr_ins.ins
3968 (p_formula_result_rule_id => l_rr_id
3969 ,p_shadow_element_type_id => l_ot_element_id
3970 ,p_element_type_id => ''
3971 ,p_input_value_id => l_rate_rest_iv
3972 ,p_result_name => 'L_REST_DAYS_OT'
3973 ,p_result_rule_type => 'D'
3974 ,p_object_version_number => l_ovn
3975 ,p_effective_date => l_effective_date
3976 );
3977
3978 -- Message (Information)
3979 pay_sfr_ins.ins
3980 (p_formula_result_rule_id => l_rr_id
3981 ,p_shadow_element_type_id => l_ot_element_id
3982 ,p_result_name => 'L_MESG'
3983 ,p_result_rule_type => 'M'
3984 ,p_severity_level => 'I'
3985 ,p_object_version_number => l_ovn
3986 ,p_effective_date => l_effective_date
3987 );
3988
3989 -------------------------------------------------------------------------
3990 -- SECTION 7 : Insert into pay_ele_tmplt_class_usages
3991 -------------------------------------------------------------------------
3992 DECLARE
3993 CURSOR csr_get_class_id IS
3994 SELECT classification_id
3995 FROM pay_element_classifications
3996 WHERE legislation_code = 'AE'
3997 AND classification_name = 'Earnings';
3998 l_classification_id NUMBER;
3999
4000 BEGIN
4001 OPEN csr_get_class_id;
4002 FETCH csr_get_class_id into l_classification_id;
4003 CLOSE csr_get_class_id;
4004
4005 INSERT INTO pay_ele_tmplt_class_usages
4006 (ele_template_classification_id
4007 ,classification_id
4008 ,template_id
4009 ,display_process_mode
4010 ,display_arrearage)
4011 VALUES
4012 (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
4013 ,l_classification_id
4014 ,l_template_id
4015 ,NULL
4016 ,NULL);
4017 END;
4018
4019 END create_ot_allw_template;
4020
4021 ------------------------------------------------------------------------
4022 ------------------------------------------------------------------------
4023 -- Procedure create_unp_leave_dedn_template
4024 -- This procedure is used to create unpaid leave deduction template
4025 ------------------------------------------------------------------------
4026 ------------------------------------------------------------------------
4027 PROCEDURE create_unp_leave_dedn_template IS
4028 --
4029 TYPE Char80_Table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
4030 t_dim Char80_Table;
4031 --
4032 l_template_id number;
4033 l_defined_bal_id number;
4034 l_effective_date date;
4035 l_ovn number;
4036 l_formula_id number;
4037 l_rr_id number;
4038 l_primary_bal_id number;
4039 l_secondary_bal_id number;
4040 l_ul_element_id number;
4041 l_ul_arrears_element_id number;
4042 l_ul_arr_payment_element_id number;
4043 l_pay_iv number;
4044 l_days_iv number;
4045 l_ul_arrear_pay_iv number;
4046 l_arrear_payment_iv number;
4047 l_bal_feed_id number;
4048
4049
4050 --
4051 CURSOR c_template IS
4052 SELECT template_id
4053 FROM pay_element_templates
4054 WHERE template_name = 'Unpaid Leave Template'
4055 AND template_type = 'T';
4056 --
4057 BEGIN
4058 ----------------------------------------------------------------------------
4059 -- Delete the existing template
4060 ----------------------------------------------------------------------------
4061 FOR c_rec in c_template LOOP
4062 l_template_id := c_rec.template_id;
4063
4064 DELETE FROM pay_ele_tmplt_class_usages
4065 WHERE template_id = l_template_id;
4066
4067 pay_element_template_api.delete_user_structure
4068 (p_validate => false
4069 ,p_drop_formula_packages => true
4070 ,p_template_id => l_template_id);
4071 END LOOP;
4072 --
4073 l_effective_date := to_date('0001/01/01', 'YYYY/MM/DD');
4074 ------------------------------------------------------------------------
4075 -- SECTION1 :
4076 ------------------------------------------------------------------------
4077 pay_etm_ins.ins
4078 (p_template_id => l_template_id
4079 ,p_effective_date => l_effective_date
4080 ,p_template_type => 'T'
4081 ,p_template_name => 'Unpaid Leave Template'
4082 ,p_base_processing_priority => 1750
4083 ,p_max_base_name_length => 50
4084 ,p_version_number => 1
4085 ,p_legislation_code => 'AE'
4086 ,p_object_version_number => l_ovn
4087 );
4088 -----------------------------------------------------------------------
4089 -- SECTION2 : Exclusion Rules.
4090 -----------------------------------------------------------------------
4091 --None
4092 ------------------------------------------------------------------------
4093 -- SECTION 3 : Formulas
4094 ------------------------------------------------------------------------
4095 ------------------------
4096 -- a) Formula
4097 ------------------------
4098 pay_sf_ins.ins
4099 (p_formula_id => l_formula_id
4100 ,p_template_type => 'T'
4101 ,p_legislation_code => 'AE'
4102 ,p_formula_name => '_UL_FF'
4103 ,p_description => 'AE Formula for Unpaid Leave'
4104 ,p_formula_text =>
4105 '
4106 /* Description: Formula for unpaid leave deduction template in UAE legislation
4107 */
4108
4109 Default for PAY_PROC_PERIOD_END_DATE_DP is ''4712/12/31 00:00:00'' (DATE)
4110 Default for PAY_PROC_PERIOD_START_DATE_DP is ''0001/01/01 00:00:00'' (DATE)
4111
4112 DEFAULT for SUBJECT_TO_UNPAID_LEAVE_ASG_RUN IS 0
4113
4114 l_amount = 0
4115
4116 l_subjected_earnings = SUBJECT_TO_UNPAID_LEAVE_ASG_RUN
4117
4118 l_unpaid_leave_days = AE_GET_ABSENCE_DAYS(PAY_PROC_PERIOD_START_DATE_DP, PAY_PROC_PERIOD_END_DATE_DP)
4119
4120 l_amount = ((l_subjected_earnings * 12)/365) * l_unpaid_leave_days
4121
4122 IF NET_ASG_RUN < l_amount THEN
4123 (l_arrears = l_amount - NET_ASG_RUN
4124 l_ul_amount = l_amount - l_arrears
4125 l_ul_amount = - l_ul_amount)
4126 ELSE
4127 l_ul_amount = - l_amount
4128
4129 RETURN l_ul_amount, l_unpaid_leave_days, l_arrears
4130
4131
4132 /*================== End Program =================*/'
4133 ,p_object_version_number => l_ovn
4134 ,p_effective_date => l_effective_date
4135 );
4136 --
4137 --
4138 ---------------------------------------------------------------------------------
4139 -- SECTION 3 : Balances and Classification
4140 ---------------------------------------------------------------------------------
4141 t_dim(1) := 'Assignment Inception To Date';
4142 t_dim(2) := 'Assignment Run';
4143 --================
4144 -- Primary Balance
4145 --================
4146 pay_sbt_ins.ins
4147 (p_balance_type_id => l_primary_bal_id
4148 ,p_template_id => l_template_id
4149 ,p_assignment_remuneration_flag => 'N'
4150 ,p_balance_name => ''
4151 ,p_reporting_name => ''
4152 ,p_comments => null
4153 ,p_balance_uom => 'M'
4154 ,p_currency_code => 'AED'
4155 ,p_object_version_number => l_ovn
4156 ,p_effective_date => l_effective_date
4157 );
4158 -- create the defined balances
4159 FOR i IN 1..2 LOOP
4160 pay_sdb_ins.ins
4161 (p_defined_balance_id => l_defined_bal_id
4162 ,p_balance_type_id => l_primary_bal_id
4163 ,p_dimension_name => t_dim(i)
4164 ,p_object_version_number => l_ovn
4165 ,p_effective_date => l_effective_date
4166 );
4167 END LOOP;
4168 --
4169 --================
4170 -- Secondary Balance
4171 --================
4172 pay_sbt_ins.ins
4173 (p_balance_type_id => l_secondary_bal_id
4174 ,p_template_id => l_template_id
4175 ,p_assignment_remuneration_flag => 'N'
4176 ,p_balance_name => ' Arrears'
4177 ,p_reporting_name => ' Arrears'
4178 ,p_comments => null
4179 ,p_balance_uom => 'M'
4180 ,p_currency_code => 'AED'
4181 ,p_object_version_number => l_ovn
4182 ,p_effective_date => l_effective_date
4183 );
4184 -- create the defined balances
4185 FOR i IN 1..2 LOOP
4186 pay_sdb_ins.ins
4187 (p_defined_balance_id => l_defined_bal_id
4188 ,p_balance_type_id => l_secondary_bal_id
4189 ,p_dimension_name => t_dim(i)
4190 ,p_object_version_number => l_ovn
4191 ,p_effective_date => l_effective_date
4192 );
4193 END LOOP;
4194 ---------------------------------------------------------------------------------
4195 -- SECTION 4 : Elements
4196 ---------------------------------------------------------------------------------
4197 --====================
4198 -- b) 'Base' element.
4199 --====================
4200 pay_set_ins.ins
4201 (p_element_type_id => l_ul_element_id
4202 ,p_template_id => l_template_id
4203 ,p_element_name => ''
4204 ,p_reporting_name => ''
4205 ,p_relative_processing_priority => 1350 --(this element should process after earnings classification )
4206 ,p_processing_type => 'N'
4207 ,p_classification_name => 'Absence'
4208 ,p_input_currency_code => 'AED'
4209 ,p_output_currency_code => 'AED'
4210 ,p_multiple_entries_allowed_fla => 'N'
4211 ,p_post_termination_rule => 'F'
4212 ,p_process_in_run_flag => 'Y'
4213 ,p_additional_entry_allowed_fla => 'N'
4214 ,p_adjustment_only_flag => 'N'
4215 ,p_closed_for_entry_flag => 'N'
4216 ,p_indirect_only_flag => 'N'
4217 ,p_multiply_value_flag => 'N'
4218 ,p_standard_link_flag => 'N'
4219 ,p_payroll_formula_id => l_formula_id
4220 ,p_object_version_number => l_ovn
4221 ,p_effective_date => l_effective_date
4222 );
4223 -- Pay value
4224 pay_siv_ins.ins
4225 (p_input_value_id => l_pay_iv
4226 ,p_element_type_id => l_ul_element_id
4227 ,p_display_sequence => 1
4228 ,p_generate_db_items_flag => 'Y'
4229 ,p_hot_default_flag => 'N'
4230 ,p_mandatory_flag => 'X'
4231 ,p_name => 'Pay Value'
4232 ,p_uom => 'M'
4233 ,p_object_version_number => l_ovn
4234 ,p_effective_date => l_effective_date
4235 );
4236
4237 pay_sbf_ins.ins
4238 (p_balance_feed_id => l_bal_feed_id
4239 ,p_balance_type_id => l_primary_bal_id
4240 ,p_input_value_id => l_pay_iv
4241 ,p_scale => 1
4242 ,p_object_version_number => l_ovn
4243 ,p_effective_date => l_effective_date
4244 );
4245
4246 --Leave Days
4247 pay_siv_ins.ins
4248 (p_input_value_id => l_days_iv
4249 ,p_element_type_id => l_ul_element_id
4250 ,p_display_sequence => 2
4251 ,p_generate_db_items_flag => 'Y'
4252 ,p_hot_default_flag => 'N'
4253 ,p_mandatory_flag => 'X'
4254 ,p_name => 'Number of Unpaid Leaves'
4255 ,p_uom => 'ND'
4256 ,p_object_version_number => l_ovn
4257 ,p_effective_date => l_effective_date
4258 );
4259
4260 --Indirect element for Arrears
4261 pay_set_ins.ins
4262 (p_element_type_id => l_ul_arrears_element_id
4263 ,p_template_id => l_template_id
4264 ,p_element_name => ' Arrears'
4265 ,p_relative_processing_priority => 1400
4266 ,p_processing_type => 'N'
4267 ,p_classification_name => 'Information'
4268 ,p_input_currency_code => 'AED'
4269 ,p_output_currency_code => 'AED'
4270 ,p_multiple_entries_allowed_fla => 'N'
4271 ,p_post_termination_rule => 'L'
4272 ,p_process_in_run_flag => 'Y'
4273 ,p_additional_entry_allowed_fla => 'N'
4274 ,p_adjustment_only_flag => 'N'
4275 ,p_closed_for_entry_flag => 'N'
4276 ,p_indirect_only_flag => 'Y'
4277 ,p_multiply_value_flag => 'N'
4278 ,p_standard_link_flag => 'N'
4279 --,p_payroll_formula_id => l_formula_id
4280 --,p_skip_formula => ''
4281 ,p_object_version_number => l_ovn
4282 ,p_effective_date => l_effective_date
4283 );
4284 --
4285 pay_siv_ins.ins
4286 (p_input_value_id => l_ul_arrear_pay_iv
4287 ,p_element_type_id => l_ul_arrears_element_id
4288 ,p_display_sequence => 1
4289 ,p_generate_db_items_flag => 'Y'
4290 ,p_hot_default_flag => 'N'
4291 ,p_mandatory_flag => 'X'
4292 ,p_name => 'Pay Value'
4293 ,p_uom => 'M'
4294 ,p_object_version_number => l_ovn
4295 ,p_effective_date => l_effective_date
4296 );
4297
4298 pay_sbf_ins.ins
4299 (p_balance_feed_id => l_bal_feed_id
4300 ,p_balance_type_id => l_secondary_bal_id
4301 ,p_input_value_id => l_ul_arrear_pay_iv
4302 ,p_scale => 1
4303 ,p_object_version_number => l_ovn
4304 ,p_effective_date => l_effective_date
4305 );
4306
4307 --Element for Arrears Payment
4308 pay_set_ins.ins
4309 (p_element_type_id => l_ul_arr_payment_element_id
4310 ,p_template_id => l_template_id
4311 ,p_element_name => ' Arrears Payment'
4312 ,p_relative_processing_priority => 1450
4313 ,p_processing_type => 'N'
4314 ,p_classification_name => 'Absence'
4315 ,p_input_currency_code => 'AED'
4316 ,p_output_currency_code => 'AED'
4317 ,p_multiple_entries_allowed_fla => 'N'
4318 ,p_post_termination_rule => 'L'
4319 ,p_process_in_run_flag => 'Y'
4320 ,p_additional_entry_allowed_fla => 'N'
4321 ,p_adjustment_only_flag => 'N'
4322 ,p_closed_for_entry_flag => 'N'
4323 ,p_indirect_only_flag => 'N'
4324 ,p_multiply_value_flag => 'N'
4325 ,p_standard_link_flag => 'N'
4326 --,p_payroll_formula_id => l_formula_id
4327 --,p_skip_formula => ''
4328 ,p_object_version_number => l_ovn
4329 ,p_effective_date => l_effective_date
4330 );
4331 --
4332 pay_siv_ins.ins
4333 (p_input_value_id => l_arrear_payment_iv
4334 ,p_element_type_id => l_ul_arr_payment_element_id
4335 ,p_display_sequence => 1
4336 ,p_generate_db_items_flag => 'Y'
4337 ,p_hot_default_flag => 'N'
4338 ,p_mandatory_flag => 'Y'
4339 ,p_name => 'Pay Value'
4340 ,p_uom => 'M'
4341 -- ,p_max_value => 100
4342 ---,p_min_value => 10
4343 -- ,p_warning_or_error => 'W'
4344 ,p_object_version_number => l_ovn
4345 ,p_effective_date => l_effective_date
4346 );
4347
4348 pay_sbf_ins.ins
4349 (p_balance_feed_id => l_bal_feed_id
4350 ,p_balance_type_id => l_secondary_bal_id
4351 ,p_input_value_id => l_arrear_payment_iv
4352 ,p_scale => 1
4353 ,p_object_version_number => l_ovn
4354 ,p_effective_date => l_effective_date
4355 );
4356
4357 pay_sbf_ins.ins
4358 (p_balance_feed_id => l_bal_feed_id
4359 ,p_balance_type_id => l_primary_bal_id
4360 ,p_input_value_id => l_arrear_payment_iv
4361 ,p_scale => 1
4362 ,p_object_version_number => l_ovn
4363 ,p_effective_date => l_effective_date
4364 );
4365
4366 -------------------------------------------------------------------------
4367 -- SECTION 6 : Formula rules
4368 -------------------------------------------------------------------------
4369
4370 pay_sfr_ins.ins
4371 (p_formula_result_rule_id => l_rr_id
4372 ,p_shadow_element_type_id => l_ul_element_id
4373 ,p_element_type_id => ''
4374 ,p_input_value_id => l_pay_iv
4375 ,p_result_name => 'L_UL_AMOUNT'
4376 ,p_result_rule_type => 'D'
4377 ,p_object_version_number => l_ovn
4378 ,p_effective_date => l_effective_date
4379 );
4380
4381 pay_sfr_ins.ins
4382 (p_formula_result_rule_id => l_rr_id
4383 ,p_shadow_element_type_id => l_ul_element_id
4384 ,p_element_type_id => ''
4385 ,p_input_value_id => l_days_iv
4386 ,p_result_name => 'L_UNPAID_LEAVE_DAYS'
4387 ,p_result_rule_type => 'D'
4388 ,p_object_version_number => l_ovn
4389 ,p_effective_date => l_effective_date
4390 );
4391
4392 pay_sfr_ins.ins
4393 (p_formula_result_rule_id => l_rr_id
4394 ,p_shadow_element_type_id => l_ul_element_id
4395 ,p_element_type_id => l_ul_arrears_element_id
4396 ,p_input_value_id => l_ul_arrear_pay_iv
4397 ,p_result_name => 'L_ARREARS'
4398 ,p_result_rule_type => 'I'
4399 ,p_object_version_number => l_ovn
4400 ,p_effective_date => l_effective_date
4401 );
4402
4403 -------------------------------------------------------------------------
4404 -- SECTION 7 : Insert into pay_ele_tmplt_class_usages
4405 -------------------------------------------------------------------------
4406 DECLARE
4407 CURSOR csr_get_class_id IS
4408 SELECT classification_id
4409 FROM pay_element_classifications
4410 WHERE legislation_code = 'AE'
4411 AND classification_name = 'Absence';
4412 l_classification_id NUMBER;
4413
4414 BEGIN
4415 OPEN csr_get_class_id;
4416 FETCH csr_get_class_id into l_classification_id;
4417 CLOSE csr_get_class_id;
4418
4419 INSERT INTO pay_ele_tmplt_class_usages
4420 (ele_template_classification_id
4421 ,classification_id
4422 ,template_id
4423 ,display_process_mode
4424 ,display_arrearage)
4425 VALUES
4426 (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
4427 ,l_classification_id
4428 ,l_template_id
4429 ,NULL
4430 ,NULL);
4431 END;
4432
4433 END create_unp_leave_dedn_template;
4434
4435 ------------------------------------------------------------------------
4436 ------------------------------------------------------------------------
4437 -- Procedure create_templates
4438 -- This procedure calls the procedures for creating the templates.
4439 -- The procedure gets called from hrglobal
4440 ------------------------------------------------------------------------
4441 ------------------------------------------------------------------------
4442 PROCEDURE create_templates IS
4443
4444 l_enabled_flag FND_CURRENCIES.ENABLED_FLAG%TYPE;
4445 CURSOR csr_get_currency IS
4446 SELECT enabled_flag
4447 FROM fnd_currencies
4448 WHERE currency_code = 'AED';
4449
4450 BEGIN
4451 OPEN csr_get_currency;
4452 FETCH csr_get_currency INTO l_enabled_flag;
4453 CLOSE csr_get_currency;
4454
4455 /* Enable AED Currency */
4456 UPDATE fnd_currencies
4457 SET enabled_flag = 'Y'
4458 WHERE currency_code = 'AED'
4459 AND enabled_flag <> 'Y';
4460
4461 create_flat_amt_template;
4462
4463 create_perc_template;
4464
4465 create_basic_sal_template;
4466
4467 create_hsg_allw_template;
4468
4469 create_trn_allw_template;
4470
4471 create_col_allw_template;
4472
4473 create_child_allw_template;
4474
4475 create_social_allw_template;
4476
4477 create_shift_allw_template;
4478
4479 create_hrly_basic_sal_template;
4480
4481 create_ot_allw_template;
4482
4483 create_unp_leave_dedn_template;
4484
4485 UPDATE fnd_currencies
4486 SET enabled_flag = l_enabled_flag
4487 WHERE currency_code = 'AED';
4488
4489 END create_templates;
4490
4491 ------------------------------------------------------------------------
4492 ------------------------------------------------------------------------
4493
4494 END pay_ae_element_template_pkg;