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