[Home] [Help]
PACKAGE BODY: APPS.PAY_CREATE_ELEMNT_TMPLT_RECORD
Source
1 PACKAGE BODY pay_create_elemnt_tmplt_record as
2 /* $Header: paycreatetemplte.pkb 120.7 2005/06/24 13:49 pganguly noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2005 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ******************************************************************
22
23 Name : pay_create_elemnt_tmplt_record
24
25 Description : This procedure is used to create element template
26 records for a specific legislation.
27
28 Change List
29 -----------
30 Date Name Vers Bug No Description
31 ----------- ---------- ------ -------- -----------------------------------
32 24-JUN-2005 pganguly 115.8 Changed the Extra Element info DDF
33 dbi name to DEDUCTION_PROCESSING.
34 22-JUN-2005 pganguly 115.7 Changed the PCT_DEDN formula to use
35 insuff_funds_type from insuff_fund
36 _type. Also changed the deduction
37 formula to use TOTAL_PAYMENTS_ASG
38 _RUN from TOTAL_PAYMENTS_PAYMENT.
39 21-JUN-2005 pganguly 115.6 4428404 While creating Eligible Comp Balance
40 for PCT_EARN elements, removed the
41 Pay Value association.
42 19-JUN-2005 pganguly 115.5 4431196 Special Feature elements are created
43 with 'Information' classification.
44 Also changed the Template Type of
45 Direct Payments, Employer Charges
46 from Deductions to Earnings.
47 16-JUN-2005 pganguly 115.4 4419843 Changed the Hours X Rate, Percent of
48 4428404 Earnings formula to incorporate
49 get_hourly_rate function.
50 15-JUN-2005 pganguly 115.3 4434071 Removed to_char while printing
51 classification name.
52 10-JUN-2005 pganguly 115.2 Changed the Hours X Rate formula to
53 call the hours function.
54 4426654 Added p_currency_code parameter in
55 the earnings template, this is passed
56 while creating elements/balances.
57 19-MAY-2005 pganguly 115.1 Changed the messages names in Hours
58 X rate formula.
59 19-MAY-2005 mmukherj 115.0 Initial Version
60
61 */
62
63 FUNCTION get_classification_id( p_classification_name IN VARCHAR2,
64 p_legislation_code in varchar2 )
65 RETURN NUMBER IS
66
67 CURSOR get_class_id( cp_classification_name VARCHAR2 ) IS
68 SELECT classification_id
69 FROM pay_element_classifications
70 WHERE legislation_code = p_legislation_code
71 AND classification_name = cp_classification_name;
72
73 l_classification_id NUMBER;
74
75 BEGIN -- get_classification_id
76
77 OPEN get_class_id( p_classification_name );
78 FETCH get_class_id INTO l_classification_id;
79 CLOSE get_class_id;
80
81 RETURN l_classification_id;
82
83 END get_classification_id;
84
85 procedure create_elemnt_tmplt_usages(p_template_id in NUMBER,
86 p_classification_type in VARCHAR2,
87 p_legislation_code in varchar2) is
88
89 BEGIN
90
91 DECLARE
92
93 TYPE char_tabtype IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
94
95 l_classification_name char_tabtype;
96 l_display_proc_mode char_tabtype;
97 l_display_arrearage char_tabtype;
98
99 l_classification_id NUMBER;
100 ln_exists NUMBER;
101 ln_ele_tmplt_class_id NUMBER;
102
103 BEGIN
104
105 IF p_classification_type = 'Earnings' THEN
106
107 l_classification_name(1) := 'Earnings';
108 l_display_proc_mode(1) := 'Y';
109 l_display_arrearage(1) := NULL;
110
111 l_classification_name(2) := 'Supplemental Earnings';
112 l_display_proc_mode(2) := 'Y';
113 l_display_arrearage(2) := NULL;
114
115 l_classification_name(3) := 'Taxable Benefits';
116 l_display_proc_mode(3) := 'Y';
117 l_display_arrearage(3) := NULL;
118
119 l_classification_name(4) := 'Absence';
120 l_display_proc_mode(4) := 'Y';
121 l_display_arrearage(4) := NULL;
122
123 l_classification_name(5) := 'Direct Payment';
124 l_display_proc_mode(5) := NULL;
125 l_display_arrearage(5) := 'Y';
126
127 l_classification_name(6) := 'Employer Charges';
128 l_display_proc_mode(6) := NULL;
129 l_display_arrearage(6) := 'Y';
130
131 ELSIF p_classification_type = 'Deductions' THEN
132
133 l_classification_name(1) := 'Voluntary Deductions';
134 l_display_proc_mode(1) := NULL;
135 l_display_arrearage(1) := 'Y';
136
137 l_classification_name(2) := 'Pre-Tax Deductions';
138 l_display_proc_mode(2) := NULL;
139 l_display_arrearage(2) := 'Y';
140
141 l_classification_name(3) := 'Involuntary Deductions';
142 l_display_proc_mode(3) := NULL;
143 l_display_arrearage(3) := 'Y';
144
145 l_classification_name(4) := 'Tax Deductions';
146 l_display_proc_mode(4) := NULL;
147 l_display_arrearage(4) := 'Y';
148
149 END IF;
150
151 FOR i IN l_classification_name.FIRST .. l_classification_name.LAST LOOP
152
153 hr_utility.trace('l_classification_name = ' || l_classification_name(i));
154 l_classification_id := get_classification_id(l_classification_name(i),p_legislation_code);
155
156 SELECT COUNT(*)
157 INTO ln_exists
158 FROM pay_ele_tmplt_class_usages
159 WHERE classification_id = l_classification_id
160 AND template_id = p_template_id;
161
162 hr_utility.trace('ln_exists = ' || to_char(ln_exists));
163
164 IF ln_exists = 0 THEN
165
166 SELECT pay_ele_tmplt_class_usg_s.nextval
167 INTO ln_ele_tmplt_class_id
168 FROM dual;
169
170 hr_utility.trace('ln_ele_tmplt_class_id ' ||ln_ele_tmplt_class_id);
171
172 INSERT INTO pay_ele_tmplt_class_usages
173 ( ele_template_classification_id
174 ,classification_id
175 ,template_id
176 ,display_process_mode
177 ,display_arrearage )
178 VALUES ( ln_ele_tmplt_class_id
179 ,l_classification_id
180 ,p_template_id
181 ,l_display_proc_mode(i)
182 ,l_display_arrearage(i));
183
184 END IF;
185
186 END LOOP;
187
188 END;
189
190 END create_elemnt_tmplt_usages;
191
192 procedure create_dedn_flat_amt_templ( p_legislation_code in varchar2,
193 p_currency_code in varchar2) IS
194 begin
195
196 declare
197 l_effective_date date;
198 --
199 l_template_exists Char;
200 --
201 l_template_id number;
202 l_object_version_number number;
203 --
204 l_special_inputs_element_id number;
205 l_base_element_id number;
206 l_special_features_element_id number;
207 --
208 l_formula_id number;
209 l_formula_text varchar2(32000);
210 l_formula_name varchar2(80);
211 l_formula_desc varchar2(240);
212 --
213 l_primary_bal_typ_id number;
214 l_accrued_bal_typ_id number;
215 l_not_taken_bal_typ_id number;
216 l_arrears_bal_typ_id number;
217 --
218 l_input_value_id number;
219 l_base_pay_value_id number;
220 l_base_amount_id number;
221 l_clear_arr_iv_id number;
222 l_total_owed_iv_id number;
223 l_sf_pay_value_id number;
224 l_sf_accrued_value_id number;
225 l_sf_not_taken_value_id number;
226 l_sf_arrears_contr_value_id number;
227 --
228 l_defined_balance_id number;
229 --
230 l_balance_feed_id number;
231 --
232 l_reg_tax_proc_type number;
233 l_non_per_tax_proc_type number;
234 l_arrearage_rule_id number;
235 l_stop_rule_id number;
236 l_start_rule_id number;
237 --
238 l_id number;
239 l_element_type_usage_id number;
240 l_balance_attribute_id number;
241
242 duplicate_template exception;
243 l_enabled_flag varchar2(4);
244
245
246 begin
247
248 --hr_utility.trace_on (null,'FLATAMTDEDN');
249
250
251 /* Check for Template Existence */
252
253 BEGIN
254 SELECT 'Y', Template_id
255 INTO l_template_exists, l_template_id
256 FROM pay_element_templates
257 WHERE Template_type = 'T'
258 AND Legislation_code = p_legislation_code
259 AND template_name = 'Flat Amount Deduction';
260 EXCEPTION
261 WHEN NO_DATA_FOUND THEN
262 l_template_exists := 'N';
263 END;
264
265 IF (l_template_exists = 'Y')
266 THEN
267 BEGIN
268 delete from PAY_ELE_TMPLT_CLASS_USAGES
269 where template_id = l_template_id;
270
271 pay_element_template_api.delete_user_structure(false,true,
272 l_template_id);
273 l_template_exists := 'N';
274 EXCEPTION
275 WHEN OTHERS THEN
276 l_template_exists := 'N';
277 NULL;
278 END;
279 END IF;
280
281 /* End of Check */
282
283 IF l_template_exists = 'N'
284 THEN
285
286 l_effective_date := to_date('1901/01/01', 'YYYY/MM/DD');
287
288 --
289 -- PAY_ELEMENT_TEMPLATES row.
290 --
291 pay_etm_ins.ins
292 (p_template_id => l_template_id
293 ,p_effective_date => l_effective_date
294 ,p_template_type => 'T'
295 ,p_template_name => 'Flat Amount Deduction'
296 ,p_base_processing_priority => 3750
297 ,p_max_base_name_length => 40
298 ,p_version_number => 1
299 ,p_legislation_code => p_legislation_code
300 ,p_object_version_number => l_object_version_number
301 );
302
303
304 --
305 -- Formula.
306 --
307
308 l_formula_name := '_FLAT_AMOUNT_DEDN';
309 l_formula_desc := 'Flat Amount formula for Deduction Template';
310
311 l_formula_text :=
312 '/*****************************************************************************
313
314 FORMULA NAME: _FLAT_AMOUNT_DEDN
315
316 FORMULA TYPE: Payroll
317
318 DESCRIPTION: Formula for Flat Amount for Deduction Template.
319 Returns pay value (Amount);
320
321 *******************************************************************************
322
323 FORMULA TEXT
324
325 Formula Results :
326
327 dedn_amt Direct Result for Deduction Amount
328 not_taken Update Deduction Recurring Entry Not Taken
329 to_arrears Update Deduction Recurring Entry Arrears Contr
330 set_clear Update Deduction Recurring Entry Clear Arrears
331 STOP_ENTRY Stop current recurring entry
332 to_total_owed Update Deduction Recurring Entry Accrued
333 mesg Message (Warning)
334
335 *******************************************************************************/
336
337
338 /* Database Item Defaults */
339
340 default for INSUFFICIENT_FUNDS_TYPE is ''NOT ENTERED''
341
342 /* ===== Database Item Defaults End ===== */
343
344 /* ===== Input Value Defaults Begin ===== */
345
346 default for Total_Owed is 0
347 default for Clear_Arrears (text) is ''N''
348 default for Amount is 0
349 default for EXTRA_ELEMENT_INFO_DDF_DEDUCTION_PROCESSING_INSUFFICIENT_FUNDS_TYPE is ''NOT ENTERED''
350
351 /* ===== Input Value Defaults End ===== */
352
353 DEFAULT FOR mesg is ''NOT ENTERED''
354
355
356 /* ===== Inputs Section Begin ===== */
357
358 INPUTS ARE
359 Amount
360 ,Total_Owed
361 ,Clear_Arrears (text)
362
363 /* ===== Inputs Section End ===== */
364
365 dedn_amt = Amount
366 to_total_owed = 0
367 to_arrears = 0
368 to_not_taken = 0
369 total_dedn = 0
370 insuff_funds_type = EXTRA_ELEMENT_INFO_DDF_DEDUCTION_PROCESSING_INSUFFICIENT_FUNDS_TYPE
371 net_amount = TOTAL_PAYMENTS_ASG_RUN
372
373 /* ==== Entry ITD Check Begin ==== */
374
375 IF ( <BASE NAME>_ACCRUED_ENTRY_ITD = 0 AND
376 <BASE NAME>_ACCRUED_ASG_ITD <> 0 ) THEN
377 (
378 to_total_owed = -1 * <BASE NAME>_ACCRUED_ASG_ITD + dedn_amt
379 )
380
381 IF ( <BASE NAME>_ARREARS_ENTRY_ITD = 0 AND
382 <BASE NAME>_ARREARS_ASG_ITD <> 0 ) THEN
383 (
384 to_arrears = -1 * <BASE NAME>_ARREARS_ASG_ITD
385 )
386
387 /* ==== Entry ITD Check End ==== */
388
389 /* ===== Arrears Section Begin ===== */
390
391 IF Clear_Arrears = ''Y'' THEN
392 (
393 to_arrears = -1 * <BASE NAME>_ARREARS_ASG_ITD
394 set_clear = ''No''
395 )
396
397 IF insuff_funds_type = ''PD'' THEN /* Partial Deduction */
398 (
399 IF ( net_amount - dedn_amt >= 0 ) THEN
400 (
401 to_arrears = 0
402 to_not_taken = 0
403 dedn_amt = dedn_amt
404 )
405 ELSE
406 (
407 to_arrears = 0
408 to_not_taken = dedn_amt - net_amount
409 dedn_amt = net_amount
410 )
411 )
412 ELSE IF insuff_funds_type = ''APD'' THEN /*Arrearage and Partial Deduction */
413 (
414 IF ( net_amount <= 0 ) THEN
415 (
416 to_arrears = dedn_amt
417 to_not_taken = dedn_amt
418 dedn_amt = 0
419 )
420 ELSE
421 (
422 total_dedn = dedn_amt + <BASE NAME>_ARREARS_ASG_ITD
423 IF ( net_amount >= total_dedn ) THEN
424 (
425 to_arrears = -1 * <BASE NAME>_ARREARS_ASG_ITD
426 to_not_taken = 0
427 dedn_amt = total_dedn
428 )
429 ELSE
430 (
431 to_arrears = total_dedn - net_amount
432 to_arrears = to_arrears - <BASE NAME>_ARREARS_ASG_ITD
433 IF ( net_amount >= dedn_amt ) THEN
434 (
435 to_not_taken = 0
436 dedn_amt = net_amount
437 )
438 ELSE
439 (
440 to_not_taken = to_arrears
441 dedn_amt = net_amount
442 )
443 )
444 )
445 )
446
447 ELSE IF insuff_funds_type = ''A'' THEN /* Arrearage */
448 (
452 to_not_taken = dedn_amt
449 IF ( net_amount <= 0 ) THEN
450 (
451 to_arrears = dedn_amt
453 dedn_amt = 0
454 )
455 ELSE
456 (
457 total_dedn = dedn_amt + <BASE NAME>_ARREARS_ASG_ITD
458 IF ( net_amount >= total_dedn ) THEN
459 (
460 to_arrears = -1 * <BASE NAME>_ARREARS_ASG_ITD
461 to_not_taken = 0
462 dedn_amt = total_dedn
463 )
464 ELSE
465 (
466 IF ( net_amount >= dedn_amt ) THEN
467 (
468 to_arrears = 0
469 to_not_taken = 0
470 dedn_amt = dedn_amt
471 )
472 ELSE
473 (
474 to_arrears = dedn_amt
475 to_not_taken = dedn_amt
476 dedn_amt = 0
477 )
478 )
479 )
480 )
481 ELSE IF insuff_funds_type = ''NONE'' THEN /* No Arrearage and No Partial Deduction */
482 (
483 IF ( net_amount - dedn_amt >= 0 ) THEN
484 (
485 to_arrears = 0
486 to_not_taken = 0
487 dedn_amt = dedn_amt
488 )
489 ELSE
490 (
491 to_arrears = 0
492 to_not_taken = 0
493 dedn_amt = 0
494 )
495 )
496 ELSE /*Error */
497 (
498 IF ( net_amount - dedn_amt < 0 ) THEN
499 (
500 mesg = GET_MESG(''PAY'',''PAY_INSUFF_FUNDS_FOR_DED'')
501 RETURN mesg
502 )
503 )
504
505
506 /* ===== Arrears Section End ===== */
507
508 /* ===== Stop Rule Section Begin ===== */
509
510 to_total_owed = dedn_amt
511
512 IF Total_Owed WAS NOT DEFAULTED THEN
513 (
514 total_accrued = dedn_amt + <BASE NAME>_ACCRUED_ASG_ITD
515
516 IF total_accrued >= Total_Owed THEN
517 (
518 dedn_amt = Total_Owed - <BASE NAME>_ACCRUED_ASG_ITD
519
520 /* The total has been reached - the return will stop the entry under
521 these conditions. Also, zero out Accrued balance. */
522
523 to_total_owed = -1 * <BASE NAME>_ACCRUED_ASG_ITD
524 STOP_ENTRY = ''Y''
525
526 mesg = GET_MESG(''PAY'',''PAY_STOPPED_ENTRY'',
527 ''BASE_NAME'',''<BASE NAME>'')
528 )
529 )
530
531 /* ===== Stop Rule Section End ===== */
532
533 RETURN dedn_amt,
534 to_not_taken,
535 to_arrears,
536 to_total_owed,
537 STOP_ENTRY,
538 set_clear,
539 mesg
540
541 /* End Formula Text */';
542
543 pay_sf_ins.ins
544 (p_formula_id => l_formula_id
545 ,p_template_type => 'T'
546 ,p_legislation_code => p_legislation_code
547 ,p_formula_name => l_formula_name
548 ,p_description => l_formula_desc
549 ,p_formula_text => l_formula_text
550 ,p_object_version_number => l_object_version_number
551 ,p_effective_date => l_effective_date
552 );
553
554 --
555 -- End Formula
556 --
557
558 --
559 -- 'Base' element.
560 --
561
562 /* Classification Name would be Voluntary Deductions since we donot
563 have Deduction Classification. */
564
565 pay_set_ins.ins
566 (p_element_type_id => l_base_element_id
567 ,p_template_id => l_template_id
568 ,p_element_name => null
569 ,p_reporting_name => null
570 ,p_relative_processing_priority => 0
571 ,p_processing_type => 'N'
572 ,p_classification_name => 'Voluntary Deductions'
573 ,p_input_currency_code => p_currency_code
574 ,p_output_currency_code => p_currency_code
575 ,p_multiple_entries_allowed_fla => 'N'
576 ,p_post_termination_rule => 'F'
577 ,p_process_in_run_flag => 'Y'
578 ,p_additional_entry_allowed_fla => 'N'
579 ,p_adjustment_only_flag => 'N'
580 ,p_closed_for_entry_flag => 'N'
581 ,p_indirect_only_flag => 'N'
582 ,p_multiply_value_flag => 'N'
583 ,p_standard_link_flag => 'N'
584 ,p_process_mode => NULL
585 ,p_payroll_formula_id => l_formula_id
586 ,p_skip_formula => NULL
587 ,p_object_version_number => l_object_version_number
588 ,p_effective_date => l_effective_date
589 ,p_exclusion_rule_id => NULL
590 );
591
592 hr_utility.trace('Base Element Created');
593
594 --
595 -- 'Special Features' element.
596 --
597
598 pay_set_ins.ins
599 (p_element_type_id => l_special_features_element_id
600 ,p_template_id => l_template_id
601 ,p_element_name => ' Special Features'
602 ,p_reporting_name => ' SF'
603 ,p_relative_processing_priority => 50
604 ,p_processing_type => 'N'
605 ,p_classification_name => 'Information'
606 ,p_input_currency_code => p_currency_code
607 ,p_output_currency_code => p_currency_code
608 ,p_multiple_entries_allowed_fla => 'N'
609 ,p_post_termination_rule => 'F'
610 ,p_process_in_run_flag => 'Y'
614 ,p_indirect_only_flag => 'N'
611 ,p_additional_entry_allowed_fla => 'N'
612 ,p_adjustment_only_flag => 'N'
613 ,p_closed_for_entry_flag => 'N'
615 ,p_multiply_value_flag => 'N'
616 ,p_standard_link_flag => 'N'
617 ,p_object_version_number => l_object_version_number
618 ,p_effective_date => l_effective_date
619 );
620
621 hr_utility.trace('Special Features Element Created');
622
623 --
624 -- Input Values for 'Base' element.
625 --
626
627 pay_siv_ins.ins
628 (p_input_value_id => l_base_pay_value_id
629 ,p_element_type_id => l_base_element_id
630 ,p_display_sequence => 1
631 ,p_generate_db_items_flag => 'Y'
632 ,p_hot_default_flag => 'N'
633 ,p_mandatory_flag => 'X'
634 ,p_name => 'Pay Value'
635 ,p_uom => 'M'
636 ,p_exclusion_rule_id => NULL
637 ,p_object_version_number => l_object_version_number
638 ,p_effective_date => l_effective_date
639 );
640
641 hr_utility.trace('Base Element Input Value - Pay Value Created');
642
643 pay_siv_ins.ins
644 (p_input_value_id => l_base_amount_id
645 ,p_element_type_id => l_base_element_id
646 ,p_display_sequence => 2
647 ,p_generate_db_items_flag => 'Y'
648 ,p_hot_default_flag => 'N'
649 ,p_mandatory_flag => 'N'
650 ,p_name => 'Amount'
651 ,p_uom => 'M'
652 ,p_exclusion_rule_id => NULL
653 ,p_object_version_number => l_object_version_number
654 ,p_effective_date => l_effective_date
655 );
656
657 hr_utility.trace('Base Element Input Value - Amount Created');
658
659 pay_siv_ins.ins
660 (p_input_value_id => l_clear_arr_iv_id
661 ,p_element_type_id => l_base_element_id
662 ,p_display_sequence => 3
663 ,p_generate_db_items_flag => 'Y'
664 ,p_hot_default_flag => 'N'
665 ,p_mandatory_flag => 'N' /* user-enterable. */
666 ,p_name => 'Clear Arrears'
667 ,p_uom => 'C'
668 ,p_lookup_type => 'YES_NO'
669 ,p_default_value => 'N'
670 ,p_object_version_number => l_object_version_number
671 ,p_exclusion_rule_id => l_arrearage_rule_id
672 ,p_effective_date => l_effective_date
673 );
674
675 hr_utility.trace('Base Element Input Value - Clear Arrears Created');
676
677 pay_siv_ins.ins
678 (p_input_value_id => l_total_owed_iv_id
679 ,p_element_type_id => l_base_element_id
680 ,p_display_sequence => 4
681 ,p_generate_db_items_flag => 'Y'
682 ,p_hot_default_flag => 'N'
683 ,p_mandatory_flag => 'N' /* user-enterable. */
684 ,p_name => 'Total Owed'
685 ,p_uom => 'M'
686 ,p_object_version_number => l_object_version_number
687 ,p_exclusion_rule_id => l_stop_rule_id
688 ,p_effective_date => l_effective_date
689 );
690
691 hr_utility.trace('Base Element Input Value - Total Owed Created');
692
693
694 --
695 -- Input Values for 'Special Features' element.
696 --
697
698 pay_siv_ins.ins
699 (p_input_value_id => l_sf_pay_value_id
700 ,p_element_type_id => l_special_features_element_id
701 ,p_display_sequence => 1
702 ,p_generate_db_items_flag => 'Y'
703 ,p_hot_default_flag => 'N'
704 ,p_mandatory_flag => 'X'
705 ,p_name => 'Pay Value'
706 ,p_uom => 'M'
707 ,p_object_version_number => l_object_version_number
708 ,p_effective_date => l_effective_date
709 );
710
711 hr_utility.trace('Pay Value for Special Features Created');
712
713 pay_siv_ins.ins
714 (p_input_value_id => l_sf_accrued_value_id
715 ,p_element_type_id => l_special_features_element_id
716 ,p_display_sequence => 2
717 ,p_generate_db_items_flag => 'Y'
718 ,p_hot_default_flag => 'N'
719 ,p_mandatory_flag => 'X'
720 ,p_name => 'Accrued'
721 ,p_uom => 'M'
722 ,p_object_version_number => l_object_version_number
723 ,p_exclusion_rule_id => l_stop_rule_id
724 ,p_effective_date => l_effective_date
725 );
726
727 hr_utility.trace('Accrued Input Value for Special Features Created');
728
729 pay_siv_ins.ins
730 (p_input_value_id => l_sf_not_taken_value_id
731 ,p_element_type_id => l_special_features_element_id
732 ,p_display_sequence => 3
733 ,p_generate_db_items_flag => 'Y'
734 ,p_hot_default_flag => 'N'
735 ,p_mandatory_flag => 'X'
736 ,p_name => 'Not Taken'
737 ,p_uom => 'M'
741
738 ,p_object_version_number => l_object_version_number
739 ,p_effective_date => l_effective_date
740 );
742 hr_utility.trace('Not Taken Input Value for Special Features Created');
743
744 pay_siv_ins.ins
745 (p_input_value_id => l_sf_arrears_contr_value_id
746 ,p_element_type_id => l_special_features_element_id
747 ,p_display_sequence => 4
748 ,p_generate_db_items_flag => 'Y'
749 ,p_hot_default_flag => 'N'
750 ,p_mandatory_flag => 'X'
751 ,p_name => 'Arrears Contr'
752 ,p_uom => 'M'
753 ,p_object_version_number => l_object_version_number
754 ,p_exclusion_rule_id => l_arrearage_rule_id
755 ,p_effective_date => l_effective_date
756 );
757
758 hr_utility.trace('Arrears Contr Input Value for Spec Features Created');
759
760 --
761 -- Primary balance types.
762 --
763
764 pay_sbt_ins.ins
765 (p_balance_type_id => l_primary_bal_typ_id
766 ,p_template_id => l_template_id
767 ,p_assignment_remuneration_flag => 'N'
768 ,p_balance_name => null
769 ,p_currency_code => p_currency_code
770 ,p_reporting_name => null
771 ,p_comments =>
772 'Primary balance for Flat Amount Deductions.'
773 ,p_balance_uom => 'M'
774 ,p_category_name => NULL --'Deductions'
775 ,p_input_value_id => l_base_pay_value_id
776 ,p_object_version_number => l_object_version_number
777 ,p_effective_date => l_effective_date
778 );
779
780 hr_utility.trace('Primary Balance Type Created.');
781
782 pay_sbt_ins.ins
783 (p_balance_type_id => l_accrued_bal_typ_id
784 ,p_template_id => l_template_id
785 ,p_assignment_remuneration_flag => 'N'
786 ,p_balance_name => ' Accrued'
787 ,p_currency_code => p_currency_code
788 ,p_reporting_name => ' Accrued'
789 ,p_comments =>
790 'Accrued balance for Flat Amount Deductions.'
791 ,p_balance_uom => 'M'
792 ,p_category_name => NULL --'Wages'
793 ,p_base_balance_type_id => l_primary_bal_typ_id
794 ,p_object_version_number => l_object_version_number
795 ,p_effective_date => l_effective_date
796 );
797
798
799 hr_utility.trace('Accrued Balance Type Created.');
800
801 pay_sbt_ins.ins
802 (p_balance_type_id => l_arrears_bal_typ_id
803 ,p_template_id => l_template_id
804 ,p_assignment_remuneration_flag => 'N'
805 ,p_balance_name => ' Arrears'
806 ,p_currency_code => p_currency_code
807 ,p_reporting_name => ' Arrears'
808 ,p_comments =>
809 'Arrears balance for Flat Amount Deductions.'
810 ,p_balance_uom => 'M'
811 ,p_category_name => NULL --'Wages'
812 ,p_base_balance_type_id => l_primary_bal_typ_id
813 ,p_object_version_number => l_object_version_number
814 ,p_effective_date => l_effective_date
815 );
816
817 hr_utility.trace('Arrears Balance Type Created.');
818
819 pay_sbt_ins.ins
820 (p_balance_type_id => l_not_taken_bal_typ_id
821 ,p_template_id => l_template_id
822 ,p_assignment_remuneration_flag => 'N'
823 ,p_balance_name => ' Not Taken'
824 ,p_currency_code => p_currency_code
825 ,p_reporting_name => ' Not Taken'
826 ,p_comments =>
827 'Not Taken balance for Flat Amount Deductions.'
828 ,p_balance_uom => 'M'
829 ,p_category_name => NULL --'Wages'
830 ,p_base_balance_type_id => l_primary_bal_typ_id
831 ,p_object_version_number => l_object_version_number
832 ,p_effective_date => l_effective_date
833 );
834
835 hr_utility.trace('Not Taken Balance Type Created.');
836
837 --
838 -- Balance Feeds.
839 --
840
841 pay_sbf_ins.ins
842 (p_balance_feed_id => l_balance_feed_id
843 ,p_balance_type_id => l_primary_bal_typ_id
844 ,p_input_value_id => l_base_pay_value_id
845 ,p_scale => 1
846 ,p_object_version_number => l_object_version_number
847 ,p_effective_date => l_effective_date
848 );
849
850 hr_utility.trace('Balance Feed - Pay Value to Primary Bal Created.');
851
852 pay_sbf_ins.ins
853 (p_balance_feed_id => l_balance_feed_id
854 ,p_balance_type_id => l_accrued_bal_typ_id
855 ,p_input_value_id => l_sf_accrued_value_id
856 ,p_scale => 1
857 ,p_object_version_number => l_object_version_number
858 ,p_effective_date => l_effective_date
859 );
860
861 hr_utility.trace('Balance Feed - Accrued to Accrued Bal Created.');
862
863 pay_sbf_ins.ins
864 (p_balance_feed_id => l_balance_feed_id
868 ,p_exclusion_rule_id => l_arrearage_rule_id
865 ,p_balance_type_id => l_not_taken_bal_typ_id
866 ,p_input_value_id => l_sf_not_taken_value_id
867 ,p_scale => 1
869 ,p_object_version_number => l_object_version_number
870 ,p_effective_date => l_effective_date
871 );
872
873 hr_utility.trace('Balance Feed - Not Taken to Not Taken Bal Created.');
874
875 pay_sbf_ins.ins
876 (p_balance_feed_id => l_balance_feed_id
877 ,p_balance_type_id => l_arrears_bal_typ_id
878 ,p_input_value_id => l_sf_arrears_contr_value_id
879 ,p_scale => 1
880 ,p_exclusion_rule_id => l_arrearage_rule_id
881 ,p_object_version_number => l_object_version_number
882 ,p_effective_date => l_effective_date
883 );
884
885 hr_utility.trace('Balance Feed - Arrears Contr to Arrears Bal Created.');
886
887 --
888 -- Formula rules.
889 --
890
891 pay_sfr_ins.ins
892 (p_formula_result_rule_id => l_id
893 ,p_shadow_element_type_id => l_base_element_id
894 ,p_result_name => 'dedn_amt'
895 ,p_result_rule_type => 'D'
896 ,p_element_type_id => l_base_element_id
897 ,p_input_value_id => l_base_pay_value_id
898 ,p_object_version_number => l_object_version_number
899 ,p_effective_date => l_effective_date
900 );
901
902 hr_utility.trace('Formula Rule - dedn_amt created.');
903
904 pay_sfr_ins.ins
905 (p_formula_result_rule_id => l_id
906 ,p_shadow_element_type_id => l_base_element_id
907 ,p_result_name => 'STOP_ENTRY'
908 ,p_result_rule_type => 'S'
909 ,p_element_type_id => l_base_element_id
910 ,p_exclusion_rule_id => l_stop_rule_id
911 ,p_object_version_number => l_object_version_number
912 ,p_effective_date => l_effective_date
913 );
914
915 hr_utility.trace('Formula Rule - STOP_ENTRY created.');
916
917 pay_sfr_ins.ins
918 (p_formula_result_rule_id => l_id
919 ,p_shadow_element_type_id => l_base_element_id
920 ,p_result_name => 'set_clear'
921 ,p_result_rule_type => 'U'
922 ,p_element_type_id => l_base_element_id
923 ,p_input_value_id => l_clear_arr_iv_id
924 ,p_exclusion_rule_id => l_arrearage_rule_id
925 ,p_object_version_number => l_object_version_number
926 ,p_effective_date => l_effective_date
927 );
928
929 hr_utility.trace('Formula Rule - set_clear created.');
930
931 pay_sfr_ins.ins
932 (p_formula_result_rule_id => l_id
933 ,p_shadow_element_type_id => l_base_element_id
934 ,p_result_name => 'mesg'
935 ,p_result_rule_type => 'M'
936 ,p_severity_level => 'W'
937 ,p_object_version_number => l_object_version_number
938 ,p_effective_date => l_effective_date
939 );
940
941 hr_utility.trace('Formula Rule - mesg created.');
942
943 pay_sfr_ins.ins
944 (p_formula_result_rule_id => l_id
945 ,p_shadow_element_type_id => l_base_element_id
946 ,p_result_name => 'to_not_taken'
947 ,p_result_rule_type => 'I'
948 ,p_element_type_id => l_special_features_element_id
949 ,p_input_value_id => l_sf_not_taken_value_id
950 ,p_object_version_number => l_object_version_number
951 ,p_effective_date => l_effective_date
952 );
953
954 hr_utility.trace('Formula Rule - to_not_taken created.');
955
956 pay_sfr_ins.ins
957 (p_formula_result_rule_id => l_id
958 ,p_shadow_element_type_id => l_base_element_id
959 ,p_result_name => 'to_total_owed'
960 ,p_result_rule_type => 'I'
961 ,p_element_type_id => l_special_features_element_id
962 ,p_input_value_id => l_sf_accrued_value_id
963 ,p_exclusion_rule_id => l_stop_rule_id
964 ,p_object_version_number => l_object_version_number
965 ,p_effective_date => l_effective_date
966 );
967
968 hr_utility.trace('Formula Rule - to_total_owed created.');
969
970 pay_sfr_ins.ins
971 (p_formula_result_rule_id => l_id
972 ,p_shadow_element_type_id => l_base_element_id
973 ,p_result_name => 'to_arrears'
974 ,p_result_rule_type => 'I'
975 ,p_element_type_id => l_special_features_element_id
976 ,p_input_value_id => l_sf_arrears_contr_value_id
977 ,p_exclusion_rule_id => l_arrearage_rule_id
978 ,p_object_version_number => l_object_version_number
979 ,p_effective_date => l_effective_date
980 );
981
982 hr_utility.trace('Formula Rule - to_arrears created.');
983
984 --
985 -- Defined Balances for Base Balance
986 --
987
988 pay_sdb_ins.ins
989 (p_defined_balance_id => l_defined_balance_id
990 ,p_balance_type_id => l_primary_bal_typ_id
991 ,p_dimension_name => 'Payments'
995
992 ,p_object_version_number => l_object_version_number
993 ,p_effective_date => l_effective_date
994 );
996 pay_sdb_ins.ins
997 (p_defined_balance_id => l_defined_balance_id
998 ,p_balance_type_id => l_primary_bal_typ_id
999 ,p_dimension_name =>
1000 'Assignment Run'
1001 ,p_object_version_number => l_object_version_number
1002 ,p_effective_date => l_effective_date
1003 );
1004
1005 pay_sdb_ins.ins
1006 (p_defined_balance_id => l_defined_balance_id
1007 ,p_balance_type_id => l_primary_bal_typ_id
1008 ,p_dimension_name =>
1009 'Assignment Calendar Year to Date'
1010 ,p_object_version_number => l_object_version_number
1011 ,p_effective_date => l_effective_date
1012 );
1013
1014 pay_sdb_ins.ins
1015 (p_defined_balance_id => l_defined_balance_id
1016 ,p_balance_type_id => l_primary_bal_typ_id
1017 ,p_dimension_name => 'Assignment Calendar Month To Date'
1018 ,p_object_version_number => l_object_version_number
1019 ,p_effective_date => l_effective_date
1020 );
1021
1022 --
1023 -- Defined Balances for Accrued Balance
1024 --
1025
1026 pay_sdb_ins.ins
1027 (p_defined_balance_id => l_defined_balance_id
1028 ,p_balance_type_id => l_accrued_bal_typ_id
1029 ,p_dimension_name =>
1030 'Element Entry Inception to Date'
1031 ,p_object_version_number => l_object_version_number
1032 ,p_effective_date => l_effective_date
1033 );
1034
1035 pay_sdb_ins.ins
1036 (p_defined_balance_id => l_defined_balance_id
1037 ,p_balance_type_id => l_accrued_bal_typ_id
1038 ,p_dimension_name =>
1039 'Assignment Run'
1040 ,p_object_version_number => l_object_version_number
1041 ,p_effective_date => l_effective_date
1042 );
1043
1044 pay_sdb_ins.ins
1045 (p_defined_balance_id => l_defined_balance_id
1046 ,p_balance_type_id => l_accrued_bal_typ_id
1047 ,p_dimension_name =>
1048 'Assignment Inception To Date'
1049 ,p_object_version_number => l_object_version_number
1050 ,p_effective_date => l_effective_date
1051 );
1052
1053 pay_sdb_ins.ins
1054 (p_defined_balance_id => l_defined_balance_id
1055 ,p_balance_type_id => l_accrued_bal_typ_id
1056 ,p_dimension_name => 'Assignment Calendar Month To Date'
1057 ,p_object_version_number => l_object_version_number
1058 ,p_effective_date => l_effective_date
1059 );
1060
1061 --
1062 -- Defined Balances for Arrears Balance
1063 --
1064
1065 pay_sdb_ins.ins
1066 (p_defined_balance_id => l_defined_balance_id
1067 ,p_balance_type_id => l_arrears_bal_typ_id
1068 ,p_dimension_name =>
1069 'Element Entry Inception to Date'
1070 ,p_object_version_number => l_object_version_number
1071 ,p_effective_date => l_effective_date
1072 );
1073
1074 pay_sdb_ins.ins
1075 (p_defined_balance_id => l_defined_balance_id
1076 ,p_balance_type_id => l_arrears_bal_typ_id
1077 ,p_dimension_name =>
1078 'Assignment Run'
1079 ,p_object_version_number => l_object_version_number
1080 ,p_effective_date => l_effective_date
1081 );
1082
1083 pay_sdb_ins.ins
1084 (p_defined_balance_id => l_defined_balance_id
1085 ,p_balance_type_id => l_arrears_bal_typ_id
1086 ,p_dimension_name =>
1087 'Assignment Inception To Date'
1088 ,p_object_version_number => l_object_version_number
1089 ,p_effective_date => l_effective_date
1090 );
1091
1092 pay_sdb_ins.ins
1093 (p_defined_balance_id => l_defined_balance_id
1094 ,p_balance_type_id => l_arrears_bal_typ_id
1095 ,p_dimension_name => 'Assignment Calendar Month To Date'
1096 ,p_object_version_number => l_object_version_number
1097 ,p_effective_date => l_effective_date
1098 );
1099
1100 --
1101 -- Defined Balances for Not Taken Balance
1102 --
1103
1104 pay_sdb_ins.ins
1105 (p_defined_balance_id => l_defined_balance_id
1106 ,p_balance_type_id => l_not_taken_bal_typ_id
1107 ,p_dimension_name =>
1108 'Assignment Inception to Date'
1109 ,p_object_version_number => l_object_version_number
1110 ,p_effective_date => l_effective_date
1111 );
1112
1113 pay_sdb_ins.ins
1114 (p_defined_balance_id => l_defined_balance_id
1115 ,p_balance_type_id => l_not_taken_bal_typ_id
1116 ,p_dimension_name =>
1117 'Assignment Run'
1118 ,p_object_version_number => l_object_version_number
1119 ,p_effective_date => l_effective_date
1120 );
1121
1122 pay_create_elemnt_tmplt_record.create_elemnt_tmplt_usages(
1123 l_template_id,
1127 END IF;
1124 'Deductions',
1125 p_legislation_code);
1126
1128
1129
1130 END;
1131
1132 end create_dedn_flat_amt_templ;
1133
1134 procedure create_earn_flat_amt_templ( p_legislation_code IN VARCHAR2,
1135 p_currency_code IN VARCHAR2) IS
1136 begin
1137 declare
1138 l_effective_date date;
1139 --
1140 l_template_exists Char;
1141 --
1142 l_template_id number;
1143 l_object_version_number number;
1144 --
1145 l_special_inputs_element_id number;
1146 l_base_element_id number;
1147 l_special_features_element_id number;
1148 --
1149 l_formula_id number;
1150 l_formula_text varchar2(32000);
1151 --
1152 l_primary_balance_id number;
1153 l_replacement_balance_id number;
1154 l_additional_balance_id number;
1155 l_neg_earn_balance_id number;
1156 l_el_balance_id number;
1157 --
1158 l_input_value_id number;
1159 l_base_pay_value_id number;
1160 l_base_amount_id number;
1161 l_base_sep_pay_id number;
1162 l_base_proc_sep_id number;
1163 --
1164 l_defined_balance_id number;
1165 --
1166 l_balance_feed_id number;
1167 --
1168 l_reg_tax_proc_type number;
1169 l_non_per_tax_proc_type number;
1170 l_sep_pay_excl_rule_id number;
1171 l_prc_sep_excl_rule_id number;
1172 l_excl_el_no_base_bal number;
1173 l_excl_el_no_el_bal number;
1174 --
1175 l_id number;
1176 l_element_type_usage_id number;
1177 l_balance_attribute_id number;
1178
1179 duplicate_template exception;
1180 l_enabled_flag varchar2(4);
1181
1182
1183 begin
1184
1185 --hr_utility.trace_on (null,'FLATAMT');
1186
1187
1188 /* Check for Template Existence */
1189
1190 l_template_exists := 'N';
1191
1192 BEGIN
1193 SELECT 'Y', Template_id
1194 INTO l_template_exists, l_template_id
1195 FROM pay_element_templates
1196 WHERE Template_type = 'T'
1197 AND Legislation_code = p_legislation_code
1198 AND template_name = 'Flat Amount';
1199 EXCEPTION
1200 WHEN OTHERS THEN
1201 NULL;
1202 END;
1203
1204 IF (l_template_exists = 'Y')
1205 THEN
1206 BEGIN
1207 delete from PAY_ELE_TMPLT_CLASS_USAGES
1208 where template_id = l_template_id;
1209
1210 pay_element_template_api.delete_user_structure(false,true,
1211 l_template_id);
1212 l_template_exists := 'N';
1213 EXCEPTION
1214 WHEN OTHERS THEN
1215 l_template_exists := 'N';
1216 NULL;
1217 END;
1218 END IF;
1219
1220 /* End of Check */
1221
1222 IF l_template_exists = 'N'
1223 THEN
1224
1225 l_effective_date := to_date('1901/01/01', 'YYYY/MM/DD');
1226
1227 --
1228 -- PAY_ELEMENT_TEMPLATES row.
1229 --
1230 pay_etm_ins.ins
1231 (p_template_id => l_template_id
1232 ,p_effective_date => l_effective_date
1233 ,p_template_type => 'T'
1234 ,p_template_name => 'Flat Amount'
1235 ,p_base_processing_priority => 1750
1236 ,p_max_base_name_length => 25
1237 ,p_version_number => 1
1238 ,p_legislation_code => p_legislation_code
1239 ,p_object_version_number => l_object_version_number
1240 );
1241
1242 --
1243 -- Formula.
1244 --
1245
1246 l_formula_text :=
1247 '/*****************************************************************************
1248
1249 FORMULA NAME: _FLAT_AMOUNT_EARN
1250
1251 FORMULA TYPE: Payroll
1252
1253 DESCRIPTION: Formula for Flat Amount for Earning Template for Internation
1254 Payroll.
1255 Returns pay value (Amount);
1256
1257 Formula Results :
1258
1259 flat_amount Direct Result for Earnings Amount.
1260 mesg Warning message will be issued for this assignment.
1261
1262 *******************************************************************************/
1263
1264 /* Database Item Defaults */
1265
1266 DEFAULT FOR flat_amount is 0
1267 DEFAULT FOR mesg is ''NOT ENTERED''
1268
1269 /* Inputs */
1270
1271 INPUTS ARE Amount
1272
1273 flat_amount = Amount
1274
1275 RETURN flat_amount,
1276 mesg
1277
1278 /* End Formula Text */';
1279
1280 pay_sf_ins.ins
1281 (p_formula_id => l_formula_id
1282 ,p_template_type => 'T'
1283 ,p_legislation_code => p_legislation_code
1284 ,p_formula_name => '_FLAT_AMOUNT_EARN'
1285 ,p_description => 'Flat Amount formula for Earning Template'
1286 ,p_formula_text => l_formula_text
1287 ,p_object_version_number => l_object_version_number
1288 ,p_effective_date => l_effective_date
1289 );
1290
1291 --
1292 -- End Formula
1293 --
1294
1295 --
1296 -- 'Base' element.
1297 --
1298
1299 pay_set_ins.ins
1303 ,p_reporting_name => null
1300 (p_element_type_id => l_base_element_id
1301 ,p_template_id => l_template_id
1302 ,p_element_name => null
1304 ,p_relative_processing_priority => 0
1305 ,p_processing_type => 'N'
1306 ,p_classification_name => 'Earnings'
1307 ,p_input_currency_code => p_currency_code
1308 ,p_output_currency_code => p_currency_code
1309 ,p_multiple_entries_allowed_fla => 'Y'
1310 ,p_post_termination_rule => 'F'
1311 ,p_process_in_run_flag => 'Y'
1312 ,p_additional_entry_allowed_fla => 'N'
1313 ,p_adjustment_only_flag => 'N'
1314 ,p_closed_for_entry_flag => 'N'
1315 ,p_indirect_only_flag => 'N'
1316 ,p_multiply_value_flag => 'N'
1317 ,p_standard_link_flag => 'N'
1318 ,p_process_mode => 'S'
1319 ,p_payroll_formula_id => l_formula_id
1320 ,p_skip_formula => NULL
1321 ,p_object_version_number => l_object_version_number
1322 ,p_effective_date => l_effective_date
1323 ,p_exclusion_rule_id => NULL
1324 );
1325
1326 hr_utility.trace('Base Element Created');
1327
1328 --
1329 -- Input Values for 'Base' element.
1330 --
1331
1332 pay_siv_ins.ins
1333 (p_input_value_id => l_base_pay_value_id
1334 ,p_element_type_id => l_base_element_id
1335 ,p_display_sequence => 1
1336 ,p_generate_db_items_flag => 'Y'
1337 ,p_hot_default_flag => 'N'
1338 ,p_mandatory_flag => 'X'
1339 ,p_name => 'Pay Value'
1340 ,p_uom => 'M'
1341 ,p_exclusion_rule_id => NULL
1342 ,p_object_version_number => l_object_version_number
1343 ,p_effective_date => l_effective_date
1344 );
1345
1346 hr_utility.trace('Base Element Input Value - Pay Value Created');
1347
1348 pay_siv_ins.ins
1349 (p_input_value_id => l_base_amount_id
1350 ,p_element_type_id => l_base_element_id
1351 ,p_display_sequence => 2
1352 ,p_generate_db_items_flag => 'Y'
1353 ,p_hot_default_flag => 'N'
1354 ,p_mandatory_flag => 'N'
1355 ,p_name => 'Amount'
1356 ,p_uom => 'M'
1357 ,p_exclusion_rule_id => NULL
1358 ,p_object_version_number => l_object_version_number
1359 ,p_effective_date => l_effective_date
1360 );
1361
1362 hr_utility.trace('Base Element Input Value - Amount Created');
1363
1364
1365 pay_siv_ins.ins
1366 (p_input_value_id => l_input_value_id
1367 ,p_element_type_id => l_base_element_id
1368 ,p_display_sequence => 3
1369 ,p_generate_db_items_flag => 'Y'
1370 ,p_hot_default_flag => 'N'
1371 ,p_mandatory_flag => 'X'
1372 ,p_name => 'Jurisdiction'
1373 ,p_uom => 'C'
1374 ,p_object_version_number => l_object_version_number
1375 ,p_effective_date => l_effective_date
1376 );
1377
1378 hr_utility.trace('Base Element Input Value- Jurisdiction');
1379
1380 --
1381 -- Primary balance types.
1382 --
1383
1384 pay_sbt_ins.ins
1385 (p_balance_type_id => l_primary_balance_id
1386 ,p_template_id => l_template_id
1387 ,p_assignment_remuneration_flag => 'N'
1388 ,p_balance_name => null
1389 ,p_currency_code => p_currency_code
1390 ,p_reporting_name => null
1391 ,p_comments =>
1392 'Primary balance for Flat Amount Earnings.'
1393 ,p_balance_uom => 'M'
1394 ,p_category_name => NULL --'Earnings'
1395 ,p_input_value_id => l_base_pay_value_id
1396 ,p_object_version_number => l_object_version_number
1397 ,p_effective_date => l_effective_date
1398 ,p_exclusion_rule_id => NULL
1399 );
1400
1401 hr_utility.trace('Primary Balance Type Created.');
1402
1403 pay_sbt_ins.ins
1404 (p_balance_type_id => l_el_balance_id
1405 ,p_template_id => l_template_id
1406 ,p_assignment_remuneration_flag => 'N'
1407 ,p_balance_name => ' EL'
1408 ,p_currency_code => p_currency_code
1409 ,p_reporting_name => ' EL'
1410 ,p_comments =>
1411 'Employer Liabilities for Flat Amount Earnings.'
1412 ,p_balance_uom => 'M'
1413 ,p_category_name => NULL --'Employer Liabilities'
1414 ,p_input_value_id => l_base_pay_value_id
1415 ,p_object_version_number => l_object_version_number
1416 ,p_effective_date => l_effective_date
1417 ,p_exclusion_rule_id => NULL
1418 );
1419
1420 --
1421 -- Balance Feeds.
1422 --
1423
1424 pay_sbf_ins.ins
1425 (p_balance_feed_id => l_balance_feed_id
1426 ,p_balance_type_id => l_primary_balance_id
1427 ,p_input_value_id => l_base_pay_value_id
1431 );
1428 ,p_scale => 1
1429 ,p_object_version_number => l_object_version_number
1430 ,p_effective_date => l_effective_date
1432
1433 hr_utility.trace('Balance Feed For Primary Balance - Pay Value Created');
1434
1435 pay_sbf_ins.ins
1436 (p_balance_feed_id => l_balance_feed_id
1437 ,p_balance_type_id => l_el_balance_id
1438 ,p_input_value_id => l_base_pay_value_id
1439 ,p_scale => 1
1440 ,p_object_version_number => l_object_version_number
1441 ,p_effective_date => l_effective_date
1442 );
1443
1444 hr_utility.trace('Balance Feed For EL Balance - Pay Value Created');
1445
1446 --
1447 -- Formula rules.
1448 --
1449
1450 pay_sfr_ins.ins
1451 (p_formula_result_rule_id => l_id
1452 ,p_shadow_element_type_id => l_base_element_id
1453 ,p_result_name => 'flat_amount'
1454 ,p_result_rule_type => 'D'
1455 ,p_element_type_id => l_base_element_id
1456 ,p_input_value_id => l_base_pay_value_id
1457 ,p_object_version_number => l_object_version_number
1458 ,p_effective_date => l_effective_date
1459 );
1460
1461 hr_utility.trace('Formula Rule - flat_amount created.');
1462
1463 pay_sfr_ins.ins
1464 (p_formula_result_rule_id => l_id
1465 ,p_shadow_element_type_id => l_base_element_id
1466 ,p_result_name => 'mesg'
1467 ,p_result_rule_type => 'M'
1468 ,p_severity_level => 'W'
1469 ,p_object_version_number => l_object_version_number
1470 ,p_effective_date => l_effective_date
1471 );
1472
1473 hr_utility.trace('Formula Rule - mesg created.');
1474
1475 --
1476 -- Defined Balances for the Primary Balance
1477 --
1478
1479 pay_sdb_ins.ins
1480 (p_defined_balance_id => l_defined_balance_id
1481 ,p_balance_type_id => l_primary_balance_id
1482 ,p_dimension_name =>
1483 'Person Calendar Year To Date'
1484 ,p_object_version_number => l_object_version_number
1485 ,p_effective_date => l_effective_date
1486 );
1487
1488 pay_sdb_ins.ins
1489 (p_defined_balance_id => l_defined_balance_id
1490 ,p_balance_type_id => l_primary_balance_id
1491 ,p_dimension_name =>
1492 'Person Calendar Quarter To Date'
1493 ,p_object_version_number => l_object_version_number
1494 ,p_effective_date => l_effective_date
1495 );
1496
1497 pay_sdb_ins.ins
1498 (p_defined_balance_id => l_defined_balance_id
1499 ,p_balance_type_id => l_primary_balance_id
1500 ,p_dimension_name =>
1501 'Person Calendar Month To Date'
1502 ,p_object_version_number => l_object_version_number
1503 ,p_effective_date => l_effective_date
1504 );
1505
1506
1507 pay_sdb_ins.ins
1508 (p_defined_balance_id => l_defined_balance_id
1509 ,p_balance_type_id => l_primary_balance_id
1510 ,p_dimension_name =>
1511 'Assignment Calendar Year To Date'
1512 ,p_object_version_number => l_object_version_number
1513 ,p_effective_date => l_effective_date
1514 );
1515
1516 pay_sdb_ins.ins
1517 (p_defined_balance_id => l_defined_balance_id
1518 ,p_balance_type_id => l_primary_balance_id
1519 ,p_dimension_name =>
1520 'Assignment Run'
1521 ,p_object_version_number => l_object_version_number
1522 ,p_effective_date => l_effective_date
1523 );
1524
1525 pay_sdb_ins.ins
1526 (p_defined_balance_id => l_defined_balance_id
1527 ,p_balance_type_id => l_primary_balance_id
1528 ,p_dimension_name => 'Payments'
1529 ,p_object_version_number => l_object_version_number
1530 ,p_effective_date => l_effective_date
1531 );
1532
1533
1534 --
1535 -- Defined Balances For Employer Liabilities
1536 --
1537
1538 pay_sdb_ins.ins
1539 (p_defined_balance_id => l_defined_balance_id
1540 ,p_balance_type_id => l_el_balance_id
1541 ,p_dimension_name =>
1542 'Person Calendar Year To Date'
1543 ,p_object_version_number => l_object_version_number
1544 ,p_effective_date => l_effective_date
1545 );
1546
1547 pay_sdb_ins.ins
1548 (p_defined_balance_id => l_defined_balance_id
1549 ,p_balance_type_id => l_el_balance_id
1550 ,p_dimension_name =>
1551 'Person Calendar Quarter To Date'
1552 ,p_object_version_number => l_object_version_number
1553 ,p_effective_date => l_effective_date
1554 );
1555
1556 pay_sdb_ins.ins
1557 (p_defined_balance_id => l_defined_balance_id
1558 ,p_balance_type_id => l_el_balance_id
1559 ,p_dimension_name =>
1560 'Person Calendar Month To Date'
1561 ,p_object_version_number => l_object_version_number
1565 pay_sdb_ins.ins
1562 ,p_effective_date => l_effective_date
1563 );
1564
1566 (p_defined_balance_id => l_defined_balance_id
1567 ,p_balance_type_id => l_el_balance_id
1568 ,p_dimension_name =>
1569 'Assignment Calendar Year To Date'
1570 ,p_object_version_number => l_object_version_number
1571 ,p_effective_date => l_effective_date
1572 );
1573
1574 pay_sdb_ins.ins
1575 (p_defined_balance_id => l_defined_balance_id
1576 ,p_balance_type_id => l_el_balance_id
1577 ,p_dimension_name =>
1578 'Assignment Run'
1579 ,p_object_version_number => l_object_version_number
1580 ,p_effective_date => l_effective_date
1581 );
1582
1583 pay_sdb_ins.ins
1584 (p_defined_balance_id => l_defined_balance_id
1585 ,p_balance_type_id => l_el_balance_id
1586 ,p_dimension_name => 'Payments'
1587 ,p_object_version_number => l_object_version_number
1588 ,p_effective_date => l_effective_date
1589 );
1590
1591 pay_create_elemnt_tmplt_record.create_elemnt_tmplt_usages(
1592 l_template_id,
1593 'Earnings',
1594 p_legislation_code );
1595
1596 END IF;
1597
1598 end;
1599 END create_earn_flat_amt_templ;
1600
1601 procedure create_earn_hxr_amt_templ( p_legislation_code IN VARCHAR2,
1602 p_currency_code IN VARCHAR2) IS
1603 begin
1604 declare
1605 l_effective_date date;
1606 --
1607 l_template_exists Char;
1608 --
1609 l_template_id number;
1610 l_object_version_number number;
1611 --
1612 l_base_element_id number;
1613 --
1614 l_formula_id number;
1615 l_formula_text varchar2(32000);
1616 l_formula_name varchar2(80);
1617 l_formula_desc varchar2(240);
1618 --
1619 l_primary_bal_typ_id number;
1620 l_hours_bal_typ_id number;
1621 l_el_bal_typ_id number;
1622 --
1623 l_input_value_id number;
1624 l_base_pay_value_iv_id number;
1625 l_base_hours_iv_id number;
1626 l_base_rate_iv_id number;
1627 l_base_multiple_iv_id number;
1628 l_base_sep_pay_iv_id number;
1629 l_base_proc_sep_iv_id number;
1630 --
1631 l_defined_balance_id number;
1632 --
1633 l_balance_feed_id number;
1634 --
1635 l_reg_tax_proc_type number;
1636 l_non_per_tax_proc_type number;
1637 l_sep_pay_excl_rule_id number;
1638 l_prc_sep_excl_rule_id number;
1639 l_dbc1 number;
1640 l_dbc2 number;
1641 l_dbc3 number;
1642 l_excl_el_no_base_bal number;
1643 l_excl_el_no_el_bal number;
1644 --
1645 l_id number;
1646 l_element_type_usage_id number;
1647 l_balance_attribute_id number;
1648
1649 duplicate_template exception;
1650 l_enabled_flag varchar2(4);
1651
1652
1653 begin
1654
1655 --hr_utility.trace_on (null,'HXR');
1656
1657
1658 /* Check for Template Existence */
1659
1660 l_template_exists := 'N';
1661
1662 BEGIN
1663 SELECT 'Y', Template_id
1664 INTO l_template_exists, l_template_id
1665 FROM pay_element_templates
1666 WHERE Template_type = 'T'
1667 AND Legislation_code = p_legislation_code
1668 AND template_name = 'Hours X Rate';
1669 EXCEPTION
1670 WHEN OTHERS THEN
1671 NULL;
1672 END;
1673
1674 IF (l_template_exists = 'Y')
1675 THEN
1676 BEGIN
1677 delete from PAY_ELE_TMPLT_CLASS_USAGES
1678 where template_id = l_template_id;
1679
1680 pay_element_template_api.delete_user_structure(false,true,
1681 l_template_id);
1682 l_template_exists := 'N';
1683 EXCEPTION
1684 WHEN OTHERS THEN
1685 l_template_exists := 'N';
1686 NULL;
1687 END;
1688 END IF;
1689
1690 /* End of Check */
1691
1692 IF l_template_exists = 'N'
1693 THEN
1694
1695 l_effective_date := to_date('1901/01/01', 'YYYY/MM/DD');
1696
1697 --
1698 -- PAY_ELEMENT_TEMPLATES row.
1699 --
1700 pay_etm_ins.ins
1701 (p_template_id => l_template_id
1702 ,p_effective_date => l_effective_date
1703 ,p_template_type => 'T'
1704 ,p_template_name => 'Hours X Rate'
1705 ,p_base_processing_priority => 1750
1706 ,p_max_base_name_length => 25
1707 ,p_version_number => 1
1708 ,p_legislation_code => p_legislation_code
1709 ,p_object_version_number => l_object_version_number
1710 );
1711
1712 --
1713 -- Formula _HOURS_X_RATE
1714 --
1715
1716 l_formula_name := '_HOURS_X_RATE';
1717 l_formula_desc := 'Formula for Hours X Rate Template';
1718
1719 l_formula_text :=
1720 '/*****************************************************************************
1721
1722 FORMULA NAME: HOURS_X_RATE
1726 is determined by one of the following, in order of preference:
1723 FORMULA TYPE: Payroll
1724 DESCRIPTION: Creates formula for template element where Hours must
1725 be input, Multiple defaults to 1 if not input, and Rate
1727 1) Entry of "Rate" input value
1728 2) Entry of "Rate Code" input value
1729 3) Salary Admin "Pay Basis" information
1730 --
1731 INPUTS: Hours
1732 Multiple
1733 Rate
1734 --
1735 Change History
1736 --
1737 **********************************************************************
1738 Formula Results :
1739 earnings_amount
1740 mesg
1741
1742 Followings are Indirect result for Hours by Rate element:
1743
1744 None
1745 **********************************************************************/
1746 /* ===== Alias Section Begin ====== */
1747 /* ===== Alias Section End ====== */
1748
1749 /* ===== Defaults Section Begin ===== */
1750
1751 DEFAULT FOR PAY_PROC_PERIOD_START_DATE is ''0001/01/01 00:00:00'' (DATE)
1752 DEFAULT FOR PAY_PROC_PERIOD_END_DATE is ''0001/01/02 00:00:00'' (DATE)
1753 DEFAULT FOR ASG_SALARY_BASIS is ''NOT ENTERED''
1754 DEFAULT FOR ASG_SALARY_BASIS_CODE is ''NOT ENTERED''
1755 DEFAULT FOR ASG_SALARY is 0
1756
1757 default for ASG_HOURS is 0
1758 default for Hours is 0
1759 default for Rate is 0
1760 default for Multiple is 1
1761 default for ASG_FREQ_CODE is ''NOT ENTERED''
1762
1763 /* ===== Defaults Section End ===== */
1764
1765 /* ===== Inputs Section Begin ===== */
1766
1767 Inputs are Hours,
1768 Rate,
1769 Multiple
1770
1771 /* ===== Inputs Section End ===== */
1772
1773 /* ===== local variables Start ===== */
1774
1775 l_return_status = 1
1776 l_schedule_source = '' ''
1777 l_schedule = '' ''
1778 mesg = '' ''
1779
1780 /* ===== local variables End ===== */
1781
1782 /* ===== CALCULATION SECTION BEGIN ===== */
1783
1784 IF Rate WAS DEFAULTED THEN
1785 (
1786 IF ASG_SALARY_BASIS WAS DEFAULTED THEN
1787 (
1788 mesg =
1789 GET_MESG(''PAY'',''PAY_RATE_NOT_FOUND'',''BASE_NAME'',
1790 ''<BASE NAME>'')
1791
1792 RETURN mesg
1793 )
1794 ELSE
1795 (
1796 /* Use pay basis input value id and basis to find rate. If
1797 ASG_HOURLY_SALARY is the amount, then can call
1798 Convert_Period_Type */
1799
1800 calc_rate = get_hourly_rate()
1801
1802 )
1803 )
1804 ELSE
1805 (
1806 /* Rate is entered */
1807 calc_rate = Rate
1808 )
1809
1810 /* ---- Now find Multiple ----- */
1811 IF Multiple WAS DEFAULTED THEN
1812 calc_Multiple = 1
1813 ELSE
1814 calc_Multiple = Multiple
1815
1816 /* ---- Now find Hours ----- */
1817 IF Hours WAS DEFAULTED THEN
1818 IF ASG_HOURS WAS DEFAULTED THEN
1819 (
1820 mesg =
1821 GET_MESG(''PAY'',''PAY_HOURS_NOT_FOUND'',''BASE_NAME'',
1822 ''<BASE NAME>'')
1823 RETURN mesg
1824 )
1825 ELSE
1826 /* Use standard hours entered on Assignment */
1827 ( calculated_hours = calculate_actual_hours_worked(
1828 PAY_PROC_PERIOD_START_DATE,
1829 PAY_PROC_PERIOD_END_DATE,
1830 '' '',
1831 ''Y'',
1832 ''BUSY'',
1833 '' '',
1834 l_schedule_source,
1835 l_schedule,
1836 l_return_status,
1837 mesg)
1838 earnings_amount = (calculated_hours * calc_Multiple * calc_rate)
1839 )
1840 ELSE
1841 /* Use entered hours. */
1842 (
1843 earnings_amount = (Hours * calc_Multiple * calc_rate)
1844 calculated_hours = Hours
1845 )
1846
1847 /* ===== CALCULATION SECTION END ===== */
1848
1849 /* ===== Returns Section Begin ===== */
1850
1851 RETURN
1852 earnings_amount
1853 , calculated_hours
1854 , mesg
1855
1856 /* ===== Returns Section End ===== */
1857
1858 /* End Formula Text */';
1859
1860 pay_sf_ins.ins
1861 (p_formula_id => l_formula_id
1862 ,p_template_type => 'T'
1863 ,p_legislation_code => p_legislation_code
1864 ,p_formula_name => l_formula_name
1865 ,p_description => l_formula_desc
1866 ,p_formula_text => l_formula_text
1867 ,p_object_version_number => l_object_version_number
1868 ,p_effective_date => l_effective_date
1869 );
1870
1871 --
1872 -- End Formula HOURS_X_RATE
1873 --
1874
1875 --
1876 -- 'Base' elements.
1877 --
1878
1879 pay_set_ins.ins
1880 (p_element_type_id => l_base_element_id
1881 ,p_template_id => l_template_id
1882 ,p_element_name => null
1883 ,p_reporting_name => null
1884 ,p_relative_processing_priority => 0
1885 ,p_processing_type => 'N'
1886 ,p_classification_name => 'Earnings'
1887 ,p_input_currency_code => p_currency_code
1891 ,p_process_in_run_flag => 'Y'
1888 ,p_output_currency_code => p_currency_code
1889 ,p_multiple_entries_allowed_fla => 'Y'
1890 ,p_post_termination_rule => 'F'
1892 ,p_additional_entry_allowed_fla => 'N'
1893 ,p_adjustment_only_flag => 'N'
1894 ,p_closed_for_entry_flag => 'N'
1895 ,p_indirect_only_flag => 'N'
1896 ,p_multiply_value_flag => 'N'
1897 ,p_standard_link_flag => 'N'
1898 ,p_process_mode => 'S'
1899 ,p_payroll_formula_id => l_formula_id
1900 ,p_skip_formula => NULL
1901 ,p_object_version_number => l_object_version_number
1902 ,p_effective_date => l_effective_date
1903 ,p_exclusion_rule_id => NULL
1904 );
1905
1906 hr_utility.trace('Base Element Created');
1907
1908 --
1909 -- Input Values for 'Base' element.
1910 --
1911
1912 pay_siv_ins.ins
1913 (p_input_value_id => l_base_pay_value_iv_id
1914 ,p_element_type_id => l_base_element_id
1915 ,p_display_sequence => 1
1916 ,p_generate_db_items_flag => 'Y'
1917 ,p_hot_default_flag => 'N'
1918 ,p_mandatory_flag => 'X'
1919 ,p_name => 'Pay Value'
1920 ,p_uom => 'M'
1921 ,p_exclusion_rule_id => NULL
1922 ,p_object_version_number => l_object_version_number
1923 ,p_effective_date => l_effective_date
1924 );
1925
1926 hr_utility.trace('Base Element Input Value - Pay Value Created');
1927
1928 pay_siv_ins.ins
1929 (p_input_value_id => l_base_hours_iv_id
1930 ,p_element_type_id => l_base_element_id
1931 ,p_display_sequence => 2
1932 ,p_generate_db_items_flag => 'Y'
1933 ,p_hot_default_flag => 'N'
1934 ,p_mandatory_flag => 'N'
1935 ,p_name => 'Hours'
1936 ,p_uom => 'H_DECIMAL2'
1937 ,p_exclusion_rule_id => NULL
1938 ,p_object_version_number => l_object_version_number
1939 ,p_effective_date => l_effective_date
1940 );
1941
1942 hr_utility.trace('Base Element Input Value - Hours Created');
1943
1944 pay_siv_ins.ins
1945 (p_input_value_id => l_base_rate_iv_id
1946 ,p_element_type_id => l_base_element_id
1947 ,p_display_sequence => 3
1948 ,p_generate_db_items_flag => 'Y'
1949 ,p_hot_default_flag => 'N'
1950 ,p_mandatory_flag => 'N'
1951 ,p_name => 'Rate'
1952 ,p_uom => 'N'
1953 ,p_exclusion_rule_id => NULL
1954 ,p_object_version_number => l_object_version_number
1955 ,p_effective_date => l_effective_date
1956 );
1957
1958 hr_utility.trace('Base Element Input Value - Rate Created');
1959
1960 pay_siv_ins.ins
1961 (p_input_value_id => l_base_multiple_iv_id
1962 ,p_element_type_id => l_base_element_id
1963 ,p_display_sequence => 4
1964 ,p_generate_db_items_flag => 'Y'
1965 ,p_hot_default_flag => 'N'
1966 ,p_mandatory_flag => 'N'
1967 ,p_name => 'Multiple'
1968 ,p_uom => 'N'
1969 ,p_exclusion_rule_id => NULL
1970 ,p_object_version_number => l_object_version_number
1971 ,p_effective_date => l_effective_date
1972 );
1973
1974 hr_utility.trace('Base Element Input Value - Multiple Created');
1975
1976 pay_siv_ins.ins
1977 (p_input_value_id => l_input_value_id
1978 ,p_element_type_id => l_base_element_id
1979 ,p_display_sequence => 5
1980 ,p_generate_db_items_flag => 'Y'
1981 ,p_hot_default_flag => 'N'
1982 ,p_mandatory_flag => 'X'
1983 ,p_name => 'Jurisdiction'
1984 ,p_uom => 'C'
1985 ,p_object_version_number => l_object_version_number
1986 ,p_effective_date => l_effective_date
1987 );
1988
1989 hr_utility.trace('Base Element Input Value- Jurisdiction');
1990
1991 --
1992 -- Primary balance types.
1993 --
1994
1995 pay_sbt_ins.ins
1996 (p_balance_type_id => l_primary_bal_typ_id
1997 ,p_template_id => l_template_id
1998 ,p_assignment_remuneration_flag => 'N'
1999 ,p_balance_name => null
2000 ,p_currency_code => p_currency_code
2001 ,p_reporting_name => null
2002 ,p_comments =>
2003 'Primary balance for Hours X Rate Earnings.'
2004 ,p_balance_uom => 'M'
2005 ,p_category_name => NULL --'Hourly Earnings'
2006 ,p_input_value_id => l_base_pay_value_iv_id
2007 ,p_object_version_number => l_object_version_number
2008 ,p_effective_date => l_effective_date
2009 ,p_exclusion_rule_id => l_excl_el_no_base_bal
2010 );
2011
2012 hr_utility.trace('Primary Balance Type Created.');
2013
2014 pay_sbt_ins.ins
2015 (p_balance_type_id => l_hours_bal_typ_id
2016 ,p_template_id => l_template_id
2020 ,p_reporting_name => ' Hours'
2017 ,p_assignment_remuneration_flag => 'N'
2018 ,p_balance_name => ' Hours'
2019 ,p_currency_code => p_currency_code
2021 ,p_category_name => NULL --'Earnings Hours'
2022 ,p_comments =>
2023 'Hours balance for Hours X Rate Earnings.'
2024 ,p_balance_uom => 'H_DECIMAL2'
2025 ,p_base_balance_type_id => l_primary_bal_typ_id
2026 ,p_object_version_number => l_object_version_number
2027 ,p_effective_date => l_effective_date
2028 ,p_exclusion_rule_id => l_excl_el_no_base_bal
2029 );
2030
2031 hr_utility.trace('Primary Balance Type Created.');
2032
2033 pay_sbt_ins.ins
2034 (p_balance_type_id => l_el_bal_typ_id
2035 ,p_template_id => l_template_id
2036 ,p_assignment_remuneration_flag => 'N'
2037 ,p_balance_name => ' EL'
2038 ,p_currency_code => p_currency_code
2039 ,p_reporting_name => ' EL'
2040 ,p_comments =>
2041 'Employer Liabilities balance for Hours X Rate Earnings.'
2042 ,p_balance_uom => 'M'
2043 ,p_category_name => NULL --'Employer Liabilities'
2044 ,p_input_value_id => l_base_pay_value_iv_id
2045 ,p_object_version_number => l_object_version_number
2046 ,p_effective_date => l_effective_date
2047 ,p_exclusion_rule_id => l_excl_el_no_el_bal
2048 );
2049
2050 hr_utility.trace('Primary Balance Type Created.');
2051
2052 --
2053 -- Balance Feeds.
2054 --
2055
2056 pay_sbf_ins.ins
2057 (p_balance_feed_id => l_balance_feed_id
2058 ,p_balance_type_id => l_primary_bal_typ_id
2059 ,p_input_value_id => l_base_pay_value_iv_id
2060 ,p_scale => 1
2061 ,p_object_version_number => l_object_version_number
2062 ,p_effective_date => l_effective_date
2063 );
2064
2065 hr_utility.trace('Balance Feed - Pay Value Created.');
2066
2067 pay_sbf_ins.ins
2068 (p_balance_feed_id => l_balance_feed_id
2069 ,p_balance_type_id => l_hours_bal_typ_id
2070 ,p_input_value_id => l_base_hours_iv_id
2071 ,p_scale => 1
2072 ,p_object_version_number => l_object_version_number
2073 ,p_effective_date => l_effective_date
2074 );
2075
2076 hr_utility.trace('Balance Feed - Hours Created.');
2077
2078 pay_sbf_ins.ins
2079 (p_balance_feed_id => l_balance_feed_id
2080 ,p_balance_type_id => l_el_bal_typ_id
2081 ,p_input_value_id => l_base_pay_value_iv_id
2082 ,p_scale => 1
2083 ,p_object_version_number => l_object_version_number
2084 ,p_effective_date => l_effective_date
2085 );
2086
2087 --
2088 -- Formula rules.
2089 --
2090
2091 pay_sfr_ins.ins
2092 (p_formula_result_rule_id => l_id
2093 ,p_shadow_element_type_id => l_base_element_id
2094 ,p_result_name => 'earnings_amount'
2095 ,p_result_rule_type => 'D'
2096 ,p_element_type_id => l_base_element_id
2097 ,p_input_value_id => l_base_pay_value_iv_id
2098 ,p_object_version_number => l_object_version_number
2099 ,p_effective_date => l_effective_date
2100 );
2101
2102 hr_utility.trace('Formula Rule - earnings_amount created.');
2103
2104 /***********
2105 ** This is Indirect result to special feature element for
2106 ** Hours X Rate template check pycaehxr.sql
2107 ************/
2108
2109 pay_sfr_ins.ins
2110 (p_formula_result_rule_id => l_id
2111 ,p_shadow_element_type_id => l_base_element_id
2112 ,p_result_name => 'earnings_hours'
2113 ,p_result_rule_type => 'D'
2114 ,p_element_type_id => l_base_element_id
2115 ,p_input_value_id => l_base_hours_iv_id
2116 ,p_object_version_number => l_object_version_number
2117 ,p_effective_date => l_effective_date
2118 );
2119
2120 hr_utility.trace('Formula Rule - earnings_hours created.');
2121
2122 pay_sfr_ins.ins
2123 (p_formula_result_rule_id => l_id
2124 ,p_shadow_element_type_id => l_base_element_id
2125 ,p_result_name => 'mesg'
2126 ,p_result_rule_type => 'M'
2127 ,p_severity_level => 'W'
2128 ,p_object_version_number => l_object_version_number
2129 ,p_effective_date => l_effective_date
2130 );
2131
2132 hr_utility.trace('Formula Rule - mesg created.');
2133
2134 --
2135 -- Defined Balances for Primary Balance
2136 --
2137
2138 pay_sdb_ins.ins
2139 (p_defined_balance_id => l_defined_balance_id
2140 ,p_balance_type_id => l_primary_bal_typ_id
2141 ,p_dimension_name =>
2142 'Person Calendar Year To Date'
2143 ,p_object_version_number => l_object_version_number
2144 ,p_effective_date => l_effective_date
2145 );
2146
2147 pay_sdb_ins.ins
2151 'Person Calendar Quarter To Date'
2148 (p_defined_balance_id => l_defined_balance_id
2149 ,p_balance_type_id => l_primary_bal_typ_id
2150 ,p_dimension_name =>
2152 ,p_object_version_number => l_object_version_number
2153 ,p_effective_date => l_effective_date
2154 );
2155
2156 pay_sdb_ins.ins
2157 (p_defined_balance_id => l_defined_balance_id
2158 ,p_balance_type_id => l_primary_bal_typ_id
2159 ,p_dimension_name =>
2160 'Person Calendar Month To Date'
2161 ,p_object_version_number => l_object_version_number
2162 ,p_effective_date => l_effective_date
2163 );
2164
2165 pay_sdb_ins.ins
2166 (p_defined_balance_id => l_defined_balance_id
2167 ,p_balance_type_id => l_primary_bal_typ_id
2168 ,p_dimension_name =>
2169 'Assignment Calendar Year To Date'
2170 ,p_object_version_number => l_object_version_number
2171 ,p_effective_date => l_effective_date
2172 );
2173
2174 pay_sdb_ins.ins
2175 (p_defined_balance_id => l_defined_balance_id
2176 ,p_balance_type_id => l_primary_bal_typ_id
2177 ,p_dimension_name =>
2178 'Assignment Calendar Quarter To Date'
2179 ,p_object_version_number => l_object_version_number
2180 ,p_effective_date => l_effective_date
2181 );
2182
2183 pay_sdb_ins.ins
2184 (p_defined_balance_id => l_defined_balance_id
2185 ,p_balance_type_id => l_primary_bal_typ_id
2186 ,p_dimension_name =>
2187 'Assignment Calendar Month To Date'
2188 ,p_object_version_number => l_object_version_number
2189 ,p_effective_date => l_effective_date
2190 );
2191
2192 pay_sdb_ins.ins
2193 (p_defined_balance_id => l_defined_balance_id
2194 ,p_balance_type_id => l_primary_bal_typ_id
2195 ,p_dimension_name =>
2196 'Assignment Run'
2197 ,p_object_version_number => l_object_version_number
2198 ,p_effective_date => l_effective_date
2199 );
2200
2201 pay_sdb_ins.ins
2202 (p_defined_balance_id => l_defined_balance_id
2203 ,p_balance_type_id => l_primary_bal_typ_id
2204 ,p_dimension_name => 'Payments'
2205 ,p_object_version_number => l_object_version_number
2206 ,p_effective_date => l_effective_date
2207 );
2208
2209
2210 --
2211 -- Defined Balances for Hours Balance
2212 --
2213
2214 pay_sdb_ins.ins
2215 (p_defined_balance_id => l_defined_balance_id
2216 ,p_balance_type_id => l_hours_bal_typ_id
2217 ,p_dimension_name =>
2218 'Person Calendar Year To Date'
2219 ,p_object_version_number => l_object_version_number
2220 ,p_effective_date => l_effective_date
2221 );
2222
2223 pay_sdb_ins.ins
2224 (p_defined_balance_id => l_defined_balance_id
2225 ,p_balance_type_id => l_hours_bal_typ_id
2226 ,p_dimension_name =>
2227 'Person Calendar Quarter To Date'
2228 ,p_object_version_number => l_object_version_number
2229 ,p_effective_date => l_effective_date
2230 );
2231
2232 pay_sdb_ins.ins
2233 (p_defined_balance_id => l_defined_balance_id
2234 ,p_balance_type_id => l_hours_bal_typ_id
2235 ,p_dimension_name =>
2236 'Person Calendar Month To Date'
2237 ,p_object_version_number => l_object_version_number
2238 ,p_effective_date => l_effective_date
2239 );
2240
2241 pay_sdb_ins.ins
2242 (p_defined_balance_id => l_defined_balance_id
2243 ,p_balance_type_id => l_hours_bal_typ_id
2244 ,p_dimension_name =>
2245 'Assignment Calendar Year To Date'
2246 ,p_object_version_number => l_object_version_number
2247 ,p_effective_date => l_effective_date
2248 );
2249
2250 pay_sdb_ins.ins
2251 (p_defined_balance_id => l_defined_balance_id
2252 ,p_balance_type_id => l_hours_bal_typ_id
2253 ,p_dimension_name =>
2254 'Assignment Calendar Quarter To Date'
2255 ,p_object_version_number => l_object_version_number
2256 ,p_effective_date => l_effective_date
2257 );
2258
2259 pay_sdb_ins.ins
2260 (p_defined_balance_id => l_defined_balance_id
2261 ,p_balance_type_id => l_hours_bal_typ_id
2262 ,p_dimension_name =>
2263 'Assignment Calendar Month To Date'
2264 ,p_object_version_number => l_object_version_number
2265 ,p_effective_date => l_effective_date
2266 );
2267
2268 pay_sdb_ins.ins
2269 (p_defined_balance_id => l_defined_balance_id
2270 ,p_balance_type_id => l_hours_bal_typ_id
2271 ,p_dimension_name =>
2272 'Assignment Run'
2273 ,p_object_version_number => l_object_version_number
2274 ,p_effective_date => l_effective_date
2275 );
2276
2280
2277 --
2278 -- Defined Balances for Employer Liabilties Balance
2279 --
2281 pay_sdb_ins.ins
2282 (p_defined_balance_id => l_defined_balance_id
2283 ,p_balance_type_id => l_el_bal_typ_id
2284 ,p_dimension_name =>
2285 'Person Calendar Year To Date'
2286 ,p_object_version_number => l_object_version_number
2287 ,p_effective_date => l_effective_date
2288 );
2289
2290 pay_sdb_ins.ins
2291 (p_defined_balance_id => l_defined_balance_id
2292 ,p_balance_type_id => l_el_bal_typ_id
2293 ,p_dimension_name =>
2294 'Person Calendar Quarter To Date'
2295 ,p_object_version_number => l_object_version_number
2296 ,p_effective_date => l_effective_date
2297 );
2298
2299 pay_sdb_ins.ins
2300 (p_defined_balance_id => l_defined_balance_id
2301 ,p_balance_type_id => l_el_bal_typ_id
2302 ,p_dimension_name =>
2303 'Person Calendar Month To Date'
2304 ,p_object_version_number => l_object_version_number
2305 ,p_effective_date => l_effective_date
2306 );
2307
2308 pay_sdb_ins.ins
2309 (p_defined_balance_id => l_defined_balance_id
2310 ,p_balance_type_id => l_el_bal_typ_id
2311 ,p_dimension_name =>
2312 'Assignment Calendar Year To Date'
2313 ,p_object_version_number => l_object_version_number
2314 ,p_effective_date => l_effective_date
2315 );
2316
2317 pay_sdb_ins.ins
2318 (p_defined_balance_id => l_defined_balance_id
2319 ,p_balance_type_id => l_el_bal_typ_id
2320 ,p_dimension_name =>
2321 'Assignment Calendar Quarter To Date'
2322 ,p_object_version_number => l_object_version_number
2323 ,p_effective_date => l_effective_date
2324 );
2325
2326 pay_sdb_ins.ins
2327 (p_defined_balance_id => l_defined_balance_id
2328 ,p_balance_type_id => l_el_bal_typ_id
2329 ,p_dimension_name =>
2330 'Assignment Calendar Month To Date'
2331 ,p_object_version_number => l_object_version_number
2332 ,p_effective_date => l_effective_date
2333 );
2334
2335 pay_sdb_ins.ins
2336 (p_defined_balance_id => l_defined_balance_id
2337 ,p_balance_type_id => l_el_bal_typ_id
2338 ,p_dimension_name =>
2339 'Assignment Run'
2340 ,p_object_version_number => l_object_version_number
2341 ,p_effective_date => l_effective_date
2342 );
2343
2344 pay_sdb_ins.ins
2345 (p_defined_balance_id => l_defined_balance_id
2346 ,p_balance_type_id => l_el_bal_typ_id
2347 ,p_dimension_name => 'Payments'
2348 ,p_object_version_number => l_object_version_number
2349 ,p_effective_date => l_effective_date
2350 );
2351
2352 pay_create_elemnt_tmplt_record.create_elemnt_tmplt_usages(
2353 l_template_id,
2354 'Earnings',
2355 p_legislation_code );
2356 END IF;
2357
2358 end;
2359 END create_earn_hxr_amt_templ;
2360
2361 procedure create_dedn_pct_amt_templ( p_legislation_code varchar2,
2362 p_currency_code in varchar2) IS
2363 begin
2364
2365 declare
2366 l_effective_date date;
2367 --
2368 l_template_exists Char;
2369 --
2370 l_template_id number;
2371 l_object_version_number number;
2372 --
2373 l_special_inputs_element_id number;
2374 l_base_element_id number;
2375 l_special_features_element_id number;
2376 --
2377 l_formula_id number;
2378 l_formula_text varchar2(32000);
2379 l_formula_name varchar2(80);
2380 l_formula_desc varchar2(240);
2381 --
2382 l_primary_bal_typ_id number;
2383 l_eligible_comp_bal_typ_id number;
2384 l_accrued_bal_typ_id number;
2385 l_not_taken_bal_typ_id number;
2386 l_arrears_bal_typ_id number;
2387 --
2388 l_input_value_id number;
2389 l_base_pay_value_id number;
2390 l_base_percent_id number;
2391 l_clear_arr_iv_id number;
2392 l_total_owed_iv_id number;
2393 l_sf_pay_value_id number;
2394 l_sf_accrued_value_id number;
2395 l_sf_not_taken_value_id number;
2396 l_sf_arrears_contr_value_id number;
2397 --
2398 l_defined_balance_id number;
2399 --
2400 l_balance_feed_id number;
2401 --
2402 l_reg_tax_proc_type number;
2403 l_non_per_tax_proc_type number;
2404 l_arrearage_rule_id number;
2405 l_stop_rule_id number;
2406 l_start_rule_id number;
2407 l_non_recurring_rule_id number;
2408 --
2409 l_id number;
2410 l_element_type_usage_id number;
2411 l_balance_attribute_id number;
2412
2413 duplicate_template exception;
2414 l_enabled_flag varchar2(4);
2415
2416
2417 begin
2418
2419 --hr_utility.trace_on (null,'FLATAMT');
2420
2421
2422 /* Check for Template Existence */
2423
2427 SELECT 'Y', Template_id
2424 l_template_exists := 'N';
2425
2426 BEGIN
2428 INTO l_template_exists, l_template_id
2429 FROM pay_element_templates
2430 WHERE Template_type = 'T'
2431 AND Legislation_code = p_legislation_code
2432 AND template_name = 'Percentage Deduction';
2433 EXCEPTION
2434 WHEN NO_DATA_FOUND THEN
2435 NULL;
2436 END;
2437
2438 IF (l_template_exists = 'Y')
2439 THEN
2440 BEGIN
2441 delete from PAY_ELE_TMPLT_CLASS_USAGES
2442 where template_id = l_template_id;
2443
2444 pay_element_template_api.delete_user_structure(false,true,
2445 l_template_id);
2446 l_template_exists := 'N';
2447 EXCEPTION
2448 WHEN OTHERS THEN
2449 l_template_exists := 'N';
2450 NULL;
2451 END;
2452 END IF;
2453
2454 /* End of Check */
2455
2456 IF l_template_exists = 'N'
2457 THEN
2458
2459 l_effective_date := to_date('1901/01/01', 'YYYY/MM/DD');
2460
2461 --
2462 -- PAY_ELEMENT_TEMPLATES row.
2463 --
2464 pay_etm_ins.ins
2465 (p_template_id => l_template_id
2466 ,p_effective_date => l_effective_date
2467 ,p_template_type => 'T'
2468 ,p_template_name => 'Percentage Deduction'
2469 ,p_base_processing_priority => 3750
2470 ,p_max_base_name_length => 40
2471 ,p_version_number => 1
2472 ,p_legislation_code => p_legislation_code
2473 ,p_object_version_number => l_object_version_number
2474 );
2475
2476 --
2477 -- Formula.
2478 --
2479
2480 l_formula_name := '_PCT_DEDN';
2481 l_formula_desc := 'Percentage Deduction formula for Deduction Template';
2482
2483 l_formula_text :=
2484 '/*****************************************************************************
2485
2486 FORMULA NAME: _PCT_DEDN
2487
2488 FORMULA TYPE: Payroll
2489
2490 DESCRIPTION: Formula for percentage Amount for Deduction Template
2491 for International Payroll.
2492 Returns pay value (Amount);
2493
2494 *******************************************************************************
2495
2496 FORMULA TEXT
2497
2498 Formula Results :
2499
2500 dedn_amt Direct Result for Deduction Amount
2501 not_taken Update Deduction Recurring Entry Not Taken
2502 to_arrears Update Deduction Recurring Entry Arrears Contr
2503 set_clear Update Deduction Recurring Entry Clear Arrears
2504 STOP_ENTRY Stop current recurring entry
2505 to_total_owed Update Deduction Recurring Entry Accrued
2506 mesg Message (Warning)
2507
2508 *******************************************************************************/
2509
2510
2511 /* Database Item Defaults */
2512
2513 default for INSUFFICIENT_FUNDS_TYPE is ''NOT ENTERED''
2514
2515 /* ===== Database Item Defaults End ===== */
2516
2517 /* ===== Input Value Defaults Begin ===== */
2518
2519 DEFAULT FOR Total_Owed IS 0
2520 DEFAULT FOR Clear_Arrears (text) IS ''N''
2521 DEFAULT FOR Percentage IS 0
2522 DEFAULT FOR EXTRA_ELEMENT_INFO_DDF_DEDUCTION_PROCESSING_INSUFFICIENT_FUNDS_TYPE is ''NOT ENTERED''
2523
2524 /* ===== Input Value Defaults End ===== */
2525
2526 DEFAULT FOR mesg IS ''NOT ENTERED''
2527
2528 /* ===== Inputs Section Begin ===== */
2529
2530 INPUTS ARE
2531 Percentage
2532 ,Total_Owed
2533 ,Clear_Arrears (text)
2534
2535 /* ===== Inputs Section End ===== */
2536
2537
2538 IF Percentage WAS DEFAULTED THEN
2539 (
2540 mesg = GET_MESG(''PAY'',''PAY_NO_VALUE_TO_CALC_DED''
2541 ,''BASE_NAME'',''<BASE NAME>'')
2542 RETURN mesg
2543 )
2544 ELSE
2545 (
2546 dedn_amt = (Percentage * <BASE NAME>_ELIGIBLE_COMP_ASG_RUN / 100)
2547
2548 /* ---------------------------------------------------------------------
2549 CUSTOMER : The formula is generated with a default to use the Eligible
2550 Compensation to calculate % of Earnings. The Eligible Compensation
2551 balance is initially defined with the same balance feeds as the Regular
2552 Earnings balance.
2553 You can modify the earnings basis for this calculation by
2554 adding and deleting balance feeds to the
2555 <BASE NAME>_ELIGIBLE_COMP balance.
2556 If you want the formula to use another balance of earnings in
2557 the run, replace the <BASE NAME>_ELIGIBLE_COMP_ASG_GRE_RUN database item
2558 reference below with the database item for the balance of choice :
2559 <BALANCE_NAME_IN_UPPER_CASE/UNDERSCORES>_ASG_GRE_RUN
2560 ---------------------------------------------------------------------
2561 */
2562
2563 )
2564
2565 to_total_owed = 0
2566 to_arrears = 0
2567 to_not_taken = 0
2568 total_dedn = 0
2569 insuff_funds_type = EXTRA_ELEMENT_INFO_DDF_DEDUCTION_PROCESSING_INSUFFICIENT_FUNDS_TYPE
2570 net_amount = TOTAL_PAYMENTS_ASG_RUN
2571
2572 /* ==== Entry ITD Check Begin ==== */
2573
2574 IF ( <BASE NAME>_ACCRUED_ENTRY_ITD = 0 AND
2575 <BASE NAME>_ACCRUED_ASG_ITD <> 0 ) THEN
2576 (
2577 to_total_owed = -1 * <BASE NAME>_ACCRUED_ASG_ITD + dedn_amt
2578 )
2579
2580 IF ( <BASE NAME>_ARREARS_ENTRY_ITD = 0 AND
2581 <BASE NAME>_ARREARS_ASG_ITD <> 0 ) THEN
2582 (
2586 /* ==== Entry ITD Check End ==== */
2583 to_arrears = -1 * <BASE NAME>_ARREARS_ASG_ITD
2584 )
2585
2587
2588 /* ===== Arrears Section Begin ===== */
2589
2590 IF Clear_Arrears = ''Y'' THEN
2591 (
2592 to_arrears = -1 * <BASE NAME>_ARREARS_ASG_ITD
2593 set_clear = ''No''
2594 )
2595
2596 IF insuff_funds_type = ''PD'' THEN /*Partial Deduction */
2597 (
2598 IF ( net_amount - dedn_amt >= 0 ) THEN
2599 (
2600 to_arrears = 0
2601 to_not_taken = 0
2602 dedn_amt = dedn_amt
2603 )
2604 ELSE
2605 (
2606 to_arrears = 0
2607 to_not_taken = dedn_amt - net_amount
2608 dedn_amt = net_amount
2609 )
2610 )
2611 ELSE IF insuff_funds_type = ''APD'' THEN /*Arrearage and Partial Deduction */
2612 (
2613 IF ( net_amount <= 0 ) THEN
2614 (
2615 to_arrears = dedn_amt
2616 to_not_taken = dedn_amt
2617 dedn_amt = 0
2618 )
2619 ELSE
2620 (
2621 total_dedn = dedn_amt + <BASE NAME>_ARREARS_ASG_ITD
2622 IF ( net_amount >= total_dedn ) THEN
2623 (
2624 to_arrears = -1 * <BASE NAME>_ARREARS_ASG_ITD
2625 to_not_taken = 0
2626 dedn_amt = total_dedn
2627 )
2628 ELSE
2629 (
2630 to_arrears = total_dedn - net_amount
2631 to_arrears = to_arrears - <BASE NAME>_ARREARS_ASG_ITD
2632 IF ( net_amount >= dedn_amt ) THEN
2633 (
2634 to_not_taken = 0
2635 dedn_amt = net_amount
2636 )
2637 ELSE
2638 (
2639 to_not_taken = to_arrears
2640 dedn_amt = net_amount
2641 )
2642 )
2643 )
2644 )
2645
2646 ELSE IF insuff_funds_type = ''A'' THEN /*Arrearage */
2647 (
2648 IF ( net_amount <= 0 ) THEN
2649 (
2650 to_arrears = dedn_amt
2651 to_not_taken = dedn_amt
2652 dedn_amt = 0
2653 )
2654 ELSE
2655 (
2656 total_dedn = dedn_amt + <BASE NAME>_ARREARS_ASG_ITD
2657 IF ( net_amount >= total_dedn ) THEN
2658 (
2659 to_arrears = -1 * <BASE NAME>_ARREARS_ASG_ITD
2660 to_not_taken = 0
2661 dedn_amt = total_dedn
2662 )
2663 ELSE
2664 (
2665 IF ( net_amount >= dedn_amt ) THEN
2666 (
2667 to_arrears = 0
2668 to_not_taken = 0
2669 dedn_amt = dedn_amt
2670 )
2671 ELSE
2672 (
2673 to_arrears = dedn_amt
2674 to_not_taken = dedn_amt
2675 dedn_amt = 0
2676 )
2677 )
2678 )
2679 )
2680 ELSE IF insuff_funds_type = ''NONE'' THEN /* No Arrearage and No Partial Deduction */
2681 (
2682 IF ( net_amount - dedn_amt >= 0 ) THEN
2683 (
2684 to_arrears = 0
2685 to_not_taken = 0
2686 dedn_amt = dedn_amt
2687 )
2688 ELSE
2689 (
2690 to_arrears = 0
2691 to_not_taken = 0
2692 dedn_amt = 0
2693 )
2694 )
2695 ELSE /* Error*/
2696 (
2697 IF ( net_amount - dedn_amt < 0 ) THEN
2698 (
2699 mesg = GET_MESG(''PAY'',''PAY_INSUFF_FUNDS_FOR_DED'')
2700 RETURN mesg
2701 )
2702 )
2703
2704
2705 /* ===== Arrears Section End ===== */
2706
2707 /* ===== Stop Rule Section Begin ===== */
2708
2709 to_total_owed = dedn_amt
2710
2711 IF Total_Owed WAS NOT DEFAULTED THEN
2712 (
2713 total_accrued = dedn_amt + <BASE NAME>_ACCRUED_ASG_ITD
2714
2715 IF total_accrued >= Total_Owed THEN
2716 (
2717 dedn_amt = Total_Owed - <BASE NAME>_ACCRUED_ASG_ITD
2718
2719 /* The total has been reached - the return will stop the entry under
2720 these conditions. Also, zero out Accrued balance. */
2721
2722 to_total_owed = -1 * <BASE NAME>_ACCRUED_ASG_ITD
2723 STOP_ENTRY = ''Y''
2724
2725 mesg = GET_MESG(''PAY'',''PAY_STOPPED_ENTRY'',
2726 ''BASE_NAME'',''<BASE NAME>'')
2727 )
2728 )
2729
2730 /* ===== Stop Rule Section End ===== */
2731
2732 RETURN dedn_amt,
2733 to_not_taken,
2737 set_clear,
2734 to_arrears,
2735 to_total_owed,
2736 STOP_ENTRY,
2738 mesg
2739
2740 /* End Formula Text */';
2741
2742 pay_sf_ins.ins
2743 (p_formula_id => l_formula_id
2744 ,p_template_type => 'T'
2745 ,p_legislation_code => p_legislation_code
2746 ,p_formula_name => l_formula_name
2750 ,p_effective_date => l_effective_date
2747 ,p_description => l_formula_desc
2748 ,p_formula_text => l_formula_text
2749 ,p_object_version_number => l_object_version_number
2751 );
2752
2753 --
2754 -- End Formula
2755 --
2756
2757 --
2758 -- 'Base' element.
2759 --
2760
2761 pay_set_ins.ins
2762 (p_element_type_id => l_base_element_id
2763 ,p_template_id => l_template_id
2764 ,p_element_name => null
2765 ,p_reporting_name => null
2766 ,p_relative_processing_priority => 0
2767 ,p_processing_type => 'N'
2768 ,p_classification_name => 'Voluntary Deductions'
2769 ,p_input_currency_code => p_currency_code
2770 ,p_output_currency_code => p_currency_code
2771 ,p_multiple_entries_allowed_fla => 'Y'
2772 ,p_post_termination_rule => 'F'
2773 ,p_process_in_run_flag => 'Y'
2774 ,p_additional_entry_allowed_fla => 'N'
2775 ,p_adjustment_only_flag => 'N'
2776 ,p_closed_for_entry_flag => 'N'
2777 ,p_indirect_only_flag => 'N'
2778 ,p_multiply_value_flag => 'N'
2779 ,p_standard_link_flag => 'N'
2780 ,p_process_mode => 'S'
2781 ,p_payroll_formula_id => l_formula_id
2782 ,p_skip_formula => NULL
2783 ,p_object_version_number => l_object_version_number
2784 ,p_effective_date => l_effective_date
2785 ,p_exclusion_rule_id => NULL
2786 );
2787
2788 hr_utility.trace('Base Element Created');
2789
2790 --
2791 -- 'Special Features' element.
2792 --
2793
2794 pay_set_ins.ins
2795 (p_element_type_id => l_special_features_element_id
2796 ,p_template_id => l_template_id
2797 ,p_element_name => ' Special Features'
2798 ,p_reporting_name => ' SF'
2799 ,p_relative_processing_priority => 50
2800 ,p_processing_type => 'N'
2801 ,p_classification_name => 'Information'
2802 ,p_input_currency_code => p_currency_code
2803 ,p_output_currency_code => p_currency_code
2804 ,p_multiple_entries_allowed_fla => 'N'
2805 ,p_post_termination_rule => 'F'
2806 ,p_process_in_run_flag => 'Y'
2807 ,p_additional_entry_allowed_fla => 'N'
2808 ,p_adjustment_only_flag => 'N'
2809 ,p_closed_for_entry_flag => 'N'
2810 ,p_indirect_only_flag => 'N'
2811 ,p_multiply_value_flag => 'N'
2812 ,p_standard_link_flag => 'N'
2813 ,p_object_version_number => l_object_version_number
2814 ,p_effective_date => l_effective_date
2815 );
2816
2817 hr_utility.trace('Special Features Element Created');
2818
2819 --
2820 -- Input Values for 'Base' element.
2821 --
2822
2823 pay_siv_ins.ins
2824 (p_input_value_id => l_base_pay_value_id
2825 ,p_element_type_id => l_base_element_id
2826 ,p_display_sequence => 1
2827 ,p_generate_db_items_flag => 'Y'
2828 ,p_hot_default_flag => 'N'
2829 ,p_mandatory_flag => 'X'
2830 ,p_name => 'Pay Value'
2831 ,p_uom => 'M'
2832 ,p_exclusion_rule_id => NULL
2833 ,p_object_version_number => l_object_version_number
2834 ,p_effective_date => l_effective_date
2835 );
2836
2837 hr_utility.trace('Base Element Input Value - Pay Value Created');
2838
2839 pay_siv_ins.ins
2840 (p_input_value_id => l_base_percent_id
2841 ,p_element_type_id => l_base_element_id
2842 ,p_display_sequence => 2
2843 ,p_generate_db_items_flag => 'Y'
2844 ,p_hot_default_flag => 'N'
2845 ,p_mandatory_flag => 'N'
2846 ,p_name => 'Percentage'
2847 ,p_uom => 'M'
2848 ,p_exclusion_rule_id => NULL
2849 ,p_object_version_number => l_object_version_number
2850 ,p_effective_date => l_effective_date
2851 );
2852
2853 hr_utility.trace('Base Element Input Value - Amount Created');
2854
2855 pay_siv_ins.ins
2856 (p_input_value_id => l_clear_arr_iv_id
2857 ,p_element_type_id => l_base_element_id
2858 ,p_display_sequence => 3
2859 ,p_generate_db_items_flag => 'Y'
2860 ,p_hot_default_flag => 'N'
2861 ,p_mandatory_flag => 'N' /* user-enterable. */
2862 ,p_name => 'Clear Arrears'
2863 ,p_uom => 'C'
2864 ,p_lookup_type => 'YES_NO'
2865 ,p_default_value => 'N'
2866 ,p_object_version_number => l_object_version_number
2867 ,p_exclusion_rule_id => l_arrearage_rule_id
2868 ,p_effective_date => l_effective_date
2869 );
2870
2871 hr_utility.trace('Base Element Input Value - Clear Arrears Created');
2872
2873 pay_siv_ins.ins
2874 (p_input_value_id => l_total_owed_iv_id
2875 ,p_element_type_id => l_base_element_id
2879 ,p_mandatory_flag => 'N' /* user-enterable. */
2876 ,p_display_sequence => 4
2877 ,p_generate_db_items_flag => 'Y'
2878 ,p_hot_default_flag => 'N'
2880 ,p_name => 'Total Owed'
2881 ,p_uom => 'M'
2882 ,p_object_version_number => l_object_version_number
2883 ,p_exclusion_rule_id => l_stop_rule_id
2887 hr_utility.trace('Base Element Input Value - Total Owed Created');
2884 ,p_effective_date => l_effective_date
2885 );
2886
2888
2889
2890 --
2891 -- Input Values for 'Special Features' element.
2892 --
2893
2894 pay_siv_ins.ins
2895 (p_input_value_id => l_sf_pay_value_id
2896 ,p_element_type_id => l_special_features_element_id
2897 ,p_display_sequence => 1
2898 ,p_generate_db_items_flag => 'Y'
2899 ,p_hot_default_flag => 'N'
2900 ,p_mandatory_flag => 'X'
2901 ,p_name => 'Pay Value'
2902 ,p_uom => 'M'
2903 ,p_object_version_number => l_object_version_number
2904 ,p_effective_date => l_effective_date
2905 );
2906
2907 hr_utility.trace('Pay Value for Special Features Created');
2908
2909 pay_siv_ins.ins
2910 (p_input_value_id => l_sf_accrued_value_id
2911 ,p_element_type_id => l_special_features_element_id
2912 ,p_display_sequence => 2
2913 ,p_generate_db_items_flag => 'Y'
2914 ,p_hot_default_flag => 'N'
2915 ,p_mandatory_flag => 'X'
2916 ,p_name => 'Accrued'
2917 ,p_uom => 'M'
2918 ,p_object_version_number => l_object_version_number
2919 ,p_exclusion_rule_id => l_stop_rule_id
2920 ,p_effective_date => l_effective_date
2921 );
2922
2923 hr_utility.trace('Accrued Input Value for Special Features Created');
2924
2925 pay_siv_ins.ins
2926 (p_input_value_id => l_sf_not_taken_value_id
2927 ,p_element_type_id => l_special_features_element_id
2928 ,p_display_sequence => 3
2929 ,p_generate_db_items_flag => 'Y'
2930 ,p_hot_default_flag => 'N'
2931 ,p_mandatory_flag => 'X'
2932 ,p_name => 'Not Taken'
2933 ,p_uom => 'M'
2934 ,p_object_version_number => l_object_version_number
2935 ,p_effective_date => l_effective_date
2936 );
2937
2938 hr_utility.trace('Not Taken Input Value for Special Features Created');
2939
2940 pay_siv_ins.ins
2941 (p_input_value_id => l_sf_arrears_contr_value_id
2942 ,p_element_type_id => l_special_features_element_id
2943 ,p_display_sequence => 4
2944 ,p_generate_db_items_flag => 'Y'
2945 ,p_hot_default_flag => 'N'
2946 ,p_mandatory_flag => 'X'
2947 ,p_name => 'Arrears Contr'
2948 ,p_uom => 'M'
2949 ,p_object_version_number => l_object_version_number
2950 ,p_exclusion_rule_id => l_arrearage_rule_id
2951 ,p_effective_date => l_effective_date
2952 );
2953
2954 hr_utility.trace('Arrears Contr Input Val for Special Features Created');
2955
2956 --
2957 -- Primary balance types.
2958 --
2959
2960 pay_sbt_ins.ins
2961 (p_balance_type_id => l_primary_bal_typ_id
2962 ,p_template_id => l_template_id
2963 ,p_assignment_remuneration_flag => 'N'
2964 ,p_balance_name => null
2965 ,p_currency_code => p_currency_code
2966 ,p_reporting_name => null
2967 ,p_comments =>
2968 'Primary balance for Percentage Deductions.'
2969 ,p_balance_uom => 'M'
2970 ,p_category_name => NULL --'Deductions'
2971 ,p_input_value_id => l_base_pay_value_id
2972 ,p_object_version_number => l_object_version_number
2973 ,p_effective_date => l_effective_date
2974 );
2975
2976 hr_utility.trace('Primary Balance Type Created.');
2977
2978 pay_sbt_ins.ins
2979 (p_balance_type_id => l_eligible_comp_bal_typ_id
2980 ,p_template_id => l_template_id
2981 ,p_assignment_remuneration_flag => 'N'
2982 ,p_balance_name => ' Eligible Comp'
2983 ,p_currency_code => p_currency_code
2984 ,p_reporting_name => ' Eligible Comp'
2985 ,p_comments =>
2986 'Eligible Comp. balance for Percentage Deduction.'
2987 ,p_balance_uom => 'M'
2988 ,p_category_name => NULL --'Wages'
2989 ,p_base_balance_type_id => l_primary_bal_typ_id
2990 ,p_object_version_number => l_object_version_number
2991 ,p_effective_date => l_effective_date
2992 );
2993
2994 hr_utility.trace('Eligible Comp. balance Type Created.');
2995
2996 pay_sbt_ins.ins
2997 (p_balance_type_id => l_accrued_bal_typ_id
3001 ,p_currency_code => p_currency_code
2998 ,p_template_id => l_template_id
2999 ,p_assignment_remuneration_flag => 'N'
3000 ,p_balance_name => ' Accrued'
3002 ,p_reporting_name => ' Accrued'
3003 ,p_comments =>
3004 'Accrued balance for Percentage Deduction.'
3005 ,p_balance_uom => 'M'
3006 ,p_category_name => NULL --'Wages'
3007 ,p_base_balance_type_id => l_primary_bal_typ_id
3008 ,p_object_version_number => l_object_version_number
3009 ,p_effective_date => l_effective_date
3010 );
3011
3012
3013 hr_utility.trace('Accrued Balance Type Created.');
3014
3015 pay_sbt_ins.ins
3016 (p_balance_type_id => l_arrears_bal_typ_id
3017 ,p_template_id => l_template_id
3018 ,p_assignment_remuneration_flag => 'N'
3019 ,p_balance_name => ' Arrears'
3020 ,p_currency_code => p_currency_code
3021 ,p_reporting_name => ' Arrears'
3022 ,p_comments =>
3023 'Arrears balance for Percentage Deduction.'
3024 ,p_balance_uom => 'M'
3025 ,p_category_name => NULL --'Wages'
3026 ,p_base_balance_type_id => l_primary_bal_typ_id
3027 ,p_object_version_number => l_object_version_number
3028 ,p_effective_date => l_effective_date
3029 );
3030
3031 hr_utility.trace('Arrears Balance Type Created.');
3032
3033 pay_sbt_ins.ins
3034 (p_balance_type_id => l_not_taken_bal_typ_id
3035 ,p_template_id => l_template_id
3036 ,p_assignment_remuneration_flag => 'N'
3037 ,p_balance_name => ' Not Taken'
3038 ,p_currency_code => p_currency_code
3039 ,p_reporting_name => ' Not Taken'
3040 ,p_comments =>
3041 'Not Taken balance for Percentage Deduction.'
3042 ,p_balance_uom => 'M'
3043 ,p_category_name => NULL --'Wages'
3044 ,p_base_balance_type_id => l_primary_bal_typ_id
3045 ,p_object_version_number => l_object_version_number
3046 ,p_effective_date => l_effective_date
3047 );
3048
3049 hr_utility.trace('Not Taken Balance Type Created.');
3050
3051 --
3052 -- Balance Feeds.
3053 --
3054
3055 pay_sbf_ins.ins
3056 (p_balance_feed_id => l_balance_feed_id
3057 ,p_balance_type_id => l_primary_bal_typ_id
3058 ,p_input_value_id => l_base_pay_value_id
3059 ,p_scale => 1
3060 ,p_object_version_number => l_object_version_number
3061 ,p_effective_date => l_effective_date
3062 );
3063
3064 hr_utility.trace('Balance Feed - Pay Value to Primary Bal Created.');
3065
3066 pay_sbf_ins.ins
3067 (p_balance_feed_id => l_balance_feed_id
3068 ,p_balance_type_id => l_accrued_bal_typ_id
3069 ,p_input_value_id => l_sf_accrued_value_id
3070 ,p_scale => 1
3071 ,p_object_version_number => l_object_version_number
3072 ,p_effective_date => l_effective_date
3073 );
3074
3075 hr_utility.trace('Balance Feed - Accrued to Accrued Bal Created.');
3076
3077 pay_sbf_ins.ins
3078 (p_balance_feed_id => l_balance_feed_id
3079 ,p_balance_type_id => l_not_taken_bal_typ_id
3080 ,p_input_value_id => l_sf_not_taken_value_id
3081 ,p_scale => 1
3082 ,p_object_version_number => l_object_version_number
3083 ,p_effective_date => l_effective_date
3084 );
3085
3086 hr_utility.trace('Balance Feed - Not Taken to Not Taken Bal Created.');
3087
3088 pay_sbf_ins.ins
3089 (p_balance_feed_id => l_balance_feed_id
3090 ,p_balance_type_id => l_arrears_bal_typ_id
3091 ,p_input_value_id => l_sf_arrears_contr_value_id
3092 ,p_scale => 1
3093 ,p_object_version_number => l_object_version_number
3094 ,p_effective_date => l_effective_date
3095 );
3096
3097 hr_utility.trace('Balance Feed - Arrears Contr to Arrears Bal Created.');
3098
3099 --
3100 -- Formula rules.
3101 --
3102
3103 pay_sfr_ins.ins
3104 (p_formula_result_rule_id => l_id
3105 ,p_shadow_element_type_id => l_base_element_id
3106 ,p_result_name => 'dedn_amt'
3107 ,p_result_rule_type => 'D'
3108 ,p_element_type_id => l_base_element_id
3109 ,p_input_value_id => l_base_pay_value_id
3110 ,p_object_version_number => l_object_version_number
3111 ,p_effective_date => l_effective_date
3112 );
3113
3114 hr_utility.trace('Formula Rule - dedn_amt created.');
3115
3116 pay_sfr_ins.ins
3117 (p_formula_result_rule_id => l_id
3118 ,p_shadow_element_type_id => l_base_element_id
3119 ,p_result_name => 'STOP_ENTRY'
3120 ,p_result_rule_type => 'S'
3121 ,p_element_type_id => l_base_element_id
3122 ,p_exclusion_rule_id => l_non_recurring_rule_id
3126
3123 ,p_object_version_number => l_object_version_number
3124 ,p_effective_date => l_effective_date
3125 );
3127 hr_utility.trace('Formula Rule - STOP_ENTRY created.');
3128
3129 pay_sfr_ins.ins
3130 (p_formula_result_rule_id => l_id
3131 ,p_shadow_element_type_id => l_base_element_id
3132 ,p_result_name => 'set_clear'
3133 ,p_result_rule_type => 'U'
3134 ,p_element_type_id => l_base_element_id
3135 ,p_input_value_id => l_clear_arr_iv_id
3136 ,p_exclusion_rule_id => l_arrearage_rule_id
3137 ,p_object_version_number => l_object_version_number
3138 ,p_effective_date => l_effective_date
3139 );
3140
3141 hr_utility.trace('Formula Rule - set_clear created.');
3142
3143 pay_sfr_ins.ins
3144 (p_formula_result_rule_id => l_id
3145 ,p_shadow_element_type_id => l_base_element_id
3146 ,p_result_name => 'mesg'
3147 ,p_result_rule_type => 'M'
3148 ,p_severity_level => 'W'
3149 ,p_object_version_number => l_object_version_number
3150 ,p_effective_date => l_effective_date
3151 );
3152
3153 hr_utility.trace('Formula Rule - mesg created.');
3154
3155 pay_sfr_ins.ins
3156 (p_formula_result_rule_id => l_id
3157 ,p_shadow_element_type_id => l_base_element_id
3158 ,p_result_name => 'to_not_taken'
3159 ,p_result_rule_type => 'I'
3160 ,p_element_type_id => l_special_features_element_id
3161 ,p_input_value_id => l_sf_not_taken_value_id
3162 ,p_object_version_number => l_object_version_number
3163 ,p_effective_date => l_effective_date
3164 );
3165
3166 hr_utility.trace('Formula Rule - to_not_taken created.');
3167
3168 pay_sfr_ins.ins
3169 (p_formula_result_rule_id => l_id
3170 ,p_shadow_element_type_id => l_base_element_id
3171 ,p_result_name => 'to_total_owed'
3172 ,p_result_rule_type => 'I'
3173 ,p_element_type_id => l_special_features_element_id
3174 ,p_input_value_id => l_sf_accrued_value_id
3175 ,p_exclusion_rule_id => l_stop_rule_id
3176 ,p_object_version_number => l_object_version_number
3177 ,p_effective_date => l_effective_date
3178 );
3179
3180 hr_utility.trace('Formula Rule - to_total_owed created.');
3181
3182 pay_sfr_ins.ins
3183 (p_formula_result_rule_id => l_id
3184 ,p_shadow_element_type_id => l_base_element_id
3185 ,p_result_name => 'to_arrears'
3186 ,p_result_rule_type => 'I'
3187 ,p_element_type_id => l_special_features_element_id
3188 ,p_input_value_id => l_sf_arrears_contr_value_id
3189 ,p_exclusion_rule_id => l_arrearage_rule_id
3190 ,p_object_version_number => l_object_version_number
3191 ,p_effective_date => l_effective_date
3192 );
3193
3194 hr_utility.trace('Formula Rule - to_arrears created.');
3195
3196 --
3197 -- Defined Balances
3198 --
3199
3200 pay_sdb_ins.ins
3201 (p_defined_balance_id => l_defined_balance_id
3202 ,p_balance_type_id => l_primary_bal_typ_id
3203 ,p_dimension_name =>
3204 'Assignment Run'
3205 ,p_object_version_number => l_object_version_number
3206 ,p_effective_date => l_effective_date
3207 );
3208
3209 pay_sdb_ins.ins
3210 (p_defined_balance_id => l_defined_balance_id
3211 ,p_balance_type_id => l_primary_bal_typ_id
3212 ,p_dimension_name =>
3213 'Assignment Calendar Year to Date'
3214 ,p_object_version_number => l_object_version_number
3215 ,p_effective_date => l_effective_date
3216 );
3217
3218 pay_sdb_ins.ins
3219 (p_defined_balance_id => l_defined_balance_id
3220 ,p_balance_type_id => l_primary_bal_typ_id
3221 ,p_dimension_name =>
3222 'Assignment Calendar Quarter to Date'
3223 ,p_object_version_number => l_object_version_number
3224 ,p_effective_date => l_effective_date
3225 );
3226
3227 pay_sdb_ins.ins
3228 (p_defined_balance_id => l_defined_balance_id
3229 ,p_balance_type_id => l_primary_bal_typ_id
3230 ,p_dimension_name =>
3231 'Assignment Calendar Month To Date'
3232 ,p_object_version_number => l_object_version_number
3233 ,p_effective_date => l_effective_date
3234 );
3235
3236 pay_sdb_ins.ins
3237 (p_defined_balance_id => l_defined_balance_id
3238 ,p_balance_type_id => l_primary_bal_typ_id
3239 ,p_dimension_name => 'Payments'
3240 ,p_object_version_number => l_object_version_number
3241 ,p_effective_date => l_effective_date
3242 );
3243
3244 --
3245 -- Defined Balances for Accrued Balance
3246 --
3247
3248 pay_sdb_ins.ins
3249 (p_defined_balance_id => l_defined_balance_id
3250 ,p_balance_type_id => l_accrued_bal_typ_id
3251 ,p_dimension_name =>
3252 'Element Entry Inception to Date'
3256
3253 ,p_object_version_number => l_object_version_number
3254 ,p_effective_date => l_effective_date
3255 );
3257 pay_sdb_ins.ins
3258 (p_defined_balance_id => l_defined_balance_id
3259 ,p_balance_type_id => l_accrued_bal_typ_id
3260 ,p_dimension_name =>
3261 'Assignment Inception to Date'
3262 ,p_object_version_number => l_object_version_number
3263 ,p_effective_date => l_effective_date
3264 );
3265
3266 pay_sdb_ins.ins
3267 (p_defined_balance_id => l_defined_balance_id
3268 ,p_balance_type_id => l_accrued_bal_typ_id
3269 ,p_dimension_name =>
3270 'Assignment Run'
3271 ,p_object_version_number => l_object_version_number
3272 ,p_effective_date => l_effective_date
3273 );
3274
3275 pay_sdb_ins.ins
3276 (p_defined_balance_id => l_defined_balance_id
3277 ,p_balance_type_id => l_accrued_bal_typ_id
3278 ,p_dimension_name =>
3279 'Assignment Calendar Month To Date'
3280 ,p_object_version_number => l_object_version_number
3281 ,p_effective_date => l_effective_date
3282 );
3283
3284 --
3285 -- Defined Balances for Arrears Balance
3286 --
3287
3288 pay_sdb_ins.ins
3289 (p_defined_balance_id => l_defined_balance_id
3290 ,p_balance_type_id => l_arrears_bal_typ_id
3291 ,p_dimension_name =>
3292 'Element Entry Inception to Date'
3293 ,p_object_version_number => l_object_version_number
3294 ,p_effective_date => l_effective_date
3295 );
3296
3297 pay_sdb_ins.ins
3298 (p_defined_balance_id => l_defined_balance_id
3299 ,p_balance_type_id => l_arrears_bal_typ_id
3300 ,p_dimension_name =>
3301 'Assignment Inception to Date'
3302 ,p_object_version_number => l_object_version_number
3303 ,p_effective_date => l_effective_date
3304 );
3305
3306 pay_sdb_ins.ins
3307 (p_defined_balance_id => l_defined_balance_id
3308 ,p_balance_type_id => l_arrears_bal_typ_id
3309 ,p_dimension_name =>
3310 'Assignment Run'
3311 ,p_object_version_number => l_object_version_number
3312 ,p_effective_date => l_effective_date
3313 );
3314
3315 pay_sdb_ins.ins
3316 (p_defined_balance_id => l_defined_balance_id
3317 ,p_balance_type_id => l_arrears_bal_typ_id
3318 ,p_dimension_name => 'Assignment Calendar Month To Date'
3319 ,p_object_version_number => l_object_version_number
3320 ,p_effective_date => l_effective_date
3321 );
3322
3323 --
3324 -- Defined Balances for Not Taken Balance
3325 --
3326
3327 pay_sdb_ins.ins
3328 (p_defined_balance_id => l_defined_balance_id
3329 ,p_balance_type_id => l_not_taken_bal_typ_id
3330 ,p_dimension_name =>
3331 'Assignment Inception to Date'
3332 ,p_object_version_number => l_object_version_number
3333 ,p_effective_date => l_effective_date
3334 );
3335
3336 pay_sdb_ins.ins
3337 (p_defined_balance_id => l_defined_balance_id
3338 ,p_balance_type_id => l_not_taken_bal_typ_id
3339 ,p_dimension_name =>
3340 'Assignment Run'
3341 ,p_object_version_number => l_object_version_number
3342 ,p_effective_date => l_effective_date
3343 );
3344
3345 pay_sdb_ins.ins
3346 (p_defined_balance_id => l_defined_balance_id
3347 ,p_balance_type_id => l_not_taken_bal_typ_id
3348 ,p_dimension_name => 'Assignment Calendar Month To Date'
3349 ,p_object_version_number => l_object_version_number
3350 ,p_effective_date => l_effective_date
3351 );
3352
3353 --
3354 -- Defined Balances for Eligible Comp. Balance
3355 --
3356
3357 pay_sdb_ins.ins
3358 (p_defined_balance_id => l_defined_balance_id
3359 ,p_balance_type_id => l_eligible_comp_bal_typ_id
3360 ,p_dimension_name =>
3361 'Assignment Inception to Date'
3362 ,p_object_version_number => l_object_version_number
3363 ,p_effective_date => l_effective_date
3364 );
3365
3366 pay_sdb_ins.ins
3367 (p_defined_balance_id => l_defined_balance_id
3368 ,p_balance_type_id => l_eligible_comp_bal_typ_id
3369 ,p_dimension_name =>
3370 'Assignment Run'
3371 ,p_object_version_number => l_object_version_number
3372 ,p_effective_date => l_effective_date
3373 );
3374
3375 pay_sdb_ins.ins
3376 (p_defined_balance_id => l_defined_balance_id
3377 ,p_balance_type_id => l_eligible_comp_bal_typ_id
3378 ,p_dimension_name => 'Assignment Calendar Month To Date'
3379 ,p_object_version_number => l_object_version_number
3380 ,p_effective_date => l_effective_date
3381 );
3382
3383 pay_create_elemnt_tmplt_record.create_elemnt_tmplt_usages(
3387
3384 l_template_id,
3385 'Deductions',
3386 p_legislation_code);
3388 END IF;
3389 END;
3390 END create_dedn_pct_amt_templ;
3391
3392 procedure create_earn_pct_amt_templ( p_legislation_code IN VARCHAR2,
3393 p_currency_code IN VARCHAR2) IS
3394 begin
3395 declare
3396 l_effective_date date;
3397 --
3398 l_template_exists Char;
3399 --
3400 l_template_id number;
3401 l_object_version_number number;
3402 --
3403 l_base_element_id number;
3404 --
3405 l_formula_id number;
3406 l_formula_text varchar2(32000);
3407 l_formula_name varchar2(80);
3408 l_formula_desc varchar2(240);
3409 --
3410 l_primary_bal_typ_id number;
3411 l_days_bal_typ_id number;
3412 l_el_bal_typ_id number;
3413 l_ec_bal_typ_id number;
3414 --
3415 l_input_value_id number;
3416 l_base_pay_value_iv_id number;
3417 l_base_days_iv_id number;
3418 l_base_rate_iv_id number;
3419 l_base_multiple_iv_id number;
3420 l_base_sep_pay_iv_id number;
3421 l_base_proc_sep_iv_id number;
3422 --
3423 l_defined_balance_id number;
3424 --
3425 l_balance_feed_id number;
3426 --
3427 l_reg_tax_proc_type number;
3428 l_non_per_tax_proc_type number;
3429 l_sep_pay_excl_rule_id number;
3430 l_prc_sep_excl_rule_id number;
3431 l_dbc1 number;
3432 l_dbc2 number;
3433 l_dbc3 number;
3434 l_excl_el_no_base_bal number;
3435 l_excl_el_no_el_bal number;
3436 --
3437 l_id number;
3438 l_element_type_usage_id number;
3439 l_balance_attribute_id number;
3440
3441 duplicate_template exception;
3442 l_enabled_flag varchar2(4);
3443
3444
3445 begin
3446
3447 --hr_utility.trace_on (null,'PCT');
3448
3449
3450 /* Check for Template Existence */
3451
3452 l_template_exists := 'N';
3453
3454 BEGIN
3455 SELECT 'Y', Template_id
3456 INTO l_template_exists, l_template_id
3457 FROM pay_element_templates
3458 WHERE Template_type = 'T'
3459 AND Legislation_code = p_legislation_code
3460 AND template_name = 'Percentage of Earnings';
3461 EXCEPTION
3462 WHEN OTHERS THEN
3463 NULL;
3464 END;
3465
3466 IF (l_template_exists = 'Y')
3467 THEN
3468 BEGIN
3469 delete from PAY_ELE_TMPLT_CLASS_USAGES
3470 where template_id = l_template_id;
3471
3472 pay_element_template_api.delete_user_structure(false,true,
3473 l_template_id);
3474 l_template_exists := 'N';
3475 EXCEPTION
3476 WHEN OTHERS THEN
3477 l_template_exists := 'N';
3478 NULL;
3479 END;
3480 END IF;
3481
3482 /* End of Check */
3483
3484 IF l_template_exists = 'N'
3485 THEN
3486
3487 l_effective_date := to_date('1901/01/01', 'YYYY/MM/DD');
3488
3489 --
3490 -- PAY_ELEMENT_TEMPLATES row.
3491 --
3492 pay_etm_ins.ins
3493 (p_template_id => l_template_id
3494 ,p_effective_date => l_effective_date
3495 ,p_template_type => 'T'
3496 ,p_template_name => 'Percentage of Earnings'
3497 ,p_base_processing_priority => 1750
3498 ,p_max_base_name_length => 25
3499 ,p_version_number => 1
3500 ,p_legislation_code => p_legislation_code
3501 ,p_object_version_number => l_object_version_number
3502 );
3503
3504 --
3505 -- Formula _EARN_PCT
3506 --
3507
3508 l_formula_name := '_PCT_EARN';
3509 l_formula_desc := 'Formula for Percentage of Earnings Template';
3510
3511
3512 l_formula_text := '
3513 /******************************************************************************
3514 FORMULA NAME: _PCT_EARN
3515
3516 FORMULA TYPE: Payroll
3517
3518 DESCRIPTION:
3519 This formula applies a percentage to the appropriate
3520 regular earnings of an employee according to the following
3521 rules ::
3522 Salary Admin Pay Basis: REGULAR_SALARY * Percentage
3523 if Pay Basis not hourly; else
3524 ASG_SALARY * Percentage * normal period hours
3525
3526 Formula Results :
3527 template_earning Direct Result for Earnings Amount (ie. Pay Value).
3528 mesg Message indicating that this earnings will be deleted
3529 for this assignment.
3530
3531 ************************************************************************/
3532 /* ===== Defaults Section Begin ===== */
3533
3534 default for Percentage is 0
3535 default for PAY_PROC_PERIOD_START_DATE is ''0001/01/01 00:00:00'' (DATE)
3536 default for PAY_PROC_PERIOD_END_DATE is ''0001/01/02 00:00:00'' (DATE)
3537 default for ASG_FREQ_CODE is ''NOT ENTERED''
3538 default for ASG_SALARY is 0
3539 default for <BASE NAME>_ELIGIBLE_COMP_ASG_RUN is 0
3540
3541 /* ===== Defaults Section End ===== */
3542
3543 /* ===== Inputs Section Begin ===== */
3544
3545 Inputs are Percentage
3546
3550 /* local variables */
3547 /* ===== Inputs Section End ===== */
3548
3549 /**********************/
3551 /**********************/
3552
3553 l_return_status = 1
3554 l_schedule_source = '' ''
3555 l_schedule = '' ''
3556 mesg = '' ''
3557
3558 /* ===== CALCULATION SECTION BEGIN ===== */
3559
3560 IF ASG_SALARY WAS NOT DEFAULTED THEN
3561 (
3562 /* The following will return the Periodic Salary */
3563
3564 calculated_hours = calculate_actual_hours_worked(
3565 PAY_PROC_PERIOD_START_DATE,
3566 PAY_PROC_PERIOD_END_DATE,
3567 '' '',
3568 ''Y'',
3569 ''BUSY'',
3570 '' '',
3571 l_schedule_source,
3572 l_schedule,
3573 l_return_status,
3574 mesg)
3575
3576 calculated_earnings = get_hourly_rate()
3577
3578 earnings_amount = ROUNDUP(
3579 (Percentage * calculated_hours * calculated_earnings / 100),2)
3580 )
3581 ELSE
3582
3583 earnings_amount = ROUNDUP(
3584 (Percentage * <BASE NAME>_ELIGIBLE_COMP_ASG_RUN / 100 ),2)
3585
3586 /* ===== CALCULATION SECTION END ===== */
3587
3588 /* ===== Returns Section Begin ===== */
3589
3590
3591 RETURN earnings_amount,
3592 mesg
3593
3594 /* ===== Returns Section End ===== */
3595
3596 /* End Program */
3597
3598 /* End Formula Text */';
3599
3600 pay_sf_ins.ins
3601 (p_formula_id => l_formula_id
3602 ,p_template_type => 'T'
3603 ,p_legislation_code => p_legislation_code
3604 ,p_formula_name => l_formula_name
3605 ,p_description => l_formula_desc
3606 ,p_formula_text => l_formula_text
3607 ,p_object_version_number => l_object_version_number
3608 ,p_effective_date => l_effective_date
3609 );
3610
3611 --
3612 -- End Formula PERCENTAGE_OF_EARNINGS
3613 --
3614
3615 --
3616 -- 'Base' elements.
3617 --
3618
3619 pay_set_ins.ins
3620 (p_element_type_id => l_base_element_id
3621 ,p_template_id => l_template_id
3622 ,p_element_name => null
3623 ,p_reporting_name => null
3624 ,p_relative_processing_priority => 0
3625 ,p_processing_type => 'N'
3626 ,p_classification_name => 'Earnings'
3627 ,p_input_currency_code => p_currency_code
3628 ,p_output_currency_code => p_currency_code
3629 ,p_multiple_entries_allowed_fla => 'Y'
3630 ,p_post_termination_rule => 'F'
3631 ,p_process_in_run_flag => 'Y'
3632 ,p_additional_entry_allowed_fla => 'N'
3633 ,p_adjustment_only_flag => 'N'
3634 ,p_closed_for_entry_flag => 'N'
3635 ,p_indirect_only_flag => 'N'
3636 ,p_multiply_value_flag => 'N'
3637 ,p_standard_link_flag => 'N'
3638 ,p_process_mode => 'S'
3639 ,p_payroll_formula_id => l_formula_id
3640 ,p_skip_formula => NULL
3641 ,p_object_version_number => l_object_version_number
3642 ,p_effective_date => l_effective_date
3643 ,p_exclusion_rule_id => NULL
3644 );
3645
3646 hr_utility.trace('Base Element Created');
3647
3648 --
3649 -- Input Values for 'Base' element.
3650 --
3651
3652 pay_siv_ins.ins
3653 (p_input_value_id => l_base_pay_value_iv_id
3654 ,p_element_type_id => l_base_element_id
3655 ,p_display_sequence => 1
3656 ,p_generate_db_items_flag => 'Y'
3657 ,p_hot_default_flag => 'N'
3658 ,p_mandatory_flag => 'X'
3659 ,p_name => 'Pay Value'
3660 ,p_uom => 'M'
3661 ,p_exclusion_rule_id => NULL
3662 ,p_object_version_number => l_object_version_number
3663 ,p_effective_date => l_effective_date
3664 );
3665
3666 hr_utility.trace('Base Element Input Value - Pay Value Created');
3667
3668 pay_siv_ins.ins
3669 (p_input_value_id => l_base_days_iv_id
3670 ,p_element_type_id => l_base_element_id
3671 ,p_display_sequence => 2
3672 ,p_generate_db_items_flag => 'Y'
3673 ,p_hot_default_flag => 'N'
3674 ,p_mandatory_flag => 'N'
3675 ,p_name => 'Percentage'
3676 ,p_uom => 'M'
3677 ,p_exclusion_rule_id => NULL
3678 ,p_object_version_number => l_object_version_number
3679 ,p_effective_date => l_effective_date
3680 );
3681
3682 hr_utility.trace('Base Element Input Value - Percentage Created');
3683
3684 pay_siv_ins.ins
3685 (p_input_value_id => l_input_value_id
3686 ,p_element_type_id => l_base_element_id
3687 ,p_display_sequence => 5
3688 ,p_generate_db_items_flag => 'Y'
3689 ,p_hot_default_flag => 'N'
3690 ,p_mandatory_flag => 'X'
3691 ,p_name => 'Jurisdiction'
3692 ,p_uom => 'C'
3696
3693 ,p_object_version_number => l_object_version_number
3694 ,p_effective_date => l_effective_date
3695 );
3697 hr_utility.trace('Base Element Input Value- Jurisdiction');
3698
3699 --
3700 -- Primary balance types.
3701 --
3702
3703 pay_sbt_ins.ins
3704 (p_balance_type_id => l_primary_bal_typ_id
3705 ,p_template_id => l_template_id
3706 ,p_assignment_remuneration_flag => 'N'
3707 ,p_balance_name => null
3708 ,p_currency_code => p_currency_code
3709 ,p_reporting_name => null
3710 ,p_comments =>
3711 'Primary balance for Percentage of Earnings.'
3712 ,p_balance_uom => 'M'
3713 ,p_category_name => NULL --'Earnings'
3714 ,p_input_value_id => l_base_pay_value_iv_id
3715 ,p_object_version_number => l_object_version_number
3716 ,p_effective_date => l_effective_date
3717 ,p_exclusion_rule_id => NULL
3718 );
3719
3720 hr_utility.trace('Primary Balance Type Created.');
3721
3722 pay_sbt_ins.ins
3723 (p_balance_type_id => l_el_bal_typ_id
3724 ,p_template_id => l_template_id
3725 ,p_assignment_remuneration_flag => 'N'
3726 ,p_balance_name => ' EL'
3727 ,p_currency_code => p_currency_code
3728 ,p_reporting_name => ' EL'
3729 ,p_comments =>
3730 'Primary balance for Percentage of Earnings for EL.'
3731 ,p_balance_uom => 'M'
3732 ,p_category_name => NULL --'Employer Liabilities'
3733 ,p_input_value_id => l_base_pay_value_iv_id
3734 ,p_object_version_number => l_object_version_number
3735 ,p_effective_date => l_effective_date
3736 ,p_exclusion_rule_id => NULL
3737 );
3738
3739 hr_utility.trace('Employer Liabities Balance Type Created.');
3740
3741 pay_sbt_ins.ins
3742 (p_balance_type_id => l_ec_bal_typ_id
3743 ,p_template_id => l_template_id
3744 ,p_assignment_remuneration_flag => 'N'
3745 ,p_balance_name => ' Eligible Comp'
3746 ,p_currency_code => p_currency_code
3747 ,p_reporting_name => ' Eligible Comp'
3748 ,p_comments =>
3749 'Eligible Comp. balance for Percentage Earnings.'
3750 ,p_balance_uom => 'M'
3751 ,p_category_name => NULL --'Earnings'
3752 ,p_input_value_id => NULL
3753 ,p_object_version_number => l_object_version_number
3754 ,p_effective_date => l_effective_date
3755 ,p_exclusion_rule_id => NULL
3756 );
3757
3758 hr_utility.trace('Eligible Comp Balance Type Created.');
3759
3760 --
3761 -- Balance Feeds.
3762 --
3763
3764 pay_sbf_ins.ins
3765 (p_balance_feed_id => l_balance_feed_id
3766 ,p_balance_type_id => l_primary_bal_typ_id
3767 ,p_input_value_id => l_base_pay_value_iv_id
3768 ,p_scale => 1
3769 ,p_object_version_number => l_object_version_number
3770 ,p_effective_date => l_effective_date
3771 );
3772
3773 hr_utility.trace('Balance Feed - Pay Value Created.');
3774
3775 pay_sbf_ins.ins
3776 (p_balance_feed_id => l_balance_feed_id
3777 ,p_balance_type_id => l_el_bal_typ_id
3778 ,p_input_value_id => l_base_pay_value_iv_id
3779 ,p_scale => 1
3780 ,p_object_version_number => l_object_version_number
3781 ,p_effective_date => l_effective_date
3782 );
3783
3784 hr_utility.trace('Balance Feed for EL - Pay Value Created.');
3785
3786 --
3787 -- Formula rules.
3788 --
3789
3790 pay_sfr_ins.ins
3791 (p_formula_result_rule_id => l_id
3792 ,p_shadow_element_type_id => l_base_element_id
3793 ,p_result_name => 'earnings_amount'
3794 ,p_result_rule_type => 'D'
3795 ,p_element_type_id => l_base_element_id
3796 ,p_input_value_id => l_base_pay_value_iv_id
3797 ,p_object_version_number => l_object_version_number
3798 ,p_effective_date => l_effective_date
3799 );
3800
3801 hr_utility.trace('Formula Rule - earnings_amount created.');
3802
3803 pay_sfr_ins.ins
3804 (p_formula_result_rule_id => l_id
3805 ,p_shadow_element_type_id => l_base_element_id
3806 ,p_result_name => 'mesg'
3807 ,p_result_rule_type => 'M'
3808 ,p_severity_level => 'W'
3809 ,p_object_version_number => l_object_version_number
3810 ,p_effective_date => l_effective_date
3811 );
3812
3813 hr_utility.trace('Formula Rule - mesg created.');
3814
3815 --
3816 -- Defined Balances for Primary Balance
3817 --
3818
3819 pay_sdb_ins.ins
3820 (p_defined_balance_id => l_defined_balance_id
3821 ,p_balance_type_id => l_primary_bal_typ_id
3822 ,p_dimension_name =>
3823 'Person Calendar Year To Date'
3824 ,p_object_version_number => l_object_version_number
3828 pay_sdb_ins.ins
3825 ,p_effective_date => l_effective_date
3826 );
3827
3829 (p_defined_balance_id => l_defined_balance_id
3830 ,p_balance_type_id => l_primary_bal_typ_id
3831 ,p_dimension_name =>
3832 'Person Calendar Quarter To Date'
3833 ,p_object_version_number => l_object_version_number
3834 ,p_effective_date => l_effective_date
3835 );
3836
3837 pay_sdb_ins.ins
3838 (p_defined_balance_id => l_defined_balance_id
3839 ,p_balance_type_id => l_primary_bal_typ_id
3840 ,p_dimension_name =>
3841 'Person Calendar Month To Date'
3842 ,p_object_version_number => l_object_version_number
3843 ,p_effective_date => l_effective_date
3844 );
3845
3846
3847 pay_sdb_ins.ins
3848 (p_defined_balance_id => l_defined_balance_id
3849 ,p_balance_type_id => l_primary_bal_typ_id
3850 ,p_dimension_name =>
3851 'Assignment Calendar Year To Date'
3852 ,p_object_version_number => l_object_version_number
3853 ,p_effective_date => l_effective_date
3854 );
3855
3856 pay_sdb_ins.ins
3857 (p_defined_balance_id => l_defined_balance_id
3858 ,p_balance_type_id => l_primary_bal_typ_id
3859 ,p_dimension_name =>
3860 'Assignment Calendar Month To Date'
3861 ,p_object_version_number => l_object_version_number
3862 ,p_effective_date => l_effective_date
3863 );
3864
3865 pay_sdb_ins.ins
3866 (p_defined_balance_id => l_defined_balance_id
3867 ,p_balance_type_id => l_primary_bal_typ_id
3868 ,p_dimension_name =>
3869 'Assignment Calendar Quarter To Date'
3870 ,p_object_version_number => l_object_version_number
3871 ,p_effective_date => l_effective_date
3872 );
3873
3874
3875 pay_sdb_ins.ins
3876 (p_defined_balance_id => l_defined_balance_id
3877 ,p_balance_type_id => l_primary_bal_typ_id
3878 ,p_dimension_name => 'Payments'
3879 ,p_object_version_number => l_object_version_number
3880 ,p_effective_date => l_effective_date
3881 );
3882
3883 --
3884 -- Defined Balances for Employer Liabilities Balance
3885 --
3886
3887 pay_sdb_ins.ins
3888 (p_defined_balance_id => l_defined_balance_id
3889 ,p_balance_type_id => l_el_bal_typ_id
3890 ,p_dimension_name =>
3891 'Person Calendar Year To Date'
3892 ,p_object_version_number => l_object_version_number
3893 ,p_effective_date => l_effective_date
3894 );
3895
3896 pay_sdb_ins.ins
3897 (p_defined_balance_id => l_defined_balance_id
3898 ,p_balance_type_id => l_el_bal_typ_id
3899 ,p_dimension_name =>
3900 'Person Calendar Quarter To Date'
3901 ,p_object_version_number => l_object_version_number
3902 ,p_effective_date => l_effective_date
3903 );
3904
3905 pay_sdb_ins.ins
3906 (p_defined_balance_id => l_defined_balance_id
3907 ,p_balance_type_id => l_el_bal_typ_id
3908 ,p_dimension_name =>
3909 'Person Calendar Month To Date'
3910 ,p_object_version_number => l_object_version_number
3911 ,p_effective_date => l_effective_date
3912 );
3913
3914 pay_sdb_ins.ins
3915 (p_defined_balance_id => l_defined_balance_id
3916 ,p_balance_type_id => l_el_bal_typ_id
3917 ,p_dimension_name =>
3918 'Assignment Run'
3919 ,p_object_version_number => l_object_version_number
3920 ,p_effective_date => l_effective_date
3921 );
3922
3923 pay_sdb_ins.ins
3924 (p_defined_balance_id => l_defined_balance_id
3925 ,p_balance_type_id => l_el_bal_typ_id
3926 ,p_dimension_name =>
3927 'Assignment Calendar Year To Date'
3928 ,p_object_version_number => l_object_version_number
3929 ,p_effective_date => l_effective_date
3930 );
3931
3932 pay_sdb_ins.ins
3933 (p_defined_balance_id => l_defined_balance_id
3934 ,p_balance_type_id => l_el_bal_typ_id
3935 ,p_dimension_name =>
3936 'Assignment Calendar Quarter To Date'
3937 ,p_object_version_number => l_object_version_number
3938 ,p_effective_date => l_effective_date
3939 );
3940
3941 pay_sdb_ins.ins
3942 (p_defined_balance_id => l_defined_balance_id
3943 ,p_balance_type_id => l_el_bal_typ_id
3944 ,p_dimension_name =>
3945 'Assignment Calendar Month To Date'
3946 ,p_object_version_number => l_object_version_number
3947 ,p_effective_date => l_effective_date
3948 );
3949
3950 --
3951 -- Defined Balances for Eligible Comp Balance
3952 --
3953
3954 pay_sdb_ins.ins
3955 (p_defined_balance_id => l_defined_balance_id
3956 ,p_balance_type_id => l_ec_bal_typ_id
3960 ,p_effective_date => l_effective_date
3957 ,p_dimension_name =>
3958 'Assignment Run'
3959 ,p_object_version_number => l_object_version_number
3961 );
3962
3963 pay_create_elemnt_tmplt_record.create_elemnt_tmplt_usages(
3964 l_template_id,
3965 'Earnings',
3966 p_legislation_code);
3967
3968 END IF;
3969 end;
3970 END create_earn_pct_amt_templ;
3971
3972 procedure create_all_templates(p_legislation_code in varchar2, p_currency_code in varchar2) is
3973 Begin
3974
3975 -- hr_utility.trace_on (null,'FLATAMTDEDN');
3976 pay_create_elemnt_tmplt_record.create_earn_flat_amt_templ(p_legislation_code,
3977 p_currency_code);
3978 pay_create_elemnt_tmplt_record.create_earn_pct_amt_templ(p_legislation_code,
3979 p_currency_code);
3980 pay_create_elemnt_tmplt_record.create_earn_hxr_amt_templ(p_legislation_code,
3981 p_currency_code);
3982 pay_create_elemnt_tmplt_record.create_dedn_pct_amt_templ(p_legislation_code,
3983 p_currency_code);
3984 pay_create_elemnt_tmplt_record.create_dedn_flat_amt_templ(p_legislation_code,
3985 p_currency_code);
3986
3987 end create_all_templates;
3988
3989 END pay_create_elemnt_tmplt_record;
3990